Microsoft SQL Server 2008 R2 CTP

The first community technology preview (CTP) of Microsoft SQL Server 2008 R2 available for download for MSDN and TechNet subscribers.

For more details about SQL Server 2008 R2 and for other related links See - http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx

If you have any questions regarding SQL Server 2008 R2 you can visit the forums - http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

ROLLUP and ORDER BY

In one of my previous posts, I discussed on how useful GROUPING function is while writing a ROLLUP/CUBE query.  One more way GROUPING function can help you is – in ordering the results returned by ROLLUP/CUBE queries.

The reason I’m writing this post is, sometime back I seen somebody writing a weird ORDER BY statement to get the desired ordering after writing a query using a ROLLUP operator.  First he didn’t use the GROUPING function in SELECT statement and 2nd his ORDER BY was something like :

ORDER BY CASE ColumnName             WHEN 'Total Of ColumnName'             THEN 'Zzzzzzzz'             ELSE ColumnName          END

Just to get sub total returned by ROLLUP at the bottom of result set.  Well of course if he had the knowledge about GROUPING then he wouldn’t have written such CASE statement in ORDER BY.

As you may know that GROUPING function returns 1 when the row is added by either the CUBE or ROLLUP operator, and 0 when the row is not the result of CUBE or ROLLUP.  So you can easily use this property of the GROUPING for ordering the result set.

Lets have a look at the following example, 1st create some sample data.  

-- create sample table Sales CREATE TABLE Sales ( ID INT, FName VARCHAR(30), Zone VARCHAR(30), Sale INT ) GO -- Load sample data INSERT INTO Sales SELECT 1, 'Mangal', 'East', 20 UNION ALL SELECT 2, 'Mangal', 'East', 150 UNION ALL SELECT 3, 'Mangal', 'West', 50 UNION ALL SELECT 4, 'Ram', 'East', 45 UNION ALL SELECT 5, 'Ram', NULL, 80 UNION ALL SELECT 6, 'Ram', NULL, 40 UNION ALL SELECT 7, 'Sachin', 'West', 50 UNION ALL SELECT 8, 'Sachin', 'West', 40 GO -- Test sample data SELECT Id, FName, Zone, Sale FROM Sales GO

The sample data :

ID	FName	Zone	Sale
1	Mangal	East	20
2	Mangal	East	150
3	Mangal	West	50
4	Ram	East	45
5	Ram	NULL	80
6	Ram	NULL	40
7	Sachin	West	50
8	Sachin	West	40

And here is the expected output :

FName	Zone	Total
Mangal	East	170
Mangal	West	50
Mangal	All Zone	220
Ram	East	45
Ram	Unknown	120
Ram	All Zone	165
Sachin	West	90
Sachin	All Zone	90
All Names	All Zone	475

As you can see in the expected output, all the FNames are ordered in ascending order and their total SUM is at the bottom, same for the Zone column.  For ordering the result in that way just use the GROUPING(column_name) in ORDER BY just before the column_name.  See the following query, esp the ORDER BY clause:

SELECT CASE GROUPING(fname)         WHEN 1 THEN 'All Names'         ELSE ISNULL(Fname, 'Unknown')         END AS FName,         CASE GROUPING(Zone)         WHEN 1 THEN 'All Zone'         ELSE ISNULL(Zone, 'Unknown') END as Zone,         SUM(Sale) AS Total FROM Sales GROUP BY Fname, Zone WITH ROLLUP ORDER BY  GROUPING(fname),FName,GROUPING(Zone),Zone

Simple, isn’t it?  Now you don’t need to write a CASE statement in ORDER BY, just use the GROUPING function.  If you will be doing the ORDERING in application layer, then you will need to get in the GROUPING(fname) and GROUPING(zone) column in the SELECT list as well.

Mangal

TSQL Challenges

I just like to thank Jacob Sebastian who is a fellow SQL Server MVP and founder of the www.tsqlchallenges.com for offering me an opportunity to be part of the TSQL Challenges team. I’m really happy to be a part of a team consists of people like Jacob, Alejandro Messa, Peter Larsson (all 3 are SQL Server MVPs), Adam Haines (a moderator of MSDN SQL Server forums), Rui Carvalho and many other talented people.

Here is a brief description about TSQL Challenges site: TSQL Challenges constantly aim at helping people to enhance their SET based query writing skills. With TSQL Challenges, sometimes you learn stuff that you don’t know, sometimes you will see better ways of doing stuff that you already know and sometimes you will be able to use your expertise to help others to learn TSQL querying skills. Even SQL Server experts love TSQL Challenges because every challenge inspires them to come up with new better ways of solving the given problem.

The Mission: The entire “TSQL Challenge” team will focus on fulfilling our mission; “helping people to enhance their SET based query writing skills”. We will come up with more and more interesting TSQL Challenges that encourages you to look for alternate logics and inspires you to think outside the regular thought process.

I would like to invite my readers to participate in a TSQL Challenge - www.tsqlchallenges.com

Also like to thank Jacob again for a warm welcome and kind word he has put in introduction post - Introducing new “TSQL Challenge” Team Members

So I hope I will come with up some interesting SQL puzzles that will challenge your SQL skills and also you will a fun solving them.

Mangal

SQL Server 2008 Service Pack (SP) 1 on Microsoft Update as a Required Automatic Update

SQL Server 2008 Service Pack 1 will soon be available through Automatic Update starting from September. 

For the latest information you can read it from SQL Server Setup blog - SQL Server 2008 Service Pack (SP) 1 on Microsoft Update as a Required Automatic Update

For better understanding of Automatic Update see - Update Your PC Automatically

UNION Vs UNION ALL

Many times you may have heard this “Use UNION ALL over UNION whenever possible.”  The question arises - why?  To answer this question in one statement  - UNION ALL performs faster compare to UNION. 
Then again question arises - Why UNION ALL performs faster?  Also - Why whenever possible, why not always?

Let me answer the 2nd question 1st – Though both UNION and UNION ALL combines the results of two or more queries into a single result set, there is fundamental difference between these two.  UNION returns only DISTINCT result set, while UNION ALL returns basically all rows available, this includes duplicates.

Lets see the following example:

-- create 2 tables A and B. CREATE TABLE A ( ID INT, Names VARCHAR(10) ) GO CREATE TABLE B ( ID INT, Names VARCHAR(10) ) GO -- insert data into table A INSERT INTO A VALUES(1,'Mangal'); INSERT INTO A VALUES(5,'Sham'); INSERT INTO A VALUES(2,'Ram'); -- insert data into table B INSERT INTO B VALUES(2,'Ram'); INSERT INTO B VALUES(3,'Shiv'); INSERT INTO B VALUES(4,'John'); -- test sample data SELECT id, Names FROM A GO SELECT id, Names FROM B GO

Here is how the data of the table A and B looks like :

Note that id=2 and names=Ram is there in both the tables.  That will help us in understanding the difference between UNION and UNION ALL.  Now lets execute the following 2 queries, 1st is with UNION and 2nd is with UNION ALL. –

-- with UNION SELECT id, Names FROM A UNION SELECT id, Names FROM B GO -- with UNION ALL SELECT id, Names FROM A UNION ALL SELECT id, Names FROM B GO

The result: 

Observations :
1. 1st query with UNION returns 5 rows, and UNION ALL query returns 6 rows.
2.  Row for ID=2(for Ram) appears twice in UNION ALL result set.
3. Result set for UNION is sorted on ID column.  For UNION ALL all the rows of table A appeared 1st followed by rows of table B(no sort).

As you can see, UNION eliminates any duplicate rows from final result set while UNION ALL returns basically all rows available including duplicates.  That is the cause of UNION being slow.  For each row UNION operator checks whether the entire row exists in previous rows or not.  And for making this validation UNION by default 1st sort the result set on the 1st available column of the result set.  In our example UNION has sorted the result set on ID column even though I haven’t specified any ORDER BY clause.  If you see Name “Sham” (which is in table A) appeared last in the UNION result because it has the highest id 5 while it appeared on 2nd row of UNION ALL result.  A look at the query execution plan can help you visualizing it better :

 

As you can see cost of the UNION query is 73% compare to 27% for UNION ALL.  And measure reason being the “Distinct Sort” that UNION operator performs to sort and to eliminate the duplicate rows.  While UNION ALL doesn’t really bother about sort and duplicates.  And that is why UNION is slow compare to UNION ALL. 

So again going back to question – why not use UNION ALL always?  And one more question to be added - when to use which one?

- You should  use UNION when you don’t want the duplicates in your final result set, and you are not sure (or may be you are sure) that duplicate records exists in the different queries involved in the UNION.

- You should be using UNION ALL when :
1.  You are not bothered about the duplicate rows in the result.
2.  You are sure there are no duplicates in different queries involved in UNION.  e.g. if you are combining results from 2 or more different years(sales orders) with each query reruns result for individual year with some unique id for each row.  Or combining result for 2 or more different departments.

All this long I’m talking about UNION and UNION ALL as if they are 2 different things all together.  Are they?  Not exactly.  Reason I’m saying this because, when one of my friend asked me about UNION ALL and I advised him to look into the Books online, and he came back complaining me that “books online doesn’t say anything about UNION ALL”.  Reason – he was thinking that Books online must be having some separate section dedicated to UNION ALL, as if it is different from UNION.

Actually, the ALL is just an optional argument in the UNION syntax.  For more on UNION you can refer the books online - http://msdn.microsoft.com/en-us/library/ms180026.aspx.  

Mangal

DELETE Vs TRUNCATE

I know this has been done many times, but still here is something from my side. What is the difference between DELETE and TRUNCATE?
Well one reason I wanted to write this post was, on so many of the blogs, forum threads I keep seeing statements like “TRUNCATE cannot be rolled back”. This is also one of the most frequently asked questions in Interviews.  And since my target audience is people who are just started learning SQL (and NOT SQL Experts), I thought I should write this one.
Instead of just focusing on ROLLBCAK I will try to cover all the differences between DELETE and TRUNCATE.

• Remove Data  : First thing first, both can remove the data from a table. 
  But a DELETE can be used, to remove the rows not only from a Table but also from a VIEW or the result of an OPENROWSET or OPENQUERY subject to provider capabilities.
  

• FROM Clause : With DELETE you can also delete rows from one table/view/rowset_function_limited based on rows from another table by using another FROM clause.  In that FROM clause you can also write normal JOIN conditions.  Actually you can create a DELETE statement from a SELECT statement that doesn’t contain any aggregate functions by replacing SELECT with DELETE and removing column names. 
  With TRUNCATE you can’t do that.
  

• WHERE : A TRUNCATE cannot have WHERE Conditions, but a DELETE can.  That means with TRUNCATE you can’t delete a specific row or specific group of rows.
  TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause.
  

• Performance : TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
  And one of the reason is locks used by either statements. The DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
  

• Transaction log : DELETE statement removes rows one at a time and makes individual entries in the transaction log for each row. 
  TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
  

• Pages : After a DELETE statement is executed, the table can still contain empty pages.
  TRUNCATE removes the data by deallocating the data pages used to store the table data.
  

• Trigger : TRUNCATE does not activate the delete triggers on the table.  So you must be very careful while using TRUNCATE.  One should never use a TRUNCATE if delete Trigger is defined on the table to do some automatic cleanup or logging action when rows are deleted.
  

• Identity Column : With TRUNCATE if the table contains an identity column, the counter for that column is reset to the seed value defined for the column.  If no seed was defined, the default value 1 is used.
  DELETE doesn’t reset the identity counter.  So if you want to retain the identity counter, use DELETE instead.
  

• Replication : DELETE can be used against table used in transactional replication or merge replication. 
  While TRUNCATE cannot be used against the tables involved in transactional replication or merge replication.
  
• Rollback : DELETE statement can be rolled back. 
  TRUNCATE can also be rolled back provided it is enclosed in a TRANSACTION block and session is not closed.  Once session is closed you won't be able to Rollback TRUNCATE.
  

• Restrictions : The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed. 
  And if DELETE is used against View, that View must be an Updatable view.
  TRUNCATE cannot be used against the table used in Indexed view. 
  TRUNCATE cannot be used against the table referenced by a FOREIGN KEY constraint, unless a table that has a foreign key that references itself.
  

Source :
DELETE - http://msdn.microsoft.com/en-us/library/ms189835.aspx 
TRUCNATE - http://msdn.microsoft.com/en-us/library/ms177570.aspx

I hope you liked my this post on the topic: the difference between DELETE and TRUNCATE in SQL Server.

PIVOT Multiple Columns

In one of my previous post I showed you how to UNPIVOT multiple columns.  On similar lines I also wanted to write on “How to PIVOT multiple columns?”, so this post was due for some time.  Actually I was looking for some efficient way of doing it.  Limitation of PIVOT operator is, it supports pivoting only on a single column.  But you can always have multiple PIVOT operators in the FROM clause. I was trying to create a PIVOT query with multiple columns with multiple PIVOT operators. But at the end of it I found that our old fashioned CASE expression is performing much better than a multiple PIVOT operator query. 

Even though I’m writing this post on how to write a multiple PIVOT operator query, my suggestion is use CASE expressions instead for getting better performance.  Though personally I like to avoid CASE also.   Normally I like to do it in Reporting Services, by creating a Matrix report.  Now a days almost all Reporting Tools provides you an option of creating Matrix report.  And good thing about Matrix report is unlike PIVOT operator you don’t need to hard code any column value.

If you try to write a PIVOT query with 2 PIVOT operators, and use same column in FOR clause you will get an error : Invalid column name NameOfColumn.

Or if you use same column, but by declaring it again and using a different alias name, you still get an error : The column name ValueOfColumn specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

So what’s the solution?  Solution is, declare the same column again, change the values in the column by some constant(you can add some constant, or you can concat some identifier ) and assign a new alias name to column.

Lets see the following example, I have used the AdventureWorks database of SQL Server 2005.

USE AdventureWorks GO SET ANSI_WARNINGS OFF SELECT CustomerId,         SUM([Q2001]) AS Qty2001,         SUM([Q2002]) AS Qty2002,         SUM([V2001]) AS Val2001,         SUM([V2002]) AS Val2002 FROM (         SELECT     H.CustomerId,                 SUM(D.OrderQty) AS TotalQty,                 SUM(D.LineTotal) AS TotalVal,                 'Q'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS QYear,                 'V'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS VYear         FROM Sales.SalesOrderDetail AS D INNER JOIN         Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId         WHERE D.ProductId=771         AND H.OrderDate >='20010101'         AND H.OrderDate <'20030101'         GROUP BY H.CustomerId,                 CONVERT(VARCHAR(4),H.OrderDate,120)     )Main PIVOT     (         SUM(TotalQty)         FOR QYear IN ([Q2001],[Q2002])     ) PQ PIVOT     (         SUM(TotalVal)         FOR VYear IN ([V2001],[V2002])     ) PV GROUP BY CustomerId ORDER BY CustomerId GO

The query returns total quantity and line amount for year 2001 and 2002 for the product id 771 for all customers.  If look at the query carefully in Main sub query, CONVERT(VARCHAR(4),H.OrderDate,120) this convert statement will take out the Year part from the OrderDate column.  I have declared the same column twice, at first I concatenated Q to the Year, and at second time I concatenated the V.  Just execute the Main sub query, so it will be easy to understand for you.

SELECT     H.CustomerId,                 SUM(D.OrderQty) AS TotalQty,                 SUM(D.LineTotal) AS TotalVal,                 'Q'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS QYear,                 'V'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS VYear  FROM Sales.SalesOrderDetail AS D INNER JOIN  Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId  WHERE D.ProductId=771                  AND H.OrderDate >='20010101'                  AND H.OrderDate <'20030101'  GROUP BY H.CustomerId,                 CONVERT(VARCHAR(4),H.OrderDate,120)

Now we have 2 columns, with different values, and we can use them in different PIVOT with same effect, and that’s what I have done in my 1st query.

Here is a CASE expression version of same query, which gives much better performance if you scale it for large amount data.

USE AdventureWorks GO SELECT     H.CustomerId,                SUM(CASE YEAR(H.OrderDate)                       WHEN 2001                       THEN D.OrderQty                       END) AS Qty2001,                SUM(CASE YEAR(H.OrderDate)                       WHEN 2002                       THEN D.OrderQty                       END) AS Qty2002,               SUM(CASE YEAR(H.OrderDate)                      WHEN 2001                      THEN D.LineTotal                      END) AS Val2001,               SUM(CASE YEAR(H.OrderDate)                      WHEN 2002                      THEN D.LineTotal                      END) AS Val2002 FROM Sales.SalesOrderDetail AS D INNER JOIN Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId WHERE D.ProductId=771           AND H.OrderDate >='20010101'           AND H.OrderDate <'20030101' GROUP BY H.CustomerId ORDER BY H.CustomerId GO

You can test the performance of both queries.  If you want to scale it for larger data you can remove the WHERE conditions added by me.  Total execution time for CASE query is almost half to that of PIVOT query. 

TCP Port of SQL Server

By default the default TCP Port for SQL Server is 1433.  It can be changed for security purpose to minimize the potential threat of a hacker trying to access through the default port number.  But that is whole together a different story.

Every now and then somebody posts this question on forums- how can I tell what port my SQL Server instance is running on?  As sometimes while accessing SQL Server remotely you may need to add the TCP Port in the connection string.

There are more than 1 way to find out the answer.  From SQL Server Error Logs, Configuration Manager, Command Prompt, Registry Editor.   But today I will share a simple T-SQL script with you, from which you can easily find out the TCP port SQL Server instance is running on.

First lets see in regedit where to look for the Port number. 
The location of port number for SQL Server 2005 -

For default instance : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

For Named Instance :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ [Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp

If you don’t know how to open the regedit :
1. Click on Start and then on Run.
2. In run type regedit and click on Ok.

After checking out the port number manually now lets see the query I created.  It uses a non-documented extended stored procedure xp_regread.  Basically it is helpful in reading from the registry via Transact-SQL.  Since it is a non-documented extended stored procedure you won’t find any reference about xp_regread in Books Online. If you want to know more about it, best thing you can do is Google for xp_regread.  So here is the script which will give you the TCP port SQL Server is running on :

DECLARE @TcpPort VARCHAR(5)         ,@RegKey VARCHAR(100) IF @@SERVICENAME !='MSSQLSERVER'     BEGIN         SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'     END     ELSE     BEGIN         SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'     END EXEC master..xp_regread     @rootkey = 'HKEY_LOCAL_MACHINE'     ,@key = @RegKey     ,@value_name = 'TcpPort'     ,@value = @TcpPort OUTPUT SELECT @TcpPort AS PortNumber         ,@@SERVERNAME AS ServerName         ,@@SERVICENAME AS ServiceName

You will get the output something like this :

Other simple way of finding out the TCP port is from the Configuration Manager.
1. Start >> All Program >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server Configuration Manager .
2. In the configuration manager click on SQL Server 2005 Network Configuration.
3. Then click on the Instance name for which you want to find out the Port Number.
4. Double click on TCP/IP.
5. Finally on IP Addresses, and you will see something like :

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.

Multiplying Column Values

Well we all know how to add the values in a column using SUM and GROUP BY.  But what about multiplying the column values?  And also how to calculate running multiplication on a column similar to Running Totals?

Sometime back I came across this interesting question on MSDN SQL Server forums - How to multiply all values in a table column?

I don't know the reason of such requirement, but very interesting problem though.  Of course there is no such built-in function in SQL Server to do it for you.  But after thinking on it for few minutes, I realize that if you still remember the basics of Mathematics, it is not that difficult to do in T-SQL.

If you remember

A * B = ANTILOG ( LOG (A) + LOG(B) )

So looking at it again, it is very straight forward using LOG, ANTILOG and SUM in SQL Server. (For doing ANTILOG you have EXP function in SQL Server.) Let me show you how :

Lets create some sample data first.

-- Create sample table CREATE TABLE Scores ( ID INT IDENTITY, Category VARCHAR(1), ) GO -- Load sample data into table INSERT INTO Scores(Category, Score) SELECT 'A', 4 UNION ALL SELECT 'A', 5 UNION ALL SELECT 'A', 2 UNION ALL SELECT 'B', 5 UNION ALL SELECT 'B', 5 UNION ALL SELECT 'B', 2 UNION ALL SELECT 'B', 3 GO -- test the sample data SELECT Id, Category, Score FROM Scores GO

The sample data :

ID	CATEGORY	SCORE
1	A	4
2	A	5
3	A	2
4	B	5
5	B	5
6	B	2
7	B	3

Multiplying Column Values:  And here is the query to calculate the Total Product of Score Column for each Category.

SELECT Category, SUM(Score) as TotalSum,EXP(SUM(LOG(Score))) as TotalProduct FROM Scores GROUP BY Category

Result :

Category	TotalSum	TotalProduct
A	11	40
B	15	150

Second column you have normal total SUM for the Score column and in 3^rd column you have the total product of the Score column for each Category.

Calculating The Running Multiplication:  After multiplying the column values, next question is how to calculate the Running Product similar to Running totals? See the following table -

ID	Category	Score	RunningProduct
1	A	4	4
2	A	5	20
3	A	2	40
4	B	5	5
5	B	5	25
6	B	2	50
7	B	3	150

The logic is similar to what I just mentioned for Multiplying Rows and adding the Running Total logic to query.

SELECT O.Id, O.Category, O.Score         ,(  SELECT EXP ( SUM ( LOG (I.Score) ) )              FROM Scores I              WHERE I.Category = O.Category              AND I.Id <= O.Id          ) as RunningProduct FROM Scores O

But remember this solution only works for positive numbers, if column contains negative values then you will need to modify the query using ABS function and taking into account number of negative values.  Well but at least you will get a start from this post.

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