-
-
Registration is now open for SQLBits 7: The Seven Wonders of SQL.
Pricing can be found at http://www.sqlbits.com/information/Pricing.aspx
The agenda will be finalised once voting has finished.
If you wish to vote please do so now as voting is closing on the 6th August 2010
You can do this at http://www.sqlbits.com/information/PublicSessions.aspx
-
-
I answered a question on the newsgroups the other week about removing partitioning from a partitioned table. The answer is very similar to the way that you partitioned the table in the first place so you may want to read my previous post on this.
If you had the following table with a partitioned clustered and non-clustered index:
CREATE DATABASE partitioneddb
GO
USE partitioneddb
GO
DECLARE @InitialPartitionDate date = '20090201';
CREATE PARTITION FUNCTION PartitionFunction (datetime)
AS RANGE LEFT FOR VALUES (@InitialPartitionDate);
GO
CREATE PARTITION SCHEME PartitionScheme
AS PARTITION PartitionFunction
ALL TO ( [PRIMARY] );
GO
CREATE TABLE [dbo].[PARTITION_TABLE]
( id int not null identity,
indexedcol int not null,
PARTITION_COLUMN datetime not null );
GO
CREATE CLUSTERED INDEX [idx_PARTITION_TABLE] ON [dbo].[PARTITION_TABLE] ( id )
ON [PartitionScheme]([PARTITION_COLUMN]);
GO
CREATE INDEX ncidx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( indexedcol ) ;
GO
You will see that each index has two partitions:
SELECT LEFT(OBJECT_NAME(object_id),16) as [Table Name],
[partition_id],
[object_id],
[index_id],
[partition_number]
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name partition_id object_id index_id partition_number
---------------- -------------------- ----------- ----------- ----------------
PARTITION_TABLE 72057594038845440 2105058535 1 1
PARTITION_TABLE 72057594038910976 2105058535 1 2
PARTITION_TABLE 72057594038976512 2105058535 2 1
PARTITION_TABLE 72057594039042048 2105058535 2 2
*/
And each index is using the partition scheme:
SELECT LEFT(OBJECT_NAME(i.object_id),16) as [Table Name],
LEFT(d.name,16) as [Partition/Filegroup Name],
LEFT(i.name,22) as [Index Name],
i.index_id,
i.[type],
LEFT(i.[type_desc],12) AS [type_desc]
FROM sys.indexes i
JOIN sys.data_spaces d ON i.data_space_id = d.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name Partition/Filegroup Name Index Name index_id type type_desc
---------------- ------------------------ ---------------------- ----------- ---- ------------
PARTITION_TABLE PartitionScheme idx_PARTITION_TABLE 1 1 CLUSTERED
PARTITION_TABLE PartitionScheme ncidx_PARTITION_TABLE 2 2 NONCLUSTERED
*/
To move the data from the partition scheme to a filegroup all you need to do is rebuild the clustered index using the filegroup instead of the partition scheme. (Note: this may be any filegroup not just PRIMARY)
CREATE CLUSTERED INDEX idx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( id )
WITH ( DROP_EXISTING = on )
ON [PRIMARY];
GO
The clustered index will then only have one partition:
SELECT LEFT(OBJECT_NAME(object_id),16) as [Table Name],
[partition_id],
[object_id],
[index_id],
[partition_number]
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name partition_id object_id index_id partition_number
---------------- -------------------- ----------- ----------- ----------------
PARTITION_TABLE 72057594039107584 2105058535 1 1
PARTITION_TABLE 72057594039173120 2105058535 2 1
PARTITION_TABLE 72057594039238656 2105058535 2 2
*/
This leaves the clustered index (i.e. data) using the filegroup:
SELECT LEFT(OBJECT_NAME(i.object_id),16) as [Table Name],
LEFT(d.name,16) as [Partition/Filegroup Name],
LEFT(i.name,22) as [Index Name],
i.index_id,
i.[type],
LEFT(i.[type_desc],12) AS [type_desc]
FROM sys.indexes i
JOIN sys.data_spaces d ON i.data_space_id = d.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'PARTITION_TABLE' ;
GO
/*Table Name Partition/Filegroup Name Index Name index_id type type_desc
---------------- ------------------------ ---------------------- ----------- ---- ------------
PARTITION_TABLE PRIMARY idx_PARTITION_TABLE 1 1 CLUSTERED
PARTITION_TABLE PartitionScheme ncidx_PARTITION_TABLE 2 2 NONCLUSTERED
*/
i.e. any non-clustered indexes will still be partitioned and you need to re-create them also:
CREATE INDEX ncidx_PARTITION_TABLE ON [dbo].[PARTITION_TABLE] ( indexedcol )
WITH ( DROP_EXISTING = on )
GO
SELECT LEFT(OBJECT_NAME(i.object_id),16) as [Table Name],
LEFT(d.name,16) as [Partition/Filegroup Name],
LEFT(i.name,22) as [Index Name],
i.index_id,
i.[type],
LEFT(i.[type_desc],12) AS [type_desc]
FROM sys.indexes i
JOIN sys.data_spaces d ON i.data_space_id = d.data_space_id
WHERE OBJECT_NAME(i.object_id) = 'PARTITION_TABLE' ;
GO
/*
Table Name Partition/Filegroup Name Index Name index_id type type_desc
---------------- ------------------------ ---------------------- ----------- ---- ------------
PARTITION_TABLE PRIMARY idx_PARTITION_TABLE 1 1 CLUSTERED
PARTITION_TABLE PRIMARY ncidx_PARTITION_TABLE 2 2 NONCLUSTERED
*/
If nothing is using the partition scheme and function they can then be dropped:
DROP PARTITION SCHEME PartitionScheme ;
GO
DROP PARTITION FUNCTION PartitionFunction ;
GO
-
-
I noticed a feature in SQL Server 2008 which I couldn’t remember seeing before, it’s remarkable that you can use something every day and still not know everything about it.
This feature was added for SQL Server 2008.. it’s not the ability to script drop and create objects which a very welcome and great new feature. The feature that I had overlooked was the ability to script and object as a job.
What a fantastic feature and I can’t wait to use it.. but I think I may be waiting a long time to use it (ignoring this post!!!)
Why?
For implementing DDL changes this may be ok for small updates, but it probably wouldn’t make it past a change review process because there is no error handling or contingency. Then if the code generated is not sufficient what is the point of scripting it to a job? If writing additional code would be best done in SSMS so why not script directly to a SSMS window and create the job when it has been fully written and tested? Scripting inserts/updates will always need to be changed doing this in the step details window of a job will not give you all the features that a SSMS query windows will have.
If I am missing something I’m sure you will let me know!