I have added a new field RecordID in the existing table which has thousands of records. I want to make it key. In oder to do that I have to fill with unique value. My question is how can I populate record number in this field?

Thanx in advance,
-ned

__________________
-Ned

You could make this column an Identity column. That would populate all the rows with a value. Then you can put a key on it. You may or may not want to leave the field as an identity depending on what you plan on doing with the table.

Peter
------------------------------------------------------
Work smarter, not harder.

Setting a column as an identity column, or adding a new column with that attribute will not assign values to existing rows. The identity value is only assigned when a row is inserted.

The larger question is why you want to do this. You are asking to assign a "record number" to rows in a table when the concept of "record number" has no meaning. "Record number" implies some way to distingush one row from another based on the position that row has relative to other rows, and by definition a table in a relational database consists of an unordered set of rows - i.e. "position" is a concept that has no meaning.

Now, you must have some way to distingush one row from another now, otherwise, how do you tell them apart? Whatever that way is, that should be your key, and not some artificial construct like a "RecordID" which attempts to impose some sort of physical ordering on the data.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason, I beleive you are one of the best person who solve our programming problems. But Peter is right in this issue. It works.
-ned

Jeff,

I must have a bug in my installation of SQL server then. I added a new column onto a table on a test database, turned on its identity property, saved the table, then opened it up again, and low-and-behold, there were values.

And on the topic of a RecordID column, how is that "imposing some sort of physical ordering on the data"? Doesn't seem to me that a RecordID column is any more or less imposing than a UserID column or any other kind of ID column.

Just answer the guy's question. If he wants to hear a personal diatribe on database technique, I'm sure he'll ask.

Peter
------------------------------------------------------
Work smarter, not harder.

You are correct. My bad. Adding an identity column will indeed populate the column with a generated identity value when the ALTER TABLE command is executed.

I made a mistake.

No need for your sarcasm concerning your installation 'bug', though.
Something like a UserID presumably is an attribute of the entity being modeled by the row.

The question in this case is on what basis is that identity value assigned? It will be in whatever order the query processor decides to return the rows. I can easily demonstrate that two tables with identical rows will result in the assignment of different values for the identity column when it is added (construct one table as a heap and the other with a clustered index on some column, for example). So if I have two identically designed tables with otherwise identical data in them, yet after the creation of another column with the identity attribute in them they differ by the identity value assigned based not on the data in those tables but on some external attribute derived from some aspect of the physical database implementation, please tell me how is that relational?

Perhaps you should study up on the concept of a set.
Some questions are best not answered right away, until the underlying reasons for asking them in the first place are discerned. The underlying reasons may be based on faulty assumptions - like trying to assign a record number in a relational system where the concept of "record" doesn't even apply.

I fail to see how such observations can be considered a "diatribe". And I'll respond with my opinion however I think it will help, whether you approve of it or not.

Have a nice day.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks guys, I have got enough information.
CASE IS CLOSED!

-Ned