SQL Myth: Primary Key and Clustered Index

After remaining quiet for almost a year I’m back with what I enjoy the most, talking about SQL and sharing whatever little knowledge I have.   Many SQL Developers have this misconception:  “Primary key => Clustered Index: Only a Clustered Index can exist on a Primary key column”.     On numerous occasions I had tough times explaining that this is not the case every time, you can create a Non-Clustered Index on a primary key column.  But if this hot discussion is going on across a coffee table and I’m away from Computer I get helpless.  So finally I decided to write about this.

You can create a Non-Clustered Index on primary key column.   Or if I try to put this in Myth Buster words “A primary key column can exist/survive without a Clustered Index”  Yes it is a fact that PRIMARY KEY constraints default to CLUSTERED Index.   But it doesn’t mean that you CAN’T create a non-clustered index on a Primary Key Column.  And also you can create a Clustered Index on a non-primary key column.  Let me show you this with some simple examples. 

Case 1:  1st lets see what happens when you specify only PRIMARY KEY and nothing else.  In this case YES, by default Clustered Index will be created on Primary Key Column.

USE tempdb GO CREATE TABLE MyTable1 ( Id INT PRIMARY KEY, Dates DATETIME ) GO

You can see as expected a clustered Index got created on ID column.

Case 2:  But by just adding a NONCLUSTERED word in front of primary key you can tell SQL Server to create a Non Clustered Index instead of a default Clustered one.

Here you go:

USE tempdb GO CREATE TABLE MyTable2 ( Id INT PRIMARY KEY NONCLUSTERED, Dates DATETIME ) GO

As you can clearly see in the image a non-clustered index got created on ID column which is also a primary key.

Case 3:  Now here is the small trick, you can force SQL Server to create a non-clustered index on a primary key column even without writing NONCLUSTERED in front of it.  Yes, there is exception to the rule   “PRIMARY KEY constraints default to CLUSTERED Index” even if you don’t specify NONCLUSTERED.   Question is, How?  Well by simply creating a Clustered Index on another column while creating the table.

USE tempdb GO CREATE TABLE MyTable3 ( Id INT PRIMARY KEY, Dates DATETIME UNIQUE CLUSTERED ) GO

See the Image, a Non Clustered got created on ID column and a Clustered on Dates column.

The obvious question will be, why SQL Server didn’t create the Clustered on Id column this time?  Answer is very simple, if you know the basic rule “You can have only one Clustered Index on a table”.  And since in the CREATE statement you forced SQL Server to create a clustered index on dates columns SQL Server had no choice but to create a non clustered on Id column.

What we learned today?
Honestly speaking, I didn’t tell anything new.  Experts/people with good knowledge about SQL Server already knew this.  But interesting thing we can learn here – yes there are some DEFAULTs set by SQL Server, but that doesn’t stop you from telling SQL Server “Boss enough of your DEFAULTs, now let me take the control”.  Actually in early days of learning SQL we all get into  this habit of relying on DEFAULTs set by SQL Server.  And we get so used to them that we start considering them as RULES that can’t be broken.

Actually I think there is no harm in taking little bit extra effort and writing some extra keywords and telling SQL Server this is what I want or this is what is expected.

You also learned how to create a Clustered Index on a column of your choice.  This can be very useful when you don’t want a Clustered Index on a primary key especially in cases like where you are using GUID as a primary key(I hate them) and you want Clustered Index to be created on some other column.

Namaste!
- Mangal.

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