September 9, 2004 at 10:29 pm
What is the difference between the two? How about their performance in retrieving the data? Is clustered index consume more space than creating it with the primary key?
September 10, 2004 at 1:12 am
Strange question to be posted in the Backups group ....
Anyway, a primary key is a primary key (unique). A clustered index means the physical order of the data is sorted using the keys in the index. A clustered index may be created on either the primary key, or on another index. Obviously, you can only have one clustered index per table, since the data can only be physically ordered one way.
In terms of space, you can't quite compare them. If your primary key was clustered, it will contain the data in the table in its structure. If it was not clustered, it will just contain the index structure and also the clustered index keys (assuming there is a clustered index on the table) at the leaf levels.
As regards performance, there are quite a few things to consider, but the general rule is that a good index to be clustered is one where you frequently query on a range of the index keys e.g. WHERE ID between 10 AND 100. This is fast because the data is already sorted in that order, logically and physically.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 10, 2004 at 1:57 am
Over and above the previous answer
A Primary key cannot contain nulls.
A unique index can contain a single null.
A clustered index holds its data in the leaves of the index. You can only have one clustered index per table.
A non-clustered index holds pointers to the data int he leaves of the index.
September 10, 2004 at 3:08 am
>> A non-clustered index holds pointers to the data int he leaves of the index.
And to clarify a little more, if a table does not have a clustered index, this 'pointer' will be a row id to the actual data.
In SQL 2000, if the table has a clustered index, this 'pointer' is the clustered index keys. The larger your clustered index keys are, the larger will be your non-clustered indexes.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply