Like This Blog 0

Added by Jeff Jones November 23, 2011

Interface Technical Training hosted a very special Advanced T-SQL course with SQL MVP Itzik Ben-Gan on October 17-21, 2011.

We sat down with Mr. Ben-Gan to get his thoughts on various topics related to Transact SQL. In this video, Itzik shares his insights on the new features of SQL Server 2012 codename “Denali”.

Below is the transcription of the video.

Jeff Jones: You mentioned that SQL Server 2012 was just announced and your class will cover a lot of the new functionality. There seems to be a lot of new T-SQL in this particular release, which is great, and you’re going to spend some time on that to get people a good understanding.

Itzik Ben-Gan: Correct, the course isn’t really version-specific so it’s not like the course is SQL Server 2012 focused. It covers versions 2005, 2008 and 2012. Some of the language features in 2012 are quite profound and some very common problems are now solvable with the unbelievable performance in the newest version.

Classic examples are things like ‘running totals’.

Set-based solutions up to SQL Server 2008 R2 were treated very badly by the optimizers, so simply running totals with classic set-based solutions that use either subqueries or joins was called in computer science ‘algorithmic complexity’ or ‘quadratic scaling’ and ‘square scaling’, where the number of rows that you’re dealing with increase by a factor of say ‘f’ then the work involved increases by a factor of ‘f-squared’.

So if you think about this, once you get into tables that aren’t necessarily very large – for example, take a table that is 100,000 rows, now square that number and try to imagine how large it will become. That’s a lot of rows to get processed. The ironic thing is the classic set-based solutions perform so badly that people have to resort to cursors or other means that are not really supported.

So with SQL Server 2012, I think some of the most important features in the language are features known as ‘Window Functions’. While it was already implemented in a partial manner in SQL Server 2005, the critical parts needed to solve these kind of problems efficiently – such capabilities in aggregate Window Functions that include what’s called ‘ordering and framing options’ – now allow us to solve running totals and many similar types of problems very eloquently, very simply and with linear scaling. They can simply do one pass over the data.

I tested it with a query that had 2 million rows. Using a classic set-based solution, it would take a matter of years before the subquery would be finished. But now, it’s a matter of something like 7 seconds for the very same query to complete one pass over the data.

And that’s just part of the enhancements in SQL Server 2012. There are number of enhancements that relate to these profound Windows Functions, including additional functions, such as offset functions called leg, lead, first-value, last-value and statistical functions. The most important are the improvement in the aggregate Window Functions.

Then there are other new functionalities. There’s a new filtering option called Offset-Fetch that allows improved paging capabilities similar to ‘Top’ but it also has skipping capabilities. Where ‘Top’ allows you to restrict such and such rows, Offset-Fetch allows you to skip rows and now fetch certain rows. So it can do simple paging concepts.

Sequence object allows us to autogenerate keys but with an object that is not tied to a particular column inside a table like the identity attribute. So we get into Sequence object advantages over the existing identity solutions, as well as other enhancements. These are some of the key enhancements in SQL Server 2012.

Jeff Jones: So if you’re trying to figure out why you would want to do an upgrade to SQL Server 2012, you are going to be able to show people the tremendous benefits of that upgrade?

Itzik Ben-Gan: Certainly, there are some features that ‘no doubt’ that will provide a big justification to upgrade.

Upcoming live Microsoft SQL Server classes at Interface Technical Training:
SQL100: Introduction to Transact-SQL
SQL250: Transact-SQL for Developers
SQL400: Itzik Ben-Gan’s Advanced T-SQL Querying, Programming and Tuning for SQL Server 2005, 2008 and 2012
6231R2: Maintaining a Microsoft SQL Server 2008 R2 Database
6232R2: Implementing and Maintaining Microsoft SQL Server 2008 R2 Database
BI6234: Implementing and Maintaining Microsoft SQL Server 2008 Analysis Services
BI6235: Implementing and Maintaining Microsoft SQL Server 2008 Integration Services
BI6236: Implementing and Maintaining Microsoft SQL Server 2008 Reporting Services

Is there a DrillBit™ Video you would like to see?
Let us know in the comments section below. If it’s a popular technical problem, we’ll make a DrillBit™ video with the solution.