Feeds:
Posts
Comments

Archive for the ‘T SQL’ Category

SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology, tagged on May 15, 2013 | 6 Comments »

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

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

About these ads

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 14, 2013 | 6 Comments »

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)

Read Full Post »

SQL SERVER – Puzzle and Answer – REPLICATE over 8000 Characters

Posted in PostADay, SQL, SQL Authority, SQL Function, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 13, 2013 | 150 Comments »

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)

Read Full Post »

SQL SERVER – Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards

Posted in PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 12, 2013 | 1 Comment »

Have you ever worked with CONCAT_NULL_YIELDS_NULL earlier in your career? If yes, you will find this post very interesting. If you have not worked with this function before I think this post will teach you something very interesting.

Before we start please note that as per SQL Server official documentation CONCAT_NULL_YIELDS_NULL will be always ON in the future so avoid setting it off. What this function essentially does is that when it is on it will return a null value when any other value is connected it with using + operator. If CONCAT_NULL_YIELDS_NULL is set to OFF it will return the original value instead of NULL.

Let us see a simple example of the same.

-- Before SQL Server 2012
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO

However, in SQL Server 2012 the behavior of the ‘+’ operator remains the same but the behavior of function CONCAT is bit different and it totally ignores this value. Here is the example of it.

--- SQL Server 2012 and onwards
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO

I assume this may be intentional as in future the value of CONCAT_NULL_YIELDS_NULL will be always ON. Irespectively I find it very interesting how CONCAT function works with NULLs.

Read more about CONCAT function over here as well you can watch the video about how the concrete function works with SQL Server 2012.

String Function – CONCAT() – A Quick Introduction

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

Read Full Post »

SQL SERVER – Weekly Series – Memory Lane – #028

Posted in Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 11, 2013 | Leave a Comment »

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

UDF – Function to Convert List to Table
Article contains UDF written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR. It will convert the list to table.

Creating Comma Separate Values List from Table – UDF – SP
Script contained in the blog will create common separate values (CSV) or common separate list from tables, convert list to table. Following script is written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR (MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

Examples and Explanation for GOTO
GOTO statement can be used anywhere within a procedure, batch, or function. GOTO can be nested as well. GOTO can be executed by any valid user on SQL SERVER. GOTO can co-exists with other control of flow statements (IF…ELSE, WHILE). GOTO can only go(jump) to label in the same batch, it can not go to label outside of the batch.

Index Optimization CheckList
Index optimization is always interesting subject to me. Every time I receive request to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.

Explanation SQL Command GO
GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

2008

Here are two straight to script blog posts where I explain how to do something quickly and easily.

2009

Find Last Date Time Updated for Any Table
If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys. dm_db_index_usage_stats and easily figure out when the table was updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.

Questions and Answers with Database Administrators
Interesting conversation – I recommend everyone to read this.

Q. According to you what goes into making the best Database Administrator?

A. The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

2010

Get Latest SQL Query for Sessions – DMV
A straight to script blog which is my favorite as well.

Size of Index Table for Each Index – Solution 2
Another blog post where I explain how to find size of index using T-SQL script.

Spatial Database Queries – What About BLOB
In this blog post I explain how to find distance between two cities using Spatial Database.

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
Not everyone knows the difference between ALTER INDEX ALL REBUILD and disabled Clustered index. In this blog post, I have tried to explain the same with example.

2011

Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
One of the most popular blog post because it is the basic requirement of the developer. Developers like to do things their own way on their database. This blog post explains how developers can absolutely create another replica of the database and later do practice over it.

What Kind of Lock WITH (NOLOCK) Hint Takes on Object?
Do we know what kind of lock WITH(NOLOCK) hint takes on object. Answer is Schema Lock. Yes, WITH(NOLOCK) hint takes Schema Lock on the object which is accessed. In this blog post we have working example where I demonstrate the same.

Resource Database ID – 32767
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

2012

In year 2012 in a single week, I had come up with the Quiz based on the Joes 2 Pros Book and here are the five questions asked in the quiz. Just like every other quiz, this quiz also had a video hints. Something very unique and never been attempted.

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

Read Full Post »

« Newer Posts - Older Posts »