fUGRCRhd

1DROP TRIGGER IF EXISTS [TR_PartitionBoundary_Allocation];
2DROP TABLE IF EXISTS [PartitionBoundary];
3DROP TABLE IF EXISTS [PartitionType];
4
5IF OBJECT_ID('[PartitionType]', 'U') IS NULL
6BEGIN
7    CREATE TABLE [PartitionType] (
8        [PartitionTypeId] [tinyint] IDENTITY(1,1)
9        ,[PartitionTypeName] [varchar](256) NOT NULL
10        ,[PartitionTypeValue] [int] NOT NULL
11        ,CONSTRAINT [PK_PartitionType_PartitionTypeId] PRIMARY KEY CLUSTERED ([PartitionTypeId] ASC)
12    ) ;
13
14    INSERT INTO [PartitionType] ([PartitionTypeName], [PartitionTypeValue])
15    VALUES 
16        ('1024 Block Ranges', 1024)
17        ,('2048 Block Ranges', 2048);
18END
19GO
20
21IF OBJECT_ID('[PartitionBoundary]', 'U') IS NULL
22BEGIN
23    CREATE TABLE [PartitionBoundary] (
24        [PartitionBoundaryId] [bigint] IDENTITY(1,1)
25        ,[PartitionTypeId] [tinyint] NOT NULL
26        ,[BoundaryBeginRange] [bigint] NOT NULL
27        ,[BoundaryEndRange] [bigint] NOT NULL
28        ,CONSTRAINT [PK_PartitionBoundary_PartitionBoundaryId] PRIMARY KEY CLUSTERED ([PartitionBoundaryId] ASC)
29        ,CONSTRAINT [FK_PartitionBoundary_PartitionType_PartitionTypeId] FOREIGN KEY ([PartitionTypeId]) REFERENCES [PartitionType]([PartitionTypeId])
30    ) ;
31    CREATE UNIQUE NONCLUSTERED INDEX [UQ_PartitionBoundary_BoundaryRange] ON [PartitionBoundary]([PartitionTypeId],[BoundaryBeginRange],[BoundaryEndRange]) ;
32
33    INSERT INTO [PartitionBoundary] ([PartitionTypeId], [BoundaryBeginRange], [BoundaryEndRange])
34    VALUES
35        -- Increment by 1024.
36        (1, 0, 1024)
37        ,(1, 1025, 2049)
38        -- Increment by 2048.
39        ,(2, 0, 2048)
40        ,(2, 2049, 4097);
41END
42GO
43
44CREATE TRIGGER [TR_PartitionBoundary_Allocation] ON [PartitionBoundary]
45INSTEAD OF INSERT
46AS
47DECLARE @PartitionTypeId tinyint;
48DECLARE @PartitionTypeValue int;
49DECLARE @BoundaryBegin bigint;
50DECLARE @BoundaryEnd bigint;
51BEGIN TRY
52
53    SELECT TOP (1) @PartitionTypeId = [PartitionTypeId] FROM inserted
54    SELECT TOP (1) @PartitionTypeValue = [PartitionTypeValue] FROM [PartitionType] WITH(TABLOCKX) WHERE [PartitionTypeId] = @PartitionTypeId
55
56    -- Set the boundary ranges.
57    SELECT TOP (1) @BoundaryBegin = MAX(BoundaryBeginRange) + @PartitionTypeValue FROM [PartitionBoundary] WITH(TABLOCKX) WHERE [PartitionTypeId] = @PartitionTypeId
58    SELECT TOP (1) @BoundaryEnd = MAX([BoundaryEndRange]) + @PartitionTypeValue FROM [PartitionBoundary] WITH(TABLOCKX) WHERE [PartitionTypeId] = @PartitionTypeId
59
60    -- Verify if the range exists for the partition type.
61    IF NOT EXISTS (SELECT TOP (1) [PartitionBoundaryId] FROM [PartitionBoundary] WHERE [PartitionTypeId] = @PartitionTypeId AND [BoundaryBeginRange] = @BoundaryBegin AND [BoundaryEndRange] = @BoundaryEnd)
62    BEGIN
63
64        INSERT INTO [PartitionBoundary] ([PartitionTypeId], [BoundaryBeginRange], [BoundaryEndRange])
65            SELECT [PartitionTypeId], @BoundaryBegin, @BoundaryEnd
66            FROM inserted;
67
68    IF @@ROWCOUNT <> 1
69    BEGIN
70        RAISERROR('Only one row must be inserted at a time.',16,1);
71    END;
72
73    END
74END TRY
75BEGIN CATCH;
76    THROW;
77END CATCH;
78GO
79
80-- Insert incrementing values.
81INSERT INTO [PartitionBoundary] ([PartitionTypeId])
82    VALUES (1);
83INSERT INTO [PartitionBoundary] ([PartitionTypeId])
84    VALUES (1);
85INSERT INTO [PartitionBoundary] ([PartitionTypeId])
86    VALUES (2);
87INSERT INTO [PartitionBoundary] ([PartitionTypeId])
88    VALUES (2);
89
90SELECT
91    *
92FROM
93    [PartitionBoundary]