SQL Server 2008 has a ton of new DBA features, but if you really want to make this thing go, just crank out a little code.
SQL Server 2008 is mostly in the domain of system and database administrators. But it's also a repository for data used by applications, which brings the product to those who aren't afraid of a little code. This new release has a lot of goodies that support application development directly. I'll walk you through several of the new and improved features that I believe are most useful and interesting for the code savvy.
Management Studio Gets Several Enhancements
Since SQL Server 2005, Management Studio has been an extended version of Visual Studio. In 2005, the implementation was useful but a bit half-baked. In SQL Server 2008, Microsoft has made Management Studio a worthy environment for both administrators and developers. (Members of both groups who prefer command-line interfaces can also use the extensible Windows PowerShell.) Many features will be familiar to users moving to SQL Server 2008, but there are also many new features that make working with SQL Server much easier. For coders, probably the nicest new feature in Management Studio is IntelliSense. Long a staple in Visual Studio, IntelliSense lets you write code in a Query Editor Window and reduce the number of times you have to go to Books Online to look up syntax or spelunk Object Explorer to find the name of that stored procedure you need. IntelliSense in SQL Server 2008 works largely as it does in VS, providing you with a list of objects and methods as you type code.
The second-most-coveted new feature in Management Studio is T-SQL debugging. You can now debug code directly from within Management Studio, which provides all of the features you expect, including the ability to step through code, view and change local variables, watch expressions and set breakpoints.
Debugging in Management Studio is nowhere as deep as it is in VS, but it is functional and provides a lot of debugging tools. Of course, you can still enter a T-SQL debugging session from within VS, which means you can have the best of both worlds.
Object Explorer Window Now Useful
Management Studio's Object Explorer has long provided a nice view into the many persistent and virtual objects in a database and server. But the Object Explorer Details window, which by default appears to the right of Object Explorer when you first start Management Studio, was less than useless in SQL Server 2005. For the most part, it just displayed the same list of objects shown in Object Explorer. The tab took up space, and many users simply closed the window.
In SQL Server 2008, Object Explorer Details often provides useful information, such as when you select the Databases node in Object Explorer. The views are highly customizable, letting you display exactly the information you find most useful.
Management Studio has a lot of other new features, and I discover more every day. Two more I recently discovered include the ability to query multiple servers by defining a server group, and the ability to configure the number of rows returned when opening a table to select or edit its contents.
2008 Adds New T-SQL Data Types
One of the sexiest new features in SQL Server 2008 is spatial data types. If you've ever worked with spatial data in a database, such as latitudes and longitudes or locations in a grid, you've probably developed your own types to support basic operations and conversions. It's not trivial code. But now, SQL Server 2008 has built-in support for two kinds of spatial-data systems. The geometry types support planar, or "flat-earth," coordinate data. The geography types store ellipsoidal data that stores locations on the earth's surface, a flattened sphere. Whether you're storing GPS data scattered around the globe, or need to store the coordinates that define complex shapes on a rectangular surface, you'll find a lot of features in these data types, along with dozens of useful methods.
It's common to store hierarchical data in a database, even though relational databases don't support hierarchies easily. You can create hierarchies with self-joins, but you generally need to do all the work. SQL Server 2008 introduces the HierarchyID data type, which greatly simplifies working with hierarchical data, complete with functions that make it easy to navigate hierarchies. It doesn't make data hierarchies a substitute for the native structure of XML data, but it does simplify operations. SQL Server maintains the structure of the data, supports random insertions and deletions, and supports location-based comparisons. You can index the data either breadth-first or depth-first, depending on the nature of the data and how your applications access it.
T-SQL Improvements
T-SQL in SQL Server 2008 hasn't received any major changes, but the new version includes many features that make code simpler and more efficient. There are a few syntax enhancements that developers will like, including a couple that make T-SQL seem more like a "real" programming language. You can now declare and initialize variables in a single statement.
One of my favorite new T-SQL features is table-valued parameters (TVPs). This one feature will single-handedly save you from a lot of ugly T-SQL code. Have you ever had to pass several pieces of data as a parameter to a stored procedure? Maybe it was a comma-delimited list or some other array-like structure. You'd have to write some nasty parsing code to split up the values, then probably use a loop to process the data. SQL Server 2005 introduced a table data type, but you couldn't pass it to a procedure.
TVPs solve these kinds of problems elegantly by letting you pass -- as the name suggests -- a table-valued parameter to the procedure or function. Then, in the body of the procedure, you can use the set-based features of SQL to process the data, such as by inserting it into a persistent table.
Administrator Features Help Developers
SQL Server 2008 is a server application, and most of its features are focused on making it robust no matter what kind of loads applications throw at it. It's chock-full of administrative-support features that make it incredibly easy to install, manage and secure the database. Usually, there's a difference between the features that administrators and developers are interested in or use during the course of a typical day, but there are a handful of administrative features in SQL Server 2008 that are useful to developers.
One such feature is partition switching. Developers and admins have long used table partitions to store subsets of data in various tables, usually for performance or data-storage reasons. A common scenario is to store each calendar year's worth of transactions in a separate table and put each of the tables in a different file group. You can create a UNION query to extract and summarize the data when you need to access all the data, such as to create a report that spans all time. This works, but it requires some work to set up and often requires modifying code when adding a new partitioned table when a new year begins. (There are lots of other ways to do this.) You can use this kind of scheme to archive old data while keeping it available for analysis.
With partition switching, you can add a table as a partition to another table that's already partitioned, remove partitioning to create a single aggregated table and switch a partition from one partitioned table to another. You could always set up your own scheme to implement these features, but in SQL Server 2008 you can perform these tasks using the ALTER TABLE and ALTER PARTITION statements. The data itself is not changed or moved. The only thing that changes is the metadata for where it's stored. There are a slew of requirements to make partition-switching work, but they basically boil down to the fact that all of the involved tables must be identical in nearly every way.
Full-text searching has long been a feature that held a lot of promise but never seemed to get traction. One of the reasons is that it always seemed like an add-in that wasn't fully implemented. But with SQL Server 2008, full-text search is completely integrated into the database instead of being stored externally. Portions of full-text indexing and querying are now integrated into the query optimizer, so performance is much better, and there are more tools to extract useful data from the database. You might want to consider dumping all that gnarly T-SQL code you wrote over the last decade to give users flexible searches into their data and implement full-text searches instead.
SQL Server 2008 supports Windows PowerShell, an enhanced, extensible scripting shell interface for developers and administrators who love the command line. SQL Server includes two PowerShell snap-ins that expose the hierarchy of database and server objects as paths (similar to file-system paths). On the surface, this sounds a bit like an abomination, but it can simplify getting around the database object model. Another snap-in implements a set of PowerShell cmdlets for performing a variety of actions, such as running sqlcmd scripts. PowerShell's a powerful tool, but if you love your mice and GUIs, you can opt not to use it.
SQL Server 2008 offers a lot to love for a developer. It isn't a revolutionary release, but it has enough great features to make it a slam-dunk upgrade as soon as your neighborhood system and database administrator lets you.
There's a ton of new stuff to learn in SQL Server 2008, though, so be careful to get up to speed on what's new and different.
Management Studio Gets Several Enhancements
Since SQL Server 2005, Management Studio has been an extended version of Visual Studio. In 2005, the implementation was useful but a bit half-baked. In SQL Server 2008, Microsoft has made Management Studio a worthy environment for both administrators and developers. (Members of both groups who prefer command-line interfaces can also use the extensible Windows PowerShell.) Many features will be familiar to users moving to SQL Server 2008, but there are also many new features that make working with SQL Server much easier. For coders, probably the nicest new feature in Management Studio is IntelliSense. Long a staple in Visual Studio, IntelliSense lets you write code in a Query Editor Window and reduce the number of times you have to go to Books Online to look up syntax or spelunk Object Explorer to find the name of that stored procedure you need. IntelliSense in SQL Server 2008 works largely as it does in VS, providing you with a list of objects and methods as you type code.
The second-most-coveted new feature in Management Studio is T-SQL debugging. You can now debug code directly from within Management Studio, which provides all of the features you expect, including the ability to step through code, view and change local variables, watch expressions and set breakpoints.
 [Click on image for larger view.] |
| Management Studio now includes IntelliSense and syntax-error squigglies, features that will make developers far more productive when writing SQL code. |
Object Explorer Window Now Useful
Management Studio's Object Explorer has long provided a nice view into the many persistent and virtual objects in a database and server. But the Object Explorer Details window, which by default appears to the right of Object Explorer when you first start Management Studio, was less than useless in SQL Server 2005. For the most part, it just displayed the same list of objects shown in Object Explorer. The tab took up space, and many users simply closed the window.
In SQL Server 2008, Object Explorer Details often provides useful information, such as when you select the Databases node in Object Explorer. The views are highly customizable, letting you display exactly the information you find most useful.
Management Studio has a lot of other new features, and I discover more every day. Two more I recently discovered include the ability to query multiple servers by defining a server group, and the ability to configure the number of rows returned when opening a table to select or edit its contents.
2008 Adds New T-SQL Data Types
One of the sexiest new features in SQL Server 2008 is spatial data types. If you've ever worked with spatial data in a database, such as latitudes and longitudes or locations in a grid, you've probably developed your own types to support basic operations and conversions. It's not trivial code. But now, SQL Server 2008 has built-in support for two kinds of spatial-data systems. The geometry types support planar, or "flat-earth," coordinate data. The geography types store ellipsoidal data that stores locations on the earth's surface, a flattened sphere. Whether you're storing GPS data scattered around the globe, or need to store the coordinates that define complex shapes on a rectangular surface, you'll find a lot of features in these data types, along with dozens of useful methods.
 [Click on image for larger view.] |
| The Object Explorer Details window lets you search for objects within a database or across all databases on a server using a wildcard search. |
T-SQL Improvements
T-SQL in SQL Server 2008 hasn't received any major changes, but the new version includes many features that make code simpler and more efficient. There are a few syntax enhancements that developers will like, including a couple that make T-SQL seem more like a "real" programming language. You can now declare and initialize variables in a single statement.
One of my favorite new T-SQL features is table-valued parameters (TVPs). This one feature will single-handedly save you from a lot of ugly T-SQL code. Have you ever had to pass several pieces of data as a parameter to a stored procedure? Maybe it was a comma-delimited list or some other array-like structure. You'd have to write some nasty parsing code to split up the values, then probably use a loop to process the data. SQL Server 2005 introduced a table data type, but you couldn't pass it to a procedure.
TVPs solve these kinds of problems elegantly by letting you pass -- as the name suggests -- a table-valued parameter to the procedure or function. Then, in the body of the procedure, you can use the set-based features of SQL to process the data, such as by inserting it into a persistent table.
Administrator Features Help Developers
SQL Server 2008 is a server application, and most of its features are focused on making it robust no matter what kind of loads applications throw at it. It's chock-full of administrative-support features that make it incredibly easy to install, manage and secure the database. Usually, there's a difference between the features that administrators and developers are interested in or use during the course of a typical day, but there are a handful of administrative features in SQL Server 2008 that are useful to developers.
One such feature is partition switching. Developers and admins have long used table partitions to store subsets of data in various tables, usually for performance or data-storage reasons. A common scenario is to store each calendar year's worth of transactions in a separate table and put each of the tables in a different file group. You can create a UNION query to extract and summarize the data when you need to access all the data, such as to create a report that spans all time. This works, but it requires some work to set up and often requires modifying code when adding a new partitioned table when a new year begins. (There are lots of other ways to do this.) You can use this kind of scheme to archive old data while keeping it available for analysis.
With partition switching, you can add a table as a partition to another table that's already partitioned, remove partitioning to create a single aggregated table and switch a partition from one partitioned table to another. You could always set up your own scheme to implement these features, but in SQL Server 2008 you can perform these tasks using the ALTER TABLE and ALTER PARTITION statements. The data itself is not changed or moved. The only thing that changes is the metadata for where it's stored. There are a slew of requirements to make partition-switching work, but they basically boil down to the fact that all of the involved tables must be identical in nearly every way.
Full-text searching has long been a feature that held a lot of promise but never seemed to get traction. One of the reasons is that it always seemed like an add-in that wasn't fully implemented. But with SQL Server 2008, full-text search is completely integrated into the database instead of being stored externally. Portions of full-text indexing and querying are now integrated into the query optimizer, so performance is much better, and there are more tools to extract useful data from the database. You might want to consider dumping all that gnarly T-SQL code you wrote over the last decade to give users flexible searches into their data and implement full-text searches instead.
 [Click on image for larger view.] |
| The MERGE statement lets you insert, update and delete data in a table with a single statement. |
SQL Server 2008 offers a lot to love for a developer. It isn't a revolutionary release, but it has enough great features to make it a slam-dunk upgrade as soon as your neighborhood system and database administrator lets you.
There's a ton of new stuff to learn in SQL Server 2008, though, so be careful to get up to speed on what's new and different.