Notes from SommarKollo 2012: What is new in SQL Server 2012?

New features in SQl server 2012

Attended the What is New in SQL Server 2012 session today at the Microsoft seminar Sommarkollo. Here are my notes, have fun ;)

Presenter: Håkan Winther

Håkan Winther presenting “What’s new in SQL Server 2012”

Didn’t talk about AlwaysOn feature, will be in another session
A lot of things that are new in SQL Server 2012, the R2 was just a minor release

SEQUENCE
Sequence generator, new core feature (been standard in Oracle for years I believe)
Generates a sequence based on the rules you set, similar to identity, but more customizable and allows you to set a global sequence instead of being limited to one table only as with identity.
It’s a database object that can be attached to a (or several) table column during insertion
Retrieved from memory rather than disk = Fast !
Can only be numeric and must be assigned to a schema
Example: create sequence s2 as int;
To view all sequence objects: select * from sys.sequences
You can set different attributes such as cache size, min and max value, start with, increment value, restart with.
Sequence nr can be used as identity and can be used across tables

question: should identity be replaced by sequence generator?
answer: with new code I can’t really see why not

Query PAGING functionality in select
Enhanced Order By using offset and fetch (an option to the old way of doing it by using row-number). No performance gained compared to row number. Just an easier syntax with better readability.

WINDOWING FUNCTION
Enhanced OVER() clause

Limits the scope of aggregation function within PARTITION
Gives new possibilities
Running total
Sliding window
Much better performance
Group by is limiting, you can only select what is a part of the grouping, the solution results often in many queries, joins and aggregations before grouping.
With OVER() you can do it in one go
You can select rows that will be included in the aggregate function within the OVER() rows gives unique rows (memory), range gives unique values (from disk thought so you might want to avoid using range)
You can sort even sort SUM()

New ANALYTICAL FUNCTIONS such as
Value calculated from first/last record

First_value
Last_value
Accesses data from a subsequent row or previous row
Lag
Lead
Calculate the relative rank of a row within a group of rows
• PERCENT_RANK
Calculate the cumulative distribution of a value in a group of values
• CUME_DIST
Calculate a percentile based on a continuous distribution of the column value
• PERCENTILE_CONT
Compute a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset
• PERCENTILE_DISC

New DATE and TIME functions
 fromparts
T.ex. Datefromparts (y,m,d)

Other new functions
STRING FUNCTIONS
Concat()
Choose()
Format()

CONVERSION FUNCTIONS
Parse()
Try_ tryconvert()
Try_parse() (can use using ‘culture’ if datetime, money etc) be aware of commas and punctuation

LOGICAL FUNCTIONS
IIF(), simplified case

THROW
New command , not relation to sys.errormessages
Requires semicolon before

DEPRECATED:
Not terminating a query sentence
Set rowcount on insert, update, delete
Set fmtonly
Databaseproperty()
80 compability
Native xml web services , create endpoint, alter endpoint

New/changed QUERY HINTS
Forceseek now includes the index name
Forcescan to be used when optimizer underestimates the nr of records

GET METADATA stored procedure
sp_describe_first_set

COLUMN STORE INDEX
For BI storage
Ability to read the values of a particular column of a table without having to read the values of all the other columns
Data also becomes more compressible
http://rusanu.com/2012/05/29/inside-the-sql-server-2012-columnstore-indexes/ for more info, good blog post

PARTITIONING
Now supports 15000 partitions, increased from 1000

EXTENED EVENTS enhancements
(things that you can see in profiler, plus more, and also better performance. Rumors are that profiler will be deprecated as extended events are recommended instead)
A new GUI
Watch live data
New profiling info
++

ONLINE OPERATIONS (enterprise edition)
Varchar(max)
Nvarchar(max)
Varbinary(max )
++ supported

NEW COLUMN, NOT NULL W. DEFAULT
Alter table add column not null default
Add new column where null is not allowed to existing table without having to rebuild everything

SSDT, VS extension
Sql server Data tools (SSDT) extension for VS10 and VS2012 (some problems in vs2012 when uppgrading from beta to RC)
Code analysis
Find code that break the best practices rules defined
Smart rename of objects

Preview changes
Based on dependencies cannot resolve “dynamic”…
Good article http://www.codeproject.com/Articles/357905/Evaluating-SQL-Server-Data-Tools

FULLTEXT SEARCH
Fulltext search motor is completely re-written
Not in express if not with advanced services
Scalable up to 350 millions documents
Property search
NEAR
Semantic search

Notes from SommarKollo 2012: What is new in SQL Server 2012?

Comments