Sunday, October 19, 2008

Microsoft SQL Server 2008 Report Builder 2.0 Released

Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 Reporting Services. The download provides a stand-alone installer for Report Builder 2.0

Download from Here

Sunday, September 14, 2008

Import /Export Wizard in SQL Server Express 2008

Finally Microsoft has decided to include Import/Export wizard in SQL Server 2008 Express edition due to the consistant demand of this tool

Tuesday, September 9, 2008

Step by Step Installation of SQL Server 2008

SQL Server 2008 is the new member in Microsoft Database Server family and the installation process as usual is simple and straight forward. This version has many features that can be configured during the initial installation. The screens are little different from earlier versions. Without any doubt, the installation process in 2008 is more centralized and has more clarity. I am demonstrating a simple stand alone installation of SQL Server 2008 in this article.

You may also refer SQL Server 2008 Books Online here

I am not intended copy paste the system requirements and all other basics here because those information is already available in Microsoft Sites. It better to refer those because it is subject to change.
Planning SQL Server 2008 Installation
System Requirement


• Referred the above mentioned sites and ensured that your system meet all the requirements
• You have planned your Instance name if it is a named Instance
• You have planned all the Data Directory. Ie. Where to keep System Databases, TempDB, and User Databases.(if not you can go by Default but it is not generally recommended).
• You have planned Startup Account for all the services. (if not you can go by Default but it is not generally recommended.)
• You have a list of features that you wants to install. Like if you are not going to use Filestream no need to configure that during installation

My System Configuration

• Operating System : Windows XP
• Existing SQL Server Instance
o Default Instance : SQL Server 2005 SP 2
o Named Instance 1: SQL Server 2000 SP 4
o Named Instance 2 : SQL Server 2005 Express SP2
• .Net Framework : 1.0

Step 1 : Insert the DVD

The system will automatically detect the system configuration and it install the .NetFramework and Windows Installer 4.5 if required.

My installation Screen is as follows :-

Step 2 : System has installed .NetFramework 3.5 and rebooted the system. Next step is to install SQL Server 2008.

In "SQL Server Installation Center" , navigate to "Installation" -- >> click "New Installation or Add Features to an Existing Installation."


In the Instance Configuration Screen you need to select the instance type (default /named) and other instance related setting. The one new thing in SQL Server 2008 is InstanceID.
Instance Configuration

Instance ID - By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, specify it in the Instance ID field.

Note :
SQL Server Management Studio Exe name in SQL Server 2008 SSMS.exe where as in SQL Server 2005 it was sqlwb. Ie. To open Management Studio in 2008 -- Start -->> Run -->> SSMS


I deliberately installed SQL Server 2000 and 2005 in the same system before installing 2008 to ensure that all version can co-exists. The installation process of SQL Server 2008 is comprehensive and simplified than earlier versions. And it is understandable that, as the features grow the installation process also gets lengthy.

Saturday, August 23, 2008

SQL Server 2008 Sample Databases Download

Download SQL Server 2008 Sample OLTP , OLAP Databases from Here

Wednesday, August 6, 2008

Microsoft SQL Server 2008 Released

Finally Microsoft SQL Server 2008 RTM version is released today. It was suppose to release with Windows 2008 and Visual Studio 2008 but somehow did not. There are seven editions in SQL Server 2008.

In a press release, Microsoft cited several large enterprise customers who are testing SQL Server 2008, including Xerox, Siemens, Clear Channel Communications and Fidelity Investments.

Friday, July 25, 2008

Data Compression in SQL Server 2008

When we talk about compression feature in SQL Server 2008, it address two areas. Data Compression and Backup Compression. I have already discussed Backup compression here. This article is specifically discussing Data compression feature in SQL Server 2008. The idea behind Data compression is, if we can reduce the data size then the resource usage like memory (data buffer) , Storage space and Network traffic can be reduced therefore you will have better performance and manageability. But before going for Data Compression you should do proper analysis, whether this feature is appropriate for your environment or not. If the Database Server is having CPU resource crunch you must not opt Data compression because, Data Compression is more CPU oriented operation. When you have a system where there is no problem of CPU but it is more IO based, then you may go for data compression which will give you better performance.

SQL Server 2008 supports ROW and PAGE compression for both tables and indexes. Data compression can be configured for the following database objects:
• A whole table that is stored as a heap.
• A whole table that is stored as a clustered index.
• A whole non-clustered index.
• A whole indexed view.
• For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.

As already mentioned there are two levels of compression, PAGE and ROW. Before going for compression of the data you must do proper estimation and planning. Use system stored procedure sp_estimate_data_compression_savings to check whether the data compression can be benefited for a particular table.

What is the difference between PAGE and ROW level compression?
(a) PAGE Level Compression• Compressing the leaf level of tables and indexes with page compression consists of three operations in the following order:
• Row compression
• Prefix compression
• Dictionary compression

(b) ROW Level Compression
• It reduces the metadata overhead that is associated with the record. This
metadata is information about columns, their lengths and offsets. In some
cases, the metadata overhead might be larger than the old storage format.
• It uses variable-length storage format for numeric types (for example
integer, decimal, and float) and the types that are based on numeric (for
example datetime and money).
• It stores fixed character strings by using variable-length format by not
storing the blank characters.

Note : You must read more about ROW and PAGE Compression in Books Online. There are few interesting points like when the PAGE compression is actually take place kind. PAGE compression is a Superset of ROW compression and system goes for page compression only when the page is filled. Till then it is a ROW level compression. Once the page is filled, The whole page is reviewed; each column is evaluated for prefix compression, and then all columns are evaluated for dictionary compression. If page compression has created enough room on the page for an additional row, the row is added, and the data is both row- and page-compressed.

How do we do data compression estimation on a table?
(a) Create a sample table Table
Use AdventureWorks
Create table TestCompression (col int,comments char(8000))
Insert into TestCompression select Message_id,text from sys.messages

Note : I have choosen CHAR Datatype intentionally so that I can demonstrate the estimation properly.
(b) Run the sp_estimate_data_compression_savings system stored procedure against this table
Row Compression Estimation Report

EXEC sp_estimate_data_compression_savings 'dbo', 'TestCompression', NULL, NULL, 'ROW' ;

object_name : TestCompression
schema_name :DBO
index_id :0
partition_number : 1
size_with_current_compression_setting(KB) : 704888
size_with_requested_compression_setting(KB) : 9952

sample_size_with_current_compression_setting(KB) : 40784
sample_size_with_requested_compression_setting(KB) : 576
Page Compression Estimation Report

EXEC sp_estimate_data_compression_savings 'dbo', 'TestCompression', NULL, NULL, 'Page' ;
object_name : TestCompression
schema_name :DBO
index_id :0
partition_number : 1
size_with_current_compression_setting(KB) : 704888
size_with_requested_compression_setting(KB) : 9134

sample_size_with_current_compression_setting(KB) : 39664
sample_size_with_requested_compression_setting(KB) : 560

How to read this result ?
I am not going to explain, how to read the result of this stored procedure in detail, since it is clearly mentioned in Books Online. In the result the size_with_current_compression_setting(KB) : 704888 and
size_with_requested_compression_setting(KB) : 9134
will give you fair amount of idea how compression is going to benefits. See the difference in space usage shown in before compression and after the compression 704888: 9134. Without any doubt the compression is going to save lot of space in this table.

Why Page Compression showing more compression of data than ROW Compression?
If you see the result of PAGE and ROW level compression, you can see that page level compression save more space. Why so? Because Page compression do ROW compression as well as Dictionary and Prefix Compression. So PAGE compression is a superset of ROW Compression. So the point here is, if we want highest level of compression, go for page at the cost of more CPU cycle.

Note : One thing I have noticed in the system stored procedure is, for every run the result is different. There is few KBs size different in each run. I have reported this in Microsoft Connect.

Estimation Report in Query Analyser

Now we know the compression feature will be useful for this table. How do we enable compression for this table? Here we go…

There are few options available to enable Data Compress on a table.
(a) From Management Studio.
(b) While creating in the CREATE Table Statement

CREATE TABLE [dbo].[TestCompression](
[col] [int] NULL,
[comments] [char](8000) NULL

(c) In ALTER Table Statement

ALTER TABLE [TestCompression]

Enabling Data Compression from Management Studio
Screen 1

Screen 2

Screen 3

Screen 4

Screen 5

Screen 6

We have successfully enabled data compression on this table. How do we confirm this. You can query sys.partitions. Here we go….
Select OBJECT_NAME(object_id),data_compression_desc,* From sys.partitions where data_compression<>0.

Screen 7

Now we have enabled compression and let us check what happens if we run the same system stored procedure to do the estimation. See the result. No scope for improvement further because it is already compressed

Few more informations from Books online.
• The compression setting of a table is not automatically applied to its nonclustered indexes.
• Each index must be set individually.
• Tables and indexes can be compressed when they are created by using the CREATE TABLE and CREATE INDEX statements. To change the compression state of a table, index, or partition, use the ALTER TABLE or ALTER INDEX statements.
• Compression is not available for system tables.
• Apply compression only after exploring options like Data defragmentation etc.

Having explained the whole topic I kept one very important point for the summary section. That is, this feature will be only available in Enterprise Edition, Developer Edition and Evaluation Edition. So the system stored procedures and options in GUI will only be available in the above mentioned editions. In IO bound application certainly this feature will be useful. I keep my finger crossed for the feedback from the community who have successfully implemented and experience this feature.

Thursday, July 10, 2008

SQL Server 2008 – Table Locking enhancement

In SQL Server 2008 ALTER TABLE statement, you can find a new option called SET LOCK_ESCALATION. This is one of the enhancement in Locking in SQL Server 2008. This option can have three value, Auto,Table, Disable

Specifies the allowed methods of lock escalation for a table.

This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.
• If the table is partitioned, lock escalation will be allowed to the heap or B-tree (HoBT) granularity. After the lock is escalated to the HoBT level, the lock will not be escalated later to TABLE granularity.
• If the table is not partitioned, the lock escalation will be done to the TABLE granularity.

Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. This behavior is the same as in SQL Server 2005. TABLE is the default value.

Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

Note : If you use partitions then After you create a partitioned table, consider setting the LOCK_ESCALATION option for the table to AUTO. This can improve concurrency by enabling locks to escalate to partition (HoBT) level instead of the table. For more information

SQL Server 2008 – PowerShell in SQL Server

SQL Server 2008 introduces support for Microsoft PowerShell. PowerShell is a new command-line shell and scripting language which offers more functionality than Windows command prompt utilities.

Read more about powershell here

I was planning to blog about this for long time but the problem is, PS is an ocean in itself. For sure, very powerful and very useful in day to day DBA activities. When I started exploring this utility I could figure out many scenarios where SQLPS can really help. Like , backup file management(or for that matter any file management) like moving/deleting old backup files. With my little knowledge and experience in SQL Server I am sure we can write many utility tools using SQLPS.

How to go to SQL Powershell (PS) prompt

(a) Start - - ->> Run -- ->> Cmd -- ->> sqlps
(b) I got a prompt like “PS C:\Documents and Settings\Madhu>
(c) Now I want to change the location to my SQL Server instance and databases

PS C:\Documents and Settings\Madhu>Set-Location SQL:\LHIL075\SQL2008FEB\Databases


PS C:\Documents and Settings\Madhu>CD SQL:\LHIL075\SQL2008FEB\Databases

Note : CD is an aliases for Set-Location cmdlet.

(d) I get the following prompt
PS SQL:\LHIL075\SQL2008FEB\Databases>
(e) Now you can do Get-ChildItem or DIR to get the object in this node

There are many useful cmdlets available. In the coming months we should find many useful utilities using PS.

Using Invoke-Sqlcmd (one of the cmdlet available)
The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.
This is an example of calling Invoke-Sqlcmd to execute a simple query, similar to specifying sqlcmd with the -Q and -S options:

Simple... need to explore more. I will be updating with few sample scripts soon

Thursday, June 12, 2008

SQL Server 2008 – All Action Auditing (AAA)

In SQL Server 2005, this was one of the area were there was significant enhancement from SQL Server 2000. DDL Trigger and default trace was introduced in SQL Server 2005 to provide administrator more controlled Audit without effecting the system performance. Still , the SQL Server community was not convinced with the auditing features of SQL Server 2005. For instance, DDL trigger was not able to track all the DDL Trigger events like sp_Rename. Running profiler for auditing was just can not be done because Profiler was not able to trace the only required data. So what was the solution provided in earlier versions to have a proper audit system? Nothing but go for third party tools.

In SQL Server 2008, Audit is a first class object in the server. You have complete set of DDL statement to manage Audit feature. Audit can also make the audited information entries into file , windows application log or windows security log. Security log entry benefit is that, even the administrator can not change the entries. Another good thing about this feature is , you have very granular level control. Ie. You can audit very specific action on an object for example, who is running select query on EmployeeSalary table , salary column. This was not possible in profiler.

Note : As on today, the information is that this feature will only be available in Enterprise Edition of SQL Server 2008.

There are two categories of actions for audits:
• Server-level. These actions include server operations, such as management changes and logon and logoff operations.
• Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.

1. How to configure Audit using Management Studio

Create Audit


• File path : Only the folder has to be specified. The File filename has to be unique so, the system will provide the name with timestamp.
• Folder : The folder must already be existing
• You can mention the max rollover files and size.

2. Create Server Audit Specification (or Database Audit Specification as per your requirement)

Audit Action Type : There are many action type. Select the appropriate one.

3. Ensure that the Audit and Audit Specification is enabled
Select is_state_enabled,name From sys.server_file_audits
Select is_state_enabled,Name From sys.server_audit_specifications
Select is_state_enabled,Name From sys.database_audit_specifications

The Audit file viewer report will looks like this.

4. Script to Create Audit Objects
GUI method is very simple and if somebody wants to do it by TSQL Script, its even simple. You can script the Audit objects the same way we do for any other objects. Just right click on the Audit and Server Audit specification which we have created and Script it. You can keep the script in Version control or so. Here is the script I have generated after configuring the audit process through GUI.

--Create Audit
/****** Object: Audit [Audit-20080612-194600] Script Date: 06/13/2008 09:43:25 ******/
CREATE SERVER AUDIT [Audit-20080612-194600]
( FILEPATH = N'D:\SQLAudit\'
( QUEUE_DELAY = 1000
,AUDIT_GUID = 'c314f405-ae9c-4f4b-947f-041070683c9d'
--Create Server Specification
USE [master]

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20080612-195650]
FOR SERVER AUDIT [Audit-20080612-194600]
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20080612-195650]

Important Datebase system objects Related to Auidt feature
fn_get_audit_file :Returns information from an audit file created by a server audit.
Select Statement,* From fn_get_audit_file ('D:\SQLAudit\*',null,null)
--Will retrun all the file reports in the specified folder

Script to List all the Database Specification created from all the database
set nocount on
declare @My_Return int
declare @command varchar(1000)
set @command = 'use [?] Select *from sys.database_audit_specifications '
print @command
exec @My_Return = master.dbo.sp_MSforeachdb @command

Audit Trucnate Table Command
DDL Trigger in 2005 and 2008 still not able to track TRUNCATE Table Command. But Audit does that. SCHEMA_OBJECT_CHANGE_GROUP Database Specification will track your Truncate command too.

There are DDL statements to configure Audit like CREATE SERVER AUDIT AND CREATE SERVER AUDIT SPECIFICATION kind. But GUI method is pretty simple and straight forward. You also have these objects exposed through SMO. Because of the new events introduced to track the information, which is inside the engine unlike SQL Trace, the performance will be faster compare to SQL Trace. Any audit system will consume resource, but what All Action Audit (AAA) feature of SQL Server 2008 offers is, very granular level audit control with negligible performance hindrance.

Wednesday, June 11, 2008

Resource Governor in SQL Server 2008

Yet another fantastic feature or utility tool of SQL Server 2008 Resource Governor allows you to control the resource according to the requirements . This was motive behind this feature is providing predictable response to the user in any situation. In earlier versions, we had a single pool of resources like Memory, CPU, threads etc. You can not priorities the workload vs Resource pool in 2005 and earlier version. Generally, who accesses the system first and starts a process, it can consume the resources without any restrictions. Consider, some kind of BI runaway query is first hit system where the OLTP and OLAP Databases are in the same server. Now the OLTP process has to wait till the OLAP process releases the resource. This was a major concern in earlier versions. So what were the solution then, go for multiple instances and configure the Resource per instance or go for different machine altogether. Both method were having its own problem. By specifying the resource, if the system is not using that resource still will not released. If you go for another machine, you may have license issue and it’s not a cost effective method.

In SQL Server 2008, these problems are addressed by providing a tool called Resource Governor. You can differentiate the workload by Application Name, Login, Group, by database name etc. Once you have defined the workload, you can configure the resource which can consumed by workload. Probably, you want to give more resource for your OLTP application than the OLAP. You have that kind of flexibility and control here.
The following three concepts are fundamental to understanding and using Resource Governor:
• Resource pools. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports user-defined resource pools.
• Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed. Resource Governor also supports user-defined workload groups.
• Classification. There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

I am exploring this tool in detail. I will be updating this blog entry soon with my hands-on with this wonderful tool

Friday, June 6, 2008

SQL Server 2008 - Sparse colum

One of the major enhancement in database engine of SQL Server 2008 is Sparse column. It improve data retrival and reduce the storage cost. It also can be used with Filtered Index to improve the performance.

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

How to Create sparse column.

Simple , just mention sparse keyword in table creation or alter statement.

CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)

Refer this KB

Note : There are many limitations as far as the implementation is concerned. like the column has to be null, cannot be included in Clustered index, merge replication,compression etc. Please refer the site mentioned above or BOL.

Notification Service no more available in SQL Server 2008

Check this thread for more info. Joe Webb has explained in detail

SQL Server 2008 - Filtered Index

If you see the Create Index Syntax in SQL Server 2008 books online, you can see a new option called “ [ WHERE ]” which stands for Filtered Index feature.

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Suppose you have a table which stored the history (not so relevant to OLTP application ) data also. The query on that table is mostly on the current data and you want to index only on current data for eg. DateofPurchace > ‘1-1-2007’ kind. Another example would be , you have a product catalog and you want to show only the active products to the customer.

ON OrderDetail (OrderDetailID, DateofPurchase)
WHERE DateofPurchase >’1-1-2007’

Filtered Index Design Guidelines

Filtered Index Feature Support
In general, the Database Engine and tools provide the same support for filtered indexes that they provide for nonclustered full-table indexes, considering filtered indexes as a special type of nonclustered indexes. The following list provides notes about tools and features that fully support, do not support, or have restricted support for filtered indexes.
• ALTER INDEX supports filtered indexes. To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.
• The missing indexes feature does not suggest filtered indexes.
• The Database Engine Tuning Advisor considers filtered indexes when recommending index tuning advice.
• Online index operations support filtered indexes.
• Table hints support filtered indexes, but have some restrictions that do not apply to non-filtered indexes. These are explained in the following section.

Sunday, May 25, 2008

SQL Server 2008 - Credential a New property for a SQL login

While creating login you can set a new property called Credential. This Property asks SQL Server Engine to use this credential (which is in-tern mapped to a windows user ) to interact outside SQL Server. Earlier all the interaction with OS or outside sql server was done using Service Account. Now you can specify which windows user credential to be used for a specific login.

Steps are as follows

(a) Create Credential

CREATE CREDENTIAL credential_name WITH IDENTITY = 'identity_name'
[ , SECRET = 'secret' ]
[ FOR CRYPTOGRAPHIC_PROVIDER cryptographic_provider_name ]

Create Credential using Management Studio

(b) Create Login and specifiy Credential

CREATE LOGIN NewLogingName WITH PASSWORD = 'Password',
CREDENTIAL = NewCredentialName

Thursday, April 3, 2008

Availability and Downtime in SQL Server Context

What is Availability ?

Availability is generally driven by the application and defined in the SLA (Service Level Agreement). For Eg. An order processing system the client requirement may be an Order should be completed in 1 sec. And also there will be a mention of acceptable downtime also. It may be 5 min/1 hr/1 day as per the requirement.


Downtime is the time when the SLA is not met. There are two type of downtime :-

(a) Planned Downtime : It’s the time when you upgrade your system or apply patches or any kind of periodic maintenance of the system. It will be mentioned in the SLA.

(b) Unplanned downtime : May be because of human error or system corruption or natural calamity etc etc.

Tuesday, March 25, 2008

SQL Server 2008 – Manage unstructured data using Filestream Storage

SQL Server 2008 – Manage unstructured data using FILESTREAM Feature

This document details the implementation of FILESTREAM within SQL Server 2008. In the previous versions of SQL Server, storing unstructured data had many challenges, like maintaining transactional consistency between structured and unstructured data , manageability (backup / restore/security) , Managing archiving and retention policies and performance and scalability were few challenges among them. SQL Server 2008 introduced FILESTREAM feature to resolve these issues. FILESTREAM, eliminate the complexity in an application development and reduce the cost of managing unstructured data. FILESTREAM also increases the manageability by extending the capabilities that are currently available only to relational data to non-relational data (backup / restore /security).

SQL Server 2008 introduces two new capabilities for storing BLOB data:

• FILESTREAM: An attribute you can set on a varbinary column so that the data is stored on the file system (and therefore benefits from its fast streaming capabilities and storage capabilities), but is managed and accessed directly within the context of the database.
• Remote BLOB Storage: A client-side application programming interface (API) that reduces the complexity of building applications that rely on an external store for BLOBs and a database for relational data.
Additionally, SQL Server 2008 continues support for standard BLOB columns through the varbinary data type.

What are the properties a product should possess to clasify itself as an Enterprise Ready Product?
An Enterprise Ready Product should support fundamental properties like:
• Scalability
• Security
• Maintainability
• Availability
• Auditability
• Reliability
SQL Server 2008 is an Enterprise Ready Product as it possess all the fundamental qualities that define an Enterprise Ready Product. FILESTREAM is one of the enhancements made within SQL Server 2008 in order to classify it as an Enterprise Ready Product.
It is very essential that an Enterprise Solution supports storage of both structured and unstructured data within an organization. It is important that this structured data and unstructured data should be secured and managed. For instance, an application dealing with patient information should support storage of structured data like patient information and unstructured data like ECG, x-ray report and other case history details. Both Security and Privacy are very important aspects in any type of storages. In the earlier versions of SQL Server though BLOB storage supported these requirement, it had its constraints. In order to address these requirenments, SQL Server 2008 has introduced FILESTREAM.

FILESTREAM is a new feature of SQL Server 2008 which allows large binary unstructured data like documents and images to be stored directly into an NTFS file system. FILESTREAM is not a Datatype like Varbinary(max). FILESTREAM is an Attribute or property which can be enabled on a Varbinary(max) datatype column, which instruct the database engine to store the data directly on the file system. Unstructred data like documents or images remain as an integral part of the database and maintain a transactional consistency. The highpoint of this feature is simultaneous performance of the file system as well as maintenance of transactional consistency between the Structured and Unstructured data.

FILESTREAM enables the storage of large binary data, traditionally managed by the database, to be stored outside the database as individual files, which can be accessed using an NTFS streaming API. Using NTFS streaming APIs allows efficient performance of common file operations while providing all the database services, including integrated security and backup.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can INSERT, UPDATE, DELETE, SELECT, SEARCH, and BACK UP FILESTREAM data. Win32 file system interfaces provide streaming access to the data. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system

FILESTREAM feature with other SQL Server editions

• SQL Server Express supports FILESTREAM and is not constrained by the 4GB limitation.
• Replication: The Varbinary(max) column which has enabled FILESTREAM from the publisher can be replicated to the subscriber. The Replicating table that has a FILESTREAM enabled column cannot be replicated to SQL Server 2000 subscriber. All other replication related limitations are applicable in this case as well.
• Log shipping supports FILESTREAM. It is essential that
 Both the primary and secondary servers have either SQL Server 2008 or a later version running FILESTREAM is enabled
• For Failover clustering, FILESTREAM file groups must be placed on a shared disk. FILESTREAM must be enabled on each node in the cluster that will host the FILESTREAM instance.
• Full-text indexing works with a FILESTREAM column in the same way that it does with a varbinary(max) column.

Pre-requisites for using FILESTREAM feature

• When a table contains a FILESTREAM column, each row must have a unique row ID.
• FILESTREAM data containers cannot be nested.
• While using failover clustering, the FILESTREAM filegroups must be on shared disk resources.
• FILESTREAM filegroups can be on compressed volumes.

In SQL Server, FILESTREAM data is secured by granting permissions at the table or column level, similar to the manner in which any other data is secured. Only if you have permissions to the FILESTREAM column in a table, you can access its associated files.

When to implement FILESTREAM feature
The size and use of the data, determines whether you should use database storage or file system storage. If the size of the data is small, SQL Server can store BLOB data in Varbinary(max)column in a table. This gives better performance and manageability.
Before opting for the FILESTREAM attribute check the following:-
• The average data size is larger than 1 MB
• Fast read access is required
• The Application is being developed using a middle tier for application logic

FILESTREAM Limitations
Listed below are the limitations one should be aware of prior to implementing this feature.
• Objects can be stored only on local volumes
• Size is limited to the volume size
• Not supported in database snapshot
• Database mirroring is not supported
• Transferent Encryption is not supported
• Can not be used in table valued parameters

Configuring FILESTREAM feature
To specify that a column should store data on the file system, assign the FILESTREAM attribute on a varbinary(max) column. This results in the Database Engine storing all the data for that column on the file system, but not in the database file. Before you start using FILESTREAM, you must enable it on the instance of the Database Engine. You should also create a local share for files to be stored.

Following are the steps to configure FILESTREAM:

Step 1: Create a folder in a Local Volume
Create a folder in a local volume where the files (documents/Images) will be stored.

In figure 1 above a folder called SQLSvr2008FILESTREAMDoc has been created within C:\ which will contain all the FILESTREAM objects.

Step 2: Configuring an Instance for FILESTREAM capability
By default, the SQL server instance is disabled for FILESTREAM storage. This property has to be enabled explicitly by using the sp_FILESTREAM_configure system stored procedure.

EXEC sp_FILESTREAM_configure
@enable_level = 3,
@share_name = "MyFILESTREAMSqlServerInstance";

There are two parameters to be provided for this sp, @enable_level and @share_name
• @enable_level = level
Note: This specifies the access level of FILESTREAM storage that you are enabling on the instance of SQL Server.Here level is defined as an integer and it can have a value of 0 to 3.

• @share_name = 'share_name'
Note: This specifies the file “share_name” that is used to access FILESTREAM values through the file system. share_name is a sysname. You can set the share_name value when you change the enabled state from 0 (disabled) or 1 (Transact-SQL only) to file system access (2 or 3).

You may check whether the instance is configured or not by running following query


Once you run this command in the Query analyzer, you can check whether the share is created or not from the CMD prompt. As shown in figure 2 below, Run NET SHARE in the CMD prompt and see the share with a name which we passed as the second parameter.

Note: When you run sp_FILESTREAM_configure, you might have to restart the instance of SQL Server. A computer restart is required when you enable FILESTREAM storage for the first time, or after you install any update to the RsFx driver. An instance restart is required when you disable FILESTREAM storage from an enabled state. To restart the instance of SQL Server, run the RECONFIGURE statement after you run sp_FILESTREAM_configure.

Requires membership in the sysadmin fixed server role and the Windows Administrators group on the local computer.

Step 3: Creat a Database with Filestream Capability
The Next step is to create a new database which will use the FILESTREAM capability.

FILENAME = 'c:\data\archdat1.mdf'),
LOG ON ( NAME = Archlog1,
FILENAME = 'c:\data\archlog1.ldf')

• The only difference in the above statement compared to a normal CREATE DATABASE statement is the filegroup creation for FILESTREAM objects.

• The following phrase in the above statement needs an explanation:
It is important to make note that the root folder must exist but not the subfolder. i.e. in the folder “SQLSvr2008FILESTREAMDoc” which has already been created in Step1, the subfolder (FILESTREAMdemo) must not exist.
Suppose within c:\ a folder called SQLSvr2008FILESTREAMDoc and a subfolder called FILESTREAMDemo' has been created. If the above mentioned script is run then we would get the following error :-

Msg 5170, Level 16, State 2, Line 1
Cannot create file 'c:\SQLSvr2008FILESTREAMDoc\FILESTREAMDemo' because it already exists. Change the file path or the file name and retry the operation.

Important : As mentioned earlier, it should be noted that the sub folder must not exist. SQL Server will automatically create this sub folder and grant appropriate permissions and other settings. If you drop the database the sub folder will automatically be removed by the system

Once the database has been created , the FILESTREAM folder is as shown in figure below:

Note: This FILESTREAM.hdr file should not be tampered with in anyway to ensure proper functionality. This file is exclusively maintained and used by sql server.

Step 4: Creat a table to store unstructrured data
The next step is to create a table. Once the database is enabled for FILESTREAM capability, you can create tables which have varbinary(max) column to store FILESTREAM objects.

Use Archive
CREATE TABLE Archive.dbo.Records
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[SerialNumber] INTEGER UNIQUE,

Note :Here the UniqueIdentifier column is a must. Each row must have a unique row ID. The column which should store BLOB should be of varbinary(max) and you should mention FILESTREAM Atrribute.

Step 5: DML Statements to store Filestream data
Now the database and the table are ready to be stored as BLOB objects.

Inserting data in the table
Inserting a row into a table which contains a FILESTREAM enabled column is just like any other t-sql insert statement. When you insert data into a FILESTREAM column, you can insert NULL or a varbinary(max) value.

(a) Inserting Null value to a varbinary(max) column
INSERT INTO Archive.dbo.Records
VALUES (newid (), 1, NULL);
Note: If the FILESTREAM value is null then the file in the file system is not created.

(b) The following example shows how to use INSERT to create a file that contains data. The Database Engine converts the string 'Seismic Data' into a varbinary(max) value. FILESTREAM will create the Windows file if it does not already exist. The data is then added to the data file.
INSERT INTO Archive.dbo.Records
VALUES (newid (), 3,
CAST ('Seismic Data' as varbinary(max)));
Note: There are many examples in BOL. Please refer BOL for more FILESTREAM DML statements

Step 6: Deleting FILESTREAM data
Delete From Records

T-SQL Delete statement removes the row from the table as well as the underlying file system files. The underlying files are removed by the FILESTREAM garbage collector.
Note: A FILESTREAM generates minimal log information and hence does not generate enough activity to fill up log files by itself. If there is no other activity generating log entries, CHECKPOINT may not be triggered. Hence, the files will not get deleted at the same time when you delete a corresponding row from the table. Physical deletion of the file happens only when CHECKPOINT occurs, and it happens as a background thread. You don’t see the file deletion immediately, unless you issue an EXPLICIT CHECKPOINT.

Delete From Records

Drop FILESTREAM Enabled Database
When you drop a FILESTREAM enabled database , the underlying folders and files are automatically removed.

Figure 4

Figure 4 shows the folder after dropping the database. The subfolder which was created has been removed with all the underlying files automatically
Note: In earlier versions , since the files were stored outside the database, even if you drop the database, the underlying folders and files will not be droped.

Restore the filestream enabled database from Backup
Restoration is as simple as any other normal database restore.

FROM DISK='E:\archive.bak'
WITH MOVE'Arch1' TO 'c:\data\archdat1.mdf',
MOVE 'Archlog1' TO'c:\data\archlog1.ldf' ,
MOVE 'Arch3' TO'c:\SQLSvr2008FileStreamDoc\filestreamDemo'
When the restoration scripts run, the database is restored and the corresponding files get restored in the location

MSDN Reference

Designing and Implementing FILESTREAM Storage

Though the earlier versions in SQL Server supported BLOB storage, the solution was a bit complex with less flexibility. FILESTREAM enables you to take advantage of both Database’s transactional consistency and Filesystem thus increasing the performance and decreasing the complexity. FILESTREAM also increases the maintainability because database backup is also applicable to FILESTREAM Objects. There is no need to take a separate backup of FILESTREAM objects. In essence, we have a single point of system for storing and managing all kinds of data (structured/unstructured). The size limitation is completely dependent on the disk size. This is another good reason to opt for FILESTREAM.

Tuesday, February 26, 2008

SQL Server 2008 - sp_repladdcolumn and sp_repldropcolumn deprecated

This is one of the common question in SQL Server 2000 DBA interview. How to add a column to a replicated / published table ? The answer is use sp_repladdcolumn and sp_repldropcolumn. If we get this question now, we may ask which version of sql server is this? Since SQL Server 2005 you may use simple DDL command like ALTER to add or drop a column from a published table (of course condition applies (***) like if the subscriber republishes the data then you should use the system sps). In SQL Server 2008 these system sps are deprecated, so you will not have this in next version.

SQL Server 2008- Compatibility Level Supported

This is one important thing to be known before switching or planning to switch to SQL Server 2008. SQL Server 2008 support only the last two versions compatibility levels ie. SQL Server 2000 (80) and SQL Server 2005(90). You can not set Compatibility level to 70 (SQL Server 7.0 ) in sql server 2008.

Saturday, February 23, 2008

SQL Server 2008 - Truncating Transaction Log

How to shrink (re-size ) the transaction log in SQL Server 2008

Scenario :

I have a database with the following size

SELECT name,size from sys.database_files

Test 173056
Test_Log 579072 -- 565 MB

Now I want to truncate Transaction log. In earlier version what we do is we truncate the log and shrink the file.

BACKUP LOG Test with Truncate_only

This statement throw an error with below message

Backup log Test with Truncate_Only
LHI-115\SQL2008(sa): Msg 155, Level 15, State 1, Line 1
'Truncate_Only' is not a recognized BACKUP option.

The errior is obevious, this command no more exists in SQL Server 2008. So the question is what is the alternative? As per books online “The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.” So this is the one command you should check before migrating to SQL Server 2008. If you have any script which have WITH TRUNCATE_ONLY option then you have to re-write the script.

How to shrink (re-size ) the transaction log in SQL Server 2008

As per books online , if you switch the Recovery Model to Simple inactive part of the transaction log should be removed. Let us see what happens

(a) select name,recovery_model_desc from sys.databases
name recovery_model_desc

Test Full

(b) Alter database Test SET Recovery simple

name recovery_model_desc

Test Simple

© select name,size from sys.database_files
Test 173056
Test_Log 451352 -- 440 MB

This process reduced the the transaction log file size. But not to the size what i want. I have no option to set the required size as we had this option in SHRINKFile. Do we need shrink the file after switching the Recovery model? Not sure. I have tested the same database in 2005 and I was able to shrink the file to 1024 KB.

I have tried to shrink the TL after switching the Recovery model and the result is as follows :-

DBCC SHRINKFILE (N'test_log' , 1)

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
6 2 451352 128 451352 128

So.. its obevious that its not shrinking. There is no active transaction in TL, that also i checked.

Note : From my SQL Server 2005 and 2000 server i restored few database in 2008 and tried to Truncate TL. For some database this process worked. others do not. I need to look into it what is the problem with the database which could not shrink.


In SQL Server 2008 , Transaction can be shrinked in two step.
(a) Change the Recovery Model to Simple
(c) Shrink the file using DBCC ShrinkFile

Wednesday, February 13, 2008

SQL Server 2008 - Transact-SQL Error List Window

SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Transact-SQL Query Editor. Its looks like more or less the Error List window available in .NET.

To display the Error List, do one of the following:
• On the View menu, click Error List.

In development environment this enhancement will really come handy .

SQL Server 2008 - Change Data Capture

What is Change Data Capture?

This new feature of SQL Server 2008 will capture all the data modifications(ie. DML- Insert /update / delete) on a table if you configure that table for Change Data Capture. SQL Server community were waiting for this feature for long time. Though earlier versions were provided with few functions and DBCC commands to read Transaction Log, it was never been a complete solution. In couple projects we had these kind of requirements and had to track each Data changes for Audit purpose. With lot of efforts we wrote our own application using Triggers and History table(s).

Change Data Capture is the mechanism to track and capture DML (Insert/Update/Delete) on a Table and stored the captured data in Relational format. A table or few columns in a table can be configured for Data capturing. All data manipulation language (DML) are read from the transaction log and captured in the associated change table. This may be considered as Customized Replication log reader. This is an asynchronous mechanism and there will not be any performance issue for the actual DML which changed the data what so ever. SQL 2008 has set of table valued function to query this trapped data to create report or synchronize standby db. Of course when you log / tack data you may have extra IO that you can not avoid. You can also capture only required columns changes from the table.

How it works?

Each insert or delete operation that is applied to a source table appears as a single row within the change table. The data columns of the row resulting from an insert operations contain the column values after the insert; the data columns of the row resulting from a delete operation contain the column values prior to the delete. An update operation, however, requires two row entries: one to identify the column values before the update and a second to identify the column values after the update.

Pre-Requisite :

(a) SQL Server 2008 Enterprise, Developer, and Evaluation editions : Many of use would not like to here this but it’s the fact.
(b) SQL Server Agent should be up and running


(a) You should not have any Schema already existing with the name cdc. change data capture requires exclusive use of the cdc schema and cdc user. If either a schema or database user named cdc currently exists in a database, the database cannot be enabled for change data capture until the schema and or user are dropped or renamed.

(b) The table columns which you are going to capture should not contain the following reserved column names: __$start_lsn, __$end_lsn, __$seqval, __$operation, and __$update_mask.

(c) captured_column_list cannot contain columns defined with a data type introduced in SQL Server 2008.

Best Practices

(a) Create a separate filegroup for change data capture change tables
(b) Configure only the required table and required columns for CDC


A member of SYSADMIN fixed server role can enable CDC on the database.
A member of DB_Owner fixed database role can enable can create a capture instance for individual source tables

My questions before starting Change Data Capture process testing

(a) What Edition will support this feature?
(b) Do we need to have Primarykey on the table which we need to track?
(c) What will happen when and CDC enabled table schema changes?
(d) Do we need to follow any extra care when we do Schema changes to CDC enabled tables?
(e) If you Disable CDC from a table, what will happened to the previous data captured.

1. Enable database for CDC

Check whether the database is already enabled CDC or not. Run this query
Select is_cdc_enabled From sys.databases where name='GPx'.

If this query return 0 then its not enabled.

USE AdventureWorks;
EXECUTE sys.sp_cdc_enable_db ;

When a database is enabled for change data capture, the cdc schema, cdc user, metadata tables, and other system objects are created for the database. Just make sure these objects are created.

2. Enabling a Source Table for Change Data Capture :-

By default, all of the columns in the source table are identified as captured columns. If only a subset of columns need to be tracked then use the @captured_column_list parameter to specify the subset of columns

Dynamic Management views for CDC.

Following DMVs can be used to display information about Change Data Capture and Log sessions

(a) sys.dm_cdc_log_scan_sessions
(b) sys.dm_cdc_errors
(c) sys.dm_repl_traninfo

Test Case :-

I have a table called CDCTest to be enabled for CDC and I planned to capture all the columns of the tables.

Enable Table CDCTest for Change Data Capturing

EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'CDCTest'
, @role_name = N'cdc_Admin';

Job 'cdc.GPx_capture' started successfully.
Job 'cdc.GPx_cleanup' started successfully.

Do some DML Operation on this table

delete top (3) from cdctest
Insert CDCTest (PLIContainerID,CenterTypeID,TotalVisits) select 1,222,333

Update CDCTest set Code='xxxxx' where plicontainerid between 1 and 20

Query Functions to get the trapped data.

DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
-- Map the time interval to a change data capture query range.
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

-- Return the changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_CDCTest(@from_lsn, @to_lsn, 'all');

Wrapper Stored Procedure to get CDC captured information for a given table

IF OBJECT_ID(N'dbo.spGetCDCInformationForATable') IS NOT NULL
PRINT 'Dropping Stored Procedure: [dbo].spGetCDCInformationForATable'
DROP PROCEDURE dbo.spGetCDCInformationForATable

PRINT 'Creating Stored Procedure: [dbo].spGetCDCInformationForATable'


Copyright 2008

Procedure : [spGetCDCInformationForATable]
Author :
on : <08-Feb-2007>
Description : Get Data captured through Change Data Capture for a given Table

Pending Issue : Nil

Returns : @Err

Date Modified By Description of Change

------- ---------------- --------------------------------

Create Procedure spGetCDCInformationForATable
@TableName sysname,
@SourceTableSchmea sysname,
@begin_time datetime,
@end_time datetime

DECLARE @from_lsn binary(10), @to_lsn binary(10);

-- Map the time interval to a change data capture query range.
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

-- Return the changes occurring within the query window.

Declare @SqlStmt nvarchar(1000)
Set @SqlStmt ='SELECT * FROM cdc.fn_cdc_get_all_changes_'+@SourceTableSchmea+'_'+@TableName+'(@from_lsn, @to_lsn, ''all'')'

Exec sp_ExecuteSQL @SqlStmt,@params=N'@from_lsn binary(10), @to_lsn binary(10)' ,@from_lsn =@from_lsn ,@to_lsn=@to_lsn


What will happen when and CDC enabled table schema changes?

You can very well add a new column. Obeviously, data in this column will not be tracked. Then what will happen if the column is dropped. CDC is not tracking any Schema Changes it seems. If you add or drop column it don’t care. CDC function still return the same number of columns as when the CDC is enabled

alter table cdctest Add column ColAddedAfterEnableCDC int

insert into cdctest(PLIContainerID,ColAddedAfterCDCEnabled) select 111111111 ,222

alter table cdctest drop column completedon

DDL modifications to the source table that change the source table column structure, such as adding or dropping a column, or changing the data type of an existing column, are maintained in the cdc.ddl_history table. These changes can be reported by using this stored procedure. Entries in cdc.ddl_history are made at the time the capture process reads the DDL transaction in the log.

You can track the DDL activity on a CDC enabled Table using sys.sp_cdc_get_ddl_history system stored procedure. This sp Returns the data definition language (DDL) change history associated with the specified capture instance since change data capture was enabled for that capture instance

Disable Change Data Capturing

sys.sp_cdc_disable_db_change_data_capture :
Disable Change Data Capturing on current database

Disable Change data capture for the specified source table in the current database. sys.sp_cdc_disable_table_change_data_capture drops the change data capture change table and system functions associated with the specified source table and capture instance. It deletes any rows associated with the specified capture instance from the change data capture system tables and sets the is_tracked_by_cdc column in the sys.tables catalog view to 0.

SQL Server 2008 - Table-Valued Parameter

It has always been a problem in earlier versions to pass multi row –multi column (record set) parameter from a Stored procedure or a function. In 2008 this problem is solved and now you can pass Table variable as parameter between stored procedures and functions.

How it works?
The first step in this is to create a User Defined Table datatype in the database. Once you created the User Defined datatype(UDT) of table you can declare a variable of that UDT as input parameter of a sp or function.

(a) Create a User TABLE Type

CREATE TYPE TestTableTypeParm AS TABLE (EmpID int primary key,
EmpType varchar(10),
EmpName varchar(50),
CHECK (EmpType IN ('MNGR', 'CLRK', 'TECH') ))

(b) Create stored procedure with a Table type as input parameter.
Create PROCEDURE spTestTableTypeParm

(@TVP TestTableTypeParm READONLY,
@someotherparam int )
Select *From @TVP

(c) How to call this stored procedure

Declare @TVP as TestTableTypeParm
Insert @TVP select 1,'MNGR','aaaa'
Insert @TVP select 2,'CLRK','bbb'
Insert @TVP select 3,'TECH','ccc'

Exec spTestTableTypeParm @TVP,1

(d) Create a function with table type input parameter

CREATE FUNCTION ufn_TestTablevaluedParameter(@TVP TestTableTypeParm READONLY)
select *From @TVP

(f) How to call function with tabletype input parameter

Declare @TVP as TestTableTypeParm
Insert @TVP select 1,'MNGR','aaaa'
Insert @TVP select 2,'CLRK','bbb'
Insert @TVP select 3,'TECH','ccc'

Select *From ufn_TestTablevaluedParameter(@TVP)

There are many Limitations mentioned in Books online. These are probably most significant among them

(a) SQL Server does not maintain statistics on columns of table-valued parameters.

(b) Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

(c) You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

(d) A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

(e) A DEFAULT value cannot be specified in the definition of a user-defined table type.

(f) The user-defined table type definition cannot be modified after it is created.

(g) User-defined functions cannot be called within the definition of computed columns of a user-defined table type.


(a) We can use declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
(b) The table valued variable data can not be modified inside the object where its referred. It has to be READONLY.
© In SQL Server 2000, it was not allowed to insert the output of an stored procedure to a table type variable. This was solved in SQL Server 2005 and it was possible to insert the output of an sp to table variable. But Table-Valued parameter has again have this limitation. You can not use in Select Into or INSERT EXEC.
(d) sys.table_types : This system object will retrun all the Table types in the database.
select *from sys.table_types

SQL Server 2008 - New functions for Date and Time

I am not aware of any functions date function deprecation in 2008. There are new functions added to compliment new datatypes.

(a) SYSDATETIME() : Returns the current database system timestamp as a datetime2(7) value. The database time zone offset is not included. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
select SYSDATETIME() : Result : 2008-02-10 10:12:54.8750000

(b) SYSDATETIMEOFFSET () : Returns the current database system timestamp as a datetimeoffset(7) value
select SYSDATETIMEOFFSET() --Result : 2008-02-10 10:12:09.6406250 +05:30

(c) SYSUTCDATETIME() : Returns the current database system timestamp as a datetime2(7) value. The database time zone offset is not included.
Select SYSUTCDATETIME() –- Result 2008-02-10 04:44:52.1875000

(d) SWITCHOFFSET() : Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.
SELECT SWITCHOFFSET ('2008-02-10 7:45:50.71345 -5:00', '-08:00') as SWITCHOFFSETResult

(e) TODATETIMEOFFSET () : Changes the time zone offset for a date and time value. This function can be used to update a datetimeoffset column.
SELECT TODATETIMEOFFSET ('2008-02-10 7:45:50.71345 -5:00', '-08:00') as TODATETIMEOFFSET

Books online have sample script to explain these functions.

SQL Server 2008 - New Datatypes

Major enhancement in this front is new Date and Time datatypes and related functions.

Here Overview of the new Date and time datatypes

(a) Date : Date only which can store date raging from 0001-01-01 through 9999-12-31 ie. January 1, 1 A.D. through December 31, 9999 A.D. The storage size is 3 byte and Date datatype gives One day accuracy.

(b) Datetime2 : Its an extension to existing Datetime datatype and datetime2 can have larger range and accuracy. Existing Datetime range limitation is January 1, 1753, through December 31, 9999. Datetime2 provides range from 0001-01-01 through 9999-12-31. The storage size is 8 byte for both datetime2 and datetime. For all new developments its recommended to use Datetime2 because is more portable and it gives more second precision.

(c) Time : Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. This provides more accuracy in terms of time than existing datetime datatype. Time datatype ranges 00:00:00.0000000 through 23:59:59.9999999 where as existing datetime data type only gives 00:00:00 through 23:59:59.997. It also provides a feature in which user can specify the precision they wanted from 0 to 99 nanoseconds. The storage size is 5 bytes, fixed, is the default with the default of 100ns fractional second precision.
SELECT CAST(getdate() AS time) AS 'time' – Default
Result : 09:43:13.6230000
SELECT CAST(getdate() AS time(2)) AS 'time' – User specific 2 percision
Result : 09:43:57.69
In the above example, the figure within brackets (ie 2) specify what precision we want. By default (if we not specify) its 7.

(d) datetimeoffset : Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. datetimeoffset have three parts. Date part , time part and timeoffset part. Datetimeoffset ranges from 0001-01-01 through 9999-12-31 and it takes 10 byte for storage.

(e) Hierarchyid : I have not tried this yet, for now I just wanted to mention that there is a new system datatype called Hierarchyid in SQL Server 2008. Books online has very detailed section on this, I need to check that


These date and time datatypes are definitly mosuse wanted datatypes for long time. For any new development purpose use these new datatypes available. I need to explore more with these datatypes. Books online has very detailed documentation with examples which needs to be referred.

SQL Server 2008 - MERGE DML Statement

What is MERGE Statement ?

Merge is a Single DML statement which can be used in place of multiple DML statements. In other words, it can do Insert/Update/Delete in single statement itself. MERGE will be ideal choice in ETL scenario or in a data synchronizing application. MERGE is SQL 2006 Standard compliant

How MERGE works ?

Merge statement has a source and target object. Source can be a table , query , view which provides the data to merge into target table. Target can be a Table or updatable view.

In ETL process , it is a very common to synchronize target table from a source. So what we generally do ? What all are the condition we check ?

(a) The new rows in the source which is not there in the target needs to be inserted
(b) The rows which is matching according to the specified matching columns needs to be updated.
© The extra rows in target which is not there in Source should be deleted

In earlier version , we had to write this logic in three different statements.

Test Case

I have two table MergeSource and MergeTarget and schema is something like this

Create table MergeTarget (Col1 Int, Name varchar(30), Address varchar(100))

Create table MergeSource (Col1 Int, Name varchar(30), Address varchar(100))

I have target tables with rows as follows :-

Insert MergeTarget select 1,'Madhu','Chennai'
Insert MergeTarget select 2,'Mike','delhi'
Insert MergeTarget select 4,'aaa','bangalore'

And the source table rows are as follows :-

Insert MergeSource select 1,'Madhu','Hyderabad'
Insert MergeSource select 2,'Mike','delhi'
Insert MergeSource select 3,'Ram','bangalore'

Points to be noted

(a) Source table have new address value for ‘Madhu’. Which needs to be updated (only address column). Ie. Row exists but different data
(b) Source table have a new row col1=4 and name=’aaa’ which needs to be inserted to target
© Target table have an extra row (col1=3 and name=’Ram’) which is not there in the Source , which needs to be deleted from target.

The Merge statement for the above requirement will be some thing like this

Merge Mergetarget T USING MergeSource S
On T.Col1=S.COl1


What I learnt here

(a) Merge works only with single target table.

(b) You need terminate merge statement with semi-colon (;)
Msg 10713, Level 15, State 1, Line 6
A MERGE statement must be terminated by a semi-colon (;).

(c) Merge Statement will fail when a target row matches more than one source row (Source table have duplicate rows for the given condition). For eg. In my given scenario if I have two rows for col1=3 in Source table the merge statement will fail with the following error message :-

Msg 8672, Level 16, State 1, Line 2
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

I think the error message is self explanatory and you must use GROUP BY to group source rows.

OUTPUT Clause with MERGE Statement

The OUTPUT clause (which was introduced in SQL 2005 in conjunction with Merge is very powerful. You can get which action and fired and what is the value.

Merge Mergetarget T USING MergeSource S
On T.Col1=S.COl1


OUTPUT $action, Inserted.Col1,deleted.col1;

Screen Short of Merge with OUTPUT clause.

Source replaced with a Query ( It can be Query / Table / View)

Mergetarget T
( SELECT 1 as Col1,'Madhu' As Name ,'Hyderabad' as Address
SELECT 2,'Mike','delhi'
SELECT 3,'Ram','bangalore' ) S

On T.Col1=S.COl1

OUTPUT $Action, Inserted.Col1,deleted.col1,inserted.address;


No doubt this is one of the major feature of SQL Server 2008. As Data migration is part of more or less all the projects this will be one of the desired DML statement of SQL Developers.


It looks really good. I tested it and its really gives me 3-4 times better performance and less size than Normal / NoCompressed Backup

Compressed Backup

--BACKUP DATABASE successfully processed 172926 pages in 40.806 seconds (34.715 MB/sec).

Not Compressed Backup

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabaseNoCompression.BAK'
BACKUP DATABASE successfully processed 172926 pages in 111.007 seconds (12.761 MB/sec).

See the time taken; its almost 1/3 of NoCompression Backup. The size of the backup files Compressed is 197 MB and Uncompressed is 1386 MB.

So next question is , is there any difference in restoring from these two backups. How the compression is affected Restoration? So here we go…

Normal / NOCOMPRESSED Database Backup Restoration Result

RESTORE DATABASE successfully processed 172927 pages in 245.748 seconds (5.764 MB/sec).

COMPRESSED Database Backup Restoration Result

RESTORE DATABASE successfully processed 172927 pages in 199.832 seconds (7.089 MB/sec).

See the time each one took. The time taken in restoration from Compressed Backup took less time . So it seems to be a very good enhancement.

By default this property is Off. Ie. Backup are No Compression. You can set this server property true to make By default all backups are compressed. See Books Online Backup Database page for more details

I would love to use this. But if this feature only available in Enterprise Edition then? That we needs to wait and see.

SQL Server 2008 - Assignment operators

For instance, while looping when we need to increment counter, in earlier version we used to set the the counter=Counter+1 kind of code. But new assignment operator will make assignment more simple (may be less readable). This may be useful in Join

SQL 2005/ 2000 way of Assigning .

Declare @Counter int
Set @Counter =1
While @Counter <10
Print @Counter
Set @Counter =@Counter +1

SQL 2008 way of Assigning .

Declare @Counter int=1
While @Counter <10
Print @Counter
Set @Counter +=1

Basically, in earlier versions, equal sign (=) was the only Transact-SQL assignment operator. Now in 2008 you have += , -=, *=, /=

SQL Server 2008 - Declaration and initialization of Variable

I would say one of the simple but genuine enhancement in 2008 is Declaration and initialization of variable in the same step. Something like this

Declare @Variable Varchar(10) =’Madhu’.

Earlier vrsion we had to do this in two steps.

Declare @Variable Varchar(10)
Set @Variable=’Madhu’ OR Select @Variable=’Madhu’

Small but very effective

SQL Server 2008 - IntelliSense

Since the release of SQL 2008 , i have been attending webcasts , usergroup meeting and Microsoft virtual lab courses blah.., blah... to get acquaintance with the product . Its really nice to hear that many features pending for long time is addressed in this version. There are many new feature even if it looks small make the programmer life very easy.

You can get the list of new features from Microsoft site. Check white paper

But, here what I am intended to do is, try my self first and then pen down my understanding and comment about the feature. I planned to start from simple feature and here I go…

The first thing one observe when open a Query Analyser in SQL Server 2008 is IntelliSense. Though I am not used to Intellisense, but I have seen many folks are very found of these kind of feature and uses third party tools in earlier SQL versions. This is of course a nice feature. But there are few discrepancy with BOL and the functionality available in Management Studio. Few setting like Parameter Info is not currently available. I may be wrong, if not I hope RTM version will address this. You can have line number kind of options available in QA itself. Just you need to switch on that feature from Tools à Options. This of course demand more tweaking and its worth to do also.

I think there is some issues. I tested the following script

create table test(col int,col1 int)
If i type select *from dbo. I should get the objects in DBO schema and current i am not getting this. It may be a bug.