Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple.

USE AdventureWorks
GO
-- Disable the constraint
ALTER TABLE HumanResources.Employee
NOCHECK CONSTRAINT CK_Employee_BirthDate
GO
-- Enable the constraint
ALTER TABLE HumanResources.Employee
WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate
GO

It is very interesting that when the constraint is enabled, the world CHECK is used twice – WITH CHECK CHECK CONSTRAINT. I often ask those to find the mistake in this script when they claim to know the syntax very well.

Have you ever disabled and enabled constraints in your production environment? I would like to know why you did so.

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

Here is quick list of the tools which are available for SQL Server and Sharepoint. These are recently updated resources from Microsoft.

External Collaboration Toolkit for SharePoint
This solution allows users to create collaboration environments that use the familiar SharePoito deploy a SharePoint-based environment for collaboration with people outside your firewall. The accelerator allows users to create collaboration environments that use the familiar SharePoint interface. Because the solution is easy to use, end users are more likely to use it rather than revert to e-mail.

SQL Server Reporting Services Add-in for SharePoint Technologies
The Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies is a Web download that provides features for running a report server within a larger deployment of Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007.

Microsoft SQL Server 2008 Reporting Services August 2009 CTP Add-In For Microsoft Sharepoint Technologies
The Microsoft® SQL Server® 2008 Reporting Services August CTP Add-in for Microsoft SharePoint® Products allows you to take advantage of new SQL Server 2008 R2 Reporting Services report processing and management capabilities in SharePoint integrated mode.

High Availability of SharePoint Server Using Virtualized Environments and Database Mirroring
This paper highlights the lessons learned from a proof of concept configuration of Microsoft Office SharePoint Server 2007 across two data centers that included database mirroring.

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

This is very short note announcing details about my course details for ‘MS SQL Server 2005/2008 Query Optimization And Performance Tuning‘.

The course is scheduled to be delivered from 19th Nov, 2009 and 21st Nov, 2009 in Pune, India.

This three day course is an intensive course designed to give attendees an in-depth look at the query optimization and performance tuning concepts and methods found in SQL Server 2005/2008. This course is designed to prepare the SQL Server developers and administrators for a transition to SQL Server 2005/2008 while discussing best practices for a variety of topics.

Day 1 : Strengthen the basics along with SQL Server 2005/2008 New Features

Module 01: Subqueries, Ranking Functions, Joins and Set Operations
Module 02: Table Expressions
Module 03: TOP and APPLY
Module 04: SQL Server 2008 Enhancements

Day 2: Query Optimization & Performance Tuning 1

Module 05: Logical Query Processing
Module 06: Query Tuning
Module 07:  Introduction to the Query Processor
Module 08:  Review of common query coding which causes poor performance

Day 3: Query Optimization & Performance Tuning 2

Module 09:  SQL Server Indexing and index maintenance
Module 10:  Plan Guides, query hints, UDFs, and Computed Columns
Module 11:  Understanding SQL Server Execution Plans
Module 12: Real World Index and Optimization Tips

The space are limited and I encourage you to register as soon as possible.

Call to register – +91 98817 95739, +91 98508 43664, +91 99229 33319

Email to register – workshop “at” seedinfotech.com

If due to any reason, you can not attend this course, please call or email on details above for future schedule for the course and location.

Click to Enlarge

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

Recently, I have been working on Query Optimization project. While working on it, I found the following interesting observation. This entire concept may appear very simple, but if you are working in the area of query optimization and server tuning, you will find such useful hints.

Before we start, let us understand the difference between Seek Predicate and Predicate. Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes whereas in Predicate, the search is on non-key columns – which implies that the search is on the data in page files itself.

Earlier, I had posted regarding how to remove key lookup and bookmark lookup in the following:

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3

Let us see an example where we will remove the bookmark lookup first using the covering index. On removing the bookmark lookup, it resulted in Index Scan, which is not good for performance. When Index Scan is converted to Index Seek, it provides  a significant improvement in performance.

Run following SELECT, which is based on the database AdventureWorks.

USE AdventureWorks
GO
-- CTRL + M Enforces Key Lookup
-- Try 1
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO

Let us check the execution plan for the same. The execution plan consists of the key lookup because there are columns that we are trying to retrieve in SELECT as well in WHERE clause.

Let us create a covering index on this table HumanResources.Employee.

-- Create Non clustered Index
CREATE NONCLUSTERED INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee
(
NationalIDNumber ASC, HireDate, MaritalStatus
) ON [PRIMARY]
GO

After creating above index, let us run the same SELECT statement again.

-- CTRL + M Removes Key Lookup, but it still enforces Index Scan
-- Try 2
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO

Due to non-clustered index, Key Lookup is removed along with Nested Loops but Index Scan still remains, and this is not good for performance.

I tried to remove the scan, but my attempts were unsuccessful. I finally looked at the datatype of the NationalIDNumber. All this time, I was assuming that this datatype is INT, but on a careful check, I found that the datatype of NationalIDNumber is nvarchar(15).

In the SELECT statement, we were comparing the datatype of NVARCHAR to INT, and this was forcing the predicate operation while executing the query.

As discussed earlier, due to predicate operation, there has to be explicit conversion on the side of NationalIDNumber, which forces the query optimizer to not use the index and instead it has to scan the complete data in table to get the necessary data. This is not the desired solution. Index Scan reduces performance. The reason for this conversion is because I am using INT value in WHERE clause instead of NVARCHAR.

I changed my WHERE clause and passed STRING as the parameter instead of INT.

-- CTRL + M Removes Key Lookup and it enforces Index Seek
-- Try 3
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'
GO

After running the query with the changed WHERE clause, the Index Scan is now converted into Index Seek.

Index Seek is definitely the most optimal solution in this particular scenario.

When the detail execution plan was checked, I found the following two notable points. First, the predicate is converted to seek predicate, which is the reason for performance improvement, as described earlier. Instead of scanning data in the table, Index Seek is performed. Second, as the datatype of the NationalIDNumber is NVARCHAR and the parameters are passed as VARCHAR, the conversion happens on the parameters instead of NationalIDNumber column, and this forces Index Scan to Index Seek.

If we pass the parameter as NVARCHAR instead of VARCHAR, the execution plan will remain the same, but CONVERT_IMPLICIT will not be required any more. Let us run the following query, which has NVARCHAR as the parameter.

-- CTRL + M Removes Key Lookup and it enforces Index Seek and no CONVERT_IMPLICIT
-- Try 4
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = N'14417807'
GO

The execution plan of the above query is very similar to that in which we had passed the parameter as VARCHAR.

Now let us check the execution plan for the same.

In the WHERE condition, the operators we have on both the sides of “=” are of NVARCHAR. NationalIDNumber and parameter passed – both are NVARCHAR, which has removed CONVERT_IMPLICIT. However, there are no changes in the execution plan.

In summary, when Key Lookup is removed and index seek replaces index scan, the performance is tuned up. Let us quickly compare the execution plan of the above four options. I have included the complete code here for easy reference.

USE AdventureWorks
GO
-- CTRL + M Enforces Key Lookup
-- Try 1
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO
-- Create Non clustered Index
CREATE NONCLUSTERED INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee
(
NationalIDNumber ASC, HireDate, MaritalStatus
) ON [PRIMARY]
GO
--WAITFOR DELAY '00:00:30'
-- CTRL + M Removes Key Lookup, but it still enforces Index Scan
-- Try 2
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807
GO
-- CTRL + M Removes Key Lookup and it enforces Index Seek
-- Try 3
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'
GO
-- CTRL + M Removes Key Lookup and it enforces Index Seek and no CONVERT_IMPLICIT
-- Try 4
SELECT NationalIDNumber, HireDate, MaritalStatus
FROM HumanResources.Employee
WHERE NationalIDNumber = N'14417807'
GO
/* What is the reason for difference between Try 2 and Try 3?
Check the exeuction plan
*/
-- Drop Index
DROP INDEX [IX_HumanResources_Employee_Example] ON HumanResources.Employee WITH ( ONLINE = OFF )
GO

Let us look at the execution plan.

Summary points:

• In general, Index Seek is better than Index Scan (I am not talking about it depends conditions)
• Understand Predicates and Seek Predicates and see if you have only Seek Predicates.
• In case of key lookup or bookmark lookup, see if you can create a covering index or included column index.
• Use the datatype wisely even though there is no change in the resultset.

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

During the PASS summit, one of the attendees asked me the following question.

Why the Stored Procedure takes long time to run for first time?

The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not be the case. In fact, Stored Procedures are not pre-compiled; they compile only during their first time execution.

There is a misconception that stored procedures are pre-compiled. They are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.

If you create any SP, you will find that there is no cache entry for the execution of that SP.

After running the SP for the first time, the entry for the cache is made in the system.

If we see the following script, we can notice the different of cache when SP was created and SP was executed.

/* Exeercise to verify if stored procedure pre-compiled */
USE AdventureWorks
GO
-- Clean Cache
DBCC FREEPROCCACHE
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompSP]
GO
-- Create New Stored Procedure
CREATE PROCEDURE CompSP
AS
SELECT *
FROM HumanResources.Department
GO
-- Check the Query Plan for SQL Batch
-- You will find that there is no ObjectName with CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
/* Execute Stored Procedure */
EXEC CompSP
GO
-- Check the Query Plan for SQL Batch
-- You will find that there is one entry with name ObjectName with name CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO

The result set of above query is as following.

The above script to find out the cache is taken from the white paper SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low. You can also read my follow up article SQL SERVER – Plan Caching and Schema Change – An Interesting Observation, where I have given an interesting conversation with Greg Low.

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

The data compression feature in SQL Server 2008 helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.

This white paper provides guidance on the following:

• How to decide which tables and indexes to compress
• How to estimate the resources required to compress a table
• How to reclaim space released by data compression
• The performance impacts of data compression on typical workloads

Read Whitepaper: Data Compression Strategy Capacity Planning and Best Practices

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

Fourth day was awesome! I had scheduled nearly 8 meetings with different groups of people today. It was really great fun. Let us see the keypoints for the same.

• PASS President Wayne Snyder honored and thanked Kevin Kline for his 10 YEARS of service.
• Kevin then gets a well-deserved standing ovation from the entire audience.
• Next year’s PASS Summit will be in Seattle from November 8 to 11, 2010.
• Dell Key note was little flat in delivery. Dell was primary sponsor for the event.
• Dr. David DeWitt, Technical Fellow, Data & Storage Platform Division at Microsoft starts presentation entitled “From 1 to 1000 MIPS.” One of the best sessions I ever had in my life.

The keynote was followed by several meetings. There was lot of food, drinks and entertainment all around. I received so much swag that I had to get brand new bag to contain the same. I will post the photos of the stuff that received once I reach home. I will be heading towards airport in next 15 minutes. So, I will be disconnected from the Internet for 36 hours once I board the flight. I am uploading only 2–3 photos at this moment. Check here again as I have nearly 30 more photos to share.

I really enjoyed the summit. I will post the in-depth summary of my whole trip to USA once I reach home. Thank you PASS; I will come here next year!

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

Posted in MVP, Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology Tagged: PASS, SQL PASS

The third day at SQL PASS Summit was education + entertainment day for me. During the last 10 days, I woke up at 4:00 AM regularly. However, as I had way too much fun yesterday at various parties earlier, I did not get up till 7:30 AM. By the time I woke up, I realized that I was late for my early breakfast meeting with Solid Quality Global Mentors. I somehow managed to reach there at 8:00 AM and we talked for nearly an hour. After the meeting, I headed to Keynote. Keynote is the best time of the day and wanton one wants to miss it.

I actively cover all my activities on twitter. So do follow me on twitter for the updates!

Today’s Keynote was extremely entertaining and educating. Audience was seen having a great time at various demos. I wished that all our technical sessions should be like this session as it was one of the excellent demos. Let us see few quick notes from the Keynote.

• Rushabh Mehta – President Elect started the keynote asking humorous question why there is no free drink at PASS and explained the budget
• Projected revenues in FY 2010 are $3.2 million, a 15% drop in revenues, while there was a 40% increase in community spending. There was 67% reduction in IT expenses
• Jacob Sebastian, the PASS Outreach Program leader, was recognized for his excellent leadership
• Charley Hanania and Allen Kinsel won PASSion Award
• Tom Casey, GM, Business Intelligence and Amir Netz presented excellent demos and future vision
• Power pivot demos were excellent and warmly welcomed by community
• Touchscreen Powerpivot demo was the major highlight of the Keynote

I attended technical session of Rushabh Mehta (President Elect for PASS, SQL Server MVP and CEO, Solid Quality India). He talked about ‘Overcome SSIS Deployment and Configuration Challenges’. Now, who knew that it would be very humorous session? Rushabh started the session with reference to the movie ‘What About Bob?’ and kept the whole session as story line of the movie. Many of us will agree that subjects like SSIS, deployment issues and configuration complexity are very dry subjects. However, with audience interaction and the presentation of this technically complex subject in simple words, the whole session was the highlight of the day. I confess that I am expert in BI, but not someone who knew it inside out. In fact, I am intrigued to learn more about this particular subject after attending this excellent session. I have requested Rushabh to spend some time with me and explain the extension of this subject.

I had many different meetings scheduled today. All of them were successful. I managed to run around the exhibition hall and meet many different MS vendors. I really enjoyed meeting them and I also learned about the upcoming technology. One of the vendors really impressed me with an excellent demo; I have requested the full evaluation version for their software. If they provide me the same, I will try that out and post my review here soon. If their software meets my expectations I have built after seeing the demo, I will sincerely wish to see them go mainstream with that product.

During evening, I had two different parties to attend and both party were rockstar party. It is so interesting that no matter how many different parties we go, we usually find the same people walking around. I found very strong presence of Microsoft in both the parties. Party hosted by Solid Quality Mentors had excellent food and drinks. I met many different SQL Experts there and we did NOT talk SQL. It was party and time to relax! As there was such a fun, I was late to attend the party of Microsoft which was hosted at Gameworks. Everybody went crazy with the games. Lots of game, food and drinks again here as well.

In the evening, I attended two different parties and both the parties were rocking to the fullest. It is so interesting that no matter how many different parties we go, we usually find the same people walking around. I found a strong presence of Microsoft in both the parties. Party hosted by Solid Quality Mentors had excellent food and drinks. I met many different SQL Experts there and we did NOT talk SQL! It was party and time to relax! As there was so much fun, I was almost late to attend the party of Microsoft, which was hosted at Gameworks. Everybody went crazy with lots of games, food, and drinks.

I just enjoyed the third day of the summit. Just one more day to go – I just wish this summit never ends. However, I strongly miss my family and especially my 2-month old daughter (and wife! – i know its clichéd but its true!!).

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

The second day of PASS started with very engaging and it started with an original game invented by Stuart Ainsworth. This game involves finding twitter people in real life. As I was not one of the square in bingo, I had decided to participate in game myself and try to win if I can. During this process, I felt guilty that I borrowed a pen from Stuart and did not return it back. In fact, after a while someone took the pen from me and never returned it. It is true that karma pays off! I should have returned it right away.

Well one very first thing in the day was having breakfast with SQL friends. I met few more celebrities and Arnie Rowland again, who is a genius and very friendly. We all headed towards Keynote at around at 10 PM. The Keynote was great; it started with very interesting details. The current PASS President presented few photos before starting the session. They are as follows:

• Year 2009 PASS registration is 2,200 for the full day event
• 24 hours of PASS had 50,123 session registrations from 3,524 people of 70+ countries
• SQL Server Standard is coming back
• Microsoft’s Bob Muglia talked about SQL Server on the very first day and its future, and he covered few interesting demos as well
• Microsoft’s Ted Kummert talked about PASS and reasons to attend PASS. He also presented few testimonies from the customers and interesting demos

Once the session was over, I headed to SQLFool aka Michelle Ufford’s session on Index Optimization session. As I mentioned earlier, she is one expert who should be respected for her extraordinary skills for index optimization.

During lunch we had wonderful event called Features of Birds MVP Lunch. During this event, I talked about Change Data Capture (CDC). I was very fortunate that many people showed up at my table. I really hoped that I could have accommodated everybody; however, the table capacity was only 10. I promise to all of those who missed that I will do special online session.

Michelle’s session was followed up by Jacob Sebastian’s session. This session was attended by a large group of XML fans. Just like any other session of Jacob, this session was very well received, and he got a lot of good feedback. Jacob is officially the human XML.

After these two back–to-back sessions, I felt that I have learned quite a lot for one day. With that confidence, I went to exhibition hall. In the hall, I met so many really cool people whom I always wanted to meet. I also received a lot of giveaways at the expo – 9 T-Shirts, 3 candy boxes, 1 tote bag, and 2 sweet little ducks. I am sure Shaivi (my two-month-old daughter) is going to love those little ducks.

In the evening, Microsoft threw a very interesting party for all the Microsoft MVPs and it was great fun. Jacob and I were almost lost our way to the party location. We had to hire taxi to reach the location. The taxi driver asked us so many questions that we felt like we were at the prison interrogation location. However, the journey (which seemed a year long) was less than a mile! I met many friends and MS employees there. It was a very great place for interacting with everyone.

Overall, today went great at SQL PASS; I am eagerly looking forward to the upcoming events in the following two days.

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

Day 1 at PASS was awesome. I usually write everything in detail when I have to cover any project. This time, I have decided to cover this event little bit differently and with lots of images. For day 1, I have more than 90 photos taken with many SQL celebrities and different sessions. I will be not able to cover all the photos taken today in this post. I will gradually post all the photos as I will do follow up posts. In this post, I will cover my activities on day 1 as well few of the photos that give you a visual tour of the spot that I have covered in one day.

Yesterday I went to city tour where I had walked on the streets of Seattle and had lots of fun. I went on an underground tour in Seattle and then got back to registration desk for registration. Day 1 started great; while I was going to checkout the venue, I had meet Blythe Morrow on my way to Seattle Convention Center. I consider this as great start of SQL PASS event as she has put in considerable work to make this event a success.

Being a vegetarian, thus far I have never faced any difficulties finding good food in USA.

Not every one know that the name “Seattle”is native American name, and it was taken from the name of the Native American tribe chief, Seattle. Please visit this wiki article to read further on Seattle City.

I was also mesmerized with the set up at SQL Pass Summit. SQL PASS Head Quarter has put in considerable efforts in making this event successful.

I met many of the SQL celebrities during my very first day. As I mentioned earlier, I managed to click around 80 photos of the legends. I will share all of them soon.

PASS event is a community event, and the community is heavily encouraged. I was very fortunate to meet two of the very active community leaders in my meeting. They amaze me with their dedication to help the SQL Server Community: Joe Webb and Arnie Rowland.

I have previously mentioned that if I want to be like someone in SQL Community, it would be Steve Jones. He is the founder and current manager for the extremely popular site SQLServerCentral.com. The reason I aspire to be like him is that he is a very nice down-to-earth person who is always smiling. I also attended his SSC Party on day 1.

I have mentioned about Brent Ozar several times on our blog. He is the only person with spectacles besides me in following image. I have been very eager to meet him for many years and early morning, I got the opportunity to have breakfast with him and many other SQL Celebrities. I will write in detail about other members in the image in a separate blog post.

I have previously blogged about SQLFool for her excellent Automated Index Defrag Script, and I was very lucky to meet her on the very first day.

During evening I had delicious dinner along with PASS Regional Mentors. New President Elect Rushabh Mehta was also present the event to encourage our efforts.

Overall, the first day went pretty well, I met many more experts and learned a lot of new tricks. I am looking forward to the second day of the event.

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

This blog has reached a remarkable milestone. It is 3 years old today. So far, there have been more than 10.5 million views on this blog and more than 1140 articles. It is really exciting that on this very important day, I am attending my very first SQL PASS in Seattle. The feeling and excitement to attend the very first summit cannot be put into words. I have been waiting to attend this summit for almost a year now, and today this dream is materializing with my blog’s “birthday.”

You can read all of my articles written thus far here. I will keep you updated with my daily activities at SQL PASS. You can follow me on twitter as well, where I update my status on the same.

Thanks again for staying with me to make this blog a community event as well. Surprisingly, first November has always been a significant day in my life. In fact, three years ago when I started writing, I never thought of reaching this milestone.

1st Nov 2009 – At my very first SQL PASS Summit, Seattle 2009
1st Nov 2008 – Ended my 1 year Outsourcing Manager contract and planned to join SolidQ
1st Nov 2007 – Left USA after 7 years and headed back to India as a permanent resident
1st Nov 2006 – Birth of  SQLAuthority.com

My humble and sincere thanks to the almighty and my family for their unconditional support.

I have got lots of request for photos of Shaivi Pinal Dave (my daughter). Here are few photos.

Nupur, Shaivi and Pinal

Shaivi Dave

Here is the photos of my current superiors (boss!). Thank you very much to you as well. Following photos is taken just 2 hour before this blog post in Sheraton Lobby Downtown Seattle.

Fernando Guerrero, Rushabh Mehta, Pinal Dave

Please note : Though this post is meant to be posted for 1st Nov India time. However, as I am in USA and due to time difference of 13.5 hours, it is posted on 1st Nov US time, which translates as 2nd Nov India time.

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

As mentioned earlier in a blog post SQL SERVER – Advanced T-SQL with Itzik Ben-Gan – A Dream Coming True, I got the wonderful opportunity to attend the course of Itzik Ben-Gan. Itzik is one of the true masters of SQL Server, and his fame had set my expectations quite high. The most interesting aspect is that I have taught a similar course in India several times, and I was quite familiar with all the slides and examples. As I already knew a lot about this course, I was wondering if I would be able to enjoy the class or learn something new. The reality is I learned alot of new things.

Overall, this class delivers quality training way more than what we expect. Learning the course from Itzik is a great experience. In the last five days, I have learned several new things. In fact, I felt that if I attend the class again, I can for surely learn more. Itzik suggested so many useful tips, which really impressed me.

Let me list a few of those tips here:

• When TOP is used along with 100% and ORDER BY clause in table expressions (view, CTE), the ORDER BY clause is ignored.
• For only two tables, both JOIN and SubQuery can be the optimal solution.
• You can use TOP to gradually delete a table with millions of rows.
• Understanding the instances when Index Scan is faster to retrieve data than Index Seek.
• Power up analysis of data without using analysis service and grouping set features.
• Trivial plan does not look for index selectivity.

This class was on Advanced T-SQL Server 2008, and it was faithful to the concept. Itzik has a very interesting way of delivering the sessions. His course and sessions are perfectly managed, and the learning topics are very well paced. All the sessions are perfectly timed and very interesting. All the sessions are followed up with exercise and lab. Itzik takes a good amount of time to explain the Hands on lab, which is very helpful.

There are 10 different advance T-SQL sessions, and they are covered very well. I enjoyed each session and learned a lot of new concepts. Few things are difficult to put in writing, and Itzik has effectively discussed many concepts like that.

To top these, all the attendees were given two different books written by Itzik for FREE!

I think this five-day training was one of the best training sessions I have ever had in this subject. A large number of concepts were covered in this training. If I get a chance to attend this course again, I will surely not miss it as I do not want to let go of the opportunity to learn from Itzik at any cost.

I express my special thanks to Itzik Ben-Gan for accepting my registration at the last moment even though the seats were full, as well as to Rushabh Mehta for making this happen for me.

During the class, Itzik invited me for dinner to Maggiano, an excellent place for Italian cuisines. Two other excellent Solid Quality Mentors also joined us for dinner – Greg Low and Peter Myers. I was extremely delighted to have these top-notch industry experts around me talking SQL. I consider myself fortunate to have dinner with them and to have spent Solid Quality time with them.

If you are in waiting list for the course and want to get into the class earlier than your schedule, please feel free to email me and I will help you as much as I can.

Peter Myers, Itzik Ben-Gan, Greg Low, Pinal Dave

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

The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community. From local user groups and special interest groups (Virtual Chapters) to webcasts and the annual PASS Community Summit – the largest gathering of SQL Server professionals in the world – PASS is dedicated to helping its members Connect, Share, and Learn.

Today was a big day as PASS announced the executive board members for the term starting on Jan 1, 2010. I would like to express my congratulations to all new executives of PASS. Please read official press note here.

The newly elected board members at executive positions are as following:

• Rushabh Mehta – President
• Bill Graziano – Executive Vice President, Finance
• Rick Heiges – Vice President, Marketing

A little bit more details about executives.

Rushabh Mehta is a Mentor for Solid Quality Mentors’ global Business Intelligence Division and the Managing Director for Solid Quality India. Rushabh has worked with Microsoft SQL Server since 1998 and has designed and developed BI systems on the Microsoft platform since 2001, including a predictive analysis system, a health-care analysis solution, and a multi-terabyte financial decision support system. He has been the lead architect for many large-scale and complex BI solutions for clients such as Publix, Raymond James Financial, Jackson Hewitt, and Checkers. Rushabh has been an active volunteer with PASS since 2000 and has also served on the board  for a number of years. Rushabh is also a SQL Server MVP and a frequent speaker at large conferences and PASS Chapters around the world.

Bill Graziano is an active member of PASS since 2001, Bill currently serves on the Board of Directors, where he is responsible for the overall marketing for PASS and PASS Summit. Bill co-founded SQLTeam.com, a web site for SQL Server developers and database administrators. In addition to managing the site, he writes articles and helps users with their most vexing problems. An involved participant in the SQL Server community for seven years, Bill was recognized as a SQL Server MVP in 2004 and still holds that designation.

Rick Heiges is a Sr. Solutions Consultant with Scalability Experts working with customers on solutions within the SQL Server environment and educating users on SQL Server 2005 by speaking at user groups, conferences, labs, and road shows across the USA, Europe, and Africa. He holds an MBA and a M.S. in MIS as well as a B.S. in Computer Science. In addition, he also has attained MCTS:SQL2005 and MCITP:DBA certifications.

Not only are these three executives experts in their domain, but have been friends for many years. Here are few of their photos from their activities at old PASS events.

Many congratulations to all of you. SQL Server community has lots of expectation from you and we are confident that you will not only satisfy them but will go way beyond our imaginations.

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

Image courtesy: Rick Heiges, Rushabh Mehta, SQL PASS

Posted in MVP, Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL User Group, SQLAuthority News, SQLServer, T SQL, Technology Tagged: PASS, SQL PASS

Last week, I had wonderful time attending meeting of small ISV (Independent Software Vendors). Several topics were discussed, but the one topic that caught my attention was the adoption of the third party SQL Server tools. There were around 100+ top level managers who take decision regarding what resources are needed for projects. I had a great time talking to them.

I have delivered a session on the subject “SQL Server – A Scalable Performance Database Platform“. Whenever I receive the right opportunity, it gives me great pleasure to talk about SQL Server. I have been working with SQL Server, and it is interesting to compare it with compete. I personally do not like to put down any compete technology; so I always make sure that I present a balanced view when I have an audience who has the knowledge of different technologies.

We had a very interesting round table discussion where we talked about SQL Server and its related technologies. What was very interesting is that only 8 out of 72 people voted that they use a third party tool for SQL Server. We found out the following reasons behind the rare use of the third party tool.

• Not supporting to India-oriented paper work (i.e. Purchase Order, Invoice, etc)
• Lack of local offices
• Lack of technical support locally
• No option to pay in local currency
• Expensive

What was interesting in whole discussion was that the reason of being expensive was placed as last option. Number one reason for why third party tools were not gaining acceptance in local Indian market was the improper understanding of the paperwork. I was very surprised because in this era of Internet and global flat world, the issue of the paper work turned out to be the first and top-most barrier for the acceptance of third party tools in India.

I just found this whole issue very interesting and decided to put them as a blog post here. Let me know if you are interested to know more about the meeting of my presented SQL session.

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

I received few emails regarding where can people meet me at SQL PASS event in Seattle. I am currently in Bellevue attending Itzik Ben-Gan’s class. I am immensely enjoying the class, and I shall post details about the class once it is over.

If you are attending SQL PASS and interested to meet me, I will be present at Birds-of-a-Feather (BOF) Lunch. I will be talking on the subject Change Data Capture (CDC). Please note that this lunch is for all of us; moreover, it is not necessary that I will be talking on only subject of Change Data Capture. In fact, I will talk about this subject for few minutes, and then we can discuss on any topic you want to know about. The table will have total of 10 seats (which includes my seating).

We can all have an effective small group conversation on SQL, SQL PASS, and the entire community. You are welcome to join me at lunch. Please read the complete details about the same here.

Thanks a lot Mike Walsh for organizing this event!

Please do join us for lunch!

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

Change data capture (CDC) is a new feature in SQL Server 2008 designed to capture insert, update, merge, and delete activities applied to SQL Server tables and to avail those changes in an easy-to-understand format.

Conventionally, detecting changes in a source database to transfer these changes to a data warehouse required any of the following:

• Special columns in the source tables (time stamps, row versions).
• Triggers that capture changes.
• Comparison of the source and the destination systems.

The above methods can have significant disadvantages: special columns require a change in the source database schema, and in many cases, a change in the application logic. Triggers must be implemented manually, and this can lead to significant additional overhead on DML commands to the source system. Comparing the source and target databases can also place a heavy load on both systems.

CDC offers a win-win alternative that allows the transfer of changes without forcing the DBA to either place a heavy load on the server or add the above change-detection steps. CDC enables the capture of changes by asynchronously reading the transaction log of the source database and then recording the changes in those logs in special change tables for uploading later into the target database. Therefore, no comparison between the source and target systems is needed for changes.

Read white paper of Tuning the Performance of Change Data Capture in SQL Server 2008

Download Script of Change Data Capture (CDC)

Introduction to Change Data Capture (CDC)

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

Many times I have seen that the index is disabled when there is large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO

While enabling the same index, I have seen developers using the following INCORRECT syntax, which results in error.

USE AdventureWorks
GO
----INCORRECT Syntax Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact ENABLE
GO

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘ENABLE’.

This is because once the index is disabled, it cannot be enabled, but it must be rebuilt. The following syntax will enable and rebuild the index with optimal performance.

USE AdventureWorks
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

I hope that now you have understood why enabling this syntax in the index throws an error and also how to enable an index with optimal performance.

If you want to read just the syntax, I have written a similar article earlier SQL SERVER – Disable Index – Enable Index – ALTER Index.

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

As from my blog posts, all of you are probably aware that I am very much excited for attending SQL PASS at Seattle from Nov 1, 2009. As the days to the summit were nearing, I could already feel the rush of adrenalin in my veins. May be because of this, I could not wait any longer and so I headed towards Seattle a week earlier!

As Robert Cain mentioned on twitter, I finally arrived at Seattle a week earlier than the start date of the summit. I landed in Seattle on the evening of Oct 24, 2009. As I was early, I wanted to spend my time constructively by learning something. I asked Rushabh Mehta, the Managing Director of Solid Quality India, regarding what to do during the free time. Rushabh was well aware that I am a big fan of Itzik Ben-Gan. I have already read every available book written by him on T-SQL subject. Rushabh right away suggested me to attend the course taught by Itzik in Bellevue (located very close to Seattle).

I dreamt of meeting Itzik, and I had finally managed to meet him in the MVP Global Summit earlier this year. In the same event, I was fortunate to meet Rushabh also, and he had promised me the following:

1. To visit my User Group in Ahmedabad whenever he comes to India next time – He kept his promise!
2. To help me attend one of Itzik’s courses to make my lifetime dream come true – Well! he is about to fulfill his second promise too.

I am very glad that Itzik accepted my registration for his course Advanced T-SQL Querying, Programming and Tuning for SQL Server 2005 & 2008. As you might be aware, for his courses, there is always a waiting list of attendees. Here, I want to thank Rushabh and Itzik along with Solid Quality Global for arranging this wonderful opportunity for me.

I am very glad as my happiness has doubled as soon as I came to USA. My stay in USA is for 12 days. I have arrived on Oct 24, 2009 at 9 PM and will leave on Nov 6, 2009 at 8 AM. During these 12 days, I will continue to blog and share my experience at Seattle.

You can follow me on twitter. I plan to update you all regarding the interesting details about my trip @twitter.

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

This article explains what SQL Server Integration Services configurations are used for, why you should use Integration Services configurations, and what options you have for leveraging configurations. It will also make some simple recommendations that are based on my experiences of building Integration Services packages in a real-world environment. An understanding of the terms “package”, “Business Intelligence Development Studio”, and “dtexec.exe” in the context of Integration Services is assumed.

There five basic types of Integration Services configurations.

• XML Configuration File
• Environment Variable Configuration
• Parent Package Configuration
• Registry Configuration
• SQL Server Configuration

Read Best Practices for Integration Services Configurations

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

Do you keep following Book Online Links handy? I do and I use them a lot.

SQL Server 2008 R2

SQL Server 2008

SQL Server 2005

SQL Server 2000

I do and I use them a lot.

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

Posted in SQL, SQL Authority, SQL BOL, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology