June 18, 2013 at 10:00 pm
Hi,
Have a small doubt - what's the difference between the below 2 SQL statements.
1. Create Table T1 (Col1 Int PrimaryKey, Col2 Int)
2. Create Table T2 (Col1 Int, Col2 Int)
- Create Unique Clustered Index T2(Col1)
Question - in the above 2 created tables, what's the difference between T1-Col1 and T2-Col1. Do they both behave same when created in the db or are they different?
Regards,
Sai Viswanath
June 19, 2013 at 12:52 am
The first creates a primary key, the second creates a unique clustered index. They're two different structures. Mostly SQL uses them in a similar way, but there are places where a primary key is needed and anything else is not good enough (transactional replication being an example)
Primary keys are a logical database modelling concept, they're designed before the architect converts the database design to physical. Indexes, clustered or nonclustered are physical database modelling concepts, they won't be designed as part of the database logical model.
Also, the clustered index is nullable, the primary key is not.
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
June 20, 2013 at 11:22 am
Hi,
GilaMonster gave a very professional answer. As I think you are a beginner I add some aspects on using a very low level explanation:
Clustered Index:
A table is stored on pages, the pages are stored on the disk. The clustered index defines the sort order in which the pages are stored on the disk.
In your example the table T2 is ordered by column Col1, the pages are sorted on the disk this way.
A clustered index can or cannot be unique.
The primary key is a constraint. When adding a primary key, SQL server realizes this using an unique index. Execute the statement sp_helpindex T1 and you will see there is an index with the same name as the primary key constraint.
The index that is used for the primary key can or cannot be clustered. I'm not sure about the standard configuration, but to be sure you're statement would have to use the appropriate clause:
Create Table T1 (Col1 Int PrimaryKey (NON)CLUSTERED, Col2 Int)
As far as the behaviour concerns: if the primary key was created as clusterd (as I said, I'm not sure about the standard) the tables will behave the same.
The tables will behave the same as far as values in Col1 will have to be unique.
But, when defined as a primary key, you MUST define Col1 as not nullable.
For performance: there is much difference between a clustered and a nonclustered index. For this have a look at books online.
Hope this helps 🙂
June 20, 2013 at 11:31 am
WolfgangE (6/20/2013)
A table is stored on pages, the pages are stored on the disk. The clustered index defines the sort order in which the pages are stored on the disk.In your example the table T2 is ordered by column Col1, the pages are sorted on the disk this way.
Not quite. A clustered index defines the logical order for the pages in the table. The pages may be stored on disk with physical order the same as logical, or with physical order different to logical. The clustered index guarantees the logical order, not the physical order
(and yes, I know Books Online says it defines the physical order, Books Online is wrong)
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
June 20, 2013 at 11:42 am
another lesson learned 🙂
June 20, 2013 at 10:38 pm
Hi,
Thanks for the explanation, and reason for asking this question is - the db on which am working has tables without any PK defined, but either a single or group of columns are defined as Unique & Clustered.
Regards,
Sai Viswanath
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply