/* Create some tables to work with */CREATE TABLE dbo.orders(      order_id      INT IDENTITY(1,1)   Not Null    , orderDate     DATETIME            Not Null    , customer_id   INT                 Not Null     CONSTRAINT PK_orders        PRIMARY KEY CLUSTERED(order_id)); CREATE TABLE dbo.orderDetails(      orderDetail_id    INT IDENTITY(1,1)   Not Null    , order_id          INT                 Not Null    , lineItem          INT                 Not Null    , product_id        INT                 Not Null     CONSTRAINT PK_orderDetails        PRIMARY KEY CLUSTERED(orderDetail_id)     CONSTRAINT FK_orderDetails_orderID        FOREIGN KEY(order_id)        REFERENCES dbo.orders(order_id));Go /* Create a new procedure using an XML parameter */CREATE PROCEDURE dbo.insert_orderXML_sp      @orderDate        DATETIME    , @customer_id      INT    , @orderDetailsXML  XMLASBEGIN     SET NOCOUNT ON;     DECLARE @myOrderID INT;     INSERT INTO dbo.orders    (          orderDate        , customer_id        )    VALUES    (          @orderDate        , @customer_id    );     SET @myOrderID = SCOPE_IDENTITY();     INSERT INTO dbo.orderDetails    (          order_id        , lineItem        , product_id    )    SELECT @myOrderID         , myXML.VALUE('./@lineItem', 'int')         , myXML.VALUE('./@product_id', 'int')    FROM @orderDetailsXML.nodes('/orderDetail') AS nodes(myXML);     SET NOCOUNT OFF; ENDGO  /* Call our stored procedure */EXECUTE dbo.insert_orderXML_sp      @orderDate = '2008-01-01'    , @customer_id = 101    , @orderDetailsXML =         '<orderDetail lineItem="1" product_id="123" />         <orderDetail lineItem="2" product_id="456" />         <orderDetail lineItem="3" product_id="789" />         <orderDetail lineItem="4" product_id="246" />         <orderDetail lineItem="5" product_id="135" />';  /* Check our data */SELECT * FROM dbo.orders;SELECT * FROM dbo.orderDetails;  /* Clean up our mess */DROP PROCEDURE insert_orderXML_sp;DROP TABLE dbo.orderDetails;DROP TABLE dbo.orders;

IF EXISTS(SELECT OBJECT_ID FROM sys.tables                WHERE [name] = N'dba_indexDefragLog')BEGIN     DROP TABLE dbo.dba_indexDefragLog;     PRINT 'dba_indexDefragLog table dropped!'; END CREATE TABLE dbo.dba_indexDefragLog(      indexDefrag_id    INT IDENTITY(1,1)   NOT NULL    , objectID          INT                 NOT NULL    , objectName        NVARCHAR(130)       NOT NULL    , indexID           INT                 NOT NULL    , indexName         NVARCHAR(130)       NOT NULL    , partitionNumber   SMALLINT            not null    , fragmentation     FLOAT               NOT NULL    , page_count        INT                 NOT NULL    , dateTimeStart     DATETIME            NOT NULL    , durationSeconds   INT                 NOT NULL    CONSTRAINT PK_indexDefragLog        PRIMARY KEY CLUSTERED (indexDefrag_id)); PRINT 'dba_indexDefragLog Table Created'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'),        N'IsProcedure') IS NullBEGIN    EXECUTE ('Create Procedure dbo.dba_indexDefrag_sp        As Print ''Hello World!''');    RAISERROR('Procedure dba_indexDefrag_sp created.'        , 10, 1);END;Go SET ANSI_Nulls ON;SET Ansi_Padding ON;SET Ansi_Warnings ON;SET ArithAbort ON;SET Concat_Null_Yields_Null ON;SET NOCOUNT ON;SET Numeric_RoundAbort OFF;SET Quoted_Identifier ON;Go ALTER PROCEDURE dbo.dba_indexDefrag_sp     /* Declare Parameters */      @minFragmentation     FLOAT           = 10.0        /* in percent, will not defrag if fragmentation           less than specified */    , @rebuildThreshold     FLOAT           = 30.0        /* in percent, greater than @rebuildThreshold           will result in rebuild instead of reorg */    , @onlineRebuild        BIT             = 1             /* 1 = online rebuild; 0 = offline rebuild */    , @executeSQL           BIT             = 1        /* 1 = execute; 0 = print command only */    , @tableName            VARCHAR(4000)   = Null         /* Option to specify a table name */    , @printCommands        BIT             = 0        /* 1 = print commands; 0 = do not print commands */    , @printFragmentation   BIT             = 0        /* 1 = print fragmentation prior to defrag;            0 = do not print */    , @defragDelay          CHAR(8)         = '00:00:05'        /* time to wait between defrag commands */AS/********************************************************************    Name:       dba_indexDefrag_sp     Author:     Michelle F. Ufford     Purpose:    Defrags all indexes for the current database     Notes:      This script was designed for SQL Server 2005                Enterprise Edition.     CAUTION: Monitor transaction log if executing for the first time!       @minFragmentation     defaulted to 10%, will not defrag if                            fragmentation if less than specified.       @rebuildThreshold     defaulted to 30% as recommended by                            Microsoft in BOL;                            > than 30% will result in rebuild instead       @onlineRebuild        1 = online rebuild;                             0 = offline rebuild       @executeSQL           1 = execute the SQL generated by this proc;                            0 = print command only       @tableName            Specify if you only want to defrag indexes                            for a specific table       @printCommands        1 = print commands to screen;                            0 = do not print commands       @printFragmentation   1 = print fragmentation to screen;                            0 = do not print fragmentation       @defragDelay          time to wait between defrag commands;                            gives the server some time to catch up     Called by:  SQL Agent Job or DBA     Date        Initials  Description    ----------------------------------------------------------------    2008-10-27  MFU       Initial Release    2008-11-17  MFU       Added page_count to log table                          , added @printFragmentation option********************************************************************    Exec dbo.dba_indexDefrag_sp          @executeSQL         = 1        , @printCommands      = 1        , @minFragmentation   = 0        , @printFragmentation = 1;********************************************************************/ SET NOCOUNT ON;SET XACT_Abort ON; BEGIN     /* Declare our variables */    DECLARE   @objectID         INT            , @indexID          INT            , @partitionCount   BIGINT            , @schemaName       NVARCHAR(130)            , @objectName       NVARCHAR(130)            , @indexName        NVARCHAR(130)            , @partitionNumber  SMALLINT            , @partitions       SMALLINT            , @fragmentation    FLOAT            , @pageCount        INT            , @sqlCommand       NVARCHAR(4000)            , @rebuildCommand   NVARCHAR(200)            , @dateTimeStart    DATETIME            , @dateTimeEnd      DATETIME            , @containsLOB      BIT;     /* Just a little validation... */    IF @minFragmentation Not Between 0.00 And 100.0        SET @minFragmentation = 10.0;     IF @rebuildThreshold Not Between 0.00 And 100.0        SET @rebuildThreshold = 30.0;     IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'        SET @defragDelay = '00:00:05';     /* Determine which indexes to defrag using our       user-defined parameters */    SELECT          OBJECT_ID AS objectID        , index_id AS indexID        , partition_number AS partitionNumber        , avg_fragmentation_in_percent AS fragmentation        , page_count         , 0 AS 'defragStatus'            /* 0 = unprocessed, 1 = processed */    INTO #indexDefragList    FROM sys.dm_db_index_physical_stats        (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')    WHERE avg_fragmentation_in_percent > @minFragmentation        And index_id > 0    OPTION (MaxDop 1);     /* Create a clustered index to boost performance a little */    CREATE CLUSTERED INDEX CIX_temp_indexDefragList        ON #indexDefragList(objectID, indexID, partitionNumber);     /* Begin our loop for defragging */    WHILE (SELECT COUNT(*) FROM #indexDefragList            WHERE defragStatus = 0) > 0    BEGIN         /* Grab the most fragmented index first to defrag */        SELECT TOP 1              @objectID         = objectID            , @fragmentation    = fragmentation            , @indexID          = indexID            , @partitionNumber  = partitionNumber            , @pageCount        = page_count        FROM #indexDefragList        WHERE defragStatus = 0        ORDER BY fragmentation DESC;         /* Look up index information */        SELECT @objectName = QUOTENAME(o.name)             , @schemaName = QUOTENAME(s.name)        FROM sys.objects AS o        INNER Join sys.schemas AS s            ON s.schema_id = o.schema_id        WHERE o.OBJECT_ID = @objectID;         SELECT @indexName = QUOTENAME(name)        FROM sys.indexes        WHERE OBJECT_ID = @objectID            And index_id = @indexID            And type > 0;         /* Determine if the index is partitioned */        SELECT @partitionCount = COUNT(*)        FROM sys.partitions        WHERE OBJECT_ID = @objectID            And index_id = @indexID;         /* Look for LOBs */        SELECT TOP 1            @containsLOB = column_id        FROM sys.columns WITH (NOLOCK)        WHERE             [OBJECT_ID] = @objectID            And (system_type_id In (34, 35, 99)            -- 34 = image, 35 = text, 99 = ntext                    Or max_length = -1);            -- varbinary(max), varchar(max), nvarchar(max), xml         /* See if we should rebuild or reorganize; handle thusly */        IF @fragmentation < @rebuildThreshold And @partitionCount <= 1            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '                + @schemaName + N'.' + @objectName + N' ReOrganize';         IF @fragmentation >= @rebuildThreshold            And IsNull(@containsLOB, 0) = 0                -- Cannot rebuild if the table has one or more LOB            And @partitionCount <= 1        BEGIN             /* We should always rebuild online if possible                (SQL 2005 Enterprise) */            IF @onlineRebuild = 0                SET @rebuildCommand = N' Rebuild With                    (Online = Off, MaxDop = 1)';            ELSE                SET @rebuildCommand = N' Rebuild With                    (Online = On, MaxDop = 1)';             SET @sqlCommand = N'Alter Index ' + @indexName + N' On '                + @schemaName + N'.' + @objectName + @rebuildCommand;        END;         /* If our index is partitioned, we should always reorganize */        IF @partitionCount > 1            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '                + @schemaName + N'.' + @objectName + N' ReOrganize'                + N' Partition = '                + CAST(@partitionNumber AS NVARCHAR(10));                -- no MaxDop needed, single threaded operation         /* Are we executing the SQL?  If so, do it */        IF @executeSQL = 1        BEGIN             /* Grab the time for logging purposes */            SET @dateTimeStart  = GETDATE();            EXECUTE (@sqlCommand);            SET @dateTimeEnd  = GETDATE();             /* Log our actions */            INSERT INTO dbo.dba_indexDefragLog            (                  objectID                , objectName                , indexID                , indexName                , partitionNumber                , fragmentation                , page_count                , dateTimeStart                , durationSeconds            )            SELECT                  @objectID                , @objectName                , @indexID                , @indexName                , @partitionNumber                , @fragmentation                , @pageCount                , @dateTimeStart                , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);             /* Just a little breather for the server */            WAITFOR Delay @defragDelay;             /* Print if specified to do so */            IF @printCommands = 1                PRINT N'Executed: ' + @sqlCommand;        END        ELSE        /* Looks like we're not executing, just print            the commands */        BEGIN            IF @printCommands = 1                PRINT @sqlCommand;        END         /* Update our index defrag list when we've            finished with that index */        UPDATE #indexDefragList        SET defragStatus = 1        WHERE objectID         = @objectID          And indexID          = @indexID          And partitionNumber  = @partitionNumber;     END     /* Do we want to output our fragmentation results? */    IF @printFragmentation = 1        SELECT idl.objectID            , o.name AS 'tableName'            , idl.indexID            , i.name AS 'indexName'            , idl.fragmentation            , idl.page_count        FROM #indexDefragList AS idl        Join sys.objects AS o            ON idl.objectID = o.OBJECT_ID        Join sys.indexes AS i            ON idl.objectID = i.OBJECT_ID            And idl.indexID = i.index_id;     /* When everything is done, make sure to get rid of        our temp table */    DROP TABLE #indexDefragList;     SET NOCOUNT OFF;    RETURN 0ENDGo

IF EXISTS(SELECT OBJECT_ID FROM sys.tables                WHERE [name] = N'dba_indexDefragLog')BEGIN     DROP TABLE dbo.dba_indexDefragLog;     PRINT 'dba_indexDefragLog table dropped!'; END CREATE TABLE dbo.dba_indexDefragLog(      indexDefrag_id    INT IDENTITY(1,1)   NOT NULL    , objectID          INT                 NOT NULL    , objectName        NVARCHAR(130)       NOT NULL    , indexID           INT                 NOT NULL    , indexName         NVARCHAR(130)       NOT NULL    , fragmentation     FLOAT               NOT NULL    , page_count        INT                 NOT NULL    , dateTimeStart     DATETIME            NOT NULL    , durationSeconds   INT                 NOT NULL    CONSTRAINT PK_indexDefragLog        PRIMARY KEY CLUSTERED (indexDefrag_id)); PRINT 'dba_indexDefragLog Table Created'; IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'),        N'IsProcedure') IS NullBEGIN    EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp        As Print ''Hello World!''');    RAISERROR('Procedure dba_indexDefragStandard_sp created.'        , 10, 1);END;Go SET ANSI_Nulls ON;SET Ansi_Padding ON;SET Ansi_Warnings ON;SET ArithAbort ON;SET Concat_Null_Yields_Null ON;SET NOCOUNT ON;SET Numeric_RoundAbort OFF;SET Quoted_Identifier ON;Go ALTER PROCEDURE dbo.dba_indexDefragStandard_sp     /* Declare Parameters */      @minFragmentation     FLOAT           = 10.0        /* in percent, will not defrag if fragmentation           less than specified */    , @rebuildThreshold     FLOAT           = 30.0        /* in percent, greater than @rebuildThreshold           will result in rebuild instead of reorg */    , @executeSQL           BIT             = 1        /* 1 = execute; 0 = print command only */    , @tableName            VARCHAR(4000)   = Null         /* Option to specify a table name */    , @printCommands        BIT             = 0        /* 1 = print commands; 0 = do not print commands */    , @printFragmentation   BIT             = 0        /* 1 = print fragmentation prior to defrag;            0 = do not print */    , @defragDelay          CHAR(8)         = '00:00:05'        /* time to wait between defrag commands */AS/********************************************************************    Name:       dba_indexDefragStandard_sp     Author:     Michelle F. Ufford     Purpose:    Defrags all indexes for the current database     Notes:      This script was designed for SQL Server 2005                Standard edition.     CAUTION: Monitor transaction log if executing for the first time!       @minFragmentation     defaulted to 10%, will not defrag if                            fragmentation if less than specified.       @rebuildThreshold     defaulted to 30% as recommended by                            Microsoft in BOL;                            > than 30% will result in rebuild instead       @executeSQL           1 = execute the SQL generated by this proc;                            0 = print command only       @tableName            Specify if you only want to defrag indexes                            for a specific table       @printCommands        1 = print commands to screen;                            0 = do not print commands       @printFragmentation   1 = print fragmentation to screen;                            0 = do not print fragmentation       @defragDelay          time to wait between defrag commands;                            gives the server some time to catch up     Called by:  SQL Agent Job or DBA     Date        Initials  Description    ----------------------------------------------------------------    2008-10-27  MFU       Initial Release    2008-11-17  MFU       Added page_count to log table                          , added @printFragmentation option********************************************************************    Exec dbo.dba_indexDefragStandard_sp          @executeSQL         = 1        , @printCommands      = 1        , @minFragmentation   = 0        , @printFragmentation = 1;********************************************************************/ SET NOCOUNT ON;SET XACT_Abort ON; BEGIN     /* Declare our variables */    DECLARE   @objectID         INT            , @indexID          INT            , @schemaName       NVARCHAR(130)            , @objectName       NVARCHAR(130)            , @indexName        NVARCHAR(130)            , @fragmentation    FLOAT            , @pageCount        INT            , @sqlCommand       NVARCHAR(4000)            , @rebuildCommand   NVARCHAR(200)            , @dateTimeStart    DATETIME            , @dateTimeEnd      DATETIME            , @containsLOB      BIT;     /* Just a little validation... */    IF @minFragmentation Not Between 0.00 And 100.0        SET @minFragmentation = 10.0;     IF @rebuildThreshold Not Between 0.00 And 100.0        SET @rebuildThreshold = 30.0;     IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'        SET @defragDelay = '00:00:05';     /* Determine which indexes to defrag using our       user-defined parameters */    SELECT          OBJECT_ID AS objectID        , index_id AS indexID        , avg_fragmentation_in_percent AS fragmentation        , page_count         , 0 AS 'defragStatus'            /* 0 = unprocessed, 1 = processed */    INTO #indexDefragList    FROM sys.dm_db_index_physical_stats        (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')    WHERE avg_fragmentation_in_percent > @minFragmentation        And index_id > 0    OPTION (MaxDop 1);     /* Create a clustered index to boost performance a little */    CREATE CLUSTERED INDEX CIX_temp_indexDefragList        ON #indexDefragList(objectID, indexID);     /* Begin our loop for defragging */    WHILE (SELECT COUNT(*) FROM #indexDefragList            WHERE defragStatus = 0) > 0    BEGIN         /* Grab the most fragmented index first to defrag */        SELECT TOP 1              @objectID         = objectID            , @fragmentation    = fragmentation            , @indexID          = indexID            , @pageCount        = page_count        FROM #indexDefragList        WHERE defragStatus = 0        ORDER BY fragmentation DESC;         /* Look up index information */        SELECT @objectName = QUOTENAME(o.name)             , @schemaName = QUOTENAME(s.name)        FROM sys.objects AS o        INNER Join sys.schemas AS s            ON s.schema_id = o.schema_id        WHERE o.OBJECT_ID = @objectID;         SELECT @indexName = QUOTENAME(name)        FROM sys.indexes        WHERE OBJECT_ID = @objectID            And index_id = @indexID            And type > 0;         /* Look for LOBs */        SELECT TOP 1            @containsLOB = column_id        FROM sys.columns WITH (NOLOCK)        WHERE             [OBJECT_ID] = @objectID            And (system_type_id In (34, 35, 99)            -- 34 = image, 35 = text, 99 = ntext                    Or max_length = -1);            -- varbinary(max), varchar(max), nvarchar(max), xml         /* See if we should rebuild or reorganize; handle thusly */        IF @fragmentation < @rebuildThreshold             Or IsNull(@containsLOB, 0) > 0             -- Cannot rebuild if the table has one or more LOB            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '                + @schemaName + N'.' + @objectName + N' ReOrganize;'        ELSE            SET @sqlCommand = N'Alter Index ' + @indexName + N' On '                + @schemaName + N'.' + @objectName +  ' Rebuild '                + 'With (MaxDop = 1)'; -- minimize impact on server         /* Are we executing the SQL?  If so, do it */        IF @executeSQL = 1        BEGIN             /* Grab the time for logging purposes */            SET @dateTimeStart  = GETDATE();            EXECUTE (@sqlCommand);            SET @dateTimeEnd  = GETDATE();             /* Log our actions */            INSERT INTO dbo.dba_indexDefragLog            (                  objectID                , objectName                , indexID                , indexName                , fragmentation                , page_count                , dateTimeStart                , durationSeconds            )            SELECT                  @objectID                , @objectName                , @indexID                , @indexName                , @fragmentation                , @pageCount                , @dateTimeStart                , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);             /* Just a little breather for the server */            WAITFOR Delay @defragDelay;             /* Print if specified to do so */            IF @printCommands = 1                PRINT N'Executed: ' + @sqlCommand;        END        ELSE        /* Looks like we're not executing, just print            the commands */        BEGIN            IF @printCommands = 1                PRINT @sqlCommand;        END         /* Update our index defrag list when we've            finished with that index */        UPDATE #indexDefragList        SET defragStatus = 1        WHERE objectID  = @objectID          And indexID   = @indexID;     END     /* Do we want to output our fragmentation results? */    IF @printFragmentation = 1        SELECT idl.objectID            , o.name AS 'tableName'            , idl.indexID            , i.name AS 'indexName'            , idl.fragmentation            , idl.page_count        FROM #indexDefragList AS idl        JOIN sys.objects AS o            ON idl.objectID = o.OBJECT_ID        JOIN sys.indexes AS i            ON idl.objectID = i.OBJECT_ID            AND idl.indexID = i.index_id;     /* When everything is done, make sure to get rid of        our temp table */    DROP TABLE #indexDefragList;     SET NOCOUNT OFF;    RETURN 0ENDGo

    EXEC dbo.dba_indexDefrag_sp          @executeSQL    = 0        , @printCommands = 1        , @minFragmentation = 0        , @printFragmentation = 1;

    EXEC dbo.dba_indexDefrag_sp       @executeSQL    = 0        , @printCommands = 1        , @printFragmentation = 1;

---------------------------------- Create A Partitioned Table ---------------------------------- /* Create a partition function. */CREATE Partition FUNCTION [test_monthlyDateRange_pf] (SMALLDATETIME)     AS Range RIGHT FOR VALUES     ('2008-01-01', '2008-02-01', '2008-03-01');Go /* Associate the partition function with a partition scheme. */CREATE Partition Scheme test_monthlyDateRange_ps     AS Partition test_monthlyDateRange_pf     All TO ([PRIMARY]);Go /* Create your first partitioned table!    Make sure the data types match.  */CREATE TABLE dbo.orders(      order_id  INT IDENTITY(1,1)   Not Null    , orderDate SMALLDATETIME       Not Null     CONSTRAINT PK_orders PRIMARY KEY CLUSTERED    (        orderDate      , order_id    )) ON test_monthlyDateRange_ps(orderDate);Go /* Create some records to play with. */INSERT INTO dbo.ordersSELECT '2007-12-31' UNION AllSELECT '2008-01-02' UNION AllSELECT '2008-01-03' UNION AllSELECT '2008-01-04' UNION AllSELECT '2008-02-01' UNION AllSELECT '2008-02-02' UNION AllSELECT '2008-03-01' UNION AllSELECT '2008-03-02'; /* The $partition function can be used to interrogate partition data.   Let's use it to see where those records are physically located. */SELECT $partition.test_monthlyDateRange_pf(orderDate)     AS 'partition_number'    , *FROM dbo.orders; /* By default, all new indexes are created on the partition.   Let's create an aligned index */CREATE NONCLUSTERED INDEX IX_orders_aligned    ON dbo.orders(order_id)    ON test_monthlyDateRange_ps(orderDate); /* Now let's create an un-aligned index.   We'll need to specify the filegroup. */CREATE NONCLUSTERED INDEX IX_orders_nonaligned    ON dbo.orders(order_id)    ON [PRIMARY]; -- can be any filegroup /* Review your indexes */EXECUTE SP_HELPINDEX orders;

---------------------------- Swap Out A Partition ---------------------------- /* We need to drop our un-aligned index; otherwise we'll    get an error when we attempt to do the switch. */DROP INDEX IX_orders_nonaligned ON dbo.orders; /* Create the table to hold the data you're swapping out.   The table structures must match identically; however,    DO NOT partition this table. */CREATE TABLE dbo.orders_stage_swapOut(      order_id  INT             Not Null    , orderDate SMALLDATETIME   Not Null     CONSTRAINT PK_orders_stage_swapOut PRIMARY KEY CLUSTERED    (        orderDate      , order_id    )) ON [PRIMARY];Go /* Create the table to hold the data you're swapping in.   The table structures must match identically; however,   DO NOT partition this table. */CREATE TABLE dbo.orders_stage_swapIn(      order_id  INT             Not Null    , orderDate SMALLDATETIME   Not Null     CONSTRAINT PK_orders_stage_swapIn PRIMARY KEY CLUSTERED    (        orderDate      , order_id    )) ON [PRIMARY];Go /* Populate the table you're swapping in. */INSERT INTO dbo.orders_stage_swapInSELECT -5, '2008-02-02' UNION AllSELECT -4, '2008-02-03' UNION AllSELECT -3, '2008-02-04' UNION AllSELECT -2, '2008-02-05' UNION AllSELECT -1, '2008-02-06'; /* Create any indexes on your table to match the    indexes on your partitioned table. */CREATE NONCLUSTERED INDEX IX_orders_stage_swapIn    ON dbo.orders_stage_swapIn(order_id); /* Add a check constraint for the    partition to be swapped in.    This step is required. */ALTER TABLE dbo.orders_stage_swapInWITH CHECKADD CONSTRAINT orders_stage_swapIn_orderDateCK CHECK (orderDate >= '2008-02-01'   And orderDate < '2008-03-01');Go /* Swap out the old partition. */ALTER TABLE dbo.orders     Switch Partition 3 TO dbo.orders_stage_swapOut;Go /* Swap in the new partition. */ALTER TABLE dbo.orders_stage_swapIn    Switch TO dbo.orders Partition 3;Go ----------------------- Check your data ----------------------- /* You should have 2 records in here. */SELECT * FROM dbo.orders_stage_swapOut; /* You should have 5 records here. */SELECT * FROM dbo.ordersWHERE orderDate >= '2008-02-01' And orderDate < '2008-03-01'; /* There should be no records in this table. */SELECT * FROM dbo.orders_stage_swapIn; SELECT $partition.test_monthlyDateRange_pf(orderDate)     AS 'partition_number'    , *FROM dbo.orders; /* Clean-up time!Drop Table dbo.ordersDrop Table dbo.orders_stage_swapOutDrop Table dbo.orders_stage_swapInDrop Partition Scheme test_monthlyDateRange_psDrop Partition Function [test_monthlyDateRange_pf]*/

DECLARE @schema_owner VARCHAR(20);SET @schema_owner = 'dbo'; SELECT [name] AS 'storedProcedure', 'Grant Execute On '    + @schema_owner + '.' + [name]    + ' To [insertDatabaseRoleHere];' AS 'sqlCode'FROM sys.objects WITH (NoLock)WHERE [name] Not In (    SELECT o.name    FROM sys.database_permissions p WITH (NoLock)    INNER Join sys.objects o WITH (NoLock)        ON p.major_id = o.OBJECT_ID    INNER Join sys.database_principals u WITH (NoLock)        ON u.principal_id = p.grantee_principal_id    WHERE u.name = '[insertDatabaseRoleHere]')And [type] = 'P';