Adding column with Identity
-
Need to add new column with Identity (s,i) to a table with ~4 million records. As I would like to insert it in particular place use EM. And stuck (fortunately it's developer server). Have no idea how long to wait. What is the fastest way to do this? Thanks
-
First thing I would do is look for a unique candidate that exists in the table before you go this route. I will give you possiblities but, the reason you are attempting to do this was not stated and may not be your best solution.
You can alter your table through EM and add the identity as the first column this will cause a new table to be recreated with a different name and the desired structure, then the data is copied to the new table, the old table is dropped, then the new table is renamed to the old table name. This is how SQL Server accomplishes that task when done through EM.
Essentially you can do the same thing yourself using a select into but this will not capture constraints and indexes it will simply create the new table with the desired columns. This is done with a statement similar to this...
SELECT IDENTITY(1,1), *
INTO T1A
FROM T1
There are other methods as well. Script out the table including triggers, constraints, indexes, etc. and do an INSERT T1A (col1, col2...) SELECT col1, col2... FROM T1
If you provide more specifics I could probably help more.
--------------------------
Zach -
Thanks, Zach. Eventually I added this row (~35 minutes) from EM. My challenge is: to replace existing column id (bigint) with new one id(bigint, identity (82,256)). My plan is: 1)drop index on id; 2)rename id to oldid; 3)add new id with identity; 4)update related tables using oldid; 5)drop oldid; and 6)recreate nonclustered index on id. That's it. Longest step is #3 (35 minutes from 45 total). Really appreciate your comments regarding my plan. Thanks
-
Yuri,
If you are not particular about the location of the column within the table, you can accomplish this with two lines of code:
ALTER TABLE tablename DROP COLUMN column_name
ALTER TABLE tablename ADD column_name INT IDENTITY(1,1)
This will not preserve indexes,constraints, etc.
One question - you said you want the column in a particular place within the table. You may want to consider why this is important. A basic definition of a relation (table) is that the order of the rows and columns is immaterial. It is dangerous to count on the location of a column.
I once had a developer insist that one particular column be the first in the table so he could write code like "SELECT * FROM ..." and 'know' that column 1 would be the key field. Aside from the fact that SELECT * is seldom a good idea, he is in danger of future changes to the table. I tried to explain to him that another developer could persuade a future DBA to change to order of columns, or even he could forget this code and ask that the order be changed to meet some future programming need.
Hope this helps.
-
"One question - you said you want the column in a particular place within the table."....wanted to add that I too am curious about why this is a requirement - the only time I knew this mattered was (similar/identical to CJohnson's experience ?!?!) when the clientside code looped through a recordset to display values on a spreadsheet and the ordinal position was used!
**ASCII stupid question, get a stupid ANSI !!!** -
i bet he wants the column in a specific location for this reason:
in our shop, business rules state the PK columns are always the first columns of the table;
if the identity column is to become the PK, our shop would require the column to be the first.
if the PK existed accross several columns, and we were modifying the table to add a nother column to the PK, we'd end up rebuilding hte table to assure the columns were at the begining.
this is just to have a standard and to help readability, it's just so you can imply the PK from the first few columns.
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! -
Thanks guys for your help. New column in particular place- my boss requirements.
-
Often you can add a new column to the end of the table and people can want it in a different location because they use SELECT *, and they want to see the columns in a specific order. This in my opinion is unacceptable because you do so at the cost of the system as apposed to dealing with the inconvenience of writing the query the proper way (i.e. Select col1, col5, col2, col3, col4...). Coaching users to query the database properly can be benificial for them as well as your system and should always be a first consideration (of course it falls in line after retaining your job, if desired)
.First, I would make an effort to educate your user base. Second, I would add the column to the end of the table. Third, I would consider creating a view (only if your boss refuses to be taught and do things properly), explicitly naming the columns in the order your user (boss) would like to see them in, then informing your boss to use that view as apposed to the querying the direct table with SELECT *.
I don't want to tell you what to do, this is just my advice.
--------------------------
Zach -
Zach - CJohnson already came up with that explanation - Lowell came up with yet another - I'm curious about Yuri's boss's requirements - why "he/she" wants it done "just so"...
**ASCII stupid question, get a stupid ANSI !!!** -
yah I feel stupid now, sorry guys I didnt see those posts above I was just posting to the last comment. And by the way I think CJohnson has a wonderful perspective
.Sorry again, promise I'll read the rest of the replies next time.
You're on the right track though sushila, i think that question has to be answered before you can really give an appropriate answer.
Thanks for pointing it out.
--------------------------
Zach
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply