• Login
• Become a member
• RSS
• Part of the TechTarget network

Microsoft just released the next CTP (Customer Technology Preview) of Microsoft SQL Server 2008. In the software's July CTP release, Microsoft has added some new features to its latest SQL Server 2008

CTP, including improved T-SQL data types.

SQL Server 2008 CTP installation

First, it is good to note that in order to install the Microsoft SQL Server 2008 July CTP, you cannot have SQL Server 2000 installed on the server or workstation. The installation process is fairly normal compared to a SQL Server 2005 installation. The screens will look fairly similar. To install over a prior CTP build, you must uninstall the prior version. The July CTP will not upgrade a prior CTP version.

New data types in SQL Server 2008

This build of Microsoft SQL Server 2008 includes some new data types which we can explore. These data types are the DATE and TIME data types. Microsoft has also included the HierarchyID system data type.

DATE and TIME data types in T-SQL allow for the storing of just the part of the datetime data type which you need. In prior versions of Microsoft SQL Server, storing or retrieving just the date or the time required storing both the date and time values using the convert function to remove the portion of the value you did not want. The DATE system data type stored only the date, and the TIME system data type stored only the time. Using the DATE and TIME system data types is just like using the datetime system data type.

In the first example, you declare your variables using the new data types and simply set the variables to the value of the getdate() function. Like the other date and time data types which are shown below, the time data type supports up to seven spaces of decimal places of precision which is 100 nano seconds.

DECLARE @Dt as DATE, @Tm as TIME
set @Dt = getdate()
set @Tm = getdate()
select @Dt, @Tm

In the second example, we create a table with a column using the data type of date. We then set a default constraint with the value of getdate(). After we insert a record into the table and query the table, we see the value of the DateAdded column is today's date.

CREATE TABLE dbo.Table_1
 (
 RecordID int NOT NULL,
 DateAdded date NULL,
 DataValue nvarchar(MAX) NULL
 )  ON [PRIMARY]
  TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
 DF_Table_1_DateAdded DEFAULT getdate() FOR DateAdded
GO
INSERT INTO Table_1
(RecordID, DataValue)
VALUES
(1, 'test')
GO
SELECT *
FROM Table_1
GO

In our third example, we create the same table, but without the default constraint. When we add the record we simply set the DateAdded column to the value of the getdate()system function. Using the code from either the second or third example, the output will be the same.

CREATE TABLE dbo.Table_1
 (
 RecordID int NOT NULL,
 DateAdded date NULL,
 DataValue nvarchar(MAX) NULL
 )  ON [PRIMARY]
  TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Table_1
(RecordID, DateAdded, DataValue)
VALUES
(1, getdate(), 'test')
GO
SELECT *
FROM Table_1
GO

Microsoft has added a third date system data type. This third system data type is datetime2. It is like the datetime data type which has been available for many years, however it is much more accurate. While the datetime data type is accurate to the thousandth of a second, it is rounded to either .000, .003 or .007 seconds. The datetime2 data type is accurate to 100 nanoseconds (seven decimal places). When using the datetime2 data type you can specify the fractional seconds precision from zero to seven by using the syntax datetime2(n). Values for the datetime2 data type are set in the same way that values are set for the datetime data type.

DECLARE @Dt as datetime2
set @Dt = getdate()
select @Dt

Here we show controlling the precision of the data type.

DECLARE @Dt as datetime2(4)
set @Dt = getdate()
select @Dt

A fourth date system data type that has been added is the datetimeoffset system data type. This data type includes the time zone offset from GMT in its output.

declare @Dt as datetimeoffset(3)
set @Dt = '2007-07-12 12:17:23.0 +7:00'
select @Dt

The HierarchyID system data type – along with a variety of system methods – is designed to make it easier to store, query, modify and work with hierarchical data. This new data type is optimized for displaying data trees. The HierarchyID data type supports two strategies for index storage. They are called depth-first and breadth-first. In depth-first, rows in a single tree are stored close to each other in the index. An original chart type data with employees and managers is a typical example. In breadth-first, the rows are stored next to each other. In the employee / manager example, the employees who report to the same manager would be stored next to each other.

There are several system functions and methods associated with the HierarchyID data type. Some include GetLevel(), ParentChildOrg(), DescendantLimit() and GetAncestor(). The example below shows a simple example of a parent child relationship between managers and employees.

CREATE TABLE Organization
   (
 NodeLevel hierarchyid,
    EmployeeID int,
    OrgLevel as NodeLevel.GetLevel(), 
    EmployeeName nvarchar(50) NOT NULL
   ) ;
GO
insert into Organization
(NodeLevel, EmployeeID,  EmployeeName)
values
(hierarchyid::GetRoot(),0,  'Bob')
go
Declare @Manager hierarchyid
SELECT @Manager = hierarchyid::GetRoot()
FROM Organization ;

insert into Organization
(NodeLevel, EmployeeId,  EmployeeName)
values
(@Manager.GetDescendant(null, null), 1, 'Joe')
go
Declare @Manager hierarchyid
declare @NodeLevel hierarchyid

select @NodeLevel = NodeLevel 
from Organization 
where EmployeeName = 'Joe'

SELECT @Manager = max(NodeLevel)
FROM Organization
where NodeLevel.GetAncestor(1) = @NodeLevel

insert into Organization
(NodeLevel, EmployeeID,  EmployeeName)
values
(@NodeLevel.GetDescendant(@Manager, null),2,  'Sarah')
go
select NodeLevel.ToString()as NodeLevel_String, *
FROM Organization
go
drop table Organization
go

The output from the select statement will look like this:

NodeLevel_String	NodeLevel	EmployeeID	OrgLevel	EmployeeName
/	0x	0	0	Bob
/1/	0x58	1	1	Joe
/1/1/	0x5AC0	2	2	Sarah

Table variable enhancements

While the table variable enhancements were released in a prior build of SQL Server 2008, they are worth mentioning again. SQL Server 2008 now supports table variables as input parameters for stored procedures. This requires the use of a user-defined data type in both the table variable declaration as well as the stored procedure declaration. Below is a basic example for accomplishing the following:

Create a user-defined data type with a single column.

Develop a procedure with a table variable as an input parameter.

Declare a table variable of the type of the user defined data type.

Loading 10 records into the table variable and pass the table variable to the stored procedure.

create type tt_example AS TABLE
 (spid int)
go
create procedure usp_example
 @spids tt_example READONLY
AS
 SELECT *
 FROM @spids
GO
declare @spids tt_example

insert into @spids
select top 10 spid
from sys.sysprocesses

exec usp_example @spids=@spids

Changes to TEXT, NTEXT and IMAGE data types

The TEXT, NTEXT and IMAGE data types have had some underlying changes. In SQL Server 2008, when data is written to a

More on SQL Server datetime errors and tables: FAQ: Creating and altering SQL Server tables Out-of-range datetime value' error in SQL Server

TEXT, NTEXT or IMAGE data type, if the data is less than 8000 bytes (4000 characters for NTEXT, 8000 for TEXT and IMAGE data types) the data is stored within the row. If the data length is greater than the bounds above, the data is stored in separate data pages in much the same way that data has been stored in Microsoft SQL Server 2005 and prior. When working with data larger than these bounds, a data pointer must be used as it was before.

There are many new and exciting changes in the SQL Server 2008 July CTP, in T-SQL data types and across the board. Even more new and improved features are expected in future releases. Stay tuned as more of the changes are discussed here on SearchSQLServer.com in the coming days and weeks.

All information about SQL Server 2008 presented in this article is based on SQL Server 2008 build 10.0.1049 (July CTP). All information is subject to change in future CTP releases and the final SQL Server 2008 release.

ABOUT THE AUTHOR:

Denny Cherry is a DBA and database architect managing one of the largest SQL Server installations in the world, supporting more than 175 million users. Denny's primary areas of expertise are system architecture, performance tuning, replication and troubleshooting. Copyright 2007 TechTarget

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

All Rights Reserved,Copyright 2005 - 2013, TechTarget