SQLAuthority News – Why I am Going to Attend #SQLPASS Summit 2012 – Seattle

I am going to Seattle I once again attend SQLPASS this year. This will be my fourth SQLPASS. Lots of people ask me why I am going to SQLPASS every year. Well there are so many different reasons for that. I go to SQLPASS because – I love it! 

Here are few of the reasons I go to SQLPASS.

  • Meet friends whom I have never met before
  • Meet community at large – it is fun to hang around with like minded people
  • Meet Rick Morelan – my book co-author and friend
  • Attend various SQL Parties – there are so many parties around – see the list below
  • Explore various new tools from various third party vendors
  • Meet fellow Chapter Leaders and Regional Mentors

And of course attend SQL Server Learning Sessions from industry known experts.

The three-day event will be marked by a lot of learning, sharing, and networking, which will help me increase both my knowledge and contacts. PASS Summit provides me a golden opportunity to build my network as well as to identify and meet potential customers or employees. If I am a consultant or vendor who is looking for better career opportunities, PASS Summit is the perfect platform to meet and show my skills to my new potential customers and employers. Further, breakfasts, lunches, and evening receptions, which are included with registration, are meant to provide more and more networking opportunities.

At PASS Summit, I gain not only new ideas but also inspire myself from top professionals and experts. Learning new things about SQL Server, interacting with different kinds of professionals, and sharing issues and solutions will definitely improve my understanding and turn me into a better SQL Server professional who can leverage and optimize SQL Server to improve business.

I am going – are you joining?

Note: This is re-blogged with modification from my 2 years old blog posts on a similar subject.

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

SQLAuthority News – 6th Anniversary and 50 Million Views and Over 2300 Blog Posts – Thank You Thank You

Six years ago, I started this SQLAuthority.com blog. There are so many things I want to say today – it is very very emotional. Instead of writing long I am including few images and cartoons.

Last month we have also reached 50 Million Total Views on this blog. Here is the screen captured at that time.

Click Image to Enlarge

In 6 years there are total 2192 days (including 2 leap year day) and my total blog post count is 2300. That means I have been blogging more than 1 blog post every day. Here is the quick glance to all the numbers.

Here you can find the list of all the 2300 blog posts.

I am very glad to see my many of the friends stay in USA, India, United Kingdom, Canada and Australia in that order. You can see the geographic distribution of the support I receive on the blog from worldwide.

On this day I would like to call out one 2 individuals who contribute equally or more in my success. When I started this blog 6 years ago, I was walking alone. After 2 years my wife Nupur joined my journey and 3 years later my daughter Shaivi joined the journey. Here is the example of the common conversation among us almost every day –

Shaivi: Daddy, play catch-catch.
Nupur: Shaivi, daddy will play with you once he finishes tomorrow’s blog.
Shaivi: Daddy, Finish Blog. Okey. I play catch-catch (alone).

SQLAuthority Family

Well, thank you very much! We all love you!

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

SQL SERVER – Free Print Book on SQL Server Joes 2 Pros Kit

Rick Morelan and I were discussing earlier this month that what we can give back to the community. We believe our books are very much successful and very well received by the community. The five books are a journey from novice to expert. The books have changed many lives and helped many get jobs as well pass the SQL Certifications. Rick is from Seattle, USA and I am from Bangalore, India. There are 12 hours difference between us. We try to do weekly meeting to catch up on various personal and SQL related topics. Here is one of our recent conversations.

Rick and Pinal

Rick and Pinal

Pinal: Good Morning Rick!

Rick: Good Morning…err… Good Evening to you – Pinal!

Pinal: Hey Rick, did you read the recent email which I sent you – one of our reader is thanking us for writing Joes 2 Pros series. He wants to dedicate his success to us. Can you believe it?

Rick: Yeah, he is very kind but did you tell him that it is all because of his hard work on learning subject and we have very little contribution in his success.

Pinal: Absolutely, I told him the same – I said we just wrote the book but it is he who learned from it and proved himself in his job. It is all him! We were just igniters.

Rick: Good response.

Pinal: Hey Rick! Are we doing enough for the community? What can we do more?

Rick: Hmmm… Let us do something more.

Pinal: Remember once we discussed the idea of if anyone who buys our Joes 2 Pros Combo Kit in the next 2 weeks – we will send them SQL Wait Stats for free. What do you say?

Rick: I agree! Great Idea! Let us do it.

Free Giveaway

Well Rick and I liked the idea of doing more. We have decided to give away free SQL Server Wait Stats books to everybody who will purchase Joes 2 Pros Combo Kit between today (Oct 15, 2012) and Oct 26, 2012. This is not a contest or a lucky winner opportunity. Everybody who participates will qualify for it.

Combo Availability

USA – Amazon

India – Flipkart | Indiaplaza

Note1: USA kit contains FREE 5 DVDs. India Kit does not contain 5 DVDs due to legal issues.
Note2: Indian Kit is priced at special Indian Economic Price.

Qualify for Free Giveaway

  • You must have purchased our Joes 2 Pros Combo Kit of 5 books between Oct 15, 2012 and Oct 26, 2012.
  • Purchase before Oct 15, 2012 and after Oct 26, 2012 will not qualify for this giveaway.
  • Send your original receipt (email, order details) to following addresses: “books@SQLAuthority.com;info@Joes2Pros.com” with the subject line “Joes 2 Pros Kit Promotion Free Offer”. Do not change the subject line or your email may be missed. 
  • Clearly mention your shipping address with phone number and pin/zip code.
  • Send your receipt before Oct 30, 2012. We will not entertain any conversation after Oct 30, 2012 cut off date.
  • The Free books will be sent to USA and India address only.
  • Availability USA – Amazon | India – Flipkart | Indiaplaza

Do leave a comment if you have question or comment.

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

SQL SERVER – 3 Online SQL Courses at Pluralsight and Free Learning Resources

Usain Bolt is an inspiration for all. He broke his own record multiple times because he wanted to do better! Read more about him on wikipedia. He is great and indeed fastest man on the planet.

Usain Bolt - World’s Fastest Man

Usain Bolt – World’s Fastest Man

“Can you teach me SQL Server Performance Tuning?”

This is one of the most popular questions which I receive all the time. The answer is YES.

I would love to do performance tuning training for anyone, anywhere.  It is my favorite thing to do, and it is my favorite thing to train others in.  If possible, I would love to do training 24 hours a day, 7 days a week, 365 days a year.  To me, it doesn’t feel like a job.

Of course, as much as I would love to do performance tuning 24/7/365, obviously I am just one human being and can only be in one place at one time.  It is also very difficult to train more than one person at a time, and it is difficult to train two or more people at a time, especially when the two people are at different levels.  I am also limited by geography.  I live in India, and adjust to my own time zone.  Trying to teach a live course from India to someone whose time zone is 12 or more hours off of mine is very difficult.  If I am trying to teach at 2 am, I am sure I am not at my best!

There was only one solution to scale – Online Trainings. I have built 3 different courses on SQL Server Performance Tuning with Pluralsight. Now I have no problem – I am 100% scalable and available 24/7 and 365. You can make me say the same things again and again till you find it right. I am in your mobile, PC as well as on XBOX. This is why I am such a big fan of online courses.  I have recorded many performance tuning classes and you can easily access them online, at your own time.  And don’t think that just because these aren’t live classes you won’t be able to get any feedback from me.  I encourage all my viewers to go ahead and ask me questions by e-mail, Twitter, Facebook, or whatever way you can get a hold of me.

Here are details of three of my courses with Pluralsight. I suggest you go over the description of the course. As an author of the course, I have few FREE codes for watching the free courses. Please leave a comment with your valid email address, I will send a few of them to random winners.

SQL Server Performance: Introduction to Query Tuning 

SQL Server performance tuning is an art to master – for developers and DBAs alike. This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems. This includes an introduction to understanding execution plans inside SQL Server.

In this almost four hour course we cover following important concepts.

  • Introduction 10:22
  • Execution Plan Basics 45:59
  • Essential Indexing Techniques 20:19
  • Query Design for Performance 50:16
  • Performance Tuning Tools 01:15:14
  • Tips and Tricks 25:53
  • Checklist: Performance Tuning 07:13

The duration of each module is mentioned besides the name of the module.

SQL Server Performance: Indexing Basics

This course teaches you how to master the art of performance tuning SQL Server by better understanding indexes.

In this almost two hour course we cover following important concepts.

  • Introduction 02:03
  • Fundamentals of Indexing 22:21
  • Practical Indexing Implementation Techniques 37:25
  • Index Maintenance 16:33
  • Introduction to ColumnstoreIndex 08:06
  • Indexing Practical Performance Tips and Tricks 24:56
  • Checklist : Index and Performance 07:29

The duration of each module is mentioned besides the name of the module.

SQL Server Questions and Answers

This course is designed to help you better understand how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works.

In this almost 2 hours and 15 minutes course we cover following important concepts.

  • Introduction 00:54
  • Retrieving IDENTITY value using @@IDENTITY 08:38
  • Concepts Related to Identity Values 04:15
  • Difference between WHERE and HAVING 05:52
  • Order in WHERE clause 07:29
  • Concepts Around Temporary Tables and Table Variables 09:03
  • Are stored procedures pre-compiled? 05:09
  • UNIQUE INDEX and NULLs problem 06:40
  • Locks and Duration of Transactions 15:11
  • Nested Transaction and Rollback 09:16
  • Understanding Date/Time Datatypes 07:40
  • Differences between VARCHAR and NVARCHAR datatypes 06:38
  • Precedence of DENY and GRANT security permissions 05:29
  • Identify Blocking Process 06:37
  • NULLS usage with Dynamic SQL 08:03
  • Appendix Tips and Tricks with Tools 20:44

The duration of each module is mentioned besides the name of the module.

SQL in Sixty Seconds

You will have to login and to get subscribed to the courses to view them. Here are my free video learning resources SQL in Sixty Seconds. These are 60 second video which I have built on various subjects related to SQL Server. Do let me know what you think about them?

Here are three of my latest videos:

You can watch and learn at your own pace.  Then you can easily ask me any questions you have.  E-mail is easiest, but for really tough questions I’m willing to talk on Skype, Gtalk, or even Facebook chat.  Please do watch and then talk with me, I am always available on the internet!

Here is the video of the world’s fastest man.Usain St. Leo Bolt inspires us that we all do better than best. We can go the next level of our own record. We all can improve if we have a will and dedication.  Watch the video from 5:00 mark.


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

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

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)?


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 :






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)

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

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)?


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.


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

SQL SERVER – Answer – Value of Identity Column after TRUNCATE command

Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time. Here is the scenario to set up the puzzle.

  • Create Table with Seed Identity = 11
  • Insert Value and Check Seed (it will be 11)
  • Reseed it to 1
  • Insert Value and Check Seed (it will be 2)
  • TRUNCATE Table
  • Insert Value and Check Seed (it will be 11)

Let us see the T-SQL Script for the same.

USE [TempDB]
-- Create Table
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(11,1) NOT NULL,
[var] [nchar](10) NULL
-- Build sample data
VALUES ('val')
-- Select Data
FROM [TestTable]
-- Reseed to 1
-- Build sample data
VALUES ('val')
-- Select Data
FROM [TestTable]
-- Truncate table
-- Build sample data
VALUES ('val')
-- Select Data
FROM [TestTable]
-- Question for you Here
-- Clean up
DROP TABLE [TestTable]

Now let us see the output of three of the select statements.

1) First Select after create table

2) Second Select after reseed table

3) Third Select after truncate table

The reason is simple: If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

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




SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

Place: Any Developer Shop

Scenario: A developer wants to drop a column from a table

Time: Any Day – usually right before developer wants to go home

The developer rushes to the manager and following conversation begins:

Developer: I want to drop  a column from one of the tables.

Manager: Sure, just document it where all the places it is used in our application and come back to me.

Developer: We only use stored procedures.

Manager: Sure, then documented how many stored procedures are there which are using your column and justify the modification. I will approve it once I see the documentation.

Developer back to the desk looking at hundreds of stored procedures in SSMS thinking how to find which stored procedure may be using his column. Suddenly he remembers a bookmark which he has saved earlier which had T-SQL Script to do so. Here quickly opened it and run the code.

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'

Above T-SQL Script will search in the stored procedure text and return the name of the stored procedure if it will find the value specified in the WHERE condition. He was happy with his discovery and immediately created the list of the stored procedures and next action items as asked by the manager. He sent the list to the manager right after 10 minutes of his discussion with the manager. He rushed to manager to office to inform his promptness and realized that the manager had left for the day just few moments before.

Moral of the story: Work life balanced can be maintained if we work smart!

Let us see above T-SQL Script in action. Let us assume that in AdventureWorks2012 database we want to find the BusinessEntityID column in all the stored procedure. We can use run following T-SQL code in SSMS Query Editor and find the name of all the stored procedure.

USE AdventureWorks2012
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'BusinessEntityID' + '%'

Above T-SQL script will give results containing the name of the stored procedure and stored procedure text along with it.

While we are discussing this subject here are a couple of other additional related blog post which may interesting.

A question to you: Is there any better way to find column used in a stored procedure? Please leave a comment with your solution. I will post the same in this blog with due credit.

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

SQL SERVER – Follow up – Usage of $rowguid and $IDENTITY

The most common question I often receive is why do I blog? The answer is even simpler – I blog because I get an extremely constructive comment and conversation from people like DHall and Kumar Harsh. Earlier this week, I shared a conversation between Madhivanan and myself regarding how to find out if a table uses ROWGUID or not? I encourage all of you to read the conversation here: SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables.

In simple words the conversation between Madhivanan and myself brought out a simple query which returns the values of the UNIQUEIDENTIFIER  without knowing the name of the column. David Hall wrote few excellent comments as a follow up and every SQL Enthusiast must read them first, second and third. David is always with positive energy, he first of all shows the limitation of my solution here and here which he follows up with his own solution here. As he said his solution is also not perfect but it indeed leaves learning bites for all of us – worth reading if you are interested in unorthodox solutions.

Kumar Harsh suggested that one can also find Identity Column used in the table very similar way using $IDENTITY. Here is how one can do the same.

data VARCHAR(60) )
INSERT INTO @t (data) SELECT 'test'
INSERT INTO @t (data) SELECT 'test1'

There are alternate ways also to find an identity column in the database as well. Following query will give a list of all column names with their corresponding tablename.

SCHEMA_NAME(so.schema_id) SchemaName,
so.name TableName,
sc.name ColumnName
FROM sys.objects so
INNER JOIN sys.columns sc
AND sc.is_identity = 1

Let me know if you use any alternate method related to identity, I would like to know what you do and how you do when you have to deal with Identity Column.

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

SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement

For the last few weeks, I have been doing Friday Puzzles and I am really loving it. Yesterday I received a very interesting question by Navneet Chaurasia on Facebook Page. He was asked this question in one of the interview questions for job. Please read the original thread for a complete idea of the conversation. I am presenting the same question here.


Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.

Here is the quick setup script for the puzzle.

USE tempdb
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
SELECT 2, 'male'
SELECT 3, 'male'
FROM SimpleTable

The above query will return following result set.

The puzzle was to write a single update column which will generate following result set.

There are multiple answers to this simple puzzle. Let me show you three different ways. I am assuming that the column will have either value ‘male’ or ‘female’ only.

Method 1: Using CASE Statement

I believe this is going to be the most popular solution as we are all familiar with CASE Statement.

UPDATE SimpleTable
SET Gender = CASE Gender WHEN 'male' THEN 'female' ELSE 'male' END
FROM SimpleTable

Method 2: Using REPLACE  Function

I totally understand it is the not cleanest solution but it will for sure work in giving situation.

UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
FROM SimpleTable

Method 3: Using IIF in SQL Server 2012

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

UPDATE SimpleTable
SET Gender = IIF(Gender = 'male', 'female', 'male')
FROM SimpleTable

You can read my article series on SQL Server 2012 various functions over here.

Let us clean up.

DROP TABLE SimpleTable

Question to you:

I came up with three simple tricks where there is a single UPDATE statement which swaps the values in the column. Do you know any other simple trick? If yes, please post here in the comments. I will pick two random winners from all the valid answers. Winners will get 1) Print Copy of SQL Server Interview Questions and Answers 2) Free Learning Code for Online Video Courses

I will announce the winners on coming Monday.

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