As you know, I am documenting my experience at the Tech Ed 2011 (India) held from March 23-25, 2011 at Bengaluru, India.

You can find the summary of my experiences on Day 01 and Day 02 at:

Day 01 – http://beyondrelational.com/blogs/nakul/archive/2011/03/28/tech-ed-2011-india-day-01-connect-learn-explore-evolve.aspx

Day 02 – http://beyondrelational.com/blogs/nakul/archive/2011/03/30/tech-ed-2011-india-day-02-ie-9-india-launch-fast-is-now-beautiful.aspx

Today, as India celebrates it’s win into the cricket World Cup 2011 finals, I will take you through the final day in the wonderful journey of Tech-Ed 2011.

A day for the DBAs

Day 03 was very special – it was a day which had a full track dedicated to the DBA community – guaranteed to attract all DBAs – accidental or not like – bees are to honey.

Hands-on Lab on Contained Databases by Jacob Sebastian

After the keynotes, the day immediately picked up speed as the DBA community got to participate in a hands-on lab on Contained Databases – a long awaited feature, and now part of SQL 11 (Code named “Denali”). The lab was conducted by none other than renowned SQL Server MVP and leader of the Ahmedabad SQL Sever User Group, Jacob Sebastian! His trademark style of explaining difficult concepts by telling a story made this concept a breeze to understand.

I will be taking everyone through the entire hand-on session in a series I am planning for the next week. Stay tuned, and I am sure you will find it useful.

Understanding SQL Server Behavioural Patterns – SQL Server Extended Events by Pinal Dave

Extended Events are not new to SQL Server 11/(“Denali”), but not many know how to efficiently use them. Pinal Dave, the renowned Microsoft Evangelist, demonstrated how to use Extended Events to execute a couple of common database administration tasks. What I liked the most were the demos on:

1. Detect long-running queries
2. Detect page splits

One of the most interesting items of the session was how Pinal demonstrated the concept of what events and extended events are by co-relating them to the human heart and a scenario wherein a hypothetical elephant needs to be taken to the hospital after collapsing during the Tech-Ed. That’s what I call – fun with learning!

You can download the scripts used during this demonstration by visiting Pinal’s blog at: http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/

Data Recovery with Backups by Vinod Kumar

Vinod Kumar was back after Day 01 – this time to show the audience the immense value a good backup strategy can have. Vinod demonstrated how to recover from the following situations from a backup:

1. Recovering/Restoring to a point in time
2. Recovering/Restoring to a named transaction
3. Recovering from a page corruption

Quite frankly, I did not know that one could “mark” a transaction and that this can be used during recovery – just as a point-in-time restore. As a proof of the importance of this feature, Vinod discussed one of the practical implementations that have been done using this feature. In the interest of privacy, the exact nature of the project was not disclosed to the audience.

SQLCAT: SQL Server Consolidation – Using Virtualization & Lessons Learned by Prem Mehra

Prem Mehra is a Program Manager with Microsoft at Redmond and works for the SQL Customer Advisory Team (SQLCAT). This team is comprised of solution experts that regularly engage in the largest, most complex and most unique customer deployment engagements worldwide.

In this session, Prem discussed some of the consolidation projects that the SQLCAT team has done in a virtualized/cloud based environment. The audience had some great things to learn from him, and my biggest take-away was the following:

“If a SQL Server is not performing well, the problem is either in the application or in the underlying hardware platform – in the order mentioned.”

Also, Prem demonstrated the ideal approach in moving towards a cloud based deployment (which is shown here as a picture captured of his slide) and how the upcoming SQL Server 11 (“Denali”) will help in the journey.

(Source: http://msdn.microsoft.com/en-us/library/ee819082.aspx)

SQL Server Waits and Queues – Your Gateway to Performance Troubleshooting by Pinal Dave

Do you really know how to remove SQL Server bottlenecks when a server seems to be really, really slow? Yeah, of course! Look at the wait statistics and queues – how difficult can it be?

Not much. So, are CXPACKET waits bad? Can we eliminate LOGBUFFER waits? What are Signal waits?

Pinal did it once again – with a simple example of a group of people awaiting a cab and then stopping to withdraw cash from an ATM, Pinal very beautifully explained Signal Waits. He then went on to demonstrate and explain what CXPACKET waits are and how they will always be there, and that they might sometimes be good. Finally, in an power-packed demo, he demonstrated how moving the data file of a database to his portable USB drive can reduce LOGBUFFER waits. (Yes, you read me right – Pinal moved the data file of a database to his portable USB drive)

Here’s where you can read Pinal’s series on Wait stats & queues – http://blog.sqlauthority.com/2011/02/28/sql-server-summary-of-month-wait-type-day-28-of-28/

SQL Server AlwaysOn: The next generation high availability solution by Balmukund Lakhani

High Availability is one of the key requirements of any SQL Server deployments. Who better than the Technical Lead in the SQL Server support team with Microsoft India GTSC to demonstrate how simple it is to setup a cluster, configure a high-availability solution, and demonstrate a failover – all LIVE and within the hour!

Balmukund demonstrated the High Availability “AlwaysON” features which will be made available in SQL Server 11 (code named “Denali”). The solution will definitely make the lives of all DBAs and IT administrators easier because combined with the power of Windows Server 2008 R2, setting up a cluster (or an Availability Group) is a breeze. The cluster can have one automatic fail-over partner, with other manual fail-over partners. The best benefit of this solution is that it is similar to mirroring – but without the drawbacks of mirroring.

Here’s a step-by-step guide to getting you started on HADR: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/12/17/sql-server-denali-alwayson-hadr-step-by-setup-setup-guide.aspx

Closing Keynote – Visit by Anil Kumble!

Finally, the time came to close the Tech-Ed 2011 (India). With a heavy heart, all participants submitted their feedback forms for the last sessions of the day and proceeded towards the keynote hall for the closing keynote.

The final attraction of this year’s Tech-Ed was that we had a very special guest with us. None other than the star cricketer – Anil Kumble! None other than Gaurav Kapoor, who hosted the live sessions of Tech-Ed, introduced Anil Kumble on stage and the entire group erupted in cheers! Hands went-up all the way to catch a snap or two of Anil Kumble in person. So high was the excitement, that nothing could distract the audience. I will let the pictures speak for themselves.

A full-house awaiting the start of the closing ceremony.

What’s next?

My next post will be a deep dive into Jacob’s session on the worst T-SQL practices on Tech-Ed Day 01. Next week, we will continue the series on the Underappreciated Features of SQL Server, and also explore the “Contained Database” feature of SQL Server 11 (code named – “ Denali”). Contained Databases is based on the hands-on lab conducted on the same topic by Jacob on Day 03 of Tech-Ed 2011.

Watch out for some exciting new and exciting reading material hot off the presses!

Until we meet next time,

Be courteous. Drive responsibly.

As many of you would know by now, these days I am attempting to summarize my experience at Tech-Ed 2011 (India) held from March 23-25, 2011 at Bengaluru, India.

The summary of my experiences on Day 01 can be found at:http://beyondrelational.com/blogs/nakul/archive/2011/03/28/tech-ed-2011-india-day-01-connect-learn-explore-evolve.aspx

Today, I will be sharing with you the experience of Day 02.

IE 9 India Launch – Fast is Now Beautiful!

I just can’t believe how lucky I am to be a part of history! Day 02 of the Tech Ed 2011 saw the India launch of the most awaited web-browser – The Internet Explorer 9! Brian Hall, General Manager of Windows Live and Internet Explorer products was at the venue to demonstrate the path towards a more beautiful web!

With full support of HTML 5 and CSS3, this is a completely new browser, that allows us (the end users) to pay more attention to the web-site and the overall browsing experience rather than the browser itself. The IE9 browser can leverage the CPU’s available power for better performance. The browser integrates seamlessly with Windows 7 and is able to make use of features like jumplists to allow for a highly productive web-experience.

Incidentally, the new JavaScript engine in the IE9 is called – “Chakra”! The world’s 2nd largest developer ecosystem (refer Mr. Qi Lu’s comment on the Indian IT industry during the opening keynote on Day 01) does have a deep influence.

Some of the sites where you can see the capabilities of IE9 in full bloom are http://www.snapdeal.com and http://www.flipkart.com. Go check them out!

Download IE9 from http://www.beautyoftheweb.com/

Test your browser against IE9 by using the test tools available at: http://ie.microsoft.com/testdrive/

IE6 countdown…

10 years ago, a browser was born. A browser that changed the browsing experience forever, and was so great that people still use it to this day! It was the Internet Explorer 6. Now, it is time for IE6 to die. Internet technologies have grown to such an extent that all of us need to work together to finally sunset IE6. Visit  http://www.ie6countdown.com/ to do the following:

1. Join the cause – Display the countdown timer on your website for IE6 users
2. Get a list of the reasons of why you absolutely should upgrade to IE9
3. Spread the message – about 15.8K tweets are already out as of writing this post – won’t you do your part?

Windows Azure – a solid cloud platform

After the power-packed launch of IE9, it was time for studying some case-studies on the practical implementations of Windows Azure. Bharat Shyam, General Manager, Windows Azure was at the event to showcase the success stories of Windows Azure. The audience was mesmerized when Bharat shared some of the biggest Azure installations. To name a few, Windows Live, Bing, Exchange Hosted Services, MSN website, Windows Hotmail and a lot of other commonly used sites and services are hosted on Azure.

Breakout Sessions – a mix of Core Dev & BI tracks

Day 02 was a mixed-bag for me. Personal interest in data access technologies and BI technologies made me attend sessions across tracks – some in the Core Dev and others in the BI track.

Core .NET Framework 4.0 enhancements every developer should know

Sanjay Vyas and Raj Chaudhuri, two of the leading independent experts (and MVPs) in the .NET area presented a very concise, but thorough session on the key enhancements of the .NET Framework 4.0.

What’s new in the Base Class Library?

1. Managed Extensibility Framework (MEF) – Declaration & consumption of extensibility points and monitoring of new runtime extensions
2. New Data types of BigInteger/ComplexNumber/Tuple and Sorted Set
3. I/O improvements – Memory mapped files

New Language Features

Data Access Methodologies – When to choose what (ADO.NET, Entity Framework, LINQ, WCF Data Services)

Post lunch, Wriju Ghosh gave a concise pill on Data Access Methodologies explaining when and how to choose between ADO.NET, Entity Framework, LINQ and WCF Data services.

Switching gears with BI

After some core Dev sessions, it was time to join the BI track by attending the following sessions:

1. Building Semantic Models in SQL Server “Denali” – Rushabh B. Mehta (President, PASS and MD, Solid Quality India Pvt. Ltd)
2. SSAS – Designing, Development & Deployment Best Practices – Amit Bansal
3. Internal and working of DAX with Power Pivot – Rushabh B. Mehta

Power Pivot, since it’s release has attracted me for it’s simplicity, and Rushabh’s session was full of pointers to make Power Pivot a very powerful self-service BI tool.

Closing time – Demo Extravaganza!

As on Day 01, the final session of the day was an action-packed show of the sheer power that Microsoft’s technologies possess. From games that one can make in under 3 minutes to calculating the distance between Bengaluru and Hyderabad (venue, Tech-Ed 2009) using SQL Server Spatial databases – this session had it all. The audience never batted an eyelid, and we had a perfect end to another perfect day!

Stay tuned for a detailed account of excitement on the final day of Tech-Ed 2011 (India)!

Until we meet next time,

Be courteous. Drive responsibly.

Last week, from March 23-25, 2011 were amongst the 3 most important and exciting days for anyone remotely connected with Microsoft technologies. They were the days when the Tech-Ed 2011 (India) was hosted by Microsoft at the Lalit Ashoka Hotel in Bengaluru, Karnataka.

Microsoft Tech-Ed 2011

Microsoft Tech-Ed is the premier technical education and networking event for tech professionals interested in learning, connecting and exploring a broad set of current and soon-to-be released Microsoft technologies, tools, platforms and services.This year, Tech-Ed 2011 had been divided into a series of multiple tracks mentioned below:

1. Developers
2. Infrastructure Professionals – Focused on solutions that save time, reduce costs and provide an efficiently managed data center
3. Architects – Focus is on bringing in uniqueness and offering niche topics
4. Management – Focused on innovating and reinventing oneself to win the dreams that have been built over the years
5. Interoperability – The focus here is on how businesses today can seamlessly interoperate and integrate with multiple vendor systems, technologies and applications using highly flexible Microsoft technologies
6. Information workers – These sessions focus on how Microsoft hardware and software can help us lead a more productive and comfortable lifestyle
7. Chalk Talks – conducted for the first time in Tech-Ed, these are “free-style” sessions making them a great place to have a great dialog and Q&A with the who’s who of Microsoft technologies

We, being developers, are ultimately most interested in the Developers track, which was further divided into:

1. The Next Web
2. Client Development
3. Database Development
4. Unleash a more beautiful web
5. Core Dev
6. Cloud Services and the Windows platform
7. Business Intelligence
8. Visual Studio Tools
9. Sharepoint Development

Read more about Tech-Ed 2011 (India) at http://www.microsoft.com/india/teched2011/

Through the next couple of posts, I will be sharing with you my experience on the wonderful time I had at the Tech-Ed event.

Opening Keynotes

Tech-Ed 2011 began by the registration of what I believe to be a crowd of at least 3000 participants. Once the registration was complete, we all started to proceed towards the main keynote area for the keynotes.

On Day 01, we were lucky to have keynotes from esteemed leaders like Mr. Qi Lu, President, Microsoft’s Online Services Division and Mr. Yousef Khalidi a Distinguished Engineer (Windows Azure) at Microsoft.

Opening his keynote session, Mr. Qi Lu appreciated India’s IT workforce as the world’s 2nd largest developer ecosystem. His session was focused on how this developer ecosystem can contribute to the making of a new web and how Microsoft’s online division is working towards this goal by developing new ways to interact and providing intelligence through services.

He introduced the concept of the consumer cloud – one that is an online service which can understand user intent using natural user interfaces like the Microsoft Kinect to enable task completion by local commerce and personal relevance. He demonstrated how Microsoft’s Bing has the vision to usher in a new cloud organizing paradigm by combining the Web of pages and the Web of people for better target-ability, measurability and predictability of the search results.

Mr. Yousef Khalidi focused on how we have grown and come almost a full circle as we moved from centralized, thin clients to distributed computers and now on to large data centers running scaled-out commodity hardware. He explained how Windows Azure is an example of Platform-as-a-Service (PaaS). However, the best take-away from his session was the definition of a “cloud” – A cloud is a style of computing provided as a service over the Internet using dynamically scalable, virtualized hardware and resources.

By the end of the session, I was left with great surprise for Windows Azure after learning that prime-time applications like Windows Gaming, Bing Twitter, Docs.com and Microsoft Hohm use Windows Azure.

Post lunch – Sessions on Database Development

After the lunch at the pool-side (being at a Microsoft event has it’s benefits), it was time for the power-packed break-out sessions.

The first of these sessions was from Vinod Kumar (blog) on the ““Unexplained” of SQL Server Security”.

Vinod demonstrated the secure SQL Server login process using the Microsoft’s Network Monitoring tool (download here). He showed the audience that not for a single handshake is the user password being exchanged over the wire. Later, he took the audience for a deep dive into SQL Server security by demonstrating how permissions can be restricted to a particular group of columns in a table to restrict the user of SELECT * and how one can use Policy Management to implement SQL Server security. He also demonstrated how auditing can be made secure and system provided an ability to detect an attempt to intrude via impersonation by the use of SQL Server certificates.

After Vinod’s session into the internals of Microsoft SQL Server, we had a session on “SQL Server Denali – What is new for Database Developers” by Prachi Bora, a Program Manager at Microsoft India R&D. She covered some of the exciting new features of SQL Server “Denali”, which include:

1. Statistical Semantic Search
2. “Surrounds With” feature
3. Substring matching in Intellisense
4. Conditional Breakpoints

After the tea break…”What did I do?”

After the tea break, we had a wonderful session on the worst T-SQL practices by none other than our very own SQL Server MVP – Mr. Jacob Sebastian!

The hall was packed with more than 200 attendees, and everyone participated whole-heartedly as Jacob ran through some of the most common T-SQL worst practices that he has seen in T-SQL code over the years. Everyone in the hall was, I am sure, recalling the various lines of code that they would need to go back and change as soon as they resumed office on Monday! Jacob highlighted the various performance and computational issues that might arise due to a simple slip of the mind on account of the developer attempting to finish off a task in a hurry. The session has left such an impression that I will, for my record, be writing a detailed post on the various points highlighted by Jacob. Stay tuned!

After Jacob’s session, it was a once-in-a-lifetime opportunity for all present in the hall at the time as Mr. Prem Mehra, a program manager with the SQLCAT (SQL Customer Advisory Team – http://sqlcat.com) at Microsoft in Redmond stepped up on stage. The honourable Mr. Mehra has over 49 years of experience in the IT industry, and it was simply mesmerizing to watch him talk about some of “The Largest, Mission-Critical Deployments of Microsoft SQL Server around the world!” He talked about some SQL Server deployments spanning leading US insurance providers, health-care organizations, financial institutions, banks and betting web-sites.

During the session, someone asked Mr. Mehra a question regarding the comparison of Microsoft SQL Server with the competition in terms of scalability, high availability and reliability. In response, Mr. Mehra simply presented the following slide and left the audience to judge for themselves. For me, it was a proof of something I have always believed – the product we love, Microsoft SQL Server, is simply the best!

Closing time – Demo Extravaganza!

As the evening set in, it was time to end the day on an action-packed, excited note. We gathered back into the main keynote hall, where we had a half hour of the most electrifying demos – all completed in less than 3 minutes each! What a magical atmosphere had been created – almost as if it was Hogwarts and Harry Potter himself was presenting!

Stay tuned for a detailed account of excitement on Tech-Ed 2011 (India) Day 02!

Until we meet next time,

Be courteous. Drive responsibly.

This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

The UPDATE Statement

Today, I will attempt to approach describing the OUTPUT clause in a slightly different manner. We will simply forget about it for a moment. We will talk about something much more simple and well-known like the UPDATE.

But, the UPDATE statement is not that simple – it’s actually two statements rolled into one – a DELETE and an INSERT are actually wrapped into a single statement.

Does that sound familiar? I am sure that you would have heard about the two special tables that SQL Server provides – the DELETED and INSERTED tables (Reference: http://msdn.microsoft.com/en-us/library/aa214435(SQL.80).aspx). These tables are used within triggers to:

• Extend referential integrity between tables
• Insert or update data in base tables underlying a view
• Check for errors and take action based on the error
• Find the difference between the state of a table before and after a data modification and take action(s) based on that difference

Here’s a quick example:

USE AdventureWorks2008R2
GO
--Step 01: Create two tables - the test table, and an Audit table
CREATE TABLE MyTABLE (MyId INT, MyCity VARCHAR(20))
CREATE TABLE MyAudit (MyChangedId INT, OldCityValue VARCHAR(20), NewCityValue VARCHAR(20), ModificationDate DATETIME)
GO

--Step 02: Insert some test data into these tables
INSERT INTO MyTABLE VALUES (1,'Gandhinagar'),
                           (2,'Ahmedabad'),
                           (3,'Mumbai'),
                           (4,'Delhi'),
                           (5,'Bangalore')
GO

--Step 03: Create a test trigger to demonstrate the most typical use of DELETED & INSERTED tables
CREATE TRIGGER trig_MyTable
ON MyTABLE
FOR UPDATE
AS
BEGIN
    INSERT INTO MyAudit (MyChangedId, OldCityValue, NewCityValue, ModificationDate)
                SELECT [DELETED].[MyId],
                       [DELETED].[MyCity],
                       [INSERTED].[MyCity],
                       GETUTCDATE()
                FROM [DELETED] [DELETED]
                INNER JOIN [INSERTED] [INSERTED] ON [DELETED].[MyId] = [INSERTED].[MyId]
END
GO

--Step 04: Perform a test update
SET STATISTICS IO, TIME ON

UPDATE mt
SET mt.MyCity = 'New Delhi'
FROM MyTABLE mt
WHERE mt.MyId = 4

SET STATISTICS IO, TIME OFF

--Step 05: Take a quick look at the data
SELECT MyId, MyCity FROM MyTABLE
SELECT MyChangedId, OldCityValue, NewCityValue, ModificationDate FROM MyAudit

--Step 06 - Perform some cleanup
DROP TABLE MyTABLE
DROP TABLE MyAudit

Upon running these scripts, you would notice that this is quite an reasonable method to maintain a custom log of critical transactions/queries.

The OUTPUT Clause

However, it might be possible that sometimes, you may not want to use triggers (because of personal choice, or because of the fact that triggers can be disabled); or this is a one-off debugging exercise or just an effort to learn the internals of SQL Server. In such cases, the recently introduced OUTPUT clause can be very useful.

USE AdventureWorks2008R2
GO
--Step 01: Create two tables - the test table, and an Audit table
CREATE TABLE MyTABLE (MyId INT, MyCity VARCHAR(20))
CREATE TABLE MyAudit (MyChangedId INT, OldCityValue VARCHAR(20), NewCityValue VARCHAR(20), ModificationDate DATETIME)
GO

--Step 02: Insert some test data into these tables
INSERT INTO MyTABLE VALUES (1,'Gandhinagar'),
                           (2,'Ahmedabad'),
                           (3,'Mumbai'),
                           (4,'Delhi'),
                           (5,'Bangalore')
GO

--Step 03: Perform a test update
SET STATISTICS IO, TIME ON

UPDATE mt
SET mt.MyCity = 'New Delhi'
    OUTPUT [deleted].[MyId], [deleted].MyCity, [inserted].MyCity, GETUTCDATE()
    INTO MyAudit
FROM MyTABLE mt
WHERE mt.MyId = 4

SET STATISTICS IO, TIME OFF

--Step 04: Take a quick look at the data
SELECT MyId, MyCity FROM MyTABLE
SELECT MyChangedId, OldCityValue, NewCityValue, ModificationDate FROM MyAudit

--Step 05 - Perform some cleanup
DROP TABLE MyTABLE
DROP TABLE MyAudit

What’s the benefit?

Drastically reduced & simplified code, and yet we get the same results. At the end of the day, it’s all about the benefits of a particular approach. So, do we have any improvement? We had the I/O and TIME statistics ON during the UPDATE statements. Let’s see what stories they tell us.

Here’s the output from the conventional trigger-based approach.

Table ‘MyTABLE’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

SQL Server parse and compile time:

Table ‘MyAudit’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 120 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 131 ms.

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

And here’s the same from the OUTPUT clause:

SQL Server parse and compile time:

   CPU time = 10 ms, elapsed time = 15 ms.

Table ‘MyTABLE’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 58 ms.

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

While there is no difference in the I/O (1 scan, 2 logical reads), the execution time has definitely improved with a total of (8+120+131) ms = 159ms with the trigger based approach v/s (15+58) ms = 75ms with the OUTPUT clause.

Finally, the OUTPUT clause can be used with all 3 of the major T-SQL statements – UPDATE, INSERT and DELETE!

Some finer points

The OUTPUT clause does not support:

1. Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound
2. A remote table, view, or common table expression as it’s target
3. A target with a FOREIGN KEY constraint, or referenced by a FOREIGN KEY constraint
4. Triggers on the target
5. A target participating in replication

Besides the above, be careful with the behaviour of @@ROWCOUNT. It will only return the number of rows affected by the outer INSERT statement.

Lots more information can be obtained by visiting the Books-On-Line page at: http://msdn.microsoft.com/en-us/library/ms177564(v=SQL.110).aspx

In Conclusion – a small challenge

Now that you are powered with the knowledge of the OUTPUT clause, can you answer the following Question of the Day at SQLServerCentral?

http://www.sqlservercentral.com/questions/T-SQL/72013/

Do leave a note as to how you did, and what are your thoughts on the same.

Until we meet next time,

Be courteous. Drive responsibly.

This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Did you ever wonder if you could create a temporary result set and use that within a single SELECT, INSERT, UPDATE or DELETE statement? I bet you have, and before SQL Server 2005 came along, I am sure that you used either sub-queries, cursors or temporary tables  to achieve the desired results.

Starting SQL Server 2005, we have a much better, and much more efficient method of achieving this – the common table expression, or the CTE. Much has been written about CTEs and their benefits, and hence I will be mostly presenting a summary of all the great articles and posts on the topic that I have chanced upon till date:

1. An introduction to CTEs – http://www.simple-talk.com/content/article.aspx?article=260
2. The things that you can do with a CTE – http://msdn.microsoft.com/en-us/library/ms175972.aspx
3. A few data points on CTE – http://msdn.microsoft.com/en-us/magazine/cc163346.aspx#S3

Something interesting – CTE in a CREATE VIEW statement

Yes, CTEs can be used in a CREATE VIEW statement as is demonstrated by the queries below:

USE AdventureWorks2008R2;
GO
CREATE VIEW vSalesCTE
AS
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
    AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    SELECT Sales_CTE.SalesPersonID, Sales_CTE.SalesOrderID, Sales_CTE.SalesYear
    FROM Sales_CTE;
GO

--Use the VIEW containing a CTE
SELECT vcte.SalesPersonID, COUNT(vcte.SalesOrderID) AS TotalSales, vcte.SalesYear
FROM vSalesCTE vcte
GROUP BY vcte.SalesYear, vcte.SalesPersonID
ORDER BY vcte.SalesPersonID, vcte.SalesYear

Don’t overestimate CTEs – don’t use them multiple times within the same query

As is the case with everything, CTEs in excess can also cause more harm than good. CTEs are more of “expandable” blocks of code, and hence, if you use them more than once in a query, you will end up with performance issues. This is because the entire data set is populated the number of times a CTE is used. For cases where you need to use the same data set again and again, use temporary tables or table variables instead.

I hope that CTE’s opened up a whole new programming paradigm for all. If you have not started experimenting with CTEs, I recommend that you start doing so – it will be fun.

Until we meet next time,

Be courteous. Drive responsibly.