SE の雑記

USE [WORK]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl1](
??? [Col1] [uniqueidentifier] NOT NULL,
??? [Col2] [nchar](4000) NULL,
??? [Col3] [date] NULL,
CONSTRAINT [PK_Tbl1] PRIMARY KEY CLUSTERED
(
??? [Col1] 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

CREATE NONCLUSTERED INDEX [IX_Tbl1] ON [dbo].[Tbl1]
(
??? [Col3] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
GO

SET NOCOUNT ON
USE [WORK]
ALTER DATABASE [WORK] SET RECOVERY SIMPLE

TRUNCATE TABLE [dbo].[Tbl1]

DECLARE @i INT = 0
DECLARE @date date = ‘2000-01-01’

WHILE (@i < 50000)
BEGIN
??? INSERT INTO [dbo].[Tbl1] VALUES (NEWID(), NCHAR(@i), DATEADD(day, @i, @date))
??? SET @I += 1
END

ALTER INDEX [PK_Tbl1] ON [dbo].[Tbl1] REBUILD
ALTER INDEX [IX_Tbl1] ON [dbo].[Tbl1] REBUILD

CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET NOCOUNT ON
USE WORK
DELETE FROM [dbo].[Tbl1]

SET NOCOUNT ON
USE WORK
WHILE(0=0)
BEGIN
??? DELETE TOP(1000) FROM [dbo].[Tbl1]
??? IF @@ROWCOUNT = 0
??? BEGIN
??????? BREAK
??? END
END