SQL Server Examples > Blogs - SQL Server 2008 DATATIME2 data type

Location: Blogs Greg
Posted by: greg	3/19/2008 10:01 PM
I was spending some time over the last few weeks researching new features in SQL Server 2008 for a presentation I gave today at the Olympia Area SQL Server User Group in Tumwater, Washington, USA. So of course I had to take a closer look at the new date/time data types as part of this review. Most everyone knows that there are a number of different date/time related data types coming out with SQL Server 2008. But for this BLOG post I want to focus in on just one new data type, DATETIME2. The new DATETIME2 data type is much like the existing DATETIME data type, except that it allows more percision for the time portion of the date/time. This new data type can now support time values that are accurate to 100 nanoseconds. This new DATETIME2 data type allows you to specify how much percision you want to store in the time portion. The more percision you want the more disk space it take. A DATETIME2 data type takes from 6 to 8 bytes depending on the percision. While I was building my examples for my user group presentation I built some queries that showed how much storage was taken for each of the different percisions. While building my DATALENGTH function calls for different DATETIME2 percisions I found out something amazing about the DATETIME2(3) format. If you use a DATETIME2(3) format for a date/time column instead of a DATETIME data type it stores the same data value but using one less byte. Thats right 7 bytes instead of 8. Now I know saving 1 byte doesn't seem like a lot. But now multiple that by the number of different dates you might have in your 10 million record table. That savings of 1 byte could account for a substainal savings of disk space. I know disk space is cheap these days. But if you use less disk space to store your records then that equates to more records read per I/O, and faster query performance. So if you like the exiting percision of DATETIME, then remember to use DATETIME2(3) data type when you start building SQL Server 2008 applications. Happy database designing, Greg Larsen, MCITP

Permalink \| Trackback

Comments (3)

problem to kill user process

By sqlserverdba76 on 3/24/2008 1:17 PM

Hi
Having problem to kill user process.

OS: windows server 2003
database version: sql server 2005

i want to kill user process.when i tried to kill user process using its spid... kill 57
i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds.
i also found that this process has suspended status and wait_type is BACKUPTHREAD
one more thing i wait to complete rollback transcation since last 3 days.
but after 3 days getting same error and estimated time increase every time i issue KILL command.
even i tried activity monitor to kill process.but no luck
is there any way to slove this issue???
how to kill this process.??
well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue.
your help is really appreciate.
thanks,
saurabh

Re: SQL Server 2008 DATATIME2 data type	By greg on 4/3/2008 9:22 PM
Sometimes the only way to get rid of user is the stop and restart SQL Server. I know not a very nice option.

Re: SQL Server 2008 DATATIME2 data type	By fairystar on 5/15/2010 9:54 PM
In Bootsue.com, MBT shoes sale, MBT sneakers in our MBT shoes store on line. Cheap and discount MBT shoes wholesale, popular style, high quality and fast shipping. MBT shoes on sale for you, do not miss it.