One of the problems I came across while writing the partitioning chapter for my FILESTREAM book is that the partitioning does not work correctly with RANGE LEFT. It works as expected when using RANGE RIGHT. Something is wrong with RANGE LEFT and the problem exists in SQL Server 2008, R2 and Denali CTP1.
I created a repro script and wanted to submit a connect bug. However, the connect page does not allow me to post scripts longer than 2000 characters. So I thought of posting the script here and add a link to this post in the connect item. Here is the repro script for those of you who are interested to try it, and here is the connect item I submitted.
-- -----------------------------------------------
-- Create the database
-- -----------------------------------------------
USE master
GO
IF DB_ID('NorthPole') IS NOT NULL
DROP DATABASE NorthPole
GO
CREATE DATABASE NorthPole ON
PRIMARY (
NAME = NorthPoleData1,
FILENAME = 'C:\Demos\Data\NorthPoleData1.mdf'),
FILEGROUP NorthPoleDB2(
NAME = NorthPoleData2,
FILENAME = 'C:\Demos\Data\NorthPoleData2.ndf'),
FILEGROUP NorthPoleFS1 CONTAINS FILESTREAM DEFAULT(
NAME = NorthPoleFS1,
FILENAME = 'C:\Demos\FS\NorthPoleFS1'),
FILEGROUP NorthPoleFS2 CONTAINS FILESTREAM(
NAME = NorthPoleFS2,
FILENAME = 'C:\Demos\FS\NorthPoleFS2')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\Demos\Data\NorthPoleLOG.ldf')
GO
-- -----------------------------------------------
-- Create Partition Function and Scheme
-- -----------------------------------------------
USE NorthPole
GO
CREATE PARTITION FUNCTION NPPartFN (INT) AS
RANGE LEFT FOR VALUES (3)
CREATE PARTITION SCHEME NPPartDBSch AS
PARTITION NPPartFN TO([PRIMARY],[NorthPoleDB2])
GO
CREATE PARTITION SCHEME NPPartFSSch AS
PARTITION NPPartFN TO(NorthPoleFS1, NorthPoleFS2)
GO
-- -----------------------------------------------
-- Create Table
-- -----------------------------------------------
CREATE TABLE [dbo].[Items](
[ItemID] [int] IDENTITY(1,1) PRIMARY KEY ON NPPartDBSch(ItemID),
[ItemGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL
UNIQUE ON [PRIMARY],
[ItemNumber] [varchar](20) NULL,
[ItemDescription] [varchar](50) NULL,
[ItemImage] [varbinary](max) FILESTREAM NULL
) ON NPPartDBSch(ItemID)
FILESTREAM_ON NPPartFSSch
-- -----------------------------------------------
-- Insert 6 Rows. 1-3 will go to FS1 and 4-6 will
-- go to FS2
-- -----------------------------------------------
INSERT INTO Items(itemguid, itemnumber, itemdescription, ItemImage)
SELECT NEWID(), 'ITM001', 'Item 1', CAST(1 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM002', 'Item 2', CAST(2 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM003', 'Item 3', CAST(3 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM004', 'Item 4', CAST(4 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM005', 'Item 5', CAST(5 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM006', 'Item 6', CAST(6 AS VARBINARY(MAX))
-- -----------------------------------------------
-- Add a new file group (DB and FS)
-- -----------------------------------------------
ALTER DATABASE NorthPole
ADD FILEGROUP NorthPoleFS3 CONTAINS FILESTREAM
ALTER database NorthPole
ADD FILE
(
NAME = 'NorthPoleFS3',
FILENAME = 'C:\Demos\FS\NorthPoleFS3'
)
TO FILEGROUP NorthPoleFS3
ALTER DATABASE NorthPole
ADD FILEGROUP NorthPoleDB3
ALTER database NorthPole
ADD FILE
(
NAME = 'NorthPoleDB3',
FILENAME = 'C:\Demos\Data\NorthPoleData3.ndf'
)
TO FILEGROUP NorthPoleDB3
-- -----------------------------------------------
-- Insert 3 more records. This will go to FS2
-- -----------------------------------------------
INSERT INTO Items(itemguid, itemnumber, itemdescription, ItemImage)
SELECT NEWID(), 'ITM007', 'Item 7', CAST(7 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM008', 'Item 8', CAST(8 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM009', 'Item 9', CAST(9 AS VARBINARY(MAX))
-- -----------------------------------------------
-- Switch partitions. Alter the partition function
-- so that rows 7 to 9 will go to the new
-- file group
-- -----------------------------------------------
ALTER PARTITION SCHEME NPPartFSSch
NEXT USED NorthPoleFS3
ALTER PARTITION SCHEME NPPartDBSch
NEXT USED NorthPoleDB3
ALTER PARTITION FUNCTION NPPartFN()
SPLIT RANGE (6);
-- -----------------------------------------------
-- After the above, a new FILESTREAM folder is
-- created in FS1. No data goes to FS3
-- -----------------------------------------------