CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO
Tag: query
Create, Alter, Drop and Execute SQL Server Stored Procedures
A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table. A block of T-SQL code can be saved in a T-SQL script file. You can also store the code from a script file in a stored procedure. There are several benefits that result from […]
SQL Query to Find and Replace text in a stored procedures
Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]
“ORDER BY” with CASE and UNION in SQL Server
If you want to use the combination of ORDER BY, CASE and UNION then you should use inner query for a solution without any headache: SELECT * FROM ( SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE conditions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE conditions ) x ORDER BY CASE […]
Average of all values in a column that are not zero in SQL
SELECT AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END) …. AVG function will not take into account NULL values. So you can also use this AVG (NULLIF(Value, 0))
Date and Time Conversions in SQL Server
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, […]
How to find records based on CAPITAL and small letters in SQL Query?
SELECT * FROM SQLTABLE WHERE SUBSTRING(TableColumn, 1, 1) = ‘x’ COLLATE SQL_Latin1_General_CP1_CS_AS
Get a Tree view with SQL Query
First of all, i must thank to Maulik Dhorajia for his great post. That post saved me a lot.