March 27, 2014 at 2:30 pm
I thought I was creating a partitioned table since the following query looked like it showed record counts in separate partitions:
SELECT $PARTITION.LargeTable_PartitionFunction(AbcDate) AS PARTITIONID, COUNT(* ) AS ROW_COUNT
FROM dbo.LargeTable
GROUP BY $PARTITION.LargeTable_PartitionFunction(AbcDate)
ORDER BY PARTITIONID;
But the table is showing as not partitioned according to this query (as well as the table properties in SSMS):
SELECT *
FROM sys.partitions
WHERE [object_id] = object_id('LargeTable')
And rather than working on the test table I went ahead and created a script that creates a test database, test table, populates the test table, then partitions the table. The code is below. Can anyone tell me what is wrong with the code below and why the table is not being partitioned?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET NOCOUNT ON;
GO
USE master
go
--START: Create test environment **********************************************
IF EXISTS(select 1 from sys.databases where [name] = 'zPartitionTest')
begin
PRINT 'dropping the old zPartitionTest database';
DROP DATABASE zPartitionTest;
end
GO
PRINT 'Creating zPartitionTest database';
CREATE DATABASE zPartitionTest;
GO
USE zPartitionTest
GO
PRINT 'Creating LargeTable';
CREATE TABLE [dbo].LargeTable
(
AbcID NCHAR(14) NOT NULL -- unique key
, AbcDate DATETIME NOT NULL -- partition key
, Column01 VARCHAR(20) NULL
, Column02 VARCHAR(50) NULL
, Column03 VARCHAR(50) NULL
, Column04 VARCHAR(50) NULL
, Column05 VARCHAR(50) NULL
, Column06 VARCHAR(50) NULL
, Column07 VARCHAR(50) NULL
, Column08 VARCHAR(50) NULL
, Column09 VARCHAR(50) NULL
, Column10 VARCHAR(50) NULL
, Column11 VARCHAR(50) NULL
, Column12 VARCHAR(50) NULL
, Column13 VARCHAR(50) NULL
, Column14 VARCHAR(50) NULL
, Column15 VARCHAR(50) NULL
, Column16 VARCHAR(50) NULL
, Column17 VARCHAR(50) NULL
, Column18 VARCHAR(50) NULL
, Column19 VARCHAR(50) NULL
, Column20 VARCHAR(50) NULL
, Column21 VARCHAR(50) NULL
, Column22 VARCHAR(50) NULL
, Column23 VARCHAR(50) NULL
, Column24 VARCHAR(50) NULL
, Column25 VARCHAR(50) NULL
, Column26 VARCHAR(50) NULL
, Column27 VARCHAR(50) NULL
, Column28 VARCHAR(50) NULL
, Column29 VARCHAR(50) NULL
, Column30 VARCHAR(50) NULL
, CONSTRAINT [PK_LargeTable_AbcID] PRIMARY KEY CLUSTERED ( [AbcID] )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [PRIMARY]
);
GO
PRINT 'Adding test data to LargeTable';
DECLARE
@i INTEGER
, @PadChars VARCHAR(9);
SELECT
@PadChars = '000000000'
, @i = 0;
WHILE @i < 1000000 -- add 10 million rows with unique AbcID's.
BEGIN
SET @i = @i + 1;
IF @i % 50000 = 0 PRINT ' ' + CONVERT(VARCHAR(20), @i);
INSERT INTO LargeTable
(AbcID, AbcDate,
Column01, Column02, Column03, Column04, Column05, Column06, Column07, Column08, Column09, Column10,
Column11, Column12, Column13, Column14, Column15, Column16, Column17, Column18, Column19, Column20,
Column21, Column22, Column23, Column24, Column25, Column26, Column27, Column28, Column29, Column30)
SELECT
'AbcID' + RIGHT(@PadChars + CAST(@i AS VARCHAR (9)), 9)
, CASE WHEN @i < 100001
THEN CONVERT(DATETIME, '2013-08-15') -- first 100k rows should be in the "current" partition (and primary file group)
ELSE CONVERT(DATETIME, '2013-07-15') -- next 900k rows should be in the "archive" partition (and DatabaseArchivePartition file group)
END
, 'xxx01', 'xxx02', 'xxx03', 'xxx04', 'xxx05', 'xxx06', 'xxx07', 'xxx08', 'xxx09', 'xxx10'
, 'xxx11', 'xxx12', 'xxx13', 'xxx14', 'xxx15', 'xxx16', 'xxx17', 'xxx18', 'xxx19', 'xxx20'
, 'xxx21', 'xxx22', 'xxx23', 'xxx24', 'xxx25', 'xxx26', 'xxx27', 'xxx28', 'xxx29', 'xxx30';
END
--END: Create test environment ************************************************
-- Now backup the test table by Selecting into a new (staging) table, dropping the test table and recreating it
PRINT 'Copying current table LargeTable to LargeTable_PartitionStaging (partition staging, from which we will load the new table).'
IF EXISTS(select 1 from sys.tables where [name] = 'LargeTable_PartitionStaging')
begin
DECLARE @StgTable VARCHAR(50)
SET @StgTable = 'LargeTable_PartitionStaging_' + CONVERT(VARCHAR(23), GETDATE(),121)
PRINT 'renaming old LargeTable_PartitionStaging table to: ' + @StgTable
EXEC sp_rename 'LargeTable_PartitionStaging', @StgTable;
end
GO
--EXEC sp_rename 'dbo.LargeTable', 'LargeTable_PartitionStaging';
PRINT 'Copying LargeTable to LargeTable_PartitionStaging.'
SELECT * INTO LargeTable_PartitionStaging FROM LargeTable;
go
IF NOT EXISTS(select 1 from sys.tables where [name] = 'LargeTable_PartitionStaging')
PRINT ' ***** Table copy failed *****';
go
PRINT 'Dropping table LargeTable';
DROP TABLE LargeTable;
-- Create new filegroup, new data file in the new filegroupm Partition Function, Partition Scheme...
IF EXISTS(select 1 from sys.filegroups where [name] = 'DatabaseArchivePartition')
begin
PRINT 'dropping the old filegroup, DatabaseArchivePartition.'
ALTER DATABASE zPartitionTest
REMOVE FILEGROUP DatabaseArchivePartition;
end
GO
PRINT 'Adding filegroup, DatabaseArchivePartition, for archive partition data.'
ALTER DATABASE zPartitionTest
ADD FILEGROUP DatabaseArchivePartition; -- separate filegroup to hold the archive table partitions
GO
IF EXISTS(select 1 from sys.master_files where [name] = 'zPartitionTestArchive.NDF')
begin
PRINT 'dropping the old data file, zPartitionTestArchive.NDF.'
ALTER DATABASE zPartitionTest
REMOVE FILE zPartitionTestArchive;
end
GO
PRINT 'Adding new data file, zPartitionTestArchive.NDF, to the new archive partition filegroup.'
ALTER DATABASE zPartitionTest
ADD FILE
( NAME = zPartitionTestArchive
,FILENAME = 'G:\data\SQLSERVER\zPartitionTestArchive.NDF'
,SIZE = 512 KB
,FILEGROWTH = 10%
)
TO FILEGROUP DatabaseArchivePartition;
GO
PRINT 'Creating the partition function that will be used to split out the archive data from the student_attendance table.'
CREATE PARTITION FUNCTION LargeTable_PartitionFunction (DATETIME)
AS RANGE LEFT FOR VALUES ('2013/08/01 00:00:00.000');
GO
PRINT 'Creating the partition scheme, LargeTable_PartitionScheme, to specify where the archive data will be stored.'
CREATE PARTITION SCHEME LargeTable_PartitionScheme
AS PARTITION LargeTable_PartitionFunction
TO ([PRIMARY], DatabaseArchivePartition);
GO
-- Now recreate the LargeTable table with the partition scheme.
PRINT 'Creating new LargeTable table, specifying how to partition data.'
CREATE TABLE [dbo].LargeTable
(
AbcID [nchar](14) NOT NULL -- primary key
, AbcDate DATETIME NOT NULL -- partition key
, Column01 VARCHAR(20) NULL
, Column02 VARCHAR(50) NULL
, Column03 VARCHAR(50) NULL
, Column04 VARCHAR(50) NULL
, Column05 VARCHAR(50) NULL
, Column06 VARCHAR(50) NULL
, Column07 VARCHAR(50) NULL
, Column08 VARCHAR(50) NULL
, Column09 VARCHAR(50) NULL
, Column10 VARCHAR(50) NULL
, Column11 VARCHAR(50) NULL
, Column12 VARCHAR(50) NULL
, Column13 VARCHAR(50) NULL
, Column14 VARCHAR(50) NULL
, Column15 VARCHAR(50) NULL
, Column16 VARCHAR(50) NULL
, Column17 VARCHAR(50) NULL
, Column18 VARCHAR(50) NULL
, Column19 VARCHAR(50) NULL
, Column20 VARCHAR(50) NULL
, Column21 VARCHAR(50) NULL
, Column22 VARCHAR(50) NULL
, Column23 VARCHAR(50) NULL
, Column24 VARCHAR(50) NULL
, Column25 VARCHAR(50) NULL
, Column26 VARCHAR(50) NULL
, Column27 VARCHAR(50) NULL
, Column28 VARCHAR(50) NULL
, Column29 VARCHAR(50) NULL
, Column30 VARCHAR(50) NULL
)
ON LargeTable_PartitionScheme(AbcDate);
go
IF EXISTS(select 1 from sys.indexes where [name] = 'CX_LargeTable_AbcDate')
begin
PRINT 'dropping the old CX_LargeTable_AbcDate index'
DROP INDEX CX_LargeTable_AbcDate ON LargeTable;
end
PRINT 'Adding Clustered Index on AbcDate.'
CREATE CLUSTERED INDEX CX_LargeTable_AbcDate
ON LargeTable(AbcDate) -- Partition Key
ON [PRIMARY];
DECLARE @TableName VARCHAR(128), @sql VARCHAR(300)
SELECT @TableName = ISNULL(o.[name], '')
FROM sys.indexes i
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[name] = 'PK_LargeTable_AbcID'
IF @TableName <> ''
begin
PRINT 'dropping the old PK_LargeTable_AbcID constraint'
SET @sql = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT PK_LargeTable_AbcID'
EXEC(@SQL)
end
PRINT 'Adding non-clustered Primary Key on AbcID'
ALTER TABLE [dbo].Largetable
ADD CONSTRAINT PK_LargeTable_AbcID PRIMARY KEY NONCLUSTERED (AbcID)
ON [PRIMARY];
PRINT 'Copying data from Staging table into the new partitioned table (LargeTable).' + CHAR(13)
INSERT INTO LargeTable SELECT * FROM LargeTable_PartitionStaging;
--check partition info
/*
SELECT $PARTITION.LargeTable_PartitionFunction(AbcDate) AS PARTITIONID, COUNT(* ) AS ROW_COUNT
FROM dbo.LargeTable
GROUP BY $PARTITION.LargeTable_PartitionFunction(AbcDate)
ORDER BY PARTITIONID;
*/
select * from sys.filegroups
select * from sys.tables where [name] like 'LargeTable%'
select * from sys.indexes where [object_id] in (select [object_id] from sys.objects where [name] like 'LargeTable%') -- = object_id('LargeTable')
select * from sys.partition_schemes
select * from sys.partition_functions
select * from sys.partitions where [object_id] = object_id('LargeTable')
-- following query should eliminate the archive partition and retrieve data from the "live" partition
--select * from LargeTable where AbcDate > convert(datetime,'2013-08-01');
--select * from LargeTable where AbcDate = convert(datetime,'2014-03-26 15:44:21.207');
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply