SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction

In SQL Server Denali, there are two new string functions being introduced, namely:

CONCAT()
FORMAT()

Today we will quickly take a look at the CONCAT() function. CONCAT takes a minimum of two arguments to concatenate them, resulting to a single string.

Now let us look at these examples showing how CONCAT() works:

Example 1: CONCAT Function Usage

SELECT CONCAT(1, 2, 3, 4) AS SingleString
SELECT CONCAT('One',1, 1.1, GETDATE()) AS SingleString
SELECT CONCAT('One',2,NULL) AS SingleString
SELECT CONCAT('','','','') AS SingleString
SELECT CONCAT(NULL, NULL) AS SingleString

concat1 SQL SERVER   Denali   String Function   CONCAT()   A Quick Introduction

Now let us observe a few things based on the result above. Earlier when we had to concat strings, we used ‘+’ sign and always CAST/CONVERT any variable to string. It used to give us an error. However, when you look at this new one function, it automatically and implicitly CAST/CONVERT any datatype to integer and then CONCATs them together in a single string. NULL values are automatically converted to empty strings. If you notice that even the datetime fields are automatically converted to the string without any extra operations. Additionally, the return value from the CONCAT string could be of datatype VARCHAR(MAX).

Example 2: Usage of CONCAT with Table

USE [AdventureWorks2008R2]
GO
SELECT CONCAT([AddressID],' ',[AddressLine1],' ',
[AddressLine2],' ',[City],' ',
[StateProvinceID],' ',[PostalCode]) AS Address
FROM [Person].[Address]
GO

In the following example, we see the result of the query listed above:

concat2 SQL SERVER   Denali   String Function   CONCAT()   A Quick Introduction

You can see how neatly and easily the strings are concatenated using this new function. It takes out lots of unnecessary code and makes it much simpler to execute.
concat4 SQL SERVER   Denali   String Function   CONCAT()   A Quick Introduction

Now let us look under the hood in the execution plan. In the execution plan we can see that CONCAT function is a scalar operation. When we look at the scalar operation using Properties, it shows that the CONVERT_IMPLICIT function is automatically called to convert non-nvarchar datatypes columns to NVARCHAR.

concat3 SQL SERVER   Denali   String Function   CONCAT()   A Quick Introduction

Overall, when I have to concat multiple values and data in the future, I am going to use the CONCATE() function.

Watch a quick video relevent to this subject:

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Uncut and Unedited – Interview of Pinal Dave on Book Authoring

Write a book was not an easy task. Publishing two books in a month was even harder task. In Sep-Oct 2011 I have published two books. I was very happy when books were published and I got print copy in my hand.

Lohith N who is finest .net expert and have recently taken my interview on about the subject of book writing. He asked me my experience about book writing and so many other things.He has even taken my earlier interview where video views have crossed more than 1300 views.

In latest episode we discuss on the following things:

  • About his new book
  • His experience as an author
  • Why the name SQL Joes 2 Pros
  • Where can we buy his books
  • Challenges when writing this book

SQL stands for Structured Query Language and to learn SQL one needs Structured Learning. Here is the list of the books I have personally share my passion with and authored myself.

joes2pros4 SQLAuthority News   Uncut and Unedited   Interview of Pinal Dave on Book Authoring | j2pwait SQLAuthority News   Uncut and Unedited   Interview of Pinal Dave on Book Authoring

[Amazon] | [Flipkart] SQL Programming Joes 2 Pros (vol 4)Pinal Dave

[Amazon] | [Flipkart] SQL Wait StatsPinal Dave

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Introduction to expressor 3.4 Lookup Tables

In this blog post, I am going to take a closer look at expressor’s new and extremely versatile implementation of lookup tables, which they are releasing as part of the upcoming expressor 3.4 product release.  As creation and use of the lookup table can be managed completely through simple-to-use graphical interfaces, it is very easy to utilize this feature in expressor data integration applications.  And for developers who want full control over the functionality, an API provides direct access to the table allowing their applications to read, write, update, and delete table content.  Let’s see how this all comes together!

The lookup tables themselves are yet another reusable artifact within expressor Studio.  Consequently, a user’s tables can be easily reused in multiple applications.  Moreover, the scope of the table can be limited to a single ETL dataflow, to all dataflows in a project, or to all projects across all workspaces.  Table scoping is restricted by storing the table’s underlying files within the project’s directory hierarchy.  Global scoping is managed by making the table definition available to your applications and storing the table’s underlying files in an accessible file system location.

lookup 1 SQL SERVER   Introduction to expressor 3.4 Lookup Tables

To create a lookup table, I open a project or library and start the New Lookup Table wizard by clicking the New Lookup Table button on the expressor Studio 3.4 ribbon bar.  I can then enter a name and descriptive content and move to the next step.  I am now ready to describe the structure of the lookup table.  Each of the table’s columns is referred to as an attribute.  I can manually add attributes by clicking the appropriate button in the ribbon bar to open the Add Attribute screen.

lookup 2 SQL SERVER   Introduction to expressor 3.4 Lookup Tables

But a far more efficient procedure is to base the table’s structure on an expressor Composite Type, which is a grouping of attributes that describe the fields in a file record or the columns in a database table.  The Composite Type Assign button at the top of the screen lets users select a type.  Since in most cases a lookup table will mirror the structure of a file or database table, you can see why this approach is so useful.

Once the structure of the table has been defined, I can select attributes as table keys.  In my example, I’ve created a unique key named Place, which is based on the attribute named place, a unique value across all the records to be stored in the table.  I’ve also created a non-unique key based on the attribute party.  My table will allow lookup of a specific US president or all presidents from a selected political party.  You may define as many non-unique keys as your application requires.

The last property I need to specify is the file system location where the database management system that implements the lookup table will store the data.  The File Connection Assign button at the bottom of the screen opens a browser from which I can make this selection.

Encapsulated within the lookup table’s definition is a description of its structure, identification of its key columns, and its location within the computer’s file system.  That is, the table definition is completely self-contained and a simple reference to the table artifact is all that is needed to use the table in an application.

With expressor 3.4’s new coding paradigm that uses a graphical control termed the Rules Editor, accessing a lookup table from within an application is as straight-forward as anything could be.  Lookups against the lookup table are performed within an expressor Transform Operator, so once I position the operator into my dataflow and connect it to the upstream operator and downstream operator, I can open the operator’s Rules Editor and add a Lookup Expression Rule.

lookup 3 SQL SERVER   Introduction to expressor 3.4 Lookup Tables

From the Lookup dropdown control I can select the name of the lookup table, and from the Key dropdown control, select the key column.  As soon as I make this selection, the left and right panels are populated with the names of the incoming and outgoing parameters.  That is, a value for the key column place will be used to select an entry from the lookup table that contains values for lname, fname, and party.  For each incoming key value, one president’s details will be retrieved from the table.

But what happens if I use the non-unique key?

lookup 4 SQL SERVER   Introduction to expressor 3.4 Lookup Tables

Again, the incoming and outgoing parameters appear but now the frame shows an icon that indicates that this rule is an iterative rule.  That is, possibly multiple records will be emitted for each incoming record.  This is absolutely correct as more than one US president have been members of each political party.

With just a few clicks of the mouse, I was able to access the data in a lookup table, select entries based on unique or non-unique keys, and emit one or more records from the Transform Operator.

Of course, it is possible that the lookup table does not contain a matching entry.  In this case, the Lookup Expression Rule gives me several options.

  • I could output a record with nil values for the attributes representing data in the lookup table.
  • I could escalate the decision as to what to do to the Transform Operator, which can be configured to either skip or reject the record, or even abort execution of the application.
  • Or, I could populate the outgoing record and, if desired insert this content into the lookup table.

lookup 5 SQL SERVER   Introduction to expressor 3.4 Lookup TablesI can specify which approach to take in the dropdown control labeled On miss.  Output Nil is the default option and Escalate Error will push the processing decision up to the Transform Operator.  Selecting the Generate Record option displays additional controls that I could use to initialize the outgoing record and enable table writes.

lookup 6 SQL SERVER   Introduction to expressor 3.4 Lookup Tables

Although I won’t discuss it in this blog post, if you really need to manage the contents of the lookup table, expressor 3.4 also includes an API that allows you to update and delete table entries.  With this in mind, your lookup tables can be completely self-contained.  That is, once they are initially populated, their content can remain current and there will be no requirement to periodically recreate the tables.

I am very impressed with the new functionality in expressor 3.4 and suggest you download this latest version of the software when it becomes generally available in mid-September.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – New Functions and Shorthand for CASE Statement

TSQL2sDay SQL SERVER   Denali   New Functions and Shorthand for CASE Statement

This blog post is written in response to the T-SQL Tuesday post of Data Presentation. This is a very interesting subject. I recently started to write about Denali Logical and Comparison functions.

I really enjoyed writing about new functions, but there was one question kept cropping up – is the CASE statement being replaced with this new functions. The answer is NO. New functions that are introduced are just shorthand for the CASE statement, and they are not replacing anything.

1) TRY_PARSE() is not replacing the CASE statement, infect it is not. However, it can be smartly used along with the CASE statement. Here is the example in which it is used along with the CASE statement.

SELECT
CASE WHEN TRY_PARSE('A100.000' AS INT) IS NULL
THEN 'Error In Result'
ELSE 'No Error'--TRY_PARSE('A100.000' AS INT)
END AS ValueInt;

2) IIF() is nothing but a shorthand to CASE statement. Here is a quick example:

SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;

Now, IIF is simulated by CASE statement

SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result;

The matter of the fact is that when you see the execution plan of IIF it also shows that internally it is using the CASE statement only.

3) CHOOSE() is also a shorthand to CASE statement. Here is a quick example:

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

The above statement is re-written using the CASE statement.

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CASE DATEPART(dw, A.ModifiedDate)
WHEN 1 THEN 'WEEKEND'
WHEN 2 THEN 'Weekday'
WHEN 3 THEN 'Weekday'
WHEN 4 THEN 'Weekday'
WHEN 5 THEN 'Weekday'
WHEN 6 THEN 'Weekday'
WHEN 7 THEN 'WEEKEND'
END  WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

Both the above queries are giving us the same result; however, by using CHOOSE function, T-SQL looks much cleaner and easier to read. You can refer my earlier blog post where I have discussed how CHOOSE() uses the CASE statement under the hood.

Summary: Well, the CASE statement is going nowhere; I think the CASE statement is so important in data presentation that it will be always there, and many new commands will show up and they will be using the CASE statement under the hood.

Now, here is a question back to you – Does PIVOT use CASE statement too? Please leave your answer as comments.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali CTP3 – Step by Step Installation Video – 200 Seconds

My recent article on SQL SERVER – Download Denali CTP3 and Denali CTP 3 Product Guide has inspired today’s post. After reading this blog post, I received a few emails and few comments on facebook page that if I can post a video guide to Denali CTP3 installation. Finally I create this video which is about how one can install SQL Server Denali CTP3. There is no audio in this video as the video is very simple and one can understand it quite easily.

Click here to watch the Denali CTP3 Installation Video on YouTube.

Let me know if you like the video I will post few more videos based on your feedback.

Download Denali from here.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DBA Quiz 2011 – All was well few moments before all went wrong – Reasons and Resolutions

disasterwall SQL SERVER   DBA Quiz 2011   All was well few moments before all went wrong   Reasons and Resolutions

My question just got published at DBA Quiz 2011. This question is inspired from a real life incident, which occurred to me a few years ago. That time, I was a DBA myself and then one fine day, everything went south. When we checked the log, all the logs were fine till few minutes before our server started to face the issue. After working for long hours, we fixed the issue. Our CTO had called us to analyze the situation. Instead of blaming anyone, he adorned an extremely positive attitude. He suggested that we all go out and come back with three top best solutions. We all gathered after a week and looked at each other’s solution. We combined that and put them in order for our later reference.

Well, today we are going to do the same task. SQL Server MVP Jacob Sebastian has come up with an SQL Server DBA Quiz where my question has just got published.

A query that was working fine every time suddenly became slow. There has been no change in the workload; no new development changes have been deployed, and there have been no other configuration changes in the system. Everybody runs to the DBA asking for resolution. If you were the DBA, what will be the first thing you would do and why? List the three most important priorities.

There are various awards for the winner, but here is a small effort from me. Irrespective of the whoever wins the SQL Quiz at the end, I will give a copy of my wait stats print book to one person who has attempted to answer the question I have asked. If you are not sure on how to answer this question, here is a quick hint.

Hint: SQL Programming Joes 2 Pros (vol 4) [Amazon] | [Flipkart]- Chapter 13 has few interesting hints.

Please participate in the DBA Quiz Here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – Logical Function – CHOOSE() – A Quick Introduction

In SQL Server Denali, there are two new logical functions being introduced, namely:

IIF()
CHOOSE()

Today we will quickly take a look at the CHOOSE() function. This function is very simple and it returns specified index from a list of values. If Index is numeric, it is converted to integer. On the other hand, if index is greater than the element in the list, it returns NULL.

Now let us look at these examples showing how CHOOSE() works:

Example 1: CHOOSE Usage

SELECT CHOOSE ( 0, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Null;
SELECT CHOOSE ( 1, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_First;
SELECT CHOOSE ( 2, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Second;
SELECT CHOOSE ( 3, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Third;
SELECT CHOOSE ( 4, 'TRUE', 'FALSE', 'Unknown' ) AS Result_NULL;

choose SQL SERVER   Denali   Logical Function   CHOOSE()   A Quick Introduction

You can see that when index is Zero or greater than the elements in the list,  it returns the value as NULL and it does not return error.

Example 2: Usage of CHOOSE when Index is Not Integer

SELECT CHOOSE ( 1.1, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_First;
SELECT CHOOSE ( 2.9, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Second;

choose1 SQL SERVER   Denali   Logical Function   CHOOSE()   A Quick Introduction

You can see that Float value is automatically converted to Integer value and appropriate list value is selected.

Example 3: Usage of CHOOSE with Table

CHOOSE() function can be very useful when it is used in the table as well. In the following example, I am trying to figure out if the day is weekend or weekday using CHOOSE function. There are other ways to figure that out as well, but here in this example I am using that to demonstrate the usage of the CHOOSE function.

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

In the following example, we see the result of the query listed above.

choose2 SQL SERVER   Denali   Logical Function   CHOOSE()   A Quick Introduction

Example 4: Usage of CHOOSE with Table and CASE statement and performance comparison

Let us re-write the above query with CASE statement first.

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CASE DATEPART(dw, A.ModifiedDate)
WHEN 1 THEN 'WEEKEND'
WHEN 2 THEN 'Weekday'
WHEN 3 THEN 'Weekday'
WHEN 4 THEN 'Weekday'
WHEN 5 THEN 'Weekday'
WHEN 6 THEN 'Weekday'
WHEN 7 THEN 'WEEKEND'
END  WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

The following image demonstrates that the results from both the queries are the same.

choose4 SQL SERVER   Denali   Logical Function   CHOOSE()   A Quick Introduction

Now let us compare the execution plans of both the query – the CHOOSE function query and CASE Statement.

choose5 SQL SERVER   Denali   Logical Function   CHOOSE()   A Quick Introduction

When the execution plans are compared, it is very clear that the cost of both queries is the same. The execution plans also look similar. Let us now examine the properties of the Compute Scalar function for both execution plans.

choose6 SQL SERVER   Denali   Logical Function   CHOOSE()   A Quick Introduction

When the properties of the Compute Scalar function are examined, it is clear that both of them are used at the end CASE statement. Just like IIF function, the CHOOSE function is also the shorthand of the CASE statement.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – Logical Function – IIF() – A Quick Introduction

In SQL Server Denali, there are two new logical functions being introduced, namely:

IIF()
CHOOSE()

Today, we will have a look at the IIF() function. This function does not need any introduction as developers have used this function in various languages from ages. This function is shorthand way for writing CASE statement. These functions take three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result.

IIF can be nested as well, which makes its usage very interesting. The limit of nesting of IIF is same as CASE statement, which is capped at 10.

Now, let us look at these examples that show how IIF() works:

Example 1: IIF Usage

SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;

iif1 SQL SERVER   Denali   Logical Function   IIF()   A Quick Introduction

Example 2: IIF simulated by CASE statement

SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result;

iif2 SQL SERVER   Denali   Logical Function   IIF()   A Quick Introduction

Example 3: IIF with NULL

SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result;

iif3 SQL SERVER   Denali   Logical Function   IIF()   A Quick Introduction

Example 4: Nested IIF

SELECT IIF ( -1 < 1, IIF ( 1=1, 'Inner True', 'Inner False' ), 'FALSE' ) AS Result;

iif4 SQL SERVER   Denali   Logical Function   IIF()   A Quick Introduction

Example 5: IIF used along with TRY_PARSE and Table

USE AdventureWorks2008R2;
SELECT SP.[StateProvinceCode],
A.[PostalCode],
IIF(TRY_PARSE(A.[PostalCode] AS INT) IS NULL,
'Canada','United States') Country
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

iif5 SQL SERVER   Denali   Logical Function   IIF()   A Quick Introduction

In above example, we can see how IIF is used instead of CASE statement and the decision is made during run time using TRY_PARSE() function. You can read more about the function TRY_PARSE() here. IIF can be used the same way as CASE statement in SELECT statement.

In the next blog post, we will discuss the difference between CONVERT and PARSE as well as TRY_CONVERT and TRY_PARSE. We will also look at a couple of interesting trivia questions.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

In SQL Server Denali, three new conversion functions have been introduced, namely,

PARSE()
TRY_PARSE()
TRY_CONVERT()

Earlier, we learnt about above three functions in various blog posts. If you have missed it, I suggest that you read all the three following blog posts before continuing this blog post as that will give you better understanding of the subject. I have so far received few questions on this subject; so I have attempted to present this blog post in question and answer format.

Q: What is the difference between PARSE() and TRY_PARSE() functions?
A: While attempting to PARSE any column, if you get one or more incorrect values, PARSE function will throw an error. However, if you use TRY_PARSE function, it will not throw error but will return the result as NULL.

Q: What is the difference between PARSE() and CONVERT() functions?
A: Both are quite different. PARSE function tries to parse a string and returns the value that is parsed from that string, if it can. CONVERT function tries to convert the string to a specified datatype; if it cannot, it will return an error. If you are not sure by what these two statements mean, consider the following example where string ‘100.00’ is attempted to PARSE and CONVERT to INT.

SELECT PARSE('100.000' AS INT) AS PARINT;
SELECT CONVERT(INT, '100.000') AS CONINT;

Let us check the result of the same.

parint SQL SERVER   Denali   Conversion Function   Difference between PARSE(), TRY PARSE(), TRY CONVERT()

conint SQL SERVER   Denali   Conversion Function   Difference between PARSE(), TRY PARSE(), TRY CONVERT()

You can clearly see that CONVERT failed because for it to convert string to a specified datatype, a value that matches the destination datatype is needed, whereas PARSE parsed the string and returned specified datatype-matched string where it can. If you had the specified string value as ‘A100.00′ instead of ‘100.000,’ both of them would have returned as error as they could not understand how this can be converted to INT.

Additionally, PARSE uses CLR datatype under the hood and currently can only convert a string to INT and DATETIME, whereas CONVERT works with any compatible datatype as well as it can be used to format dates.

convertexample SQL SERVER   Denali   Conversion Function   Difference between PARSE(), TRY PARSE(), TRY CONVERT()

Q: I have disabled CLR using sp_configure; how can I still use PARSE function?
A: If the user has disabled the CLR by using sp_configure, but .net framework is installed on the server, the PARSE function continues to work since it is a system function and not a user-defined function.

Q: What is optimal PARSE or CONVERT?
A: They are different and have different usages. Try both; whichever gives you a correct answer is something that you would want to use.

Just for kicks, I ran the following query together and checked their STATISTICS IO and execution plan; I found both them to be equal.

USE AdventureWorks2008R2
GO
SET STATISTICS  IO ON;
SELECT SP.[StateProvinceCode]
,PARSE(A.[PostalCode] AS INT) PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US'
AND LEN(A.[PostalCode]) <=6
GO
SELECT SP.[StateProvinceCode]
,CONVERT(INT,A.[PostalCode]) PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US'
AND LEN(A.[PostalCode]) <=6
GO

The output of STATISTICS IO is as following: In both case, the same amount IO reads are happening.

(8666 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvince'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8666 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvince'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Let us check the execution plan; it looks same as well.

conpar SQL SERVER   Denali   Conversion Function   Difference between PARSE(), TRY PARSE(), TRY CONVERT()

Let me know if you have any other question; I will expand this article appropriately.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – Conversion Function – TRY_CONVERT() – A Quick Introduction

In SQL Server Denali, there are three new conversion functions being introduced, namely:

PARSE()
TRY_PARSE()
TRY_CONVERT()

Today we will quickly take a look at the TRY_CONVERT() function. The TRY_CONVERT() function is very similar to CONVERT function which is avail in SQL Server already. Only difference is that it will attempt to CONVERT the datatype in specified datatype and while doing the same, if it fails (or error occurs) instead of displaying error it will return value NULL. Function CONVERT() is same as in earlier version (as far as I know till CTP3).

Now let us examine these examples showing how TRY_CONVERT() works:

Example1: Converting String to INT

-- No error
SELECT CONVERT(INT, '100') AS ValueInt;
SELECT TRY_CONVERT(INT, '100') AS ValueInt;
-- Error
SELECT CONVERT(INT, 'A100.000') AS ValueInt;
SELECT TRY_CONVERT(INT, 'A100.000') AS ValueInt;

When you try first set where string can be converted to INT, it does not throw an error and it works fine.

tryconvert SQL SERVER   Denali   Conversion Function   TRY CONVERT()   A Quick Introduction

However, in the second case wherein the string has an Alphabetic character, it cannot be converted to INT. In this case, CONVERT will throw an error but TRY_CONVERT will return a NULL result. Therefore, the major difference between CONVERT and TRY_CONVERT is that TRY_CONVERT will TRY to CONVERT and if CONVERT fails it will produce a NULL result instead of an error.

tryconvert1 SQL SERVER   Denali   Conversion Function   TRY CONVERT()   A Quick Introduction

tryconvert2 SQL SERVER   Denali   Conversion Function   TRY CONVERT()   A Quick Introduction

This distinct attribute separating the two functions are very helpful when we are using TRY_PARSE function over table. This is displayed in the examples below:

Example2: Using TRY_CONVERT with CASE statement.

SELECT
CASE WHEN TRY_CONVERT(INT, '100') IS NULL
THEN 'Error In Result'
ELSE 'No Error'
END AS ValueInt;

The example above demonstrates how CASE statement can be used with TRY_PARSE, while avoiding any errors in the statement execution.

tryconvert3 SQL SERVER   Denali   Conversion Function   TRY CONVERT()   A Quick Introduction

In the following example, TRY_PARSE is applied over table and it makes it more meaningful:

Example3: Converting String to INT from Table using TRY_CONVERT

You can clearly see that the PostalCode column in the database is in the format of the nvarchar. We can use CONVERT to convert it to INT.

USE AdventureWorks2008R2
GO
SELECT SP.[StateProvinceCode], A.[PostalCode],
TRY_CONVERT(INT, A.[PostalCode]) PC_TryConvert
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

tryconvert4 SQL SERVER   Denali   Conversion Function   TRY CONVERT()   A Quick Introduction

When attempting to CONVERT a column, you might get one or more incorrect values. If the CONVERT function is used it will throw an error and will not return any results but with TRY_CONVERT it will not throw error but it will return the NULL value. As seen in example, the NULL value can be used along with CASE and desired message can be displayed.

In next blog post, we will discuss difference between CONVERT and PARSE as well TRY_CONVERT and TRY_PARSE. As well look at couple of interesting trivia questions.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)