July 2, 2010 at 9:07 am
The obvious answer is one, but there is something more fundamental in this. This question is even discussed at PASS and many important SQL Server interviews. I am humble enough to accept I dont know the detailed explanation. Could someone shed light on this ?
July 2, 2010 at 9:11 am
http://www.sqlservercentral.com/questions/Indexes/68595/
Number of Clustered indexes in SQL 2005 is one and 249 non clustered indexes, altogether 250 indexes for a table in SQL Server 2005. In SQL Server 2008, the maximum is 1000.
SQL DBA.
July 2, 2010 at 9:18 am
The answer IS one. Each table can only have one in SQL Server.
The underlying reason for it is that the clustered index becomes the logical storage order (logical in that this doesn't affect the physical order of data pages, etc...). In short - the entire table "lives" inside of that clustered, index, so there can be only one.
Not the most technical answer, but it sounds like you've already heard the technical end, so I qwas trying to explain in in a different way.
There's a fair amount of good info about this topic here if you DO want the technical answers:
http://msdn.microsoft.com/en-us/library/ms190639.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 2, 2010 at 9:46 am
okay, my question was not how many NCIs are possible on a table... so i have to ignore Sanjay;s answer.
July 2, 2010 at 9:48 am
Matt, I agree that the clustered index IS the table...
July 2, 2010 at 10:46 am
Even I have had this doubt for a sometime now... Have seen different views in blogs more than once on this topic... The obvious answer may be 1 but I think there is a catch somewhere... Attached is the screen shot of a blog with a different view...
It would be great if someone could help here 🙂
Thanx in Advance !
July 2, 2010 at 10:56 am
Sanz (7/2/2010)
Even I have had this doubt for a sometime now... Have seen different views in blogs more than once on this topic...
Then those blogs are wrong. There can be only one clustered index per table. Period.
The blog you've attached just implies that lots of people don't know that there can only be one. That doesn't mean that there can be more, it means those people don't understand what a clustered index is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2018 at 5:27 pm
A well designed table should have a primary key index and if the table uses surrogate keys then the table should have one or more unique key indexes/constraints .. The use of when surrogate keys should or should not be used is another discussion !
The terminology and DDL syntax relating to "Clustered index" is just a Microsoft and MySQL thing I am thinking?
(seems from above discussion that a MS SQL Server table can have just one clustered index and that it relates to table data storage.)
The database table user isn't going to care too much about index implementation so long as desired performance gains are achieved so I wondered why Microsoft distinguished this term Clustered ... ?
Seems MySQL has this terminology also. i.e. see this reference:
http://www.mysqltutorial.org/mysql-index/mysql-clustered-index/
https://www.w3resource.com/sql/creating-index/sql-creating-index.php ... says ANSI SQL standard does not include index creation syntax.
See also:
https://www.w3schools.com/sql/sql_primarykey.asp
Oracle does not have a clustered index syntax as per this discussion
https://dba.stackexchange.com/questions/129504/how-to-create-clustered-and-non-clustered-index-in-oracle
but Oracle does have something called an "index-organized table"
Another discussion on this topic (which conflicts with some previous discussions where by it states the clustered index not same as primary key)
https://stackoverflow.com/questions/8333013/sql-server-creating-table-with-clustered-index-without-a-primary-key
Can try some table and index constraints creation variations .. to see what is allowed and what gives error messages ...
use foraldb;
go;
CREATE TABLE dbo.myTable (
myTableId int PRIMARY KEY NONCLUSTERED,
myColumn int NOT NULL
)
CREATE CLUSTERED INDEX myIndex ON dbo.myTable(myColumn)
CREATE TABLE MyTable2 (
ID uniqueidentifier NOT NULL,
UserID uniqueidentifier NOT NULL,
EntryDate DATETIME NOT NULL,
CONSTRAINT PK_MyPrimaryKey_Name PRIMARY KEY NONCLUSTERED (ID),
CONSTRAINT UCI_MyClusteredIndexName UNIQUE CLUSTERED (UserID ASC,EntryDate ASC,ID ASC)
);
CREATE CLUSTERED INDEX myIndex234 ON dbo.MyTable2(ID);
An example error message from the last line of code above, upon trying to create a second clustered index on a table is as per this:
Msg 1902, Level 16, State 3, Line 18
Cannot create more than one clustered index on table 'dbo.MyTable2'. Drop the existing clustered index 'UCI_MyClusteredIndexName' before creating another.
I noted that the Lynda.com training course "Developing Microsoft SQL Server 2016 Databases" has a topic/module on this also: https://www.lynda.com/SQL-Server-tutorials/Clustered-indexes/534417/570931-4.html
- [Instructor] The primary index for a table…is called the clustered index.…When a clustered index is applied to a data table…the rows in the table get sorted and stored…based off of the clustered indexes key value.…A clustered index is what converts a heap…into an orderly arrangement that allows record seeking…rather than just full table scans.…In essence, clustered indexes not only define…the sorting order for a table,…they are the table.…This is why there can only be a single…clustered index per table,…as there's only one way that the table…will naturally be stored on disk.…
October 29, 2018 at 2:09 am
Please note: 8 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply