Charlie’s path to dEAth

更改列名

EXECUTE sp_rename N’dbo.Customers.City’, N’Tmp_CityName_2′, ‘COLUMN’
GO
EXECUTE sp_rename N’dbo.Customers.Tmp_CityName_2′, N’CityName’, ‘COLUMN’
GO

不明白为啥要改两遍。

将列更改为NOT NULL

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Shippers
(
ShipperID int NOT NULL IDENTITY (1, 1),
CompanyName nvarchar(40) NULL,
Phone nvarchar(24) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Shippers ON
GO
IF EXISTS(SELECT * FROM dbo.Shippers)
EXEC(’INSERT INTO dbo.Tmp_Shippers (ShipperID, CompanyName, Phone)
SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT dbo.Tmp_Shippers OFF
GO
DROP TABLE dbo.Shippers
GO
EXECUTE sp_rename N’dbo.Tmp_Shippers’, N’Shippers’, ‘OBJECT’
GO
COMMIT

为啥要重新将表建立一遍，倒腾一遍数据呢？不是有 alter table alter column … 这样的语句吗？

更改某一列为主键

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Shippers
(
ShipperID int NOT NULL IDENTITY (1, 1),
CompanyName nvarchar(40) NOT NULL,
Phone nvarchar(24) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Shippers ON
GO
IF EXISTS(SELECT * FROM dbo.Shippers)
EXEC(’INSERT INTO dbo.Tmp_Shippers (ShipperID, CompanyName, Phone)
SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT dbo.Tmp_Shippers OFF
GO
DROP TABLE dbo.Shippers
GO
EXECUTE sp_rename N’dbo.Tmp_Shippers’, N’Shippers’, ‘OBJECT’
GO
ALTER TABLE dbo.Shippers ADD CONSTRAINT
PK_Shippers PRIMARY KEY CLUSTERED
(
CompanyName
) ON [PRIMARY]

GO
COMMIT

这个更怪了，除了最后面的那个alter table，前面折腾个什么劲呢？

有一位同事说修改一个大表的表结构总有不成功，每次都超时。看了他操作才知道是在企业管理器里面直接点点改改。以后招人凡是在企业管理器里面直接操作的，一律不要。



Technorati Tags     database

Filed by charlie at 10:20 pm under Engineer