Identity Column

Question

Identity Column

Deborah Brown

SSC Enthusiast

Points: 193
More actions
January 30, 2007 at 11:59 am

#117460

I have a table that I want to add an identity column too. The existing records need to have the value in a specific order based on a date in one or more other columns. As new records are added, the identity column will now represent those records being added in the correct order. The problem is of course the old records that may or may not have been added in date order but I need ot have the identity column "look" like they were added in date order. I added a new column and updated it with the correct numbers in it. Now I need to make this column an identity column. This should be easy but for some reason I cannot find the syntax to do this. I apologize for asking what I assume is most trivial.
Thanks in advance. Deborah

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'll want to do the following:

do this on test first, and not production. I'm recommending using Enterprise manager for part of this, because it will handle a lot of the foreign keys automatically for you. technically, EM will drop and recreate the table, and readd all the constraints realted to the table.

in QA, SELECT * INTO YOURTABLE_BAK FROM YOURTABLE

in EM, add the identity field to the table...we KNOW they are out of order, but the BAK table is what you will use to re-populate it correctly.

in QA SELECT * FROM YOURTABLE_BAK ORDER BY SOMEDATEFIELD ASC to confirm the desired order. like you said, some dates might be blank, so you might want to add an additional order by somedatefield, salesareid or something.

in QA,

-- disable referential integrity

TABLE YOURTABLE NOCHECK CONSTRAINT ALL

GO

DELETE FROM YOURTABLE

GO

--simply do not reference the new identity column in the select statement:

INSERT INTO YOURTABLE(Col1,Col2,Col3...)

SELECT Col1,Col2,Col3... FROM YOURTABLE_BAK ORDER BY SOMEDATEFIELD ASC

-- enable referential integrity again

ALTER TABLE YOURTABLE CHECK CONSTRAINT ALL

someone else might KNOW this answer, but I bet if you added a clustered index to the table based on the date field, and then added the constraint in EM, it would do the same thing, but be much faster and easier than what i outlined above:

in QA : ADD PK_YOURTABLE on YOURTABLE(SOMEDATEFIELD) ie :

CREATE CLUSTERED INDEX PK_YOURTABLE ON YOURTABLE(SOMEDATEFIELD)

wait for the index to get created.

then add the the Identity column in EM.

after that, you can drop the PK that we created to organize the data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Rowan SSC Guru Points: 56440 More actions · Answer 2

Lowell is correct. Adding a clustered index on your date columns should force the Identity column to generate values ordered by that date. I would recommend testing thoroughly.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Deborah Brown SSC Enthusiast Points: 193 More actions · Answer 3

Adding the clustered index worked. Unfortunately I already had a clustered index on the table as well as many constraints so I had to do a lot of dropping and adding. I guess the bottom line is there is no command that sets an existing column to an identity column?

Thanks to all!

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Nope. Because an Identity column is in numeric order.

1

2

4

5

6

10

etc.

So, numbering the rows yourself:

1

5

2

10

6

etc

can't be converted to IDENTITY.

-SQLBill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Not if the indentity order needs to be different than the clustered index. In that case you always have to do some extra work.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Just curious, why did you need to have an order on the ident column???

Deborah Brown SSC Enthusiast Points: 193 More actions · Answer 7

I wanted the identity column to look like the order the records should have been entered in. Going forward the identity column will work fine since it will represent the order the records are being entered. It just so happens that a lot of my data was added after the fact so the column "datecreated" didn't really mean that.

I just hoped that if a column did not have any duplicate data and it represented some order, that I should be able to make an identity column out of it. The real issue here turned out to be that I already have a clustered index that the rest of the database (many tables) already had constraints involved with. (Sorry for the poor sentence construction there.)

I may end up rethinking the necessity to having the old records in order and go with the simple solution of just adding an identity column!