z*y
发帖数: 1311 | 1 One table already has records in it.
I want to add one more column as below:
"id INT NOT NULL AUTO_INCREMENT"
and also set "id" as PRIMARY KEY.
If I do this using ALTER TABLE, what will happen?
Will the DBMS automatically fill the "id" of existing records?
If not, what to do to achieve that?
Thank you so much! | g***l
发帖数: 18555 | 2 alter table orders
add id_order int not null Identity(1,1) | c*****d
发帖数: 6045 | 3 这是一个挺有意思的问题
how does sql server assign auto-generated identity to existing records
我手边没有sql server，谁能试一下？
比如原表(name char(10))
添加一个字段成为(id int identity(1,1), name char(10))
如果记录物理顺序是{a, b, c, d}，会变成{(1,a), (2,b), (3,c), (4,d)}吗？
如果记录物理顺序是{d, c, b, a}，会变成{(1,d), (2,c), (3,b), (4,a)}吗？
字段id和原来表中的物理排列顺序是否相关？ | y****w
发帖数: 3747 | 4 需要offline table么？
【在 g***l 的大作中提到】
: alter table orders
: add id_order int not null Identity(1,1)
| c*****d
发帖数: 6045 | 5 I think sql server will make the table offline
【在 y****w 的大作中提到】
: 需要offline table么？
| y****w
发帖数: 3747 | 6 自动还是手动？ 我现在也没环境试验了． 猜测应该是有手动开关的．
【在 c*****d 的大作中提到】
: I think sql server will make the table offline
| y****w
发帖数: 3747 | | gy
发帖数: 620 | 8 I did a test on this, and the conclusion is "字段id和原来表中的物理排列顺序
相关"
Here is the test code:
-- 1. First create a table with only one column
CREATE TABLE dbo.test(col varchar(5));
-- 2. Insert some test data
DECLARE @id int
SET @id = 1
WHILE(@id <= 5000)
BEGIN
INSERT INTO dbo.test(col)
VALUES(LEFT(CAST(NEWID() AS VARCHAR(50)), 5));
SET @id = @id + 1;
END
SELECT * FROM dbo.test
-- 3. Now, add the identity column:
ALTER TABLE dbo.test
ADD TID INT IDENTITY;
-- 4. Check the result compared with the result from step 2.
SELECT * FROM dbo.test
-- Clean up.
DROP TABLE dbo.test
GO
--------------------
NOTE: The test was performed on SQL SERVER 2008. I don't have ORACLE here.
【在 c*****d 的大作中提到】
: 这是一个挺有意思的问题
: how does sql server assign auto-generated identity to existing records
: 我手边没有sql server，谁能试一下？
: 比如原表(name char(10))
: 添加一个字段成为(id int identity(1,1), name char(10))
: 如果记录物理顺序是{a, b, c, d}，会变成{(1,a), (2,b), (3,c), (4,d)}吗？
: 如果记录物理顺序是{d, c, b, a}，会变成{(1,d), (2,c), (3,b), (4,a)}吗？
: 字段id和原来表中的物理排列顺序是否相关？
|
|
