locked
Partition Tables RRS feed

  • Question

  • I partition few tables on yearly basis and would like to find out that the data from all these tables goes to the correct yearly partition and then match the total rows. Need help with the syntax. Thanks
    Thursday, March 12, 2015 2:46 PM

Answers

  • SELECT 
    	  $partition.[PartitionFunctionName]([DateTimeColumnAsPartitionKey]) AS [Partition Number]
    	, YEAR([DateTimeColumnAsPartitionKey]) AS [Year]
    	, COUNT(*) AS [Rows In Partition]
    FROM dbo.[PartitionedTable]
    GROUP BY $partition.[PartitionFunctionName]([DateTimeColumnAsPartitionKey])
    ORDER BY [Partition Number]


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Yuan Thursday, March 12, 2015 3:23 PM
    • Marked as answer by Eric__Zhang Thursday, March 19, 2015 1:32 PM
    Thursday, March 12, 2015 3:23 PM

All replies

  • http://dba.stackexchange.com/questions/22638/get-a-records-current-partition 

    https://technet.microsoft.com/en-us/library/ms188071(v=sql.105).aspx

    That will help you get all info

    and this below 

    https://technet.microsoft.com/en-us/library/ms187924(v=sql.105).aspx


    ebro


    • Edited by ebrolove Thursday, March 12, 2015 3:04 PM Addition
    Thursday, March 12, 2015 3:03 PM
  • SELECT 
    	  $partition.[PartitionFunctionName]([DateTimeColumnAsPartitionKey]) AS [Partition Number]
    	, YEAR([DateTimeColumnAsPartitionKey]) AS [Year]
    	, COUNT(*) AS [Rows In Partition]
    FROM dbo.[PartitionedTable]
    GROUP BY $partition.[PartitionFunctionName]([DateTimeColumnAsPartitionKey])
    ORDER BY [Partition Number]


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Yuan Thursday, March 12, 2015 3:23 PM
    • Marked as answer by Eric__Zhang Thursday, March 19, 2015 1:32 PM
    Thursday, March 12, 2015 3:23 PM
  • Refer the below link

    http://davidpeterhansen.com/view-partitions-in-sql-server/

    --Prashanth

    Thursday, March 12, 2015 3:35 PM