What changed in SQL Server 2008 regarding T-SQL - From the developers perspective

In version 2008, SQL Server introduces us to new data types to deal with date and time. Before SQL Server 2008, there was the DATETIME field, but to work with times there was no specific data type for that.

To take a DATETIME field and return only the date part, without the time, one of the solutions (and a very common one) was to use the CFC (Cast, Floor, Cast) technique:

Currently, this is much simpler using the DATE type:

And I will demonstrate a little about the other types of data:

New data types: date, team, datetime2, datetimeoffset.

Features available since SQL Server 2008, we can now declare types with table structures and use them as parameters in SP’s and functions, as per the example below:

-- Cria o tipo tpPessoa
CREATE TYPE dbo.tpPessoa AS TABLE (
    Nome varchar(100),
    Idade INT,
    Dt_Nascimento DATE
)
GO

-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa
CREATE PROCEDURE dbo.stpExibe_Pessoa (
    @Pessoa tpPessoa READONLY
)
AS
BEGIN

    SELECT *
    FROM @Pessoa

END
GO


-- Instancia uma variável @Variavel_Pessoa, do tipo tpPessoa, popula os dados e executa a SP stpExibe_Pessoa
DECLARE @Variavel_Pessoa AS tpPessoa

INSERT INTO @Variavel_Pessoa
(
    Nome,
    Idade,
    Dt_Nascimento
)
VALUES
( 'Dirceu Resende', 31, '1987-05-28'),
( 'Patrícia', 31, '1987-01-15'),
( 'Letícia', 21, '1997-04-15')


EXEC dbo.stpExibe_Pessoa 
    @Pessoa = @Variavel_Pessoa -- tpPessoa

Result:

To learn more about this, visit the post SQL Server – How to pass a table as a parameter to Stored Procedures and Functions.

Starting with SQL Server 2008, it is possible to assign values ​​already in the variable declaration, making it easier and reducing the number of lines of code needed.

Usage examples:

Note: This functionality is not applied to variables of type TEXT, NTEXT and IMAGE.

A very well-received feature from SQL Server 2008 is the use of GROUPING SETS, CUBE and ROLLUP to create groupings of data, totals and sub-totals in a simple way and with few changes to your original query.

Example of use – ROLLUP

SELECT 
    ISNULL(B.Ds_Categoria, 'Total') AS Ds_Categoria,
    ISNULL(B.Ds_Produto, 'Subtotal') AS Ds_Produto,
    COUNT(*) AS Qt_Vendas,
    SUM(B.Preco) AS Vl_Total
FROM 
    #Vendas A
    JOIN #Produtos B ON A.Cd_Produto = B.Codigo
GROUP BY
    ROLLUP(B.Ds_Categoria, B.Ds_Produto)

Example of use – CUBE

SELECT
    ISNULL(CONVERT(VARCHAR(10), MONTH(A.Dt_Venda)), 'Total') AS Mes_Venda, 
    ISNULL(B.Ds_Categoria, 'Subtotal') AS Ds_Categoria,
    COUNT(*) AS Qt_Vendas,
    SUM(B.Preco) AS Vl_Total
FROM 
    #Vendas A
    JOIN #Produtos B ON A.Cd_Produto = B.Codigo
GROUP BY
    CUBE(MONTH(A.Dt_Venda), B.Ds_Categoria)

Usage example – GROUPING SETS

SELECT
    ISNULL(B.Ds_Produto, 'Total') AS Ds_Produto, 
    ISNULL(B.Ds_Categoria, 'Subtotal') AS Ds_Categoria,
    COUNT(*) AS Qt_Vendas,
    SUM(B.Preco) AS Vl_Total
FROM 
    #Vendas A
    JOIN #Produtos B ON A.Cd_Produto = B.Codigo
GROUP BY
    GROUPING SETS(B.Ds_Categoria, B.Ds_Produto)

Want to know more about this feature? Don't forget to access the post SQL Server – Grouping data using ROLLUP, CUBE and GROUPING SETS.

Starting with SQL Server 2008, it is now possible to use the MERGE command to INSERT, DELETE and UPDATE with just 1 SQL statement. Its operation is simple: One or more columns of the tables involved are considered keys (identifiers) so if the key value exists in the destination table, the values ​​will be updated according to the source table. If this identifier does not exist, this record will be inserted into the destination table.

Usage example:

MERGE 
    dbo.Dim_Venda AS Destino
USING 
    dbo.Venda AS Origem ON (Origem.Id_Venda = Destino.Id_Venda)

-- Registro existe nas 2 tabelas
WHEN MATCHED THEN
    UPDATE SET 
        Destino.Dt_Venda = Origem.Dt_Venda,
        Destino.Id_Produto = Origem.Id_Produto,
        Destino.Quantidade = Origem.Quantidade,
        Destino.Valor = Origem.Valor

-- Registro não existe no destino. Vamos inserir.
WHEN NOT MATCHED THEN
    INSERT
    VALUES(Origem.Id_Venda, Origem.Dt_Venda, Origem.Id_Produto, Origem.Quantidade, Origem.Valor);

Do you want to know more about the MERGE command and view several other examples, including stpETL_Upsert, which automatically creates MERGE commands for you? Don't forget to visit my post SQL Server – How to use the MERGE command to insert, update and delete data with just 1 command.

Starting with SQL Server 2008, it is now possible to insert multiple values ​​in a single INSERT command with VALUES. Although it was already possible to do this using INSERT… SELECT, this feature helps a lot during everyday life.

Usage example:

INSERT INTO contacts 
VALUES 
    ('John Doe', '425-333-5321'), 
    ('Jane Doe', '206-123-4567'), 
    ('John Smith', '650-434-7869')

It is worth remembering the maximum number of values ​​that can be inserted in a single INSERT is 1,000 records.

Starting with SQL Server 2008, we can use operators for assigning compound values, which are these:

• += Add and assign value
• -= Subtract and assign value
• *= Multiply and assign value
• /= Split and assign value
• %= Module and assign value
• &= Bitwise AND and assign value
• ^= Bitwise XOR and assign value
• |= Bitwise OR and assign value

Usage examples:

SQL Server 2008 introduced special data types (spatial datatypes) in the DBMS, which allows us to represent the physical location or shape of any geometric figure, using only T-SQL. We can use this type of data to represent countries, streets, cities, etc. These data types are implemented using the .NET Common Language Runtime (CLR).

Example 1:

DECLARE @point geometry;
SET @point = geometry::STGeomFromText ('POINT (25 18)', 0);

SELECT 
    @point.STX, @point.STY

Result

Example 2

SELECT geometry::Point(10,10,0).STBuffer(1)
UNION ALL
SELECT geometry::Point(20,20,0).STBuffer(1)
UNION ALL
SELECT geometry::Point(25,10,0).STBuffer(1)

Result

Available methods:

• STLength
• STStartPoint
• STEndPoint
• STPointN
• STNumPoints
• STIsSimple
• STIsClosed
• STIsRing