2.What Is New‎ > ‎MSSQL‎ > ‎

MSSQL 2012

SQL Server Code Name is the latest release primarily focus on High Availability, High Scalability , Cloud and BI. Key feature are listed below.

Programming Enhancement

Sequences

In simple terms, it is a new database object and a substitute for the Identity of columns.

  • Using the identity attribute for a column, you can easily generate auto-incrementing numbers (which as often used as a primary key).
  • With Sequence, it will be a different object which you can attach to a table column while inserting.
  • Unlike identity, the next number for the column value will be retrieved from memory rather than from the disk – this makes Sequence significantly faster than Identity. We will see this in coming examples.
  • Identity works for single table , Sequence can spread across multiple tables.
Ad-Hoc Query Paging
  • 2012 add support for paging on the fly in query statement itself.
  • Keywords 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.
Example
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Column Store (“Apollo”)

  • Relational database management systems traditionally store data in row-wise fashion. The values comprising one row are stored contiguously on a page. We sometimes refer to data stored in row-wise fashion as a row store. In a column store, values from a single column (from multiple rows) are stored contiguously, potentially in a compressed form.
  • This feature greatly improve search performance on any columns. 

Columnstore index

It improves data warehouse query performance by “hundreds to thousands of times in some cases” if it is combined with enhanced query processing.

FileTables

With file Table you can store files and documents in special tables in SQL Server and access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.The FileTable feature builds on top of SQL Server FILESTREAM technology.

Circular arc segment support for spatial types

It is a new spatial data type defined by three points in a two dimensional plane and the third point cannot be the same as the first point.

Table Partitioning up to 15,000

Support growing data warehousing with table partitioning that now scales to 15,000 partitions.

Extended Events enhancements

Enhancements include flexible event selection, logging, and filtering with Extended Events.

Property-scoped full-text search (Extended Properties Index)

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

Statistical semantic search

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

Customizable proximity search

  • Developers can now also benefit with improved NEAR operator that allows them to specify distance in-between as well as the order of words.

High Availability

SQL Server AlwaysOn

This feature is a brand name for availability and recovery. In this release several new recovery and faileover feature to enhance over all availability

Failover Availability Group

Availability Groups provides an integrated set of options including automatic and manual failover of a group of databases, support for up to four secondaries, fast application failover and automatic page repair.

Failover Cluster Instances

support multi-site clustering across subnet which helps enable cross-datacenter failover of SQL Server instances.

Active Secondries

In 2012 backup sever will not sit idle , they  secondary instances to are utilized for running report queries and backup operations which helps eliminate idle hardware and improve resource utilization.

Connection Director

  • Multi-subnet failover: Now client applications can failover across multiple subnets (up to 64) almost as fast as they can failover within a single subnet.
  • Read-Only Intent: This improvement gives the customers a way to control the type of workloads that run on their HA servers and efficiently manage their resources.

AutoStat

This feature auto-creates and updates temporary statistics needed for queries running on the readable secondary. These temporary statistics area stored in TempDB therefore no physical changes are required in the user database. This allows the optimizer to generate optimal query plans on secondary replica as it would do on the primary replica and it does not require any user intervention.

Windows Server Core Support

SQL2012 natively support installation Server Core (Minimum Install) that in turn reduces overall database downtime because Server Core installation  greatly reduces patching requirements.

Database Recovery Advisor:

This feature include a visual timeline that presents the backup history of the database and the available points in time to which the user can restore the database, algorithms to streamline identifying the right sets of backup media to get the database back to a specific point in time, and page restore dialog in SSMS to do page-level restores of the database.

StreamInsight Enhancement

Microsoft StreamInsight is Microsoft’s Complex Event Processing technology to help businesses create event-driven applications and derive better insights by correlating event streams from multiple sources with near-zero latency. Several features are improved to enhance availability of StreamInsight

BI Enhancement

BI Semantic Model (BISM).

Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes:

  • Data Model
  • Business Logic
  • Data Access

BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.

Ad-hoc Reporting (Crescent)

  • Project Crescent is the Microsoft code name for the new reporting and visualization tool expected in SQL Server 2012 (code-named Denali).
  • Project Crescent provides drag and drop ad-hoc reporting functionality and was built entirely on Silverlight. It includes a powerful query tool and interactive storyboarding to allow a user to share visualizations of large datasets.

Data Quality Service (DQS)

  • Data Quality Services is a knowledge-based approach that runs in SSIS (SQL Services Integration Services) enables a data steward or IT professional to maintain the quality of their data and ensure that the data is suited for its business usage.
  • DQS is a knowledge-driven solution that provides both computer-assisted and interactive ways to manage the integrity and quality of your data sources.
  • DQS enables you to discover, build, and manage knowledge about your data.
  • You can then use that knowledge to perform data cleansing, matching, and profiling. You can also leverage the cloud-based services of reference data providers in a DQS data-quality project.

SharePoint 2010 share service

This is a new service provides Integration with SharePoint.,

SQL Server Data Tools (SSDT)

The SQL Server Data Tools (SSDT) add-in for Visual Studio 2010 to work with and upgrade SQL Server 2005 and 2008 reports, the Reporting Services Excel/Word rendering extension to generate reports for earlier versions of Excel/Word.

Comments