Microsoft SQL-Server 2012 New Features

Microsoft SQL-Server 2012 New Features

Sequences:

Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence.

/****** Create Sequence Object ******/
CREATE SEQUENCE Seqexample
START WITH 1
INCREMENT BY 1;

/****** Create Temp Table ******/
DECLARE @Emp TABLE
(
ID int NOT NULL PRIMARY KEY,
Fname varchar(100) NOT NULL
);

/****** Insert Some Data ******/
INSERT @ Emp (ID, Fname)
VALUES (NEXT VALUE FOR Seqexample, ‘Satya Abc’),
(NEXT VALUE FOR Seqexample, ‘Satya Mno’),
(NEXT VALUE FOR Seqexample, ‘Satya Xyz’);

/****** Show the Data ******/
SELECT * FROM @ Emp;

The results would look like this:

ID       Fname
1         Satya Abc
2         Satya Mno
3         Satya Xyz

Ad-Hoc Query Paging:

 Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bring back only the results you want to show to your users when they are needed. The following TSQL code runs against the Person table in the AdventureWorks sample database (available from Microsoft). In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.

SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

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 column store;

Full Text Search: The Full Text Search in SQL Server 2012 has been enhanced by allowing you to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.

SQL Server Express LocalDB — a new lightweight edition of Express that has all its programmability features

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.

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);

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 cumulative 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;

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;