SQL SERVER – Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076

It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight.

The question is in the form of video which is displayed here. Watch the video and answer correct question in the comment on the this blog. You have 48 hours from the time of publishing this blog to answer it correctly.

If you due to any reason, you can’t watch the video, here is the image taken from the video. I strongly suggest you watch this video to get a clear idea of the puzzle.

In the above image, when I execute first select statement, it returns me answer as 0 but when I execute the second function, it gives me answer as 1. What is passed in the second select statement in ISNUMERIC function which is producing answer as 1, even though it is not visible.

Please leave your answer as a comment to this blog post.

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

About these ads

SQL SERVER – Script to Find First Day of Current Month

Earlier I wrote a blog post about SQL SERVER – Query to Find First and Last Day of Current Month and it is a very popular post. In this post, I convert the datetime to Varchar and later on use it. However, SQL Expert Michael Usov has made a good point suggesting that it is not always a good idea to convert datetime to any other date format as it is quite possible that we may need it the value in the datetime format for other operation. He has suggested a very quick solution where we can get the first day of the current month with or without time value and keep them with datatype datetime.

Here is the simple script for the same.

-- first day of month
-- with time zeroed out
SELECT CAST(DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATE)) AS DATETIME)
-- with time as it was
SELECT DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATETIME))

Here is the resultset:

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

MySQL – UDF – Validate Integer Function

There is a default function in SQL Server called ISNUMERIC to determine if the string is numeric. In SQL Server ISNUMERIC returns 1 for non numerials also. Ex ISNUMERIC (‘,’) returns 1. To avoid this you can write a user defined function as shown over here. In MySQL, there is no default function like ISNUMERIC as in SQL Server. You need to create a similar user defined function as shown below.

DELIMITER $$
CREATE FUNCTION 'udf_IsNumeric'(number_in VARCHAR(100)) RETURNS BIT
BEGIN
DECLARE
Ret BIT;
IF number_in NOT regexp '[^0123456789]' THEN
SET
Ret:= 1;
ELSE
SET
Ret:= 0;
END IF;
RETURN Ret;
END

The above function uses Regular expression. The expression regexp ‘[^0123456789]’ will find out if the string has at least one character which is not a digit. The expression not regexp ‘[^0123456789]’ will just negate that condition so that you get the string which has all characters as a digit.

You can test this using the following example.

SELECT '999' TestValue,udf_IsNumeric('999') NumericTest;
SELECT 'abc' TestValue,udf_IsNumeric('abc') NumericTest;
SELECT '9+9' TestValue,udf_IsNumeric('9+9') NumericTest;
SELECT '$9.9' TestValue,udf_IsNumeric('$9.9') NumericTest;
SELECT 'SQLAuthority' TestValue,udf_IsNumeric('SQLAuthority') NumericTest;

The result is

TestValue NumericTest
 ——— ———–
 999 1
TestValue NumericTest
 ——— ———–
 abc 0
TestValue NumericTest
 ——— ———–
 9+9 0
TestValue NumericTest
 ——— ———–
 $9.9 0
TestValue NumericTest
 ———— ———–
 SQLAuthority 0

You can find more examples about Regular expressions in MySQL over here.

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

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)

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)