Menu
begin transaction;
create table person_id(person_id integer primary key);
insert into person_id values(1);
... snip ...
insert into person_id values(50000);
commit;
This code takes about 0.9 seconds on my machine and creates a db file taking up 392K. These numbers become 1.4 seconds and 864K if I change the second line to
create table person_id(person_id integer nonclustered primary key);
Why is this the case?
306
It's more efficient to create a clustered primary key.
A clustered index is used to define the order or to sort the table or arrange the data by alphabetical order just like a dictionary. A non-clustered index collects the data at one place and records at another place. It is faster than a non-clustered index. It is slower than the clustered index.
By default a primary keys gets to be the clustered index key too, but this is not a requirement. The primary key is a logical concept: is the key used in your data model to reference entities. The clustered index key is a physical concept: is the order in which you want the rows to be stored on disk.
A clustered index may be the fastest for one SELECT statement but it may not necessarily be correct choice. SQL Server indices are b-trees. A non-clustered index just contains the indexed columns, with the leaf nodes of the b-tree being pointers to the approprate data page.
A cluster index is a type of index that sorts the data rows in the table on their key values, whereas the Non-clustered index stores the data at one location and indices at another location.
If Primary Keys are good at uniquely identifying each row in a data table, Clustered Indexes are good at finding the specific rows quickly. A Clustered Index actually sorts the table using the values in the specified column.
A great answer to this question is available over at the DBA StackExchange: https://dba.stackexchange.com/questions/7741/when-should-a-primary-key-be-declared-non-clustered/7744#7744
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
