SQL Server Partition Details

by Amol 29. April 2010 12:26

You will find the following query useful if you deal with SQL Server Partitioned objects. I have written this query to know whether paritioned data gets instered into desired partition. Apart from this you will get to know partition boundaries, the filegroup is used to store the data, the number of rows in each partition. Provided query is much faster as it deals with catalong views.

You can use this query to find out the number of rows in table if you have billions of rows instead firing query on actual table which may take long time to execute.


You can just replace '<ObjectName>' with the object you interested in filer criteria.

 

 

SELECT

      i.object_id,

      i.name AS IndexName,

      p.partition_number,

      fg.name AS FileGroupName,

      value,

      ps.name AS SchemaName,

      f.name FunctionName

FROM sys.partitions p

JOIN sys.indexes i

      ON (p.object_id = i.object_id

          AND p.index_id = i.index_id)

JOIN sys.partition_schemes ps

      ON (ps.data_space_id = i.data_space_id)

JOIN sys.partition_functions f

      ON (f.function_id = ps.function_id)

LEFT JOIN sys.partition_range_values rv   

      ON (f.function_id = rv.function_id

          AND p.partition_number = rv.boundary_id)

JOIN sys.destination_data_spaces dds

      ON (dds.partition_scheme_id = ps.data_space_id

          AND dds.destination_id = p.partition_number)

JOIN sys.filegroups fg

      ON (dds.data_space_id = fg.data_space_id)

WHERE i.index_id < 2

      AND i.object_id = Object_Id('<ObjectName>')

Tags: ,

SQL Server Scripts

Script to generate calender for a month

by Amol 29. April 2010 12:14
Now days we can see variety of calender controls to display on web page. We might not or rarely used t-SQL to generate calender.
This code is just to test our T-SQL logic.
 
Here is T_SQL code.
 

SET NOCOUNT ON

DECLARE @TempT TABLE (MM INT, YYYY INT)

INSERT @TempT(MM, YYYY) SELECT 02, 2009

INSERT @TempT(MM, YYYY) SELECT 03, 2010

;

WITH Numbers(LineNumber) AS

(

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

)

, FirstAndLastDayOfMonth AS

(

    SELECT CAST(CONVERT(VARCHAR,DATEADD(dd,-(DAY(CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))-1),CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)),101) AS DATETIME) AS FirstDay

          ,CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)))),DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))),101) AS DATETIME) AS LastDay

    FROM @TempT

)

, YearMonthAndDays AS

(

    SELECT

         UPPER(DATENAME(MONTH, FirstDay)) + N' ' + DATENAME(YEAR, FirstDay) AS Header

        ,REPLICATE(N' ', 4 * ((CASE WHEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST > 7 THEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST - 7

                               ELSE DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST

                               END) - 1)) +

         SUBSTRING(N'   1   2   3   4   5   6   7   8   9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31',

            1, 4 * DAY(LastDay) + 1) + N'                             ' AS AllDays

        ,ROW_NUMBER() OVER(ORDER BY FirstDay) AS RowNum

    FROM FirstAndLastDayOfMonth

)

, WeekHeader AS

(

   SELECT 'Sun Mon Tue Wed Thu Fri Sat' AS DayNames

)

, Calendar AS

(

    SELECT

         CASE LineNumber

            WHEN 1 THEN N'*=============================*'

            WHEN 2 THEN N'|' + LEFT(REPLICATE(N' ', (29 - LEN(Header)) / 2) + Header + REPLICATE(N' ', 29), 29) + N'|'

            WHEN 3 THEN N'|=============================|'

            WHEN 4 THEN N'|' + N' ' + DayNames + N' ' + N'|'

            WHEN 5 THEN N'|-----------------------------|'

            WHEN 12 THEN N'*-----------------------------*'

            ELSE N'|' + SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28) + N' ' + N'|'

         END AS Outputlines

        ,RowNum

        ,LineNumber

    FROM YearMonthAndDays

    CROSS JOIN WeekHeader

    CROSS JOIN Numbers

    WHERE LineNumber < 6

       OR LEN(SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28)) > 0

       OR LineNumber = 12

)

SELECT  Outputlines

FROM    Calendar

ORDER BY RowNum, LineNumber

Tags:

SQL Server Scripts

Interesting - Huge performance gain while inserting data

by Amol 2. November 2009 12:34

Initially I was populating date dimension in usual manner. It tooks lot of time to insert 4 years of date information in date dimension. I has to populate date dimension in loop as I wanted to populate various attributes for for it like year, month, week, fiscal week, date etc. I started investigating and found a solution to boost the performance. Here I am demonstrating the same situation by using sample table to insert the data.

 

This is optimum way of populating date dimension or any table which you are populating by running loop.

 

Try to insert the records in loop with and without transactions.


I tried this and got the huge performance difference. 

With transaction, execution completes with a second. And without transaction it take 659 seconds (11 minutes). 


GO

IF OBJECT_ID ('TempTable') IS NOT NULL
     DROP TABLE TempTable

 

GO

CREATE TABLE TempTable (ID INT IDENTITY(1,1), Name VARCHAR(1000))

GO

/********* Without transaction ***********/

GO

TRUNCATE TABLE TempTable

GO

DECLARE @Counter INT = 0

WHILE (@Counter <= 100000)
BEGIN

      INSERT INTO TempTable
      SELECT 'Name ' + CAST(@Counter AS VARCHAR)

      SET @Counter = @Counter + 1

END

GO

 

/********* With transaction – run this once above statement completed ***********/

TRUNCATE TABLE TempTable

DECLARE @Counter INT = 0

BEGIN TRANSACTION

WHILE (@Counter <= 100000
BEGIN

      INSERT INTO TempTable
      SELECT 'Name ' + CAST(@Counter AS VARCHAR)

      SET @Counter = @Counter + 1

END

COMMIT TRANSACTION

GO


 

Tags: , ,

SQL Server Tips & Tricks

Category

Recent Posts

Tag cloud