SQL SERVER – Puzzle – Why Decimal is Rounded Up?

Lots of people know the answer to this puzzle, but I am sure there are many who do not know the answer of the same as well. Here is the puzzle again for those who have vacation for the Holidays!

Here is the first script:

-- Decimal is rounded up
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17
SELECT @MyValue DecResult
GO

decround1 SQL SERVER   Puzzle   Why Decimal is Rounded Up?

Here is the second script:

-- Decimal is not rounded up
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.0000
SELECT @MyValue DecResult
GO

decround2 SQL SERVER   Puzzle   Why Decimal is Rounded Up?

Here is the question – When we execute the first script the decimal’s are rounded up and when we execute the second script the decimal’s are not rounded up. WHY?

Just leave your answer in the comments field. I will select two random winners in the first week of January and will send surprise gift worth USD 29.

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

SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

This error has to be one of the most famous error. I have installed SQL Server quite a many times, but I keep on getting this error once in a while. This is also one of the most searched error online. I have previously blogged about this but today, I planned to create a small video on the same subject. In this video I demonstrate how one can resolve the permissions issue with this error.

ere are few other blog posts related to this error, which I have written earlier.

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

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

quadcompter SQL SERVER   Take the Quiz for a chance to win a Quadcopter DroneIt 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 both the parts the contest. 

Contest Part 1: Brain Teasers

Please execute following script and answer the questions.

embquiz SQL SERVER   Take the Quiz for a chance to win a Quadcopter Drone

-- What will be the output of the following? and Why?
SELECT 28E3
-- What will be the output of the following? and Why?
SELECT 28F3
-- What will be the output of the following? and Why?
SELECT F328

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!

emb33 SQL SERVER   Take the Quiz for a chance to win a Quadcopter Drone

That’s it!

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

Remember:

  • Download RapidSQL from this link.
  • Use comments area right below the blog to take participation in the contest
  • Answer before November 21, 2014 midnight GMT.
  • The winner will be announced on December 8.
  • The winner will be selected randomly from all the valid answers.
  • All the valid answers will be kept hidden till December 24, 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 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.

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

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)

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.

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

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

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

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

quadcompter SQL SERVER   Take the Quiz for a chance to win a Quadcopter Drone   Brain TeasersIt 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

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

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

Hint: SELECT (Answer)

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

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!

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

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

bitwisezero SQL Puzzle   A Quick Fun with Bit Wise Operator

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;

bitwisezero1 SQL Puzzle   A Quick Fun with Bit Wise Operator

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?

system shutdown 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?”

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

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.

3celebs SQL Contest   Hint for Identify the Database 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.

dl nuodb SQL Contest   Hint for Identify the Database Celebrity

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

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

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

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

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

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

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

amazon gift cards SQL Contest   Identify the Database Celebrity   Win USD 100 Amazon Gift Card

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)