SQL Server table partitioning operations and design methods _mssql

I. Clustered index

The page level of a clustered index contains an index key and a data page, so the answer to what is stored in the leaf level of a clustered index other than the key value is everything, that is, all the fields in each row are planted at the leaf level.
Another way of saying this is that the data itself is also part of the clustered index, and the clustered index keeps the data in the table orderly based on the key value.
In SQL Server, all clustered indexes are unique, and if the unique keyword is not specified when the clustered index is created, SQL Server guarantees the uniqueness of the index internally by adding a unique identifier (uniqueifier) to the record, as needed. The unique identifier is a 4-byte value that is added to the data as a field attached to the clustered index key, and only those rows declared as index key fields with duplicate values are added.

Two. Non-clustered index
For nonclustered indexes, the leaf level does not contain all of the data. In addition to the key values, the index row in each leaf level (the lowest level of the tree) contains a bookmark (bookmark) that tells SQL Server where to find the data row corresponding to the index key. A bookmark class can have two forms. If a clustered index exists on the table, the bookmark is the clustered index key for the corresponding data row. If the table is a heap (heap) structure, or if there is no clustered index, the bookmark is a row identifier row Identifier,rid that locates the actual row in the format of the file number page number slot number.

The presence or absence of a nonclustered index does not affect the organization of the data paging, so each table is not limited to having a nonclustered index as a clustered index, and SQL Server 2005 can contain 249 nonclustered indexes per table SQL Server 2008 each table can contain 999 a nonclustered index , but it actually uses a lot less than this number.

Three. Include index
The number of index key fields is limited to 16, a total of 900 byte sizes, and the inclusion column appears only in the leaf level and does not control the ordering of the indexed rows in any way. Their purpose is to enable the leaf level to contain more information to make the index tuning of the overlay Index (covering index) more effective. The overlay index is a nonclustered index that can find all the information that satisfies the query at its leaf level so that the SQL There is no need for server to access data paging at all, and in some cases SQL Serer will silently add an inclusion column to the index. This can happen when the index is based on the partition table, which is the blog O (∩_∩) O (partitioned table) that I am posting today, that does not specify on filegroup or no partition_scheme.

One. Introduction to SQL SERVER table partitions:
The table partitioning technology introduced by SQL Server allows users to spread data to different physical disks, improving parallel processing performance of these disks to optimize query performance ...

Two. The SQL SERVER database table partition is completed in three steps:

1. Create partition functions

2. Create a partition Schema

3. Partitioning the table

Based on the cache update mechanism, I use the time to partition, where everyone according to the requirements of the business to use the appropriate fields as a partition

Create the number of database partition files, where the data stored for one year is divided into 12 partitions, which requires the primary folder and the FG1 FG2 FG3 FG4in the folder where the D disk is now established.

Copy Code code 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 = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Airavcache FG2]
(NAME = ' Airavcache FG2 ',
FILENAME =
' D:\Data\FG2\AirAvCache fg2.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Airavcache FG3]
(NAME = ' Airavcache FG3 ',
FILENAME =
' D:\Data\FG3\AirAvCache fg3.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Airavcache FG4]
(NAME = ' Airavcache FG4 ',
FILENAME =
' D:\Data\FG4\AirAvCache fg4.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Airavcache FG5]
(NAME = ' Airavcache FG5 ',
FILENAME =
' D:\Data\FG5\AirAvCache fg5.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),

FILEGROUP [Airavcache FG6]
(NAME = ' Airavcache FG6 ',
FILENAME =
' D:\Data\FG6\AirAvCache fg6.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),


FILEGROUP [Airavcache FG7]
(NAME = ' Airavcache FG7 ',
FILENAME =
' D:\Data\FG7\AirAvCache fg7.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),

FILEGROUP [Airavcache FG8]
(NAME = ' Airavcache FG8 ',
FILENAME =
' D:\Data\FG8\AirAvCache fg8.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),

FILEGROUP [Airavcache FG9]
(NAME = ' Airavcache FG9 ',
FILENAME =
' D:\Data\FG9\AirAvCache fg9.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),

FILEGROUP [Airavcache FG10]
(NAME = ' Airavcache FG10 ',
FILENAME =
' D:\Data\FG10\AirAvCache fg10.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),

FILEGROUP [Airavcache FG11]
(NAME = ' Airavcache FG11 ',
FILENAME =
' D:\Data\FG11\AirAvCache fg11.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),

FILEGROUP [Airavcache FG12]
(NAME = ' Airavcache FG12 ',
FILENAME =
' D:\Data\FG12\AirAvCache fg12.ndf ',
SIZE = 5MB,
MAXSIZE=500,
filegrowth=1)

After you create it, figure:


Open FG1 folder See more AIRAVCACHEFG1.NDF files

Creating partition Functions
Code

Copy Code code as follows:

Use Airavcache
Go
--Create a function
CREATE PARTITION FUNCTION [Airavcache PARTITION Range] (DATETIME)
As RANGE left for VALUES (' 2010-09-01 ', ' 2010-10-01 ', ' 2010-11-01 ', ' 2010-12-01 ', ' 2011-01-01 ', ' 2011-02-01 ', ' 2011-03-01 ', ' 2011-04-01 ', ' 2011-05-01 ', ' 2011-06-01 ', ' 2010-07-01 ');

Create a partition Schema
Code

Copy Code code 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 schema

Copy Code code as follows:

CREATE TABLE [dbo]. [Avcache] (
[Citypair] [varchar] (6) Not NULL,
[Flightno] [varchar] (a) NULL,
[Flightdate] [DateTime] Not NULL,
[CacheTime] [DateTime] Not NULL DEFAULT (getdate ()),
[Avnote] [varchar] (a) NULL
On [Airavcache Partition Scheme] (flightdate); --note that the [Airavcache Partition scheme] schema is used here, depending on the flightdate partition

Query partition conditions

Copy Code code as follows:

--View Usage
SELECT *, $PARTITION. [Airavcache Partition Range] (flightdate)
FROM dbo. Avcache

It can be seen that September and October have been separated.