9/10/2015

New feature in SQL Server 2012


New features in SQL server 2012:

-          AlwaysOn Availability Groups

-          Contained Databases

-          ColumnStore Indexes

-          FileTable

-          Semantic Search – full text search

-          Distributed Replay –used for performance testing

-          SQL server audit enhancements – ability to define custom audit specifications, new filtering options

-          User Defined Server Roles – Allows more control in segregating duties

-          Database tuning Advisor Enhancement – can use plan cache as a workload

-          T-SQL Enhancements:

o   Sequence object – similar to identify fields

-          Logical Functions:

o   CHOOSE – Return the item at the specified index from a list of values

                                Select shoose(2,’A’,’B’,’C’) - result will be B

o   IIF – returns of two values depending on the Boolean expression

                                Select IIF (1=2,’Car’,’Bike’) – result will be Bike

-          Date and Time functions

o   DATEFROMPARTS (year, month, day) – Returns the date value.

o   DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions) –Returns the datetime2 value.

o   DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds) –Returns the datetime value.

o   DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precison) – Returns the datetimeoffset value.

o   SMALLDATETIMEFORMPARTS (Year, month, day, hour, minute) – Returns the small datetime value.

o   TIMEFORMPARTS (Hour, minute, seconds, fractions, precison) – Returns the time value.

o   EOMONTH (start_Date) – Returns the last day of the month.

-          OFFSET and FETCH: -

o   OFFSET: specifies the number of rows to skip before it start to return rows.

o   FETCH: specifies the number of rows to return after the offset.

-          WINDOW Functions: Analytics functions used with OVER and ORDER BY clause.

o   FIRST_VALE: Returns the first value of an ordered group.

o   LAST_VALUE: Returns the last value of an ordered group.

o   LEAD: Returns the value from the next row.

o   LAG: Returns the value from the previous row.

o   CUME_DIST: calculates the cumulative distribution of a value in a group of values.

o   PERCENT_RANK: calculates the relative rank of a row within a group of rows.

o   PERCENT_CONT: Calculates a percentile based on a continuous distribution of the column value.

o   PERCENTILE_DISC: Computes a specific percentile for sorted value in an entire rowset or within distinct partitions of a rowset.

-          Online index rebuild available for LOB data.

-          Allowable table partitions increased to 15000.

-          Nodes of SQL 2012 clusters can be on different subnets.