TIME data type in SQL Server 2008

SQL Server 2008 has introduced the TIME data type to store Time without the Date. Before SQL Server 2008, we had to use the DATETIME data type to store Time only data, which was an absolute waste.

Usage of this data type is quiet straight forward.

DECLARE @tm TIME

SET @tm = '12:34:56.1234'

It accepts most common time formats in string format. Assigning a normal DATETIME data to TIME data type is also possible, in which case, only the Time part of the DATETIME data will be stored.

You can also provide an accuracy value for the data type while declaring:

DECLARE @tm TIME(5)

SET @tm = '12:34:56.1234567'

PRINT @tm

-- Output: 12:34:56.123457

The default accuracy is 7 digits. But depending on the application of the data type, you can even set it to 0.

Technorati Tags: SQL Server,time
del.icio.us Tags: SQL Server,time

SQL Server 2005 Paging – The Holy Grail

The paging and ranking functions introduced in 2005 are old news by now, but the typical ROW_NUMBER OVER() implementation only solves part of the problem.

Nearly every application that uses paging gives some indication of how many pages (or total records) are in the total result set. The challenge is to query the total number of rows, and return only the desired records with a minimum of overhead? The holy grail solution would allow you to return one page of the results and the total number of rows with no additional I/O overhead.

In this article, we're going to explore four approaches to this problem and discuss their relative strengths and weaknesses. For the purposes of comparison, we'll be using I/O as a relative benchmark.

Read More About This at SQLServerCentral.com