Click here to Skip to main content
12,455,917 members (37,966 online)
Rate this:
 
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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100