This is Part 4 of Tsql Features in Sql 2008. In this article we discussed about Table value parameters, Large user defined Types, Constructor support, The hieararcy id data type, DDL trigger enhancements and User defined Aggregate functions.
Before reading this we suggest to read
Table valued parameters
In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data needed to be sent to SQL Server then developers either had to send one row at a time, or come up with other workarounds to meet the requirements. In SQL Server 2008, you simply pass a table-valued parameter to the stored procedure.
Example
Use the scripts
CREATE TABLE ProductCategory
(
CategoryID [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
CategoryName VARCHAR(100) NULL
)
CREATE TABLE ProductDetails
(
ProductID [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
CategoryID INT,
Product VARCHAR(100) NULL,
Price FLOAT NULL
)
INSERT INTO ProductDetails(CategoryID, Product, Price) VALUES (1,'P1',120)
INSERT INTO ProductDetails(CategoryID, Product, Price) VALUES (1,'P2',100)
INSERT INTO ProductDetails(CategoryID, Product, Price) VALUES (2,'P3',125)
When you run this:
SELECT * FROM ProductCategory
SELECT * FROM ProductDetails
Result
Example
We can create a table type using the following script,
/* Create a table type. */
CREATE TYPE ProductCategoryType AS TABLE
( CategoryID INT
, CategoryName VARCHAR(100));
GO
Now let us create a procedure to receive data using a table-valued parameter. Note that there is a READONLY qualifier that comes after the table valued parameter. This is required because a copy of the user-defined table variable is not passed to the procedure. To be more efficient, a pointer is passed to the procedure. This restricts any changes to the table variable inside the procedure otherwise the changes will affect the original data.
CREATE PROC dbo.usp_getCategory
(@T AS ProductCategoryType READONLY)
AS
SELECT Category.CategoryID,Category.CategoryName, Product.Product, Product.Price
FROM ProductDetails AS Product
JOIN @T AS Category
ON Category.CategoryID = Product.CategoryID;
Run the Script
DECLARE @Category AS ProductCategoryType;
INSERT INTO @Category(CategoryID, CategoryName)
VALUES (1,'Category1'),(2,'Category2');
EXEC dbo.usp_getCategory @T = @Category;
Result
Large User Defined Types:
User-defined types (UDTs) introduced in SQL Server 2005 allow a developer to extend the server's scalar type system by storing common language runtime (CLR) objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, unlike the traditional alias data types, which consist of a single SQL Server system data type.
The size of a UDT is defined by the attribute SqlUserDefinedTypeAttribute.MaxByteSize as part of the type’s definition. If this attribute is set to -1, the serialized UDT can reach the same size as other large object types (currently 2 GB); otherwise, the UDT cannot exceed the size specified in the MaxByteSized property. The client APIs such as OLE DB and ODBC in the SQL Server Native Client and ADO.NET were enhanced to support large UDTs. The Down-level clients (SQL Server 2005 and 2000 clients) convert a large UDT to VARBINARY(MAX) and IMAGE, respectively.
Table Value Constructor Support
SQL Server 2008 introduces support for table value constructors through the VALUES clause. Row Constructors allow you to insert mulitiple records with a single INSERT statement. However, if you have values as static strings or variables, with the previous versions of SQL Server, you always need multiple insert statements.
Example
CREATE TABLE #Books(BookID INT, Title VARCHAR(250), Author VARCHAR(100))
INSERT INTO #Books(BookID, Title, Author)
VALUES
(1, 'Sunset', 'Hunter'),
(2, 'Winter', 'Maurer, Tracy Nelson'),
(3, 'Fortune Cookie', 'Ure, Jean');
SELECT * FROM #Books
Result
Note that even though no explicit transaction is defined here, this INSERT statement is considered as an atomic operation. So if any row fails to enter the table, the entire INSERT operation fails.
A table value constructor can be used to define table expressions such as key derived tables and Common Table Expressions, and can be used where table expressions are allowed (such as in the FROM clause of a SELECT statement or as the source table in a MERGE statement). The following example demonstrates how to use the VALUES clause to define a derived table in the context of an outer SELECT statement:
SELECT * FROM (VALUES
(1, 'Sunset', 'Hunter'),
(2, 'Winter', 'Maurer, Tracy Nelson'),
(3, 'Fortune Cookie', 'Ure, Jean')
) AS C(BookID, Title, Author);
The HIERARCHYID data type
The new HIERARCHYID data type in SQL Server 2008 is system-provided that can be useful for storing and manipulating hierarchies. We may use the HIERARCHYID as a data type to create tables with a hierarchical structure, or to refer the hierarchical structure of data in another location. Also the HIERARCHYID can be used in functions to query and perform work with hierarchical data by using Transact-SQL.
The HIERARCHYID type is internally stored as a VARBINARY value that represents the position of the current node in the hierarchy (both in terms of parent-child position and position among siblings). You can perform manipulations on the HIERARCHYID type by using either Transact-SQL or client APIs to invoke methods exposed by this data type.
Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships are where one item of data is the parent of another item. Hierarchical data is common in databases. The HIERARCHYID type makes it easier to store and query hierarchical data. HIERARCHYID is optimized for representing trees, which are the most common type of hierarchical data.
Problems with the HIERARCHYID Data type are
- Finding next available HIERARCHYID level is difficult.
- We cannot have two root nodes in a table.
DDL trigger enhancements
In SQL Server 2008, the type of events on which you can create DDL triggers is now enhanced to include stored procedures that perform DDL-like operations. This gives you more complete coverage of DDL events that you can capture with triggers.
Many stored procedures perform DDL-like operations. Before SQL Server 2008, you could not capture their invocation with a trigger. Now you can capture many new events that fire as a result of calls to such procedures.
Using the trigger we can track the DDL operations like Create, Alter and Drop Table in your database.
Example
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE DDLEvents (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100));
GO
CREATE TRIGGER TestTrigger ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
INSERT DDLEvents (PostTime, DB_User, Event)
VALUES (GETDATE(),CONVERT(nvarchar(100), CURRENT_USER), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(200)')) ;
GO
CREATE TABLE Table1 (ID INT,Name VARCHAR(100))
GO
SELECT * FROM DDLEvents
GO
ALTER TABLE Table1 ADD [Address] VARCHAR(250)
ALTER TABLE Table1 ALTER COLUMN Name VARCHAR(200)
GO
DROP TABLE Table1;
GO
SELECT * FROM DDLEvents
GO
Result
Using the above scripts we can track the DDL operations like CREATE, ALTER and DROP.
CLR User-Defined Aggregate Functions
SQL Server 2008 introduces support for large UDAs (User-Defined Aggregate) and exceeds the maximum size in bytes of a persisted value to 8,000 bytes and reach up to 2 GB.
To allow a UDA to exceed 8,000 bytes, specify “-1” in the “MaxByteSize” attribute; otherwise specify a value that is smaller than or equal to 8,000 in which, the size in bytes cannot exceed the specified value.
The UDAs are enhanced and support multiple inputs. The UDA can accept two input parameters: the column holding the string to concatenate and the character to use as a separator. The UDA can return an output larger than 8,000 bytes.
Additional rules apply
- The current user should have “EXECUTE” permission on the user-defined aggregate.
- User-defined aggregates must be invoked using a two-part name in the form of schema_name.udagg_name.
- The argument type of the user-defined aggregate must match or be implicitly convertible to the input_type of the aggregate, as defined in the CREATE AGGREGATE statement.
- The return type of the user-defined aggregate must match the return type in the CREATE AGGREGATE statement.
The ORDER option for CLR table-valued functions
CLR table-valued functions represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table.
The “IEnumerable” object returned by the managed function is directly called by the execution plan of the query, which calls the table-valued function, and the results are consumed in an incremental manner.
This streaming model ensures that, results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It is also a better alternative for very large numbers of rows returned, that don’t have to be materialized in memory as a whole.
For example, a managed “table-valued” function could be used to parse a text file and return each line as a row.
CLR TVFs now support a new ORDER clause as part of the CREATE FUNCTION DDL statement. You can use this clause to specify column names in the output table, when you know the rows will always be returned in the same order.
This can help the optimizer, when you query the table function and rely on those columns for ordering purposes (such as when they are used in ORDER BY, GROUP BY, DISTINCT, and INSERT).