Return to Microsoft announces SQL Server 2008 Microsoft announces SQL Server 2008 Written by Bill Graziano on 04 June 2007 Microsoft is making a series of announcements at Tech*Ed related to SQL Server 2008 -- previously codenamed "Katmai". I've got some details on some of the new features including the MERGE statement, Table Valued Parameters, Change Data Capture and the Declarative Management Framework. There should also be a download of the June CTP available inside Connect. MERGE Statement One of the things I'm most interested in is the MERGE statement. I'd always referred to this jokingly as an UPSERT statement however the 2003 ANSI-SQL standard defines the syntax using the MERGE keyword. MERGE gives you a single statement to do inserts or updates based on whether a record exists or not. The syntax should look something like this: MERGE INTO FactTable F USING TransactionTable T ON T.OrderID = F.OrderID WHEN MATCHED THEN UPDATE SET F.Quantity = T.Quantity WHEN NOT MATCHED THEN INSERT (OrderID, Quantity) VALUES (T.OrderID, T.Quantity) This will be very useful to populate tables in data warehouses. And handy for DBA's that are too lazy to write separate stored procedures for updates and inserts ;) Change Data Capture Microsoft describes this as: Change Data Capture (CDC) is a generic component that will track database changes asynchronously and expose the changes through a relational interface which can be consumed easily. Through this interface, consumers can very easily track changes based on their specific requirements and consume the change data using T-SQL or other data access methods. This is certainly an interesting feature. A way to run a trace of data changes without actually running a trace. My guess is they'll use Service Broker under the hood to expose the data. I especially like the ability to manipulate it in T-SQL. This should solve a great many problems with audit issues. If remote clients can connect in and capture this information that provides a way to audit outside SQL Server. Declarative Management Framework This is one that I know the least about. I didn't get to sit in the session at the MVP Summit. They describe it as: Declarative Management Framework (DMF) is a new policy-based management framework for the SQL Server Database Engine that delivers the following benefits: Ensure compliance with policies for system configuration Prevent/monitor changes to the system by authoring policies for the desired configuration Reduce total cost of ownership by simplifying administration tasks Basically you define a policy and then you can "deploy" that policy to other servers. Table Valued Parameters Oh how I've pined for this feature! I just want a simple way to pass a set of rows to a stored procedure. I can't tell you how many stupid little hacks I've written to do something like this. Data Warehouse Enhancements Microsoft is also making enhancements to the query processor that will benefit data warehouses. Specifically, the optimizer will recognize star joins and will improve the query response time. They are also making improvements to Analysis Server. According to Microsoft these include: Enhance UI for creating and editing dimensions to guide users toward designs that follow best practices. These include: Finish Attribute Relationship Designer, Dimension structure (presentation of attribute relationships), modification to wizards to align output with best practices, simplifying creation of composite keys, and AMO warnings (spanning all objects, not just dimensions) SQL Server Samples Microsoft is making all the SQL Server samples available on CodePlex. Now you can install individual samples for the specific feature you're learning about rather than installing all the samples at once. They are also releasing Community Samples on CodePlex. These are samples created by SQL Server MVPs, Microsoft employees or other members of the community. In summary, it looks like the start of a very interesting summer for SQL Server news.

Return to Microsoft announces SQL Server 2008

Microsoft announces SQL Server 2008

Written by Bill Graziano on 04 June 2007

Microsoft is making a series of announcements at Tech*Ed related to SQL Server 2008 -- previously codenamed "Katmai". I've got some details on some of the new features including the MERGE statement, Table Valued Parameters, Change Data Capture and the Declarative Management Framework. There should also be a download of the June CTP available inside Connect.

MERGE Statement

One of the things I'm most interested in is the MERGE statement. I'd always referred to this jokingly as an UPSERT statement however the 2003 ANSI-SQL standard defines the syntax using the MERGE keyword. MERGE gives you a single statement to do inserts or updates based on whether a record exists or not. The syntax should look something like this:

MERGE INTO FactTable F
USING TransactionTable T ON T.OrderID = F.OrderID
WHEN MATCHED THEN UPDATE
	SET F.Quantity = T.Quantity
WHEN NOT MATCHED THEN INSERT (OrderID, Quantity)
VALUES (T.OrderID, T.Quantity)

This will be very useful to populate tables in data warehouses. And handy for DBA's that are too lazy to write separate stored procedures for updates and inserts ;)

Change Data Capture

Microsoft describes this as:

Change Data Capture (CDC) is a generic component that will track database changes asynchronously and expose the changes through a relational interface which can be consumed easily. Through this interface, consumers can very easily track changes based on their specific requirements and consume the change data using T-SQL or other data access methods.

This is certainly an interesting feature. A way to run a trace of data changes without actually running a trace. My guess is they'll use Service Broker under the hood to expose the data. I especially like the ability to manipulate it in T-SQL. This should solve a great many problems with audit issues. If remote clients can connect in and capture this information that provides a way to audit outside SQL Server.

Declarative Management Framework

This is one that I know the least about. I didn't get to sit in the session at the MVP Summit. They describe it as:

Declarative Management Framework (DMF) is a new policy-based management framework for the SQL Server Database Engine that delivers the following benefits:

Ensure compliance with policies for system configuration

Prevent/monitor changes to the system by authoring policies for the desired configuration

Reduce total cost of ownership by simplifying administration tasks

Basically you define a policy and then you can "deploy" that policy to other servers.

Table Valued Parameters

Oh how I've pined for this feature! I just want a simple way to pass a set of rows to a stored procedure. I can't tell you how many stupid little hacks I've written to do something like this.

Data Warehouse Enhancements

Microsoft is also making enhancements to the query processor that will benefit data warehouses. Specifically, the optimizer will recognize star joins and will improve the query response time. They are also making improvements to Analysis Server. According to Microsoft these include:

Enhance UI for creating and editing dimensions to guide users toward designs that follow best practices.
These include: Finish Attribute Relationship Designer, Dimension structure (presentation of attribute relationships), modification to wizards to align output with best practices, simplifying creation of composite keys, and AMO warnings (spanning all objects, not just dimensions)

SQL Server Samples

Microsoft is making all the SQL Server samples available on CodePlex. Now you can install individual samples for the specific feature you're learning about rather than installing all the samples at once. They are also releasing Community Samples on CodePlex. These are samples created by SQL Server MVPs, Microsoft employees or other members of the community.

In summary, it looks like the start of a very interesting summer for SQL Server news.