Recover unsaved SQL query scripts

Imagine a situation when you are working on a query for couple of hours and suddenly your management studio is crashed or your PC is restarted and you forgot to hit the “CTRL + S” in between.  Worst nightmare of any developer.   It is always extrmely difficult to re-create the same query again.

In the past I used to go to this folder to find my unsaved sql scripts : C:\Users\YourUsername\AppData\Local\Temp

But somehow this was never realiable.  There were few occassions when I didn’t find the query I was looking for.   Few days back I came across this table valued system fuction sys.dm_exec_sql_text which can save our day from the nightmare of not saving the scripts.  More information about this function can be found on the Books Online here: sys.dm_exec_sql_text

Now striaght away jump to the query which can help us to recover the unsaved SQL query scripts from the management studio.  

--------------------------------------------------------------------------------------------------------
USE [DATABASE_NAME]
SELECT S.LAST_EXECUTION_TIME
,T.TEXT AS [SCRIPT]
FROM SYS.DM_EXEC_QUERY_STATS AS S
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE) AS T
--WHERE T.TEXT LIKE '%SOMETHING%'
ORDER BY S.LAST_EXECUTION_TIME DESC
--------------------------------------------------------------------------------------------------------

Few notes:
1. You will need VIEW SERVER STATE permissions to execute this query.
2. Since there could be many queries running on the server you would like to use WHERE condition TEXT LIKE '%SOMETHING%'.  
3. You can recover the queries only till SQL Server keeps them in the memory.
4. DM_EXEC_QUERY_STATS has few more useful columns that can help you with some extra information related to your query. You may like to add them in this query.

Thanks
Mangal

Convert Seconds to HH:MM:SS

Today 2 of my colleagues from reporting team had this requirement. They had one table where Seconds were stored as INT and in report they wanted to convert the seconds to HH:MM:SS format.  They already had 1 solution ready with them.   It was something like:

---------------------------------------------------------------------------------------------------------------

DECLARE @Seconds INT
SET @Seconds = 3800

SELECT CONVERT(VARCHAR(10),@Seconds/3600) 
    +':'
    + RIGHT('00'+CONVERT(VARCHAR(2),(@Seconds%3600)/60),2)
    +':'
    + RIGHT('00'+CONVERT(VARCHAR(2),@Seconds%60),2) AS [HH:MM:SS]
  GO
----------------------------------------------------------------------------------------------------------------- The output--
01:03:20

It was working fine, but my colleagues were looking for something different, something elegant.  And I jumped on to help them.  Now a days I hardly get any chance to write SQL, so i don’t let such opportunities go.  I had one solution in mind using CONVERT and Style 108:

--------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT
SET @Seconds = 3800
SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108) AS [HH:MM:SS]
GO
---------------------------------------------------------------------------------------------------------------

But problem with the above query is, it fails when number of seconds are more than 86399(there are total 86400 seconds in a day).  So if number of seconds are 86400; above query will show 00 hours instead of showing 24 hours.  See following example:

--------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT
SET @Seconds = 86500
SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108)  AS [HH:MM:SS]
GO
---------------------------------------------------------------------------------------------------------------

-- The output--
00:01:40

Here the expected output was 24:01:40.  So I modified the above query a little and I came up with this:

---------------------------------------------------------------------------------------------------------------DECLARE @Seconds INT,@Hour VARCHAR(10)
SET @Seconds = 86500
SET @Hour = DATEDIFF(HH, 0, DATEADD(SS,@Seconds,0))
SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108),1,2,@Hour)  AS [HH:MM:SS]
GO
---------------------------------------------------------------------------------------------------------------

-- The output--
24:01:40

Just in case you want to run above query on table:

--------------------------------------------------------------------------------------------------------------- SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SS, Seconds,0),108),1,2, DATEDIFF(HH, 0, DATEADD(SS, Seconds,0)))  AS [HH:MM:SS]
FROM TableName
GO
---------------------------------------------------------------------------------------------------------------

I don’t know which approach is better, rather you tell me which one you liked.  Looking at them I think both the queries will give almost identical performance, just that 1st query looks like a Mathematical solution while my approach looks like a SQL solution.  If you know any other approach please feel free to share.

Namaste!
- Mangal

Alternating Row Background Color in Reports

Now lets see how to get alternating row background colors in Reports. This is one of the 1^st typical report enhancement you might be doing once you started working on Reporting Services.  See the following report : 

This can be achieved very easily in SSRS with the help of IIF and RowNumber function for a simple report with no groupings.   You can use a background color expression as:

= IIF(RowNumber(Nothing) Mod 2, "White","Gainsboro")

It becomes little complicated in cases of Matrix and when some groupings are involved.  In that case use following expression for the details row within a group:

= IIF(RunningValue(Fields!Some_Field.Value,Count,"Group_Name") Mod 2, "White","Gainsboro")

Here instead of RowNumber function I have used he RunningValue function.  Just remember to replace Some_Field with actual column name and Group_Name with actual Group Name in your report.

Expiration Date of SQL Server Evaluation Edition

If you have installed the Evaluation / Trial Version of SQL Server, you may like to find out what is the expiry date of the Trial Version.  The Trial Edition or Evaluation Edition of SQL Server will expire exactly 180 days after the install date.  Using it after the 180 days period is violation of MICROSOFT evaluation license terms.  Anyways after 180 days when an install of Evaluation Edition Expires, the SQL Server services stop functioning.  So you must purchase the license for SQL Server and upgrade the Evaluation copy before the expiry date of the Evaluation.

But then how to find out the exact expiry date of Trial Version?  There are 2 ways to find out

• From installation logs
• From Management Studio

From installation logs
A summary.txt file is get created during the installation of SQL Server.  So this summary.txt can give you the exact date and time of the SQL Server installation.  And the trial software will automatically expire after six months.  You can find summary.txt file under

<Drive>\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

At the end of the file you can see something like

Setup succeeded with the installation, inspect the log file completely for status on all the components. Time : Tue Dec 30 15:59:55 2008

So after 180 days counting from 30 Dec 2008 i.e. 28 Jun 2009 the SQL Server Evaluation Edition will be expired.

From Management Studio
Now this is very simple. Open the Management Studio, click on the “Help” of Menu Bar and then on “About”.  A new window will appear, where you will see some thing like:

Microsoft SQL Server Management Studio (expires in 102 days)

See the Image :

Calculating Running Totals

Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server

In one of my previous posts I wrote about Calculating Running Totals using a SELF JOIN.  But after realizing that, SELF JOIN is not efficient way of calculating Running Totals I thought of writing this post, with another technique without using Cursor.

In this post I'll show you how to Calculate Running Totals without using a cursor.  On the same line with my previous post, the different scenarios I’ll be covering in this post are -

• Running Total for all records.
• Running Total on each Group of Records.
• Running Total on each Date.

Here is our sample table :

ShipId	OrderId	ShipDate	Quantity
1	1	2009-02-01 12:06:16.820	10
2	1	2009-02-02 16:16:16.820	15
3	2	2009-02-01 13:26:16.820	20
4	2	2009-02-03 17:40:16.820	12
5	2	2009-02-04 13:05:16.820	15
6	3	2009-02-03 14:25:16.820	25
7	3	2009-02-04 15:50:16.820	50
8	3	2009-02-05 19:40:16.820	30
9	4	2009-02-06 15:30:16.820	20
10	4	2009-02-07 16:20:16.820	10
11	5	2009-02-05 17:05:16.820	35

Create Sample Data:

-- Create Table CREATE TABLE Sales ( ShipId INT, OrderId INT, ShipDate DateTime, Quantity INT ) GO -- Load Sample Data INSERT INTO Sales SELECT 1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT 2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT 3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT 4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT 5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT 6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT 7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT 8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT 9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT 10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT 11,5, 'Feb 05 2009 05:05:16:820PM',35

Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I'll be using a correlated sub query to calculate the Running Totals for the all the records. So the query will be :

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity         ,(SELECT SUM(I.Quantity)             FROM Sales I             WHERE I.ShipId <= O.ShipId          ) as RunningTotal FROM Sales O ORDER BY O.ShipId

Here is the result returned by above query :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

Case II: Running Total on each Group of Records.
Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one condition in WHERE clause of , that will be - I.OrderId = O.OrderId. So the final query will be:

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity         ,(SELECT SUM(I.Quantity)             FROM Sales I             WHERE I.OrderId = O.OrderId             AND I.ShipId <= O.ShipId          ) as RunningTotal FROM Sales O ORDER BY OrderId

And the Output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Case III: Running Total on each Date.
Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our WHERE clause, observe them carefully and try to understand what I’m trying to do :

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity         ,(SELECT SUM(I.Quantity)             FROM Sales I             WHERE I.shipDate <= O.Shipdate             AND DateAdd(dd, DateDiff (dd, 0, I.Shipdate), 0)             = DateAdd(dd, DateDiff (dd, 0, O.Shipdate) ,0)           ) as RunningTotal FROM Sales O ORDER BY O.ShipDate

And the output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
3	2	2009-02-01 13:26:16.820	20	30
2	1	2009-02-02 16:16:16.820	15	15
6	3	2009-02-03 14:25:16.820	25	25
4	2	2009-02-03 17:40:16.820	12	37
5	2	2009-02-04 13:05:16.820	15	15
7	3	2009-02-04 15:50:16.820	50	65
11	5	2009-02-05 17:05:16.820	35	35
8	3	2009-02-05 19:40:16.820	30	65
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	10

Mangal Pardeshi

Query to fetch the Yesterday's Data

Seems to be easy question. And many of you may be already knew 2-3 ways of doing it. Here is the query I use normally to get the all the data from the previous day(Yesterday).

SELECT Id, OrderId, CustomerId FROM Orders WHERE Date >= DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AND Date < DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)

Make it more readable and scalable using variable

DECLARE @YesterDay DATETIME, @Today DATETIME SET @YesterDay = DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) SET @Today = DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) SELECT Id, OrderId, CustomerId FROM Orders WHERE  Date >= @YesterDay AND Date < @Today

Logic : Basically above WHERE conditions is like - Greater than Equal to Yesterday and Less than Today.

This DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) gives Yesterday's date with time as 00:00:00.

And this DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)  gives today's date with time as 00:00:00.

You can do a quick check by executing -

SELECT DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AS YDay ,DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) as ToDay

Reason for doing this way instead of using some obvious ones – As you may heard this from many SQL experts, “Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.”

Question is Why?

Well whenever you use the any function on a column , SQL Server no longer uses the Index define on particular column. And use of a CAST or CONVERT functions slows down the query execution considerably.

You may say, I used the DateAdd and DateDiff function in the WHERE clause. Yes, I used them, but NOT on Date column from the table. I used them with the Getdate function which is more of a constant and NOT part of the table's column.

Query Optimization Tip :

Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.

In future I'll have some more posts on Date comparison in WHERE condition. Also some more simple tricks to optimized the everyday's queries.

 

Mangal Pardeshi
SQL MVP

Script All the Stored Procedures in The Database

In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”.

But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it -

To script All the Stored Procedures in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

Similarly,

To script All the Views in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'V'

To script All the Functions in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'FN'

For scripting all Triggers small modification is required, instead of sys.objects I joined the sys.triggers with sys.sql_modules.

To script All the Triggers in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.triggers as O ON M.object_id = O.object_id

Mangal Pardeshi
SQL MVP

How To Split A Comma Delimited String

In one of my previous posts I wrote about “How to Create a Comma Delimited List”.  Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id	AllNames
1	A,B,C
2	A,B
3	X,Y,Z

And here is the expected output -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

Create Sample Data :

-- Create Table for  Sample Data CREATE TABLE Test ( ID INT, AllNames VARCHAR(100) ) GO -- Load Sample Data INSERT INTO test SELECT 1, 'A,B,C' UNION ALL SELECT 2, 'A,B'  UNION ALL SELECT 3, 'X,Y,Z' GO -- Verify the Sample Data SELECT Id, AllNames FROM Test

And here is the query for How to split a comma delimited string :

;WITH Cte AS (     SELECT         id,         CAST('<M>' + REPLACE( Allnames,  ',' , '</M><M>') + '</M>' AS XML) AS Names     FROM Test ) SELECT     ID,     Split.a.value('.', 'VARCHAR(100)') AS Names FROM Cte CROSS APPLY Names.nodes('/M') Split(a)

Acknowledgement : Well seriously don’t know.  Somewhere I came across this solution while answering / participating  on MSDN Sql Server Forums.  So credit goes to my fellow Moderators/answrers on MSDN Forums.

Actually whatever expertise I gained on T-SQL and Sql Server is by participating / answering to the posts on online forums.  I’m one of those freaks who hate to read books ;)

- Mangal Pardeshi

Create A Comma Delimited List From a Column

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

And here is the expected output -

Id	AllNames
1	A, B, C
2	A, B
3	X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table CREATE TABLE Test ( Id INT, Names VARCHAR(100) ) GO -- Load sample data INSERT INTO Test SELECT 1,'A' UNION ALL SELECT 1,'B' UNION ALL SELECT 1,'C' UNION ALL SELECT 2,'A' UNION ALL SELECT 2,'B' UNION ALL SELECT 3,'X' UNION ALL SELECT 3,'Y' UNION ALL SELECT 3,'Z' GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id             ,AllNames = SubString (( SELECT ', ' + T2.Names FROM Test as T2 WHERE T1.Id = T2.Id FOR XML PATH ( '' ) ), 3, 1000) FROM Test as T1 GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id            ,AllNames = REPLACE (( SELECT T2.Names AS 'data()' FROM Test as T2 WHERE T1.Id = T2.Id FOR XML PATH ( '' ) ), ' ', ', ') FROM Test as T1 GROUP BY Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id             ,AllNames = SubString (( SELECT '| ' + T2.Names FROM Test as T2 WHERE T1.Id = T2.Id FOR XML PATH ( '' ) ), 3, 1000) FROM Test as T1 GROUP BY Id

Mangal Pardeshi

SQL MVP

SQL Server Versions and Editions.

Lets see how to find which Sql Server Version is installed and Service Packs applied on your machine-

To know Execute

SELECT @@version

If you are on Sql Server 2000 or later use following which will give you a more elegant output -

SELECT SERVERPROPERTY('productversion') as VersioNumber
,SERVERPROPERTY ('productlevel') as SPLevel
,SERVERPROPERTY ('edition') as ServerEdition

And here is detailed chart which will show you SQL Server version numbers and the corresponding product or service pack level -

Sr No	Version Number	Release	SP Level
10.1	10.0.1600.22	SQL Server 2008	RTM
-	-	-	-
9.1	9.0.1399.00	SQL Server 2005	RTM
9.2	9.0.2047.00	SQL Server 2005	SP1
9.3	9.0.3042.00	SQL Server 2005	SP2
9.4	9.0.4035.00	SQL Server 2005	SP3
-	-	-	-
8.1	8.00.194.00	SQL Server 2000	RTM
8.2	8.00.384.00	SQL Server 2000	SP1
8.3	8.00.534.00	SQL Server 2000	SP2
8.4	8.00.760.00	SQL Server 2000	SP3
8.5	8.00.2039.00	SQL Server 2000	SP4
-	-	-	-
7.1	7.00.623.00	SQL Server 7.0	RTM
7.2	7.00.699.00	SQL Server 7.0	SP1
7.3	7.00.842.00	SQL Server 7.0	SP2
7.4	7.00.961.00	SQL Server 7.0	SP3
7.5	7.00.1063.00	SQL Server 7.0	SP4
-	-	-	-
6.51	6.50.201.00	SQL Server 6.5	RTM
6.52	6.50.213.00	SQL Server 6.5	SP1
6.53	6.50.240.00	SQL Server 6.5	SP2
6.54	6.50.258.00	SQL Server 6.5	SP3
6.55	6.50.281.00	SQL Server 6.5	SP4
6.56	6.50.415.00	SQL Server 6.5	SP5
6.57	6.50.416.00	SQL Server 6.5	SP5a
6.58	6.50.479.00	SQL Server 6.5	SP5a update
-	-	-	-
6.01	6.00.121.00	SQL Server 6.0	RTM
6.02	6.00.124.00	SQL Server 6.0	SP1
6.03	6.00.139.00	SQL Server 6.0	SP2
6.04	6.00.151.00	SQL Server 6.0	SP3

- Mangal Pardeshi