Introduction

This article is the fourth in a series. There are links at the bottom of the article to the other parts.

In my last article, I discussed about the SQL Server 2008 T-SQL Enhancements, for example, IntelliSence, Grouping Set, FORCESEEK table hint etc. In this article and coming few next articles I will be discussing about the new data types introduced in SQL Server 2008.

SQL Server 2008 has introduced several new data types to improve the developer productivity and overall performance of SQL Server. For example, User-Defined Table type and Table-Valued parameter allow user to pass a result-set to a procedure and save multiple round-trips to the server whereas new Date and Time data types are efficient enough to save memory requirements by requiring much less memory in case if you have to save either date or time component only and making easier working with them. In this article, I will be discussing about above two data types in details, its usage scope and applications.

User-Defined Table Type and Table-Value Parameter (TVP)

With SQL Server 2008, you can create a user-defined table type which represents the definition of a table structure. To ensure that the data in a user-defined table type meets specific requirements, you can also create unique constraints and primary keys on this type. Further, to send multiple rows of data to a stored procedure or a function without creating a temporary table or many parameters, you can use a user-defined table type to declare table-valued parameters for stored procedures or functions.

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits; for example it does not acquire locks for the initial population of data from a client, it does not cause a statement to recompile, reduce round trips to the server, enable the client to specify sort order and unique keys etc.

When table variables are passed as parameters, the table is materialized in the tempdb system database rather than passing the entire data set in memory; this makes handling of large amounts of data rather efficient. All server side passing of table variable parameters are passed by reference, using the reference as a pointer to the table in the tempdb to avoid making a copy of the input data.

Limitations

· User-defined table types have the several restrictions imposed on it for example it cannot be used as a column in a table, its definition cannot be modified after it is created, A DEFAULT value cannot be specified in the definition of a user-defined table type etc, for complete list of restrictions please refer to BOL or MSDN link provided at the end of this section.

· SQL Server does not maintain statistics on columns of table-valued parameters.

· Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. If the requirement is to modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable instead. Also, we cannot use table variables as OUTPUT parameters -- we can only use table variables as input parameters.

Example

In this example we will see how we can create a user-defined table type, create a variable of this type, insert records to it and pass it to a stored procedure as table-valued parameter.

Let's first create a table for customers and add few records to it.

             --Create a table to hold information for customers
 CREATE TABLE [Customers]
 (
 [ID] [int] NOT NULL PRIMARYKEY IDENTITY,
 [FirstName] [varchar](100)NOT NULL,
 [LastName] [varchar](100)NOT NULL,
 [Email] [varchar](200) NOTNULL
 )
 GO
 --Insert few records in customers table
 INSERT INTO [Customers] (FirstName, LastName, Email)
 VALUES('AAA','XYZ', 'aaa@test.com')
 INSERT INTO [Customers] (FirstName, LastName, Email)
 VALUES('BBB','XYZ', 'bbb@test.com')
 INSERT INTO [Customers] (FirstName, LastName, Email)
 VALUES('CCC','XYZ', 'bbb@test.com')
 GO
         

Now create a user-defined table type, once created you can view details of it using two system catalog views as shown below:

 --Create a user defined table type to hold customers records
 CREATE TYPE [CustomersUDT] AS TABLE
 (
 [FirstName] [varchar](100)NOT NULL,
 [LastName] [varchar](100)NOT NULL,
 [Email] [varchar](200) NOTNULL
 )
 GO
 --You can use catalog views as given below to see the created type
 SELECT name, system_type_id, user_type_id, is_assembly_type, is_table_type FROM SYS.TYPES WHERE is_table_type = 1
 SELECT name, system_type_id, user_type_id, is_assembly_type, is_table_type FROM SYS.TABLE_TYPES
 GO

You can also use SSMS(SQL Server Management Studio) to view created user-defined tables in a database as shown below, navigate to User-Defined Table Types node under Types node in object explorer:

Now create a stored procedure to pass the variable of user-defined table type as a table-valued parameter as shown below, please note the scope rule for a variable applies here as well with variable of type user-defined table, i.e. it goes out of scope automatically when the batch is completed.:

 --Create a procedure to receive data for the table-valued parameter.
 --Notice the READONLY, it says you cann't perform INSERT, UPDATE and DELETE inside the routine
 CREATE PROCEDURE AddCustomers(@CustomersTVP CustomersUDT READONLY)
 AS
 BEGIN
       INSERT INTO Customers
       SELECT *FROM @CustomersTVP
 END
 GO
 --Declare a variable of table type created above to hold customers records
 DECLARE @CustomersCollection CustomersUDT
 --Insert few records in the variable of table type declared above
 INSERT INTO @CustomersCollection (FirstName, LastName, Email)
 VALUES('DDD','XYZ', 'ddd@test.com')
 INSERT INTO @CustomersCollection (FirstName, LastName, Email)
 VALUES('EEE','XYZ', 'eee@test.com')
 INSERT INTO @CustomersCollection (FirstName, LastName, Email)
 VALUES('FFF','XYZ', 'fff@test.com')
 --Pass the table type variable data to a stored procedure as a table value parameter
 EXEC AddCustomers @CustomersCollection
 GO
             

Passing Table-valued parameter by .Net Application

First you need to have a .Net Framework 3.5 installed, which provides a new SQL DB Type called “Structure” inside the System.Data.SQLClient namespace.

Make sure that the DataTable that you create in .Net application matches the user-defined table type’s schema, in other words the name of columns, count of columns and data types of columns must be same. Though in certain cases if the data type is not same but still if it is compatible with each other then in that case it allows the run.

 //Create a local data table to hold customer records
 DataTable dtCustomers = new DataTable("Customers");
 DataColumn dcFirstName = new DataColumn("FirstName", typeof(string));
 DataColumn dcLastName = new DataColumn("LastName", typeof(string));
 DataColumn dcEmail = new DataColumn("Email", typeof(string));
 dtCustomers.Columns.Add(dcFirstName);
 dtCustomers.Columns.Add(dcLastName);
 dtCustomers.Columns.Add(dcEmail);
 //Add customer 1
 DataRow drCustomer = dtCustomers.NewRow();
 drCustomer["FirstName"] = "AAA";
 drCustomer["LastName"] = "XYZ";
 drCustomer["Email"] = "aaa@test.com";
 dtCustomers.Rows.Add(drCustomer);
 //Add customer 2
 drCustomer = dtCustomers.NewRow();
 drCustomer["FirstName"] = "BBB";
 drCustomer["LastName"] = "XYZ";
 drCustomer["Email"] = "bbb@test.com";
 dtCustomers.Rows.Add(drCustomer);
 //Add customer 3
 drCustomer = dtCustomers.NewRow();
 drCustomer["FirstName"] = "CCC";
 drCustomer["LastName"] = "XYZ";
 drCustomer["Email"] = "ccc@test.com";
 dtCustomers.Rows.Add(drCustomer);
 //Create Connection object to connect to server/database
 SqlConnection conn = new SqlConnection("Data Source=ARALI-LAPTOP;Initial Catalog=tempdb;Integrated Security=true");
 conn.Open();
 //Create a command object that calls the stored procedure
 SqlCommand cmdCustomer = new SqlCommand("AddCustomers", conn);
 cmdCustomer.CommandType = CommandType.StoredProcedure;
 //Create a parameter using the new SQL DB type viz. Structured to pass as table value parameter
 SqlParameter paramCustomer = cmdCustomer.Parameters.Add("@CustomersTVP", SqlDbType.Structured);
 paramCustomer.Value = dtCustomers;
 //Execute the query
 cmdCustomer.ExecuteNonQuery();

For more information, see these articles:

User-Defined Table Types - http://technet.microsoft.com/en-us/library/bb522526.aspx

Table-Valued Parameters - http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspx

New Date and Time Data Types

SQL Server 2008 introduces four new DATETIME data types as follows:

· DATE: - In previous version of SQL Server, we had to use the DATETIME or SMALLDATETIME data types even though if we had to store Date only. These data types store time component as part of it, which will appear as 12:00 AM. Then we needed to format our output to display only the date component. SQL Server 2008 introduces DATE data type, it is useful to store the dates without the time part, it supports the Gregorian calendar and uses 3 bytes to store the date. The range for the DATE data type is from 01-01-0001 through 12-31-9999 unlike DATETIME data type which has a range of 01-01-1753 to 31-12-9999 and SMALLDATETIME data type which has a range of 01-01-1900 to 06-06-2079.

 CREATE TABLE Employee
 (
 EmpId INT IDENTITY,
 Name VARCHAR(100),
 DOB DATE, -- Date of birth column will store only date and no time part
 DOJ DATE DEFAULT GETDATE()--Date of joining will be default, again only date, no time part
 )
 GO
 --create a variable of type date and assign a date
 DECLARE @DOB DATE = CONVERT(DATE,'12/05/1982')
 INSERT INTO Employee(Name, DOB)
 VALUES ('Rocky', @DOB)
 GO
 SELECT * FROM Employee
 GO
             

· TIME: - Similar to the Date data type, there is a TIME data type in cases where if we need to store only the time. The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999. The Time data type is accurate up to 100 nanoseconds, though it can be configured to be less accurate — and take up less space (anywhere from 3 to 5 bytes). The data type is not Time Zone aware and it is based on a 24 hour clock, it also is not aware of Day Light Saving.

 CREATE TABLE ShiftMaster
 (
 ShiftName VARCHAR(100),
 StartTime TIME, -- StartTime without storing date component
 EndTime TIME -- EndTime without storing date component
 )
 GO
 --Declaring variables of TIME data type to store time
 DECLARE @StartTime TIME = '00:01'
 DECLARE @EndTime TIME = '08:00'
 INSERT INTO ShiftMaster(ShiftName, StartTime, EndTime)
 VALUES
       ('Shift A', @StartTime, @EndTime),
       ('Shift B','08:01', '04:00'),
       ('Shift C','04:01', '00:00')
 GO
 SELECT * FROM ShiftMaster
 GO
            

· DATETIMEOFFSET: - DATETIMEOFFSET is another new data type that is included in SQL Server 2008. We can store high precision date/ time with the DATETIMEOFFSET. We can't store the Time Zone like Eastern Time, Central Time etc. in the data type but can store the offset -5:00 for EST and -6:00 CST and so on. The data type is not Day light saving aware. The date range is between 01-01-0001 and 12-31-9999 and the Time Range is between 00:00:00 and 23:59:59.9999999. The offset range is between -14:00 through +14:00. The precision of the data type can be set manually and it follows the Gregorian calendar. In nutshell, the new data type DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

 CREATE TABLE OrderMaster
 (
 OrderID INT IDENTITY,
 CustomerID INT,
 OrderDateTIme DATETIMEOFFSET--DATETIMEOFFSET data type to store date and time along with the time-zone offset
 )
 GO
 --Declaring a variable of DATETIMEOFFSET data type to store date and time along with the time-zone offset
 DECLARE @OrderDateTIme DATETIMEOFFSET= '2009-01-16 00:24 +05:30'
 INSERT INTO OrderMaster(CustomerID, OrderDateTIme)
 VALUES
       (1, @OrderDateTIme),
       (1,'2009-01-18 00:24 +05:30'),
       (2,'2009-01-18 00:24 -04:00')
 GO
 SELECT * FROM OrderMaster
 GO
            

· DATETIME2: - The reason of introducing DATETIME2 data type is, DATETIME is not SQL standard compliant, and DATETIME is not completely compatible with the .Net DATETIME data type. DATETIME2 is basically a combination of the new DATE and TIME data types and uses 6-8 bytes. Actual size is determined by the amount of precision being stored for example DATETIME2(0) will take 6 bytes, DATETIME2 (3) will take 7 bytes whereas DATETIME2(7) which is by default will take 8 bytes (the number inside the bracket denotes precision and can be anything between 0-7).

The time portion in this is configurable again, and there are a lot of string formats that the DATETIME2 will accept. This data type also follows the Gregorian calendar and is not Day Light Saving Aware. The Time Zone can't be specified in this data type. You have the option of specifying the number of fractions that you may need. The maximum fraction you can specify is 7 while the minimum fraction is 0.

Apart from the changes in data type, SQL Server 2008 introduces five new functions: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET. The SYSDATETIME function returns the current system timestamp without the time zone, with an accuracy of 10 milliseconds. The SYSDATETIMEOFFSET function is the same as the SYSDATETIME function, however it includes the time zone. SYSUTCDATETIME returns the Universal Coordinated Time (same as Greenwich Mean Time) date and time within an accuracy of 10 milliseconds. This is derived from the current local time and the time zone setting of the server where SQL Server is running. Both SYSDATETIME and SYSUTCDATETIME return DATETIME2 data type, whereas SYSDATETIMEOFFSET returns the DATETIMEOFFSET data type. SWITCHOFFSET functions return a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset. The TODATETIMEOFFSET function converts a local date or time value and a specified time zone offset to a DATETIMEOFFSET value.

 SELECT
       GETDATE()AS [GETDATE],
       --current system timestamp without the time zone,
       --with an accuracy of 10 milliseconds
       SYSDATETIME() AS [SYSDATETIME],
       --returns UTC(Universal Coordinated Time) date and
       --time within an accuracy of 10 milliseconds.
       SYSUTCDATETIME() AS [SYSUTCDATETIME]
            

 SELECT
       --returns current system timestamp with the time
       --zone, with an accuracy of 10 milliseconds
       SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET],
       --return a DATETIMEOFFSET value that is changed from
       --the stored time zone offset to a specified new time
       --zone offset and preserves the original value.
       SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:00')AS [SWITCHOFFSET],
       --converts a local date or time value and a specified
       --time zone offset to a DATETIMEOFFSET value.
       TODATETIMEOFFSET(GETDATE(),'+05:30') AS [TODATETIMEOFFSET]

New Date and Time Data Types - http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx

Conclusion

The User-Defined Table type and Table-Valued parameters have together opened up a new window to allow user to pass a result-set to a procedure and save multiple round-trips to the server which were required in previous version of SQL Server.

The new Date and Time data types are efficient enough to save memory requirements by requiring much less memory in case if you have to save either date or time only and making easier working with them, it also allows more precision now and working with DATETIMEOFFEST.

In the next article I will discuss about the new HIERARCHYID data type which allows you to save a hierarchical structure, something like organizational hierarchy, in the database, makes easier working with these kinds of data and then will talk of Large User Defined Type which allows users to expand the size of defined data types by eliminating the 8KB limit.

This article is the fourth in a series. The other articles in the series are:

• SQL Server 2008 T-SQL Enhancements Part - I (Intellisense)
• SQL Server 2008 T-SQL Enhancements Part - II (UDTs and TVPs)
• SQL Server 2008 T-SQL Enhancements Part - III (HierarchyID and Large UDTs)
• SQL Server 2008 T-SQL Enhancements Part - IV (Filestream)
• SQL Server 2008 T-SQL Enhancements Part - V (Spatial)