What does the SQL Server 2008 July CTP offer?

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 T...

RELATED CONTENT Database Management and Administration Database encryption in SQL Server 2008: Improvements you finally need Common oversights with SQL Server security audits Top 5 SQL Server DBA tasks that are a waste of time Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Microsoft SQL Server 2008 Database encryption in SQL Server 2008: Improvements you finally need Programming report generation with SQL Server Reporting Services 2008 Q&A: SQL Server 2008 a better fit for consolidation End of life comes for SQL Server 2005 SP2, 2008 Data Transformation Services vs. SSIS: The key differences What's new for installation with SQL Server 2008? SQL Server Reporting Services 2008 offers faster speeds, new variations Microsoft SQL Server 2008 Learning Guide Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 SQL/Transact SQL (T-SQL) Combining result sets from multiple SQL Server queries Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement SQL/Transact SQL (T-SQL) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) Collaboration Data Objects  (SearchSQLServer.com) commit  (SearchSQLServer.com) container  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

IME 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.

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.

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.

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.

Here we show controlling the precision of the data type.

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.

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.

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:

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

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip 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.