|
|
Posted by Erland Sommarskog on 11/23/07 22:29
Mike (ablyplus@yahoo.com) writes:
> Hi, I use SQL Server Enterprise Manager for MS SQL Express and i want
> to change the column properites for [int]
> type of column, but I cann't change it.
>
> More in details...
> I'd like to set autoincremntal option and in order to change that I am
> trying to change Identity specification from no to yes, without
> success.
> It is primary key too,
> Name AricleID,
> int type,
> Primary set of binding --> ((0))
> Full text specification --> No
> Identity specification --> No --> I want to change this
First of all, why do you want to use IDENTITY? IDENTITY is primarily
of interest when you have many simultaneous insert operations. In that
case, rolling your own with SELECT MAX causes serialization and poor
throughput.
But if you don't have a high-concurrency scenario, there is little reason
to use IDENTITY. It's likely to cause your more trouble than benefit in
the long run. Rolling your own is very simple:
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)
INSERT (id, ,...)
VALUES (@id, ....)
COMMIT TRANSACTION
One of the many problems with IDENTITY is the one you are facing now:
there is no ALTER TABLE syntax for adding the IDENTITY property to a
column, but you need to create a new table and copy data over, and in
that process, make sure that you include all triggers, indexes and whatnots.
Why SSMS will not let you change the propery, I don't know, but it's a
good thing that it does. SSMS would generate the kind of update I
described above. And had SSMS done it right, it would have been a good
thing, but the true story is that a number of serious bugs with that
script engine. I strongly advice you to implement all table changes
with ALTER TABLE.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|