LAG & LEAD In Sql Server

Suppose you have to find next value of the given column or the previous value with SQL server 2012 or above, you can use : SELECT id,department,Code, LEAD(department,2,’hello’) OVER (ORDER BY department ) LeadValue, LAG(Code,2) OVER (ORDER BY department ) LagValue FROM test_table order by department LEAD(department,2,’hello’) OVER (ORDER BY department ) Above is in […]

Read more "LAG & LEAD In Sql Server"

Parameter Sniffing

Whenever a stored procedure is executed for the first time, it compiles plan into the the plan cache, which enables Sql sevrer to actullay use the Cached plan instead of spending time on CPU cycle recompilation Suppose some stored procedure is to get some report which expects date range as parameter, first time the procedure […]

Read more "Parameter Sniffing"

Implicit Data Conversions

SQL server is smart, A little too smart for its’ own good. Implicit data type conversions are a total performance killer, It may appear to be small issue but when it comes to filtering data they cause problems which are hard to catch Suppose we have a query where the data to be filtered on […]

Read more "Implicit Data Conversions"

Script to Generate Dynamic Insert Into statments

Use below script to have dynamic Insert into statments.

Typically may be used to replicate data from one database to another

SELECT DISTINCT ‘SET IDENTITY_INSERT ‘+TABLENAME +’ ON’+’ INSERT INTO ‘+ TABLENAME+’ (‘+(STUFF((
SELECT ‘, ‘ + name
FROM SYS.COLUMNS Z
WHERE Z.object_id=Y.object_id

AND Y.object_id=OBJECT_ID(C.TABLENAME)

FOR XML PATH(”)
), 1, 2, ”)) +’ )’
+
‘SELECT ‘ + (STUFF((
SELECT ‘, ‘ + name
FROM SYS.COLUMNS Z
WHERE Z.object_id=Y.object_id

AND Y.object_id=OBJECT_ID(C.TABLENAME)

FOR XML PATH(”)
), 1, 2, ”))+ ‘ FROM ‘+TABLENAME +’ SET IDENTITY_INSERT ‘+TABLENAME+’ OFF’

FROM (

SELECT [Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
WHERE [Tables].name like ‘YourTableNameHere%’ — Change table Name here
GROUP BY SCHEMA_NAME(schema_id), [Tables].name) AS C

INNER JOIN SYS.COLUMNS Y ON Y.object_id=OBJECT_ID(C.TABLENAME)
WHERE TotalRowCount >0

Read more "Script to Generate Dynamic Insert Into statments"

Merge Join

Merge join is a strategy which sql servers takes up when condition on which the table is joined is a column which is ordered ; this happens when lets say that two table T1, T2 have columns T1C and T2C T1C and T2C are the columns on which the tables are joined these both columns […]

Read more "Merge Join"