Introducing SQL Server 2008

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
09:47

Hello and welcome to another SQL Server Video, my name is Michael Campbell and in this video we're going to take a very high-level look at some of the features and benefits of SQL Server 2008.

And along the way we'll outline some of the upcoming videos in this series - which are designed to help you better take advantage of these features and changes in day-to-day operations.

And the first thing we should probably cover when it comes to SQL Server 2008 is that it was effectively designed to build upon, or augment, existing capabilities found within SQL Server 2005.

Of course, it's still an upgrade and switching to it will take some effort - but the key thing to note is that with the exception of just a few discontinued features, most organizations should be able to upgrade to SQL Server 2008 very easily.

So, with that in mind, two of the videos in this series will address both a high-level overview of the upgrade process, along with a more hands-on look at the way to check for discontinued and deprecated code.

Of course, that all begs the question of whether or not there are significant enough improvements within SQL Server 2008 in order to want to upgrade. And, if you're working with a system that's running on SQL Server 2000 that still sees active development or requires lots of maintenance then the answer is a resounding yes.

And, frankly, even if you've got a very stable and, effectively, legacy application running on SQL Server 2000, you'll still see some decent performance gains by migrating over to SQL Server 2008 which only improves upon some of the performance benefits delivered by SQL Server 2005.

But what about applications and solutions already running on SQL Server 2005? Are there any benefits to upgrading in those environments? From a technical perspective the answer is also a resounding yes - though, obviously, that answer may be tempered by other considerations as well.

So, with that in mind, let's switch gears by working through a high-level overview of new features and functionality in SQL Server 2008 by audience type - and then we'll conclude with a review of the features that virtually everyone using SQL Server 2008 can take advantage of.

For developers there are a number of exciting new additions to SQL Server - most of them subtle, but all of them very are cool and beneficial.

On the lower end of the scale are some great new T-SQL improvements - including things like the ability to declare and set variable values within a single statement, along with T-SQL row constructors - which give you the ability to INSERT multiple rows into a table using a single statement - by letting you specify multiple VALUES um... values.

And there's also a whole new slew of assignment operators that help make T-SQL feel a bit more like a modern programming language.

SQL Server 2008 also introduces a whole new bevy of date and time datatypes. For example, a new datetime2 datatype provides greater storage ranges and temporal precision than existing datetime datatypes today - and its bigger cousin, the DateTimeOffset datatype affords this same increased storage capability along with the inclusion of timezone awareness.

And, in SQL Server 2008 we finally get plain old date and time datatypes - which is something that we've been asking for for quite a while.

In addition to these date/time types, there are also two new data types that take advantage of CLR functionality in order to represent spatial and geometric data such as polygons, intersections, and geographic locations, distances, and the likes.

Likewise, a new hierarchyID datatype makes it very easy to keep track of interrelated data both by virtue of its storage structure and through the use of built in functions provided by the data type itself.

One of my favorite new features, table-valued parameters, allows us to now, effectively, pass table variables as parameters into sprocs. And not only can this help boost developer productivity when it comes to dealing with nested sprocs, but you can also leverage this functionality from within ADO.NET - meaning that it's now possible to pass in arrays or lists of data as a single parameter from your applications.

Likewise, additional improvements help make CLR extensibility more robust and viable and there are additional improvements in terms of XML interaction and a couple of other key T-SQL enhancements as well.

And in this series of videos we'll look at nearly all of these changes and improvements in step-by-step tutorial videos that show you what problems these changes were designed to solve, and then which show you how to go about getting started when it comes to working with these new improvements.

For DBAs, there is obviously the benefit of everything I just mentioned, but SQL Server 2008 also provides some great new features and functionality that you'll love as well - including a cleaner and more streamlined setup process that gives you great control over configuration, security, and setup options.

SQL Server 2008 also gets rid of that nasty thing known as the Surface Area Configuration Manager - which always struck me as such a hack given that you should be able to manage security settings from within SQL Server itself - and now, happily, you can.

There are also some great new engine enhancements including improved storage options and full-on integration with SQL Server Full-Text Indexing which can help make some security troubleshooting a tiny bit easier.

Other enhancements include improved security management and integration, along with great new options for compressing data at row, page, and backup levels.

Likewise, core SQL Server components such as replication, and service broker have also seen some improvements - and there have also been some enhancements to the way logging and recovery work to help make things more available or stable.

And speaking of availability, database mirroring has been significantly tweaked to make it more robust and less bandwidth intensive, and SQL Server 2008 also offers hot-pluggable CPU functionality as well - though I probably won't be doing a video on that one given the amount of hardware I'd need to demonstrate that.

DBAs will also enjoy improved monitoring capabilities including an entirely new troubleshooting infrastructure known as extended events - which provides some insanely robust tracing and even auditing capabilities while keeping overhead very low and performant.

Management has also seen some HUGE improvements and changes with the introduction of policy based management and the inclusion of capabilities that allow DBAs to powerfully partition and throttle access to server resources using the resource governor. Another exciting advance is the integration of PowerShell, which will be covered in a series of its own.

Otherwise, there's also a number of exciting new capabilities that can be used to boost and improve performance from a number of angles - including new engine enhancements such as sparse columns and filtered indexes.

Plan Guides - which were originally introduced in SQL Server 2005, have actually now become mature enough to actually use - and offer some really exciting possibilities for DBAs who need to tune and tweak in environments where they don't have control over underlying code.

And, of course, we'll look at most of these features in upcoming videos.
For SQL Server users taking advantage of data warehousing, reporting, and other BI-centric capabilities, there are also a number of great improvements in SQL Server 2008 - including some great new changes and additions to SQL Server Integration Services, such as improved performance for lookup transformations, a better scripting environment and improved data flow enhancements, along with a whole new solution used to ensure data quality called data profiling.

Likewise, distinct ETL improvements - above and beyond the SQL Server Integration Services changes include the new MERGE statement - which is frequently referred to as the UPSERT command as it's on par with INSERT and UPDATE and lets you define what to do if row data is matched or not found. And there's also change data capture - which, like the MERGE statement, was specifically targeted for BI users, but can easily be used by developers and DBAs in other use cases as well.

Additionally, there are a host of new improvements for Data Cubes and SQL Server Analysis Services in general. Too many, frankly, to go over in this video. But suffice it to say that if you're big on BI, then SQL Server 2008 is likely something you should definitely look into - assuming that you haven't already done so.

Otherwise, in a subsequent video we'll look at some of the improvements that pretty much everyone can take advantage of with SQL Server 2008 - by looking at both IntelliSense and some of the bigger changes to SQL Server Management Studio - where there are a couple of cool new improvements as well.

And of course, in a whirlwind tour of features like we've just gone through, it wasn't possible for me to address everything that's new or changed in SQL Server 2008. So, if you're looking for a comprehensive list, then you'll want to check out books online - which you can access, um... well, online at the link specified (though I've also provided a matching link on the references page for this video).

Otherwise, to see what other videos have been added to this series on SQLServerVideos.com, you can navigate to the url specified, and to stay updated on any newly released videos, make sure to sign up for SSV's RSS feed at updates.sqlservervideos.com.

And, with that, we're done with this video - thanks for joining and we look forward to seeing you in subsequent SQL Server Videos.