Creating a new identifier column

Question

Creating a new identifier column

CJ-234774

SSC-Addicted

Points: 411
More actions
May 31, 2005 at 11:23 am

#163844

I have a table with lots of data in it. To properly link it with another table I need to create a new column where I will just need it to increment. To populate the old records in the table I used this statement that I found somewhere on the web:
DECLARE @ROWNUM int
SET @ROWNUM = 0
UPDATE table
SET @ROWNUM = rowkey = @ROWNUM + 1
It worked OK, but the numbering appears totally random. When I select * from the table, it is sorted on the primary keys, but this new rowkey column jumps all over the place. Why do I care, you may ask... I guess it's not a functional issue, but when looking at the raw data, it's a little confusing/odd/messy.
I was wondering if anyone has another recommendation for accomplishing filling up the column in an orderly manner.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

I tried with following example. It works on the primary key. Execute it with primary key then remove the primary key and see the difference.

May be some one with more experience can throw some light on this.

DECLARE @Contact TABLE

(

ContactID INT

PRIMARY KEY,

RowNum INT NULL,

ContactName VARCHAR(100)

)

INSERT @Contact (ContactID, ContactName) VALUES (1, 'Contact 1')

INSERT @Contact (ContactID, ContactName) VALUES (2, 'Contact 2')

INSERT @Contact (ContactID, ContactName) VALUES (3, 'Contact 3')

INSERT @Contact (ContactID, ContactName) VALUES (4, 'Contact 4')

INSERT @Contact (ContactID, ContactName) VALUES (5, 'Contact 5')

INSERT @Contact (ContactID, ContactName) VALUES (6, 'Contact 6')

INSERT @Contact (ContactID, ContactName) VALUES (14, 'Contact 14')

INSERT @Contact (ContactID, ContactName) VALUES (15, 'Contact 15')

INSERT @Contact (ContactID, ContactName) VALUES (16, 'Contact 16')

INSERT @Contact (ContactID, ContactName) VALUES (17, 'Contact 17')

INSERT @Contact (ContactID, ContactName) VALUES (18, 'Contact 18')

INSERT @Contact (ContactID, ContactName) VALUES (7, 'Contact 7')

INSERT @Contact (ContactID, ContactName) VALUES (8, 'Contact 8')

INSERT @Contact (ContactID, ContactName) VALUES (9, 'Contact 9')

INSERT @Contact (ContactID, ContactName) VALUES (10, 'Contact 10')

INSERT @Contact (ContactID, ContactName) VALUES (11, 'Contact 11')

INSERT @Contact (ContactID, ContactName) VALUES (12, 'Contact 12')

INSERT @Contact (ContactID, ContactName) VALUES (13, 'Contact 13')

DECLARE @ROWNUM int

SET @ROWNUM = 0

UPDATE @Contact

SET @ROWNUM = RowNum = @ROWNUM + 1

SELECT * FROM @Contact ORDER BY 1

Regards,
gova

CJ-234774 SSC-Addicted Points: 411 More actions · Answer 2

That does seem to work. I'm not sure why it's not working for my existing table... What am I missing? Here's the create statement for the table, having removed all the misc columns:

CREATE TABLE [quote_det] (

[quote_no] [int] NOT NULL ,

[numb_] [int] NOT NULL ,

[cst_rowkey] [int] NULL ,

PRIMARY KEY CLUSTERED

(

[quote_no],

[numb_])

WITH FILLFACTOR = 80 ON [PRIMARY]

) ON [PRIMARY]

I tried adding a dual PK to your code and it worked nicely...

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

>It worked OK, but the numbering appears totally random.

What you are using is a strange feature of SQL Server, where it increments a variable once per row even though it is doing a set-based update of a set of rows.

Since sets are not ordered you cannot depend on the rows being updated in a specific order. They will be 'randomly' worked through by the update process and updated while the counter is incremented by one for each row processed.

>I was wondering if anyone has another recommendation for accomplishing filling up the column in an orderly manner.

What you need to do is specify for each row in the table which value that specific row should have. Here is one example of doing that, using a correlated subquery.

----------------