Tip 4: The New HierarchyID
SQL Server 2005 gave developers new language capabilities to perform recursive queries against data stored hierarchically. SQL Server 2008 stores hierarchical data better, and provides new functions to query data without needing to use common table expressions.
SQL Server 2008 introduces a new data type called the HierarchyID data type. As with many new concepts, a simple example can be worth a thousand words of explanation, so here's a simple example of a product hierarchy structure (
family,
brand,
category,
subcategory, etc.) stored in a single table called
ProductTree. First, the syntax to create the
ProductTree table is as follows:
CREATE TABLE ProductTree
(ProductTreePK int IDENTITY,
Description varchar(1000),
ProductLevelPK int, HierID hierarchyid)
Next, here's a foreign table of descriptions for each
ProductLevel:
CREATE TABLE ProductLevels
(ProductLevelPK int IDENTITY,
Description varchar(50))
-- PK of 1 (All)
INSERT INTO ProductLevels VALUES ('All Products')
-- PK of 2 (Family)
INSERT INTO ProductLevels VALUES ('Family')
-- PK of 3 (Brand)
INSERT INTO ProductLevels VALUES ('Brand')
-- PK of 4 (Category)
INSERT INTO ProductLevels VALUES ('Category')
-- PK of 5 (SubCategory)
INSERT INTO ProductLevels VALUES ('SubCategory')
-- PK of 6 (SKU)
INSERT INTO ProductLevels VALUES ('SKU')
The HierarchyID data type contains methods to get descendants and ancestors, as well as a root definition. So you can use the following functions as part of building an "API" for inserting into/retrieving from a hierarchical data type.
hierProductParentID.GetDescendant(@LastChild,NULL)
hierarchyid::GetRoot()
GetAncestor(1)
With an API for inserting (see
Listing 1), you can make creating data very easy.
exec InsertProductTree null,'All Products', 1
exec InsertProductTree 1 ,'Family A', 2
exec InsertProductTree 1 ,'Family B', 2
exec InsertProductTree 1 ,'Family C', 2
declare @TempParent int =
(SELECT ProductTreePK FROM ProductTree WHERE
 | |
| Figure 1. Hierarchical Data: The figure shows some stored hierarchical data and query that uses GetAncestor to determine each parent. |
Description = 'Family A')
exec InsertProductTree
@TempParent ,'Family A - Brand AA', 3
exec InsertProductTree
@TempParent ,'Family A - Brand AAA', 3
Figure 1 shows the results.
Tip 5: The New Table Data Type
Despite reactions from database "purists", many database/application developers have often asked for the ability to pass a table definition (or an ADO.NET DataTable) as a pure parameter. While Table-valued functions allow developers to write UDFs to return table variables, most developers would have to use XML or other tricks to actually pass a table as a parameter.
Fortunately, SQL Server 2008 contains a new Table type that finally gives developers what they've been asking for. For each instance where you wish to pass a table, you'll need to establish an actual Table type in the database. For example, if you have a table of sales information that you want to pass as a parameter (say, to do a mass update of order information), you can define a Table type like so:
-- Step 1: Create a new Table type
-- in the database
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
Next, let's create an actual physical table that we'll use as the destination table (actual sales orders).
-- Step 2: Create an actual sales history table
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int]
IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
Our objective is to create a stored procedure that will take a temporary table variable (of type SalesHistoryTableType) and insert it into the permanent table. The code below contains a stored procedure that performs this task. Note that the Table type parameter is
READONLY—you cannot
modify the contents of a Table type inside the procedure that receives it as a parameter.
-- Step 3: Create a stored procedure that will
-- read a variable of type SalesHistoryTableType
-- and insert certain rows into the main Table
CREATE PROCEDURE usp_InsertProductOne
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
SELECT Product, SaleDate, SalePrice
FROM @TableVariable
WHERE Product = 'Product 1'
END
Finally, you can write code to create a variable of type SalesHistoryTableType, populate it with data, and then pass it to the stored procedure.
-- Step 4: Create a variable of type
-- SalesHistoryTableType, and add test data
DECLARE @tTB AS SalesHistoryTableType
DECLARE @i SMALLINT = 1
WHILE (@i <=1000)
BEGIN
INSERT INTO @Ttb (Product, SaleDate, SalePrice)
VALUES ('Product 1',
DATEADD(mm, @i, '3/11/2007'), @i),
('Product 2',
DATEADD(mm, @i, '3/11/2007'), @i),
('Product 3',
DATEADD(mm, @i, '3/11/2007'), @i);
SET @i += 1
END
-- Step 5: Call the stored procedure
EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable
You may be wondering, "Can I do this from a .NET application?" The answer is YES! The SqlClient data provider for SQL Server 2008 contains a new SqlDbType called "Structured" that allows you to pass a DataTable (or a DataReader, or any IEnumerable).
As an example, you can create a DataTable in ADO.NET:
DataTable dt = new DataTable();
dt.Columns.Add("Product", typeof(String));
dt.Columns.Add("SaleDate", typeof(DateTime));
dt.Columns.Add("SalePrice", typeof(Decimal));
dt.Rows.Add("Product 1", DateTime.Today,
10000.00);
Second, you can create a SQL connection:
SqlConnectionStringBuilder osb =
new SqlConnectionStringBuilder();
osb.DataSource = "localhost";
osb.InitialCatalog = "AdventureWorks";
osb.IntegratedSecurity = true;
SqlConnection oConn =
new SqlConnection(osb.ConnectionString);
oConn.Open();
And finally, you can pass the DataTable as a parameter, using the new SqlDbType:
SqlCommand oCmd =
new SqlCommand("usp_InsertProductOne", oConn);
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParm =
oCmd.Parameters.AddWithValue
("@TableVariable", dt);
oParm.SqlDbType = SqlDbType.Structured;
oParm.TypeName = "dbo.SalesHistoryTableType";
oCmd.ExecuteNonQuery();
oConn.Close();
Finally, another application for the Table type is handling a variable number of user selections. For example, many developers have faced situations where they need to query against invoices, where the user might select one account, or two, or ten, etc. Often, developers will pass a comma-separated list of integer keys (or an XML string) as a parameter to a stored procedure. With the new Table type, you can now pass an ADO.NET DataTable as a parameter. This represents the simplest and most efficient means yet for this type of process.
Tip 6: Simplified Date and Time Data Types
Of all the new enhancements in SQL Server 2008, the one that often gets the most applause is the fact that you can now create separate Date and Time data types.
-- Ability to specify a Date as a type
DECLARE @tDates TABLE (WorkDate DATE)
INSERT INTO @tdates
VALUES ('1-1-2008'),
('1-1-2009'),
('12-31-2007'),
('3-1-2008'),
('11-1-2009'),
('12-1-1620');
SELECT DATEPART(yy,workDate),
WorkDate FROM @tDates ORDER BY WorkDate
Previous Page
