THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? It's in the Books Online!

Did you know you can ALTER an existing column to give it the identity property?

If you look up ALTER TABLE in the BOL index, you'll see this:

ALTER TABLE table_name
   { [ ALTER COLUMN column_name 
       {DROP DEFAULT |
         SET DEFAULT constant_expression |
         IDENTITY [ ( seed , increment ) ] }

....

I had been telling people for years that you couldn't change the identity property of an existing column, but there is was. And if you can't trust BOL, who can you trust?

Then I looked again.

Oops... this was ALTER TABLE for SQL Server Compact Edition, which has very different syntax in many cases. But the CE entry seems to always come up first whenever I do a search or use the Index.  There are some workarounds however.

In the Search Dialog, right under the wide search field, is a drop-list list next to the word "Technology". Click the down-arrow and you'll see a list of checkboxes which allow you pick and choose which elements of Books Online you want to search. You can uncheck SQL Server 2005 Compact Edition. However, that only affects the search operations. If you use the Index, you'll still see CE content. There's good news here too. There is a Filtered by: drop-down list at the top of the index list, and you can choose which content you're interested in. For this list however, the choice is all or one. I can choose just to see SQL Server Database Engine, but I can't choose to see everything BUT Compact Edition.

 But wait. I just found out about a really clever trick, from former SQL Server MVP Umachandar Jayachandran, who now works on the SQL team at Microsoft:

You can use the Help Collection Manager in BOL to remove the SQLCE content
entirely. This will filter it from the index too. You can access the Help
Collection Manager from:

ms-help://MS.SQLCC.v9/sqlcc9/html/b06d0f98-ef00-4b03-9f5d-b5c184b8df92.htm

Search for below if you can't use the link above:

Adding and Removing Help Collections

The URL can be entered directly into a browser, or into the URL text box at the top of the main BOL content screen.

Have fun!

~Kalen

Published Friday, July 13, 2007 8:25 PM by Kalen Delaney
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

I think the real question is, why can't you alter a column and give or take away the IDENTITY property??  I was able to do this via a bit of system table hacking in SQL Server 2000, so I know for a fact that it's something the engine is capable of doing. And while I obviously can't guarantee that it wouldn't ever cause a problem of some sort, I can say that in my experimentation with it nothing bad happened, and if there is such a possibility the engine could deal with it internally if it were a supported feature... It would be extremely useful in certain cases!

July 18, 2007 10:14 AM
 

Preethi said:

There is no direct way of ALTERing table to change the identity property.  

However,  the users of SQ: SErver Enterprise edition (and Developer edition) have a workaround. They can change the identity property -  by creating another table... Dont laugh.  These are the steps.

Step 1: Create another table with the desired structure. This table will be of the same structure except the identity property. You should have all the constraints of your original table including PK, default constraints, FK etc.

Step 2: If some other table is referencing the PK of your live table you need to drop it.

Step 3: Run this command:

 ALTER TABLE <OriginalTable> SWITCH TO <NewTAble>

Step 4:  Drop the original table and rename the New table

Ofcourse,  you need to rebuild the FK for the tables referencing this table.  But it is much better than copying all the data and rebuilding indexes etc.

G.R. Preethiviraj Kulasingham

December 9, 2007 1:53 AM
 

Dinakar said:

Kalen

I cant find it in BOL and I do get syntax errors even in 2005 Enterprise Edition.

January 8, 2008 1:44 PM
 

Kalen Delaney said:

What is giving the syntax errors? What exactly are you doing?

~Kalen

January 8, 2008 11:30 PM
 

Dinakar said:

In your post you said

>>Did you know you can ALTER an existing column to give it the identity property?

I tried doing that and got syntax errors:

CREATE TABLE IdTest (col1 int, col2 varchar(10) )

Go

ALTER TABLE IdTest ALTER COLUMN col1 IDENTITY(1,1)

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'IDENTITY'.

And I am using SQL Server 2005 Enterprise Edition.

January 14, 2008 1:32 PM
 

Kalen Delaney said:

Hi Dinakar

I think you need to reread my post. Although I said it was in the BOL, it is only for SQL Server Compact Edition (SQLCE), which has a different language than Standard, Developer or Enterprise.

The main point of the post was that you can remove the references for SQLCE so you don't get confused like this in the future. In other additions, you cannot alter a column to add an IDENTITY property.  You can add a new column with the IDENTITY property, and then drop the original column.

I apologize for causing confusing where I meant to try to alleviate it.

January 15, 2008 10:52 AM
 

Dinakar said:

Got it...

January 17, 2008 7:12 PM
 

Victor said:

Found your blog really helpful. However I have one difficulty, is the below only for SQL 2005? Looks like 2000 does not accept the SWITCH TO command whilst 2005 yes.

ALTER TABLE <OriginalTable> SWITCH TO <NewTAble>

Thanks

April 14, 2008 8:28 AM
 

Kalen Delaney said:

Victor

SWITCH is used to move partitions, and partitioning is a new feature of SQL Server 2005.

~Kalen

April 14, 2008 12:24 PM
 

kev said:

Thanks for posting this Kalen. I have been trying to drop a column DEFAULT, but was using the Compact Ed. syntax on a Developer Ed.   Was wondering why it wasnt working!

July 15, 2008 6:04 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement