USE [tempdb]
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
DROP TABLE [dbo].[TestData];
END
GO
CREATE TABLE [dbo].[TestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO
INSERT INTO [dbo].[TestData]
SELECT TOP 10000000
ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId,
CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode
FROM
Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'NewTestData')
BEGIN
DROP TABLE [dbo].[NewTestData];
END
GO
--Create New Table To Move Data To
CREATE TABLE [dbo].[NewTestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
--Move data to the new table
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
ALTER TABLE [dbo].[TestData] SWITCH to [dbo].[NewTestData];
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
SELECT COUNT(*) FROM [dbo].[TestData]; --0
SELECT COUNT(*) FROM [dbo].[NewTestData]; --10,000,000
Select * into #temp from [NewTestData]
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
DROP TABLE [dbo].[TestData];
END
GO
CREATE TABLE [dbo].[TestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO
INSERT INTO [dbo].[TestData]
SELECT TOP 10000000
ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId,
CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode
FROM
Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'NewTestData')
BEGIN
DROP TABLE [dbo].[NewTestData];
END
GO
--Create New Table To Move Data To
CREATE TABLE [dbo].[NewTestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
--Move data to the new table
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
ALTER TABLE [dbo].[TestData] SWITCH to [dbo].[NewTestData];
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
SELECT COUNT(*) FROM [dbo].[TestData]; --0
SELECT COUNT(*) FROM [dbo].[NewTestData]; --10,000,000
Select * into #temp from [NewTestData]
No comments:
Post a Comment