First of all we should know, why table partitions required? Where
we are playing with the big data and we try to dig out the query from the table
it takes more time to execute.
For example; if you have a database tables (tblYearSales)
and it contains past 5 years data. If you want to run the query on table it
almost scan entire table to get the required output. Instead of taking specific
data it scans entire tables from start to end.
Partitions makes different file groups for your criteria
i.e. in our scenario we can split our data into year wise and create separate
file group for each year.
See how it works:
First we create our database i.e. SalesDB
now we alter the
database and create file groups for each year
ALTER DATABASE SALESDB ADD
FILEGROUP [SALESDB_2000]
GO
ALTER DATABASE SALESDB ADD
FILEGROUP [SALESDB_2001]
GO
ALTER DATABASE SALESDB ADD
FILEGROUP [SALESDB_2002]
GO
ALTER DATABASE SALESDB ADD
FILEGROUP [SALESDB_2003]
GO
ALTER DATABASE SALESDB ADD
FILEGROUP [SALESDB_2004]
GO
ALTER DATABASE SALESDB ADD
FILEGROUP [SALESDB_2005]
GO
ALTER DATABASE SALESDB
ADD FILE
(NAME = N'SALESDB_2000',
FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SALESDB_2000.ndf')
TO FILEGROUP
[SALESDB_2000]
GO
ALTER DATABASE SALESDB
ADD FILE
(NAME = N'SALESDB_2001',
FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SALESDB_2001.ndf')
TO FILEGROUP
[SALESDB_2001]
ALTER DATABASE SALESDB
ADD FILE
(NAME = N'SALESDB_2002',
FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SALESDB_2002.ndf')
TO FILEGROUP
[SALESDB_2002]
ALTER DATABASE SALESDB
ADD FILE
(NAME = N'SALESDB_2003',
FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SALESDB_2003.ndf')
TO FILEGROUP
[SALESDB_2003]
ALTER DATABASE SALESDB
ADD FILE
(NAME = N'SALESDB_2004',
FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SALESDB_2004.ndf')
TO FILEGROUP
[SALESDB_2004]
ALTER DATABASE SALESDB
ADD FILE
(NAME = N'SALESDB_2005',
FILENAME = N'C:\Program
Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SALESDB_2005.ndf')
TO FILEGROUP
[SALESDB_2005]
GO
CREATE PARTITION FUNCTION
DateRangePartitionFunction(DATETIME) AS
RANGE LEFT FOR VALUES
(
'20001231
23:59:59.997',
'20011231
23:59:59.997',
'20021231
23:59:59.997',
'20031231
23:59:59.997',
'20041231
23:59:59.997',
'20051231
23:59:59.997'
)
--Create scheme
for partition functions:
CREATE PARTITION SCHEME DateRangePartitionFunctionScheme AS
PARTITION
DateRangePartitionFunction TO
(
[SALESDB_2000],
[SALESDB_2001],
[SALESDB_2002],
[SALESDB_2003],
[SALESDB_2004],
[SALESDB_2005],
[PRIMARY]
)
--Now Create
table and apply fileGroup
CREATE TABLE [dbo].[tblYearSales](
[SalesAmt] [numeric](18, 2) NULL,
[Date] [datetime] NULL
) ON [DateRangePartitionFunctionScheme] (Date)
--Insert data
into tables by addind years
DECLARE @i int=0
DECLARE @Date as date='2000-01-01'
DECLARE @Amt as numeric(18,2) =15
WHILE @i<=6
BEGIN
INSERT INTO tblYearSales (SalesAmt, Date) VALUES (@Amt+10, @Date)
SET @Date = DATEADD(YEAR,1,@Date)
SET @i = @i+1
END
GO
--now run the
query to check the record in respective file groups, extra record will go to
default file group i.e. Primary Group
SELECT
OBJECT_NAME(p.object_id) as my_table_name,
f.name,
f.physical_name,
f.size,
f.max_size,
f.growth,
p.rows
from sys.allocation_units u
join sys.database_files f on
u.data_space_id =
f.data_space_id
join sys.partitions p on u.container_id = p.hobt_id
where
u.type in (1, 3) and
OBJECT_NAME(p.object_id) = 'tblYearSales'
Now it's done.
Cheers,
Comments