July 2010 - Posts

SQLBits VII registrations now open
28 July 10 09:00 AM | MartinBell | 1 comment(s)

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

Filed under:
Removing Partitioning on tables
25 July 10 06:40 PM | MartinBell | with no comments
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

 

Filed under:
Great feature…. or not!!
16 July 10 09:15 PM | MartinBell | with no comments

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.


image


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!

Filed under:

This Blog

SQL Blogs

Syndication