What’s New in SQL Server 2012 -Part 1

Columnstore Index

             Columnstore technology has introduced to SQL Server 2012 is the

columnstore index. This is an index created just like any other, but it stores index

data in a highly compressed, column-wise fashion. For certain classes of queries,

particularly those found in Kimball-design star schemas, columnstore indexes make

a lot of sense. Typical performance gains of between 10x – 100x performance can be

possible for certain queries, so they are worth investigating, as they may provide

an alternative to a very costly hardware upgrade.

 CREATE TABLE Customer

(

CustomerName varchar(200) NULL,

DateOfBirth datetime NULL,

Sex char(10) NULL,

Salary int NULL,

LoanAmount int NULL

)

CREATE NONCLUSTERED COLUMNSTORE INDEX csidxCustomer

ON Customer (CustomerName, DateOfBirth, Sex, Salary, LoanAmount)

However, columnstore indexes do have some restrictions. The most limiting

restriction at present is that they cannot be updated, and as such any updates

applied to a table will fail. If you do try to perform an insert, update or delete,

you will see the following error message, even if the query affects zero rows:

INSERT statement failed because data cannot be updated in a table with

a columnstore index

IN SQL2014 we can use columnstore index DML operations also.

Update or insert will not fail

String functions

With SQL Server 2012 Microsoft has introduced two new string functions,

bringing the total to 25.

CONCAT

CONCAT is to perform a concatenation operation. Pass CONCAT a number of string arguments and it will concatenate, or join them together and return an output string.

 CREATE TABLE #Customer

(

FirstName varchar(30) NOT NULL,

MiddleName varchar(30) NULL,

LastName varchar(30) NOT NULL

)

INSERT INTO #Customer

VALUES (‘Rachel’, ‘Jane’, ‘Clements’), (‘Jon’, NULL, ‘Reade’)

SELECT CONCAT(FirstName + ‘ ‘, MiddleName + ‘ ‘, LastName) AS

CustomerName

FROM #Customer

SELECT FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName AS CustomerName

FROM #Customer

Using the CONCAT function we can return a concatenation of values,

allowing for NULL values

FORMAT

Syntax:

       FORMAT ( value, format [, culture ] )

The first parameter (value) is the variable to apply the formatting to. The second

argument, format, is the format of the value. Take care with the format argument,

as this must be a supported .NET Framework format. Culture is the locale you would

like to apply to the value. If you choose not to supply a culture, the function will

use that of the current session.

 DECLARE @MyDate datetime = ’10/08/2012′

SELECT FORMAT(@MyDate, ‘dd/mm/yyyy’, ‘en-US’) AS NewDate

Datetime functions

EOMONTH

This function does exactly what it says, returning the last day

of the month you specify.

 

DECLARE @MyDate datetime

SET @MyDate = ’05/17/2012′

SELECT EOMONTH (@MyDate) AS LastDayOfTheMonth

There is another parameter value you can feed in to return the end

of the month, either in the past or future

DECLARE @MyDatedatetime DATETIME
SET @MyDatedatetime = ’05/17/2012′
SELECT EOMONTH (@MyDatedatetime, 6) AS LastDayOfTheMonth

By utilizing a negative number, we can go back six months

 DECLARE @MyDate datetime

SET @MyDate = ’05/17/2012′

SELECT EOMONTH (@MyDate, -6) AS LastDayOfTheMonth

DATEFROMPARTS

Next up is the DATEFROMPARTS function which accepts year, month and day

parameters and returns them as a date variable

In  SQL 2008 and below version we can write like this:

DECLARE @Year int, @Month int, @Day int

SET @Year = 2012

SET @Month = 04

SET @Day = 09

SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + ‘/’ +

CONVERT(varchar(10),@Month) + ‘/’ +

CONVERT(varchar(10),@Day),103) AS MyDate

We can write the above query as

DECLARE @Year int, @Month int, @Day int

SET @Year = 2012

SET @Month = 09

SET @Day = 23

SELECT DATEFROMPARTS (@Year, @Month, @Day) AS MyDate

TIMEFROMPARTS

 The TIMEFROMPARTS function works in exactly the same way, but instead of

passing in the year, month and day, as you have probably guessed, you pass

in time parameters instead:

  DECLARE @Hour int, @Minutes int, @Seconds int,

@FractionsOfASecond int

SET @Hour = 15

SET @Minutes = 23

SET @Seconds = 47

SET @FractionsOfASecond = 0

SELECT TIMEFROMPARTS (@Hour, @Minutes, @Seconds,@FractionsOfASecond, 2) AS MyTime

DATETIMEFROMPARTS

You can combine time and date with the DATETIMEFROMPARTS function and this

will return a datetime variable

 DECLARE @Year int, @Month int, @Day int, @Hour int,

@Minutes int, @Seconds int, @MilliSeconds int

SET @Year = 2012

SET @Month = 07

SET @Day = 23

SET @Hour = 17

SET @Minutes = 27

SET @Seconds = 49

SET @MilliSeconds = 0

SELECT DATETIMEFROMPARTS (@Year, @Month, @Day, @Hour, @Minutes,

@Seconds, @MilliSeconds) AS MyDateTime

TRY_PARSE

 Is a conversion function

 SELECT TRY_PARSE (‘SQL Server 2012’ AS datetime) AS MyDateTime

Will return null value

 CREATE TABLE DataToBeCleansed

(

ID int IDENTITY (1,1),

IntegerDataToBeCleansed nvarchar(3)

)

GO

INSERT INTO DataToBeCleansed (IntegerDataToBeCleansed)

VALUES (‘1’),

(‘2’),

(‘E’),

(‘4’),

(‘5’),

(‘6’),

(‘L’),

(‘8’),

(‘9′),

(’10’)

GO

SELECT ID, IntegerDataToBeCleansed

FROM DataToBeCleansed

WHERE TRY_PARSE(IntegerDataToBeCleansed AS int) IS NULL

SEQUENCE

A SEQUENCE object is created at the database level but, unlike an IDENTITY property,

it can be used across multiple tables. An IDENTITY value is generated when you

insert a row into a table and it cannot be updated. You can retrieve the SEQUENCE

value at any time and reset it without altering its previous value, and even set a

minimum and maximum value. Let us look at a simple example:

 CREATE SEQUENCE mySequence AS int

START WITH 1

INCREMENT BY 1

CREATE TABLE Employee

(

EmployeeID int NOT NULL,

FirstName varchar(30) NOT NULL,

LastName varchar(30) NOT NULL

)

INSERT INTO Employee (EmployeeID, FirstName, LastName)

VALUES

(NEXT VALUE FOR mySequence, ‘Rachel’, ‘Clements’),

(NEXT VALUE FOR mySequence, ‘Jon’, ‘Reade’)

GO

Reset Sequence

ALTER SEQUENCE mySequence

RESTART WITH 1

If we wanted to set a minimum and maximum value we could have declared our

SEQUENCE as follows:

 

CREATE SEQUENCE mySequence1 AS int

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 15

As you can see, mySequence is now available for use by all tables in the database:

We have not used the SEQUENCE object yet, so the first value returned should be 1.

Run the following statement to confirm this:

SELECT NEXT VALUE FOR mySequence AS [Next Value]

WITH RESULT SETS

The EXECUTE statement has been extended in SQL Server 2012 to include the WITH

RESULT SETS option. This allows you to change the column names and data types

of the result set returned in the execution of a stored procedure.

We will jump straight into an example to see how this works. The following

procedure returns a straightforward result set using the Employee table we

created in the previous section:

CREATE PROC spGet_Employees

AS

SELECT EmployeeID, FirstName, LastName

FROM Employee

ORDER BY EmployeeID

If we call this stored procedure in the usual way it will return all columns.

The data type of each column will be the same as the column type in the table.

EXEC spGet_Employees

In the previous section, we used the mySequence SEQUENCE object to set the value

that was inserted into the EmployeeID column. We want to return the result set

so the integer EmployeeID column is a varchar instead. To see how you can

easily change the name of the columns, we will output EmployeeID as NI_Number

and LastName as Surname. We can do this easily using

  WITH RESULT SETS:

EXEC spGet_Employees

WITH RESULT SETS

(

(

NI_Number varchar(15),

FirstName varchar(30),

Surname varchar(30)

)

)

FileTable table type

 FileTable is a new type of table which builds on the existing FILESTREAM

functionality that was introduced in SQL Server 2008. FILESTREAM is an efficient

way to store documents inside the database, as they are managed by SQL Server

and included in any backups. You can store any type of file and up to any size—the

only limit is the size of the disk the FILESTREAM store sits on.

FILESTREAM effectively manages the files you insert, whereas FileTable will allow

access through Windows to the properties of files stored on the NT file system. In

effect, FileTable really is a table of files. This is where it becomes exciting, as you

are about to discover.

CREATE DATABASE Documents

ON PRIMARY

(

NAME = N’Documents’,

FILENAME = N’C:\SQL2012\Documents.mdf’

),

FILEGROUPFSGROUP CONTAINS FILESTREAM

(

NAME = FSData,

FILENAME= ‘C:\SQL2012\FSDataStore.ndf’

)

LOG ON

(

NAME = N’Documents_Log’,

FILENAME = N’C:\SQL2012\Documents_log.ldf’

)

WITH FILESTREAM

(

NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N’Documents’

)

Now that we have created our database, we will add a new FileTable table called

DocumentStore:

CREATE TABLE DocumentStore AS FileTable

WITH

(

FileTable_Directory = ‘DocumentStore’,

FileTable_Collate_Filename = database_default

)

We have not yet added any documents, so we will do that next. Right click on

the newly created FileTable, in our case DocumentStore, to open the menu

and choose Explore FileTable Directory.

You can now query the FileTable to see the documents you have just added:

SELECT [name], file_type, cached_file_size, last_access_time,

is_readonly

FROM DocumentStore

http://visualstudiomagazine.com/articles/2012/10/25/using-filetables-in-sql-2012.aspx

FIRST_VALUE

This function, as its name suggests, returns the first value from an ordered set of

values.

 CREATE TABLE Sales

(

SalesYear int,

SalesAmount int

)

INSERT INTO Sales

VALUES (2000, 100),

(2001, 250),

(2002, 300),

(2003, 400),

(2004, 375)

 

SELECT * FROM Sales

SELECT SalesYear,

SalesAmount,

FIRST_VALUE (SalesAmount)

OVER (ORDER BY SalesAmount) AS FirstYearSales,

SalesAmount – FIRST_VALUE (SalesAmount)

OVER (ORDER BY SalesAmount) AS SalesGrowthSinceFirstYear

FROM Sales

ORDER BY SalesYear

LAST_VALUE

 This is the opposite of FIRST_VALUE and it returns the last value from an ordered

set of values.