Feeds:
Posts
Comments

Archive for the ‘SQL Puzzle’ Category

SQL Contest – Win USD 300 Worth Gift – Cartoon Contest is Back

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, SQLServer, T SQL, Technology on November 29, 2012 | 86 Comments »

There are two excellent contests and we have lots of winning to do this year end.

1) Win USD 25 Amazon Gift Cards (10 Units)

This is very simple, you just have to download SQL Server DB Optimizer. That’s it!

There are only two conditions:

  1. You must have a valid email address. As USD 25 Amazon Gift Card will be sent to the same address.
  2. Download DB Optimizer between today and Dec 8, 2012.

Link to Download DB Optimizer.

Every day one winner will be notified about their winning USD 25 Amazon Gift Cards for next 10 days.

2) Win Star Wars R2-D2 Inflatable R/C

This the coolest thing to win. I personally want one but as I am running a contest, I can’t  participate.

You get this cool Remote Controlled Device – you just have to answer following cartoon contest. Read the complete story and think what will be the answer provided by the smart employee.

There are only two conditions:

  1. Leave your answer in the comment area of this blog post (every comment will be hidden till Dec 8, 2012).
  2. Please leave your answer in the comment area between today and Dec 8, 2012.

Remember you can participate as many times as you want. Make sure that your answer is correct and creative. The most creative answer will be selected. The decision of contest owner will be final. We may have runner’s up prices but for the moment let us try to win R2-D2. Here is the cool video of R2D2.

Now here is the cartoon story, please follow the story and complete the very last cartoon template. Your answer should be correct and should be creative. However, the ideal answer will not be longer than one or two sentences.

Hint: (Hint)

Well, Leave your answer in the comment area of this blog post. If you do not win R2D2, trust me there are chances you may win a surprise gift from me. Remember your answer should be correct and should be creative. However, the ideal answer will not be longer than one or two sentences.

Last day to participate in both of the contest is Dec 8, 2012. We will announce the winner in the week of December 10.

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

About these ads

Read Full Post »

SQL SERVER – Recover the Accidentally Renamed Table

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 8, 2012 | 15 Comments »

I have no answer to following question. I saw a desperate email marked as urgent delivered in my mailbox.

“I accidentally renamed table in my SSMS. I was scrolling very fast and I made mistakes. It was either because I double clicked or clicked on F2 (shortcut key for renaming). However, I have made the mistake and now I have no idea how to fix this.

I am in big trouble. Help me get my original tablename.”

I have seen many similar scenarios in my life and they give me a very good opportunity to preach wisdom but when the house is burning, we cannot talk about how we should have conserved the water earlier. The goal at that point is to put off the fire as fast as we can. I decided to answer this email with my best knowledge.

If you have renamed the table, I think you pretty much is out of luck. Here are few things which you can do which can give you idea about what your tablename can be if you are lucky.

Method 1: (Not Recommended but try your luck)
Check your naming convention of your system. I have often seen that many organizations name their index as IX_TableName_Colms or name their keys as FK_TableName1_TableName2_Cols. If your organization is following the same you can get the name from your table, you may refer your keys. Again, note that this is quite possible that your tablename was already renamed and your keys were not updated. This can easily lead you to select incorrect name. I think follow this if you are confident or move to the next method.

Method 2: (Not Recommended but try your luck)
This method is also based on your orgs naming convention. If you use the name of the table in any columnname (some organizations use tablename in their incremental identity column name), you can get that name from there.

Method 3: (Not Recommended but try your luck)
If you know where your table was used in your stored procedures, you can script your stored procedure and find the name of the table back.

Method 4: (Try your luck)
All the best organizations first create a data model of the schema and there is good chance that this table is used there, you should take your chances and refer original document. If your organization is good at managing docs or source code, you will get the name of the table back for sure.

Method 5: (It WORKS but try on a development server)
There is no sure way to get you the name of the table which you accidentally renamed however, there is one way which will work for sure. You need to take your latest full backup and restore it on your development server (remember not on production or where you have renamed this column). Now restore latest differential file of the full backup. Now restore all the log files one by one making sure that you are restoring before the point of time of you renamed the tablename. Now go to explore and this will give you the name of the table which you have renamed. If you are confident that the same table existed with the same name when the last full backup was made, you do not have to go to all the steps. You can just get the name of the table directly from last backup’s restore. Read the article about Backup Timeline.

Method 6: Interesting Solution by Muhammad Imran
I suggest you to go ahead and read it yourself.

Wisdom:
How can I miss to preach wisdom when I get the opportunity to do so? Here are a few points to remember.

  • Use a different account to explore production environment. Do not use the same account which have all the rights and permissions all the time. Use the account which has read only permissions if there are no modification required.
  • Use policy based management to prevent changes which are accidental. If there was policy of valid names, the accidental change of the table was not possible unless it was intentional delibarate changes.
  • Have a proper auditing of the system in place.
  • You can use DDL triggers but be careful with its usage (get it reviewed properly first).
  • (Add your suggestion here)

I guess Method 5 will work all the time (using point in time restore). Everything else is chance of luck and if you are lucky are bad – you will get further incorrect name.

Now go back and read the first line of this blog. Out of five method four methods are just lucky guesses. The method 5 will work but again it is a lengthy process if the size of the database is huge or if you do not have full backup. Did I miss anything obvious? Please leave a comment and I will publish your answer with due credit.

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

Read Full Post »

SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged on October 2, 2012 | 16 Comments »

Earlier on this blog we had asked two puzzles. The response from all of you is nothing but Amazing. I have received 350+ responses. Many are valid and many were indeed something I had not thought about it.

I strongly suggest you read all the puzzles and their answers here - trust me if you start reading the comments you will not stop till you read every single comment. Seriously trust me on it. Personally I have learned a lot from it.

Let us recap the puzzles here quickly.

Puzzle 1:

Why following code when executed in SSMS displays result as a * (Star)?

SELECT CAST(634 AS VARCHAR(2))

Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

Bonus Q:

How many different Operating System (OS) NuoDB support?

As I mentioned earlier the participation was nothing but Amazing. I will write about the winners and the best answers in short time. Meanwhile I will give to the point answers to above puzzles.

Solution 1:

When you convert character or binary expressions (char, nchar, nvarchar, varchar,binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

Reference of the text and table from MSDN.

Solution 2:

The shortest code to produce answer 1 :

SELECT EXP($)

or

SELECT COS($)

or

SELECT DAY($)

When SELECT $ it gives us the result as 0.00 and the EXP of the same is 1. I believe it is pretty neat. There were plenty other answers but this was the shortest. Another shorter answer would be PRINT EXP($) but no one has proposed that as in original Question I have explicitly mentioned SELECT in the original question.

Bonus Answer:

5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS Reference

Please do read every single comment here. Do leave a comment which one do you think is the best comment out of all the comments. Meanwhile if there is a better solution and I have missed it do let me know as we still have time to correct it. I will be selecting the winner before the weekend as I am going through each and every of 350 comment.

I will be selecting the best comments along with the winning comment. If our selection matches – one of you may still win something cool. 

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

Read Full Post »

SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged on September 20, 2012 | 364 Comments »

We all love brain teasers and interesting puzzles. Today I decided to come up with 2 interesting puzzles and winner of the contest will get USD 50 worth Amazon Gift Card. The puzzles are sponsored by NuoDB. Additionally, The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.

Puzzle 1:

Why following code when executed in SSMS displays result as a * (Star)?

SELECT CAST(634 AS VARCHAR(2))

Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

Bonus Q:

How many different Operating System (OS) NuoDB support?
Click here HINT

If you can solve above puzzles you will be eligible for winning USD 50 Amazon Gift Card. However, you can always enroll yourself for following Bonus Prizes where if you have good chance of winning USD 10 Amazon Gift Card (if you are first 25 individual in specific time).

Bonus Prizes:

The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.

Rules:

  • Please leave an answer in the comments section below.
  • You can resubmit your answer multiple times, the latest entry will be considered valid.
  • The winner will be announced on 1st October.
  • Last day to participate in the puzzle is September 28th, 2012.
  • All valid answer will be kept hidden till September 28th, 2012.
  • Only One Winner will get USD 50 worth Amazon Gift Card.
  • The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
  • The winner will be selected using random algorithm.

UPDATE: The winner of the contest is Jaime Mtnz Lafargue (@jaimeml)

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

Read Full Post »

SQL SERVER – Understanding XML – Contest Win Joes 2 Pros Combo (USD 198) – Day 5 of 5

Posted in Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 10, 2012 | 12 Comments »

August 2011 we ran a contest where every day we give away one book for an entire month. The contest had extreme success. Lots of people participated and lots of give away. I have received lots of questions if we are doing something similar this month. Absolutely, instead of running a contest a month long we are doing something more interesting. We are giving away USD 198 worth gift every day for this week. We are giving away Joes 2 Pros 5 Volumes (BOOK) SQL 2008 Development Certification Training Kit every day. One copy in India and One in USA. Total 2 of the giveaway (worth USD 198). All the gifts are sponsored from the Koenig Training Solution and Joes 2 Pros.

The books are available here Amazon | Flipkart 

How to Win:

  • Read the Question
  • Read the Hints
  • Answer the Quiz in Contact Form in following format
    • Question
    • Answer
    • Name of the country (The contest is open for USA and India residents only)
  • 2 Winners will be randomly selected announced on August 20th.

Question of the Day:

Is following XML a well formed XML Document?

<?xml version=”1.0″?>
<address>
<firstname>Pinal</firstname>
<lastname>Dave</lastname>
<title>Founder</title>
<company>SQLAuthority.com</company>
</address>

a) Yes
b) No
c) I do not know

Query Hints:

BIG HINT POST

A common observation by people seeing an XML file for the first time is that it looks like just a bunch of data inside a text file. XML files are text-based documents, which makes them easy to read.  All of the data is literally spelled out in the document and relies on a just a few characters (<, >, =) to convey relationships and structure of the data.  XML files can be used by any commonly available text editor, like Notepad.

Much like a book’s Table of Contents, your first glance at well-formed XML will tell you the subject matter of the data and its general structure. Hints appearing within the data help you to quickly identify the main theme (similar to book’s subject), its headers (similar to chapter titles or sections of a book), data elements (similar to a book’s characters or chief topics), and so forth. We’ll learn to recognize and use the structural “hints,” which are XML’s markup components (e.g., XML tags, root elements).

The XML Raw and Auto modes are great for displaying data as all attributes or all elements – but not both at once. If you want your XML stream to have some of its data shown in attributes and some shown as elements, then you can use the XML Path mode. If you are using an XML Path stream, then by default all values will be shown as elements. However, it is possible to pick one or more elements to be shown with an attribute(s) as well.

Additional Hints:

I have previously discussed various concepts from SQL Server Joes 2 Pros Volume 5.

Next Step:

Answer the Quiz in Contact Form in following format

  • Question -
  • Answer
  • Name of the country (The contest is open for USA and India)

Bonus Winner

Leave a comment with your favorite article from the “additional hints” section and you may be eligible for surprise gift. There is no country restriction for this Bonus Contest. Do mention why you liked it any particular blog post and I will announce the winner of the same along with the main contest.

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

Read Full Post »

« Newer Posts - Older Posts »