T-SQL Find Number Of Records In a Partitioned Table

by Amol 25. February 2011 19:13

SELECT     

      Object_name(p.Object_ID) AS ObjectName,

      p.Partition_Number,

      prv.[Value],

      p.Rows           

FROM sys.partitions p

INNER JOIN sys.partition_range_values prv ON (p.partition_number = prv.boundary_id )

INNER JOIN sys.partition_functions pf ON (prv.function_id = pf.function_id)

WHERE p.Object_Id = Object_ID('<TableName>')

      AND p.Rows > 0

      AND pf.Name = '<PartitionFunctionName>'

Tags:

SQL Server Partitioning

SQL Server Partitioned Tables

by Amol 24. February 2011 15:37

Why Partitions?

        Microsoft introduced table partitioning functionality from SQL Server 2005 onwards. Usually partitions are implemented on large tables. With the help of partitioning, tables or indexes are becomes easy to manage, because partitioning enables you to manage and access subsets of data quickly and efficiently. By using partitioning loading and deleting data only takes seconds instead of minutes or hours in case of non-partitioned tables. Select opration is also becomes more efficient as it target only the data that is required, instead of enitre data.

Steps to create Partitioned Table  

        Follow steps mentioned below to create a partitioned table in SQL Server. 

  1. Create a partition function to specify how a table or index that is being sliced.
  2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
  3. Create a table or index using the partition scheme.

Example:

 -- Create Partition Function
CREATE PARTITION FUNCTION pfTestPartition (SMALLDATETIME)
AS RANGE LEFT FOR VALUES ('2010-02-01 23:59:00', '2010-02-02 23:59:00')

GO

-- Create Partition Schema

CREATE PARTITION SCHEME psTestPartition

AS PARTITION pfTestPartition ALL TO ([PRIMARY])

 

GO

 

-- Create Partitioned Table

CREATE TABLE TestPartition

( ID INT IDENTITY(1,1),

  EndTime SMALLDATETIME NOT NULL )

 

GO

 

ALTER TABLE TestPartition

ADD CONSTRAINT [pk_TestPartition]

PRIMARY KEY CLUSTERED

(

      [EndTime] ASC

)ON psTestPartition (EndTime)

GO

Tags: ,

SQL Server Partitioning

T-SQL Get Partition Details

by Amol 22. February 2011 19:16

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 Partitioning | SQL Server Scripts

Category

Recent Posts

Tag cloud