Unleashing MS SQL Server 2012
Microsoft recently launched a new member to the family of SQL Server i.e. MS SQL Server 2012. Presently, many of you might be working on MS SQL Server 2005 or MS SQL Server 2008 version. So, are you excited to know more about the new features of MS SQL Server 2012? Congratulations!!! You are at the right door as this article will aim to unleash the new features of MS SQL Server 2012. Microsoft Code-named the MS SQL Server 2012 as “Denali”.
Denali (MS SQL Server 2012) includes several types of groundbreaking new features such as Always-on, Column store Index, Data Quality Services, Power View and Cloud Connectivity. All these great features help to provide better performance and reduce the work of the developers at the time of writing queries. This article will mainly focus on the new things added to MS SQL Server 2012. In future articles, we will be learning these features into depth practically. Also, we will uncover MS SQL Server 2014 features. Let’s gather some brief information on the above mentioned terms.
Always on - An improved version of Database mirroring known as Availability groups is added. It helps to simplify high availability environment. It helps in Multi-site clustering also referred as Multi-Subnet clustering.
Column Store Index - It is a new type of index in data warehouse to improve query performance. How columnstore index can enhance the query performance? The funda behind this is that columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored.
So, what is the use of this and why we should go with it? Because it is the more efficient way as the query only requests a few columns from the table. Microsoft claims that with this feature, query performance can improve from 6X to 100X in cases where the data can be cached in RAM, and thousand-fold improvements where the working set does not fit in RAM.
NOTE :- For Technology giants, you can follow the below link for an exhaustive guide to what’s new in SQL Server 2012, Microsoft Press have released a free 288 page book covering all the new features; available in PDF form here :- http://download.microsoft.com/download/F/F/6/FF62CAE0-CE38-4228-9025-FBF729312698/Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012_PDF.pdf
The following are the other new features added in MS SQL Server 2012 – Denali :-
- Data Paging.
- Analytic Window Functions.
- Conversion Functions.
- Logical Functions.
- Date/Time Functions.
- String Functions.
- Error handling.
Feature# Sequences :-
- It’s almost similar to Identity property.
- New construct to generate numeric sequences.
- We can set min and max values.
- Cycle will set the current value to the min value when the max value is reached.
- Future values can be cached minimizing disk IO.
Feature# Data Paging :-
OFFSET and FETCH :-
- It allows for server side paging.
- Not data pages (8K) but paging like on a web page.
- OFFSET AND FETCH is the argument of the ORDER BY clause.
- OFFSET – Number of rows to skip.
- FETCH – Number of rows to return.
Feature# Analytic Window Function:-
We can use OVER clause with the Analytic functions. The OVER clause determines the partitions and order of a row set before the window function is applied. Before going to Analytic function we must know about OVER clause.
OVER clause -
- PARTITION BY – Similar to GROUP BY but only applies to the window function and not the entire query.
- ORDER BY – Specifies the order of the rows in the partition.
Now, Let’s learn about Analytic functions.
LAG and LEAD :- No longer need to use a self-join or CTE. Many developers use to find previous rows and future rows by using SELF join and CTE, But now it’s pretty much simple to find those by using LAG() and LEAD() functions.
- LAG : Access data from previous rows in the result set.
- LEAD : Access data from future rows in the result set.
FIRST_VALUE and LAST_VALUE :-
- FIRST_VALUE : Retrieves the first value in a partition.
- LAST_VALUE : Retrieves the last value in a partition.
- Calculates the percentage of values less than or equal to the current value in the group.
- COUNT (*) OVER (ORDER BY Col1) / Total Count.
- Calculates a percentile value.
- Will interpolate the appropriate value.
- Can use to find the median.
- CONT stands for continuous.
- Calculates a percentile value.
- Like PERCENTILE_CONT but will select a value that exists in the set.
- Can use to find the median.
- DISC stands for discrete distribution.
Feature# Conversion functions:-
- PARSE :-
- Can only convert to a number or datetime.
- TRY_PARSE :-
- Like PARSE but if an error occurs returns a NULL.
- TRY_CONVERT :-
- Attempts to cast a value into a specified data type. Returns NULL if CONVERT fails.
Feature# Logical functions:-
- IIF :-
- Takes a Boolean expression and returns one of two values.
- Has the same limitations as CASE.
- Can only be nested to 10 levels.
- CHOOSE :-
- Returns a value from a list based on a specified index.
- If the specified index is not in the list NULL is returned.
- Returns the data type based on data type precedence.
Feature# DATE/TIME functions :-
- EO_MONTH :-
- Returns last date of a specified month.
- Can specify a month_to_add argument to increment or decrement result.
- FROMPARTS :-
- DATEFROMPARTS ( year, month, day)
- DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
- DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
- SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
- TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
Feature# STRING FUNCTIONS :-
- CONCAT :-
- Does what it says…concatenates strings together.
- NULLs are automatically converted to empty strings.
- Can pass other data types for concatenation.
- FORMAT :-
- Simplifies the string formatting of dates and other data types.
- No more memorizing numeric predefined format values.
- Returns a string.
Feature# Error handling :-
- THROW :-
- Reduces the need to use RAISERROR in TRY/CATCH blocks.
- Can provide custom error messages.
- Always uses severity level 16.
Conclusion :- Microsoft SQL Server 2012 code name Denali is launched with features such as Always on, Column store Index, Data Quality Services, Power View and Cloud Connectivity will benefit in the following way.
- Greater availability.
- Blazing-fast performance.
- Rapid data exploration.
- Consistent data.
- Optimized productivity.
Free e-books and Pdf’s :-
Please Subscribe or Like PhpRinG Tutorials for free e-books and pdf’s.
- Launching Very Soon free e-books and pdf’s on MS SQL Server.
These all makes MS SQL Server 2012 – “Next generation Data warehouse”. Please share your views in the form of comments. Stay tune to PhpRinG Tutorials for more Information!!!