Archive for August, 2011

SQL Server Denali – CONCAT() string function

28/08/2011 Leave a comment

CONCAT() is new string function in SQL Server Denali. It returns a string that is the result of concatenating two or more string values (or values that can be converted to string).
Syntax: CONCAT (string_value1, string_value2 [, string_valueN ] )

With CONCAT function, you can concatenate between 2 and 254 values. If you try to use CONCAT with only one value or with more than 254 values you will get an error.


Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Simple examples:

SELECT CONCAT('SQL', 'Server', 'Denali') AS Result1

DECLARE @a varchar(10) = 'SQL'
DECLARE @b varchar(10) = 'Server'
DECLARE @c varchar(10) = 'Denali'
SELECT CONCAT(@a, @b, @c) AS Result2

USE AdventureWorks2008R2
SELECT TOP 5 CONCAT(FirstName, LastName) AS FullName
FROM Person.Person

It’s easy to work with CONCAT function, but as you can see there is no string delimiter between values. To get proper FullName in the last example you will need to add space between FirstName and LastName.

USE AdventureWorks2008R2
SELECT TOP 5 CONCAT(FirstName, ' ', LastName) AS FullName
FROM Person.Person
Syed Abbas
Catherine Abel
Kim Abercrombie
Kim Abercrombie
Kim Abercrombie

CONCAT function has 2 advantages compared to the concatenating of strings using the “+” operator:
1. NULL values are implicitly converted to an empty string
2. All arguments are implicitly converted to string

CONCAT() and NULL arguments

CONCAT function automatically converts all NULL arguments to an empty string. It’s great for T-SQL developers. In next examples you can see the difference between CONCAT function and concatenation of strings using the “+” operator.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = NULL
DECLARE @c varchar(10) = 'Denali'
-- CONCAT implicitly convert NULL to an empty string
SELECT CONCAT(@a, @b, @c) AS Result1

-- If you use "+" operator with NULL result is NULL
SELECT @a + @b + @c AS Result2

-- To get the same result with "+" operator you need to deal with NULL values
SELECT @a + ISNULL(@b, '') + @c AS Result3
SQL ServerDenali
SQL ServerDenali

If all the arguments are null, then an empty string of type varchar(1) is returned.

CONCAT() and non-string arguments

CONCAT function implicitly converts all non-string arguments to string data types (if conversion is possible) and then concatenate values.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT CONCAT(@a, @b, @c, @d) AS Result1
SQL ServerDenali2011Aug 27 2011  9:42PM

As you can see, @c and @d are implicitly converted to string and you get result back. If you use same example with “+” operator you will get an error.
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ‘SQL ServerDenali’ to data type int.

To get the same result with “+” operator you need to convert @c and ©d values to string data types.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT @a + @b + CONVERT(varchar(10),@c) + CONVERT(varchar(20),@d) AS Result2
SQL ServerDenali2011Aug 27 2011  9:42PM

As you can see, concatenating string is much easier with CONCAT function.  What you think?

Categories: Denali, TSQL

SQL Server Denali – IIF() and CHOOSE() functions

22/08/2011 Leave a comment

SQL Server Denali introduced 2 new logical functions: IIF and CHOOSE.

IIF function

IIF function returns one of two values, depending on whether Boolean expression evaluates to true or false.
Syntax:  IIF (boolean_expression, true_value, false_value)
IIF function is simple replacement for:
Case when <boolean_expression>=true  then <true_value> else <false_value> end

Let’s see how IIF function works.

-- Simple IIF
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

In next select statement, I will use IIF function to retrieve PriceDescription value. If ListPrice is greater the 1000 then set Expensive for PriceDscription else it is Cheap.

If we look at the execution plan, we will see that Compute Scalar is used to support IIF function. On Compute Scalar property we can see that IIF function is converted to CASE expression. So, the same rules that apply to the CASE statement also apply to IIF function.

IIF function can be nested but only up to the level of 10. Next examples show IIF nesting to 10th level and error if we try to nest IIF up to the level of 11.

CHOOSE function

CHOOSE function returns a value from the given array based on the specified index position.

Syntax: CHOOSE (index, val_1, val_2 [, val_n ])

Let’s see how CHOOSE function works.

SELECT CHOOSE(1, 'a', 'b', 'c') AS First,
CHOOSE(2, 'a', 'b', 'c') AS Second;
First Second
----- ------
a     b
-- If the index value exceeds the bounds of the array of values,
-- then CHOOSE returns null.
SELECT CHOOSE(0, 'a', 'b', 'c') AS First,
CHOOSE(4, 'a', 'b', 'c') AS Second
First Second
----- ------
-- If the provided index value has a numeric data type other than int,
-- then the value is implicitly converted to an integer
SELECT CHOOSE(2.5, 'a', 'b', 'c') AS First,
CHOOSE(3.9, 'a', 'b', 'c') AS Second;
First Second
----- ------
b     c

In next query, I will show how CHOOSE function can be used in clauses such as select list, GROUP BY and ORDER BY.

SELECT p.Name,
SUM(s.OrderQty) SellQuantity,
CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th') AS Quarter
FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p
ON p.ProductID = s.ProductID
WHERE s.ModifiedDate >= '2006-01-01'
AND s.ModifiedDate < ' 2007-01-01'
GROUP BY p.Name,CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')
HAVING SUM(s.OrderQty) > 600
ORDER BY p.Name, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

If we look at the execution plan, we will see that Compute Scalar is used to support CHOOSE function. The engine has taken the CHOOSE function and converted it into a CASE.

IIF and CHOOSE functions are nice additions to the language and it’s good to know how they work.

Categories: Denali, TSQL

XEVENT Provider in Denali

16/08/2011 Leave a comment

XEVENT provider is new sqlps provider in Denali CTP3. To admit, I was surprised when I saw it. In SQL Server 2008R2 we don’t have any GUI for XEVENT (Extended Events) written by SQL Team and now, in Denali CTP3, we have nice GUI through SSMS and sqlps provider. Nice work.

XEVENT provider is built on SMO Library (Microsoft.SqlServer.Management.XEvent Namespace). I will try to show how to work with XEVENT provider from the beginning. To start to work with XEVENT objects in sqlps, we first need to access XEVENT provider in sqlps. From PS SQLSERVER:\ type CD XEVENT. Then you need to navigate to server instance typing <server name>\<instance name>. When you are at instance level, you can see that XEVENT provider have 2 subfolders: Packages and Sessions.

From Packages subfolder we can access to metadata of all objects that are available on the instance. To be precise, all public metadata, private objects are not visible with XEVENT provider. From Sessions subfolder we can see all available sessions on the system, manage it and create new sessions. We can even generate SQL DDL script for CREATE, DROP and ALTER session.

Let’s start with Packages subfolder. (1)From Packages subfolders we can see all public packages on the system. (2)To access some package ModuleId and Name need to be specified. (3) From the Package we can get all metadata that are available in the package: events, actions, targets…

From there, to list all Events in the package we can type: dir .\EventInfoSet
The same pattern can be used for any object in the package: ActionInfo for Actions, TargetInfo for target etc.
To see Event Fields for some specific Event we can use the code:

# Get Event "sqlserver.scan_started" in $event variable
$event = dir .\EventInfoSet | Where-Object {$ -eq "scan_started"}
# Return ReadOnly Event Fields
# Return Data Event Fields
# Return Customizable Event Fields

It’s nice to have possibility to see Extended Events metadata with Powershell but I prefer to use T-SQL script or new GUI in SSMS.

For me, sessions subfolder is much more useful. (1) From Sessions subfolder we can see all available session on that instance, manage it or create new session. (2) On my server, 3 sessions are available. System_health is running and Perf1 and Perf2 are stopped. (3) We can navigate to system_health session and from there (4) we can see all events and targets that are associated to the session.

If you want to see all relevant information (actions, predicates and event fields) for specific event in the system_health session you can use:

dir .\Events | Where-Object {$ -eq "sqlserver.error_reported"} | Format-List Name, Actions,PredicateExpression, EventFields


$ev = dir .\Events | Where-Object {$ -eq "sqlserver.error_reported"}

From sessions subfolder, session can be started and stopped.

# Find system_health session
$session = Get-ChildItem | Where-Object {$ -eq "system_health"}
# Stop session
# Check is session running

If you want to create T-SQL DDL for CREATE EVENT SESSION and DROP EVENT SESSION you can use next code:


In this post I showed some basics of using XEVENT provider in Denali sqlps. I hope it will help someone.

Categories: Denali, Extended Events, SQLPS

Microsoft SQL Server Denali CTP 3 Product Guide

12/08/2011 Leave a comment

Microsoft has released a Product Guide for SQL Server Denali CTP3. It contains white papers, presentations, datasheets, demos and links to online videos and references. It’s excellent source of information for everyone who want to experience and evaluate Microsoft SQL Server Denali.

Categories: Denali


08/08/2011 Leave a comment

I’ve tried Denali CTP3 SQLPS and noticed some great changes. Here I will describe my first experience with Denali SQLPS.
I’ve started SQLPS by right-clicking on server node in SSMS Object Explorer and selecting “Start PowerShell”. I read that sqlps is no longer a mini-shell, so I first executed Get-Module cmdlet. As a result I got 2 modules: SQLPS and SQLASCMDLETS.
Under “PS SQLSERVER:\>”  I ran dir and here is result:

I noticed 3 new Providers in Denali CTP3: XEvent, IntegrationServices and SQLAS.
Next, I looked for available Cmdlets in SQLPS and SQLASCMDLETS modules.

Get-Command -CommandType Cmdlet -Module sqlps | select name
Get-Command -CommandType Cmdlet -Module SQLASCMDLETS  | select name

Here are the results:

SQLPS Cmdlets


Add-SqlAvailabilityDatabase Add-RoleMember
Add-SqlAvailabilityGroupListenerStaticIp Backup-ASDatabase
Backup-SqlDatabase Invoke-ASCmd
Convert-UrnToPath Invoke-ProcessCube
Decode-SqlName Invoke-ProcessDimension
Disable-SqlHADRService Invoke-ProcessPartition
Enable-SqlHADRService Merge-Partition
Encode-SqlName New-RestoreFolder
Invoke-PolicyEvaluation New-RestoreLocation
Invoke-Sqlcmd Remove-RoleMember
Join-SqlAvailabilityGroup Restore-ASDatabase

There are 29 cmdlets in SQLPS module and 11 cmdlets in SQLASCMDLETS  module. All new cmdlets in SQLPS module, except Backup-SqlDatabase and Restore-SqlDatabase, are for high-availability and disaster recovery solution (HADR).
Next, I looked inside SQL, SQLPolicy and SQLRegistration Provides. It seems that they are the same as in SQL Server 2008R2. XEvent provider gives us possibility to work with Extended Events. I must admit that I like it. My next post will be about it. 🙂

I had troubles with IntegrationServices provider.

I found solution in Chad Miller’s blog post, but my first command finished with bug. I filled a bug report on Connect and here is response from Microsoft:

Similar as 5.9 Cannot Load the Data-tier Application Assembly in PowerShell in Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes, here, 

The SQL Server Code-Named “Denali” CTP3 IntegrationServices assemblies are compiled using .Net Framework 4.0, and .Net Framework 4.0 assemblies are not supported in any currently available version of PowerShell. No workaround exists for this issue; IntegrationServices operations are not supported in PowerShell for SQL Server Code-Named “Denali” CTP3.

For now, I will not play with IntegrationServices.

Backup-SqlDatabase and Restore-SqlDatabase test

I decided to test Backup-SqlDatabase and Restore-SqlDatabase cmdlets. First, I extracted help about cmdlets to .txt files.
Get-Help Backup-SqlDatabase -detailed > D:\Backup-SQLDatabase.txt
Get-Help Restore-SqlDatabase -detailed > D:\Restore-SQLDatabase.txt
I tested Backup-SQLDatabase cmdlets and it seems to be working correctly. I even tried to create a compressed backup on the media with non-compressed backup.  It didn’t work as I expected.

To format disk Mediaset you need to use “FormatMedia”, “Initialize” and “SkipTapeHeader” properties during backup.

Backup-SqlDatabase -Database TestDB -BackupFile “D:\1\Test.bak” -CompressionOption on -FormatMedia -Initialize -SkipTapeHeader

Here is how I restored my database with Restore-SqlDatabase  cmdlet.

Categories: Denali, SQLPS

SQL Server Denali – EOMONTH()

03/08/2011 1 comment

SQL Server Denali introduces 7 new date and time built-in functions. One of these functions is EOMONTH(). EOMONTH() returns the date value that represents the last day of the month for given date.

Syntax: EOMONTH( start_date [, months_to_add] )

Pre- Denali, to calculate the date value that represents the last day of the month we used:

2011-08-31 00:00:00.000

In Denali, we can use EOMONTH function.

EOMONTH('2011-09-05') AS LastDay2
LastDay1                LastDay2
----------------------- ----------------------
2011-08-31 00:00:00.000 2011-09-30 00:00:00.00

If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth,
EOMONTH (GETDATE(),1)LastDayOfNextMonth
LastDayOfPreviousMonth  LastDayOfMonth         LastDayOfNextMonth
----------------------- ----------------------- -----------------------
2011-07-31 00:00:00.000 2011-08-31 00:00:00.000 2011-09-30 00:00:00.000

It is strange that SQL Server team hasn’t added a function for beginning of the month.  For me, BOMONTH would be more valuable because almost all reporting queries I’m writing make more sense with first date of month.

To calculate the first day of the month for given date.

-- Pre-Denali
-- With EOMONTH function
-- or with DATETIMEFROMPARTS - new Denali datetime function
Categories: Denali, TSQL

SQL Server Denali– TRY_CONVERT() conversion function

01/08/2011 Leave a comment

TRY_CONVERT is new conversion function in SQL Server Denali. Unlike the CAST and CONVERT functions, TRY_CONVERT allows us to test whether it is possible to convert a value to specific data type.

TRY_CONVERT converts passed value to the specified data type, if conversion isn’t possible null is returned. But, if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
style accepts the same values as the style parameter of the CONVERT function.

Let’s see how it works.

SELECT TRY_CONVERT(datetime, '2010-12-13') AS Date1,
TRY_CONVERT(datetime2, '20101203') AS Date2,
TRY_CONVERT(datetime, '21:15') AS  Date3,
TRY_CONVERT(time, '2010-12-13') AS Time1,
TRY_CONVERT(time, '21:15') AS Time2

DECLARE @a as datetime
SET @a = '2011-07-25 21:15:31.120'

TRY_CONVERT(time, @a),
TRY_CONVERT(varchar(10), @a,104),
TRY_CONVERT(varchar(10), @a,108),
TRY_CONVERT(numeric(18,6), @a),
TRY_CONVERT(float, @a),
TRY_CONVERT(datetime,TRY_CONVERT(float, @a))

Pre-Denali, conversion of value ‘2010-12-32’ to datetime will raise an error.

SELECT CONVERT(datetime, '2010-12-32')

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

In Denali, with TRY_CONVERT we get NULL. But if we try a conversion that is explicitly not permitted, TRY_CONVERT fails with an error.

SET @a = '<a>1</a>'

Msg 529, Level 16, State 2, Line 3
Explicit conversion from data type xml to int is not allowed.

To see how TRY_CONVERT works with numbers, I will create table, insert some values and try to convert values from the table to a various number data types.

CREATE TABLE #T1 (a varchar(20))
VALUES ('10'),('12.4'),('Value'),('12,6'),('-5'),('3d3'),(CHAR(9))/*Tab*/
,(CHAR(10))/*new line*/,('+'),('-'),('$'),(CHAR(32))/*space*/,('2.31e-3')
TRY_CONVERT(int, a) AS ASInteger,
TRY_CONVERT(numeric(8,2), a) AS ASNumeric,
TRY_CONVERT(float, a) AS ASFloat,
TRY_CONVERT(money, a) AS ASMoney,

As you can see, if we try to convert signs “+” ,“-“ and space to integer we will get 0, but they can’t be converted to numeric data type. We can easily find all integer values in the table.

-- Everything that can be parsed to int
TRY_CONVERT(int,a) IntegerValues
FROM #t1

-- without signs "+","-" and space
TRY_CONVERT(int,a) IntegerValues
FROM #t1
AND a NOT IN ('+','-',CHAR(32))

TRY_CONVERT is excellent for data type conversion and will eliminate a lot of issue. No more need for custom IsInt or IsNumeric function.

Categories: Denali, TSQL