Review: SQL Server 2012 stands tall
Huge HA and indexing improvements top the list of great new features in Microsoft's latest database release
T-SQL doesn't have many new features, but the ones it has are cool enough. My favorites are the new LAG and EOMonth windowing functions. LAG gives each row in your result set access to the column data in the previous row. So say you've got a price column and you want to be able to easily display the current price and the previous price in the same row. LAG will let you do this. EOMonth is a function that lets you have instant access to the last day of the month for the value passed into it. There are other new functions, but I'll have to leave them to you to discover on your own.
Among all the T-SQL enhancements, FileTable is probably the coolest feature there is. Basically, it's file stream data that can be accessed directly from the file system. Let me explain. File stream allows you to store documents on the file system, but they're backed up with the database so you can make sure they're protected. FileTable takes this one step further. FileTable makes the line between database and file system transparent. You start by defining a table as a FileTable and assigning it a directory on the file system. Now all you have to do to put files in the table is just drop them into the folder from Windows Explorer. There's no T-SQL to write, nothing else special to do. You just manage the files at the file system level like you always have and they get stored in the database. You can also make changes to these files directly from T-SQL or at the Windows level.
SQL Server 2012 isn't all good news. There are some disappointments. For me, the biggest disappointment is the lack of love given to PowerShell in this release. Other than a few cmdlets for AlwaysOn and backup/restore, you'll find no real PowerShell improvements in SQL Server 2012. As heavily as Microsoft is relying on PowerShell these days, I fully expected more. Another disappointment is the lack of enhancements to SSMS (SQL Server Management Studio). Microsoft has ported SSMS to Visual Studio 2010, but other than what we get from that move (like better snippet management and integration with Team Foundation Server), there's really nothing to help DBAs manage their servers better. I would have liked to see better multi-server management and reporting features, and tighter integration of PowerShell into SSMS, for example.
I've long had a five-point rule for database upgrades. It means you should have at least five features you're interested in before upgrading your database. And while I was only able to talk about a small handful here, there are a plethora of features I wasn't able to talk about. SQL Server Integration Services has had a major overhaul, and there are some really nice enhancements to SQL Server Analysis Services and SQL Server Reporting Services as well. You will have no trouble finding your five points for the upgrade to SQL Server 2012.
This article, "Review: SQL Server 2012 stands tall," was originally published at InfoWorld.com. Follow the latest developments in Microsoft Windows and data management at InfoWorld.com. For the latest business technology news, followInfoWorld.com on Twitter.