Programmability Enhancements (Database Engine)

Beginning in Microsoft SQL Server 2012 Release Candidate 0 (RC 0), you can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints.

The FileTable feature builds on top of the SQL Server FILESTREAM technology to bring support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. This lets an application integrate its storage and data management components, and provides integrated SQL Server services (including full-text search and semantic search) over unstructured data and metadata, along with easy policy management and administration.

In summary, you can 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.

For more information about the FileTable features, see FileTables (SQL Server).

Statistical Semantic Search provides deep insight into unstructured documents stored in SQL Server databases by extracting statistically relevant key phrases, and then - based on these phrases – identifying similar documents. These results are made available as structured data through three Transact-SQL rowset functions.

Semantic search builds upon the existing full-text search feature in SQL Server but enables new scenarios that extend beyond syntactical keyword searches. While full-text search lets you query the words in a document, semantic search lets you query the meaning of the document. New scenarios include automatic tag extraction, related content discovery, and hierarchical navigation across similar content. For example, you can query the document similarity index to identify resumes that match a job description. Or, you can query the index of key phrases to build the taxonomy for an organization, or for a corpus of documents.

For more information, see Semantic Search (SQL Server).

Before you can use Semantic Search, you have to install, attach, and register an additional database. For more information, see Install and Configure Semantic Search.

Property Search

Customizable NEAR

New Word Breakers and Stemmers

The SET FMTONLY option for determining the format of a response without actually running the query is replaced with sp_describe_first_result_set, sp_describe_undeclared_parameters, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object.

The EXECUTE statement can now specify the metadata returned from the statement by using the WITH RESULT SETS argument. For more information, see EXECUTE (Transact-SQL).

A new family of supplementary characters (SC) collations can be used with the data types nchar, nvarchar and sql_variant. For example: Latin1_General_100_CI_AS_SC or, if using a Japanese collation, Japanese_Bushu_Kakusu_100_CI_AS_SC. These collations encode Unicode characters in the UTF-16 format. Characters with codepoint values larger than 0xFFFF require two consecutive 16-bit words. These characters are called supplementary characters, and the two consecutive 16-bit words are called surrogate pairs. SC collations can improve searching and sorting by functions that use the Unicode types nchar and nvarchar. For more information, see Collation and Unicode Support.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) adds a new collation option – "SC" or "supplementary characters" – that identifies whether a collation is UTF-16-aware. For more information, see Supplementary Characters. Microsoft SQL Server 2012 Release Candidate 0 (RC 0) also adds support for this collation option in the SQL Types XML schema and in other locations where SQL Server exposes or consumes this information in an XML context. The affected locations are the following:

• SQL Types XML schema. The schema version is now 1.2. The schema is backward-compatible and the target namespace has not changed. The schema now exposes the supplementaryCharacters global attribute.

• XMLSCHEMA directive with FOR XML. The new global attribute is exposed in the inline schemas and instance annotations generated by the XMLSCHEMA directive, alongside similar attributes such as localeId and sqlCompareOptions. This directive is supported with FOR XML in RAW and AUTO modes, but not in EXPLICIT or PATH modes.

• sys.sys XML schema collection. The new global attribute is prepopulated in the built-in sys.sys XML schema collection and is made available implicitly in all other XML schema collections that import the SQL Types XML schema.

• Catalog views. The new global attribute is now listed in the following catalog views:

  • sys.xml_schema_components

  • sys.xml_schema_attributes

  • sys.xml_schema_component_placements

• Upgraded XML schema collections. After upgrade from a previous version of SQL Server, the new global attribute is exposed in all XML schema collections that import the SQL Types XML schema.

• XML column sets. The new global attribute is added to XML column set values that represent sql_variant strings that use the new UTF-16 collations. It can also be applied during inserts and updates to set string values of type sql_variant in sparse columns to use the UTF-16 aware collation.

You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide. This is useful when you want to control the number of rows sent to a client application for a given query. For more information, see ORDER BY Clause (Transact-SQL).

For a detailed description and examples of the new spatial features, download the white paper, New Spatial Features in SQL Server Code-Named "Denali".

Circular Arc Segment Support for Spatial Types

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. For more information, see Sequence Numbers.

The THROW statement can be used to raise an exception and transfer execution to a CATCH block of a TRY…CATCH construct. For more information, see THROW (Transact-SQL).

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) introduces 14 new built-in functions. These functions ease the path of migration for information workers by emulating functionality that is found in the expression languages of many desktop applications. However these functions will also be useful to experienced users of SQL Server.

The new functions are:

Conversion functions

• PARSE (Transact-SQL)

• TRY_CONVERT (Transact-SQL)

• TRY_PARSE (Transact-SQL)

Date and time functions

• DATEFROMPARTS (Transact-SQL)

• DATETIME2FROMPARTS (Transact-SQL)

• DATETIMEFROMPARTS (Transact-SQL)

• DATETIMEOFFSETFROMPARTS (Transact-SQL)

• EOMONTH (Transact-SQL)

• SMALLDATETIMEFROMPARTS (Transact-SQL)

• TIMEFROMPARTS (Transact-SQL)

Logical functions

• CHOOSE (Transact-SQL)

• IIF (Transact-SQL)

String functions

• CONCAT (Transact-SQL)

• FORMAT (Transact-SQL)

In addition to the 14 new functions, one existing function has been changed. The existing LOG (Transact-SQL) function now has an optional second base parameter.

SQL Server Express LocalDB is a new lightweight edition of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. The LocalDB edition of SQL Server is targeted to program developers. LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine. To use the SQL Server Database Engine, developers connect directly to the database files and the SQL Server infrastructure is transparent to them. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server. An instance of SQL Server Express LocalDB is managed by using the SqlLocalDB.exe utility. SQL Server Express LocalDB should be used in place of the SQL Server Express user instance feature which is deprecated. For more information, see SQL Server Code-named 2012 Express LocalDB.

The syntax for the FORCESEEK table hint has been modified. You can now specify an index and index columns to further control the access method on the index. The existing FORCESEEK syntax remains unmodified and works as before. No changes to applications are necessary if you do not plan to use the new functionality.

The FORCESCAN table hint has been added. It complements the FORCESEEK hint by specifying that the query optimizer use only an index scan operation as the access path to the table or view referenced in the query. The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. FORCESCAN can be specified with or without an INDEX hint. For more information, see Table Hints (Transact-SQL).

The following new Extended Events are available.

page_allocated:

• Fields: worker_address, number_pages, page_size, page_location, allocator_type, page_allocator_type, pool_id

page_freed:

• Fields: worker_address, number_pages, page_size, page_location, allocator_type, page_allocator_type, pool_id

allocation_failure:

• Fields: worker_address, failure_type, allocation_failure_type, resource_size, pool_id, factor

The following Extended Events have been modified.

resource_monitor_ring_buffer_record:

• Fields removed: single_pages_kb, multiple_pages_kb

• Fields added: target_kb, pages_kb

memory_node_oom_ring_buffer_recorded:

• Fields removed: single_pages_kb, multiple_pages_kb

• Fields added: target_kb, pages_kb

The OVER clause has been extended to support window functions. Window functions perform a calculation across a set of rows that are in some relationship to the current row. For example, you can use the ROWS or RANGE clause over a set of rows to calculate a moving average or cumulative total. For more information, see OVER Clause (Transact-SQL).

In addition, ordering rows within a partition is now supported in the aggregate functions that allow the OVER clause to be specified.

The following analytic functions have been added.

The W3C recommendation for XQuery functions and operators requires them to count a surrogate pair that represents a high-range Unicode character as a single glyph in UTF-16 encoding. However, in versions of SQL Server prior to Microsoft SQL Server 2012 Release Candidate 0 (RC 0), string functions did not recognize surrogate pairs as a single character. Some string operations – such as string length calculations and substring extractions – returned incorrect results. Microsoft SQL Server 2012 Release Candidate 0 (RC 0) now fully supports UTF-16 and the correct handling of surrogate pairs. For more information, see the section "XQuery Functions Are Surrogate-Aware" in the topic Breaking Changes to Database Engine Features in SQL Server 2012.

Concepts