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:
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 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
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