Microsoft Technologies, ASP.NET & SQL SERVER Tips with Rajat Jaiswal

See beyond the SQL SERVER 2005 – Latest version of SQL SERVER “Denali”

Hello friends,
As you know i am SQL SERVER 2005 user now i am moving on some latest tool to play with so here we go.

Microsoft introduced a new CTP 3 released for SQL Server Latest Version “Denali”.Now if you go through in detail of this you will find “Denali” having many new features and lots of new learning for us.
Today I am going to share one the new updates in this version which is Data Types. Although many things you will find in just previous version like SQL SERVER 2008 & R2 but I am comparing it with SQL SERVER 2005 version.

So, in Microsoft SQL SERVER latest version “Denali” you will find many new data types like Time, DateTime2, Date Time Offset, Geography, Geometry, and Hierarchy Id.

So here we will discuss all the above data type one by one.1) Time:-
Sometime the business need is to capture time only at that in our previous SQL Server version we don’t have any specific Time Data type if we need to capture time then we use date Time data Type but now in “Denali” we have specific Time data Type . Which store time in hh:mm:ss[.nnnnnnn]. Below is the range for each individual area.
hh is two digits, ranging from 0 to 23, that represent the hour.
mm is two digits, ranging from 0 to 59, that represent the minute.
ss are two digits, ranging from 0 to 59, that represent the second.
n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
We can store time up to 7 precision. And it requires 5 byte for storage.

2) DateTime2:-
DateTime2 is an addition on Date Time data type. It is more robust more depth range. It store data in YYYY-MM-DD hh:mm:ss[.fractional seconds] format. And it has huge date time range. The date range starts with 0001-01-01 through 9999-12-31. It requires 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

3) DateTimeOffset:-
By the name it is clear that it store Time offset. Some time we have to store data time zone wise like “2011-09-12 12:30:30.12345 -07:00” then in that situation this data type is very useful. The default storage template is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. And it requires 10 byte to store data. And below is range of each individual factor.
YYYY is four digits, ranging from 0001 through 9999 that represent a year.
MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.
hh is two digits, ranging from 00 to 23, that represent the hour.
mm is two digits, ranging from 00 to 59, that represent the minute.
ss are two digits, ranging from 00 to 59, that represent the second.
n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
hh is two digits that range from -14 to +14.
mm is two digits that range from 00 to 59.4) Geography: – (Available in SQL SERVER 2008 also I am comparing with SQL SERVER 2005)
You can find geography data type in SQL SERVER 2008 also but it is really new for me. By the name it clear that it will restore some geography related things. And great our sixth sense worked here it store geography coordinates like latitude, longitude. It store coordinate that is relevant to GPS. Later on we will discuss it separately in my new post specific for this data type.5) Geometry: – (Available in SQL SERVER 2008 also I am comparing with SQL SERVER 2005)
Again this is old data type you can find in SQL SERVER 2008 but it is really new for me. So again by the name it is clear that it store geometry instance like point, rectangle, path etc. Mostly it is 2 dimension coordinate for x and y axis. We will discuss it later on separately in my specific post for this.

6) HierarchyId: – (Available in SQL SERVER 2008 also I am comparing with SQL SERVER 2005)
This is something more unique and attractive data type in “Denali” and I am pretty much sure that I will use in my coming up projects. It uses to save hierarchy id. The database use varBinary to store internally. You can easily traverse the hierarchy data by using some in build function provided by Microsoft SQL SERVER. You can move in both direction top to bottom or bottom to top easily. We will go in detail later on.
I hope you like many new data type in latest version of SQL SERVER. In next post I will provide some great function and features introduced in “Denali”.