Bucket data

Here's the full illsutration using a sample table

ALTER DATABASE DBName ADD FILEGROUP [Filegroup1]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup2]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup3]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup4]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup5]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup6]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup7]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup8]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup9]  
GO  
ALTER DATABASE DBName ADD FILEGROUP [Filegroup10]  
GO

ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data1',
  FILENAME = N'<full path>\data1.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup1]  
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data2',
  FILENAME = N'<full path>\data2.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup2]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data3',
  FILENAME = N'<full path>\data3.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup3]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data4',
  FILENAME = N'<full path>\data4.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup4]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data5',
  FILENAME = N'<full path>\data5.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup5]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data6',
  FILENAME = N'<full path>\data6.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup6]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data7',
  FILENAME = N'<full path>\data7.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup7]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data8',
  FILENAME = N'<full path>\data8.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup8]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data9',
  FILENAME = N'<full path>\data9.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup9]
GO  
ALTER DATABASE DBName 
  ADD FILE
  (NAME = N'data10',
  FILENAME = N'<full path>\data10.ndf',
  SIZE = 5000MB,
  MAXSIZE = 10000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Filegroup10]
GO  



--create partition function
CREATE PARTITION FUNCTION BucketPartitionFN (int)  AS
  RANGE LEFT FOR VALUES 
  ( 1,2,3,4,5,6,7,8,9) 

--create partition scheme
CREATE PARTITION SCHEME BucketScheme AS
PARTITION BucketPartitionFN TO
(
[Filegroup1],
[Filegroup2],
[Filegroup3],
[Filegroup4],
[Filegroup5],
[Filegroup6],
[Filegroup7],
[Filegroup8],
[Filegroup9],
[Filegroup10]
)


--Now create sample table based on scheme
create table PartitionTest
(
ID int IDENTITY(1,1),
Val int,
BucketNo int
)
ON BucketScheme(BucketNo)

--populate some sample data
;WITH T1 AS (SELECT 1 N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
T2 AS (SELECT 1 N FROM T1 a,T1 b),
T3 AS (SELECT 1 N FROM T2 a,T2 b),
T4 AS (SELECT 1 N FROM T3 a,T3 b),
Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Seq FROM T4)


INSERT PartitionTest (Val,BucketNo)
SELECT Seq,NTILE(10) OVER (ORDER BY Seq) 
FROM Numbers


--Check the partitions where data resides with recordcount
SELECT $partition.BucketPartitionFN(BucketNo) AS PartitionNo,MIN(BucketNo) AS StartBucketNo,MAX(BucketNo) AS EndBucketNo,COUNT(*) AS RecordCount
FROM PartitionTest
GROUP BY $partition.BucketPartitionFN(BucketNo)

just replace DBName and path in above script and you will see it splits up data into 10 partitions based on Bucket value

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c