For more information about SQL Server table partition operations and design methods, see.
For more information about SQL Server table partition operations and design methods, see.
I. Clustered Index
The page level of the clustered index includes the index key and data page. Therefore, the answer to what the leaf level of the clustered index stores besides the key value is everything, that is, all fields in each row are in the leaf level.
Another talk is that data itself is part of the clustered index, which maintains the Data Order in the table based on the key value.
In SQL SERVER, all clustered indexes are unique,
Ii. Non-clustered Index
For non-clustered indexes, the leaf level does not contain all the data. In addition to the key value, the index row in each leaf level (the lowest layer of the tree) contains a bookmark to tell SQL Server where to locate the data row corresponding to the index key. A bookmarked course can be in two formats. If there is a clustered index on the table, the bookmarks are the clustered index keys of the corresponding data rows. If the table is in a heap structure, that is, if no clustered index is found, the bookmarkdonet is a row identifier, rid, locate the actual row in the format of the file number page number slot number.
Iii. Index inclusion
The maximum number of index key fields is 16, with a total size of 900 bytes. The inclusion column only appears at the leaf level and does not control the sorting of index rows in any way. This may occur when the index is created in the partition table, that is, the blog O (partition _ partition) O (partitioned table) I posted today does not specify on filegroup or no partition_scheme.
I. SQL SERVER table partition introduction:
The Table Partitioning technology introduced by SQL Server allows users to distribute data to different physical disks, improve the parallel processing performance of these disks to Optimize Query performance ......
2. SQL SERVER database table partitions are completed in three steps:
1. Create a partition function
2. Create a partition Architecture
3. Partition the table
Based on the cache update mechanism, I use time for partitioning. Here we use suitable fields as partitions according to business requirements.
Number of database partition files created. The data stored for one year is divided into 12 partitions, you need to create a Data folder on drive D, which contains the Primary folder and FG1 FG2 FG3 fg4 ............
The Code is as follows:
If exists (SELECT name FROM sys. databases WHERE name = n' AirAvCache ')
Drop database [AirAvCache]
GO
Create database [AirAvCache]
ON PRIMARY
(NAME = 'data Partition DB Primary fg ',
FILENAME =
'D: \ Data \ Primary \ AirAvCache Primary FG. mdf ',
SIZE = 5,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG1]
(NAME = 'airavcache FG1 ',
FILENAME =
'D: \ Data \ FG1 \ AirAvCache FG1.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG2]
(NAME = 'airavcache fg ',
FILENAME =
'D: \ Data \ FG2 \ AirAvCache FG2.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG3]
(NAME = 'airavcache fg3 ',
FILENAME =
'D: \ Data \ FG3 \ AirAvCache FG3.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG4]
(NAME = 'airavcache fg4 ',
FILENAME =
'D: \ Data \ FG4 \ AirAvCache FG4.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG5]
(NAME = 'airavcache fg5 ',
FILENAME =
'D: \ Data \ FG5 \ AirAvCache FG5.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG6]
(NAME = 'airavcache FG6 ',
FILENAME =
'D: \ Data \ FG6 \ AirAvCache FG6.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG7]
(NAME = 'airavcache fg7 ',
FILENAME =
'D: \ Data \ FG7 \ AirAvCache FG7.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG8]
(NAME = 'airavcache fg8 ',
FILENAME =
'D: \ Data \ FG8 \ AirAvCache FG8.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG9]
(NAME = 'airavcache fg9 ',
FILENAME =
'D: \ Data \ FG9 \ AirAvCache FG9.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG10]
(NAME = 'airavcache fg10 ',
FILENAME =
'D: \ Data \ FG10 \ AirAvCache FG10.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG11]
(NAME = 'airavcache fg11 ',
FILENAME =
'D: \ Data \ FG11 \ AirAvCache FG11.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1 ),
FILEGROUP [AirAvCache FG12]
(NAME = 'airavcache fg12 ',
FILENAME =
'D: \ Data \ FG12 \ AirAvCache FG12.ndf ',
SIZE = 5 MB,
MAXSIZE = 500,
FILEGROWTH = 1)
After creation
Open the FG1 folder and check that the AirAvCacheFG1.ndf file is missing.
Create a partition function
Code
The Code is as follows:
USE AirAvCache
GO
-- Create a function
Create partition function [AirAvCache Partition Range] (DATETIME)
As range left for values ('2017-09-01 ', '2017-10-01', '2017-11-01 ', '2017-12-01', '2017-01-01 ', '2017-02-01 ', '2017-03-01', '2017-04-01 ', '2017-05-01', '2017-06-01 ', '2017-07-01 ');
Create a partition Architecture
Code
The Code is as follows:
Create partition scheme [AirAvCache Partition Scheme]
As partition [AirAvCache Partition Range]
TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3], [AirAvCache FG4], [AirAvCache FG5], [AirAvCache FG6], [AirAvCache FG7], [AirAvCache FG8],
[AirAvCache FG9], [AirAvCache FG10], [AirAvCache FG11], [AirAvCache FG12]);
Create a table using the AirAvCache Partitiion Scheme Architecture
The Code is as follows:
Create table [dbo]. [AvCache] (
[CityPair] [varchar] (6) not null,
[FlightNo] [varchar] (10) NULL,
[FlightDate] [datetime] not null,
[CacheTime] [datetime] not null default (getdate ()),
[AVNote] [varchar] (300) NULL
) ON [AirAvCache Partition Scheme] (FlightDate); -- note that the [AirAvCache Partition Scheme] architecture is used here, which is partitioned by FlightDate
Query partition information
The Code is as follows:
-- View usage
SELECT *, $ PARTITION. [AirAvCache Partition Range] (FlightDate)
FROM dbo. AVCache
We can see that September and October were separated.