SQL TIps

Change Data Capture (CDC)–An easy way to track data changes of a database–TIP #79

  In last TIP #78  we have discussed Change tracker (CT) which was introduced in SQL SERVER 2008. CT feature only tracks which row is changes means on which row Insert/update/delete operation is performed but it does not track what exact value is changed. If we want an audit of database means whatever changes occurred …

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

  When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space . Now what you need to do to get free space from each drive ? Just create a simple job which run on daily basis which send you space …

How to determine Meta data or result set information of a stored procedure /trigger ? TIP # 73

  Sometimes it may require that you don’t know what will be output of  a stored procedure ? what kind of result set it return ? In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object. In other words if we want to know result set’s meta data then we can use …

How to replace a specific string from a large string with Write feature ? TIP #72

  This is very interesting feature and I recently come to know this awesome feature. Lets understand this by an example below Suppose we have a student table with following structure  as shown in below image   We have a detail column with VARCHAR(MAX) . Now as it is VARCHAR(MAX) column it may content a …

How explicitly insert default value ? TIP #72

  Suppose, sometimes you just need  entry in the table with all the default values. For example you have a Student table which looks like as below CREATE TABLE #tmpStudent (StudentId INT IDENTITY(1,1),                          FirstName VARCHAR(100) DEFAULT ‘RAJAT’,                          Course    VARCHAR(100) DEFAULT ‘MATHS’)   Now , suppose for in some situation we need just default entry for …