March 19, 2009 at 4:46 pm
Hello,
I've begun a new project and my predecessor used a char(10) field as the primary key in all tables. The key is generated by using the last 10 characters of newid(). Apparently this was done "because of known issues with identity columns".
I think I remember reading of issues with identity columns about a decade ago, concerning multi-million record inserts, but I haven't heard of anything since then.
Is there any reason/logic that backs up such a setup? I can't imagine how fragmented that clustered key would get (when there is one)...
Thanks!
March 19, 2009 at 5:24 pm
I've begun a new project and my predecessor used a char(10) field as the primary key in all tables. The key is generated by using the last 10 characters of newid().
😀 Funny approach! I think it's not really guaranteed that this stays unique. There is a reason why the GUID is as it is (and still not unique).
Apparently this was done "because of known issues with identity columns".
I think I remember reading of issues with identity columns about a decade ago, concerning multi-million record inserts, but I haven't heard of anything since then.
Is there any reason/logic that backs up such a setup? I can't imagine how fragmented that clustered key would get (when there is one)...
I don't know any problems on local server but there seem to be some problems with replication. See the following thread: http://www.sqlservercentral.com/Forums/Topic669595-338-1.aspx
Greets
Flo
March 20, 2009 at 5:54 am
I wouldn't support this approach because you're likely to get non-unique data. Better to use the whole guid and be sure you have a real PK. However, using GUID's has problems too. They lead to a lot of fragmentation on the index which will cause a serious performance problem on your database.
Identity columns, with the exception of merge replication, are a great mechanism for having a unique identifier other than the natural key. I'm not aware of any issues with them since SQL Server 7. Prior that, they created serious hot spots on indexes and could bring down a server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2009 at 8:54 pm
Florian Reischl (3/19/2009)
I've begun a new project and my predecessor used a char(10) field as the primary key in all tables. The key is generated by using the last 10 characters of newid().
😀 Funny approach! I think it's not really guaranteed that this stays unique. There is a reason why the GUID is as it is (and still not unique).
Apparently this was done "because of known issues with identity columns".
I think I remember reading of issues with identity columns about a decade ago, concerning multi-million record inserts, but I haven't heard of anything since then.
Is there any reason/logic that backs up such a setup? I can't imagine how fragmented that clustered key would get (when there is one)...
I don't know any problems on local server but there seem to be some problems with replication. See the following thread: http://www.sqlservercentral.com/Forums/Topic669595-338-1.aspx
Greets
Flo
GUIDS are unique... just not across the last 10 characters. From Books Online...
If an application must generate an identifier column that is unique across the database, or [font="Arial Black"]every database on every networked computer in the world[/font], use the ROWGUIDCOL property, the uniqueidentifier data type, and the NEWID function.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply