How to improve SQL Performance when searching by date

If you have a datetime column in a WHERE clause, and you need to convert it or use a data function, try to push the function to the literal expression.

For the below two query , the First one take more query cost rather than the Second one,

SELECT OrderID FROM dbo.Orders WHERE DATEADD(day, 15, 
OrderDate) = '07/23/1996'



SELECT OrderID FROM Orders WHERE OrderDate = DATEADD(day, 

-15, '07/23/1996')

In below Figure you can check the query cost

How to create Dynamic Pivot Table in SQL?

Here below code, which describes to create Dynamic Pivot view from the Table.




Create table yourtable (itemID INT, part CHAR(1))



INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')

DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted

Set @sql=N' if object_id(''anewtable'',''U'') is not null drop table anewtable ; with mycte as (SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols

FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select *   into aNewtable
from mycte;'
 --print @sql
 exec sp_executesql @sql;


 select * from aNewtable

 select * from yourtable

drop table yourtable








Happy coding!!!!!!!!!!!!!!!!!!