Deprecated Datatypes

In case any sql dev’s wasn’t aware, TEXT and NTEXT will be deprecated. Seems like SQL 2008 could be the last version to have them, in any case I guess we should all think now about changing columns to VARCHAR/NVARCHAR(max) sooner rather than later.

A Date with Dates

There are some interesting changes in the upcoming release of SQL Server 2008, some of which are powerful and sexy (“Spatial data” anyone?) and some have a less of a wow factor but never less are extremely useful. As a start, I wanted to write a special introductory post about the new date and time datatypes.

In the currnet (2005) and previous versions of Microsoft’s SQL Server there is no date specific datatype to store only the date element. You must use the DATETIME or SMALLDATETIME types. Working with the data sometimes became a chore, especially but not exclusivily when designing reporting solutions. We had to implement CAST and or CONVERT hacks to get around this. How many of us have had to write something along the lines of:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
CAST(CAST(FLOOR(DateEntered) AS FLOAT) AS DATETIME) >= @StartDate
AND < CAST(CAST(FLOOR(DateEntered) AS FLOAT) AS DATETIME) <@EndDate

Well, not anymore. Assuming of course you don’t want the time element of a date, it is now possible to use the new DATE datatype. The following example will output the date without the time element:

DECLARE @MyDt AS DATE
SET @MyDt = GETDATE()
PRINT @MyDt

The previous query can be simplified like this:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
DateEntered >= @StartDate
AND < DateEntered <@EndDate

Also , if you still need the granularity of the time the query is still a lot simpler than before:

SELECT
OrderId, OrderValue
FROM
OrderItem
WHERE
CAST(DateEntered AS DATE) >= @StartDate
AND < CAST(DateEntered AS DATE) <@EndDate

The range of this new DATE datatype is from 0001-01-01 through to 9999-12-31. The TIME datatype works in much the same way, it stores only the time element with a range of 00:00:00.0000000 through to 23:59:59.9999999 – providing a very useful type for certain applications.

Lastly, there is an unusual hybrid datatype called DATATIME2. Ok, not the greatest named datatype in the history of naming but it is very interesting. What this allows us to do is store a DATETIME with fractional granuluarity. For instance , do you want to store the date/time without the milliseconds? No problem for Mr DateTime2:

 

DECLARE @MyDt DATETIME2(0)
SET @MyDt = GETDATE()
PRINT @MyDt

These are the other available fractional settings you can use:

 

Fraction	Output
0	2007-10-28 22:11:20
1	2007-10-28 22:11:19.7
2	2007-10-28 22:11:19.70
3	2007-10-28 22:11:19.703
4	2007-10-28 22:11:19.7030
5	2007-10-28 22:11:19.70300
6	2007-10-28 22:11:19.703000
7	2007-10-28 22:11:19.7030000

 

I will be delving deeper into the datatypes in future blog posts.

 

 

 

 

Sparse Columns

SQL Server 2008 (“Katmai”) introduces something called Sparse Columns. What this allows you to do is to define a column that rarely contains data. When this is defined the column does not take up any space until it is used. An example of the code required to define the column is shown below.

CREATE TABLE Product

(

ProductId INT IDENTITY(1,1)
,ProductName VARCHAR (80)
,ColourMatch INT SPARSE

)

I will blog more about this features once I have had the chance to play with the CTP 6.