December 21, 2014 at 6:40 pm
hi experts,
When I design a new table, I first try to find a group of columns that will
represent uniqueness. I usually create the Primary Key on those columns.
But I have heard others say that a Record number (identity type) is needed by SQL Server for performance reasons.
If you use a RecID column in every table, please inform me as to why I should also.
Thanks
December 22, 2014 at 1:28 am
No.
If any developer came to me with tables with a generic 'RecordID', they'd be changing their design. Unless the table was the Records table.
You need a primary key. It is a design choice whether or not that primary key is a natural key (made up of columns in the data) or an artificial primary key (one added to just be used for relationships and never displayed to the user. If you chose artificial, you then need to decide how that column will be populated. Identity is one option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 5:11 am
No, a "record number" / surrogate key is not necessary in all cases. Adding such a column to every table offers no general advantage in terms of performance. Surrogate keys can have advantages in some situations but certainly not in others.
What is important is that you model the business domain accurately, i.e. with usable, "natural key" identifiers that implement the business rules your database is intended to satisfy. When you have done that, you are then in a position to evaluate whether you need to create additional keys in some places. Be guided by the criteria of Familiarity, Simplicty and Stability when choosing and designing your keys.
December 22, 2014 at 1:38 pm
sqlvogel (12/22/2014)
No, a "record number" / surrogate key is not necessary in all cases. Adding such a column to every table offers no general advantage in terms of performance.
Are you saying that having a big composite primary key on multiple varchar columns which also has to be used in other tables for referential integrity has absolutely no effect on performance?
December 22, 2014 at 2:24 pm
Alexander Suprun (12/22/2014)
sqlvogel (12/22/2014)
No, a "record number" / surrogate key is not necessary in all cases. Adding such a column to every table offers no general advantage in terms of performance.Are you saying that having a big composite primary key on multiple varchar columns which also has to be used in other tables for referential integrity has absolutely no effect on performance?
I don't think that's at all what he was saying. Those two scenarios are at two very polar opposites of one continuum. If you have a big composite primary key, maybe thats a good argument for using a surrogate key (although I've worked with tables with 200M rows with composite keys with up to seven columns and haven't notice any performance problems). There are optimizations which can be done on integer columns which can't be done on varchar columns (e.g. radix sorts, etc) but in many many cases, adding a surrogate key doesn't net you much.
As with any advice you get on the internet, take it with a grain of salt and try to figure out what works best for you. As to the OP's question, I think the answer is resoundingly "no", you dont NEED a surrogate key in SQL to get decent performance in most scenarios.
December 22, 2014 at 2:53 pm
Alexander Suprun (12/22/2014)
Are you saying that having a big composite primary key on multiple varchar columns which also has to be used in other tables for referential integrity has absolutely no effect on performance?
I'm saying the choice of keys should suit the circumstances and intended usage and that dogmatic one-size-fits-all rules ought to have no place in good database design. The question was asked in very general terms whereas you are describing something much more specific. Even the specific situation you describe raises plenty of possibilities about whether a surrogate key would actually be beneficial or not depending on the circumstances.
December 22, 2014 at 3:50 pm
sqlvogel (12/22/2014)
Alexander Suprun (12/22/2014)
Are you saying that having a big composite primary key on multiple varchar columns which also has to be used in other tables for referential integrity has absolutely no effect on performance?
I'm saying the choice of keys should suit the circumstances and intended usage and that dogmatic one-size-fits-all rules ought to have no place in good database design. The question was asked in very general terms whereas you are describing something much more specific. Even the specific situation you describe raises plenty of possibilities about whether a surrogate key would actually be beneficial or not depending on the circumstances.
Exactly. The notion that every table "needs" an identity, and particularly that it should be clustered on that identity by default, in the biggest myth in dbs.
[big key] has absolutely no effect on performance?
No, it won't have no effect. Having the big natural key could in fact have a positive effect.
Moreover, long char columns can each be encoded to an int if necessary for use in a key.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 22, 2014 at 6:06 pm
Whenever this topic comes up, I say "It Depends" and refer folks to the following video. It might change your mind about the use of IDENTITY columns as the clustered index, which should not be necessarily confuse with what the PK should be. There's more to it than just "logic".
http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply