I have a table with just one (nvarchar) column for tracking IDs that have been used, to ensure they aren’t duplicated. Should I use Clustered or Non-clustered Index for this? The only two operations on this table will be 1) to insert a new id and 2) to check if an id already exists in this table.

I have done some research and I understand that a clustered index sorts the table in the order of the index whereas NCI is just a pointer to the row but I'm still not sure which to use when there is just one column.

  • Which DBMS are we talking about? The term "clustered index" might mean different things in different products. – mustaccio Sep 18 '18 at 13:22
  • 1
    I would use a clustered index (assuming that you are using MS SQL Server). specify a low fill factor (70/80%?) if you are inserting ID's out of order. But if the ID's are sequential then you should use a higher fill factor. – Jonathan Fite Sep 18 '18 at 13:29
  • Sorry @mustaccio Microsoft SQL Server 2012+ – Caltor Sep 18 '18 at 13:56
  • 1
    Thanks @JonathanFite. Do you want to post that as an answer. – Caltor Sep 18 '18 at 13:57
  • 1
    Nonclusters use a pointer. They are logical structures. Clustered are physically structured. If you care about the order, use a Clustered Index. If you do not, use the nonclustered index. – clifton_h Oct 1 '18 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.