SQLAuthority News – New Book Released – SQL Server Interview Questions And Answers

Two days ago, on birthday of my blog – I asked simple question – Guess! What is in this box?

I have received lots of interesting comments on the blog about what is in it. Many of you got it absolutely incorrect and many got it close to the right answer but no one got it 100% correct. Well, no issue at all, I am going to give away the price to whoever has the closest answer first in personal email.

Here is the answer to the question about what is in the box? Here it is – the box has my new book. In fact, I should say our new book as I co-authored this book with my very good friend Vinod Kumar. We had real blast writing this book together and had lots of interesting conversation when we were writing this book. This book has one simple goal – “master the basics.”

This book is not only for people who are preparing for interview. This book is for every one who wants to revisit the basics and wants to prepare themselves to the technology. One always needs to have practical knowledge to do their duty efficiently. This book talks about more than basics. There are multiple ways to present learning – either we can create simple book or make it interesting. We have decided the learning should be interactive and have opted for Interview Questions and Answer format.

Here is quick interview which we have done together.

Details of the books are here

The core concept of this book will continue to evolve over time. I am sure many of you will come along with us on this journey and submit your suggestions to us to make this book a key reference for anybody who wants to start with SQL server. Today we want to acknowledge the fact that you will help us keep this book alive forever with the latest updates. We want to thank everyone who participates in this journey with us.

You can get the books from [Amazon] | [Flipkart].

Read Vinod‘s blog post. Do not forget to wish him happy birthday as today is his birthday and also book release day – two reason to wish him congratulations.

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

SQL SERVER – expressor Studio 3.4 Rules Editor – ETL Graphical Coding Tool

New in the expressor Studio 3.4 release is the rules editor.  This graphical coding tool replaces the transform editor of earlier versions.  The rules editor works in concert with the newly introduced attribute propagation functionality to minimize the amount of data mapping and coding you need to provide.  The expressor folks are telling me that in a future release we will be able to save and reuse rules, which will make everyone’s  application development tasks even simpler and less prone to errors.

So what’s attribute propagation?  expressor’s starting point observation is that in any transformation most values are either copied from the input record to the output record without any changes or dropped from the output record.  What attribute propagation does is transparently perform these operations without a requirement for coding.  Consequently, within an operator performing a transformation, your only responsibility is to manipulate the values that do require modification.  And this is where the rules editor becomes involved.

Let’s consider an example.  A company wants to give any employee whose tenure with the company started before January 1, 1995 a 5% salary increase.  The application will read a list of employees from a SQL Server database table, determine if the employee’s hire date was before January 1, 1995, and if appropriate calculate a new salary.

Of course expressor offers many ways to develop this application, but I’ll just highlight one approach as it’s the rules editor that I want to discuss.

With expressor, this application can be developed using three operators: a SQL Query operator to select the qualified employees from the SQL Server database table, a Transform operator to carry out the salary calculation, and a Write Table operator to write the modified record back to the database table.  The SQL Query operator executes a SELECT statement that limits the result set to only those employees who qualify for the raise.

SELECT * FROM employees WHERE HIRE_DATE < CONVERT(VARCHAR(23), ‘1995-Jan-01′ , 121)

To continue developing the application, I open the rules editor of the Transform operator.

In the left-hand panel is a listing of the attributes in each incoming record.  The names of these attributes are identical to the table column names, although expressor allows you to easily rename if desired.

The right-hand panel shows the effect of attribute propagation.  That is, each incoming attribute has been transferred to the outgoing record.  The right-facing arrow before each attribute name indicates that the attribute will be automatically initialized and that no coding is required.

To alter the value assigned to the outgoing record’s SALARY attribute, start by using the mouse to drag a connection between the input and output attributes.  An expression rule is displayed.  The default code simply transfers the incoming value to the output.  Also note that the symbol before the output attribute’s name has changed from the right-facing arrow to a diamond, indicating that this attribute is initialized through code.

All I need to do to complete the application is modify the default code, applying the salary increase and, since all salaries are stored as whole dollars, apply a rounding, which I can easily do by clicking the Math button in the Edit tab of the ribbon bar and selecting round from the drop down list of functions.

But what happens when my processing logic is more complex, for example, I only want to give the raise to employees whose commission percentage is smaller than 0.35%.  Then I replace the expression rule with a function rule, where I have full access to the underlying code and I can use the filter helper function to enrich my logic.

In this example, I demonstrated the use of an expression rule and a function rule in the Transform operator.  These rule types are also available in the Join operator.  Within the Transform operator you can also work with a lookup expression rule and a lookup function rule.  These are the rules you use to access the lookup table artifact, another new feature of expressor Studio 3.4.  And in the Aggregate operator you can use an aggregate expression rule, which allows you to select an aggregating function from a drop down control, or an aggregate function rule that allows you to completely manage the processing logic.

While the example developed in this article was quite simple, I think you will agree that attribute propagation and the new rules editor provide a high level of support when your applications require coding.  I continue to be intrigued by the many innovations expressor is putting into their desktop ETL tool.  You can download their tool by following this link.

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

SQLAuthority News – Various Ways to Stay in Touch with SQLAuthority.com – Best Practices

Social Media is growing and quite commonly we reach to the point where we have confusion about the various aspects of the same. I have written a previous article on this subject SQLAuthority News – Social Media Confusion – Twitter, FaceBook, LinkedIn and Me. I am present and active at so many spots that many wonder on how to approach me. I have decided to create this blog post, which will serve as a quick guide for others regarding how to stay in touch with SQLAuthority.com

My Personal Coordinates

Blog Coordinates

My Preference for Facebook

I am very active on Facebook. I think the Facebook page is great way to stay connected with everyone. I check my Facebook page once in every 2 hours during the day. I want to make sure that no comments left on the Facebook are missed. I often post or blog various events related to the SQL Server. I think Facebook comments give us a structured communication medium. There are so many topics that we discuss on Facebook.

For example,

What are we going to learn on today?
What is going on in the SQL Server World around the globe?
Where I am I speaking next?
Where can you find more details about various SQL Servers?

I always announce all my upcoming news first on my Facebook page. I consider every fan and friend on the Facebook page as very special. Earlier, we had done a special exclusive competition on Facebook page, where we had gave away a lot of gifts.

Office Hours

Every week, I observe office hours on my Facebook page, where I am available to answer any SQL Server-related question. We actively communicate during this hour. The best part is that there are so much conversation going on at the same time, and the learning experience goes to next level ‑ so many questions and so many real answers in 60 minutes.

My Preference for Twitter

I currently follow many people on Twitter, and I enjoy reading the information shared on twitter. However, Twitter can be considered like a river; so, I often miss lots of things. If you want to draw my attention to something, then please include @pinaldave or #SQLAuthority somewhere in the email; this way, I will get an email of the same in my mailbox, and I will surely read the comments.

I often post my new activities and interesting finds on my Twitter. I do not consider Twitter as a marketing channel; so I always make sure that I write all of my tweets myself. If you are following me on Twitter, then only tweet you will which are automated contains information regarding my blog and link to my blog.

You can always ask me any question on Twitter, and I always make sure that I respond to it. I often RT tweets, which I find interesting in my river of tweets. If your tweet is during the time when I have not keep my Twitter page open, then I may miss your tweet. This does not mean that your tweet was not interesting and I ignored it. If you want me to read your tweet, then you can always mention my name or send me an email directly.

I open Twitter in the morning and evening for couple of hours. During the work hours, I usually keep off Twitter  as it can be a distraction.

My Preference for LinkedIn

LinkedIn is my professional network, and I often participate in questions and answers as well in the groups. I do not post personal posts in this network and keep the communication totally work related and professional. If you have any question regarding the blog, I suggest you go with the Facebook page or contact page on the SQLAuthority blog.

My Preference for Email

No matter how much ever we grow our social media network, Email still seems to be the most preferred method. I get more than 300 emails every day on various subjects. This 300+ does not include any work email, personal email, or emails from my close friends. These emails are from various people who have questions for SQL Server or technology in general. I always make sure that I answer any email in 24 hours.

I have created my own deadlines to answer emails. Let me just quickly share with you.

  • Family, Friends and Work: 2 Hours
  • Questions related to Books: 8 Hours
  • Questions related to Blog: 8 Hours
  • Other: 24 Hours

If you do not get answer from me in 24 hours, which means your post has either hit my spam folder or just got lost in cyberspace. When your first email is delivered to spam your subsequent emails may be delivered to spam as well. I suggest you try alternative method to contact me in this case. I suggest facebook page as next method to communicate with me.

While asking question through Email

Sometime I get questions that have very little information. If you have any question related to the SQL Server, I suggest you include as much as information you can instead of just sending me an email suggesting ‘help me’. Here is the list of the few things that I would like to see in any email query.

  • SQL Server Version
  • Screenshot of the error
  • Issue you are facing
  • Expected result
  • Current result
  • Recent changes in server/configuration or anything else

Instant Communication / Messengers

Many have figured out my messenger id by trying my username at various communicators. I often get pinged asking for help. I use the messenger mainly for friends and family. I always politely answer each of the messenger pop-ups and forward them to any of the abovementioned communication media. It will be of immense help if you email me when you have an urgent question. I assure you that I will do my best. I have a family, job and other priorities. All the social media communication channels give me time to answer the questions when I am free; however, instant messages can create interruption.

Stay updated

There are cases when you just wanted to stay updated with what is happening with SQL Server and SQLAuthority.


If you are using the RSS reader, I suggest that you subscribe my Feed in your RSS reader. Most of the RSS readers provide facilities to reply right back to the blog author or go to comments page.


If you do not prefer the RSS reader and prefer to go our good old email way, then you can subscribe to my newsletter over here. This will ensure that the emails directly reach your mailbox. You can hit reply and reach me ‑ write away!


If all of these sound too complicated, then just get in touch with me any way you prefer. I am here to help you always!

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

SQLAuthority News – Tomorrow Online Session – Ancient Trade of Performance Tuning – Index, Beyond Index and No Index

Today in few hours I am going to present on my very favorite subject of performance tuning.

You can read more about this sessions over here.

This presentation is based on the famous book ‘The Art of War’ written in sixth century BC by Sun Tzu. Index is usually a favorite tool of many when it is about performance tuning. However, Index is not everything. Performance tuning is much very deep subject and one needs to understand various aspect of the performance tuning. In today’s session I will cover performance tuning beyond indexes. I have created some real interesting demos.

Sessions Details

Title: Ancient Trade of Performance Tuning – Index, Beyond Index and No Index
Timing: September 29, 2011 11:45am-12:45pm IST
SQL Server Indexes are double edge sword. If used properly it gives tremendous performance improvement of the server. However, there is a dark side of the Index as well, which one needs to learn before using them to improve performance of the query. In this session we will go over various concepts of the Indexes which are not common but extremely important.
Location: Register for Virtual Tech Days


During my session I will ask a simple question. I will give away 5 copies of my SQL Server Wait Stats books to five random person who will answer it correctly. If you have already have this book, I strongly suggest you attend this session as this session will take the performance tuning concepts to next level. I will make sure that I autograph and send this copies to your way.

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

SQLAuthority News – Online Session – Ancient Trade of Performance Tuning – Index, Beyond Index and No Index

Performance Tuning has been my favorite subject always. I love this subject the most. I personally have enjoyed every aspect of performance tuning. Quite often I have seen that when it is about performance, people end up talking about Indexes. Index for sure can help performance, but it is like secret weapon and it must be used carefully as the same thing can be dangerous. I have personally attended many sessions that are related to Indexes as well as how to identify the correct index and remove useless indexes. I always wanted Indexing presentation to bring much more than these usual topics.

Session Inspiration

Driven with coming up something different around indexes, I created this presentation. This presentation is based on the famous book ‘The Art of War’ written in sixth century BC by Sun Tzu. This book is composed of 13 chapters, each of which is devoted to one aspect of warfare, and it is said to be the definitive work on military strategies and tactics of its time, and it is still read for its military insights. I am heavily inspired by this book, and I have always treated performance tuning as a war.

Here are the three subjects which I will be covering in my online presentation.

How to defeat an opponent

  • Using Weapon
  • Carry a convenient weapon
  • Use anything as weapon
  • Use Counter Intelligence
  • Use spy network
  • Use statistical data points
  • The Lords of War
  • Extra-ordinary intellect
  • Supreme heroic ability

I have created three demos to support the above three sections. I am very sure that this is one of the most interesting and inspiring sessions for performance tuning. Here are the details for the session.

Sessions Details

Title: Ancient Trade of Performance Tuning – Index, Beyond Index and No Index
Timing: September 29, 2011 11:45am-12:45pm IST
SQL Server Indexes are like a double-sided sword. If used properly it gives tremendous performance improvement of the server. However, there is a dark side of the Index as well, which one needs to learn before using them to improve the performance of the query. In this session, we will go over various concepts of the Indexes, which are not common but extremely important.
Location: Register for Virtual Tech Days


During my session, I will ask a simple question. I will give away 5 copies of my SQL Server Wait Stats books to five random people who will answer it correctly. If you already have this book, I strongly suggest you attend this session as this session will take the performance tuning concepts to next level. I will make sure that I personally autograph and send these copies to your way.

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

SQL SERVER – Use INSERT INTO … SELECT instead of Cursor

This blog post is written in response to the post showing some of the worst practices of past. Well, just like last month’s theme, everybody learns by doing it one step at a time. In my case, I started my career as a network engineer and had no database knowledge during that time. I can still remember my old code which became quite a laughingstock when it was sent for a code review. This story is indeed interesting, so instead of writing shortly, I am going to write today in detail.

It happened about 8 years ago when I was working as Network Engineer in United States. I was responsible for a large data center and we had more than 40 servers under my watch. In our SQL Server database we had one table for the inventory of all the servers. On that table we used to keep a lot of details of the server. To keep it simple, I will say that we have only two columns in that table: ServerID and ServerName. I was asked to write a script which was intended to retrieve all the data from this table and insert them into the new table that was created by the database administrator. I thought I could handle SQL Server script well enough because I learned SQL a bit when I was younger. I ended up writing a script where I wrote cursor to SELECT single row from the table and insert into new table. Well, this went very fine. The script was taking a while to perform its tasks; nevertheless, it worked. I used this script for a short while.

Here is the script that I wrote. I am using sample and simple database for example. Please note that this is not a good practice.

-- Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, 'First Server'
SELECT 2, 'Second Server'
SELECT 3, 'Third Server'
-- Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
-- Insert Logic
@Flag = COUNT(*) FROM ServerTable
WHILE(@Flag > 0)
NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
WHERE ServerID = @Flag
SET @Flag = @Flag - 1
ServerID, ServerName
FROM NewServerTable
-- Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

This script worked very well till we had changed in company policy. We then had a newly- appointed CTO (which I was about to become 3 years after this incidence), who introduced the requirement of Code Reviewing of all both old codes and new ongoing codes. When my code went to a code review, my Team Leader really laughed at my code. So he sent me new piece of the code which was much shorter and more efficient. The code which he proposed was as follows. I honestly say this was much better than my code.

-- Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, 'First Server'
SELECT 2, 'Second Server'
SELECT 3, 'Third Server'
-- Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
-- Insert Logic
NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
SELECT ServerID, ServerName
FROM NewServerTable
-- Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

If you’ve noticed, the new insert code is just reduced to two lines only. Today you might think it is a very simple code, but for me it was a very new thing during those times. Now comes the best part of this whole story.

My team leader, who was very supportive and extremely positive person, asked me to demonstrate this whole incidence on our next team meeting. Honestly, I did not feel bad about accepting my mistake and learning new things. During the team meeting, I first accepted the fact that I was wrong with my code and thanked the Team Leader for giving me the opportunity to improve and develop myself. Because of his encouragement to accept my own mistake and improve from that incident, today I have reached this place.

Please get rid of cursors and use INSERT INTO…SELECT or SELECT INTO logic.

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

SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters

This blog post is written in response to the T-SQL Tuesday hosted by Amit Banerjee. I know Amit personally and very big fan of his community activities. I read his blog, read his comments, follow his tweets, and the most importantly reach out to him when I feel like talking SQL.

Amit has selected a very interesting subject – Best Practices. When I read the subject, I can directly relate this subject to my real world interactions. I have seen so many developers and DBAs making fatal mistakes in the early days of their career. Most of the time, they get another chance because they are new to the job or that particular task, which they are doing for the first time. However, such excuses cannot be made for experts and senior professionals. They are expected perform and carry out the right practices always.

Instead of writing best practices, I am going to write about few of the worst practices which I have quite often seen in the real world. Some of them are really bad that I often wonder how those who still use them were able to continue on the job so far. Let us go over few of the worst practices I have observed in the industry.

My log file is growing too big – I truncated the log without taking backup.

This has to be the numero uno of worst practices. Every time I come across such a statement, I almost miss a heartbeat. Before I continue, let me confess that at the beginning of the career, I have practiced the same. After a while, I had learned it the hard way. This is never a good practice; truncating the log file is not an option. The reason why I rate this as the worst practice: this one mistake can make database impossible to recover. When someone truncates the log file without taking backup, there is no chance of recovery.

Here are a couple of articles which I have written on how to prevent log file from growing too big.

How to Stop Growing Log File Too Big

Log File Growing for Model Database – model Database Log File Grew Too Big

master Database Log File Grew Too Big

I shrink my database daily to regain the space.

This is one of the popular worst practices. I have seen administrators shrinking the database at the end of the day to gain the space only to lose it the very next day. Shrinking is a VERY BAD operation. It increases fragmentation, reduces the performance, and wastes the resources. I strongly advise not to do it.

Here are few articles I had earlier written on this subject.

Shrinking Database is Bad – Increases Fragmentation – Reduces Performance

SHRINKDATABASE For Every Database in the SQL Server

Shrinking NDF and MDF Files – Readers’ Opinion

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Clustered Index makes the table to sort every time. I do not have clustered index on any table.

For an OLTP system, Index is very important and clustered index is the most important index (in my opinion). Clustered index forces order to the table and removes the ‘forwarding records’ problem from the database. Personally, I consider tables without clustered index performance to be unacceptable?. In my OLTP system, I always recommend that all tables should have clustered index.

Here is a quick script that can help in identifying a table without clustered index in the database.

Find Table without Clustered Index – Find Table with no Primary Key

Clustered Index on Separate Drive From Table Location

Observation – Effect of Clustered Index over Nonclustered Index

TempDB is not important; so I will keep it on my slow drive.

Personally, I have tremendous respect for TempDB. Even though it resets every time when the server restarts, this is a single most important database that is shared among all the other databases in the system. This database is used for sorting, temporary objects, triggers, row version and in other operations. Keeping it on the slow drive is not the solution, but reality is that it will just create many performance-related problems in the overall system. If your TempDB is becoming full, move it to the another drive.

Here are a few blog posts I have written on TempDB.

T-SQL Script to Find Details About TempDB

TempDB is Full. Move TempDB from one drive to another drive

Reducing Page Contention on TempDB

Improvements in TempDB

TempDB Restrictions – Temp Database Restrictions

Ideal TempDBFileGrowth Value

I am confused between Full, Differential, and Log Backup

Inability to understand the proper recovery model is another worst practice. I have people restoring many differential backups while restoring the database. I quite often see that log file backup interval is so huge it is more than differential backup interval. There are so many factors which can lead to disaster and data loss, leading to people to look for a new job in a new town at times. If you are confused regarding what is tail log backup, then stop, and learn from online books before implementing the backup strategy. Even if you are not responsible for implementing the backup strategy, I would still suggest you to read how to carry out proper backup as you never know when it will land heavily on your job card!

Here are few interesting write-ups on this subject on this blog.

Backup Timeline and Understanding of Database Restore Process in Full Recovery Model

Restore Sequence and Understanding NORECOVERY and RECOVERY

Mirrored Backup and Restore and Split File Backup

Restore Database Without or With Backup – Everything About Restore and Backup

Restore Database Backup using SQL Script (T-SQL)

There are many more practices I can write, but I think these five are the top worst practices. Feel free to post your opinions and suggestions.

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

SQL SERVER – Free Online Training on .net and SQL

I around 10 Free Online Training Codes available of .NET and SQL Training from Pluralsight. I am willing to give it to someone who wants learn technology this weekend. You just have to go to my Facebook page and leave a comment explaining in one line – what course will you learn during weekend. I will send all this codes to 10 winners whom I will randomly select using Facebook.

Meanwhile do you know how can you generate Zero without using any numbers in T-SQL. My friend Madhivanan has done that and I find it very interesting.Run following T-SQL code – ‘SELECT $’. He has written many other tricks how to generate zero also on his blog.

On another note – I have published my answer for question about SELECT * vs SELECT COUNT(*) here.

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

SQL SERVER – First Month as DBA Trainee – Disasters and Recovery

This blog post is written in response to the T-SQL Tuesday hosted by Allen Kinsel. He has selected very interesting subject for T-SQL Tuesday – Disaster and Recovery. This subject took me in past – my past. There were various things, I had done or proposed when I started very first month as a DBA trainee. I was tagged along with very senior DBA in my organization who always protected me or correct my mistake. He was great guy and totally understand the young mind of over-enthusiastic Trainee DBA. I respect him very much.

Here are few things which I had learned in my very first month (not necessarily I have practices them on production).

  • Never compress (zip) native backup using any tools, when disaster happen sometime the extra time to un-compress the database can be too long and not acceptable for business SLA
  • Do not truncate logs
  • After restoring full database backup – only restore latest differential back, no need to restore all the backup
  • Always write WHERE condition when deleting and updating

Sr. DBA always advised me – always keep your résumé ready and car ready – you never know when you can not recover disaster! Well for sure it was a joke. Today’s T-SQL Tuesday remind me of my very first month as DBA trainee.

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

SQLAuthority News – Community Tech Days – TechEd on The Road – Ahmedabad – June 11, 2011

TechEd on Road is back! In Ahmedabad June 11, 2011!

Inviting all Professional Developers, Project Managers, Architects, IT Managers, IT Administrators and Implementers of Ahmedabad to be a part of Tech•Ed on the Road, on 11th June, 2011. We have put together the best sessions from Tech•Ed India 2011 for you in your city. Focal point will be technologies like Database and BI, Windows 7, ASP.NET.


Venue: Ahmedabad Management Association (AMA)
Dr. Vikram Sarabhai Marg,
University Area,
Ahmedabad, Gujarat 380 015

Time: 9:30AM – 5:30PM

The biggest attraction of the event is session HTML5 – Future of the Web by Harish Vaidyanathan. He is Evangelist Lead in Microsoft and hands on developer himself. I strongly urge all of you to attend his session to understand direction of the web and Microsoft’s take on the subject.

I (Pinal Dave) will be presenting on the session of SQL Server Performance Tuning and Jacob Sebastian will be presenting on T-SQL Worst Practices. Do not miss this opportunity. Those who have attended in the past know that from last two years the venue is jam packed in first few minutes. Do come in early to get better seat and reserve your spot.

We will have QUIZ during the event and we will have various gifts – Watches, USB Drives, T-Shirts and many more interesting gifts.

Refer the agenda today and register right away. There will be no video recording so come and visit the event in person.

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