Yesterday for me was SQL Server 2008 day, where virtually all the sessions I attended were based on SQL Server 2008. By 5pm when getting a coffee I started to think in T-SQl.
I think it went something like: select CupWithLiquid from Drink where Type=’Coffee’ and Milk=TRUE and Sugar=2 and Caffeine=’LOTS’
Not content with the SQL Server bashing of yesterday, I went back for a yet another day virtually fill of SQL Server today. “Life is so much easier with indices” – am I going SQL mad??!!
I decided to group together my notes from all the SQL Server sessions I have attended over the last two days and put it in one blog because there was significant overlap between the various sessions.
I have divided my notes into the following sections:
a) T-SQL enhancements
b) XML enhancements
c) Service Broker enhancements
Create Procedure SQLServer2008TechEdReport
begin
a) T-SQL Enhancements
Table Value Parameters
Ask any developer who has worked with SQL Server what is the problem they most commonly run into and they will most probably respond something along the following lines: We want to pass a list of rows of data as a parameter to a stored procedure, and we either have to use a comma delimited list (which has the potential to become corrupted once we exceed the size of varchar(max)) or we use XML (which has high performance costs when stripping the XML).
Table Value Parameters solves this whole problem space in a very neat way by allowing the consumer to pass a table of rows as a parameter into a stored procedure. For some this feature alone makes SQL Server a worthwhile release (me included!).
Below is a real-world scenario where this feature demonstrates its’ raison d’etre (there are many other such scenarios):
We have a retail scenario where we have a basket which consists of basketlines, stored in the Basket and BasketLine table respectively. There is a stored procedure to add multiple basket lines to be linked to a basket called “AddBasketLines” which in the past had the BasketID as an int and BasketLines as XML (or varchar(max) if a comma delimited list was used).
With SQL Server 2008 the BasketLines parameter would be declared as a BasketLines Type containing (which contains all the columns which a BasketLine consists of). The consumer of the stored procedure would pass a DataTable with each row representing a Basket Line, using the new SQLDBType of Structured when calling the Stored Procedure using ADO.Net. The AddBasketLines stored procedure can then insert all the rows in one pass, dealing with the parameter much in the same way it would with any other table. This insertion would be done without any XML shredding or comma delimited list processing, achieving an substantial performance improvement.
Merge
Many RDBMS have had this feature for a long while, and many would say it is well overdue in SQL Server, well SQL Server 2008 finally delivers this feature. The Merge statement allows for combining an Insert, Update and Delete all in one statement, when performing operations between two tables. To best illustrate this feature I will extend on the scenario described in the Table Value Parameters section.
There is a stored procedure called “UpdateBasketLines” which updates the BasketLine table with the BasketLines that is passed to it via the client as a Table Value Parameter. The client application will pass the entire BasketLine collection which may require the insertion of new basketline records , deletion of existing basketline records or updating of existing basketline records. Previously such an operation would require at least three passes in order to achieve a set of rows in the BasketLine table matching that which is passed by the client application. With SQL Server 2008 and the new Merge statement, the entire operation can be done in one pass. According to the query plan in the examples I have run the performance benefit is not 300% as it may seem likely on the surface, it is more like 200%, which is no mean feat.
So how does it work? Well the best way to explain this is by showing the SQL syntax of the Merge statement that would be written for this scenario:
MERGE BasketLine AS bi
USING (SELECT BasketLineID, BasketID, ProductID, Quantity FROM @BasketLines) as bis
ON bi.BasketLineID = bis.BasketLineID and bi.BasketID = bis.BasketID
WHEN MATCHED AND (bi.ProductID <> bis.ProductID or bi.Quantity <> bis.Quantity) THEN UPDATE SET bi.ProductID = bis.ProductID, bi.Quantity = bis.Quantity
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT values (bis.BasketID, bis.ProductID, bis.Quantity);
Here is a decomposition of the above Merge statement:
a) How to join the data in the Table Value Parameter (@BasketLines) ---> ON bi.BasketLineID = bis.BasketLineID and bi.BasketID = bis.BasketID
b) What to do when both rows match on the key but the ProductID or Quantity is different [update the values] ---> UPDATE SET bi.ProductID = bis.ProductID, bi.Quantity = bis.Quantity
c) What to do when there is no matching row in the source (i.e. the @BasketLines Table Value Parameter) [Delete the row in the BasketLine table] ---> DELETE
d) what to do when there is no matching row in the destination (i.e. the BasketLine table) [insert a row in the BasketLine table] ---> INSERT values (bis.BasketID, bis.ProductID, bis.Quantity)
As you can see in one pass the contents of the BasketLine table and @BasketLines Table Value Parameter are synchronised.
Delighters (aka Shortcuts)
The SQL Server team have clearly been influenced by the c# team when adding some new features to the T-SQL language in SQL Server 2008.
Declare and Assign in-line
As in C# it is now possible to declare a variable and assign to it on the same line (e.g. declare @CustomerID int = 123)
Multiple inserts in one statement
It is now possible to insert multiple rows into a table (explicit data as opposed to from another table), within the same SQL Statement (e.g. insert into Customer (FirstName, LastName) values (‘David’, ‘Myers’), (‘John, ‘Smith), (‘Peter, ‘Jones)
Compound assignment statements
Another new language feature copied from C# is the use of +=, –+, /+, and *= in order to increment, decrement, multiply, divide values by themselves. These += clause can also be used to concatenate strings.
Date and Time
There have been many changes to the DateTime types within SQL Server 2008 namely the addition of the following types:
a) dateTime2 (excellent naming – NOT!) - Goes down to milliseconds and accepts dates before 1753
b) dateTimeOffSet – Allows for time zones to be stored as a part of a date.
c) Date – Contains only the date portion of DateTime (e.g. 12/08/2008)
d) Time – Contains only the time portion of DateTime (e.g. 12:20:22.00)
Two new functions have also been added associated with time zone offsets:
a) ToDateTimeOffSet – Converts a dateTime2 variable to a specified offset.
b) SwitchOffset – Changes the offset stored with a dateTime2 variable.
Spatial Data
SQL Server 2008 adds a new type GEOGRAPHY which can be used to store and manipulate geodetic and planar spatial data. in addition a new Index type is included to handle Spatial data types for high-query performance. In SQL Server Management Studio when a result involves a spatial data type, a new table is displayed “Spatial Results” which plot the results on a graph.
Sparse Columns
Sparse columns are columns that are optimized for the storage of NULLs. To define a column as sparse, specify the SPARSE attribute as part of the column definition. Sparse columns consume no storage for NULLs, even with fixed-length types; however, when a column is marked as sparse, storage of non-NULL values becomes more expensive than usual. A sparse column should only be used when it will store a large percentage of NULLs.
b) XML Enhancements
XML was certainly not the focal point of new features in SQL Server 2008, with not many new features added.
Below is a summary of the new XML features in SQL Server 2008:
XML Schema Validation
Lax Validation Support
Lax validation allows you to validate any schema definitions specified but ignore those not specified (previously it was the case of validate or not).
An example of this is illustrated with the following schema definition:
<schema>
<element name="OrderLine" type="anyType"/>
<element name="ProductID" type="int"/>
<element name="Quantity" type="int"/>
<element name="Kgs" type="int"/>
</schema>
The following XML is valid even though the element <NoInPacket> is not defined in the schema:
<OrderLine>
<ProductID>55</ProductID>
<NoInPacket>Wrong</NoInPacket>
<Quantity>2</Quantity>
</OrderLine>
However the following XML is invalid because the <Kgs> element was defined as int and not string:
<OrderLine>
<ProductID>22</ProductID>
<Quantity>2</Quantity>
<Kgs>Wrong</Kgs>
</OrderLine>
Full xs:dateTime Support
When storing dateTime in XML in SQL Server 2005 you had to specify your timezone when storing dateTime did not preserve the time zone information for your data for dateTime or time, instead if normalized it to UTC. Now you no longer have to specify the timezone and if a timezone is specified it is preserved and no normalization to UTC is performed.
Union and List Types
You can use XML schemas to define data types for your XML data that allow a limited set of values to be assigned to multi-value elements and attributes (i.e. enumerations). In SQL Server 2008 you can union these enumerations given the option to have more than one type of enumeration to be used. For example if you had a list of shirt sizes that could be either “S, M, L” or “18, 20, 22”, in SQL Server 2005 you could not have an enumeration combining both sets of values. This features allows both the below to be valid xml:
<TShirtSize>22</TShirtSize>
<TShirtSize>L</TShirtSize>
XQuery Enhancements
Let Clause
SQL Server 2008 adds support for the let clause, which is used to assign values to variables in an XQuery expression (as illustrated below)
SELECT @x.query(
'<Orders>
{
for $invoice in /Invoices/Invoice
let $count :=count($invoice/Items/Item)
order by $count
return
<Order>
{$invoice/Customer}
<ItemCount>{$count}</ItemCount>
</Order>
}
</Orders>')
XML DML Enhancements
insert expression
In SQL Server 2005 the xml data type supports the XML DML expressions insert, replace value of, and delete through its modify method – guess which one is missing? Insert!
SQL Server 2008 adds support for using an xml variable in an insert expression to insert XML data into an existing XML structure. Be warned the performance is horrendous (I tried this a few months back when I first found out about this new feature).
Below is an example:
declare @newSQLServerVersion varchar(50)
set newSQLServerVersion = 'SQL Server 2008'
SET @sqlServerVersionList.modify('insert sql:variable("newSQLServerVersion") as last into (/SQLServerVersions)[1]')
c) Service Broker Enhancements
The enhancements to Service Broker in SQL Server 2008 is few and far between, although there are one or two significant additions.
Conversation Priority
The most significant enhancement is one that is present in virtually all other message-based frameworks, “Conversation Priority”. This allows for developing a solution where some messages should be processed as a higher priority than others. Messages are sent over conversations using Service Broker from one Service to another. Conversation Priorities are stipulated in the Destination database based on the Contract, Sending Service or Receiving Service. The priority can be set from 1 to 10, with 10 being the highest priority. The priority rules determine the order in which messages get transmitted or received. One example where this new feature could be implemented is the processing of instructions where different initiators have differing SLA levels e.g. customer facing instructions having a higher priority over internal initiated instructions.
In the Service Broker community this feature was not really what we were looking for, but it is recognised as a step in the right direction. The idea of priority should have been extended to being able to specify the priority of a message, based on its type, instead of its origin or destination. This can be achieved today via some clever workarounds (i.e. hacks) , and I guess at least the ability to prioritise conversations supplies yet another workaround route to message prioritisation.
Diagnostic Utility
Service Broker comes with very few tools, and often tools have to be written by the developers of a Service Broker-based solution. Microsoft have however supplied one new tool which will be very useful in diagnosing problems associated with a Service Broker-based solution. The Diagnostic Utility is a command-line tool (called SSBDIAGNOSE) which has two main purposes:
a) Analyze the configuration between two Service Broker Services –-> gives you the ability to preempt problems.
b) Analyze running conversations for errors –-> gives you the ability to identify problems when they happen.
Of course it would have been nice for this to be incorporated into SQL Server Management Studio, and maybe in future releases it will be (although no promises have been made thus far!)
SQL Server Management Studio Service Broker Enhancements
SQL Server Management Studio for SQL Server 2008 is still pretty much devoid of any graphical utilities to help a developer of a Service Broker-based solution, however at least there have been some token efforts made to add something for Service Broker developers.
Service Broker Object Templates
Right-clicking on any of the Service Broker object types (e.g. Service, Queue etc…) will give you the option to Create a new instance of that object, which in effect opens up the template for that object type. In addition right-clicking an existing instance of a Service Broker object gives you the option to script the create, alter or drop the object. Another template that is available via Management Studio which will help Service Broker beginners is one to create a Service Broker application. It would have been nice to create maybe some wizards to assist the creation of Service Broker objects, but that is not within this release (despite early promises that it would be).
Service Broker Reports
There is one new report which has been added to SQL Server Management Studio for SQL Server 2008 which relates to Service Broker. The report is quite comprehensive giving you the ability to drill down through services, queues, activations and messages. It would be nice if this could be updated in real-time, although I guess writing a macro or clicking f5 is not such a hardship!
New System Monitor Objects and Counters
Five new objects has been added to the Broker Statistics performance objects:
Activation Errors Total
Corrupted Messages Total
Dequeued TransmissionQ Msgs/sec
Dropped Messages Total
Enqueued TransmissionQ Msgs/sec
END