-2

I don't have much experience or knowledge about SQL. I got assigned a project which required creating a database thought. I just finished it and generated the SQL code to create the database via Oracle Data Builder. In the script I can see that most tables use all primary keys from each table as unique non-clustered index. I never heard of SQL indexes so I googled it and now I have an idea but still am quite confused. Is it common to use primary keys as non-clustered indexes? Also I read that the SQL Server automatically uses primary keys as clustered index. Can they be both? Could you please give me an example of scenario when to use clustered or non-clustered indexes so I can evaluate if it makes sense for my database? I would appreciate any help. Thanks

Edit: To clarify the database will be used to store data about production and states of different machines during shifts. Each of the 20 tables has 4 PK (usually date, shift, time, machine_id) at average and the database is expected to work with a lot of data as it is storing a lot of information from each shift each day on different machines. Also the client will be gathering data from all the tables into one report that will be either shown to the user or exported to CSV file.

Jetamo
  • 11
  • 2
  • It is not common to use nonclustered indexes for primary keys in SQL Server (since the clustered index is the default), however, for example in Oracle the default is nonclustered index for a primary key. – Radim Bača Jul 28 '20 at 09:56
  • 2
    Does this answer your question? [When should a primary key be declared non-clustered?](https://dba.stackexchange.com/q/7741/140734) – Thom A Jul 28 '20 at 10:00
  • Does this answer your question? [How to choose the clustered index in SQL Server?](https://stackoverflow.com/questions/2267326/how-to-choose-the-clustered-index-in-sql-server) – SMor Jul 28 '20 at 12:42
  • Someone without experience or knowledge shouldn't be designing databases without help and guidance - it's that simple. I also don't think it is a good sign that you use an Oracle product to implement a MS SQL Server database. Now would be a good time to introduce code reviews (in this case, schema reviews) to leverage the knowledge of others, gain knowledge yourself, and document your schema and the reasons driving your decisions. – SMor Jul 28 '20 at 12:51
  • and beware - "shift work" is always a problematic concept. – SMor Jul 28 '20 at 12:51

1 Answers1

0

By default SQL Server uses primary key as a clustered index key, but in some cases the DBA would change it, so it is possible to use primary key as non-clustered index key. If your case is normal usage, first scenario is better for performance reasons.

Check it: https://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/

Dale K
  • 25,246
  • 15
  • 42
  • 71
nimajv
  • 423
  • 3
  • 11
  • Its rather more complex than that - the link posted by Larnu gives much better detail. Your post doesn't even mention uniqueidentifier primary keys for example. – Dale K Jul 28 '20 at 10:08