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.
No comments:
Post a Comment