Delete logical duplicates using Ranking Function

A simple way to delete duplicate (not absolute duplicate records where records are completely identical) records from table using the ranking function and CTE is as follows.

The following SQL will remove logical duplicate records from the table tblTransaction.

If the table tblTransaction has multiple records for a combination of AccountId and RecordId the DELETE syntax will keep only the latest dates and delete all previous records for the combination.

WITH Transaction_CTE (Ranking, AccountId, RecordId, StartDate) AS (

SELECT Ranking = DENSE_RANK()

OVER(PARTITION BY AccountId, RecordId

ORDER BY StartDate ASC),

AccountId, RecordId, StartDate

FROM dbo.tblTransaction )

DELETE FROM Transaction_CTE WHERE Ranking > 1

Unable to remove Partition Filegroup

Today while removing a Filegroup we came across the famous error message.
The filegroup FileGroupNameXXX cannot be removed because it is not empty.

Normally, on getting this error message the first thing I do is try to shrink the Filegroup
DBCC SHRINKFILE (FileGroupNameXXX, 0)
This will clear all unused space from it.

However SQL Server informed me that the File does not exist. On further checking I noticed that the Filegroup does not have an associated File or Partition Scheme associated with it. Then why is it not empty and what is associated with it?

At this point, SQL Server DMV (Dynamic Management Views) came to my rescue and the following command showed there is a table along with an index still associated with the Filegroup.

SELECT ds.name, i.name, o.name
FROM sys.data_spaces ds
inner join sys.indexes i on i.data_space_id = ds.data_space_id
inner join sys.objects o on i.object_id = o.object_id
where ds.name = FileGroupNameXXX

This gave me the name of the Objects associated. So, I first dropped the table and ran the following command to remove the FileGroup.

ALTER DATABASE TestDB REMOVE FILEGROUP FileGroupNameXXX

Hope this helps...

Clean Partition on SQL Server

During the development and testing for Partitions on SQL Server, I have come across this question a lot of time "How do we clear the partition, file, filegroup, partition scheme and function and then start all over again?"

This can be a really tedious process, if you do not do it right and in the right order. This post documents on the steps you need to take to clean everything and start from scratch.

The first thing I always suggest is move all the tables that are on the said partition back to PRIMARY file group. You can check the steps here.

Once you have moved all the tables to the PRIMARY file group, the second step is to remove the Partitioned file groups. Assuming you have a database named TestDB and the Partition Filegroups name is something like TEST_PARTITION_2009_01, TEST_PARTITION_2009_02 and so on for 12 months, run the following syntax

ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_01
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_02
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_03
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_04
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_05
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_06
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_07
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_08
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_09
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_10
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_11
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_12

Third, remove the Partition Scheme and Function. Replace the Partition scheme and Function name accordingly.

DROP PARTITION SCHEME TEST_PARTITION_Part_Sch
DROP PARTITION FUNCTION TEST_PARTITION_Part_Func

Finally, you need to remove the Partition FileGroups from the database. I assume the same naming convention..

ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_01
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_02
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_03
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_04
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_05
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_06
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_07
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_08
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_09
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_10
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_11
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_12

And voila... you have a clean slate now. Enjoy

Create a Duplicate table

There are often times when I want to create a duplicate structure of an existing table. The simplest way to do that is following:

SELECT TOP 0 * INTO dbo.tablename

Remember to put a dbo before the new tablename, or else SQL Server creates the table under your schema.

Create Clustered Index as Unique or Not?

Clustered Index is very important and determine the order in which data is stored in the table. A table without a Clustered Index is called a HEAP. (Try and picture a Heap in your mind and then you can visualize how data is stored in table without a Clustered Index.. Huh!!)

Clustered Index also helps the SQL Engine uniquely identify a row. Thus, if possible, always create a Clustered Index as UNIQUE. If you do not specify the UNIQUE clause while creating your Clustered Index, SQL Server automatically adds a 4 byte uniqueidentifier column to the table to make every row unique.

Thus, specifying a UNIQUE clause also helps save valueable database space.

More information can be found at the following MSDN Page.

Clear Log Space from Database

After large operations on the database the Log file may get filled and you may get a warning that there is no space to execute the SQL Command.

Use the following command to clear the Log File.
DBCC SHRINKFILE(LogFileName, 1)

You can get the name of the Log File with the following command
sp_helpdb databasename

DBCC SHRINKFILE is an interesting command and can also be used to shrink the Data files. Read the following MSDN article to more about this command.

View Log Size and Space Used

Use the following command to see the current log size (MB) and space used for all Databases.

DBCC SQLPERF(LOGSPACE)

The output will be as follows

Move a Partitioned Table to PRIMARY Filegroup

The following command will move the Partitioned Table to PRIMARY Filegroup. (The table will be un-partitioned then....)

1. Drop the existing Non Clustered Indexes on the Table (if any)
2. Drop the Clustered Index on the table
3. Re-Create the Clustered Index on the table specify the Filegroup as PRIMARY.
   CREATE CLUSTERED INDEX IndexName ON TableName(ColName) ON [PRIMARY]
4. Re-Create the Non Clustered Indexes (if any)

Identify your SQL Server Version

The following code can help you identify your SQL Server Version.

SELECT
SERVERPROPERTY('ProductVersion'),
SERVERPROPERTY('ProductLevel'),
SERVERPROPERTY('Edition')

Well, more details on this can be found at the following KB321185

Partition Alignment, LEFT or RIGHT?

While defining the Partition Range you need to specify if the Partition will be LEFT aligned or RIGHT aligned. Unless you have a good idea of this, it can be very confusing.
Let us break the code..... :)

A Partition Range defined with:
LEFT means Upper boundary of the 1st Partition Range
RIGHT means Lower boundary of the 2nd Partition Range

Also, the no. of Partition Boundary is always 1 less than total no. of Partition Range. e.g. A Partition with 5 Range will have 4 boundary specified.

Let us try and understand all with an example.
A Partition defined with the LEFT as follows
RANGE LEFT for VALUES ('20010101', '20020101', '20030101', '20040101')
will have the following Partition Range
<= 20010101
20010102 to 20020101
20020102 to 20030101
20030102 to 20040101
>= 20040102

(Notice the first date for every Partition Range is 1 more than the Boundary value specified as the LEFT is for Upper Boundary)

Similarly a Partition defined with the RIGHT
RANGE RIGHT for VALUES ('20010101', '20020101', '20030101', '20040101')
will have the following Partition Range
<20010101
20010101 to 20011231
20020101 to 20021231
20030101 to 20031231
>= 20040101

(Notice the first date for every Partition Range is the same as Boundary value specified as the RIGHT is for Lower Boundary)

Thus, you see it is more simple to specify a Partition Range as RIGHT Aligned for date values.

Specifying a Partition Boundary with a datetime data type has additional complexities. We will discuss this in a later post.