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:

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.

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)

About these ads

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:

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)

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)

SQL SERVER – How to use xp_sscanf in Real World Scenario?

I need your help. I recently came across extended stored procedure xp_sscanf. After reading a lot about it and searching online, I could not figure out how and where in real world, I will use this function.

Microsoft documentations suggest that this extended stored procedure reads data from the string into the argument locations specified by each format argument. I still do not get it. I know it is very similar to C function but again I am not sure where in the real world I will use this function. Here is the demonstration of how this function works.

Following example is an enhanced version of the example provided on MSDN.

DECLARE @valueb VARCHAR(20), @filename VARCHAR(20), @message VARCHAR(20)
EXEC xp_sscanf 'sync -b123 -fauthors10.tmp -rrandom', 'sync -b%s -f%s -r%s',
@valueb OUTPUT, @filename OUTPUT, @message OUTPUT
SELECT @valueb, @filename, @message

The above query will return us following result:

-------------------- -------------------- --------------------
123                  authors10.tmp        random

Here is the result set.

You can see how xp_sscanf retrieves the parameters from the string and returns as an output. However, I am still not sure where I will use this feature in real world scenarios. Any insight and or guidance will be helpful.

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

SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

It has been a long time since we have played a puzzle over this blog. This Monday, let us play a quick puzzle.

SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.

SELECT 'Ha'+REPLICATE('ha',20)

The script above will return following result:

`

You can notice that it has returned a string ha about 20 times after first Ha.

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:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Here is the second part of the Quiz.

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.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.

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

SQL SERVER – Caption the Cartoon Contest – Last 2 Days

Developer’s life is very interesting, we often want to start my day early at a job so we can go home early. However, the day never comes as the life of the developer is always about working late hours. If the developer goes to the office early – there are good chances that his co-workers will come late. Additionally, I am confident that there will be always something urgent for developers or DBA to solve right at the time they are ready to go home. This is the life of the developers! 

Here is the interesting story of a DBA who was about to go to the home. He had to take his girlfriend to a movie and dinner in 30 minutes. However, his manager asks him to fix the performance related issues with their production server. In normal case, he had only two choices a) Job or b) Girlfriend. Well, our super hero DBA decided to use efficient tools and improve the performance of the production server in merely 30 minutes. When he was done, his manager was absolutely surprised by his efficiency and accuracy of the work. He asked him following question -

Here is the contest – you need to guess what was the answer of our Super Hero DBA. If you guess the answer correct you may win Star Wars R2-D2 Inflatable Remote Controlled device.

Additionally, if you Download DB Optimizer before Dec 8, 2012 – you will be eligible for USD 25 Amazon Gift Card (there are total 10 such awards).

Please do not leave comments in this thread – to participate in the contest – please leave a comment here in the original contest page.

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

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

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)

SQL SERVER – Recover the Accidentally Renamed Table

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)