SQL SERVER – Take the Quiz for a chance to win a Quadcopter Drone – Brain Teasers

It has been a long time since we ran quiz. So let us get ready for a quiz. The quiz has two parts. You have to get both the parts correct to win Quadcopter with Camera (we will call it drone). We will be giving away a total of 2 Quadcopters.

The quiz is extremely easy and I will ship the Drone anywhere in the world where Amazon will ship it.

Let us jump directly to the quiz. Please complete all the three questions of the contest. 

Contest Part 1: Brain Teasers

There are two questions for you in this part of the contest.

Question: There are two 7s. How will you write select statement with a single operator that returns single 7?

Hint: SELECT 7(Answer)7

Question: Write down the shortest code that produces 1 without using any numbers in the select statement?

Hint: SELECT (Answer)

Contest Part 2: Download and Activate Rapid SQL

Question: Download and Activate Rapid SQL.

Hint: You have to download and activate Rapid SQL. If you do not activate Rapid SQL, you will be disqualified for the contest. Why take risk, let us start!

That’s it!

Just answer above questions in the following comments area, in following format.


  • Use comments area right below the blog to take participation in the contest
  • Answer before June 5, 2014 midnight GMT.
  • The winner will be announced on June 8.
  • The winner will be selected randomly from all the valid answers.
  • All the valid answers will be kept hidden till June 5, 2014.
  • There will be a total of two winners.
  • The contest is open for any country of the world where Amazon ships products.

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

SQL Puzzle – A Quick Fun with Bit Wise Operator

It has been a long time since we have puzzle on this blog so let us have a quick puzzle this Friday. It is based on Bit Wise Operator and very simple if you know those operators.

We will guess the output of the SQL script.

For example, here is the select statement:

SELECT 10 & 10

Upon executing it will return us answer as a 10.

However, when I execute following two queries, both of the queries give an answer as Zero.

SELECT 10 & 0;
SELECT 1 & 100;

Here is the question for you – why is it giving answers as zero? Please leave your answer in a comment below.

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

SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?

Recently I received a very interesting question- I have no idea about its answer so I will request your help to answer this question.


“I am using a Powershell script to shutdown SQL Services. Here is the command I use.

Stop-Services 'mysqlserver' - Force

When I have to start the services, I use

Start-Services 'mysqlserver'

If you notice when I start this service, I do not have to use Force flag/command. If I do not use -Force flag when I try to stop services it gives me error that.

Stop-Service : Cannot stop service ‘SQL Server (MSSQLSERVER) (mssqlserver)’ because it has dependent services. It can only be stopped if the Force flag is set.

Now, here is the question to you – When I use -Force flag does it execute SHUTDOWN process internally or it does SHUTDOWN WITH NOWAIT?”

This is a very interesting question. I honestly have no answer to this question. From the syntax we can guess that it might be doing Shutdown with Nowait but again, guessing is not a good idea. In the past, there have been enough time I have been proven wrong when I have guessed anything. There is always a chance when what we see is not what happens internally.

Here is few additional information. When we execute SHUTDOWN command in T-SQL it waits for all the currently running Transact-SQL statements or stored procedures to finish. However, when we use syntax SHUTDOWN WITH NOWAIT it shuts down server immediately without even doing Checkpoint. This increases more work for the server when it restarts.

So here is the question again – Stop-Services ‘mysqlserver in powershell is equivalent to Shutdown or Shutdown With Nowait in T-SQL?

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

SQL Contest – Hint for Identify the Database Celebrity

Earlier week I have posted a SQL Contest about Identifing the Database Celebrity over here Identify the Database Celebrity – Win USD 100 Amazon Gift Card. We have got fantastic response to the blog post however, there are quite many readers have requested Hint to the contest. In the world of Internet and Google Search, it is honestly not difficult to find the answers of the quiz. However, it seems there are many friends who wants me to provide some hint.

Here is the hint for Part 1: Identify Database Celebrity

Write click on the image of the celebrity and save it to local disk. The name of the image should give some hint about the Celebrity.

Here is the hint for Part 2: Identify NuoDB Sample Database Names

The original quiz says Mac and Linux but you can download the NuoDB for Windows and install the sample database Hockey as well. It also have same Sample Tables. Do not wait for the contest to end tomorrow. Participate today and you may be the winner of USD 100 Amazon Card.  Download NuoDB Here.

Note: Please leave comment in original blog post over here.

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

SQL Contest – Identify the Database Celebrity – Win USD 100 Amazon Gift Card

Regular readers of this blog are familiar with NuoDB and their generous offers for SQLAuthority.com readers. I was just talking to them earlier and together we have come up with a very interesting contest for all of us.

This contest has two parts. Part 1 Identify Database Personality and in Part 2 You have to identify the size of the NuoDB installer. You have to answer both the questions to eligible to enter in the contest.

Part 1: Identify Database Celebrity

Personality 1 – He is known as the father of Relational Database

Personality 2 – He has received the Turing Award “for seminal contributions to database and transaction processing research and technical leadership in system implementation.”

Personality 3 – He invented the NuoDB Emergent Architecture, and developed the initial implementation of the product.

Part 2: Identify NuoDB Samples Database Names

Download NuoDB for Linux or Mac and install the software. Run the Quick Start sample app. Tell us what the four tables in the sample Hockey database are called. Download NuoDB Here.

Contest Details

The contest will be open till September 25th. The winner will be announced before September 30th. Remember there are only two gift cards each worth USD 100. All the valid entries will be hidden till September 25th. You must give correct answer to both the questions.

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

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


The query above will return following result

The query above will return following execution plan

Query 2


The query above will return following result

The query above will return following execution plan

Query 3


The query above will return following result

The query above will return following execution plan

Now let us look at all the execution plans together.

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:


It will return following result:

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

Query 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.

Now let us execute following query and guess the result:

Query 3:


Above query will return following result:

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).

To 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.


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.

We have exciting content prizes for the winners.


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

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

Amazon | 1 | 2 | 3 | 4 | 5 


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

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)