FIRST_VALUE AND LAST_VALUE SQL Server 2012 Feature

FIRST_VALUE

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

values. This is really useful as we can calculate, inside a result set, the difference
between an initial value and each subsequent value on-the-fly. With no variables
and no re-querying, everything is done inside a single piece of Transact SQL:

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

Running this query will return the following result set:

SalesYear	SalesAmount	FirstYearSales	Grwoth from last year
2000	100	100	0
2001	150	100	50
2002	200	100	100
2003	250	100	150

Same way things will work for LAST_VALUE

SEQUENCE in SQL Server 2012

You are no doubt used to IDENTITY and how this works with a seed and increment

value. SEQUENCE is structured in a very similar way, but with fewer limitations,
giving it a welcome flexibility.

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.

CREATE SEQUENCE mySequence AS int

START WITH 1
INCREMENT BY 1

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]

Result

NextValue

1

We can see that the SEQUENCE has not been used:

Next we will create a table so we can put SEQUENCE to the test.
Run the following code to create the Employee table:

CREATE TABLE Employee

(

EmployeeID int NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL
)

Now we will insert a couple of rows. Note that in the following code we use NEXT

VALUE FOR just as we did in the preceding code to return the next SEQUENCE value.
This will increment the SEQUENCE, in our case by one, though you can set this to
be whatever you wish when you declare the SEQUENCE:

INSERT INTO Employee (EmployeeID, FirstName, LastName)

VALUES
(NEXT VALUE FOR mySequence, 'Rachel', 'Clements'),
(NEXT VALUE FOR mySequence, 'Jon', 'Reade')
GO

SELECT * FROM Employee

EmployeeID	FName	LName
2	Raj	K
3	Randheer	P

The SEQUENCE doesn't have to be unique; we can reset the seed to use the same

value again. If we had a unique constraint on our EmployeeID column we would
not be able to do this, but because we have not added a constraint, we can have
some fun.

Run the following statement:

ALTER SEQUENCE mySequence
RESTART WITH 1

If again insert the value by using it the new value would be 1.
If we wanted to set a minimum and maximum value we could have declared our
SEQUENCE as follows:
CREATE SEQUENCE mySequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 15

However we can change the maximum value using an ALTER statement.

If we wanted to restrict the number of rows inserted into a table we could use a

SEQUENCE object to limit this number. So as you can see, the seed and increment
values operate in the same fashion as IDENTITY, but you are free to use and
manipulate it.

WithResultSet in SQL Server 2012

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.

For Example the following procedure returns a straightforward result set using the Employee table

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

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)

)

)

NI_NUMBER	FirstName	LASTNAME
1	John	M
2	Ram	S
3	RAJ	D
5	Mano	C

CONCAT Function in SQL Server 2012

The job of 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. If you have used it in Excel you will easily understand it.

The basic structure is as follows:

CONCAT ( string_value_1, string_value_1 [, string_value_n ] )

So now need to check how it works:

For this create a temporary table and add a couple of records:

CREATE TABLE #Customer

(
FirstName varchar(30) NOT NULL,
MiddleName varchar(30) NULL,
LastName varchar(30) NOT NULL
)

INSERT INTO #Customer

VALUES (‘Randheer’, 'Singh', 'Parmar'), ('Raj', NULL, 'kapur')

We have our customer table, so now let us use the CONCAT function to return the

full name of our customers:

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

CustomerName
FROM #Customer

Result will be

Randheer Singh Parmar
Raj kapur

Analytics Function in SQL Server 2012

Analytics quite a buzzword these days in the market. There are few tools in market which do analytics such as SAS, SPSS, R language.
My expertise is in SQL Server and trying to relate analytics with SQL Server and many times end up in comparing SAS and SQL Server.Dig out SQL Server for handling analytics. Got some functions which were introduced in SQL Server 2010.

Lead, Lag, Cumulative Percentage and many other such functions. Still can’t see the other algorithms such as logistic regression, linear regression as a part of SQL Server data base engine.
These all are the part of SQL Server Analysis Services data mining activities. In SSAS data mining you can implement most of the common analytics algorithm to validate the data.

This was just an Introduction and the flavour of my focus for next posts and my development area in the SQL Server. I am looking for competition that SQL Server can give to analytics tools likes SAS. How SSAS can be improved to incorporate those changes.

Stay connected will so many more ways to do analytics reporting in SQL Server.

Below is the Link for Analytics newly introduced function in sql server.

Analytics functions