Whenever a new version of SQL Server is released, there’s a lot of talk about the big new features. But in this article, rather than focusing on SQL Server 2008’s big features, I’ll cover smaller improvements that you might have overlooked. I’ll discuss the enhanced VALUES clause, support for the ISO week system, and new conversion options between binary and character types.
VALUES Clause
SQL Server 2008 introduces support for a row value constructor (also known as a table value constructor). Using the VALUES clause, you can now represent more than one row. The obvious scenario in which you might want to use this option is to use a single INSERT statement to insert multiple rows into a table rather than needing to use multiple INSERT statements. To demonstrate this capability, first run the code in Listing 1 to create a table called Orders in the tempdb database (for test purposes).
The following code demonstrates how to use the enhanced VALUES clause with a single INSERT statement to insert 6 rows:
INSERT INTO dbo.Orders (orderid, orderdate, empid, custid) VALUES (10001, '20090212', 3, 'A'), (10002, '20090212', 5, 'B'), (10003, '20090213', 4, 'B'), (10004, '20090214', 1, 'A'), (10005, '20090213', 1, 'C'), (10006, '20090215', 3, 'C');
As you can see, each pair of parentheses represents a single row, and you separate the rows with commas. Besides the obvious benefit of shortening your code, another benefit of using a single statement is that the operation is considered atomic. If any of the rows fails to enter the target table, the entire INSERT statement fails.
Prior to SQL Server 2008 you could achieve a similar capability by performing a UNION ALL operation between several SELECT statements based on constants, like so:
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid)
SELECT 10001, '20090212', 3, 'A'
UNION ALL SELECT 10002, '20090212', 5, 'B'
UNION ALL SELECT 10003, '20090213', 4, 'B'
UNION ALL SELECT 10004, '20090214', 1, 'A'
UNION ALL SELECT 10005, '20090213', 1, 'C'
UNION ALL SELECT 10006, '20090215', 3, 'C';
In fact, the current implementation of the enhanced VALUES clause is internally handled like this UNION ALL solution—so don’t expect any performance improvements. One benefit is that the VALUES clause is standard, whereas the solution based on UNION ALL isn’t, because the queries don’t have FROM clauses. We can only hope that in a future version of SQL Server, the VALUES clause will also provide performance benefits over the existing technique.
Note that you’re not restricted to using a table value constructor only with an INSERT statement. You can also use it to construct a derived table that you query in an outer query’s FROM clause, like so:
SELECT *
FROM ( VALUES
(10001, '20090212', 3, 'A'),
(10002, '20090212', 5, 'B'),
(10003, '20090213', 4, 'B'),
(10004, '20090214', 1, 'A'),
(10005, '20090213', 1, 'C'),
(10006, '20090215', 3, 'C') )
AS O(orderid, orderdate, empid, custid);
Table 1 shows this query’s output.
I find this capability useful whenever I need to create a small auxiliary table made of constants for the purposes of a query. Now you don’t need to create a temporary table or table variable, or use the nonstandard UNION ALL technique for this purpose. Still missing, however, is the ability to define a common table expression (CTE) based on a table value constructor. For now, the technique works only with derived tables.
ISO Week
The ISO week system is a week-numbering system used mainly in European countries. The first day of the week is always Monday. All days of the same week have the same week number, even if the week spans two years (i.e., end of December and beginning of January). If a week spans two years, whether the week number is the last in the first year (i.e., week 52 or 53) or the first in the second year (i.e., week 1) depends on which year the week’s Thursday occurs. For example, take the week starting on Monday, December 29, 2008, and ending on Sunday, January 4, 2009. The Thursday of that week falls in the year 2009 (January 1), hence all days of that week get week number 1.
SQL Server 2008 introduces a simple and efficient way to calculate an ISO week number. You get a new part called ISO_WEEK in the DATEPART function. The following code shows an example of calculating an ISO week number using the DATEPART function for a set of input dates:
SELECT
CAST(dt AS DATE) AS dt,
DATEPART(ISO_WEEK, dt) AS weeknum,
DATENAME(weekday, dt) AS weekday
FROM ( VALUES
('20081228'),
('20081229'),
('20081230'),
('20081231'),
('20090101'),
('20090102'),
('20090103'),
('20090104'),
('20090105') ) AS D(dt);
Table 2 shows the output of this code.
Prior to SQL Server 2008 you had to implement your own manipulation to calculate an ISO week number for a given date. For example, Listing 2 shows the definition of a scalar user-defined function UDF to calculate an ISO week number taken from SQL Server 2005’s Books Online under the subject CREATE FUNCTION.
As you can see, the definition of the function is quite convoluted. Also, scalar UDFs incur a high performance penalty when invoked against a table per row. The following code shows an example for using this function against a set of dates:
SET DATEFIRST 1; SELECT CAST(dt AS DATETIME) AS dt, dbo.ISOweek(dt) AS weeknum, DATENAME(weekday, dt) AS weekday FROM ( SELECT '20081228' UNION ALL SELECT '20081229' UNION ALL SELECT '20081230' UNION ALL SELECT '20081231' UNION ALL SELECT '20090101' UNION ALL SELECT '20090102' UNION ALL SELECT '20090103' UNION ALL SELECT '20090104' UNION ALL SELECT '20090105' ) AS D(dt);
Binary-Character Conversion
Another of SQL Server 2008’s small features is one that has to do with conversions between binary and character data. When you use the CONVERT or CAST functions to convert binary to character data or the other way around, you don’t get the same characters in the source and target values. For example, when converting the character string ‘1A’ to binary you normally get the binary representation of the characters: 0x3141. Similarly, when converting the binary string 0x3141 to character, you get the characters that this binary data represents: ‘1A.’
In certain cases you might need to convert binary data to character or character to binary data such that each digit in the source value is represented by the same digit in the target. That is, the string ‘1A’ (or ‘0x1A’ with the 0x prefix) would be converted to the binary value with those hex digits: 0x1A, and 0x1A would be converted to the string ‘1A’ (or ‘0x1A’ with the 0x prefix). This capability is sometimes useful for importing data.
SQL Server 2008 introduces a simple and efficient solution in the form of new style numbers in the CONVERT function. Style 0 represents the default pre-SQL Server 2008 behavior. Style 1 should be used when the character string has a 0x prefix, and style 2 should be used when it doesn’t. As an example, the following code demonstrates conversion of a binary value to a character string using both styles:
SELECT CONVERT(VARCHAR(20), 0x4775696E6E657373, 1) AS bin_to_char_with_0x_prefix, CONVERT(VARCHAR(20), 0x4775696E6E657373, 2) AS bin_to_char_no_prefix;
Table 3 shows the output of this code.
The following code demonstrates conversion of character strings to binary values using both styles:
SELECT CONVERT(VARBINARY(10), '0x4775696E6E657373', 1) AS char_with_0x_prefix_to_bin, CONVERT(VARBINARY(10), '4775696E6E657373', 2) AS char_no_prefix_to_bin;
Table 4 shows the output of this code.
It was possible to achieve similar behavior prior to SQL Server 2008, but you had to use scalar UDFs—so the solution was much slower. SQL Server 2005 and SQL Server 2000 provide a built-in UDF called fn_varbintohexstr that does the binary to character conversion. Here’s an example for using the function:
SELECT sys.fn_varbintohexstr(0x4775696E6E657373);
Internally, this function calls a more flexible function called fn_varbintohexsubstring. This function accepts four arguments. The first indicates whether to include the 0x prefix in the output (1) or not (0). The second is the binary value. The third indicates in which byte to start extracting the substring (use 1 for beginning), and the fourth indicates how many bytes to consider (0 for all). So the above call to the function fn_varbintohexstr is equivalent to the following call to fn_varbintohexsubstring:
SELECT sys.fn_varbintohexsubstring(1,0x4775696E6E657373,1,0);
If you’re curious about the T-SQL definition of the function, you can find it by running the following code:
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.fn_varbintohexsubstring'));
As you can see, the function is quite long and convoluted. If you need to perform such conversions, you’ll appreciate the simplicity, elegance, and efficiency of the new option using the CONVERT function.
Pre-2008 versions of SQL Server did not provide an option for converting the other way around; namely, from character to binary. As long as you don’t need to implement this logic in a function, and need to operate on a single value, you can use a neat trick that I learned from my friend Ron Talmage. You simply construct a dynamic batch where you concatenate the character representation of the binary value as part of the code as an assignment of a binary value to a parameter, like so:
Char to Bin pre-2008 Using dynamic SQL DECLARE @char AS NVARCHAR(20), @bin AS VARBINARY(10), @sql AS NVARCHAR(500); SET @char = N'0x4775696E6E657373'; SET @sql = N'SET @result = ' + @char + N';' EXEC sp_executesql @stmt = @sql, @params = N'@result AS VARBINARY(10) OUTPUT', @result = @bin OUTPUT; SELECT @bin;
But if you want to be able to invoke the conversion in a query against a table, you must implement it as a UDF. You can use the function fn_chartobin that is provided in Listing 3 for this purpose.
The function extracts from the input string one pair of digits at a time (since each pair represents a byte), produces the corresponding binary byte, and concatenates it to the result binary string. The function can accept the input with or without the 0x prefix. To test the function, run the following code:
SELECT dbo.fn_chartobin('0x4775696E6E657373');
The output will be the binary value 0x4775696E6E657373.
Not Much Ado About Something
It’s easy to overlook some of the T-SQL enhancements in SQL Server 2008, especially because Microsoft hasn’t made a lot of fuss about them. Three small features that you might find handy include the enhanced VALUES clause, improved ISO week number calculation, and the ability to convert character to binary and binary to character values while preserving the hexadecimal digits. Next month I’ll cover some additional SQL Server 2008 features that you might find useful.