Pete FreitagDear SQL Server Enterprise Manager Developer
Published on July 31, 2008
By Pete Freitag
By Pete Freitag
Please add a button to make a column an identity.
You have a button to set as primary key, why do we have to scroll down through 3/4 of the properties, click the plus under "Identity", and then choose "Yes" for such a common operation?
Thank You
Dear SQL Server Enterprise Manager Developer was first published on July 31, 2008.
If you like reading about sql, sql server, or microsoft then you might also like:
- Use varchar(max) instead of text in SQL Server
- Cheat Sheet for SQL Server
- DateFormat for SQL Server
- Try Catch for SQLServer T-SQL
Discuss / Follow me on Twitter ↯
Tweet Follow @pfreitagComments
Amen, brother, amen. I don't even use Identity, and I found that to be a crazy depth of burial in the Properties.
by jfish on 07/31/2008 at 1:38:11 PM UTC
I think they should bury it deeper to discourage it's use. It's super simple in code:
create table myTable
( myId int not null IDENTITY(1,1) PRIMARY KEY)
The big problem with them is that they often allow for the entry of duplicate data that differs only by the id value. Things seem to work out much better if you can find a better candidate key in your data, even if it's a complex key.
create table myTable
( myId int not null IDENTITY(1,1) PRIMARY KEY)
The big problem with them is that they often allow for the entry of duplicate data that differs only by the id value. Things seem to work out much better if you can find a better candidate key in your data, even if it's a complex key.
by Mike Rankin on 07/31/2008 at 4:41:41 PM UTC
I couldn't agree with you more - this has been annoying me recently - if anything its worse in SQL Server 2005 than in 2000...
by Dan Lancelot on 07/31/2008 at 5:00:07 PM UTC
@Mike, why not just use a uniqueness constraint to avoid the duplicate data? I don't think it's valid to discourage their use. Using non integer primary keys can be really really slow in large tables.
by Pete Freitag on 07/31/2008 at 9:49:59 PM UTC
@Mike,
I think the use of the primary key can be completely separate from other constraints you may need on a table. As Pete points out, you can still set a unique constraint on a set of columns without making that complex key into the primary key.
I often do that, especially in child tables. Example: a folder table and a file table, where the file name has to be unique per folder, but not across folders, so maybe like this:
PK: fileID
FK: folderID
unique constraint: folderID, fileName
At that point, whether my keys are integer or UID or whatever, they're independent of the uniqueness. I think that the cross-table lookups are faster when the RDBMS doesn't have to compare complex keys.
I think the use of the primary key can be completely separate from other constraints you may need on a table. As Pete points out, you can still set a unique constraint on a set of columns without making that complex key into the primary key.
I often do that, especially in child tables. Example: a folder table and a file table, where the file name has to be unique per folder, but not across folders, so maybe like this:
PK: fileID
FK: folderID
unique constraint: folderID, fileName
At that point, whether my keys are integer or UID or whatever, they're independent of the uniqueness. I think that the cross-table lookups are faster when the RDBMS doesn't have to compare complex keys.
by jfish on 08/01/2008 at 5:39:21 AM UTC
@Mike if your uniqueness constraint has to span all columns then you don't have a meaningful key already in your table. This is often the case, rarely do I have a column that is unique aside from the primary key. If such a column exists then I think it is wise to consider alternate primary keys.
It is a design decision, and with all design decisions there are trade offs.
It is a design decision, and with all design decisions there are trade offs.
by Pete Freitag on 08/01/2008 at 9:12:14 AM UTC
I just about blew the soda i was drinkin out my nose after reading this, I totally need that button!
by Chris Carter on 08/05/2008 at 8:54:20 AM UTC