Programmability Enhancements (Database Engine)

Programmability enhancements in the Database Engine include FileTables, statistical semantic search, property-scoped full-text search and customizable proximity search, ad-hoc query paging, circular arc segment support for spatial types, support for sequence objects, default support for 15,000 partitions, and numerous improvements and additions to Transact-SQL.

Native XML Web Services (SOAP/HTTP endpoints) is Removed

Beginning in SQL Server 2012, you can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints.

FileTables

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.

Beginning in SQL Server 2012, you can configure a full-text index to support property-scoped searching on properties, such as Author and Title, which are emitted by IFilters. This form of searching is known as property searching. Whether property searching is possible on a given type of document depends on whether the corresponding filter (IFilter) extracts search properties during full-text indexing. Among IFilters that extract a number of document properties are the IFilters for Microsoft Office 2007 document file types, such as .docx, .xlsx, and .pptx. For more information, see Search Document Properties with Search Property Lists.

Customizable NEAR

Beginning in SQL Server 2012, you can customize a proximity search by using the new custom NEAR option of the CONTAINS predicate or CONTAINSTABLE function. Custom NEAR enables you to optionally specify the maximum number of non-search terms that separate the first and last search terms in a match. Custom NEAR also enables you to optionally specify that words and phrases are matched only if they occur in the order in which you specify them. For more information, see Search for Words Close to Another Word with NEAR

New Word Breakers and Stemmers

All the word breakers and stemmers used by Full-Text Search and Semantic Search, with the exception of the Korean language, are updated in this release. For consistency between the contents of indexes and the results of queries, we recommend that you repopulate existing full-text indexes after upgrading.

  1. The third-party word breakers for English that were included with previous releases of SQL Server have been replaced with Microsoft components. If you have to retain the previous behavior, see Change the Word Breaker Used for US English and UK English.

  2. The third-party word breakers for Danish, Polish, and Turkish that were included with previous releases of SQL Server have been replaced with Microsoft components. The new components are enabled by default.

  3. There are new word breakers for Czech and Greek. Previous releases of SQL Server Full-Text Search did not include support for these two languages.

  4. The behavior of the new word breakers has changed. For more information, see Behavior Changes to Full-Text Search. If you have to retain the previous behavior, see Revert the Word Breakers Used by Search to the Previous Version.

  5. This release installs the latest Microsoft word breakers and stemmers, but does not install the latest Microsoft filters. To download the latest filters, see Microsoft Office 2010 Filter Packs.

New and Enhanced Spatial Features

The new spatial features in SQL Server 2012 represent a significant milestone in the evolution of spatial data support in SQL Server. The support for full globe spatial objects and for circular arcs on the ellipsoid are industry firsts for relational database systems. The geography data type has achieved parity with the geometry data type in the functionality and the variety of methods that it supports. Overall performance, from spatial indexes to methods, has significantly improved. These and other improvements to spatial data support represent a significant step forward in the spatial capabilities of SQL Server.

For a detailed description and examples of these new spatial features, download the white paper, New Spatial Features in SQL 2012.

Enhancements to spatial data types

New circular arcs and related methods

  • New subtypes. There are 3 new subtypes of circular arcs:

    • CircularString

    • CompoundCurve

    • CurvePolygon

  • New methods. All existing methods work on these circular objects. The following new methods are also introduced:

    • BufferWithCurves() uses circular arcs to construct a buffered object with a greatly reduced number of points compared to STBuffer().

    • STNumCurves() and STCurveN() are used for iteration through the list of the circular arc edges.

    • STCurveToLine() and CurveToLineWithTolerance() are used for approximating circular arcs with line segments within default and user-specified tolerance.

New and updated methods and aggregates for geometry and geography

  • New methods.

    • IsValidDetailed() returns a message that can help to identify why a spatial object is not valid.

    • HasZ returns 1 (true) if a spatial object contains at least one Z value.

    • HasM returns 1 (true) if a spatial object contains at least one M value.

    • AsBinaryZM() adds support for Z and M values to the OGC WKB format.

    • ShortestLineTo() returns a LineString that represents the shortest distance between two objects.

    • STLength() has been updated and now works on both valid and invalid LineStrings.

    • MinDbCompatibilityLevel() is a new method used for backward compatibility. It indicates whether spatial objects can be recognized by SQL Server 2008 and SQL Server 2008 R2.

  • New aggregates. These aggregates are available only in Transact-SQL, and not in the client-side programming library.

    • UnionAggregate

    • EnvelopeAggregate

    • CollectionAggregate

    • ConvexHullAggregate

Improved precision.

All constructions and relations are now done with 48 bits of precision, compared to 27 bits used in SQL Server 2008 and SQL Server 2008 R2. This can reduce the errors caused by the rounding of floating-point coordinates.

Enhancements to the geography type

  • Full globe. SQL Server now supports spatial objects that are larger than a logical hemisphere. Geography features were restricted to slightly less than a logical hemisphere in SQL Server 2008. In SQL Server 2012, they can now be as big as the entire globe. A new type of object, called FULLGLOBE, can be constructed or received as a result of an operation.

  • New methods.

    • For invalid objects. The geography type now allows invalid objects to be inserted into a table. STIsValid() and MakeValid() allow invalid geography objects to be detected and corrected in a similar fashion to the geometry type.

    • For ring orientation. Geography polygons can now be accommodated without regard to ring orientation. This can lead to unintended behavior. ReorientObject() can be used to reorient polygon rings for cases where they are constructed with the wrong orientation.

    • geometry methods added for the geography type. STWithin(), STContains(), STOverlaps(), and STConvexHull() methods were previously available only for the geometry type, but have now been added for the geography type. With the exception of STConvexHull(), these new methods are supported by spatial indexes.

  • New SRID. A new spatial reference id (SRID), 104001, has been added to the list of supported spatial reference systems. This new SRID is an Earth unit sphere (a sphere of radius 1) and can be used with the geography type to perform optimized numerical computations when more precise ellipsoidal mathematics are not required.

Spatial performance improvements

Spatial index improvements

  • New auto grid. A new auto grid spatial index is available for both spatial types (geometry_auto_grid and geography_auto_grid). The new auto grid uses a different strategy to pick the right tradeoff between performance and efficiency. For more information, see CREATE SPATIAL INDEX (Transact-SQL).

  • New spatial index hint, SPATIAL_WINDOW_MAX_CELLS. This new spatial hint is critical for fine-tuning query performance using a spatial index. Dense spatial data often requires a higher SPATIAL_WINDOW_MAX_CELLS, whereas sparse spatial data often demands a lower SPATIAL_WINDOW_MAX_CELLS for optimum performance. This hint does not guarantee that a spatial index will be used in the query plan. However, if it is used, this hint will override the default WINDOW_MAX_CELLS parameter.

  • Compression for spatial indexes. For more information, see CREATE SPATIAL INDEX (Transact-SQL).

Additional performance improvements

  • An optimized nearest neighbor query plan is available when certain syntax is used.

  • Several other methods have been optimized for common scenarios.

  • Spatial aggregates have better performance as a result of improvements that affect all CLR UDT aggregates.

Other spatial improvements

Spatial helper stored procedures

Two new helper stored procedures are available. These procedures can be used to evaluate the distribution of spatial data within a table over a given spatial column.

Support for persisted computed columns

UDTs and spatial types can now be persisted in computed columns.

Changes in the client-side spatial programming library

Metadata Discovery

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.

EXECUTE Statement

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

UTF-16 Support

UTF-16 Supplementary Characters (SC) Collations

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.

UTF-16 Supplementary Characters (SC) Collation Option for XML

SQL Server 2012 adds a new collation option – "SC" or "supplementary characters" – that identifies whether a collation is UTF-16-aware. For more information, see Collation and Unicode Support. SQL Server 2012 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.

Ad-hoc Query Paging Implementation

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

Sequence Objects

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.

THROW statement

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

14 New Functions and 1 Changed Function

SQL Server 2012 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

Date and time functions

Logical functions

String functions

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

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. Once LocalDB is installed, developers initiate a connection by using a special connection string. When connecting, the necessary infrastructure is automatically created and started, enabling the application to use the database without complex or time consuming configuration tasks. 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 can be 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 2012 Express LocalDB.

New and Enhanced Query Optimizer Hints

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

Extended Event Enhancements

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

OVER Clause Support Enhanced

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.

Analytic Functions

The following analytic functions have been added.

CUME_DIST (Transact-SQL)

LAST_VALUE (Transact-SQL)

PERCENTILE_DISC (Transact-SQL)

FIRST_VALUE (Transact-SQL)

LEAD (Transact-SQL)

PERCENT_RANK (Transact-SQL)

LAG (Transact-SQL)

PERCENTILE_CONT (Transact-SQL)

XQuery Functions Are Surrogate-Aware

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 SQL Server 2012, 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. SQL Server 2012 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.

See Also

Concepts

What's New (Database Engine)