Contained Database - SQL Server 2011 - Denali

by Amol 3. March 2011 10:53

One of the best feature available in SQL Server 2011 - Denali is Contained Database. As name indicates, it includes all the required meta data and database settings and it doesnt have any dependencies on SQL Server Database instance where database is hosted. That is why user can connect to database without authenticating at database engine level and contained database can easily move from one SQL Server instance to another.

Contained Database Model:

  • Application boundary
  • Contained
  • Uncontained
  • Non-contained database
  • Fully contained database
  • Partially contained database
  • Contained user

More information about Contained Databases is available on MSDN

Benefits of Contained Databases

Contained Databases Example

Tags: , ,

SQL Server Denali

What is Columnstore Index

by Amol 1. March 2011 06:20

Introdution:

SQL Server 2011 CTP1 has been relesed and it has introduced new INDEX type called Columnstore. Microsoft SQL Server is the first who described columnstore index. Non of the other database products introduced this concept. This newly introduced index will help dramatically increase query performance as columnstore index stores each column in a separate pages rather than traditional index pages which stores multiple rows.  Traditional index pages will looks like as shown in figure below.   

Figure 1

Columnstore index structure is bit different. Instead of storing entire columns in single page it store each column in separate page. Columnstore index is structure is show in figure below.

Figure2

Figure 1 and Figure 2 shows the basic difference between index and columnstore index.
In Figure 1 entire rows (all columns) are stored in page. And in Figure 2 column Col1 to Col4 are stored in different pages.

Benifites of Columnstore Index:

1.      As Columnstore Index stored in separate pages, only required pages are fethced from the disk.
2.      Faster query processing.
3.      Frequently accessed columns remains in memory.
4.      Enhanced query optimization and execution features, improves data warehouse query performance by hundreds to thousands of times in some cases

I will try to post the comparision result between traditinal Index and Columnstore index. Meanwhile you can try downloading SQL Server Denali CTP.

Tags: , ,

SQL Server Denali

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

Tag cloud