table partitions for SQL Server 2005 _ database other

Source: Internet
Author: User
Tags filegroup getdate one table create database

Below, in the SQL SERVER 2005 table partition, how to partition the existing table with data, in fact, before the http://www.cnblogs.com/jackyrong/archive/2006/11/13/ 559354.html said, just exchange the order, the following is still illustrated by examples:
Still set up 4 folders in the DATA2 directory in C disk to make 4 filegroups and then build the database


Use master
IF EXISTS (SELECT name from sys.databases WHERE name = N ' Data Partition DB3 ')
DROP DATABASE [Data Partition DB3]
Go
CREATE DATABASE [Data Partition DB3]
On PRIMARY
(name= ' Data Partition DB Primary FG3 ',
Filename=
' C:\Data2\Primary\Data Partition DB Primary fg3.mdf ',
Size=5,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG1]
(NAME = ' Data Partition DB3 FG1 ',
FILENAME =
' C:\Data2\FG1\Data Partition DB3 fg1.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG2]
(NAME = ' Data Partition DB3 FG2 ',
FILENAME =
' C:\Data2\FG2\Data Partition DB3 fg2.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG3]
(NAME = ' Data Partition DB3 FG3 ',
FILENAME =
' C:\Data2\FG3\Data Partition DB3 fg3.ndf ',
SIZE = 5MB,
MAXSIZE=500,
Filegrowth=1),
FILEGROUP [Data Partition DB3 FG4]
(NAME = ' Data Partition DB3 FG4 ',
FILENAME =
' C:\Data2\FG4\Data Partition DB3 fg4.ndf ',
SIZE = 5MB,
MAXSIZE=500,
filegrowth=1)
Then create a data table:
Use [Data Partition DB3]
Go
CREATE TABLE MyTable
(ID INT not NULL,
Date DATETIME,
Cost $) on [primary]
and establish an index
Use [Data Partition DB3]
Go
CREATE UNIQUE CLUSTERED INDEX MYTABLE_IXC
On MyTable (ID) on [PRIMARY]
Next, add the data to the list.
Use [Data Partition DB3]
Go
DECLARE @count int
Set @count =-25
While @count <=100
Begin
Insert INTO MyTable select @count, GETDATE (), 100.00
Set @count = @count +1
End
Set @count =101
While @count <=200
Begin
Insert INTO MyTable select @count, GETDATE (), 200.00
Set @count = @count +1
End
Set @count =201
While @count <=300
Begin
Insert INTO MyTable select @count, GETDATE (), 300.00
Set @count = @count +1
End
Set @count =301
While @count <=400
Begin
Insert INTO MyTable select @count, GETDATE (), 400.00
Set @count = @count +1
End
Set @count =401
While @count <=800
Begin
Insert INTO MyTable select @count, GETDATE (), 500.00
Set @count = @count +1
End
At this point, you can see that the data is in one table select * from sys.partitions where object_name (object_id) = ' MyTable '

We then set up the table partitioning function use [Data Partition DB3]
Go
CREATE PARTITION FUNCTION [Data PARTITION Range] (int)
As RANGE left for VALUES (100,200,300)
This shows that the principle of partitioning is four partitions, from negative to 100,101-200,201-300, greater than 300
Of course, if you use right for values, it's from negative to 99,100 to 199,200-299, and greater than 300

Finally, the table partitioning function is applied to the file group.
Use [Data Partition DB3]
Go
CREATE PARTITION Scheme [Data PARTITION scheme]
As PARTITION [Data PARTITION Range]
to ([Data Partition DB3 FG1] [data Partition DB3 FG2], [data Partition DB3 fg3],[data Partition DB3]);
Move the table that was originally built into this table partition
Drop index MYTABLE_IXC on MyTable with (move to [Data Partition Scheme] (ID))

Finally take a look at select * from Sys.partitions where object_name (object_id) = ' MyTable '

As you can see, the original table data was correctly split into four filegroups to implement the table partition

Http://www.cnblogs.com/jackyrong/archive/2006/11/16/562514.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.