SQL Server 2008 new datetime types, functions and the conversions between them

April 27, 2010 3 comments


Datetime Types in SQL Server 2005:

Lets start with a quick look at the existing date time types in SQL Server. The datetime and smalldatetime, these two types are well known to us, So we wont be spending much time here.

Data Type Representation Accuracy
smalldatetime YYYY-MM-DD hh:mm:ss 1 minute
datetime YYYY-MM-DD hh:mm:ss[.nnn] 0.00333 second


Though these types provide a whole lot of datetime functionality required for any database. The problem is that the date representation is always combined with the time in both the types and there is no way to represent the date only component or time only component in a database with out doing a lot of cast and convert.

New Datetime Types in SQL Server 2008

SQL Server 2008 has introduced four new datetime data types for the date and time representations in SQL Server Databases.   

Data Type Representation Accuracy
date YYYY-MM-DD 1 day
time[n] hh:mm:ss[.nnnnnnn] 100 nanoseconds
datetime2[n] YYYY-MM-DD hh:mm:ss[.nnnnnnn] 100 nanoseconds
datetimeoffset[n] YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 100 nanoseconds


The new Date type allows us to represents the date as an individual component with out the time field attached to it. And the Time type represents the Time as an individual component in databases. Note that the new Time type is at higher precision than the regular time in datetime type.

Datetime2 can be considered as an extension to regular datetime type with the time representation at an accuracy of 100 nanoseconds than at the regular 0.00333 seconds. The datetime2 type can be used with a default precision (datetime2(7)) or can be used with a user defined precision like datetime2(n) format.

If the time precision of datetime2 is set to 3 (datetime2(3)) which gives the exact equivalent representation of a regular datetime type.

DateTimeOffset is the combination of default datetime2 with the system time zone offset attached to it. The time zone offset displayed based on the Operating System date, time and culture settings. The offset should be between –14:00 to +14:00. The DateTimeOffset type does not support the day light saving times.

Here is my machine current datetimeoffset value 2010-04-25 23:24:07.7086518 +05:30. Notice that the time zone offset returned is +05:30 i.e. IST (Indian Standard Time) attached to datetime2 data.

New Date and Time Functions

SYSDATETIME() Similar to GETDATE() but it returns the current system date as new datetime2(7) type
SYSUTCDATETIME() Similar to GETUTCDATE() but it returns the current system date in UTC as a new datetime2(7) type
SYSDATETIMEOFFSET() Returns the current system date time as DateTime2(7) with system time zone offset attached to it
SWITCHOFFSET(expr, tz) Function to convert the datetimeoffset in one time zone to a datetimeoffset in another timezone
TODATETIMEOFFSET(expr, tz) Function to convert the datetime, datetime2 or datetimeoffset to a datetimeoffset with the specified time zone


Conversions between different datetime types

        1. How to convert datetime or datetime2 to datetimeoffset with specified time zone?

 -- results: 2010-04-26 00:18:36.927  | 2010-04-26 00:18:36.927 +08:30

-- results: 2010-04-26 00:18:36.9316407 | 2010-04-26 00:18:36.9316407 +05:30

TODATETIMEOFFSET() combines the specified datetime or datetime2 to the specified time zone offset.      
       2. How to convert the datetimeoffset in one time zone to datetimeoffset in other time zone? 

-- my current system time in IST (GMT + 5:30)
       -- 2010-04-26 00:32:48.1838343 +05:30       

-- IST (GMT + 5:30) converted to North America PST (GMT - 8:00)
       -- 2010-04-25 11:02:48.1838343 -08:00

-- IST (GMT + 5:30) converted to Europe BST (GMT + 1:00)
       -- 2010-04-25 20:02:48.1838343 +01:00


       3. How to convert local datetime or datetime2 to UTC (Coordinated Universal Time)? 

DECLARE @LocalDateTime datetime
DECLARE @TimeZoneOffset INT

-- convert my current system datetime to UTC
SET @LocalDateTime = GETDATE()
     -- 2010-04-26 00:44:19.107

-- get the timezone offset of the system
     -- 330 mins (i.e. 5:30 )

-- get the utc datetime
SELECT DATEADD(MI,@TimeZoneOffset, @LocalDateTime)
      -- 2010-04-25 19:14:19.107 


       4. How to convert datetimeoffset to UTC?

-- convert my system datetimeoffset to UTC
-- 2010-04-26 00:50:35.3701337 +05:30

-- switch offset to '+00:00' i.e. UTC
-- 2010-04-25 19:20:35.3701337 +00:00  | 2010-04-25 19:20:35.3701337

       5. Other simple conversions? 

      ,CAST(GETDATE() as date)
 -- results: 01:03:35.5700000 | 2010-04-26 | 2010-04-26 01:03:35.5971486

-- get timezone offset from datetimeoffset
 -- results: 330 (number of minutes)


The new date time types in SQL Server 2008 are more portable than datetime or smalldatetime and it is recommended to use them in the future development work.

Hope the post is helpful.

🙂 ranjith🙂

Dynamically getting the execution status of a SQL Server Agent Job

April 23, 2010 2 comments


This problem seems trivial but there is no straight forward way to get this information. If you have tried the INSERT into EXEC command to insert the results of the sp_help_job procedure into a temporary table like below then you are familiar with the below error.

insert into #jobstatus
execute msdb..sp_help_job

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

So you can not directly insert the results to a temp table from this procedure. Here is a trick I have used using the OPENROWSET to get around with the INSERT EXEC problem. This query results can be stored into a temporary table or a table variable using the INSERT INTO or SELECT INTO command which can be used for further processing.

   select name, case when current_execution_status = 1 then ‘Executing’
when current_execution_status = 2 then ‘Waiting For Thread’
when current_execution_status = 3 then ‘Between Retries’
when current_execution_status = 4 then ‘Idle’
when current_execution_status = 5 then ‘Suspended’
when current_execution_status = 6 then ‘[Obsolete]’
when current_execution_status = 7 then ‘PerformingCompletionActions’
else NULL end as [status]  from
(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’,‘EXEC msdb..sp_help_job’)

The OPENROWSET is one of the workarounds for the nested INSERT EXEC problem and many other solutions are proposed here (Thanks to Kalman for the workarounds).

Hope it helps

🙂 ranjith🙂

SQL Server 2008 R2 Free eBook from Microsoft Press

April 22, 2010 Leave a comment

Just came to know about this book. More details on the Microsoft Press Blog.

You can download the ebook in XPS format here and in PDF format here.

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189


🙂 Ranjith🙂

Categories: SQL Server Tags:

Script to drop all objects of a Schema in SQL Server

January 31, 2010 8 comments


I have spent almost 3 hours to complete the script and test it for couple of sample scenarios during this weekend (31/01/10).  The script initially drops all the objects of the schema and then drops the schema itself. It takes care of all the object dependencies with in the schema by dropping all of them in a specific order that will resolve the dependency issues.

Download the SP created using the script from here (MS Word Document) or use this Google Doc link to view the script(Thanks to Elias for the link).

The stored procedure takes two parameters SchemaName and the WorkTest. Use WorkTest equal to ‘t’ to print all the drop statements without executing them or specify anything else to execute the drop operations. The default option is ‘w’ i.e. Work for me.

EXEC CleanUpSchema 'MySchema', 't'        -- debug
EXEC CleanupSchema 'MySchema', 'w'        -- work for me


These are the known limitations of the script

  • It can not drop a PK table in the schema with an XML index or Spatial index defined
  • It can not drop the schema which is referred by a XML schema collection
    Please let me know if you find any more issues with the script. I will list all of them here for others reference and will fix them to improve it further.


Hope it helps

🙂 ranjith🙂

How to get the File Group of a Table or an Index?

January 31, 2010 2 comments


The file group information of both an Index and a Table are stored in the sys.indexes table. You might wonder how the tables file group is stored in sys.indexes metadata table. It is because when ever a clustered index is created on a table in SQL Server the physical data pages of the table will be made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the clustered index file group is the file group of the table.

If there is no clustered index on the table then it is represented as a HEAP with index_id equal to ZERO in sys.indexes table. 

At any point a clustered index with index_id equal to 1 or a heap with index_id equal to ZERO exists for a table in sys.indexes table.  All non clustered indexes will have index_id greater than 1.

Below query gets the file group of the table Employee in HumanResources schema of AdventureWorks database.

 SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.index_id<2                     -- could be heap or a clustered table
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

And below query gets the file group of the index ‘AK_Employee_rowguid’ on Employee table in HumanResources schema of AdventureWorks database.

 SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.name = 'AK_Employee_rowguid'        
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

Please note that schema_id is important otherwise both the queries might return multiple results if you have same table name on different schemas.

Hope it helps

🙂 ranjith🙂


January 10, 2010 12 comments

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?

Below is the typical usage of these options.

 -- select employees
 SELECT * FROM HumanResources.Employee

Lets first understand what they exactly mean to SQL Server and then we will move on to why they are special.


It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes.

When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a Literal.



SELECT "sometext" AS Value   -- FAIL because “sometext” is not a literal 

Though the SELECT” and “TABLE” are reserved keywords  we are able to create the table because they are now treated as identifiers and the T SQL rules for identifier names are ignored.

When it is set to OFF any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.



SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated as a literal

You can clearly see the difference in CREATE TABLE and SELECT query. Here the CREATE TABLE fails because “SELECT” is a reserved keyword and it is considered as a literal.
The default behaviour is ON in any database.

The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL.
And when it is set to OFF any comparison with NULL using = and <> will work as usual i.e. NULL = NULL returns true and 1= NULL returns false.
 PRINT 'same'
 PRINT 'different'
--result:  different

 PRINT 'same'
 PRINT 'different'
-- result: same 

The default behaviour is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

Why are these two options Special?:

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET SQL Server remembers those settings. And every time the stored procedure is executed it always uses the stored settings irrespective of what the current user session settings are. So the behaviour of the stored procedure is not altered by the calling session settings and the usage of the SET option behaviour inside the SP is always guaranteed.

You can get any procedure or trigger or function settings for these options from the sys.sql_modules metadata table.

SELECT uses_ansi_nulls, uses_quoted_identifier
 FROM sys.sql_modules
WHERE object_id = object_id('SampleProcedure')

And if you need to guarantee the behaviour for other SET options like SET ARITHABORT inside the SP then you need to SET them inside the procedure. The scope of the options specified inside the procedure is only applicable until the procedure completes its execution.

Hope it helps.

🙂 ranjith🙂

SQL Server: ODBC, OLEDB and SQL Native Client Providers

December 22, 2009 Leave a comment


We have different providers to use (Microsoft OLE DB Provider for SQL Server or SQL Native Client) while creating SQL Server linked servers, or connecting to other sources like Excel, Access DB etc. This post gives brief overview of each of these providers and the difference between them.

ODBC is the programming API for connecting to any database management systems independent of vendor, operating system and programming languages. This was the first driver software designed for interacting with different Database Management Systems.

OLE DB is the Microsoft specific Data Access API for connecting to various Database Management Systems and extends the functionality of ODBC. This API was built using Microsoft COM technology and is part of the MDAC (Microsoft Data Access Components) which is installed with the Operating System or can be installed separately. MDAC includes the support for OLE DB, ODBC and ADO.

Till the SQL Server 2000 the SQL OLE DB and SQL ODBC connectivity was provided by MDAC package. But from SQL Server 2005 we have two different APIS

1. The Original MDAC which comes with OS

2. New API for SQL OLE DB, SQL ODBC and ADO which is called the SQL Native Client.

SQL Native Client combines the SQL OLE DB and SQL ODBC in MDAC into one DLL (SQLNCLI.DLL) which is shipped with the SQL Server and not part of the Operating System. So there is no dependency on the Operating System. As this DLL is shipped with the SQL Server every new release of the product might have a new version of SQL Native Client.

Now if you see the list of providers in SQL Server 2008 linked server creation wizard below; You can figure out that the providers listed with the prefix Microsoft are from the MDAC and SQL Native Client 10.0 is the SQL Server 2008 provider for SQL OLE DB and ODBC.




To create a linked server to SQL Server 2000 database we should use MDAC provider (Microsoft OLE DB Provider for SQL Server)  and to SQL Server 2005 database we can use SQL Native Client provider or MDAC provider if you are not plannng to leverage the following new features offered by SQL Native Client.


  • MARS (Multiple Active Result Sets in a single connection)
  • XML Data Types
  • CLR User Defined Types
  • Asynchronous Data Access
  • Database Mirroring
  • Snapshot Isolation

Is there a Problem to existing Applications that use MDAC SQL OLE DB or ODBC connectivity on SQL Server 2005?

For the applications that use SQL ODBC and SQL OLE DB from MDAC there is no impact. But if you want to utilize the new features in SQL Server 2005 like UDTs or XML Types you need to use the SQL Native Client otherwise your applications should work fine.


For more information on the MDAC architecture you can go here.

🙂 ranjith🙂