New T-SQL features in SQL server 2012
OFFSET and FETCH
OFFSET and FETCH are two new clauses introduced in SQL
Server 2012. Used together in your queries, they allow you to extract a portion
of rows from your result set.
Use [AdventureWorks2012]
Go
SELECT BusinessEntityID, FirstName,
MiddleName, LastName
FROM [Person].[Person]
ORDER BY
BusinessEntityID;
The following result set would be returned:
However, what if you wanted to retrieve just a selection
of rows from within that result set and using the SELECT TOP statement doesn't
deliver the record you need? In comes the combination of OFFSET and FETCH.
Take a look at the following query:
Use [AdventureWorks2012]
Go
SELECT BusinessEntityID, FirstName,
MiddleName, LastName
FROM [Person].[Person]
ORDER BY
BusinessEntityID
OFFSET 100 ROWS
FETCH NEXT 5 ROWS ONLY;
Here, the OFFSET tells the query to ignore the first 100
rows and then return only the following five rows.
A great practical use for this is in applications and
websites where you wish to display just a small selection of records.
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.
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.
SELECT NEXT
VALUE FOR mySequence AS
[Next Value];
We will create a table so we can put Sequence to the test.
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.
INSERT INTO
Employee (EmployeeID, FirstName, LastName)
VALUES
(NEXT VALUE FOR
mySequence, 'Rachel', 'Clements'),
(NEXT VALUE FOR
mySequence, 'Jon', 'Reade')
GO
SELECT *
FROM Employee
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.
ALTER SEQUENCE mySequence
RESTART WITH
1
INSERT INTO
Employee (EmployeeID, FirstName, LastName)
VALUES
(NEXT VALUE FOR
mySequence, 'John', 'Smith'),
(NEXT VALUE FOR
mySequence, 'Simon', 'Jones')
GO
SELECT *
FROM Employee
You can reset the value by right-clicking on the SEQUENCE
in the Object Explorer pane and choosing Properties. Check the Restart
sequence box and click on the OK button:
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. We shall set this maximum value to 10:
ALTER SEQUENCE mySequence
MAXVALUE 10
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.
Use [AdventureWorks2012]
Go
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.
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.
EXEC spGet_Employees
WITH RESULT SETS
(
(
NI_Number varchar(15),
FirstName varchar(30),
Surname varchar(30)
)
);
This is an easy and flexible option for executing a
stored procedure and transforming the result set to be in the format you need.
Error handling with THROW
In T-SQL it consists of a TRY block and a CATCH block,
which must always be paired. If an error occurs in the TRY block, it is passed
to the CATCH block code to handle.
In previous versions of SQL Server you would use
@@RAISE_ERROR and would need to neatly collect the error data and return this.
BEGIN TRY
DECLARE @MyInt int
SET @MyInt = 1 / 0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity =
ERROR_SEVERITY()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1)
END CATCH
In the preceding CATCH block, there is a lot of work
going on to collect the details
of this error. Now there is a slicker way of finding out
what has caused the error.
Compare the above code to the code below:
BEGIN TRY
DECLARE @MyInt int
SET @MyInt = 1/0
END TRY
BEGIN CATCH
-- throw out the error
THROW
END CATCH
It is worth using THROW as this one word can tell you
just as much, with less effort.
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.
We will create a database with FILESTREAM functionality
so we can add a FileTable table.
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'
)
We will add a
new FileTable table called DocumentStore:
CREATE TABLE
DocumentStore AS FileTable
WITH
(
FileTable_Directory =
'DocumentStore',
FileTable_Collate_Filename = database_default
)
If you refresh your database list in SSMS, you will
see your new database. Expand the Tables node and there is a folder
called FileTables. Under here is the DocumentStore table. Expand
the table node to see the full column list:
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.
This will open a Windows Explorer window, which
will be empty as nothing is in there yet. Drag in some files, just as you would
to any other file location and return to SSMS.
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;
This is an incredibly powerful feature as it allows you
to use an application to publicise the metadata for any documents you store in
the FileTable data type, without having direct access to the Windows file
system.
New T-SQL analytical functions
CREATE TABLE
#Sales
(
SalesYear int,
SalesAmount int
)
INSERT INTO
#Sales
VALUES (2000,
100),
(2001, 250),
(2002, 300),
(2003, 400),
(2004, 375);
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.
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.
LAG allows you to access a value in the previous
(lagging) row, without joining
a result set to itself. This is really helpful in
comparing sales from previous years,
or a server state compared to its previous value:
SELECT SalesYear, SalesAmount, LAG(SalesAmount,1,0)
OVER (ORDER BY SalesYear) AS
PreviousYearSalesAmount,
SalesAmount -
LAG(SalesAmount,1,0)
OVER (ORDER BY SalesYear) AS
YearOnYearDifference
FROM #Sales
LEAD
No comments:
Post a Comment