none
How to switch data from a Table A to Table B when there is no query running on Table B

    질문

  • i have 2 Table A, B, which have the same columns. Table B is Used for Tableau Reports. Table A is a temporary Table which has new Data from source System. How to switch the Data from Table A to Table B when there is no Query running on Table B?

    i need to do that to avoid downtime on Table B and make sure that Table B is always available for Users Thankyou very much!

    sample partition and switch partition:

    Query 1:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]
    (
    @PFName sysname,
    @PartitionNumber INT
    )
    RETURNS SQL_VARIANT
    AS
    BEGIN
    /***************************************************************************
    Procedure : dbo.fn_GetPartitionRangeValueForPartitionFunctionAndNumber
    Created By : Saru Radhakrishnan

    Purpose : To get the partition value for a given partition number and 
      given partition function

    Modification History:
    Date Name Comment
    ----------------------------------------------------------------------------
    07/07/2011 Saru Radhakrishnan Initial version. 
    ****************************************************************************/

    DECLARE @Value SQL_VARIANT

    IF @PartitionNumber > 0
    BEGIN
    SELECT @Value = prv.value
      FROM sys.partition_range_values prv
      JOIN sys.partition_functions pf
    ON pf.function_id = prv.function_id
    WHERE pf.name = @PFName
       AND prv.boundary_id = @PartitionNumber
    END

    RETURN @Value
    END

    GO

    Query 2:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
    (
    @PFName sysname,
    @Value varchar(20)
    )
    RETURNS INT
    AS
    BEGIN
    /**************************************************************************
    Procedure : dbo.fn_GetPartitionNumberForPartitionFunctionAndValue
    Created By : Saru Radhakrishnan

    Purpose : To get the partition number for a given partition function and
      its value

    Modification History:
    Date Name Comment
    ----------------------------------------------------------------------------------------------------------
    07/07/2011 Saru Radhakrishnan Initial version. 
    ***********************************************************************************************************/

    DECLARE @PartitionNumber INT

    IF ISDATE(@Value) = 1 AND ISNUMERIC(@Value) = 0
    BEGIN
    SELECT @PartitionNumber = prv.boundary_id
      FROM sys.partition_range_values prv
      JOIN sys.partition_functions pf
    ON pf.function_id = prv.function_id
    WHERE pf.name = @PFName
       AND prv.value <= CONVERT(SMALLDATETIME, @Value)
       AND prv.value > DATEADD(DD, -1, CONVERT(SMALLDATETIME, @Value))    
    END

    IF ISNUMERIC(@Value) = 1
    BEGIN
    SELECT @PartitionNumber = ROW_NUMBER() OVER (ORDER BY prv.value ASC)
      FROM sys.partition_range_values prv
      JOIN sys.partition_functions pf
    ON pf.function_id = prv.function_id
    WHERE pf.name = @PFName
       AND prv.value <= CONVERT(INT, @Value)
    END

    RETURN @PartitionNumber
    END

    GO

    Query 3: 

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_CreateORGetPartitionNumber]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_CreateORGetPartitionNumber]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[Proc_CreateORGetPartitionNumber]
    (
    @TableName sysname,
    @DateValue DATE = NULL,
    @SmallDateTimeValue SMALLDATETIME = NULL,
    @PartitionNumber INT = NULL OUTPUT
    )
    AS
    BEGIN
    /*
    ** Name : dbo.Proc_CreateORGetPartitionNumber
    ** Purpose : For any table that is partitioned, this proc either creates a new
    **   partition based on the partitioned column (if one does not already
    **   exists) or obtains the partitioned number of an already existing
    **   partition based on the partitioned column, using the input parameter
    **   value.  Finally returns the partition number to the caller.
    **
    ** Exmaple : DECLARE @PartitionNumber INT
    **   EXEC dbo.Proc_CreateORGetPartitionNumber @TableName = 'dbo.SalesPartiotionTable',
    **    @DateValue = '07/01/2011',
    **    @PartitionNumber = @PartitionNumber OUTPUT
    **
    ** Modification History
    ** Date Name Comment
    ** --------------------------------------------------------------------------------------------------------------
    ** 07/07/2011 Saru Radhakrishnan Initial Version
    ** --------------------------------------------------------------------------------------------------------------
    */

    SET NOCOUNT ON

    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT
    DECLARE @ExecutingProcedure VARCHAR(100)
    DECLARE @ErrorNumber INT
    DECLARE @ErrorMessage VARCHAR(256)
    DECLARE @ErrorProcedure VARCHAR(100)
    DECLARE @ErrorLine INT

    BEGIN TRY

    -- Pricing.ResultHistory
    IF @TableName = 'dbo.SalesPartitionTable'
    BEGIN
    IF @DateValue IS NOT NULL
    BEGIN
    SELECT @PartitionNumber = [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
    (
    'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
    @DateValue
    )

    IF @PartitionNumber IS NULL  -- means we need to create a new partition
    BEGIN
    ALTER PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek()
    SPLIT RANGE (@DateValue)

    ALTER PARTITION SCHEME PS_dbo_SalesPartitionTable_SalesDate_ByWeek
    NEXT USED [PRIMARY]

    SELECT @PartitionNumber = [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
    (
    'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
    @DateValue
    )
    END /* IF @PartitionNumber IS NULL */
    END /* IF @DateValue IS NOT NULL */
    END /* IF @TableName = 'dbo.SalesPartitionTable' */

    END TRY

    BEGIN CATCH
    SELECT @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorProcedure = ERROR_PROCEDURE(),
    @ErrorLine = ERROR_LINE()

    RAISERROR ('SQL Error trapped in Procedure:%s. Error:%d. Message:%s Procedure:%s. Line:%d.', 
    @ErrorSeverity, @ErrorState, @ExecutingProcedure, @ErrorNumber, @ErrorMessage, 
    @ErrorProcedure, @ErrorLine)
    END CATCH

    SET NOCOUNT OFF  
    END
    GO

    • 편집됨 Phalondon 2019년 5월 10일 금요일 오전 6:05
    2018년 6월 15일 금요일 오전 4:12

모든 응답

  • Hello,

    When the tables have exact the same structure incl indizes, then you can use ALTER TABLE PARTITION SWITCH , that works even in the Express Edition.

    The target table must be empty, so add a third empty table to Switch first to that one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    2018년 6월 15일 금요일 오전 5:51
  • See illustration here

    https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 15일 금요일 오전 6:41
  • In addition to the other posts, you can also do:

    BEGIN TRANSACTION

    EXEC sp_rename B, C
    EXEC sp_rename A, B
    EXEC sp_rename C, A

    COMMIT TRANSACTION

    Or if you don't want to keep the data in B:

    BEGIN TRANSACTION

    DROP TABLE B
    EXEC sp_rename A, B

    COMMIT TRANSACTION

    No matter how you do it, the table will be unavailable for a short moment, but that should be less than a second.

    2018년 6월 15일 금요일 오전 7:08
  • If there is a process in place to know when to switch, perhaps you can use a SYNONYM to point to the table you want used at the time, and query it instead.
    2018년 6월 15일 금요일 오전 11:24
    중재자
  • If there is a process in place to know when to switch, perhaps you can use a SYNONYM to point to the table you want used at the time, and query it instead.

    Absolutely the best idea so far!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2018년 6월 15일 금요일 오후 9:28
  • Wow, thank you. I consider that high praise coming from you.

    Fwiw, i was working on a project in Oracle where a batch process had to check a user list against an LDAP data file that we FTPed and loaded from a source every night. To keep everything running smoothly, we had two tables with a synonym. When the synonym pointed to A, at night time we loaded B and then changed the synonym to point to B. The following night we would load A and then point the synonym to A. (The process was automated.) This way the data was fresh, but the system never went down. Worked beautifully.

    2018년 6월 16일 토요일 오전 12:12
    중재자
  • Query 4

    -- The following TSQL script creates the non-partitioned SalesNormalTable 
    -- 'dbo.SalesNormalTable' and the necessary constraints and indexes
    IF OBJECT_ID('dbo.SalesNormalTable') IS NULL
    BEGIN
    CREATE TABLE dbo.SalesNormalTable
    (
    SalesDate DATE NOT NULL,
    StoreId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity DECIMAL(15, 6) NOT NULL,
    Revenue MONEY NOT NULL,
    SalesActivity NVARCHAR(MAX) NOT NULL
    )
    END
    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesNormalTable]') 
      AND name = N'PK_SalesNormalTable_ProductId_StoreId_SalesDate')
    BEGIN
    ALTER TABLE [dbo].[SalesNormalTable]
    ADD CONSTRAINT PK_SalesNormalTable_ProductId_StoreId_SalesDate
    PRIMARY KEY NONCLUSTERED (ProductId, StoreId, SalesDate)
    END
    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesNormalTable]') 
      AND name = N'UX_SalesNormalTable_SalesDate_StoreId_ProductId')
    BEGIN
    CREATE UNIQUE INDEX UX_SalesNormalTable_SalesDate_StoreId_ProductId
    ON dbo.SalesNormalTable (SalesDate, 
    StoreId, 
    ProductId)
    END
    GO

    2019년 5월 10일 금요일 오전 6:05
  • -- The following TSQL script populates the 'dbo.SalesNormalTable'
    -- The data represents weekly rolled-up of a specific transaction
    -- In other wards, a specific type of transaction each day of the
    -- week is rolled-up to represent one row

    INSERT INTO dbo.SalesNormalTable
    (SalesDate, StoreId, ProductId, Quantity, Revenue, SalesActivity)
    VALUES (DATEADD(WK, -11, GETDATE()), 100, 710, 100, 10000.00, 'Transactions Week1'),
       (DATEADD(WK, -10, GETDATE()), 100, 720, 200, 20000.00, 'Transactions Week2'),
       (DATEADD(WK, -9, GETDATE()), 100, 730, 300, 30000.00, 'Transactions Week3'),
       (DATEADD(WK, -8, GETDATE()), 100, 740, 400, 40000.00, 'Transactions Week4')
    GO

    INSERT INTO dbo.SalesNormalTable
    (SalesDate, StoreId, ProductId, Quantity, Revenue, SalesActivity)
    VALUES (DATEADD(WK, -7, GETDATE()), 200, 810, 500, 50000.00, 'Transactions Week5'),
       (DATEADD(WK, -6, GETDATE()), 200, 820, 600, 60000.00, 'Transactions Week6'),
       (DATEADD(WK, -5, GETDATE()), 200, 830, 700, 70000.00, 'Transactions Week7'),
       (DATEADD(WK, -4, GETDATE()), 200, 840, 800, 80000.00, 'Transactions Week8')
    GO

    INSERT INTO dbo.SalesNormalTable
    (SalesDate, StoreId, ProductId, Quantity, Revenue, SalesActivity)
    VALUES (DATEADD(WK, -3, GETDATE()), 300, 910, 900, 90000.00, 'Transactions Week9'),
       (DATEADD(WK, -1, GETDATE()), 300, 920, 800, 80000.00, 'Transactions Week10'),
       (GETDATE(), 300, 930, 700, 70000.00, 'Transactions Week11')
    GO
    2019년 5월 10일 금요일 오전 6:06
  • Query 6:

    -- The purpose of this TSQL file is to the following activities
    -- 1. If the non-partitioned Sales table exists, drop the primary key constraint
    --    and rename it
    -- 2. Obtain the MIN(SalesDate) from the renamed table
    --    and store it in a local variable
    -- 3. Create a new Partition Function using the local variable
    -- 4. Create a new Partition scheme based on the newly created partition function
    -- 5. Create the new Sales table with same column definition as the renamed table
    --    on the newly created Partition Scheme (This way the new table is partitioned
    --    based on the SalesDate)
    -- 6. Create the necessary primary key constraint and the index on the newly created
    --    partitioned sales table
    -- 7. Create the placeholder table with same column definition as the renamed table
    --    on the newly created Partition Scheme (This table will be used during partition switch)

    DECLARE @tmpSalesWeekDate DATE

    IF (
    (OBJECT_ID('[dbo].[SalesNormalTable]') IS NOT NULL)
    AND
    (OBJECT_ID('[dbo].[SalesNoramlTable_B4_Partition]') IS NULL)
       )
    BEGIN
    IF EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesNormalTable]') 
      AND name = N'PK_SalesNormalTable_ProductId_StoreId_SalesDate')
    BEGIN
    ALTER TABLE [dbo].[SalesNormalTable]
    DROP CONSTRAINT PK_SalesNormalTable_ProductId_StoreId_SalesDate
    END

    EXEC sp_rename 'dbo.SalesNormalTable', 'SalesNormalTable_B4_Partition'
    END

    IF OBJECT_ID('[dbo].[SalesNormalTable_B4_Partition]') IS NOT NULL
    BEGIN
    SELECT @tmpSalesWeekDate = ISNULL(MIN(SalesDate), '01/01/2010')
      FROM dbo.SalesNormalTable_B4_Partition WITH (NOLOCK)
    END

    IF @@ROWCOUNT > 0 AND @tmpSalesWeekDate IS NOT NULL
    BEGIN
    IF OBJECT_ID('[dbo].[SalesPartitionTable]') IS NOT NULL
    BEGIN
    DROP TABLE dbo.SalesPartitionTable
    END

    IF OBJECT_ID('[dbo].[TempSalesPartitionTable]') IS NOT NULL
    BEGIN
    DROP TABLE dbo.TempSalesPartitionTable
    END

    IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'PS_dbo_SalesPartitionTable_SalesDate_ByWeek')
    BEGIN
    DROP PARTITION SCHEME PS_dbo_SalesPartitionTable_SalesDate_ByWeek
    END

    IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'PF_dbo_SalesPartitionTable_SalesDate_ByWeek')
    BEGIN
    DROP PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek
    END

    IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'PF_dbo_SalesPartitionTable_SalesDate_ByWeek')
    BEGIN
    CREATE PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek (DATE)
    AS RANGE LEFT FOR VALUES (@tmpSalesWeekDate)
    END

    IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'PS_dbo_SalesPartitionTable_SalesDate_ByWeek')
    BEGIN
    CREATE PARTITION SCHEME PS_dbo_SalesPartitionTable_SalesDate_ByWeek
    AS PARTITION PF_dbo_SalesPartitionTable_SalesDate_ByWeek ALL TO ([PRIMARY])
    END

    -- Drop and Create dbo.SalesPartitionTable table
    IF OBJECT_ID('[dbo].[SalesPartitionTable]') IS NOT NULL
    BEGIN
    DROP TABLE dbo.SalesPartitionTable
    END

    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    CREATE TABLE [dbo].[SalesPartitionTable](
    SalesDate DATE NOT NULL,
    StoreId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity DECIMAL(15, 6) NOT NULL,
    Revenue MONEY NOT NULL,
    SalesActivity NVARCHAR(MAX) NOT NULL
    CONSTRAINT [PK_SalesPartitionTable_ProductId_StoreId_SalesDate] PRIMARY KEY NONCLUSTERED 
    (
    [ProductId] ASC,
    [StoreId] ASC,
    [SalesDate] ASC
    )
    ) ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)

    IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesPartitionTabl]') 
       AND name = N'UX_SalesPartitionTable_SalesDate_StoreId_ProductId')
    BEGIN    
    DROP INDEX UX_SalesPartitionTable_SalesDate_StoreId_ProductId 
    ON [dbo].[SalesPartitionTable] WITH ( ONLINE = OFF )
    END

    CREATE UNIQUE NONCLUSTERED INDEX UX_SalesPartitionTable_SalesDate_StoreId_ProductId 
    ON [dbo].[SalesPartitionTable] 
    (
    SalesDate, 
    StoreId, 
    ProductId
    )
    ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)

    -- Drop and Create dbo.TempSalesPartitionTable table
    IF OBJECT_ID('[dbo].[TempSalesPartitionTable]') IS NOT NULL
    BEGIN
    DROP TABLE dbo.TempSalesPartitionTable
    END

    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    CREATE TABLE [dbo].[TempSalesPartitionTable](
    SalesDate DATE NOT NULL,
    StoreId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity DECIMAL(15, 6) NOT NULL,
    Revenue MONEY NOT NULL,
    SalesActivity NVARCHAR(MAX) NOT NULL
    CONSTRAINT [PK_TempSalesPartitionTable_ProductId_StoreId_SalesDate] PRIMARY KEY NONCLUSTERED 
    (
    [ProductId] ASC,
    [StoreId] ASC,
    [SalesDate] ASC
    )
    ) ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)

    IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[TempSalesPartitionTable]') 
       AND name = N'UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId')
    BEGIN    
    DROP INDEX UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId 
    ON [dbo].[TempSalesPartitionTable] WITH ( ONLINE = OFF )
    END

    CREATE UNIQUE NONCLUSTERED INDEX UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId 
    ON [dbo].[TempSalesPartitionTable] 
    (
    [SalesDate] ASC,
    [StoreId] ASC,
    [ProductId] ASC
    )
    ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)
    END
    GO

    2019년 5월 10일 금요일 오전 6:06
  • Query 7:

     

    USE [master]
    GO

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetTablePartitionRowsInfo]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sp_GetTablePartitionRowsInfo]
    GO

    USE [master]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [dbo].[sp_GetTablePartitionRowsInfo]
    (
    @TableName VARCHAR(MAX) -- SchemaName.TablName
    )
    AS
    BEGIN
    /*
    ** Author : Saru Radhakrishnan
    **  Purpose : To provide partition info for a given partitioned table.
    **   1. It provides each partition value by which it is partitioned
    **      (For example by week or month) and no. of rows in each partition
    **   2. It also provides the no. of rows in the main and temporary
    ** table.  The temporary table has the exact same definition as the
    ** main table, except that the temporary table is used as temporary
    ** place holder for data manipulation (such as adding new data and/or
    ** drop/create indexes) before the data is pushed to main table.
    **
    **  Example run: EXEC sp_GetTableParitionRowsInfo 'dbo.SalesPartitionTable'
    **
    **  Modification History
    ** Date Name Comment
    **  -------------------------------------------------------------------------------------
    **  07/07/2011 Saru Radhakrishnan Initial version.
    **  -------------------------------------------------------------------------------------
    */
    DECLARE @SchemaNameOnly VARCHAR(MAX)
    DECLARE @TableNameOnly VARCHAR(MAX)
    DECLARE @Rowcount INT

    DECLARE @ErrorMsg1 VARCHAR(MAX)

    IF (CHARINDEX('.', @TableName)) = 0
    BEGIN
    SET @ErrorMsg1 = 'ERROR: The proc expects input parameter qualified with Schema name' + CHAR(13)
    SET @ErrorMsg1 = @ErrorMsg1 + 'For Example: EXEC sp_GetTableParitionRowsInfo ' + '''' + 'SchemaName.TableName' + ''''
    PRINT @ErrorMsg1
    RETURN
    END

    SET @SchemaNameOnly = SUBSTRING(@TableName, 1, CHARINDEX('.', @TableName) - 1)
    SET @TableNameOnly = SUBSTRING(@TableName, CHARINDEX('.', @TableName) + 1, 1000)

    -- To get list of partitions and the no. of rows in each partition
    -- for partitioned tables

    SELECT DISTINCT sc.name + '.' + so.name AS TableName, p.[rows], p.partition_number, prv.value
      INTO #tmpTable
      FROM sys.partitions p WITH (NOLOCK)
      JOIN sys.objects so WITH (NOLOCK)
    ON so.object_id = p.object_id
      JOIN sys.schemas sc WITH (NOLOCK)
    ON sc.schema_id = so.schema_id
      JOIN sys.partition_functions pf WITH (NOLOCK)
    ON pf.name LIKE '%' + @SchemaNameOnly + '%' + @TableNameOnly + '%'
      JOIN sys.partition_range_values prv WITH (NOLOCK)
    ON prv.function_id = pf.function_id
       AND prv.boundary_id = p.partition_number
    WHERE so.name = @TableNameOnly
       AND sc.name = @SchemaNameOnly
    ORDER BY p.partition_number, TableName

    SELECT @Rowcount = COUNT(*) FROM #tmpTable
    IF @Rowcount = 0
    BEGIN
    SELECT DISTINCT sc.name + '.' + so.name AS TableName, p.[rows], p.partition_number, prv.value
      FROM sys.partitions p WITH (NOLOCK)
      JOIN sys.objects so WITH (NOLOCK)
    ON so.object_id = p.object_id
      JOIN sys.schemas sc WITH (NOLOCK)
    ON sc.schema_id = so.schema_id
      JOIN sys.partition_functions pf WITH (NOLOCK)
    ON pf.name LIKE '%[_]' + @TableNameOnly + '[_]%'
      JOIN sys.partition_range_values prv WITH (NOLOCK)
    ON prv.function_id = pf.function_id
       AND prv.boundary_id = p.partition_number
    WHERE so.name = @TableNameOnly
       AND sc.name = @SchemaNameOnly
      ORDER BY p.partition_number, TableName
    END
    ELSE
    BEGIN
    SELECT * FROM #tmpTable
    END

    SET @TableNameOnly = 'Temp' + @TableNameOnly

    SELECT DISTINCT sc.name + '.' + so.name AS TableName, p.[rows], p.partition_number
      FROM sys.partitions p WITH (NOLOCK)
      JOIN sys.objects so WITH (NOLOCK)
    ON so.object_id = p.object_id
      JOIN sys.schemas sc WITH (NOLOCK)
    ON sc.schema_id = so.schema_id
    WHERE so.name = @TableNameOnly
       AND sc.name = @SchemaNameOnly
      ORDER BY p.partition_number, TableName
    END

    GO


    EXEC sp_ms_marksystemobject 'sp_GetTablePartitionRowsInfo'
    GO

    Query 8:

    -- The following script displays the partition information
    -- for the partitioned Sales and temporary place holder table
    -- such information as 
    -- 1. Each partition number
    -- 2. # of rows in each partition
    -- 3. Each partition range value
    IF OBJECT_ID('dbo.SalesPartitionTable') IS NOT NULL
    BEGIN
    EXEC sp_GetTablePartitionRowsInfo 'dbo.SalesPartitionTable'
    END
    GO

    Query 9:

                         


    SET NOCOUNT ON
    GO

    IF OBJECT_ID('dbo.SalesNormalTable_B4_Partition') IS NULL
    BEGIN
    PRINT 'ERROR: Unable to find Pre partition dbo.SalesNormalTable_B4_Partition table'
    RETURN
    END
    GO

    DECLARE @ROWCOUNT BIGINT
    DECLARE @Infomsg NVARCHAR(MAX)
    DECLARE @tmpSalesWeekDate DATE
    DECLARE @PartitionNumber INT

    IF OBJECT_ID('dbo.SalesNormalTable_B4_Partition') IS NOT NULL
    BEGIN
    --
    -- Turn on trace flags
    --
    dbcc traceon(610)  -- Minmal Logging
    dbcc traceon(1118) -- Use Extent at a time

    IF object_id('tempdb.dbo.#DistinctSalesDate') IS NOT NULL
    BEGIN
    DROP TABLE #DistinctSalesDate
    END

    CREATE TABLE #DistinctSalesDate
    (
    SalesDate DATE NOT NULL
    )

    INSERT INTO #DistinctSalesDate
            ( SalesDate )
    SELECT DISTINCT old.SalesDate 
      FROM dbo.SalesNormalTable_B4_Partition old WITH (NOLOCK)
    ORDER BY 1 ASC

    -- The following DELETE statement gets rid-off any SalesDate
    -- whose data might have already been migrated to partitioned
    -- Sales table
    DELETE tmp
      FROM #DistinctSalesDate tmp
    WHERE tmp.SalesDate IN
    (SELECT DISTINCT new.SalesDate FROM dbo.SalesPartitionTable new WITH (NOLOCK))

    SET @ROWCOUNT = 0

    WHILE 1=1
    BEGIN
    SELECT TOP 1 @tmpSalesWeekDate = tmp.SalesDate
      FROM #DistinctSalesDate tmp WITH (NOLOCK)
    ORDER BY tmp.SalesDate

    IF @@ROWCOUNT = 0 OR OBJECT_ID('SalesPartitionStop') IS NOT NULL
    BEGIN
    BREAK
    END

    EXEC dbo.Proc_CreateORGetPartitionNumber @TableName = 'dbo.SalesPartitionTable',
    @DateValue = @tmpSalesWeekDate,
    @PartitionNumber = @PartitionNumber OUTPUT

    IF @PartitionNumber > 0
    BEGIN
    -- Drop any existing indexes on the partitioned Sales table
    IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesPartitionTable]') 
       AND name = N'PK_SalesPartitionTable_ProductId_StoreId_SalesDate')
    BEGIN
    ALTER TABLE [dbo].[SalesPartitionTable] 
    DROP CONSTRAINT [PK_SalesPartitionTable_ProductId_StoreId_SalesDate]
    END

    IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesPartitionTable]') 
       AND name = N'UX_SalesPartitionTable_SalesDate_StoreId_ProductId')
    BEGIN
    DROP INDEX [UX_SalesPartitionTable_SalesDate_StoreId_ProductId] 
    ON [dbo].[SalesPartitionTable] WITH ( ONLINE = OFF )
    END

    -- Drop any existing indexes on the partitioned placeholder table
    IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[TempSalesPartitionTable]') 
       AND name = N'PK_TempSalesPartitionTable_ProductId_StoreId_SalesDate')
    BEGIN
    ALTER TABLE [dbo].[TempSalesPartitionTable] 
    DROP CONSTRAINT [PK_TempSalesPartitionTable_ProductId_StoreId_SalesDate]
    END

    IF  EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[TempSalesPartitionTable]') 
       AND name = N'UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId')
    BEGIN
    DROP INDEX [UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId] 
    ON [dbo].[TempSalesPartitionTable] WITH ( ONLINE = OFF )
    END

    -- Switch the partition for the specific SalesDate
    -- to placeholder table so we can transfer the data
    -- from non-partitioned table
    ALTER TABLE dbo.SalesPartitionTable
    SWITCH PARTITION @PartitionNumber 
    TO dbo.TempSalesPartitionTable PARTITION @PartitionNumber

    SET @Infomsg = N'Transferring data for PartitionNumber =' + ' ' + CONVERT(VARCHAR(20), @PartitionNumber) + CHAR(13)
    SET @Infomsg = @Infomsg + 'and SalesDate =' + ' ' + CONVERT(VARCHAR(20), @tmpSalesWeekDate)
    PRINT @Infomsg

    INSERT INTO [dbo].[TempSalesPartitionTable]
    (
    StoreId,
    ProductId,
    SalesDate,
    Quantity,
    Revenue,
    SalesActivity
    )
    SELECT old.StoreId,
       old.ProductId,
       old.SalesDate,
       old.Quantity,
       old.Revenue,
       old.SalesActivity
      FROM dbo.SalesNormalTable_B4_Partition old
    WHERE old.SalesDate = @tmpSalesWeekDate

    SET @Rowcount = ROWCOUNT_BIG()

    ALTER TABLE dbo.TempSalesPartitionTable
    SWITCH PARTITION @PartitionNumber 
    TO dbo.SalesPartitionTable PARTITION @PartitionNumber
    END /* IF @PartitionNumber > 0 */

    DELETE tmp
      FROM #DistinctSalesDate tmp
    WHERE tmp.SalesDate = @tmpSalesWeekDate

    SET @Infomsg = N'Transfered data for SalesDate = ' + ' ' + CONVERT(VARCHAR(20), @tmpSalesWeekDate) + CHAR(13)
    SET @Infomsg = @Infomsg + 'and #records transferred =' + ' ' + CONVERT(VARCHAR(20), @Rowcount)
    PRINT @Infomsg
    END /* WHILE 1=1 */

    -- Sanity check to make sure we transferred 100% data from
    -- non-partitioned table to partitioned Sales table
    IF (
    (SELECT COUNT_BIG(*) FROM dbo.SalesPartitionTable WITH (NOLOCK))
       =
    (SELECT COUNT_BIG(*) FROM dbo.SalesNormalTable_B4_Partition WITH (NOLOCK))
       )
    BEGIN
    DROP TABLE dbo.SalesNormalTable_B4_Partition

    -- Create indexes on the partitoned Sales Table
    IF NOT EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesPartitionTable]') 
      AND name = N'PK_SalesPartitionTable_ProductId_StoreId_SalesDate')
    BEGIN
    ALTER TABLE [dbo].[SalesPartitionTable] 
    ADD  CONSTRAINT [PK_SalesPartitionTable_ProductId_StoreId_SalesDate] PRIMARY KEY NONCLUSTERED 
    (
    [ProductId] ASC,
    [StoreId] ASC,
    [SalesDate] ASC
    )WITH (SORT_IN_TEMPDB = ON)
    ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)
    END

    IF NOT EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[SalesPartitionTable]') 
      AND name = N'UX_SalesPartitionTable_SalesDate_StoreId_ProductId')
    BEGIN
    CREATE UNIQUE NONCLUSTERED INDEX [UX_SalesPartitionTable_SalesDate_StoreId_ProductId] 
    ON [dbo].[SalesPartitionTable] 
    (
    [SalesDate] ASC,
    [StoreId] ASC,
    [ProductId] ASC
    )WITH (SORT_IN_TEMPDB = ON)
    ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)
    END

    -- Create indexes on the partitoned placeholder Table
    IF NOT EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[TempSalesPartitionTable]') 
      AND name = N'PK_TempSalesPartitionTable_ProductId_StoreId_SalesDate')
    BEGIN
    ALTER TABLE [dbo].[TempSalesPartitionTable] 
    ADD  CONSTRAINT [PK_TempSalesPartitionTable_ProductId_StoreId_SalesDate] PRIMARY KEY NONCLUSTERED 
    (
    [ProductId] ASC,
    [StoreId] ASC,
    [SalesDate] ASC
    )WITH (SORT_IN_TEMPDB = ON)
    ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)
    END

    IF NOT EXISTS (SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[TempSalesPartitionTable]') 
      AND name = N'UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId')
    BEGIN
    CREATE UNIQUE NONCLUSTERED INDEX [UX_TempSalesPartitionTable_SalesDate_StoreId_ProductId] 
    ON [dbo].[TempSalesPartitionTable] 
    (
    [SalesDate] ASC,
    [StoreId] ASC,
    [ProductId] ASC
    )WITH (SORT_IN_TEMPDB = ON)
    ON PS_dbo_SalesPartitionTable_SalesDate_ByWeek (SalesDate)
    END
    END

    IF OBJECT_ID('tempdb.dbo.#DistinctSalesDate') IS NOT NULL
    BEGIN
    DROP TABLE #DistinctSalesDate
    END

    --
    -- Turn off trace flags
    --
    dbcc traceoff(610)  -- Minmal Logging
    dbcc traceoff(1118) -- Use Extent at a time
    END /* IF OBJECT_ID('dbo.SalesNormalTable_B4_Partition') IS NOT NULL */
    GO

    2019년 5월 10일 금요일 오전 6:07
  • Query 10:

      

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_PurgeWeeklyPartitionedTables]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_PurgeWeeklyPartitionedTables]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [dbo].[Proc_PurgeWeeklyPartitionedTables]
    (
    @Tablename VARCHAR(MAX),
    @RetentionPeriod SMALLINT -- Specify in weeks
    )
    AS
    BEGIN
    /*
    ** Name : dbo.Proc_PurgeWeeklyPartitionedTables
    ** Purpose : To purge older data in partitioned Sales table
    **   that are partitioned on weekly basis based on the SaleDate
    **
    ** Exmaple : EXEC dbo.Proc_PurgeWeeklyPartitionedTables @TableName = 'dbo.SalesPartitionedTable',
    **      @RetentionPeriod = 10
    **
    ** Modification History
    ** Date Name Comment
    ** --------------------------------------------------------------------
    ** 07/07/2011 Saru Radhakrishnan Initial Version
    ** --------------------------------------------------------------------
    */

    SET NOCOUNT ON

    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT
    DECLARE @ErrorNumber INT
    DECLARE @ErrorMessage VARCHAR(256)
    DECLARE @ExecutingProcedure VARCHAR(256)
    DECLARE @ErrorProcedure VARCHAR(100)
    DECLARE @ErrorLine INT

    DECLARE @Infomsg NVARCHAR(MAX)
    DECLARE @DeleteDateDt DATE
    DECLARE @PartitionNumber SMALLINT
    DECLARE @Rowcount BIGINT

    -- Temp tale to hold one or more old HistoryDate data to be purged
    CREATE TABLE #tmpSalesDateDt
    (
    SalesDate DATE
    )

    BEGIN TRY

    SET @ExecutingProcedure = 'dbo.Proc_PurgeWeeklyPartitionedTables'

    -- Purge old Sales data from the table 'dbo.SalesPartitionedTable'
    IF @TableName = 'dbo.SalesPartitionTable'
    BEGIN
    SET @RetentionPeriod = @RetentionPeriod * -1
    SET @DeleteDateDt = DATEADD(WK, @RetentionPeriod, GETDATE())

    IF EXISTS (SELECT 1 FROM dbo.SalesPartitionTable WITH (NOLOCK)
    WHERE SalesDate < @DeleteDateDt)
    BEGIN
    TRUNCATE TABLE #tmpSalesDateDt

    INSERT INTO #tmpSalesDateDt
    ( SalesDate )
    SELECT DISTINCT SalesDate
      FROM dbo.SalesPartitionTable WITH (NOLOCK)
    WHERE SalesDate < @DeleteDateDt

    WHILE 1=1
    BEGIN
    SELECT TOP 1 @DeleteDateDt = tmp.SalesDate
      FROM #tmpSalesDateDt tmp
    ORDER BY tmp.SalesDate

    IF @@ROWCOUNT = 0
    BEGIN
    BREAK
    END

    SELECT @PartitionNumber = dbo.fn_GetPartitionNumberForPartitionFunctionAndValue
    (
    'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
    @DeleteDateDt
    )

    IF @PartitionNumber > 0
    BEGIN
    ALTER TABLE dbo.SalesPartitionTable
    SWITCH PARTITION @PartitionNumber
    TO dbo.TempSalesPartitionTable PARTITION @PartitionNumber

    SELECT @Rowcount = COUNT_BIG(*)
      FROM dbo.TempSalesPartitionTable WITH (NOLOCK)

    TRUNCATE TABLE dbo.TempSalesPartitionTable

    ALTER TABLE dbo.TempSalesPartitionTable
    SWITCH PARTITION @PartitionNumber
    TO dbo.SalesPartitionTable PARTITION @PartitionNumber

    SET @Infomsg = N'Expired rows older than' + ' ' + convert(varchar(10), @RetentionPeriod) + CHAR(13)
    SET @Infomsg = @Infomsg + 'weeks purged from' + ' ' + @Tablename + CHAR(13)
    SET @Infomsg = @Infomsg + 'for SalesDate' + ' ' + CONVERT(VARCHAR(10), @DeleteDateDt, 101)
    PRINT @Infomsg
    END /* IF @PartitionNumber > 0 */

    DELETE #tmpSalesDateDt
    WHERE SalesDate = @DeleteDateDt
    END /* WHILE 1=1 */
    END /* IF EXISTS...*/
    END /* IF @TableName = 'dbo.SalesPartitionTable' */

    END TRY

    BEGIN CATCH
    SELECT @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorProcedure = ERROR_PROCEDURE(),
    @ErrorLine = ERROR_LINE()

    RAISERROR ('SQL Error trapped in Procedure:%s. Error:%d. Message:%s Procedure:%s. Line:%d.', 
    @ErrorSeverity, @ErrorState, @ExecutingProcedure, @ErrorNumber, @ErrorMessage, 
    @ErrorProcedure, @ErrorLine)
    END CATCH

    SET NOCOUNT OFF  
    END
    GO

    Query 11:

    IF OBJECT_ID('dbo.Proc_PurgeWeeklyPartitionedTables') IS NOT NULL
    BEGIN
    EXEC dbo.Proc_PurgeWeeklyPartitionedTables @TableName = 'dbo.SalesPartitionTable',
       @RetentionPeriod = 10
    END
    GO

    Query 12:

          

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_PurgeTableParitionsWithNoRows]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[Proc_PurgeTableParitionsWithNoRows]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [dbo].[Proc_PurgeTableParitionsWithNoRows]
    (
    @TableName VARCHAR(MAX)
    )
    AS
    BEGIN
    /*
    ** Name : dbo.Proc_PurgeTableParitionsWithNoRows
    ** Purpose : On tables that are partitioned, purge those partitions
    **   that no longer have data in it.
    **
    ** Exmaple : EXEC dbo.Proc_PurgeTableParitionsWithNoRows @TableName = 'dbo.SalesPartitionTable'
    **
    ** Modification History
    ** Date Name Comment
    ** -------------------------------------------------------------------------------
    ** 07/07/2011 Saru Radhakrishnan Initial Version
    ** -------------------------------------------------------------------------------
    */

    SET NOCOUNT ON

    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT
    DECLARE @ExecutingProcedure VARCHAR(100)
    DECLARE @ErrorNumber INT
    DECLARE @ErrorMessage VARCHAR(256)
    DECLARE @ErrorProcedure VARCHAR(100)
    DECLARE @ErrorLine INT

    DECLARE @DeleteDateDT DATE
    DECLARE @PartitionNumber INT
    DECLARE @ActualTableName VARCHAR(MAX)
    DECLARE @Infomsg NVARCHAR(MAX)

    SET @ExecutingProcedure = 'dbo.Proc_PurgeTableParitionsWithNoRows'

    BEGIN TRY

    -- Purge empty partitions for table Client.Sales
    IF @TableName = 'dbo.SalesPartitionTable'
    BEGIN
    SELECT @DeleteDateDT = MIN(SalesDate)
      FROM dbo.SalesPartitionTable WITH (NOLOCK)

    IF @DeleteDateDT IS NOT NULL
    BEGIN
    SELECT @PartitionNumber = [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
      (
    'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
    @DeleteDateDT
      )

    WHILE (@PartitionNumber > 1)
    BEGIN
    SET @PartitionNumber = @PartitionNumber - 1

    SELECT @DeleteDateDT = CAST(dbo.fn_GetPartitionRangeValueForPartitionFunctionAndNumber
       (
    'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
    @PartitionNumber
    ) AS DATE)

    -- Just make sure before get rid-off a partition
    -- that there are no rows in that specific partition
    IF (SELECT COUNT(*) FROM dbo.SalesPartitionTable WITH (NOLOCK)
    WHERE SalesDate = @DeleteDateDT) = 0
    BEGIN
    ALTER PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek()
    MERGE RANGE (@DeleteDateDT)

    SET @Infomsg = N'dbo.SalesPartitionTable - Dropped Partition Number ->' + ' ' + CONVERT(VARCHAR(10), @PartitionNumber) + CHAR(13)
    SET @Infomsg = @Infomsg + 'with Zero rows for SalesDate ->' + ' ' + CONVERT(VARCHAR(10), @DeleteDateDT, 101)
    PRINT @Infomsg
    END /* IF (SELECT COUNT(*)... */
    END /* WHILE (@PartitionNumber > 1) */
    END /* IF @DeleteDateDT IS NOT NULL */
    END /* IF @TableName = 'dbo.SalesPartitionTable' */

    END TRY

    BEGIN CATCH
    SELECT @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorProcedure = ERROR_PROCEDURE(),
    @ErrorLine = ERROR_LINE()

    RAISERROR ('SQL Error trapped in Procedure:%s. Error:%d. Message:%s Procedure:%s. Line:%d.', 
    @ErrorSeverity, @ErrorState, @ExecutingProcedure, @ErrorNumber, @ErrorMessage, 
    @ErrorProcedure, @ErrorLine)
    END CATCH

    SET NOCOUNT OFF  
    END
    GO

    Query 13:

    IF OBJECT_ID('dbo.Proc_PurgeTableParitionsWithNoRows') IS NOT NULL
    BEGIN
    EXEC dbo.Proc_PurgeTableParitionsWithNoRows @TableName = 'dbo.SalesPartitionTable'
    END
    GO

    2019년 5월 10일 금요일 오전 6:09