SQL SERVER – A Question on SSMS Prompt about Connect to Server

Today is Sunday, so letting us have some fun thing to discuss and think about. If you have not read my earlier blog post SQL SERVER – Connecting to Azure Storage with SSMS. I have discussed about how to connect to the SQL Azure storage via SSMS. Here is a follow up question I have on the same subject.

Question: When we connect to object explorer via the main toolbar menu, we get a prompt where we can connect to Azure Storage but when we open first object explorer why do we see the option to Azure Storage.

To better explain my question, please see the image attached herewith.

Do you have any answer for the same? If yes, please share.

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

About these ads

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.

Remember:

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

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

SELECT 1
UNION ALL
SELECT 2

The query above will return following result

The query above will return following execution plan

Query 2

SELECT 1
UNION ALL
SELECT 2
ORDER BY 1

The query above will return following result

The query above will return following execution plan

Query 3

SELECT DISTINCT 1
UNION ALL
SELECT DISTINCT 2
ORDER BY 1

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)