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. :-)

Good articels on PARTITION

Here are some articels about PARTITION in SQL Server 2008 that helped me:

• Alan Granfield talks about Myths and Truths of partitioning.
• Microsoft published a good white paper about partitioning and index strategies in SQL Server 2008.
• A good explanation on how to move a partition into another filegroup comes from Dan Guzman.
• Feodor Georgiev writes about how to risk losing data and how to have most down time.

And, a bit of propaganda for myself:

• I wrote a guest post on Feodor's blog sqlconcept.com about how to generally handle new and old data with partitioning (using a Louis Vuitton and an army bag as an example).

Query a SQL Server Database while it is still restoring

PARTITION is an SQL Server Enterprise Edition feature that can be used to spread data over different partitions, which can be put into different filegroups.
When you think about Backup and Restore strategies, a neat thing is that secondary filegroups can be set read-only and then don't have to be backuped permanently (simply because the data keeps unchanged). Thus, your backup runs faster, because you only backup readwrite filegroups, and thus the backup doesn't effect your database so much. Furthermore, you can do piecemeal restore after a disaster, which means that you can make the database available already after restoring the primary filegroup. So the users can query the restored part of the database until you restored all the filegroups of the database.

The following example shows how queries to the database work, if one filegroup isn't restored.

In the example, there are two tables with ID and CreateDate. The second table has a foreign key to the ID of the first table. The tables are partitioned with column ID at value 3, so that ID 1 and 2 of each table reside in partition 1, and ID 3 and 4 in partition 2. The first partition is on filegroup 1, the other on filegroup 2. A join query an the two tables returns this result:









In case of a crash, we would restore first the primary filegroup. If we run the same query against all the data in the tables, we get an error because not all data is available:
Msg 679, Level 16, State 1, Line 8
One of the partitions of index 'PK_Transfile' for table 'dbo.Transfile'(partition ID 72057594039238656) resides on a filegroup ("SECONDARY") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

In order to make your query run, you have to restrict it to the restored partitions. In this case, you have to restrict the query so that the ID columns of the tables are smaller or equal to 2. Since the partition column is known, the SQL processor knows that it only has to query the first partition and, thus, only enters the first, restored filegroup. The result then is not complete, but it doesn't return an error:







This way, you can use PARTITION to get your system run much faster, you can shorten your backup time and you can ease restore.

Archiving Table Entries

If you want to backup table entries into a second table and delete them in the master table, you can use the folling SQL:

DELETE FROM masterTable
OUTPUT DELETED.* INTO archiveTable
FROM deletionTable
WHERE masterTable.ID=deletionTable.ID

while deletionTable contains the IDs of the entries, which should be deleted.

When using this, all entries of the masterTable, which are joined to the IDs in the deletionTable are copied to the archiveTable and then deleted.

Thanks, Ronny!

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.

Using CASE in SQL Queries

This morning, I've got to know the CASE functionality of SQL Server (which surely exists in other databases, too). With CASE you can set values for a column under specified conditions, e.g.

SELECT
Column1,
Column2 = CASE
WHEN Column1 = '1' THEN 'Y'
ELSE 'N'
END
FROM Table1

Thus, column2 will be set with 'Y' if column1 has the value '1'; otherweise 'N' will be set.

PS: An overview by Craig S Mullins helps to understand this method in more detail.