SQL Server 2012 New features

1)

CREATE SEQUENCE [schema_name . ] sequence_name

[ AS [ built_in_integer_type | user-defined_integer_type ] ]

[ START WITH <constant> ]

[ INCREMENT BY <constant> ]

[ {MINVALUE [ <constant> ] } | { NO MINVALUE } ]

[ {MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]

[ CYCLE | { NO CYCLE } ]

[ { CACHE [ <constant> ] } | { NO CACHE } ]

    [ ; ]

2)

SELECT *

FROM Customers

ORDERBYCustomerID

OFFSET 10 ROWS

FETCH NEXT 10 ROWS ONLY;

--skips first 10 rows and fetches next 5

3)

BEGIN TRY

               BEGIN TRANSACTION -- Start the transaction

               -- Delete the Customer

               DELETE FROM Customers

               WHERE EmployeeID = ‘CACTU’

               -- Commit the change

               COMMIT TRANSACTION

END TRY

BEGIN CATCH

               -- There is an error

               ROLLBACK TRANSACTION

               -- Re throw the exception

               THROW

END CATCH

Also, you can use Throw to replace RAISERROR function:

Collapse | Copy Code

THROW 51000, ‘The record does not exist.’, 1;

4)

EXEC CustOrderDetail ‘2’

WITH RESULT SETS

(

               (

               ProductName1varchar(100),

               Unitprice1varchar(100),

               Quantity1varchar(100),

               Discount1varchar(100),

               ExtendedPrice1varchar(100)

               )

);

5)

SQL Server 2012, we have a new feature called Contained Databases, which intends to reduce or eliminate the dependencies that a database has on the SQL Server instance, making it easier to migrate a database to a new instance with less of the work involved in reproducing and validating these dependencies. In SQL Server 2012, tempdb will automatically create objects using the collation of the Contained Database, rather than the server default, making it easier to rely on your code regardless of the server collation (of course this still does not resolve issues where you try to join #temp tables generated from multiple databases with different collations).

6)

SQL Server 2012, the relational engine gets to take advantage of the column-based index first seen through the VertiPaq acquisition a few years ago. What a ColumnStore index does is essentially turn a traditional index on its side. Think about a covering index that includes multiple columns, but instead of storing all the different columns in a single row on a single page, split it up and store each column on its own set of pages. In this version, ColumnStore indexes have a major limitation: they are read only. This means that once a ColumnStore index has been created on a table, you can no longer perform any DML operations against that table.

7)

SELECT 

    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 

    THEN 'Cast failed'

    ELSE 'Cast succeeded'

END AS Result;

GO