TOP 1 WITH TIES

TOP Enhancements
Structured error Handling
Except And Intersect
Where How many Times you want to GO Today..!
SOME, ANY, ALL are NOT new Features
AdventureWorks OLTP Database
Ranking and Windowing Functions
DML With OUTPUT
New APPLY Operator
MAX Sized datatypes
Sampling Using TABLESAMPLE

BUG: Combination of GROUP BY with HAVING Clause and LEFT OUTER JOIN with Derived Table With LEFT Function Produces Incorrect Result

Named Constraints on Temporary tables

Common (TOP 10?) T-SQL Programming mistakes

Path Enumeration Using Prime Number Products

Difference between Table Variable and Temporary Table

Difference Between SET and SELECT

Let Us Count them!

Differences between COALESCE and ISNULL

Fancy Scoping

SQL Server 2005 ships with four functions exclusively for ranking and windowing operations. These functions will make the life of tthe T-SQL programmer easy. The following are the four functions.

1. ROW_NUMBER()

This function allows you to provide a pseudo column containing sequential integer values to result rows of a query, which makes many tasks like paging easy. Before 2005, to get the same result, you had to write complex queries like,


use pubs
GO

Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc



Lets see how easy its in 2005


CREATE TABLE #Students(StudentName VARCHAR(10), subject VARCHAR(10), Mark int)
INSERT INTO #Students VALUES('Jaison','.Net',90)
INSERT INTO #Students VALUES('Senthil','.Net',70)
INSERT INTO #Students VALUES('Joe','.Net',50)
INSERT INTO #Students VALUES('Jaison','SQL',70)
INSERT INTO #Students VALUES('Senthil','SQL',90)
INSERT INTO #Students VALUES('Joe','SQL',80)

--SELECT * FROM #Students

SELECT ROW_NUMBER() OVER (ORDER BY Mark DESC) as rownum, * FROM #Students


And here is the output.
rownum StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
2 Senthil SQL 90
3 Joe SQL 80
4 Senthil .Net 70
5 Jaison SQL 70
6 Joe .Net 50


Note that in the above example we have 2 ties, for marks 90 and 70. The ROW_NUMBER() break ties and assign a unique integer number to each row. If the column specified in the ORDER BY clause is not unique, then the ROW_NUMBER() function is not deterministic. That is the assignment of sequential values for tied rows may vary from execution to execution.

2. RANK()
The Rank function is similiar to ROW_NUMBER function in the sense that Rank() also produces a ranking column. But the difference is that the Rank function does not break ties. It assigns the same value to the tied rows. Lets see Rank() in action.


SELECT RANK() OVER (ORDER BY Mark DESC) as rank, * FROM #Students


rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
3 Joe SQL 80
3 Ann SQL 80
5 Senthil .Net 70
5 Jaison SQL 70
7 Joe .Net 50

Noe that the result has ties and gaps.

3. DENSE_RANK()
The DENSE_RANK() function is same as RANK() except that it doesnt leave gaps.


SELECT DENSE_RANK() OVER (ORDER BY Mark DESC) as rank, * FROM #Students


rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
2 Joe SQL 80
2 Ann SQL 80
3 Senthil .Net 70
3 Jaison SQL 70
4 Joe .Net 50

4.NTILE()

NTile(n) will evenly divide all the results into approximately even pieces based on the input argument, and assign each piece by the same number in the result set. For eg, if you want to assign grades to students based on their mark you can use NTILE function like


SELECT NTILE(2) OVER (ORDER BY Mark DESC) as grade, * FROM #Students


grade StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
1 Senthil SQL 90
1 Joe SQL 80
1 Ann SQL 80
2 Senthil .Net 70
2 Jaison SQL 70
2 Joe .Net 50


PARTITION BY
Ranking functions can be used for windowing with the PARTITION BY Clause. PARTITION BY clause lets you apply ranking functions for subgroups. For eg. if you want to get the ranks in the Subject Level, you canget that by using the PARTITION BY clause along with the ORDER BY Clause.


SELECT RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as rank, * FROM #Students


Here is the result.

rank StudentName subject Mark
-------------------- ----------- ---------- -----------
1 Jaison .Net 90
2 Senthil .Net 70
3 Joe .Net 50
1 Senthil SQL 90
2 Joe SQL 80
2 Ann SQL 80
4 Jaison SQL 70

You can apply the PARTITION BY Clause on all the ranking functions.


SELECT
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY Mark DESC) as rownum,
RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as rank,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY Mark DESC) as dese_rank,
NTILE(3) OVER (PARTITION BY subject ORDER BY Mark DESC) as grade, * FROM #Students



Here is the result.

rownum rank dese_rank grade StudentName subject Mark
-------------------- -------------------- -------------------- -------------------- ----------- ---------- -----------
1 1 1 1 Jaison .Net 90
2 2 2 2 Senthil .Net 70
3 3 3 3 Joe .Net 50
1 1 1 1 Senthil SQL 90
2 2 2 1 Joe SQL 80
3 2 2 2 Ann SQL 80
4 4 3 3 Jaison SQL 70


For more info on ranking functions, see these articles(1, 2) by SQL MVP Itzik Ben Gan.

The samples used in these notes use the new AdventureWorks database. You can download the adventureworks databas diagram from this link.

This more of a Whats not new rather than Whats NEW.

I had seen many authentic links like this and even the book Introducing Microsoft SQL Server(TM) 2005 for Developers saying that SOME, ANY, ALL are new T-SQL extensions in SQL Server 2005. But the fact is that they are present in SQL Server 200 also.

See the below code samples, executed from Query Analyser, using the pubs database, in SQL Server 2000


USE PUBS
GO

SELECT * FROM Titles WHERE Type = 'business'
AND price > SOME(SELECT Price FROM Titles WHERE Type = 'psychology')

SELECT * FROM Titles WHERE Type = 'business'
AND price > ANY(SELECT Price FROM Titles WHERE Type = 'psychology')

SELECT * FROM Titles WHERE Type = 'business'
AND price > ALL(SELECT Price FROM Titles WHERE Type = 'psychology')

Where How Many Times you want To GO Today !

This is about a really INTERESTING SQL server 2005 feature.

You can execute a transact sql batch any number of times just by Giving the number with GO.

See Go in action here

PRINT 'Hai.. How many times you expect to see this message..?'
GO 10

And here is the output.

Beginning execution loop
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Hai.. How many times you expect to see this message..?
Batch execution completed 10 times.


Thats COOL right?

Okay, Lets start at the Top. (Afterall this blog is named TopOneWithTies.) The Good news is that, in SQL Server 2005, you can specify a variable as the argument for the Top clause. Wait, not just a variable, you can specify a sub query or a UDF as an argument for the TOP clause.

Also you can specify the TOP clause NOT just with the select, but also with INSERT, DELETE and UPDATE.

Sounds great right!. Lets see TOP in action.


USE AdventureWorks
GO
DECLARE @n int
SET @n = 5
SELECT TOP(@n) * FROM Sales.SalesOrderHeader

Now lets try to use a User Defined function as the input for TOP.

CREATE FUNCTION dbo.MyFunc()
RETURNS INT
AS
BEGIN
RETURN 3
END
GO

SELECT TOP(dbo.MyFunc()) * FROM Sales.SalesOrderHeader

And a subquery,

SELECT TOP ((SELECT COUNT(*) FROM Production.Product)) * FROM Sales.SalesOrderHeader

Now lets try to run some INSERT, UPDATE, DELTE Statements with the TOP Clause.


CREATE TABLE #TopSales(SalesOrderId int, OrderDate datetime)

INSERT TOP (5) INTO #TopSales
SELECT SalesOrderId, OrderDate
FROM Sales.SalesOrderHeader
ORDER By TotalDue DESC

SELECT * FROM #TopSales

UPDATE TOP (2) #TopSales
SET OrderDate = OrderDate +1

SELECT * FROM #TopSales

DELETE TOP (2) FROM #TopSales

SELECT * FROM #TopSales


Note that in case of UPDATE and DELETE you cannot specify an ORDER BY Clause (It seems).
So use this option carefully (for eg. Splitting the update of large no of rows into batches) as you cannot predict which rows will get affected. (I may have to do some further research on this)

It was a common question in SQL server newsgroups that how one can implement EXCEPT and INTERSECT set operations in T-SQL. The answer is, When NULLs are not involved, you can achieve the INTERSECT effect by using INNER JOIN and except effect using NOT EXISTS or LEFT JOIN with a NULL check.

But When NULLS are present in data, the above methods will fail because, INTERSECT treats NULLs as equal for matching rows. This NG thread by SQL MVP David Portas discuss some some full equivalents. Also, SQL MVP Itzik Ben Gan has an excellent article on Mimicking INTERSECT and EXCEPT in T-SQL

But those are the days when EXCEPT and INTERSECT were not part of the T-SQL. SQL Sever 2005 adds both these set operators to the language, to make it more powerful.

See this link for for a list of Relational Operators

INTERSECT

INTERSECT is one of the the basic relational operators. INTERSECT works on two sets. It returns rows exists in both sets eliminating duplicates.

INTERSECT ALL also returns rows existis in both the inputs, but if a row repeat n times in the first input and m times in the second input, then the output will contain n or m number of the same row, whichever is less.
Note that both the set should be UNION compatible, that is the number of columns should be same etc..

The ALL version of the INTERSECT is NOT Currently supported in SQL Server 2005.

Enough theory, its time to see the new INTERSECT Operator in action

CREATE TABLE #Skills(Programmer VARCHAR(20), Skill CHAR(3))

INSERT INTO #Skills VALUES('Jaison','NET')
INSERT INTO #Skills VALUES('Roji','NET')
INSERT INTO #Skills VALUES('Roji','SQL')
INSERT INTO #Skills VALUES('Srinivas','SQL')


SELECT * FROM #Skills

Lets get the list of Programmers who has both SQL server and .Net skills

SELECT Programmer FROM #Skills WHERE Skill='NET'
INTERSECT
SELECT Programmer FROM #Skills WHERE Skill='SQL'

Here is the output

Programmer
--------------------
Roji

(1 row(s) affected)

EXCEPT

EXCEPT, also called MINUS returns DISTINCT rows from the LEFT input, which is not present in the RIGHT input.
With EXCEPT ALL, if a row appears more times in the LEFT set than the RIGHT set, then the row will appear in the output n number of times, where n is the number of times the row appear in the left set minus the number of times the row appears in the right set. The ALL version of the EXCEPT is NOT Currently supported in SQL Server 2005.

Here is the EXCEPT version of our above query

Lets get the list of programmers who knows .NEt, but doesnt know SQL.

SELECT Programmer FROM #Skills WHERE Skill='NET'
EXCEPT
SELECT Programmer FROM #Skills WHERE Skill='SQL'

Here is the output.

Programmer
--------------------
Jaison

(1 row(s) affected)

Its time to to forget the @@Error Global variable. SQL Server 2005 Provides you the power of structured Error handling as you do in any .Net Language, Which eliminates the need for checking IF @@ERROR > 0 ...

Now lets just create a table

CREATE TABLE #Student(StudentID int PRIMARY KEY, StudentName VARCHAR(50))

And try the following.

BEGIN TRY
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
END TRY
BEGIN CATCH
PRINT 'Insert Failed'
END CATCH

When you run the above code for the first time you will get the message 'Successfully Inserted'. When you try the code for the second time, You will get the message saying 'Insert failed', which means that our Catch block executed.

You can see that, when a statement within the TRY block fails, control is passed to the first statement within the catch block.

In the catch block you can use the new ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY() and ERROR_STATE() functions to get the details of the error. For eg. you can modify the above code as follows to get the details of the Error.

BEGIN TRY
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
END TRY
BEGIN CATCH
PRINT 'Insert Failed'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH

TRANSACTIONS and Error Handling
The new XACT_State function will come handy when you are using explicit transactions in the Try block. The XACT_STATE() function returns the transaction state. The possible values are -1,0 and 1.

-1 : Means that a transaction is open, but in a doomed (uncommitable) state. This typically happens when an error occutred, which has a severity level higher than 17.
0 : No transactions are open
1 : a transaction is open and commitable.

The following code shows the usage of XACT_STATE() function.

BEGIN TRY
BEGIN TRAN
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT XACT_STATE()
ROLLBACK TRAN
PRINT XACT_STATE()
PRINT 'Insert Failed'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH

Note that the error will not be propogated to the application from the try block, unless you explicitly throw the error using RAISERROR, as in the following code segment.


BEGIN TRY
BEGIN TRAN
INSERT INTO #Student(StudentId, StudentName)
VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR('Insert Failed',16,1)
END CATCH

For more information see the following MSDN article.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp