What’s New in SQL 2008 – Part 3 – Table Type

The new Table type allows you to save a table definition in the database which can be reused later to define table variables, stored procedure parameters and functions. To create a Type, we use the CREATE TYPE clause, for example:

CREATE TYPE dbo.ProductList as TABLE (

    ProductName varchar(200) NULL,

    ProductCategory varchar(200) NOT NULL)

Important Notes:

• The metadata is visible using sys.table_types
  

• Variables/parameters cannot be set to NULL – if no table is supplied, an empty table is generated
  

• Not affected by transaction rollbacks
  

• Do not cause recompilations
  

Examples

Create Table-Type Variables	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES ('Grey Slate Paver 10x10','Paver'), ('Blue Slate Paver 10x10','Paver'), ('Heritage Blue 12x6','Brick')
Creating a Stored Procedure with a Table-typed Parameter	CREATE PROC dbo.uspGetOrdersforProduct ( @t as dbo.ProductList READONLY, @StartDate as Date, @EndDate as Date ) AS SELECT o.OrderId, o.OrderDate, o.CustomerId, o.ProductId, o.Amount, o.Qty FROM tOrder as o INNER JOIN tProduct t on t.ProductId = o.ProductId INNER JOIN @t as f on f.ProductCategory = t.ProductCategory and (t.ProductName is NULL or t.ProductName = f.ProductName) WHERE o.OrderDate between @StartDate and @EndDate
Passing Table-parameters into Stored Procedures	DECLARE @t as dbo.ProductList; INSERT INTO @t VALUES (NULL,'Paver'), (NULL,'Brick') EXEC dbo.uspGetOrdersforProduct @t

What’s New in SQL 2008 – Part 2 – Data Types

Date and Time

Data Type	Size (bytes)	Example	Notes	Client Providers
DATE	3	'2009-10-01'		OLEDB: DBTYPE_DBDATE ADO.Net: DateTime ODBC: SQL_DATE
TIME	3-5	'13:12:00.1234567'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIME ADO.Net: TimeSpan ODBC: SQL_TIME
DATETIME2	6-8	'2009-10-01 13:12:00.1234567'	Accurate to 100 nanoseconds Can specify precision ie decimal places eg DATETIME(3)	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP
DATETIMEOFFSET	8-10	'2009-10-01 13:12:00.1234567+02:00'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP

There are a number of new functions which can also be used with these new datatypes including:

• SYSDATETIME (returns DATETIME2), SYSUTCDATETIME, SYSDATETIMEOFFSET (includes time zone in return value) – Current Date and Time
  
• SWITCHOFFSET – to change the timezone for a DATETIMEOFFSET value
  
• TODATETIMEOFFSET – convert non-timezone-aware values to time-zone-aware
  

Other

Data Type	Size (bytes)	Useage	Returns	Notes
HIERARCHYID	VARBINARY eg 5 bytes for 100k records at 6-Levels	DECLARE @h hierarchyid = hierarchyid::GetRoot() DECLARE @c hierarchyid SET @c = @h.GetDescendant(NULL, NULL) SELECT @c.ToString()	'/1/'	Represents position in a hierarchy Actually a CLR function with several methods eg GetDescendant/IsDescendantOf GetLevel/GetRoot GetReParentedValue ToString/Read/Write (CLR)

What's New in SQL 2008 - Part 1 - Functionality

Function	Usage Example	Notes
Declare & Initialise	DECLARE @iValue as integer = 100;
Compound Assignments	SET @iValue -= 50 UPDATE dimCustomer SET Balance += 200	+= -= *= /= %=
Multi-Row Inserts Using VALUES	INSERT INTO dimProduct (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate) VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00'), ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00'), ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00')	The insert is executed as a single transaction. Interestingly, it can also be used like this: SELECT * FROM (VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00') ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00') ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00') ) as p (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate)
MERGE	MERGE INTO dbo.dimProduct as d USING dbo.dimProductStage as s ON d.ProductKey = s.ProductId WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.ProductCategory = s.ProductCategory WHEN NOT MATCHED THEN INSERT (ProductName,ProductCategory) VALUES (s.ProductName, s.ProductCategory) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.ProductId as DeletedId, inserted.ProductId as InsertedId;
Grouping Sets	SELECT Year(OrderDate) as OrderYear, SalespersonId, OrderType, count(OrderId) as OrderCount, sum(Amount) as OrderAmount FROM factOrders GROUP BY GROUPING SETS ( (OrderType, Year(OrderDate)), (SalespersonId,OrderType, Year(OrderDate)), (SalespersonId, Year(OrderDate)), (Year(OrderDate)), ());	() empty brackets represents an 'ALL' grouping
Filtered Indexes	CREATE NONCLUSTERED INDEX idxOrders_Product ON factOrders(OrderId,ProductId) WHERE CreditFlag IS NULL
Change Data Capture	** to be discussed in further detail in an upcoming post **	Tracks data changes into a table. Enabled using sys.sp_cdc_enable_db or sys.sp_cdc_enable_table Disabled using sys.sp_cdc_disable_db or sys.sp_cdc_disable_table Uses SQL Agent

SharePoint Magazine - Part 2 available now

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/sell-sell-sell-why-build-a-dashboard-anyway-part-2-of-6

PerformancePoint Planning: Error When Deleting Members of a Member Set

Unfortunately I discovered a problem in PPS Planning today which caused me a few headaches... thought I would pass it on to you!! The issue occurs when editing/creating a dimension member set... a save will fail if you do an outdent and a delete member immediately afterwards. Let me demonstrate.

Let's presume you have a structure something like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Salaries
--- --- --- --- Base Salary
--- --- --- --- Bonuses
--- --- --- --- Allowances
--- --- --- Tax Expense

But you want to remove the Salaries level and end up with a structure that looks like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Base Salary
--- --- --- Bonuses
--- --- --- Allowances
--- --- Tax Expense

If you do this:

1. Outdent the 3 leaf level members
2. Remove the Salary level member
3. Save

You will get an error when you save similar to the following:

In order for it to work correctly, you MUST (in this order):

1. Outdent the 3 leaf level members
2. Save
3. Remove the Salary level member
4. Save
   

I have logged this problem on Microsoft Connect. Please feel free to vote! https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=375153&SiteID=181