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


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.


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

Whats new SQL Server “Denali” – SQL Server 2011

by Amol 19. February 2011 10:42

Microsoft has relesed SQL Server 2011 CTP, It also know as SQL Server “Denali”.  You can find the whats new in SQL Server Denali in details here.  “What’s New in SQL Server Denali”

Download CTP (Commulitive Technology Preview)

Programmability Enhancements for EXECUTE Statement

Pagination with SQL Server 2011 (Denali)

Database Engine has been enhanced in following areas

Tags: , ,

SQL Server Denali

Tag cloud