Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
i wanted to assign a column as primary key size is over 900 bytes and i got column size is over 900 bytes error.i had to decrease size to make it a primary key.question -
1.is there any better alternative to assign key to a column size over 900 bytes
2.suppose i decrease size of the column less than 900 bytes and assigned it primary key.but my other columns total sizes are more than 900 bytes as usual as there are many long data to be saved.in that case will there be any insert and update problem?
Posted 23-May-12 5:23am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This article explains better than I can in a quick answer!
 
http://blogs.msdn.com/b/bartd/archive/2011/01/06/optionsforindexedlookupsoflongvalues.aspx[^]
 
Basically, you need to reconsider your key design. Are you trying to create a primary key using fields that have long strings in, or combination of string fields?
 
Out of the suggestions in that article, I'd go with creating a primary key field using the IDENTITY specification which will solve this issue immediately. Then, you could consider some unique constrainsts or other indexes to provide database integrity and performance.
  Permalink  
Comments
losmac at 23-May-12 12:50pm
   
Very good answer and useful link, my 5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Agree with Dylan Morley, use surrogate keys whenever possible (=always) and define the natural keys as unique.
 
The only difference to Dylan's answer is that I would personally prefer a uniqueidentifier[^] instead of IDENTITY. One reason for example is that uniqueidentifier can be used in environments where you need replication. In overall it's more flexible.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 23 May 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid