更改表中某列的数据类型如何做和怎麽做效率最高

更改表中某列的数据类型如何做和怎麽做效率最高


https://www.cnblogs.com/lyhabc/diary/2014/04/02/3641758.html
https://www.cnblogs.com/lyhabc/articles/3446961.html
https://www.cnblogs.com/lyhabc/p/3359430.html

 1 USE [tempdb]
 2 go
 3 CREATE TABLE t1
 4     (
 5       id INT PRIMARY KEY
 6              NOT NULL
 7              IDENTITY(1, 1) ,
 8       name VARCHAR(200) ,
 9       memo CHAR(500)
10     );
11 go
12 -------------------------
13 TRUNCATE TABLE t1
14 go
15 
16 -----------------------------
17 
18 DECLARE @i INT
19 SET @i = 0
20 DECLARE @beginTime DATETIME ,
21     @endTime DATETIME
22 SET @beginTime = GETDATE()
23 WHILE ( @i < 200000 )
24     BEGIN
25         INSERT  INTO t1
26                 ( name ,
27                   memo
28                 )
29         VALUES  ( 'test' + STR(RAND() * 100000000) ,
30                   'memo' + STR(RAND() * 100000000)
31                 )
32         SET @i = @i + 1
33     END
34 SET @endTime = GETDATE()
35 SELECT  @endTime - @beginTime
36 -------------------------------------------
37 ALTER TABLE t1 ALTER COLUMN [id] BIGINT


因为聚集索引依赖于id列，所以不允许更改数据类型

消息 5074，级别 16，状态 1，第 1 行
对象'PK__t1__3213E83FFF6CC8A9' 依赖于 列'id'。
消息 4922，级别 16，状态 9，第 1 行
由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN id 失败。

这时候可以先drop掉聚集索引再更改，更改完毕再创建聚集索引

1 DROP INDEX PK__t1__3213E83FFF6CC8A9 ON [dbo].[t1]

还是不行

消息 3723，级别 16，状态 4，第 1 行
不允许对索引 'dbo.t1.PK__t1__3213E83FFF6CC8A9' 显式地使用 DROP INDEX。该索引正用于 PRIMARY KEY 约束的强制执行。

只有drop掉表了

1 DROP TABLE [dbo].[t1]

重新建表

1 USE [tempdb]
2 go
3 CREATE TABLE t1
4     (
5       id INT  NOT NULL IDENTITY(1, 1) ,
6       name VARCHAR(200) ,
7       memo CHAR(500)
8     );
9 go

插入数据

 1 DECLARE @i INT
 2 SET @i = 0
 3 DECLARE @beginTime DATETIME ,
 4     @endTime DATETIME
 5 SET @beginTime = GETDATE()
 6 WHILE ( @i < 200000 )
 7     BEGIN
 8         INSERT  INTO t1
 9                 ( name ,
10                   memo
11                 )
12         VALUES  ( 'test' + STR(RAND() * 100000000) ,
13                   'memo' + STR(RAND() * 100000000)
14                 )
15         SET @i = @i + 1
16     END
17 SET @endTime = GETDATE()
18 SELECT  @endTime - @beginTime

查询在alter column的时候加了什么锁

 1 USE [tempdb]
 2 GO
 3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 4 GO
 5 BEGIN TRAN
 6 ALTER TABLE t1 ALTER COLUMN [id] BIGINT
 7 
 8 --COMMIT TRAN
 9 
10 
11 SELECT
12 [request_session_id],
13 c.[program_name],
14 DB_NAME(c.[dbid]) AS dbname,
15 [resource_type],
16 [request_status],
17 [request_mode],
18 [resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
19 p.[index_id]
20 FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
21 ON a.[resource_associated_entity_id]=p.[hobt_id]
22 LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
23 WHERE c.[dbid]=DB_ID('tempdb') AND a.[request_session_id]=@@SPID  ----要查询申请锁的数据库
24 ORDER BY [request_session_id],[resource_type]

20万条数据，13个锁

统计时间和IO

1 SET STATISTICS TIME ON
2 SET STATISTICS IO ON
3 ALTER TABLE t1 ALTER COLUMN [id] BIGINT

 

SQL Server 执行时间:    CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

表 't1'。扫描计数 1，逻辑读取 15392 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:    CPU 时间 = 624 毫秒，占用时间 = 786 毫秒。

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o

posted @ 2013-11-28 08:46  桦仔  阅读(296)  评论(0)  编辑  收藏  举报