Posts Tagged ‘SQL Server’

Its very common when a Developer/DBA wanted to see sample of few records, commonly they use SELECT TOP N records from a table being ordered by a column,  the query look like as


As we know above statement is that if the table has multiple records having the same value as that of the selected list then all those records will not be selected. It will select only one record. But if we want to select all the rows, with same value as the one selected we have to include WITH TIES option in the query. So the query would be

For demonstration purpose, below are the step by step details example

Step -1 , Create a Sample Table as PRODUCTLIST

Create table ProductList(ID Int Identity(1,1),PName varchar(30),Price Decimal(10,2))

Step -2 , Insert some sample data

insert into ProductList (Pname,Price) values
(‘Bajaj CFL’,    210.00),
(‘TL’,    135.00),
(‘Table Fan’    ,450.00),
(‘Iron’    ,450.00),
(‘Cable’    ,250.00),o
(‘USB Disk’    ,450.00),
(‘Floppy’    ,120.00),
(‘CD-R’    ,280.00),
(‘CD-W’    ,450.00),
(‘USB Cable’    ,180.00)


For testing, SELECT TOP 3 * from PRODUCTLIST Order by Price DESC will return only 3 records as

But SELECT TOP 3 WITH TIES from ProductList Order by Price DESC will return more than 3 records as

 If we examine about performance, initial one without TIES is more better compare to WITH TIES option, Let see an example here, suppose we have to find top 1 record having maximum price, let see the execution plan

Here we can see, WITH TIES option is performing very poorly compare to initial Select TOP query code, this happened because of ORDER BY clause, to resolve this here we have to create a proper index here for same to get the optimal performance.


The .WRITE clause is an integral part of the UPDATE statement. Commonly it’s used to perform a partial update on big data set of VARCHAR (MAX), NVARCHAR (MAX) and VARBINARY (MAX) data types. Its functionally is very similar to the standard STUFF statement. The UPDATE statement is logged, however, partial updates to large value data types using the .WRITE are minimally logged.
In general practice we use REPLACE or STUFF function to update partial data of a big data values.

To demonstrate this , here I am creating a test table as :

DROP TABLE dbo.VirendraTest

–Create a table as ‘VirendraTest’

CREATE TABLE dbo.VirendraTest (Details VARCHAR(MAX))|

–Insert test data
INSERT INTO dbo.VirendraTest (Details)
VALUES (‘VIRENDRA YADUVANSHI – Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker’);

— Check test data

Select from VirendraTest

Now, let see syntax of WRITE

.WRITE ( expression, @Offset , @Length )

 As per BOL – The .WRITE (expression, @Offset, @Length) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) data types. For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple.

Suppose, here I want to change word ‘Microsoft’ as ‘MS’, there may be 2 options, either use of REPLACE or STUFF as

–Option 1
SET VT.Details REPLACE(Details,‘Microsoft’,‘MS’FROM dbo.VirendraTest AS VT
–Option 2
SET VT.Details =STUFF(Details,CHARINDEX(‘Microsoft’,Details,1),LEN(‘Microsoft’),‘MS’)
FROM dbo.VirendraTest AS VT


Now same thing with .WRITE
–UPDATE with .WRITE option
UPDATE VT SET Details.WRITE(‘MS’,(CHARINDEX(‘Microsoft’,Details,1)-1),LEN(‘Microsoft’))
FROM dbo.VirendraTest AS VT
 Please do comment on this performance tips

Happy Reading!


In SQL Server Log Shipping process, we can see two files having extension name like .TUF and .WRK. These both files only generate on Log Shipping’s Secondary Server.
Actually SQL server manage internally handle the file copy process and restoration process at Secondary server using LS Copy and LS Restore job which are created at secondary server during log shipping initialization process. To manage the file copy process from Primary server to Secondary server, .WRK files temporarily generated. Means, The .wrk file got generate when the transaction log backups files are being copied from the backup location (Commonly at Primary Server end) to the secondary server by the agent job named as LS-Copy on the secondary, and when file copied completely at secondary server, they renamed to the .trn
extension. The temporary naming using the .wrk extension indicates/ensure that the files will not picked up by the restore job until successfully copied.

The .TUF file is the Transaction Undo File. It got generated only when we have configured the Log Shipping with Stand by Option only. As we know, in Stand by Log Shipping option, Secondary Database is available to user in read mode. In this scenario .TUF file Keeps Pending Transaction Which are in Log File came from Primary server, and when next Log Backup will come from primary server they can be synchronized at Secondary Server.

Happy Reading ! You live several lives while reading  🙂 


Some time we have to do Row Numbering, for same we can use IDENTTY function with SELECT Statement, but we can’t use it with simply SELECT command, its always used with … INTO .. form as

SELECT Col1,Col2..Coln INTO TableName from SourceTablename

If it tried with simply select command as

Select identity(int,1,1IDDEMPNAME,DEPT,BASICSALARY from Employee
Will give error as 

Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

So if we want to do it, we have to pass Select statement as

Select IDENTITY(int,1,1IDDNAME,BASICSALARY into #TempEmp from Employee
from #TempEmp
The IDENTITY gets three mandatory parameters, namely datatype of the identity column, starting value and the increment. With these, it can customize the new column according to our requirement. For example, an integer starting from 1 and incremented for each row with 1 can be specified as:

Note : Suppose in source table there is already a Identity column exist, this column should be not in Select statement otherwise it will give a error, let in Employee Table, EMPID is an IDENTITY column, and we are passing Select statement as

Select IDENTITY(int,1,1IDDEMPID,NAME,BASICSALARY into #TempEmp from Employee
from #TempEmp
Will throw error as
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘#TempEmp’, which already has column ‘EMPID’ that inherits the identity property.

As a DBA we have to maintain our all databases Dictionaries, Here is a Script , from which we can generate a full view of Tables structure details.

SELECT a.Name  [Table Name],  [Column Name],  [Data Type],
b.length [Column Length],
b.isnullable [Allow Nulls],
CASE WHEN is null  THEN 0 ELSE 1 END  [Primary Key],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [ForiegnKey],
CASE WHEN e.parent_object_id is null
THEN ‘-‘ ELSE END [Reference Table],
       CASE WHEN h.value is null THEN ‘-‘ ELSE h.value END [Description]
from sysobjects as a
join syscolumns as on
join systypes as on b.xtype  c.xtype
left join (SELECT,sc.colid, FROM syscolumns sc
JOIN sysobjects so ON
JOIN sysindexkeys si ON and sc.colid  si.colid
WHERE si.indid = 1on and b.colid d.colid
Left join sys.foreign_key_columns as on e.parent_object_id
          and b.colid e.parent_column_id
left join sys.objects as on e.referenced_object_id g.object_id
left join sys.extended_properties as on h.major_id
and b.colid h.minor_id
where a.type ‘U’
order by

We can also get somehow details, not as above , using below



Here are initial tips for writing efficient/  cost-effective Queries

  • When using ANDput the condition least likely to be true first. The database system evaluates conditions from left to right, subject to operator precedence. If you have two or more AND operators in a condition, the one to the left is evaluated first, and if and only if it’s true is the next condition evaluated. Finally, if that condition is true, then the third condition is evaluated. You can save the database system work, and hence increase speed, by putting the least likely condition first. For example, if you were looking for all members living in Delhi and born before January 1, 1960, you could write the following query:
    SELECT  FirstName LastName  FROM  EMPLOYEE  WHERE  State  ‘Delhi’  AND  DateOfBirth  ‘1960-01-01’
    The query would work fine; however, the number of members born before that date is very small, whereas plenty of people live in New State. This means that State = Delhi will occur a number of times and the database system will go on to check the second condition, DateOfBirth < ‘1960-01-01’. If you swap the conditions around, the least likely condition (DateOfBirth < ‘1960-01-01’) is evaluated first:
    SELECT  FirstName LastName  FROM  MemberDetails  WHERE
    DateOfBirth  ‘1960-01-01’  AND  State  ‘Delhi’;
    Because the condition is mostly going to be false, the second condition will rarely be executed, which saves time. It’s not a big deal when there are few records, but it is when there are a lot of them.
  • When using ORput the condition most likely to be true first. Whereas AND needs both sides to be true for the overall condition to be true, OR needs only one side to be true. If the left-hand side is true, there’s no need for OR to check the other condition, so you can save time by putting the most likely condition first. Consider the following statement:
    SELECT  FirstName LastName  FROM  MemberDetails  WHERE  State
    ‘Delhi’  OR  DateOfBirth  ‘1960-01-01’;
    If Delhi is true, and it is true more often than DateOfBirth < ‘1960-01-01’ is true, then there’s no need for the database system to evaluate the other condition, thus saving time.
  • DISTINCT can be faster than GROUP BYDISTINCT and GROUP BY often do the same thing: limit results to unique rows. However,DISTINCT is often faster with some database systems than GROUP BY. For example, examine the following GROUP BY:

    SELECT  MemberId  FROM  Orders  GROUP  BY  MemberId;
    GROUP BY could be rewritten using the DISTINCT keyword:
    SELECT  DISTINCT  MemberId  FROM  Orders

  • Use IN with your subqueries. When you write a query similar to the following, the database system has to get all the results from the subquery to make sure that it returns only one value,
    EMPID  (SELECT  EMPID  FROM  Orders  WHERE  OrderId  = 2);
    If you rewrite the query using the IN operator, the database system only needs to get results until there’s a match with the values returned by the subquery; it doesn’t necessarily have to get all the values:

    SELECT  FirstName LastName  FROM  EMPLOYEE  WHERE  EMPID  IN (SELECT  EMPID  FROM  Orders  WHERE  OrderId  = 2);

  • Avoid using SELECT * FROM. Specifying which columns you need has a few advantages, not all of them about efficiency. First, it makes clear which columns you’re actually using. If you use SELECT * and actually use only two out of seven of the columns, it’s hard to guess from the SQL alone which ones you’re using. If you say SELECT FirstName, LastName…..then it’s quite obvious which columns you’re using. From an efficiency standpoint, specifying columns reduces the amount of data that has to pass between the database and the application connecting to the database. This is especially important where the database is connected over a network.
  • Search on integer columns. If you have a choice, and often you don’t, search on integer columns. For example, if you are looking for the member whose name is VIRENDRA YADUVANSHI and whose MemberId is 101, then it makes sense to search via the MemberId because it’s much faster.

Locking is a means of not allowing any other transaction to take place when one is already in progress. In this the data is locked and there won’t be any modification taking place till the transaction either gets successful or it fails. The lock has to be put up before the processing of the data whereas Multi-Versioning is an alternate to locking to control the concurrency. It provides easy way to view and modify the data. It allows two users to view and read the data till the transaction is in progress. Multiversion concurrency control is described in some detail in the 1981 paper “Concurrency Control in Distributed Database Systems” by Philip Bernstein and Nathan Goodman.
Multi-Versioning, As the name implies, each record in the system might have multiple versions visible to different transactions. When a transaction modifies a record, a new version is written to the database, and a previous version, representing only the difference between the version of the record that was read by the transaction and the new value of the record, is written as a back version of that record.
Read committed isolation using row versioning is somewhere in between Locks and Multi-Versioning. Under this isolation level, read operations do not acquire locks against the active live data. However, with update operations the process is the same for this isolation level as it is for the default read committed isolation level, The selection of rows to update is done by using a blocking scan where an update lock is taken on the data row as data values are read.
Snapshot isolation uses purely Multi-Versioning because data that is to be modified is not actually locked in advance, but the data is locked when it is selected for modification. When a data row meets the update criteria, the snapshot transaction verifies that the data has not been modified by another transaction after the snapshot transaction started. If the data has not been modified by another transaction, the snapshot transaction locks the data, updates the data, releases the lock, and moves on. If the data has been modified by another transaction, an update conflict occurs and the snapshot transaction rolls back.
Although locking can be the best concurrency-control choice for applications that require data consistency and inter-transaction protection, it can cause writers to block readers. If a transaction changes a row, another transaction cannot read the row until the writer commits. There are cases where waiting for the change to complete is the correct response; however, there are cases where the previous transactionally consistent state of the row is enough.

As we know blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.
By using the read-committed snapshot isolation level introduced in Microsoft SQL Server 2005, we can reduce blocking and deadlocking issues caused by lock contention.Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. 

As per BOL, The read-committed snapshot isolation level provides the following benefits:

  • SELECT statements do not lock data during a read operation. Read transactions do not block write transactions and vice versa.
  • Since SELECT statements do not acquire locks in most cases, the number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks.
  • The possibility of blocking is significantly reduced.
  • SELECT statements can access the last committed value of the row while other transactions are updating the row without getting blocked.
  • The number of blocks and deadlocks is reduced.
  • Fewer lock escalations occur.

A quick and easy way to remember how Read-Committed Snapshot Isolation works:

  • Readers DO NOT block Writers
  • Writers DO NOT block Readers
  • Writers DO block Writers

REMEMBER: TempDB is a critical resource for the performance of Read-Committed Snapshot Isolation. RCSI puts a copy of the records that are being modified in TempDB before the transaction is committed so you need to make sure that TempDB has appropriate space and is placed on fast drives that ideally are isolated away from your user database data and log files.

The default Syntax is as


As we know, to check whats going on at server level, we commonly use SP_WHO or SP_WHO2 but from both of these, there are no clear picture about exact commands which currently running on server, these shows the command, not exact full text string that’s currently running, Here is a some DMV’s manipulation to find the same.

SELECT SPID er.session_id
er.logical_reads er.reads
OBJECT_SCHEMA_NAME(qt.objectid,dbid‘.’ + OBJECT_NAME(qt.objectidqt.dbid)
= SUBSTRING(qt.text,er.statement_start_offset/2,
CASE WHEN er.statement_end_offset 1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE
END – er.statement_start_offset)/2)
=CASE ses.transaction_isolation_level 
                        WHEN 0 THEN ‘Unspecified’
                        WHEN 1 THEN 
‘Read Uncommitted’
                        WHEN 2 THEN ‘Read Committed’
                        WHEN 3 THEN ‘Repeatable’
                        WHEN 4 THEN ‘Serializable’
                        WHEN 5 THEN ‘Snapshot’
sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id er.session_id
JOIN sys.dm_exec_connections con ON con.session_id ses.session_id
APPLY sys.dm_exec_sql_text(er.sql_handleas qt
OUTER APPLY SELECT execution_count MAX(cp.usecountsFROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle er.plan_handle ec
ORDER BY er.blocking_session_id DESCer.logical_reads er.reads DESCer.session_id