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

Programmability Enhancements for EXECUTE Statement

by Amol 24. February 2011 09:58

We all are familiar with EXECUTE statement in SQL Server. Below are some programmability enancement for EXECUTE statement.

Using WITH RESULT SET

 Execute stement can return resultset by using WITH RESULT SET argument.  You can specify your own aliases to return resultset. 

EXEC dbo.GetEmployeeDetails  100

WITH RESULT SETS

(

   ([Id] int NOT NULL,

    [First Name] nvarchar(50) NOT NULL,

    [Last Name] nvarchar(50) NOT NULL,

    [Manager Name] nvarchar(50) NOT NULL)

);

You can define multiple resultset as well using WITH RESULT SET clause. Following example demonstrate how to retrun multiple resultsets.

CREATE PROCEDURE [dbo].[GetEmployeeDetails]

(     @EmployeeId INT )

AS

BEGIN

 

    -- First resultset

      SELECT     

            EmployeeId,      

            EmployeeName

      FROM [dbo].[Employee]

      WHERE EmployeeId = @EmployeeId

 

             -- Second resultset

 

      SELECT

            mgr.EmployeeId AS ManagerId,

            mgr.EmployeeName AS ManagerName

      FROM [dbo].[Employee] emp

      LEFT JOIN [dbo].[Employee] mgr

ON (emp.ManagerId = mgr.EmployeeId)

      WHERE emp.EmployeeId = @EmployeeId       

 

END

GO

-- Execute the procedure

EXECECUTE [dbo].[GetEmployeeDetails] 100

WITH RESULT SETS

(

    -- first result set definition starts here

    (EmployeeId int,  

    Name nvarchar(50))

    , -- comma separates result set definitions

    -- second result set definition starts here

    (ManagerId int,

    ManagerName nvarchar(50)) -- second result set definition starts here
);

Actual resultset return can be different from the resultset defined in RESULT SET clause in one of the following ways. number of result sets, number of columns, column name, nullability, and data type. If the number of result sets differs, an error occurs and the batch is aborted.

Using multiple parameters

Exceute statement allows to pass multiple parameters with variable as well hardcode values. Earlier it was accepting either variables or hardcode values.
Procedure accepts two parameters, OrderID (100 as hard coded value) and @OrderDate as variable as datetime.

  DECLARE @OrderDate datetime;
  SET @OrderDate = GETDATE();
  EXEC
dbo. GetOrderDetails, 100, @OrderDate;

Using EXECUTE AS USER

EXECUTE statement can be used to execute as user to switch context to another user.
Following example execute SQL statement to create table in context of XYZ user. XYZ user must be present in the datebase and needs permission to create table otherwise statement will get fail.

EXECUTE ('CREATE TABLE [dbo].[TestTable] (Id int, Name varchar(50))')
      AS USER = 'XYZ';

Tags: ,

SQL Server Denali

Category

Recent Posts

Tag cloud