Tag: Fragment Table

IF EXISTS(
       SELECT 1
       FROM   sys.databases AS d
       WHERE  d.name = 'Test_1'
   )
    DROP DATABASE Test_1
 GO
 CREATE DATABASE [Test_1] ON PRIMARY(
                                        NAME
                                        =
                                        N'Test_1',
                                        FILENAME
                                        =
                                        N'E:\Database\Sharding\Test_1.mdf',
                                        SIZE
                                        =
                                        10240KB,
                                        MAXSIZE
                                        =
                                        UNLIMITED,
                                        FILEGROWTH
                                        =
                                        1024Kb
                                    ),
 FILEGROUP [Test_A](
                       NAME = N'Test_A',
                       FILENAME = 'E:\Database\Sharding\Test_A.ndf',
                       SIZE = 10240kb,
                       MAXSIZE = UNLIMITED,
                       FILEGROWTH = 1024kB
                   ),
 FILEGROUP [Test_B](
                       NAME = N'Test_B',
                       FILENAME = 'E:\Database\Sharding\Test_B.ndf',
                       SIZE = 10240kb,
                       MAXSIZE = UNLIMITED,
                       FILEGROWTH = 1024kB
                   )
 LOG ON (
            NAME = N'Test_log',
            FILENAME = 'E:\Database\Sharding\Test_Log.ldf',
            SIZE = 10240kb,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 1024kB
        ) COLLATE Chinese_PRC_CI_AS

GO
 USE [Test_1]

IF EXISTS(
       SELECT 1
       FROM   sys.partition_functions AS pf
       WHERE  pf.name = 'TEST_PART'
   )
    DROP PARTITION FUNCTION [TEST_PART]
 GO
 -- set a partiton condition
 CREATE PARTITION FUNCTION TEST_PART(INT) AS RANGE LEFT FOR VALUES(2)
 GO
IF EXISTS(
       SELECT 1
       FROM   sys.partition_schemes AS ps
       WHERE  ps.name = 'TEST_SCH'
   )
    DROP PARTITION SCHEME [TEST_SCH]
 GO
 --set a scheme for table using.
 CREATE PARTITION SCHEME TEST_SCH
 AS PARTITION [TEST_PART] TO (Test_A, Test_B)
 GO
IF OBJECT_ID('STUDENT', 'U') IS NOT NULL
    DROP TABLE STUDENT
 GO
CREATE TABLE STUDENT
(
	Id        INT IDENTITY(1, 1) NOT NULL,
	NAME      VARCHAR(10) NULL,
	CLASS     INT NULL,
	GRADE     INT NULL
) ON TEST_SCH(Id) -- set scheme
 GO
INSERT INTO STUDENT
  (
    -- Id -- this column value is auto-generated
    NAME,
    CLASS,
    GRADE
  )
VALUES
  (
    'A',
    1,
    1
  );
INSERT INTO STUDENT
  (
    -- Id -- this column value is auto-generated
    NAME,
    CLASS,
    GRADE
  )
VALUES
  (
    'B',
    2,
    2
  );
INSERT INTO STUDENT
  (
    -- Id -- this column value is auto-generated
    NAME,
    CLASS,
    GRADE
  )
VALUES
  (
    'C',
    3,
    3
  );
INSERT INTO STUDENT
  (
    -- Id -- this column value is auto-generated
    NAME,
    CLASS,
    GRADE
  )
VALUES
  (
    'D',
    4,
    4
  )
 GO