SQL SERVER – Simple Puzzle with UNION – Part 2

Yesterday we had very easy kind of Back to Basics Puzzle with UNION and I have received tremendous response to the simple puzzle. Even though there is no giveaway due to sheer interest in the subject, I have received many replies. Due to all the request, here is another back to the basic question with UNION again.

Let us execute following three query one by one. Please make sure to enable Execution Plan in SQL Server Management Studio (SSMS).

Query 1

SELECT 1
UNION ALL
SELECT 2

The query above will return following result

puzzunion 1 SQL SERVER   Simple Puzzle with UNION   Part 2

The query above will return following execution plan

puzzunion 2 SQL SERVER   Simple Puzzle with UNION   Part 2

Query 2

SELECT 1
UNION ALL
SELECT 2
ORDER BY 1

The query above will return following result

puzzunion 1 SQL SERVER   Simple Puzzle with UNION   Part 2

The query above will return following execution plan

puzzunion 3 SQL SERVER   Simple Puzzle with UNION   Part 2

Query 3

SELECT DISTINCT 1
UNION ALL
SELECT DISTINCT 2
ORDER BY 1

The query above will return following result

puzzunion 1 SQL SERVER   Simple Puzzle with UNION   Part 2

The query above will return following execution plan

puzzunion 4 SQL SERVER   Simple Puzzle with UNION   Part 2

Now let us look at all the execution plans together.

puzzunion 5 SQL SERVER   Simple Puzzle with UNION   Part 2

When you look at closely at all the resultset – they all returns the same result. When we see their execution plan they are very different from each other. Now here is the question back to you.

Question: When we add DISTINCT in Query 3 it is technically more work for SQL Server to do than Query 2. However, the execution plan demonstrates that Query 3 is using much lesser resources than Query 2. WHY?

Please leave your answer in the comment section. I will publish all the valid answer in the blog next week with due credit.

Do not miss to checkout the part 1 of this puzzle.

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

SQL SERVER – Simple Puzzle with UNION

It has been a long time since played a simple game on SQLAuthority.com. Let us play a simple game today. It is very simple puzzle but indeed a fun one.

First let us execute following SQL.

Query 1:

SELECT 1
UNION ALL
SELECT 1
ORDER BY 1

It will return following result:

puzzunion1 SQL SERVER   Simple Puzzle with UNION

Now try to execute the following query and guess the result:

Query 2:

SELECT 2
UNION ALL
SELECT 2
ORDER BY 2

When you execute the same it gives error that:

Msg 108, Level 16, State 1, Line 4
The ORDER BY position number 2 is out of range of the number of items in the select list.
Msg 104, Level 16, State 1, Line 4
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

puzzunion2 SQL SERVER   Simple Puzzle with UNION

Now let us execute following query and guess the result:

Query 3:

SELECT 2 AS '2'
UNION ALL
SELECT 2 AS '2'
ORDER BY '2'

Above query will return following result:

puzzunion3 SQL SERVER   Simple Puzzle with UNION

Here is the question back to you – Why does a Query 2 returns error but Query 3 returns result successfully?

Just leave a comment with the answer – I will post the answer with due credit in future blog posts.

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

SQL Contest – Download NuoDB 1.2 to Win 20 Amazon Gift Cards

NuoDb have earlier released NuoDB 1.2 and I have been using it for an entire month and my favorite feature is their support to Stored Procedure. I am a big fan of stored procedures and their advantages. Nod 1.2 has received great response in the industry and it has emerged as a serious competitor to many of the NewSQL databases. To celebrate the success and reception in the industry. The kind folks at NuoDB have announced 20 Amazon Gift Card (each of USD 10).

amazon gift cards SQL Contest   Download NuoDB 1.2 to Win 20 Amazon Gift CardsTo enter in the contest you just have to download NuoDB 1.2. Everybody who downloads NuoDB in the next 36 hours will be eligible in the contest.  At the end of 36th hour, 20 winners will be selected and sent USD 10 Amazon Gift Card. This contest is open everywhere in the world and there is no other restriction.

Here are the details about the latest release. I am pretty excited with their product and all the new features.

SQL Explorer improvements

  • The NuoDB Schema Definition for defining new schemas, tables, columns, and keys via  easy-to-use UI gestures;
  • Multi-line statements are now supported in the query window.

A unique take on stored procedures (preview) that allows users to:

  • Scale-out at high performance; internal testing on DBT-2 resulted in a 3X increase in an already high performance of 1Million+ transactions per second;
  • Run procedures on multiple hosts;
  • Support for high-level languages like Java, .NET and JavaScript.

Enhanced SQL functions to:

  • Aid the user in more easily understanding his/her data. NEED MORE FROM ENGINEERING ON MIN/MAX OR WHATEVER

You can read my earlier article describing the same over here. You can download NuoDB 1.2.

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

SQL – What ACID stands in the Database? – Contest to Win 24 Amazon Gift Cards and Joes 2 Pros 2012 Kit

We love puzzles. One of the brain’s main task is to solve puzzles. Sometime puzzles are very complicated (e.g Solving Rubik Cube or Sodoku)  and sometimes the puzzles are very simple (multiplying 4 by 8 or finding the shortest route while driving). It is always to solve puzzle and it creates an experience which humans are not able to forget easily. The best puzzles are the one where one has to do multiple things to reach to the final goal. Let us do something similar today. We will have a contest where you can participate and win something interesting.

Contest

This contest have two parts.

Question 1: What ACID stands in the Database?

This question seems very easy but here is the twist. Your answer should explain minimum one of the properties of the ACID in detail. If you wish you can explain all the four properties of the ACID but to qualify you need to explain minimum of the one properties.

Question 2: What is the size of the installation file of NuoDB for any specific platform.

You can answer this question following format – NuoDB installation file is of size __ MB for ___ Platform.

Click on the Download the Link and download your installation file for NuoDB. You can post figure out the file size from the properties of the file.

nuodbdownloadlink SQL   What ACID stands in the Database?   Contest to Win 24 Amazon Gift Cards and Joes 2 Pros 2012 Kit

We have exciting content prizes for the winners.

Prizes

1) 24 Amazon Gift Cards of USD 10 for next 24 hours. One card at every hour. (Open anywhere in the world)

amazoncard SQL   What ACID stands in the Database?   Contest to Win 24 Amazon Gift Cards and Joes 2 Pros 2012 Kit

2) One grand winner will get Joes 2 Pros SQL Server 2012 Training Kit worth USD 249. (Open where Amazon ship books).

combo SQL   What ACID stands in the Database?   Contest to Win 24 Amazon Gift Cards and Joes 2 Pros 2012 Kit

Amazon | 1 | 2 | 3 | 4 | 5 

Rules

The contest will be open till July 21, 2013. All the valid comments will be hidden till the result is announced.

The winners will be announced on July 24, 2013.

Hint: Download NuoDB 

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

SQL SERVER – Relationship with Parallelism with Locks and Query Wait – Question for You

Today, I have one very simple question based on following image. A full disclaimer is that I have no idea why it is like that. I tried to reach out to few of my friends who know a lot about SQL Server but no one has any answer.

Here is the question:

If you go to server properties and click on Advanced you will see the following screen. Under the Parallelism section if you noticed there are four options:

  • Cost Threshold for Parallelism
  • Locks
  • Max Degree of Parallelism
  • Query Wait

parallel lock query wait SQL SERVER   Relationship with Parallelism with Locks and Query Wait   Question for You

I can clearly understand why Cost Threshold for Parallelism and Max Degree of Parallelism belongs to Parallelism but I am not sure why we have two other options Locks and Query Wait belongs to Parallelism section. I can see that the options are ordered alphabetically but I do not understand the reason for locks and query wait to list under Parallelism.

Here is the question for you – Why Locks and Query Wait options are listed under Parallelism section in SQL Server Advanced Properties?

Please leave a comment with your explanation. I will publish valid answers on this blog with due credit.

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

 

SQL – Contest to Get The Date – Win USD 50 Amazon Gift Cards and Cool Gift

If you are a regular reader of this blog – you will find no issue at all in resolving this puzzle. This contest is based on my experience with NuoDB.

If you are not familiar with NuoDB, here are few pointers for you.

In today’s contest you have to answer following questions:

Q 1: Precision of NOW()

What is the precision of the NuoDB’s NOW() function, which returns current date time?

Hint: Run following script on NuoDB Console Explorer section:

SELECT NOW() AS CurrentTime
FROM dual;

Here is the image. I have masked the area where the time precision is displayed.

quiz1 SQL   Contest to Get The Date   Win USD 50 Amazon Gift Cards and Cool Gift

Q 2: Executing Date and Time Script

When I execute following script –

SELECT 'today' AS Today, 'tomorrow' AS Tomorrow, 'yesterday' AS Yesterday
FROM dual;

I will get the following result:

 quiz2 SQL   Contest to Get The Date   Win USD 50 Amazon Gift Cards and Cool Gift

NOW – What will be the answer when we execute following script? and WHY?

SELECT CAST('today' AS DATE) AS Today, 
 CAST('tomorrow' AS DATE) AS Tomorrow, 
 CAST('yesterday'AS DATE) AS Yesterday
FROM dual;

quiz3 SQL   Contest to Get The Date   Win USD 50 Amazon Gift Cards and Cool Gift

HINT: Install NuoDB (it takes 90 seconds).

Prizes:

  • 2 Amazon Gifts
  • 2 Limited Edition Hoodies (US resident only)

 SQL   Contest to Get The Date   Win USD 50 Amazon Gift Cards and Cool Gift amazoncard SQL   Contest to Get The Date   Win USD 50 Amazon Gift Cards and Cool Gift

Rules:

  • Please leave an answer in the comments section below.
  • You must answer both the questions together in a single comment.
  • US resident who wants to qualify to win NuoDB apparel please mention your country in the comment.
  • You can resubmit your answer multiple times, the latest entry will be considered valid.
  • Last day to participate in the puzzle is June 24, 2013.
  • All valid answer will be kept hidden till June 24, 2013.
  • The winner will be announced on June 25, 2013.
  • Two Winners will get USD 25 worth Amazon Gift Card. (Total Value = 25 x 2 = 50 USD)
  • The winner will be selected using a random algorithm from all the valid answers.
  • Anybody with a valid email address can take part in the contest.

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

SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards

SQLQueries2012Vol1 9 SQL SERVER   Puzzle #1   Querying Pattern Ranges and Wild CardsNote: Read at the end of the blog post how you can get five Joes 2 Pros Book #1 and a surprise gift.

I have been blogging for almost 7 years and every other day I receive questions about Querying Pattern Ranges. The most common way to solve the problem is to use Wild Cards. However, not everyone knows how to use wild card properly.

SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners Book On Amazon | Book On Flipkart

Learn SQL Server get all the five parts combo kit Kit on Amazon | Kit on Flipkart

Many people know wildcards are great for finding patterns in character data. There are also some special sequences with wildcards that can give you even more power. This series from SQL Queries 2012 Joes 2 Pros® Volume 1 will show you some of these cool tricks.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter2.2Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

Querying Pattern Ranges

The % wildcard character represents any number of characters of any length. Let’s find all first names that end in the letter ‘A’. By using the percentage ‘%’ sign with the letter ‘A’, we achieve this goal using the code sample below:

SELECT *
FROM Employee
WHERE FirstName LIKE '%A'

To find all FirstName values beginning with the letters ‘A’ or ‘B’ we can use two predicates in our WHERE clause, by separating them with the OR statement.

j2pcontest 613 1 SQL SERVER   Puzzle #1   Querying Pattern Ranges and Wild Cards

Finding names beginning with an ‘A’ or ‘B’ is easy and this works fine until we want a larger range of letters as in the example below for ‘A’ thru ‘K’:

SELECT *
FROM Employee
WHERE FirstName LIKE 'A%'
OR FirstName LIKE 'B%'
OR FirstName LIKE 'C%'
OR FirstName LIKE 'D%'
OR FirstName LIKE 'E%'
OR FirstName LIKE 'F%'
OR FirstName LIKE 'G%'
OR FirstName LIKE 'H%'
OR FirstName LIKE 'I%'
OR FirstName LIKE 'J%'
OR FirstName LIKE 'K%'

The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when a query requires a large range of letters, the LIKE operator has an even better option. Since the first letter of the FirstName field can be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘J’ or ‘K’, simply list all these choices inside a set of square brackets followed by the ‘%’ wildcard, as in the example below:

SELECT *
FROM Employee
WHERE FirstName LIKE '[ABCDEFGHIJK]%'

A more elegant example of this technique recognizes that all these letters are in a continuous range, so we really only need to list the first and last letter of the range inside the square brackets, followed by the ‘%’ wildcard allowing for any number of characters after the first letter in the range.

j2pcontest 613 2 SQL SERVER   Puzzle #1   Querying Pattern Ranges and Wild Cards

Note: A predicate that uses a range will not work with the ‘=’ operator (equals sign). It will neither raise an error, nor produce a result set.

--Bad query (will not error or return any records)
 SELECT *
 FROM Employee
 WHERE FirstName = '[A-K]%'

Question: You want to find all first names that start with the letters A-M in your Customer table and end with the letter Z. Which SQL code would you use?

a. SELECT * FROM Customer
WHERE FirstName LIKE 'm%z'

b. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'

c. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'

d. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'

e. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'

f. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'

g. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'

Contest

  • Leave a valid answer before June 18, 2013 in the comment section.
  • 5 winners will be selected from all the valid answers and will receive Joes 2 Pros Book #1.
  • 1 Lucky person will get a surprise gift from Joes 2 Pros.
  • The contest is open for all the countries where Amazon ships the book (USA, UK, Canada, India and many others).

Special Note: Read all the options before you provide valid answer as there is a small trick hidden in answers.

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

SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL

Earlier I have posted a puzzle which received so many valid responses and got a fantastic explanation to the questions as well. I encourage all of you to read the original puzzle here.

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

emptyresult SQL SERVER  Solution   SQL Puzzle of SET ANSI NULL

You can clearly see that in the first case we are getting different results.

Question: Why do Query 1 return results but Query 2 does not return any result?

The answer is very simple and easy. Let us see the answer

Solution:

In case of the Query1
SELECT ‘SQLAuthority’ AS Statement11
WHERE ‘Authority’ IN (‘S’,’Q’, ‘L’, ‘Authority’, NULL);

Explanation:
IN Operator is equivalent to
Condition = ‘S’ OR Condition = ‘Q’ OR Condition = ‘L’ OR Condition = ‘Authority’ OR Condition = NULL

Above query represents in our case as

‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ OR ‘Authority’ = NULL

Returns FALSE OR FALSE OR FALSE OR TRUE OR FALSE so the final result is TRUE.

In this case if ANY of the above condition returns true it the query will return value ‘SQLAuthority’

In case of the Query2
SELECT ‘SQLAuthority’ AS Statement12
WHERE ‘Authority’ NOT IN (‘S’,’Q’, ‘L’, NULL);

Explanation:
NOT IN Operator is equivalent to
Condition != ‘S’ AND Condition != ‘Q’ AND Condition != ‘L’ AND Condition != NULL

Above query represents in our case as

‘Authority’ != ‘S’ AND ‘Authority’ != ‘Q’ AND ‘Authority’ != ‘L’ AND ‘Authority’ != NULL

Returns TRUE AND TRUE AND TRUE AND FALSE so the final result is FALSE.

In this case if ALL of the above condition returns true it the query will return value ‘SQLAuthority’

I guess this solves the puzzle. However do not forget to note that in my query I have ANSI_NULL ON. When ANSI_NULLS is ON, any comparison operation with one of the operands is NULL will evaluate to UNKNOWN. For the result to include a record, all the operands for that record should evaluate to TRUE. We can absolutely get different results if we have ANSI_NULLS OFF.

We will continue the discussion in future blog posts. Additionally, the winners of the contests will be announced in the original blog posts.

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 – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

We all love puzzles and here is interesting puzzle which you can play with me and win Amazon Gift Cards and Bubble Copter R/C. The contest for Amazon Gift Card is open worldwide, however, Bubble Copter winner will be chosen from USA only.

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

emptyresult SQL SERVER   SQL Puzzle of SET ANSI NULL   Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

Question: Why do Query 1 return results but Query 2 does not return any result?

Answer this question in comments area along with the question in Step2.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.

Giveaway:

  • 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 50)
  • One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

wincopteramazon SQL SERVER   SQL Puzzle of SET ANSI NULL   Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

Rules and Conditions:

  • Contest open till May 25 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
    • Answer to Q2:
  • Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
  • The answers will be kept hidden till the winner will be announced to have fair competition.

Click to Download Scripts

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