SQL Server 2012 changes

Submitted by m_sabal on Fri, 02/10/2012 - 15:00

This is an incomplete summary of what's new with SQL Server 2012 as I discover them:

  • Data Center edition will be moved into Enterprise Edition.
  • Licensing will be based on the number of cores rather than the number of processors.
  • A database-level sequence number, similar to identity within a table, will be available for use.
    1. Create the sequence under the Programmability section of the database in Object Explorer, or with SQL:

      CREATE SEQUENCE DemoSequence
      START WITH 1
    2. Assign the sequence to a field or variable with,

      SELECT @seqvar=NEXT VALUE FOR dbo.DemoSequence
  • Paging now acts more like MySQL (but why couldn't they just use LIMIT and be done with it?):

    SELECT *
    FROM Customers
    ORDER BY CustomerID
    OFFSET 10 ROWS -- I assume this is base 1, as opposed to LIMIT which is base 0.
    FETCH NEXT 10 ROWS ONLY; -- So if I understand it right, this will return rows 11-20.
  • Working with results from stored procedures was sometimes cumbersome. The new WITH RESULT SETS option makes EXECUTE work more like a common table expression, or SELECT INTO.
  • Reporting services are being replaced with Power View, with the results being more graphically oriented as opposed to text or chart based.
  • Power View reports can be exported directly to MS Word or Excel formats.
  • Improved high availability features, termed "Always On."
  • SQL Express LocalDB can be placed into a user directory and run without requiring Admin rights to the PC.
  • There is the expected mix of overall performance, security, and capacity improvements.

[1] http://www.codeproject.com/Articles/265760/Using-SQL-Server-2011-T-SQL-…
[2] http://download.microsoft.com/download/E/9/D/E9DBB1BB-89AE-4C70-AF02-AA…