Video: Itzik Ben-Gan on the new features of Microsoft SQL Server 2012 Denali

Home > Blogs > SQL Server > Video: Itzik Ben-Gan on the new features of Microsoft SQL Server 2012 Denali

Video: Itzik Ben-Gan on the new features of Microsoft SQL Server 2012 Denali

Like This Blog 0 Jeff Jones
Added by 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.

Videos You May Like

A Simple Introduction to Cisco CML2

0 3876 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Cable Testers and How to Use them in Network Environments

0 720 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Government Edition – Encrypting a USB Flash Drive in Windows 10

0 274 2

In this video, Security Instructor Mike Danseglio demonstrates how to use BitLocker in Window 10 to secure files on a USB Flash drive that adhere to stricter data protection requirements as found inside Government entities. BitLocker 2-day instructor-led training is now available at Interface: BITLOCK: Planning and Deploying BitLocker Drive Encryption Training Video Transcription: Hi. … Continue reading Government Edition – Encrypting a USB Flash Drive in Windows 10

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.