Walking through what’s new in querying SQL Server 2012

Hi,

I’ve just been experimenting with the new T-SQL functions and I’ve compiled a set of queries demonstrating them.

This is mainly a demo of the new functionality and I’ve added some useful hints here and there – if anyone has more information about the performance etc, please comment below.

NOTE: I Know this data structure is terrible…don’t bring that up…it’s a demo.

This demo of SQL functionality contains the SQL code for demo data allowing you to execute each query and tinker around with the examples.

First let us begin with a small sample data table:

CREATE TABLE [dbo].[tblA]
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Value VARCHAR(20) NOT NULL
)

Now let uss add some Data to it:

DECLARE
    @end as INT,
    @current as INT

SET @current = 0
SET @end = 250

WHILE @current < @end
    BEGIN
        INSERT INTO tblB
        (Value)
        VALUES
        (CONVERT(VARCHAR(20), @current + 1))
        SET @current = @current + 1
    END

PAGINATION:

The new pagination keywords make paging results set on the server side much easier, however it’s not meant to be a performance tweak, just ease of use.

NOTE: The data set MUST ALWAYS BE ORDERED when paging.

The following query will select everything after the first 10 rows (gets all from row 11 to the end of the set):

SELECT * FROM tblA ORDER BY Id OFFSET 10 ROWS

The OFFSET 10 ROWS does the magic, OFFSET mainly specifies the start position.

The following Queries are 2 different ways of selecting the first 10 rows (gets all up to row 10):

-- Query #1:

SELECT * FROM tblA ORDER BY Id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

-- Query #2:

SELECT TOP 10 * FROM tblA Order By Id

Query #1, shows us how using the new syntax of OFFEST x ROWS and FETCH NEXT n ROWS ONLY
Query #2, shows us the more traditional way of just getting the top 10

I have not come across any documentation which determines performance differences in the above two techniques however the OFFSET and FETCT NEXT would be much easier when selecting from the middle of the table

The following query will select the next 5 rows only after the first 10 rows (gets from row 11 to row 15)

SELECT * FROM tblA ORDER BY Id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

LAG:

The lag function was introduced so we can work with previous rows in the same set of data without needing sub queries or cursors.

The following Query:

SELECT
    Id,
    Value,
    LAG(Value, 1, NULL) OVER (ORDER BY Id) as ValueLessOne,
    LAG(Value, 2, NULL) OVER (ORDER BY Id) as ValueLessTwo
FROM tblA

Based on the data structure we created on the start of this tutorial, we are selecting all the fields in the table (Id, Value) as well as the previous row (ValueLessOne) and the row previous to that (ValueLessTwo)

We are not limited to just selecting these values, we can do calculations on them as well:

SELECT
    Id,
    Value,
    TRY_CONVERT
    (
        INT,
        LAG(Value, 1, NULL) OVER (ORDER BY Id) + CONVERT(INT, Value)
    ) as Computation
FROM tblA

I’m mainly just adding the INT value of the previous row to the INT value of the current row.

A quick Touch on the TRY_CONVERT: if for some reason anything within the TRY CONVERT is not convertible, the result will be NULL.
REFLECTION:

There are a few new “tools” added in to allow us to do a bit of reflection, such as gathering details on our data tables and their columns.

The following query demonstrates:

EXEC sp_describe_first_result_set N'SELECT * FROM tblA'

This returns A LOT of information about each column within the returned result of the query – you may the query as complex as you wish or even execute stored procedures and get detailed information on each field

Now what if we wanted only a specific portion of that data or we wanted to do a more complex query for the details?

SELECT column_ordinal, name, system_type_name FROM sys.dm_exec_describe_first_result_set('SELECT * FROM tblA', NULL, 0)

The following shows us a dynamic view that does the same Reflection as the previous stored proc .
I have focused column ordinate, column name and data type for each column in the data table

EOMONTH:

The EOMONTH function allows us to get the LAST DATE of the MONTH for a specified DATE

SELECT EOMONTH(CONVERT(DATETIME, '2012-04-04 02:20:13'))

This would return “2012-04-30” we are just getting the last DATE of the 4th month, the time is not important to this function.

CHOICE LISTS:

A few of you may have heard about the CHOICE LISTS in LightSwtich which or the CHOOSE function in certain programming Languages, a flavor of this is not available in SQL

SELECT CHOOSE(2, 'A', 'B', 'C')

The first parameter is the selected index and the rest of the parameters are the choices.
In this case, we choose “B”.
If the selected index was 1 we would have chosen “A”

Let us apply this more practically to our sample data:

SELECT TOP 5
    Value,
    CHOOSE(Value, 'A', 'B', 'C', 'D', 'E') as chosenAlphaber,
    CHOOSE(Value, 'Alpha', 'Bravo', 'Charlie', 'Delta', 'Echo') as chosenPhonetic
FROM tblA

OUTPUT:

Value                 chosenAlphaber  chosenPhonetic
-------------------- ---------------- --------------
1                    A                Alpha
2                    B                Bravo
3                    C                Charlie
4                    D                Delta
5                    E                Echo
(5 row(s) affected)

Using the same set of indexes (tblA.Value) I’ve selected the corresponding alphabet of each number as well as the phonetic of each Alphabet by means of a choice list

An alternative would be a look up table.

Realistically the Choice list would be great for more STATIC small sets of data that you do not want permanently stored in the database.
another example:

SELECT TOP 3
    Value,
    CHOOSE(Value, 'True', 'False', 'Undecided') as choice
FROM tblA

CONCAT:

The new concatenation function was introduced as a means of language portability but also carries some hidden advantages.

SELECT
    CONCAT('A', 'L', 'P', 'H', 'A') as withCONCAT,
    'A' + 'L' + 'P' + 'H' + 'A' as withoutCONCAT

OUTPUT:

withCONCAT withoutCONCAT
---------- -------------
ALPHA      ALPHA
(1 row(s) affected)

In the above query, we are just doing the same thing in two different ways, the new way and the old way.
So what makes CONCAT different?

SELECT
    CONCAT('A', 'L', NULL, 'H', 'A') as withCONCAT,
    'A' + 'L' + NULL + 'H' + 'A' as withoutCONCAT

OUTPUT:

withCONCAT withoutCONCAT
---------- -------------
ALHA       NULL
(1 row(s) affected)

If you notice now, when decided to replace one of the letters with an NULL, CONCAT was more stable and produced everything excluding the NULL whereas the old way fell over and returned NULL.

Depending on your situation having the NULL may be more useful or not.