1

I have read few posts and articles about NEWID() in MS SQL. Before I decide should I use this method or not I would like to get some information. My Single Page App has few tables. One of the tables should store unique key for each customer. I'm wondering if I should use NEWID() also how I should store that id in the table? I was looking over dataypes and there is unique-identifier type. Few articles mentioned that I will have potential problems with performance especially if I would be joining over 100k to some other tables. I will have this scenario where I would have to join these records to different tables. If anyone can provide some answers or suggestions that would be great. Thanks in advance!

Here is example of my Table:

   Column Name     Data Type           Allow Nulls    
    hm_id           int                 Unchecked   // auto-increment id
    hm_studentID    uniqueidentifier    Unchecked   // primary key
    hm_firstName    varchar(50)         Checked
    hm_lastName     varchar(50)         Checked
    hm_dob          datetime            Checked
espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • 3
    I usually create a int field with identity and then make this the primary key like this: create table Client ( ClientID int identity not null, ... constraint PK_ClientID primary key (ClientID) – GuidoG Nov 10 '17 at 12:29
  • 3
    Possible duplicate of [What are the best practices for using a GUID as a primary key, specifically regarding performance?](https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega) – gotqn Nov 10 '17 at 12:36
  • @GuidoG Using auto-increment ID as primary key can cause some problems. For example if you restore the database no one guarantees those values will be the same. This can be found on Microsoft webpage as well. – espresso_coffee Nov 10 '17 at 12:51
  • @espresso_coffee I did not know that. I restore a backup of the production DB many times into my test database and never had that problem yet – GuidoG Nov 10 '17 at 13:07
  • @espresso_coffee Do you have an url to that microsoft webpage ? – GuidoG Nov 10 '17 at 13:08
  • @GuidoG I will try to find. They specifically said that can't be guaranteed key will remain the same. – espresso_coffee Nov 10 '17 at 13:10
  • @espresso_coffee I would really like to read that, it sounds odd to me to be honest but with microsoft you never know... – GuidoG Nov 10 '17 at 13:11
  • @GuidoG I agree. We faced that problem recently after we restored one of our DB. I'm debating what I should use and still can't decide. Thanks for your help. – espresso_coffee Nov 10 '17 at 13:13
  • 4
    @espresso_coffee I'm afraid the idea that restoring a database could change the keys sounds like absolutely nonsense to me. That would make MSSQL useless, which is clearly not the case. – underscore_d Nov 10 '17 at 13:58
  • @espresso_coffee I think you're reading way to much into that spec. When you restore a db identity insert is usually turned on, nullifying that problem. And for what it's worth, I've built dozens of enterprise systems on top of SQL Server, leveraging primarily int pk's, and I've NEVER had that issue. The debate on whether to use an `int` or `uniqueidentifier` is pretty seasoned, and if you want more info on it I'd recommend: https://dba.stackexchange.com/questions/264/guid-vs-int-which-is-better-as-a-primary-key – pim Nov 10 '17 at 20:36

0 Answers0