SQL-Server 2012 New Features of Interest : Newest Microsoft RDMS

Microsoft SQL-Server 2012 New Features

SQL-Server 2012 Upgrades and Enhancements of Note

As any of my tech-blog readers know, I am a huge fan of Microsoft SQL-Server for my own database applications as well as for software I develop for others.  And, it is that time again: a major update of SQL-Server is available and I am busy upgrading my existing databases while also experimenting with the latest features that SQL 2012 has to offer.

Microsoft SQL-Server 2012 continues the SQL-Server line of enterprise-class databases by extending the SQL Server 2008 & 2008R2 through additional modern features and functionality, including the following items that I considered noteworthy enough to mention here (*Note: keep in mind that some features may not be available in all versions of the product):

New and Improved Features / Enhancements:

• New and enhanced spatial features — this is a significant evolution of spatial data support in SQL Server including support for full globe spatial objects and for circular arcs on the ellipsoid.
• FileTables — extends FILESTREAM technology and allows you to now store files and documents in special tables in SQL Server, but access them from Windows applications as if they were stored in the file system, without making any changes to the Windows applications. File and directory data is exposed through a Windows share.
• Sequences — these should be familiar (in concept) to anyone that has worked with Oracle. A sequence object is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table.
• Statistical Semantic Search — provides deep insight into unstructured documents stored in SQL Server databases by extracting statistically relevant key phrases (allowing you to query the meaning of a document);
• Full-Text Search has new abilities for property-scoped searching and customized proximity searches;
• Availability Enhancements including AlwaysOn SQL Server Failover Cluster Instances, AlwaysOn Availability Groups and enhancements to Online Operations for online index builds (where index columns contain LOB data);
• Scalability and Performance Enhancements including a new data warehouse query acceleration feature based on a new type of index called the columnstore;
• SQL Server Express LocalDB — a new lightweight edition of Express that has all its programmability features

Transact-SQL (T-SQL) improvements / enhancements:

• New built-in T-SQL functions: Conversion functions now include PARSE, TRY_PARSE, TRY_CONVERT; Date and time functions now include DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS; Logical functions now include CHOOSE (gets item at the specified index from a list of values), IIF (a shorthand if-then construct already common in other languages); and String functions now include CONCAT and FORMAT;
• ORDER BY clause improvements using OFFSET and FETCH (limit result set rows to a range) — this feature should come in very handy for all sorts of ad-hoc queries and/or web-applications that implement "paging" through data! Finally, less custom-code for this!
• THROW statement can be used to raise an exception and transfer execution to a CATCH block of a TRY…CATCH construct;
• OVER clause has been extended to support window functions which will reduce the need for custom code to compute things like moving averages or cummulative totals;
• additional analytic functions like: FIRST_VALUE, LAST_VALUE, , LAG (previous rows access without self-JOIN), LEAD (subsequent rows access without self-JOIN), CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK
• EXECUTE statement can now specify the metadata returned from the statement by using the WITH RESULT SETS argument;
• New and Enhanced Query Optimizer Hints: FORCESEEK includes further options and a new FORCESCAN hint;
• ...and more.

I really look forward to putting some of the new programming features to use! The windowing enhancements to the OVER clause are very interesting to me, since I have previously written custom code to perform moving-averages, running subtotals, and other interesting algorithms that should now be MUCH easier to implement using the new abilities of the OVER clause.

If you happen to need "windowing" functions but are unable to upgrade to SQL-Server 2012, search this blog for my source code for SQL-Server for moving-averages, running-subtotals, and so on: implemented as pure SET-BASED algorithms too!!

I have not yet tried the LocalDB version of SQL-Server 2012, but I definitely can think up some uses for it.  And, it's free, which is always a good thing, though it does have somewhat low limits for memory and total database size.

Breaking Changes in SQL-Server 2012

As with any major version upgrade, there is the potential for "breaking changes" that will affect your existing SQL-Server code base (Transact-SQL) and/or your database designs, options, and the like. Obsolete features are the most critical, but you should also consider deprecated features, since it is much easier to get ahead of the curve with regards to planning for future releases.

Obsolete and Deprecated Features:

• Native XML Web Services (SOAP/HTTP endpoints) is Removed;
• Finally! ANSI JOIN syntax is required! No more "*= and =*" join syntax, which hopefully you migrated away from years ago;
• FASTFIRSTROW (query hint) is replaced with OPTION (FAST n);
• COMPUTE / COMPUTE BY (T-SQL) — use ROLLUP instead;
• SET ROWCOUNT for INSERT, UPDATE, and DELETE statements are deprecated and will not be supported in the next version of SQL-Server. Use the TOP keyword instead;
• And a host of other changes — See the Microsoft SQL-Server 2012 Backward Compatibility site for details;

Enjoy the new SQL-Server 2012 features! Download the free trial editions and/or consider the Developer version once it is available.