Little brown man in the land of the giants

By now, we are partitioning data based on 01/01/2008 as range right. Now lets assume that it is already 2009, and you want to add another partition. The good news is that, anytime you feel like it, you can add or remove partition.

To create another partition for 2009, first you have to create a new filegroup and attach a file to that file group. (For details on how to do this, kindly refer to my first post). Once you have done that, you now have to modify the partition scheme and the partition function.

To modify the partition scheme:
ALTER PARTITION SCHEME [partition scheme name]
NEXT USED [the new filegroup that you have created]

eg.
ALTER PARTITION SCHEME ps_TransactionDate
NEXT USED FG3

Next, you would have to modify the partition function:
ALTER PARTITION FUNCTION [partition function name]()
SPLIT RANGE ('[new range]')

eg.
ALTER PARTITION FUNCTION pf_TransactionDate()
SPLIT RANGE ('01/01/2009')

The commands above will create a new partition moving all data that is 1/1/2009 and newer to the new filegroup - FG3 in this example.

If you wanna merge a partition function, then you simply use this command:
ALTER PARTITION FUNCTION [partition function name]()
MERGE RANGE ('[range to be merged]')

Although SQL Server has always provided support for large binary data by using varbinary(max), on the relational database system, it will cause a problem on the IO performance. Hence, although the feature is available, it has significant effect to SQL's performance. Ive seem some solution by storing these BLOBS into the filesystem and then using SQL Server as a pointer to that file... but the problem is maintaining consistency. In SQL Server 2008, this has been addressed by using FILESTREAM.

To enable filestream, you need to do the following:

• You must be running SQL Server 2008 CTP 5 :)
• You need to enable filestream on the server level
• You need to setup the database for filestream

To enable SQL Server Filestream:
You can do this in SSMS by right clicking on your server, going to properties, on the property window, select advance. One of the configuration option that will be displayed is the Filestream level access. By default, this is set to disabled and we need to set it "Full access enabled". You can also do this via TSQL:

EXEC sp_filestream_configure @enable_level = 3
GO

To configure the database for filestream:
Once you have enabled filestream already, then you need to setup your database by adding a new filegroup that contains filestream data. you can do this using SSMS and also by using TSQL:

ALTER DATABASE [Database Name] 
  ADD FILEGROUP [FileGroup Name] CONTAINS FILESTREAM;
GO
 
ALTER DATABASE NGDA ADD FILE
 (NAME = [Logical name],
  FILENAME = '[Physical filename including path]')
TO FILEGROUP [FigeGroup Name];
GO

eg:
ALTER DATABASE AdventureWorks 
  ADD FILEGROUP FSGroup CONTAINS FILESTREAM;
GO
 
ALTER DATABASE AdventureWorks ADD FILE
 (NAME = FSFile,
  FILENAME = 'D:\Temp\FSFile')
TO FILEGROUP FSGroup;
GO

After this, then you need to create a table something similar to this:

CREATE TABLE [table name]
( [some normal column],
  [BLOB] varbinary(max) FILESTREAM DEFAULT (0x)
);
GO

As you can see, we are still using varbinary(max) but please note of the word FILESTREAM. This will store whatever BLOB data you have to the filestream filegroup. Another thing to note here is the default value of 0X. This creates the file. Without this, there will be no file pointer.

As you can see, with these new implementation, we can now fully integrate BLOBs to our relational database system and not worry about performance problem. If you wanna see more of these, kindly attend my session this coming Wednesday at the Microsoft Server Wave launch.

More details here:
http://www.microsoft.com/singapore/heroeshappenhere/agenda.mspx