SQL SERVER – MySQL – How to Concat Strings?

As a regular reader you may know that I now a days work with MySQL and SQL Server both at the same time. Working with two different products have changed quite a few things in how I write code. Here is an example of that – I changed myself about how I CONCAT strings.

concathuman SQL SERVER   MySQL   How to Concat Strings?

Earlier with SQL Server

Earlier I used to use ‘+’ (Plus) operator when I was concatenating strings. Here is the script for the same:

SELECT 'FirstName' + ' Last Name' AS FullName;

Now with SQL Server

SELECT CONCAT('FirstName',' Last Name') AS FullName;

concatsqlserver SQL SERVER   MySQL   How to Concat Strings?

The reason I use the CONCAT function instead of ‘+’ (Plus) operator is because CONCAT functions work in MySQL as well.

concatmysql SQL SERVER   MySQL   How to Concat Strings?

Here is the example how the same scripts work in MySQL without changing any code.

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

SQL SERVER – Building Three-Part Name from OBJECT_ID – Database Name, Schema Name, TableName

Here is the question I recently received based on my earlier blog post – SQL SERVER – Get Schema Name from Object ID using OBJECT_SCHEMA_NAME.

“How I get three part name if the only thing I have is object_id and current database context. What I mean is that, when I query executes, I do not know for which database it is executing as the name of the database is part of the connection string. I only know the name of the object_id. I have already read your earlier article but I need further guidance. Can you help?”

Here is the script which will generate three part name based out of just object_id. For example we have object_id 1154103152 which belongs to AdventureWorks database, Sales SchemaName and SalesOrderDetails table. Here is how you can generate three part namings.

SELECT QUOTENAME(DB_NAME())
+
N'.'+QUOTENAME(OBJECT_SCHEMA_NAME(1154103152))
+
N'.'+QUOTENAME(OBJECT_NAME(1154103152));

The above query will give us following result:

3partname SQL SERVER   Building Three Part Name from OBJECT ID   Database Name, Schema Name, TableName

Instead of passing static variables you can also pass the column containing object_id into the function and get three part name of the objects.

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

SQL SERVER – Implementing IF … THEN in SQL SERVER with CASE Statements

Here is the question I received the other day in email.

“I have business logic in my .net code and we use lots of IF … ELSE logic in our code. I want to move the logic to Stored Procedure. How do I convert the logic of the IF…ELSE to T-SQL. Please help.”

I have previously received this answer few times. As data grows the performance problems grows more as well. Here is the how you can convert the logic of IF…ELSE in to CASE statement of SQL Server.

Here are few of the examples:

Example 1:

If you are logic is as following:

IF -1 < 1 THEN ‘TRUE’
ELSE ‘FALSE’

You can just use CASE statement as follows:

-- SQL Server 2008 and earlier version solution
SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result
GO

-- SQL Server 2012 solution
SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;
GO

If you are interested further about how IIF of SQL Server 2012 works read the blog post which I have written earlier this year .

Well, in our example the condition which we have used is pretty simple but in the real world the logic can very complex. Let us see two different methods of how we an do CASE statement when we have logic based on the column of the table.

Example 2:

If you are logic is as following:

IF BusinessEntityID < 10 THEN FirstName
ELSE IF BusinessEntityID > 10 THEN PersonType
FROM Person.Person p

You can convert the same in the T-SQL as follows:

SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName
WHEN BusinessEntityID > 10 THEN PersonType END AS Col,
BusinessEntityID, Title, PersonType
FROM Person.Person p

However, if your logic is based on multiple column and conditions are complicated, you can follow the example 3.

Example 3:

If you are logic is as following:

IF BusinessEntityID < 10 THEN FirstName
ELSE IF BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType
ELSE IF Title = 'Mr.' THEN 'Mister'
ELSE 'No Idea'
FROM Person.Person p

You can convert the same in the T-SQL as follows:

SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName
WHEN BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType
WHEN Title = 'Mr.' THEN 'Mister'
ELSE 'No Idea' END AS Col,
BusinessEntityID, Title, PersonType
FROM Person.Person p

I hope this solution is good enough to convert the IF…ELSE logic to CASE Statement in SQL Server. Let me know if you need further information about the same.

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

SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 4

This is the fourth post in the series of finding previous row and next row value in SELECT Statement. Read all the blog post before continuing reading this blog post for complete idea.

In the very first part I discussed that performance with the help of CTE is very poor and I encouraged users to use LEAD and LAG function of SQL Server 2012. My friend and SQL Server Expert Szymon Wojcik have written a fantastic post about this subject. I encourage everyone to read that blog post. He has demonstrated that with the help of numbers table we can further improve the performance of the query.

Now let us compare all the various solutions which I have presented in my earlier blog post.

Before continuing with the performance comparison, I suggest you to change the database context to AdventureWorks and also enable STATISTICS IO settings on for performance comparison.

USE AdventureWorks2012
GO
SET STATISTICS IO ON;

Query 1 for SQL Server 2012 and later version

SELECT
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO

Query 2 for SQL Server 2005+ and later version

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
CTE.FirstName,
nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO

Query 3 for SQL Server 2005+ and later version

CREATE TABLE #TempTable (rownum INT, FirstName VARCHAR(256));
INSERT INTO #TempTable (rownum, FirstName)
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

Query 4 for SQL Server 2000+ and later version

SELECT
rownum = IDENTITY(INT, 1,1),
p.FirstName
INTO #TempTable
FROM Person.Person p
ORDER BY p.BusinessEntityID;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

All of the above examples will return the following results as a result.

leadlagfunction1 SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?   Part 4

Now let us compare the performance for each of the queries with the help of message displayed in the result set. 

  Worktable Logical Reads Person Logical Read Total Logical Read
Query 1 0 3820 3820
Query 2 1977606 11460 1989066
Query 3 171 3820 3991
Query 4 216 3820 4036

You can clearly see that Query 1 which is using Lead and Lag functions of SQL Server 2012 has the most optimized performance. This gives you one more reason to upgrade to SQL Server 2012.

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

 

SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 3

Earlier I wrote a blog post SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? and SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 2. In part 2 of the blog post, I wanted to write a solution which works with SQL Server 2000. In the solution I removed CTE but I forgot the detail that I SQL Server 2000 does not support RowNumber function as well. Thanks to smart blog readers who caught the error and immediately pointed that out in the comment area. Thank you so much for it. In this blog post, I will now demonstrate how to come up with the solution for previous row and next row in SQL Server 2000 version.  The question was that, how to get access to Previous Row and Next Row in the SELECT statement.

Let us assume that we have following SQL Query.

USE AdventureWorks2012
GO
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID
GO

leadlagfunction SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?   Part 3

What we want is that in the same SELECT statement the previous row and next row should be listed. Additionally the solution should support SQL Server 2000 and later versions. Here is the solution for the same.

USE AdventureWorks2012
GO
SELECT
rownum = IDENTITY(INT, 1,1),
p.FirstName
INTO #TempTable
FROM Person.Person p
ORDER BY p.BusinessEntityID;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

In the above example we have used Temp Table and with the help of Temp Table we have built our solution, which returns following result.

leadlagfunction1 SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?   Part 3

I hope today I have got the answer correct with the help of Identity Function and Temp Table.

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

SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 2

Earlier I wrote a blog post SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement?. Right after the blog post was published I received an email from SQL Server users who have no access to CTE and wanted me to help him out with the solution as well. Absolutely, Here is the solution for the anyone who is using SQL Server 2005 and does not use CTE.

I strongly suggest you read my earlier blog post before continuing this blog post as they are related to each other. The question was that, how to get access to Previous Row and Next Row in the SELECT statement.

Let us assume that we have following SQL Query.

USE AdventureWorks2012
GO
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID
GO

leadlagfunction SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?   Part 2

What we want is that in the same SELECT statement the previous row and next row should be listed. Additionally the solution should support SQL Server 2005 and later versions. Here is the solution for the same.

CREATE TABLE #TempTable (rownum INT, FirstName VARCHAR(256));
INSERT INTO #TempTable (rownum, FirstName)
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p;
SELECT
prev.FirstName PreviousValue,
TT.FirstName,
nex.FirstName NextValue
FROM #TempTable TT
LEFT JOIN #TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN #TempTable nex ON nex.rownum = TT.rownum + 1;
GO

In the above example we have used Temp Table and with the help of Temp Table we have built our solution, which returns following result.

leadlagfunction1 SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?   Part 2

UPDATE: I had a few inaccuracies in the blog post, which is corrected based on the feedback of users. Thanks!

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

SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement?

The first email I read this morning had only one statement in it, and it gave me an idea to write this blog post.

“How to access Previous Row Value and Next Row Value in SELECT statement?”

Very interesting question indeed. The matter of the fact, I have written about this in my earlier blog Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012. Let us quickly understand it in it with the help of script.

leadlagfunction2 SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?

For example here is the column where we want to get access to the previous row and the next row in SELECT statement.

USE AdventureWorks2012
GO
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID
GO

leadlagfunction SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?

Now let us use the new Analytic Function from SQL Server 2012 to get previous and next row values.

SELECT
LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,
p.FirstName,
LEAD(p.FirstName) OVER (ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO

leadlagfunction1 SQL SERVER   How to Access the Previous Row and Next Row value in SELECT statement?

You can see it is very simple to get Previous and Next value with the help of Lead and Lag Function in SQL Server.

However, if you are using an earlier version of SQL Server which does not support LEAD and LAG function we can do the following:

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
p.FirstName
FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
CTE.FirstName,
nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO

Though the result will be almost same as earlier queries but the performance will be very bad. I suggest you use SQL Server 2012 and the query related to the same.

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

SQL SERVER – A Quick Note on CONCAT_NULL_YIELDS_NULL

Recently one of my friends sent me a SQL script to debug and I noticed that above all of his scripts he was executed following query.

SET CONCAT_NULL_YIELDS_NULL OFF;

This made me curious and I asked him reason why is he executes above script. He answered that when his application have few columns which when he concats return the value as zero because his column contains NULL values. This made me curious as I believe if he has such business needs he should have changed his columns to allow NOT Null. When asked he suggested that he can’t do that as well due to his database structure is following some legacy standard and system.

Well, let us quickly understand his scenario with following small reproduction script.

DECLARE @VAR1 VARCHAR(100)
SET @VAR1 = 'First'
DECLARE @VAR2 VARCHAR(100)
SET @VAR2 = 'Second'
DECLARE @VAR3 VARCHAR(100)
SET @VAR3 = 'Third'
DECLARE @VAR4 VARCHAR(100)
SET @VAR4 = NULL
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT @VAR1 + @VAR2 + @VAR3 + @VAR4 AS ConcatString
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT @VAR1 + @VAR2 + @VAR3 + @VAR4 AS ConcatString

When you execute above scripts you get following resultset:

concatstring1 SQL SERVER   A Quick Note on CONCAT NULL YIELDS NULL

When you run above script it is very clear that he is setting the values CONCAT_NULL_YIELDS_NULL because one of the variable (in his case column) contains a NULL value and he can’t have that NULL value when he is concating the values. As NULL when concats with another value it returns NULL as well. In his case, he wanted to ignore any value when they are NULL and does not convert the value of the entire string to NULL.

When I looked at the script, I felt that he is over engineering everything. When I asked him why he is not using ISNULL function and he was not sure about it. I think it is not a good idea to set the value of CONCAT_NULL_YIELDS_NULL to OFF as that is not going to be supported in future versions of the SQL. If you start writing code and script which is based the settings of CONCAT_NULL_YIELDS_NULL, there is a good chance in future that you will have to re-write every script of your application to accommodate the change. As per the Microsoft Documentation in the future the value of CONCAT_NULL_YIELDS_NULL will be always ON.

It will be a good idea to re-write above script with ISNULL function and let the value of the setting CONCAT_NULL_YIELDS_NULL to be always true.

DECLARE @VAR1 VARCHAR(100)
SET @VAR1 = 'First'
DECLARE @VAR2 VARCHAR(100)
SET @VAR2 = 'Second'
DECLARE @VAR3 VARCHAR(100)
SET @VAR3 = 'Third'
DECLARE @VAR4 VARCHAR(100)
SET @VAR4 = NULL
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT ISNULL(@VAR1,'') + ISNULL(@VAR2,'') + ISNULL(@VAR3,'') + ISNULL(@VAR4,'') AS ConcatString
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT ISNULL(@VAR1,'') + ISNULL(@VAR2,'') + ISNULL(@VAR3,'') + ISNULL(@VAR4,'') AS ConcatString

When you execute above scripts you get following resultset:

concatstring2 SQL SERVER   A Quick Note on CONCAT NULL YIELDS NULL

When you execute above script you will notice that it does not matter what is the settings of the option CONCAT_NULL_YIELDS_NULL  the result set is now predictable and NULL is ignored in resultset.

NOTE: If your business need is such that you always want to return NULL when it is concated with another value, you should not play with settings CONCAT_NULL_YIELDS_NULL  as well should not use ISNULL function.

Click to Download Scripts

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

SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters

Earlier this week, I asked a puzzle about how REPLICATE works with 8000 and over 8000 characters. I strongly suggest to read the original blog post where I have described the problem in detail SQL SERVER Puzzle – REPLICATE over 8000 Characters.

Just quick to summarize the puzzle. Here is the quick recap of the same.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

replicatepuz2 SQL SERVER   Solution to Puzzle   REPLICATE over 8000 Characters

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Answer 1:

The reason for the same is that if the first parameter of the REPLICATE function is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first parameter must be explicitly cast to the appropriate varchar(max) or nvarchar(max).

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

Answer 2:

To return the result as 11000, one has to just CAST or CONVERT the first parameters to VARCHAR(MAX) or NVARCHAR(MAX). Here is the example of the same.

DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),'C'),11000)
SELECT LEN(@ThirdString) LenThirdString;

Now let us see the result set.

replicate8000 SQL SERVER   Solution to Puzzle   REPLICATE over 8000 Characters

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

If you notice that this was not very difficult puzzle but it was interesting for sure. There are so many valid answers that it will be not possible to name every single person. I strongly encourage all of you to go over the original blog post and read all the comments. Though all the comments are very similar there are so many new information there that I will say wealth of information just right there in the comments area.

Click to Download Scripts

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

SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle.

SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.

SELECT 'Ha'+REPLICATE('ha',20)

The script above will return following result:

replicatepuz1 SQL SERVER   Puzzle and Answer   REPLICATE over 8000 Characters

`

You can notice that it has returned a string ha about 20 times after first Ha.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

replicatepuz2 SQL SERVER   Puzzle and Answer   REPLICATE over 8000 Characters

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Here is the second part of the Quiz.

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.

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