This is the seventh post in my retrospective attempt to answer every T-SQL Tuesday invitation.
In the beginning of June 2010, Jorge Segarra invited us to write about our favourite hot new feature in SQL Server 2008 or 2008 R2.
For me, this would be the introduction of the DATE, TIME, and DATETIME2 data types that were introduced in SQL Server 2008. I have written extensively on this website about these data types, most recently in September 2020. You can also read the other posts about dates and times in SQL Server by visiting the Blog Series page.
So, instead I would argue that most — if not all — of the features in SQL Server 2008 were important, and we might even take them for granted today. This list comes from an archive of the official Microsoft Docs page for SQL Server 2008, dated September 13, 2008.
- Compressed storage for tables and indexes – While this was an Enterprise only feature until SQL Server 2016 with Service Pack 1, data compression has added a new dimension to data storage for SQL Server, and it all started here.
- FILESTREAM storage – This introduced a way to store transactionally-consistent data outside of the database engine on the underlying file system, dramatically reducing the storage requirements for data file management.
- New collations – Not as dramatic as the SC collations in SQL Server 2012, but this release introduced nearly 100 new collations that matched up with those available in Windows Server.
- Partition switching – While not a performance feature, table and index partitioning allows easier management of massive tables, and partition switching allows us to add or remove subsets of that partitioned table using a metadata-only operation, which is practically instantaneous.
- Sparse columns and column sets – If I am not mistaken, this feature is used in SharePoint. I honestly haven’t used this feature myself apart from seeing how it worked, but it was ostensibly designed to store tables with a large amount of NULL values more efficiently. Column sets were an XML representation of all sparse columns in a table.
- Wide tables – This was the feature that enabled column sets, providing as many as 30,000 columns in a table. Michael J. Swart will suggest that if you need to store that many columns in a table then you have bigger problems, but again I suspect this was to enable features for SharePoint.
- Spatial data and data types – Here is a set of features I have definitely used. GEOGRAPHY (ellipsoidal, or round-earth) and GEOMETRY (Euclidean, or flat-earth) data type support directly in the database engine allows for not only storing points, linestrings, and polygons, but also performing spatial queries on them. Aside from drawing CPU load graphs, it is useful for storing and querying location data, calculating distances between two points, and so on.
- The
hierarchyiddata type – I admit this one has been a little tricky to get my head around, even though I’ve used it in several database designs; I have to go back to the documentation each time. We can use hierarchyid to store and query hierarchical data such as organizations, file systems, project structures, and so on. Normally if I use it it’s only in one or two tables, and in other cases I can avoid it completely by using a ParentId column with a foreign key relationship on the primary key in the same table. - User-defined table types – This feature I’ve used quite a bit. You can set up your own custom table definition as a type, and pass that around as a temporary table definition (which can also be used as a parameter in a stored procedure). Of course, performance will be affected with larger tables, so use this with care. There’s a good explainer here by Jamie O’Donoghue.
What is your favourite feature from SQL Server 2008 or 2008 R2? For example, I didn’t even mention things like Transparent Data Encryption. Sound off in the comments.