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

SQL Server 2008 R2 Cumulative Update 5 released

by Amol 1. March 2011 07:06

Today microsft has released SQL Server 2008 R2 Cumulative Update #5. The build number would be 10.50.1753.0 after applying this updates. This update is available to download at

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2438347&kbln=en-us

Note: This hostfix contains all the updates since initial release SQL Server 2008 R2 RTM.

Tags: ,

General

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

Category

Recent Posts

Tag cloud