Identity Columns
-
Is it true that once I declare a column as an Identity column I cant change it later? ( after I fill the data).
-
I think you can alter the data afterwards, but only if you turn off IDENTITY for the column with ALTER statement.
N 56°04'39.16"
E 12°55'05.25" -
In SQL 2000, there's no provided way to turn off the identity property for a column once it's been established. You'll have to either:
- Create a new table without the identity, copy the data, drop the old table, & rename the new table, or...
- Create a new column, update it with the data from the identity column, drop the identity column, & rename the new column.
That said, it is possible to disable the identity property by directly updating the system tables, but it's not a recommended practice, & I'll not ellaborate on how to do so.
-
Could be usefull:
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
_____________
Code for TallyGenerator -
IDENTITY_INSERT, as Sergiv pointed out, is quite useful, but only when you wish to disable the IDENTITY column temporarily. Only one table may have IDENTITY_INSERT set to ON for a session.
It could be problematic to use IDENTITY_INSERT as a permanent method to diasble an IDENTITY column on a table. Should the need arise later to temporarily disable an IDENTITY column on another table (such as for a bulk insert or importing data from a dev/test system), then IDENTITY_INSERT must first be SET OFF on the first table (re-enabling the IDENTITY column starting with the greater of the highest inserted value or the prior identity value as the current identity value) before it can be SET ON for the second table to perform the insert/import. This would cause problems when attempting to force a value into the re-enabled IDENTITY column when inserting rows into the first table.
If you have a table where you wish to switch an identity column to a non-identity column (Yes! Good call!), then I would advise pursuing one of the create new table/create new column paths outlined above.
-Eddie
Eddie Wuerch
MCM: SQL -
One problem I have had with IDENTITY columns is that when I use the
SET IDENTITY_INSERT dbo.tblAnswer ON INSERT INTO dbo.tblAnswer(fields....) VALUES(...) SET IDENTITY_INSERT dbo.tblAnswer OFFThe identity value increments even though I am specifying the value. To clarify if the last identity value was 10 and I inserted values 1,2,3 then the next value would be 14!
To get around this I reset the identity value after I have used it.
DECLARE @MaxValue INT
SELECT @MaxValue=MAX(AnswerID)+1 FROM dbo.tblAnswer
DBCC CHECKIDENT ('dbo.tblAnswer',RESEED,@MAXValue)
SELECT IDENT_CURRENT('dbo.tblAnswer') tells me what SQL Server thinks the current identify value is for a given table.
-
The only point I see for disabling IDENTITY is to perform the favourite exersise of most of developers - "row by agonising row". Except those ones, of course, who is familiar with set based approach.
Sorry to know another good database to be spoiled by by ".Net developer with SQL skills".
IDENTITY column suppose to contain values internal references not ment to be exposed to anybody including database developer.
There is no point to copy identites accross when moving database. New instance must create it's own identities. If application stops working because identity values have been changed - it's a good reason to throw it away and fire the developer(s).
David, FYI, there is no need for that exersise with MAX(Value).
DBCC CHECKIDENT ('dbo.tblAnswer',RESEED, 0 )
is absolutely enough.
And prefixing tables with "tbl" as well as all other objects in SQL decreases database performance. Just in case you did not hear about it.
_____________
Code for TallyGenerator -
Shyam,
Although Serqiy has gotten blunt as all get-out, he's absolutely correct. Why on earth would you want to modify the content of the IDENTITY column? If you run out of integers, switch the datatype to BIGINT. If you want to preserve a given set of ID's for re-use (still, a bad idea unless you're selling 2 digit dog-tags in a one stop-light town), don't use IDENTITY.
So, enquiring minds want to know, why would you want to change an IDENTITY once it's been formed?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Seqiy,
Do you have a Microsoft URL for "And prefixing tables with "tbl" as well as all other objects in SQL decreases database performance. Just in case you did not hear about it." I've heard of such a thing but haven't been able to find a reference from the horses-mouth, yet.
Or... do you have a test that demonstrates that particular problem? Some of my guys are "Hungarian Notation Crazy" and I'd really like to break them of the habit.
Thanks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Years ago I started off prefixing tables with tbl, views with vw etc but moved away from doing that when I found I needed to replace a view with a denormalised table.
However, if this is the existing standard that is used in your place of work then you are likely to face an up-hill struggle to change it. To be frank, unless someone can proove absolutely categorically that changing an established (albeit in house) standard will benefit performance I would not bother to try.
Telling your boss, that you have spent 'n' months changing a working system into....a working system is not going to endear you to the powers that be.
-
Jeff,
It was just a matter of curiosity that caused me to pose this question. And I totally agree to the fact that once an IDENTITY is set there is no point in dropping it. Personally I cant think of a reason to drop IDENTITY.
But I thought that maybe I couldnt think of a reason, since I am new to SQL.
But this simple 2 liner question has helped me to learn a lot of new facts that I didnt know before. Thanks to all u experts for providing enlighting info.
- Shyam
-
F**k!!!
Stupid forum!!!
I spent half an hour writing a post about hungarian notation in TSQL and this thing wiped it out!
Sorry, have not time to repeat this exersise. The main idea is the Hungarian notation is for compilers, and TSQL is not the one. Draw the picture how your query is being executed and you'll find out why you should never use it in TSQL.
2 David.
There is probably no reason for redeveloping your life system unless the performance os system tables is proven bottleneck, but there is a strong reason to change a standard for a new development.
_____________
Code for TallyGenerator -
Thanks David... and I agree, but it's nothing quite so serious... our standards currently have some fairly strict naming conventions that do not include Hungarian Notation (except for the letter "p" for parameter), but every once in a while, someone brings the same arguments back... I though if Seqiy had a Microsoft reference or a good performance test that shows why it's bad, I could just staple a harcopy to the developers' heads so they'd stop asking me if we could change the standard.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply