|
|
Posted by Erland Sommarskog on 11/24/07 23:23
Mike (ablyplus@yahoo.com) writes:
> For me is important to be 1,2,3,4,5,6 ... because it is important for
> the business logic of application, now, I have some random values
> instead.
So you need the series to be continuous? That is, gaps are not
permitted? In such case you must not use IDENTITY. Watch this:
CREATE TABLE myident(a int IDENTITY,
b int NOT NULL)
go
INSERT myident(b) VALUES (12)
INSERT myident(b) VALUES (NULL)
INSERT myident(b) VALUES (122)
go
SELECT * FROM myident ORDER BY a
go
DROP TABLE myident
An IDENTITY value is "consumed" no matter the INSERT succeeds or
not. And this is precisely why IDENTITY is good for scalability: there
is no number that is locked and which causes serialisation.
If you what to change the ID to be a running number, you can do this
with the existing data:
UPDATE tbl
SET ID = b.rowno
FROM tbl a
JOIN (SELECT ID, rowno = row_number() OVER(ORDER BY ID)
FROM tbl b) ON a.ID = b.ID
But do this first in BEGIN/ROLLBACK TRANSACTION, because I did not test it.
As for assigning new ids, please review my previous post in thread.
> Please, help me, what is the best way to do that.
I said previously that you needed to rebuild the table, but that is
not true. You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column,
and then use ALTER TABLE DROP COLUMN to get rid of the old. Finally,
use sp_rename to rename the column.
But whatever: do under no circumstances use the table-designer GUI
in Mgmt Studio. It is not reliable.
--
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
Navigation:
[Reply to this message]
|