Delete all the Data of a Partition

If you want to delete blocks of data, you usually would like to delete blocks instead of deleting rows. If you at the same time use partitions to store your data, and all the data that you want to delete (e.g. all the data from 2010 and before), you can use the partition commands to realize this. Instead of deleting row by row with the delete-Command
delete * from table where timestamp < '2011-01-01'

you would then truncate the data of the partition(s) that contains this data (from 2010 and before). To do this, you have to create a staging table and then use the SWITCH TO command to move the data to this table. Both steps could be done using the SQL Server wizards. If you have created partitions on a table, you could right-click on the table name, choose "Storage", then "Manage partition":



In the wizard, you choose "Manage partitioned data in a sliding window scenario" and then enable "Switch Out":


The wizard composes now SQL commands to create a staging table with the same columns and indexes as the original table. However, to delete a partition's data you wouldn't need to create all the indexes, it's enough to create the columns. Then you have to invoce the SWITCH TO command to move the data to the staging table:

ALTER TABLE table SWITCH PARTITION 1 TO stagingTable

This is a meta data-operation, no data is moved physically, and thus the data movement doesn't cost a lot. To delete the data, you can then drop the table or truncate it. Both is a block operation and, thus, won't harm the database.

PS: In case, that other tables have foreign keys to this table, you would have to delete this data beforehand (since it might not make sense to have it without its references anyway). Most usually, you would have these referenced tables also partitioned so that you should think about deleting the partitions of referenced tables in the same transaction.

Doing Partition Elimination

Let us assume, that we want to move old data to secondary filegroups. In this case, we would then partition on the timestamp of a row. However, our applications querys a lot on the primary key column "ID", so we prefer to make the ID instead of the timestamp the partition column.

The table therefore partitioned like this:

Partition 1: ID < 99 (before 01-February-2011)
Partition 2: ID 100..199 (February 2011)
Partition 3: ID 200..299 (March 2011)
Partition 4: ID 300..399 (April 2011)
Partition 5: ID > 400 (after 01-May-2011)

At the same time, there are a lot of queries with timely conditions such as
select * from table where timestamp > '2011-04-01' and error is not null

The SQL Server query optimzer would now search the whole table for entries that are younger then the 1st of April 2011 (and have the error column filled). Since the timestamp is not part of the partition column, the query optimizer doesn't know that all data from April 2011 and younger is in partition 4 and 5 and, hence, it can't do partition elimination but has to go through all partitions.

How can we help the query optimizer that he only has to look up the concrete partition.

Until now, I found three answers:

• You can add a condition to the partition to the query:
  select * from table where timestamp > '2011-04-01' and error is not null and $partition.function(ID)>=4
  This way, the SQL Server goes through all rows with an clustered index seek and returns rows from partition 4 and greater. It's fast, but you have to change the query.
  
  
• You can add a condition to the ID to the query:
  You can also limit the query to the boundary value of the partition - adding and ID>=300, and thus, the SQL Server uses a clustered index scan, which is faster than a clustered index seek (see Scans vs. Seeks from Freedman), and furthermore, only reads through the partitions 4 and 5.
  
  
• You could add a non-clustered index on the timestamp:
  Thus, the ID is automatically included because it's in the clustered index. Then, you don't have to change your queries, and the SQL Server can use the index to find the IDs quite fast. This is the most convenient option because you don't change the query; however, the SQL Server needs more steps to find the result.
  

If you have more ideas, please let me know in a comment. :-)

Move a Table to another Filegroup

In case you want to move a table to another filegroup, you would have to change the primary key of the table... and also all the foreign keys to that primary key.

This script allows you to do all this, it drops the constraints of the foreign keys, then it moves the primary key and then it creates again the constraints of the foreign keys.

You can just run the script - as a result you receive the needed SQL statements to move the table (the script itself doesn't make any changes!!).

declare @tablename varchar(50)
set @tablename = --'TableA
declare @newFilegroup varchar(50)
set @newFilegroup = --'FilegroupB'

declare @tableID int
select @tableID = object_id from sys.tables where name=@tablename

select 'BEGIN TRANSACTION'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] DROP CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] DROP CONSTRAINT ' + kc.name + ' WITH (MOVE TO ' + @newFilegroup + ')' from sys.key_constraints kc where kc.parent_object_id = @tableID and kc.type='PK'

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] ADD CONSTRAINT ' + kc.name + ' PRIMARY KEY(' + c.name + ') ' from sys.key_constraints kc
inner join sys.columns c on kc.parent_object_id = c.object_id
where kc.parent_object_id = @tableID and c.is_identity = 1 and kc.type='PK'

UNION ALL select'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] WITH CHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY([' + cMove.name + ']) REFERENCES [dbo].[' + @tablename + '] ([' + c.name + '])' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
inner join sys.columns c on t.object_id = c.object_id
inner join sys.foreign_keys fkMove on fkMove.object_id=fkc.constraint_object_id
inner join sys.columns cMove on cMove.column_id=fkc.parent_column_id
where tMove.name = @tablename and c.is_identity = 1
and fkc.referenced_object_id=@tableID and fkc.parent_object_id=cMove.object_id

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] CHECK CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL

select '--COMMIT'

The result of the script looks like this:

Have fun!

Aggregating Values from a Hierarchy

In a session about the SQL Server 2008 datatype HierarchyID, the question came up whether you can easily ask question such as: "If I store things (and sup-things and so on) with a number in a hierachy, can I sum up the numbers of a thing, including the subthings?"
The answer is, yes, you can, and here is how it goes. The following code shows a table which hierarchically stores websites and their clickrate.

-- create hierarchical table
create table websiteCalls
(
path hierarchyid,
filename nvarchar(100),
clickCount int
)

-- insert root node "index"
insert into websiteCalls values (hierarchyid::GetRoot(), 'index.html', 1000)
go

-- insert a level 1 entry "products"
declare @index hierarchyid
select @index = path from websiteCalls where filename='index.html'
insert into websiteCalls values (@index.GetDescendant(null, null), 'products.html', 200)

-- insert two more level 1 entries "customers" and "impressum"
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
insert into websiteCalls values (@index.GetDescendant(@products, null), 'customers.html', 100)
insert into websiteCalls values (@index.GetDescendant(null, @products), 'impressum.html', 10)

-- insert a "product1" on level 2
insert into websiteCalls values (@products.GetDescendant(null, null), 'product1.html', 300)
declare @product1 hierarchyid
select @product1 = path from websiteCalls where filename='product1.html'

-- insert two more level 2 entries
insert into websiteCalls values (@products.GetDescendant(@product1, null), 'product2.html', 50)
insert into websiteCalls values (@products.GetDescendant(null, @product1), 'product0.html', 100)
go

-- get all websites and their clickrates
select path.ToString(), filename, clickCount from websiteCalls

-- get the sum of clickrate number of all product-pages:
declare @products hierarchyid
select @products = path from websiteCalls where filename='products.html'
select SUM(clickcount) from websiteCalls where path.IsDescendantOf(@products) = 1 and path<>@products

Managing hierarchical data in SQL Server 2008

There are roughly three different approaches to store and retrieve hierarchical data in SQL Server:

Parent-Child-Approach with Recursive TCE:
You can save the ID of a node's parent in an extra “parent column”:

Then you connect the referenced items with recursive Common Table Expressions (CTE).

HierarchyID data type:
Or you save the node’s hierarchy location with the new data type HierarchyID, which stores a path to a node and, thus, reveals the where in a tree the row occurs. A sample table looks like this:

The hierarchy is directly visible and, unlike in the simple parent-child-pattern, you have now the HierarchyID methods to work against the tree structure.

XML data type:
The third option is to represent the hierarchy in a XML document and store the instance in an xml column:

Doing this, you can retrieve information from the tree using XQuery.

This is meant to be a teaser... ;-) I'm going to write more about this the next days.