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.

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;

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

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

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

About these ads

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:

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.

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

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.

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

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.

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.

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

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

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)