sql server 2005中Delete语句可以添加一个top参数
以下示例删除 Testdb 表中所有行的 10%。
--
生成测试数据库
Create database Testdb
go
USE [ Testdb ]
GO
-- 生成测试表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ TestDelete ] (
[ PKID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ TestID ] [ int ] NULL ,
[ testString ] [ nvarchar ] ( 50 ) NULL ,
CONSTRAINT [ PK_TestDelere ] PRIMARY KEY CLUSTERED
(
[ PKID ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
go
-- 插入测试数据
declare @tmpID int
set @tmpID = 1
WHILE ( ( SELECT max (PKID) FROM TestDelete) < 2000 )
BEGIN
set @tmpID = @tmpID + 2
insert into [ TestDelete ] ( [ TestID ] , [ testString ] ) values ( @tmpID , cast ( @tmpID as nvarchar ( 10 )))
CONTINUE
END
select count ( * ) from TestDelete
go
-- 删除测试数据
--以下示例删除 Testdb 表中所有行的 10%。
DELETE TOP ( 10 ) PERCENT FROM TestDelete
GO
-- 下面这句执行错误
-- --DELETE TOP (10) PERCENT FROM TestDelete order by PKID desc
-- --GO
select count ( * ) from TestDelete
go
-- 删除测试数据库
-- --drop database Testdb
-- --go
Create database Testdb
go
USE [ Testdb ]
GO
-- 生成测试表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ TestDelete ] (
[ PKID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ TestID ] [ int ] NULL ,
[ testString ] [ nvarchar ] ( 50 ) NULL ,
CONSTRAINT [ PK_TestDelere ] PRIMARY KEY CLUSTERED
(
[ PKID ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
go
-- 插入测试数据
declare @tmpID int
set @tmpID = 1
WHILE ( ( SELECT max (PKID) FROM TestDelete) < 2000 )
BEGIN
set @tmpID = @tmpID + 2
insert into [ TestDelete ] ( [ TestID ] , [ testString ] ) values ( @tmpID , cast ( @tmpID as nvarchar ( 10 )))
CONTINUE
END
select count ( * ) from TestDelete
go
-- 删除测试数据
--以下示例删除 Testdb 表中所有行的 10%。
DELETE TOP ( 10 ) PERCENT FROM TestDelete
GO
-- 下面这句执行错误
-- --DELETE TOP (10) PERCENT FROM TestDelete order by PKID desc
-- --GO
select count ( * ) from TestDelete
go
-- 删除测试数据库
-- --drop database Testdb
-- --go
微软的开发人员真的很奇怪?不得而知。一个奇怪的语句。