What has changed in SQL Server 2008 compared to T-SQL - In Developers' view

Views: 574 views

In this article

This post is the 1 part of 2 in the series. What has changed in T-SQL

What has changed in SQL Server 2008 in relation to T-SQL - In the view of Developers
What has changed in SQL Server 2012 in relation to T-SQL - In the view of Developers

Reading Time: 6 minutes

Speak guys!
All right ?

In today's post, I will share with you a research that I have been doing for some time, about the new features of SQL Server in each version, with a focus on query developers and database routines. In the environments I work in, I see that many end up “reinventing the wheel” or creating UDF functions to perform certain tasks (which we know are terrible for performance) when SQL Server itself already provides native solutions for this.

My goal in this post is to help you, who are using older versions of SQL Server, assess what advantages and new features (developer view only) you will have access to when upgrading your SQL Server.

My initial idea was to create a post with just about everything that changed from 2008 to 2019, but after chatting and getting some feedback from great professionals that I consider, like Edvaldo Castro, Nilton Pinheiro, Caio Lover and the Ariel fernandez, I will, for the first time in the blog, start a series of posts, so that the article does not get too big and tiring to read ..

So, I start the series “What has changed in SQL Server X in relation to T-SQL - In the view of Developers”, starting with SQL Server 2008 and I will publish a post for each version until arriving in 2019. After all the articles published , I will create an article to centralize this whole series.

SQL Server - What has changed in T-SQL in version 2008?

New data types: date, time, datetime2, datetimeoffset

View content

In the 2008 release, SQL Server introduces us with new data types to handle with date and time. Prior to SQL Server 2008, there was the DATETIME field, but for working with hours there was no specific data type for it.

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

This is much simpler today using the DATE type:

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

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

Data types in table format

View content

Resources available since SQL Server 2008, we can now declare types with table structures and use them as parameters in SP's and functions, as shown 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

-- Cria o tipo tpPessoa

CREATE TYPE dbo.tpPessoa AS TABLE (

Nome varchar(100),

Idade INT,

Dt_Nascimento DATE

)

-- Cria uma procedure que você irá receber como parâmetro uma variável do tipo tpPessoa

CREATE PROCEDURE dbo.stpExibe_Pessoa (

@Pessoa tpPessoa READONLY

)

BEGIN

SELECT *

FROM @Pessoa

END

-- 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 for Stored Procedures and Functions.

Assign values during variable declaration

View content

Starting with SQL Server 2008, you can assign values already in the variable declaration, making it easier and reducing the number of lines of code you need.

Examples of use:

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

Grouping data with GROUPING SETs

View content

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

Usage example - 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)

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 usage - 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)

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)

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? Be sure to access the post SQL Server - Grouping Data Using ROLLUP, CUBE, and GROUPING SETS.

Using MERGE for INSERT, DELETE, and UPDATE with just 1 Command

View content

From SQL Server 2008, you can now use the MERGE command for INSERT, DELETE, and UPDATE with only 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 target table, the values will be updated according to the source table. If this identifier does not exist, this record will be inserted into the target table.

Example of use:

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);

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);

Want to learn more about the MERGE command and see several other examples, including stpETL_Upsert, which automatically creates MERGE commands for you? Be sure to visit my post SQL Server - How to use MERGE command to enter, update and delete data with 1 command only.

Multiple INSERT's with 1 single command

View content

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

Example of use:

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

INSERT INTO contacts

VALUES

('John Doe', '425-333-5321'),

('Jane Doe', '206-123-4567'),

('John Smith', '650-434-7869')

Remember the maximum amount of values that can be entered in a single INSERT is 1.000 records.

Using compound value assignment operators

View content

From SQL Server 2008, we can use compound value assignment operators, which are these:

+ = Add and assign value
- = Substrair 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

Examples of use:

Spatial data types

View content

SQL Server 2008 introduced spatial datatypes into the DBMS, which allows us to represent the physical location or shape of any geometry using only T-SQL. We can use this kind 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

DECLARE @point geometry;

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

SELECT

@point.STX, @point.STY

Result

2 Example

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)

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

I hope you enjoy this theme, the series I am starting and leave your doubts in the comments.
Hug!

Jose says said:

26 from 2018 from September to 07: 54

When I need to manipulate part of date & time expressions and there is no native function I use the formula “origin of time”. For example, to reset the time, it would be like this:
dateadd (day, datediff (day, 0, DateTime), 0)

It is useful when we need to get, for example, only the day and the hours (neglecting minutes and seconds):
dateadd (hour, datediff (hour, 0, DateTime), 0)

It also works to get month end, month start etc.

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

What has changed in SQL Server 2008 in relation to T-SQL - In the view of Developers

SQL Server - What has changed in T-SQL in version 2008?

New data types: date, time, datetime2, datetimeoffset

Data types in table format

Assign values during variable declaration

Grouping data with GROUPING SETs

Using MERGE for INSERT, DELETE, and UPDATE with just 1 Command

Multiple INSERT's with 1 single command

Using compound value assignment operators

Spatial data types

You may also like ...

1 Response

Leave a Comment Cancel reply

SQL Server - What has changed in T-SQL in version 2008?

New data types: date, time, datetime2, datetimeoffset

Data types in table format

Assign values ​​during variable declaration

Grouping data with GROUPING SETs

Using MERGE for INSERT, DELETE, and UPDATE with just 1 Command

Multiple INSERT's with 1 single command

Using compound value assignment operators

Spatial data types

You may also like ...

Webcast - 300 Celebration posts + SQL Server 2019 What's New (04 / 10 / 2018 - 21h)

SQL Server - Cannot resolve the collation conflict between… in the same as operation.

SQL Server – How to list the largest tables and indexes in the database and show the size of each

1 Response

Leave a Comment Cancel reply

Assign values during variable declaration