SQL Server End to End: T-SQL, Views, Procedures, Functions, SSIS and SSRS

There is a job profile that keeps coming up in the Brazilian market: BI and database analyst on the Microsoft stack. The wording changes, the tools do not — T-SQL, Views, Stored Procedures, Functions, SSIS, SSRS, Excel and, as a bonus, Power BI, a Data Warehouse and an ERP such as Dynamics AX or Navision.

This guide covers that whole set, in the order it shows up day to day: first the language, then the objects you create inside the database, then investigating wrong data, then the loading and reporting tools, and finally the analytical layer.

Every example is real T-SQL, testable on SQL Server. Where the behaviour changes between versions, it is noted.

Do not memorise syntax. Understand where each piece fits — you can look syntax up, but you need to know the fit by heart to pick the right tool at the right moment.

Contents

• The lay of the land · how the pieces fit together
• T-SQL · the language that holds everything up
• Views · when they help and when they get in the way
• Stored Procedures · the workhorse
• Functions · the three forms and the performance trap
• Inconsistencies · investigating and fixing wrong data
• Performance · execution plan, indexes and SARGability
• SSIS · loads, integrations and validations
• SSRS · paginated reports
• Data Warehouse · dimensional modelling and SCD
• Power BI and Excel · the analysis layer
• ERPs · Dynamics AX and Navision from the inside
• QuickSight · the AWS cousin
• A roadmap · where to start

The lay of the land: how the pieces fit together

Before the code, the map. A BI operation on the Microsoft stack usually has four layers:

The classic beginner's mistake is thinking those layers compete. They do not — each one solves a problem the others solve badly:

• SSRS delivers a paginated report, with a fixed layout, made to be printed or turned into a PDF: an invoice, a statement, a 300-page accounting report. It also schedules its own delivery by email.
• Power BI delivers exploration: the user filters, cross-references, digs in. Terrible for printing.
• Excel delivers freedom: the analyst pulls the data and does whatever they want with it.

If the request is "I need the closing report as a PDF on the 1st of every month at 6 a.m. in the board's inbox", the answer is SSRS. If it is "I want to understand why the margin fell in the South", the answer is Power BI. Confusing the two generates months of rework.

T-SQL: the language that holds everything up

T-SQL is Microsoft's SQL dialect. Everything that comes afterwards — a procedure, a view, an SSIS package, a report dataset — is T-SQL underneath.

The SELECT you write every day

SELECT  ped.PedidoID,
        cli.Nome,
        ped.DataPedido,
        ped.ValorTotal
FROM    dbo.Pedidos AS ped
JOIN    dbo.Clientes AS cli ON cli.ClienteID = ped.ClienteID
WHERE   ped.DataPedido >= '2026-01-01'
  AND   ped.Status = 'FATURADO'
ORDER BY ped.DataPedido DESC;

Three habits that separate professional code from improvised code:

1. Always qualify the schema (dbo.Pedidos, not Pedidos). Without it, SQL Server looks in the user's schema first, and the execution plan is not reused across different users.
2. Always use an alias and prefix the columns. When someone adds a Nome column to Pedidos two years from now, your query will not break with "ambiguous column name".
3. Never SELECT * in code going to production. It brings columns you do not use, breaks when the table changes and stops the optimiser using covering indexes.

Dates: the most expensive everyday mistake

This is the query that shows up in every slow database:

-- WRONG: the function on the column prevents the index being used
SELECT * FROM dbo.Pedidos
WHERE YEAR(DataPedido) = 2026 AND MONTH(DataPedido) = 3;

Applying a function to the column makes the predicate non-SARGable — SQL Server has to compute YEAR() on every row of the table to know which ones qualify. The index on DataPedido becomes decoration.

-- RIGHT: an open-ended upper bound, and the index is used
SELECT PedidoID, ClienteID, ValorTotal
FROM   dbo.Pedidos
WHERE  DataPedido >= '2026-03-01'
  AND  DataPedido <  '2026-04-01';

Note the < on the upper bound instead of BETWEEN '2026-03-01' AND '2026-03-31'. With datetime, BETWEEN loses everything that happened between 2026-03-31 00:00:00.001 and 23:59:59.997. The half-open interval is always right, with any date type.

And always use the YYYYMMDD or YYYY-MM-DD format in literals: they are the only ones SQL Server interprets the same way under any language setting.

JOINs and the LEFT JOIN that silently became an INNER

-- The classic trap: filtering the right-hand table in the WHERE
SELECT cli.Nome, ped.PedidoID
FROM   dbo.Clientes AS cli
LEFT JOIN dbo.Pedidos AS ped ON ped.ClienteID = cli.ClienteID
WHERE  ped.Status = 'FATURADO';   -- <- discards customers with no order

The WHERE runs after the JOIN. A customer with no order has a null ped.Status, and NULL = 'FATURADO' is false — the customer vanishes from the result, and the LEFT JOIN becomes an INNER JOIN in practice.

-- Right: the right-hand table's condition goes in the ON
SELECT cli.Nome, ped.PedidoID
FROM   dbo.Clientes AS cli
LEFT JOIN dbo.Pedidos AS ped
       ON ped.ClienteID = cli.ClienteID
      AND ped.Status = 'FATURADO';

CTE: the query you read from top to bottom

WITH creates a named, temporary result. It serves to break a large query into readable steps:

WITH VendasMes AS (
    SELECT  VendedorID,
            EOMONTH(DataPedido) AS Mes,
            SUM(ValorTotal)     AS Total
    FROM    dbo.Pedidos
    WHERE   Status = 'FATURADO'
    GROUP BY VendedorID, EOMONTH(DataPedido)
),
Ranking AS (
    SELECT  *,
            RANK() OVER (PARTITION BY Mes ORDER BY Total DESC) AS Posicao
    FROM    VendasMes
)
SELECT  ven.Nome, r.Mes, r.Total, r.Posicao
FROM    Ranking AS r
JOIN    dbo.Vendedores AS ven ON ven.VendedorID = r.VendedorID
WHERE   r.Posicao <= 3
ORDER BY r.Mes, r.Posicao;

An honest caveat: a CTE is not a temporary table. It materialises nothing — the optimiser expands its text wherever it is used. If you reference the same CTE three times, it is executed three times. When the intermediate result is expensive and reused, a #temporary table is usually faster.

Recursive CTE: hierarchies

WITH Hierarquia AS (
    -- anchor: the top of the tree
    SELECT  FuncionarioID, Nome, GerenteID, 0 AS Nivel
    FROM    dbo.Funcionarios
    WHERE   GerenteID IS NULL

    UNION ALL

    -- recursion: each level pulls in the next
    SELECT  f.FuncionarioID, f.Nome, f.GerenteID, h.Nivel + 1
    FROM    dbo.Funcionarios AS f
    JOIN    Hierarquia AS h ON h.FuncionarioID = f.GerenteID
)
SELECT REPLICATE('  ', Nivel) + Nome AS Estrutura, Nivel
FROM   Hierarquia
ORDER BY Nivel
OPTION (MAXRECURSION 100);

The MAXRECURSION is a safety catch: without it, a cycle in the data (A manages B, B manages A) runs until it blows up.

Window functions: what saves the most time

Window functions calculate over a set of rows without collapsing the result, unlike GROUP BY.

SELECT  PedidoID,
        ClienteID,
        DataPedido,
        ValorTotal,
        -- the customer's running total over time
        SUM(ValorTotal) OVER (PARTITION BY ClienteID ORDER BY DataPedido
                              ROWS UNBOUNDED PRECEDING) AS Acumulado,
        -- how much the same customer's previous order was
        LAG(ValorTotal) OVER (PARTITION BY ClienteID ORDER BY DataPedido) AS PedidoAnterior,
        -- the order's position within the customer
        ROW_NUMBER() OVER (PARTITION BY ClienteID ORDER BY DataPedido DESC) AS Ordem
FROM    dbo.Pedidos;

The ROWS UNBOUNDED PRECEDING clause matters more than it looks. Without it, the default is RANGE UNBOUNDED PRECEDING, which treats tied values as a single block and uses a slower on-disk spool. With repeated dates, the two give different results. Write ROWS whenever the intention is "row by row".

APPLY: the JOIN that can see the left-hand row

CROSS APPLY and OUTER APPLY are exclusive to SQL Server and solve the classic "the latest record of each group":

SELECT  cli.Nome,
        ult.PedidoID,
        ult.DataPedido,
        ult.ValorTotal
FROM    dbo.Clientes AS cli
OUTER APPLY (
    SELECT TOP (1) ped.PedidoID, ped.DataPedido, ped.ValorTotal
    FROM   dbo.Pedidos AS ped
    WHERE  ped.ClienteID = cli.ClienteID    -- it sees the outer row
    ORDER BY ped.DataPedido DESC
) AS ult;

OUTER APPLY keeps the customer even with no order (like LEFT JOIN); CROSS APPLY discards them (like INNER JOIN).

MERGE: use with care

MERGE does an insert, an update and a delete in a single statement. It is elegant and it has a documented history of bugs from Microsoft themselves, especially with partitioned tables, triggers and foreign keys.

-- A safe, predictable alternative to MERGE
BEGIN TRANSACTION;

UPDATE  dest
SET     dest.Nome = orig.Nome,
        dest.Email = orig.Email,
        dest.AtualizadoEm = SYSDATETIME()
FROM    dbo.Clientes AS dest
JOIN    stg.Clientes AS orig ON orig.ClienteID = dest.ClienteID
WHERE   dest.Nome <> orig.Nome OR dest.Email <> orig.Email;

INSERT INTO dbo.Clientes (ClienteID, Nome, Email, AtualizadoEm)
SELECT  orig.ClienteID, orig.Nome, orig.Email, SYSDATETIME()
FROM    stg.Clientes AS orig
WHERE   NOT EXISTS (SELECT 1 FROM dbo.Clientes AS dest
                    WHERE dest.ClienteID = orig.ClienteID);

COMMIT;

The WHERE dest.Nome <> orig.Nome OR ... on the UPDATE is not a detail: without it you rewrite identical rows, generate transaction log for nothing and fire triggers needlessly.

Views: when they help and when they get in the way

A view is a saved query with a name. It stores no data — every time you query it, the query behind it runs.

CREATE OR ALTER VIEW dbo.vw_PedidosFaturados
AS
SELECT  ped.PedidoID,
        ped.ClienteID,
        cli.Nome         AS ClienteNome,
        ped.DataPedido,
        ped.ValorTotal
FROM    dbo.Pedidos  AS ped
JOIN    dbo.Clientes AS cli ON cli.ClienteID = ped.ClienteID
WHERE   ped.Status = 'FATURADO';
GO

What it is really for:

• Security: granting access to the view without granting access to the table, hiding sensitive columns (salary, tax ID).
• A stable contract: the report consumes the view; if the table changes, you adjust the view and the report never notices.
• Standardising a rule: the definition of "an invoiced order" lives in one place, instead of being repeated across twenty queries.

Where it becomes a problem: a view on a view on a view. Each layer looks tidy, and the final execution plan becomes a monster that reads tables nobody asked for. Two layers is reasonable; four is technical debt.

Indexed view: when the view starts storing data

With SCHEMABINDING and a unique clustered index, the result is materialised on disk and kept up to date by SQL Server:

CREATE OR ALTER VIEW dbo.vw_VendasPorDia
WITH SCHEMABINDING
AS
SELECT  CAST(DataPedido AS date) AS Dia,
        COUNT_BIG(*)             AS Pedidos,
        SUM(ValorTotal)          AS Total
FROM    dbo.Pedidos              -- schema mandatory with SCHEMABINDING
WHERE   Status = 'FATURADO'
GROUP BY CAST(DataPedido AS date);
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_VendasPorDia ON dbo.vw_VendasPorDia (Dia);

Rules that catch everyone out: COUNT_BIG(*) is mandatory in an aggregated view (not COUNT(*)), SUM over a nullable column is forbidden, and no OUTER JOIN, subquery or DISTINCT. In exchange, an aggregation that took minutes starts answering instantly — at the cost of making every INSERT on the base table a little slower.

Stored Procedures: the workhorse

A procedure is T-SQL code stored in the database, with parameters. It is where the loading, validation and business-rule logic lives.

The skeleton you should copy

CREATE OR ALTER PROCEDURE dbo.usp_FaturarPedido
    @PedidoID   INT,
    @UsuarioID  INT,
    @Faturado   BIT = 0 OUTPUT
AS
BEGIN
    -- Without this, each statement returns "(N rows affected)" and some drivers
    -- (including SSIS's) treat that message as one more result set.
    SET NOCOUNT ON;

    -- Ensures any error aborts the whole transaction, not just the statement.
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF NOT EXISTS (SELECT 1 FROM dbo.Pedidos WHERE PedidoID = @PedidoID)
        BEGIN
            THROW 50001, 'Pedido não encontrado.', 1;
        END

        UPDATE  dbo.Pedidos
        SET     Status       = 'FATURADO',
                FaturadoEm   = SYSDATETIME(),
                FaturadoPor  = @UsuarioID
        WHERE   PedidoID     = @PedidoID
          AND   Status       = 'ABERTO';   -- idempotent: re-invoicing does nothing

        SET @Faturado = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END;

        INSERT INTO dbo.LogFaturamento (PedidoID, UsuarioID, OcorridoEm)
        VALUES (@PedidoID, @UsuarioID, SYSDATETIME());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        INSERT INTO dbo.LogErro (Rotina, Mensagem, Numero, Linha, OcorridoEm)
        VALUES ('usp_FaturarPedido', ERROR_MESSAGE(), ERROR_NUMBER(),
                ERROR_LINE(), SYSDATETIME());

        THROW;   -- rethrows preserving the number, message and severity
    END CATCH
END
GO

Five details in that block that make a difference:

• SET NOCOUNT ON eliminates the count messages that confuse applications and SSIS packages.
• SET XACT_ABORT ON guarantees a rollback on errors that, on their own, would not abort the transaction — leaving it open and locking the table.
• XACT_STATE() distinguishes a still-valid transaction from one that is already doomed; IF @@TRANCOUNT > 0 on its own does not cover that case.
• THROW with no arguments rethrows the original error. RAISERROR requires rebuilding the message and loses the error number.
• AND Status = 'ABERTO' makes the procedure idempotent: calling it twice does not invoice twice. In an integration, that is worth gold.

Parameter sniffing: the slowness that appears out of nowhere

SQL Server compiles the procedure's plan based on the first value it receives and reuses it. If the first call was @ClienteID = 12345 (three orders) and the next is for a customer with two million orders, the plan optimised for three rows is used for two million.

-- Recompiles only this statement on each execution; the rest of the procedure stays cached
SELECT PedidoID, ValorTotal
FROM   dbo.Pedidos
WHERE  ClienteID = @ClienteID
OPTION (RECOMPILE);

Alternatives depending on the case: OPTIMIZE FOR UNKNOWN (uses the average from the statistics), copying the parameter into a local variable (a similar effect) or, on SQL Server 2022+, letting Parameter Sensitive Plan optimization sort it out.

When the procedure got slow "without anyone touching anything", parameter sniffing is the first suspect.

Table-Valued Parameter: sending a whole batch

Instead of calling the procedure a thousand times, send the thousand rows at once:

CREATE TYPE dbo.TipoItemPedido AS TABLE (
    ProdutoID  INT     NOT NULL,
    Quantidade INT     NOT NULL,
    ValorUnit  DECIMAL(18,2) NOT NULL
);
GO

CREATE OR ALTER PROCEDURE dbo.usp_InserirItens
    @PedidoID INT,
    @Itens    dbo.TipoItemPedido READONLY   -- a TVP is always READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.PedidoItens (PedidoID, ProdutoID, Quantidade, ValorUnit)
    SELECT @PedidoID, ProdutoID, Quantidade, ValorUnit
    FROM   @Itens;
END
GO

Functions: the three forms and the trap

SQL Server has three types of function, and choosing the wrong one costs hours of execution time.

1. Scalar — avoid it

It returns a single value. It looks harmless and it is the biggest performance killer on the stack:

CREATE OR ALTER FUNCTION dbo.fn_CalculaDesconto (@Valor DECIMAL(18,2))
RETURNS DECIMAL(18,2)
AS
BEGIN
    RETURN CASE WHEN @Valor > 1000 THEN @Valor * 0.10 ELSE 0 END;
END
GO

Used in a SELECT over a million rows, it is called a million times, once per row, and until SQL Server 2017 it blocked parallelism on the entire query. SQL Server 2019 introduced automatic scalar inlining, which solves some cases — not all, and you do not always control the server's version.

2. Inline Table-Valued — always prefer it

A RETURN with a single query. The optimiser expands it inside the calling query, as if it were a parameterised view. Practically zero cost:

CREATE OR ALTER FUNCTION dbo.tvf_PedidosDoCliente (@ClienteID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT PedidoID, DataPedido, ValorTotal
    FROM   dbo.Pedidos
    WHERE  ClienteID = @ClienteID
      AND  Status = 'FATURADO'
);
GO

-- A natural fit with APPLY
SELECT cli.Nome, ped.PedidoID, ped.ValorTotal
FROM   dbo.Clientes AS cli
CROSS APPLY dbo.tvf_PedidosDoCliente(cli.ClienteID) AS ped;

Note there is no BEGIN/END — that absence is what makes it inline. The same logic written with BEGIN ... RETURN ... END becomes multi-statement and loses the optimisation.

3. Multi-Statement Table-Valued — only when there is no way out

It declares a table variable, fills it in several steps and returns it. The optimiser cannot see what is inside and estimates a fixed number of rows (1 up to SQL Server 2012, 100 afterwards), which produces bad plans:

CREATE OR ALTER FUNCTION dbo.mstvf_Resumo (@Ano INT)
RETURNS @Resultado TABLE (Mes INT, Total DECIMAL(18,2))
AS
BEGIN
    INSERT INTO @Resultado (Mes, Total)
    SELECT MONTH(DataPedido), SUM(ValorTotal)
    FROM   dbo.Pedidos
    WHERE  DataPedido >= DATEFROMPARTS(@Ano, 1, 1)
      AND  DataPedido <  DATEFROMPARTS(@Ano + 1, 1, 1)
    GROUP BY MONTH(DataPedido);

    RETURN;
END
GO

Rule of thumb: if it fits in a single query, make it an inline TVF. If it needs several steps, prefer a procedure writing into a #temporary table.

Inconsistencies: investigating and fixing wrong data

A good part of the work is not writing a new query — it is finding out why the report's number does not add up. These queries solve most cases.

Duplicates that should not exist

-- Which keys are duplicated and how many times
SELECT  CPF, COUNT(*) AS Qtd
FROM    dbo.Clientes
WHERE   CPF IS NOT NULL
GROUP BY CPF
HAVING  COUNT(*) > 1
ORDER BY Qtd DESC;

To delete while keeping the most recent, ROW_NUMBER is the safe way — the DELETE over the CTE deletes from the base table:

WITH Duplicadas AS (
    SELECT  ClienteID,
            ROW_NUMBER() OVER (PARTITION BY CPF ORDER BY CriadoEm DESC) AS rn
    FROM    dbo.Clientes
    WHERE   CPF IS NOT NULL
)
DELETE FROM Duplicadas WHERE rn > 1;

Before any DELETE in production: run it as a SELECT first, check the count, and take a backup. A DELETE with the wrong WHERE is the most expensive mistake there is.

Orphans: children with no parent

SELECT  ped.PedidoID, ped.ClienteID
FROM    dbo.Pedidos AS ped
WHERE   NOT EXISTS (SELECT 1 FROM dbo.Clientes AS cli
                    WHERE cli.ClienteID = ped.ClienteID);

Prefer NOT EXISTS to NOT IN: if the NOT IN subquery returns a single NULL, the whole result becomes empty, silently. It is one of the hardest bugs to spot in SQL.

Sums that do not match between systems

-- Reconciliation: the header against the sum of the lines
SELECT  ped.PedidoID,
        ped.ValorTotal                       AS TotalCabecalho,
        SUM(itm.Quantidade * itm.ValorUnit)  AS TotalItens,
        ped.ValorTotal - SUM(itm.Quantidade * itm.ValorUnit) AS Diferenca
FROM    dbo.Pedidos      AS ped
JOIN    dbo.PedidoItens  AS itm ON itm.PedidoID = ped.PedidoID
GROUP BY ped.PedidoID, ped.ValorTotal
HAVING  ABS(ped.ValorTotal - SUM(itm.Quantidade * itm.ValorUnit)) > 0.01;

The > 0.01 instead of <> 0 is not laziness: with float or currency rounding, one-cent differences appear from numeric representation and pollute the result. Use DECIMAL for money — never float.

Spaces, capitals and accents: the invisible problem

-- 'ABC ' and 'ABC' look the same on screen and may not match in the JOIN
SELECT  ClienteID, '[' + Codigo + ']' AS ComDelimitador, LEN(Codigo) AS Tamanho,
        DATALENGTH(Codigo) AS Bytes
FROM    dbo.Clientes
WHERE   Codigo <> LTRIM(RTRIM(Codigo));

LEN() ignores trailing spaces; DATALENGTH() does not. When the two disagree, you have found the problem. On SQL Server 2017+, TRIM() does both sides at once.

And watch out for collation: if one table is SQL_Latin1_General_CP1_CI_AS and the other ..._CS_AS, the JOIN between them fails with a collation conflict error — or worse, matches differently from what you expected (CI ignores capitals, CS does not).

-- Find out each column's collation
SELECT  c.name AS Coluna, c.collation_name
FROM    sys.columns AS c
WHERE   c.object_id = OBJECT_ID('dbo.Clientes')
  AND   c.collation_name IS NOT NULL;

The database's physical integrity

-- On-disk corruption: run it in a maintenance window, it is heavy
DBCC CHECKDB ('MinhaBase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Trust in the foreign keys: 0 = trusted, 1 = not validated
SELECT  name, is_not_trusted
FROM    sys.foreign_keys
WHERE   is_not_trusted = 1;

An "untrusted" FK happens after a BULK INSERT or an ALTER TABLE ... NOCHECK. The optimiser stops using it to simplify plans, and it stops guaranteeing what it promises. To revalidate it:

ALTER TABLE dbo.Pedidos WITH CHECK CHECK CONSTRAINT FK_Pedidos_Clientes;

The duplicated WITH CHECK CHECK is not a typo: the first tells it to validate the existing data, the second switches the constraint back on.

Performance: where the time goes

Measure before you touch anything

SET STATISTICS IO, TIME ON;
-- your query here
SET STATISTICS IO, TIME OFF;

STATISTICS IO shows how many pages were read per table. It is the most honest number there is: time varies with the machine's load, logical reads do not. If you changed the query and the reads dropped from 400,000 to 300, you genuinely improved it.

In SSMS, Ctrl+M turns on the actual execution plan. Look for:

• Table Scan / Clustered Index Scan on a large table with a selective filter — a missing index, or a predicate that is not SARGable.
• Key Lookup with a high number of executions — the index found the row but had to go back to the table for columns; solve it with INCLUDE.
• A thick arrow between operators — a lot of rows travelling; see whether you can filter earlier.
• An estimate very different from the actual — stale statistics: UPDATE STATISTICS dbo.Pedidos WITH FULLSCAN;

An index that covers the query

CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data
ON dbo.Pedidos (ClienteID, DataPedido)      -- filtering and ordering columns
INCLUDE (ValorTotal, Status);               -- columns only returned

The order of the columns in the key matters: (ClienteID, DataPedido) works for filtering by customer, and by customer + date; it does not work for filtering by date alone. Think of it as the order of the words in a book's index.

Find the heaviest queries

SELECT TOP (20)
        qs.total_worker_time / qs.execution_count / 1000 AS CpuMedioMs,
        qs.execution_count                               AS Execucoes,
        qs.total_logical_reads / qs.execution_count      AS LeiturasMedias,
        SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS Consulta
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

On SQL Server 2016+, turn on the Query Store in the database: it keeps a history of plans and lets you force the good plan when one regresses.

SSIS: loads, integrations and validations

SSIS (SQL Server Integration Services) is Microsoft's ETL tool. You design the package in Visual Studio (with the SQL Server Integration Services Projects extension) and it runs on the server, scheduled by SQL Server Agent.

Control Flow and Data Flow: the distinction that organises everything

A package has two surfaces, and confusing them is beginners' mistake number 1:

• Control Flow is the flow of tasks: "truncate the staging table, then load, then validate, then send an email". It works with tasks and precedences (success, failure, completion).
• Data Flow is the flow of rows: inside a Data Flow task, the data goes from source to destination through transformations, row by row, in memory.

Golden rule: whatever you can do in SQL, do in SQL. An SSIS Sort transformation loads everything into the integration server's RAM; an ORDER BY at the source uses the database's index. Use the Data Flow to move data and for what the database does badly (reading a file, calling an API, splitting to several destinations).

The loading pattern that solves 90% of cases

Nearly every serious load follows three stages:

1. Staging — bring the raw data into a stg.* table, transforming nothing. If something goes wrong, you still have the original material to investigate.
2. Transformation — run T-SQL over the staging table (Execute SQL Task). It is faster, easier to test and versions better than graphical components.
3. Final load — insert/update the destination from the staging table, inside a transaction.

For an incremental load, keep a watermark instead of rereading the whole table:

-- Control table
CREATE TABLE dbo.ControleCarga (
    Tabela        SYSNAME       NOT NULL PRIMARY KEY,
    UltimaCarga   DATETIME2(3)  NOT NULL,
    LinhasCarregadas INT        NOT NULL DEFAULT 0
);

-- At the source, the package reads only what has changed since last time
DECLARE @Desde DATETIME2(3) = (SELECT UltimaCarga FROM dbo.ControleCarga
                               WHERE Tabela = 'Pedidos');

SELECT PedidoID, ClienteID, DataPedido, ValorTotal, AlteradoEm
FROM   dbo.Pedidos
WHERE  AlteradoEm > @Desde;

One detail that prevents data loss: record as the new watermark the time the load started, not the time it finished. Records written at the source while the package was running would fall into a blind gap if you used the end time.

Row-by-row error handling

Every Data Flow component has an error output (the red arrow). Configure it as Redirect Row and send the problem rows to a rejects table, instead of bringing the whole package down:

CREATE TABLE stg.LinhasRejeitadas (
    RejeitadaID   INT IDENTITY PRIMARY KEY,
    Pacote        SYSNAME NOT NULL,
    LinhaOriginal NVARCHAR(MAX) NOT NULL,
    ErroCodigo    INT,
    ErroColuna    INT,
    OcorridoEm    DATETIME2(3) DEFAULT SYSDATETIME()
);

An invoice with an invalid company registration number cannot stop the other 50 thousand from getting in. Load what is valid, set aside what is not, and send the list to whoever entered it.

Validations after the load

-- Checks that should run at the end of every load
DECLARE @Erros TABLE (Verificacao VARCHAR(100), Qtd INT);

INSERT INTO @Erros
SELECT 'Pedidos sem cliente', COUNT(*)
FROM   stg.Pedidos AS s
WHERE  NOT EXISTS (SELECT 1 FROM dbo.Clientes c WHERE c.ClienteID = s.ClienteID)
UNION ALL
SELECT 'Valores negativos', COUNT(*) FROM stg.Pedidos WHERE ValorTotal < 0
UNION ALL
SELECT 'Datas no futuro', COUNT(*) FROM stg.Pedidos WHERE DataPedido > SYSDATETIME()
UNION ALL
SELECT 'Contagem origem x destino',
       ABS((SELECT COUNT(*) FROM stg.Pedidos) - (SELECT COUNT(*) FROM dbo.Pedidos
            WHERE CargaID = @CargaID));

IF EXISTS (SELECT 1 FROM @Erros WHERE Qtd > 0)
    THROW 50100, 'Validação de carga falhou. Veja dbo.LogValidacao.', 1;

A load that does not validate is a load that lies. The validation is what turns "the package ran" into "the data is right".

Parameters, environments and deployment

Since SQL Server 2012, use the Project Deployment Model: the project becomes an .ispac published to the SSIS Catalog (SSISDB). There you create Environments — Dev, Staging, Production — each with its own connection values, and bind the execution to the environment.

Never leave a connection string hard-coded inside the package. Beyond the risk of running against production thinking it was a test, a password in a package is a password in a versioned file.

Good practices that save you nights

• Delay Validation = True on tasks that depend on objects created during execution, otherwise the package fails at initial validation.
• Fast Load on the OLE DB destination, with Rows per batch and Maximum insert commit size tuned — the difference from row-by-row mode reaches tenfold.
• Logging in SSISDB turned on at Basic level; Verbose only when you are hunting a problem (it generates enormous volume).
• Checkpoints on long packages, so you can restart from the point that failed instead of from the beginning.
• Name everything: DFT_CargaPedidos, SQL_TruncaStaging, FLC_ArquivosDoDia. A package with "Data Flow Task 1" and "Execute SQL Task 4" is impossible to maintain.

SSRS: paginated reports

SSRS (SQL Server Reporting Services) generates reports with a fixed layout, made to paginate, print and export. You design them in Report Builder or in Visual Studio, and publish to a web portal.

The anatomy of a report

Every .rdl has four pieces:

1. Data Source — the connection to the database.
2. Dataset — the query (T-SQL or a procedure) that brings the data.
3. Parameters — what the user chooses before running it.
4. Layout — table, matrix, chart, header and footer.

The dataset: always a procedure

CREATE OR ALTER PROCEDURE rpt.usp_VendasPorPeriodo
    @DataInicio DATE,
    @DataFim    DATE,
    @VendedorID INT = NULL          -- NULL = everyone
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  ven.Nome                AS Vendedor,
            cli.Nome                AS Cliente,
            ped.PedidoID,
            ped.DataPedido,
            ped.ValorTotal
    FROM    dbo.Pedidos    AS ped
    JOIN    dbo.Clientes   AS cli ON cli.ClienteID  = ped.ClienteID
    JOIN    dbo.Vendedores AS ven ON ven.VendedorID = ped.VendedorID
    WHERE   ped.DataPedido >= @DataInicio
      AND   ped.DataPedido <  DATEADD(DAY, 1, @DataFim)   -- includes the whole day
      AND   ped.Status = 'FATURADO'
      AND   (@VendedorID IS NULL OR ped.VendedorID = @VendedorID)
    ORDER BY ven.Nome, ped.DataPedido;
END
GO

Two things here deserve attention. The DATEADD(DAY, 1, @DataFim) with < solves the classic problem of the report that "forgets" the last day's orders. And the (@VendedorID IS NULL OR ...) is the optional-parameter pattern — if it makes the query slow, add OPTION (RECOMPILE), which lets the optimiser simplify the predicate knowing the real value.

Cascading parameters

The second parameter depends on the first: choosing the state filters the cities. Create a dataset for each list and, in the cities dataset, use the state parameter:

-- "Estados" dataset
SELECT DISTINCT UF FROM dbo.Clientes ORDER BY UF;

-- "Cidades" dataset — receives @UF from the first parameter
SELECT DISTINCT Cidade FROM dbo.Clientes WHERE UF = @UF ORDER BY Cidade;

For a multi-value parameter, SSRS hands you a list; on the T-SQL side, receive it as text and use STRING_SPLIT:

-- @Ufs arrives as 'SP,RJ,MG'
SELECT ped.PedidoID, ped.ValorTotal
FROM   dbo.Pedidos AS ped
JOIN   dbo.Clientes AS cli ON cli.ClienteID = ped.ClienteID
WHERE  cli.UF IN (SELECT value FROM STRING_SPLIT(@Ufs, ','));

Expressions: the VB that SSRS speaks

Expressions start with = and use Visual Basic syntax:

' A group's total
=Sum(Fields!ValorTotal.Value)

' Format currency
=Format(Fields!ValorTotal.Value, "C2")

' Zebra striping: alternate the rows' background colour
=IIf(RowNumber(Nothing) Mod 2 = 0, "#F5F5F5", "White")

' Avoiding division by zero — IIf evaluates both sides, so protect the divisor
=IIf(Sum(Fields!Meta.Value) = 0, 0,
     Sum(Fields!Realizado.Value) / IIf(Sum(Fields!Meta.Value) = 0, 1, Sum(Fields!Meta.Value)))

' Page numbering in the footer
="Página " & Globals!PageNumber & " de " & Globals!TotalPages

That nested IIf in the divisor is not decoration: unlike an if in a normal language, SSRS's IIf evaluates both branches before choosing, so the division by zero happens even when the condition says not to divide.

Subscriptions: the report that sends itself

A subscription schedules the execution and delivers the result by email or to a network folder. It is what handles "on the 1st of every month at 6 a.m., the closing report in the board's inbox" with nobody clicking anything.

A data-driven subscription (Enterprise edition) goes further: a query defines who receives what. One execution generates 40 reports, one per regional manager, each with their own region's filter, sent to each of their inboxes.

Layout details that prevent rework

• Interactive Size and Page Size define the break on screen and in print; leaving the defaults produces a PDF with a blank page between each real page.
• KeepTogether and RepeatColumnHeaders make the header repeat on every page — without them, page 7 is a table with no titles.
• Matrix for dynamic columns (months as columns), Table for fixed columns.
• Export to Excel during development: merged cells and loose text boxes turn into a mess in the spreadsheet, and that is how the user is going to want to use it.

Data Warehouse: dimensional modelling

When the volume grows and the report starts competing with the transactional system, the analytical data is separated into a Data Warehouse. The standard model is the star schema: a fact table at the centre, surrounded by dimensions.

-- Dimension: it describes, it has few rows and many columns
CREATE TABLE dim.Cliente (
    ClienteSK    INT IDENTITY PRIMARY KEY,   -- the DW's surrogate key
    ClienteBK    INT          NOT NULL,      -- business key: the source system's ID
    Nome         VARCHAR(200) NOT NULL,
    Cidade       VARCHAR(100) NULL,
    UF           CHAR(2)      NULL,
    Segmento     VARCHAR(50)  NULL,
    ValidoDe     DATE         NOT NULL,
    ValidoAte    DATE         NULL,          -- NULL = the current version
    Corrente     BIT          NOT NULL DEFAULT 1
);

-- Fact: it measures, it has many rows and few columns
CREATE TABLE fato.Vendas (
    VendaSK      BIGINT IDENTITY PRIMARY KEY,
    DataSK       INT     NOT NULL,           -- FK to dim.Calendario
    ClienteSK    INT     NOT NULL,
    ProdutoSK    INT     NOT NULL,
    Quantidade   INT     NOT NULL,
    ValorBruto   DECIMAL(18,2) NOT NULL,
    Desconto     DECIMAL(18,2) NOT NULL DEFAULT 0,
    ValorLiquido AS (ValorBruto - Desconto) PERSISTED
);

The surrogate key (ClienteSK) exists so the DW does not depend on the source system's ID — which can change, repeat across companies or disappear in a migration. The business key is kept for traceability.

SCD Type 2: keeping the history

If a customer changes segment, last year's revenue should keep adding up under the old segment. That is what a Slowly Changing Dimension type 2 solves: instead of overwriting, it closes the current version and opens a new one.

-- 1) Close the current version of whatever changed
UPDATE  dim
SET     dim.ValidoAte = CAST(SYSDATETIME() AS DATE),
        dim.Corrente  = 0
FROM    dim.Cliente AS dim
JOIN    stg.Cliente AS stg ON stg.ClienteID = dim.ClienteBK
WHERE   dim.Corrente = 1
  AND   (dim.Segmento <> stg.Segmento OR dim.UF <> stg.UF);

-- 2) Open the new version
INSERT INTO dim.Cliente (ClienteBK, Nome, Cidade, UF, Segmento, ValidoDe, Corrente)
SELECT  stg.ClienteID, stg.Nome, stg.Cidade, stg.UF, stg.Segmento,
        CAST(SYSDATETIME() AS DATE), 1
FROM    stg.Cliente AS stg
WHERE   NOT EXISTS (SELECT 1 FROM dim.Cliente AS dim
                    WHERE dim.ClienteBK = stg.ClienteID AND dim.Corrente = 1);

The calendar dimension

Every analysis crosses time, and nobody wants to compute a quarter with a function in every query. Generate a date table once:

CREATE TABLE dim.Calendario (
    DataSK       INT  PRIMARY KEY,        -- 20260319
    Data         DATE NOT NULL UNIQUE,
    Ano          SMALLINT NOT NULL,
    Trimestre    TINYINT  NOT NULL,
    Mes          TINYINT  NOT NULL,
    NomeMes      VARCHAR(20) NOT NULL,
    DiaSemana    TINYINT  NOT NULL,
    EhDiaUtil    BIT NOT NULL
);

-- Fills 20 years with no loop, using a generated sequence
WITH Numeros AS (
    SELECT TOP (7305) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM   sys.all_objects a CROSS JOIN sys.all_objects b
),
Datas AS (
    SELECT DATEADD(DAY, n, '2020-01-01') AS Data FROM Numeros
)
INSERT INTO dim.Calendario (DataSK, Data, Ano, Trimestre, Mes, NomeMes, DiaSemana, EhDiaUtil)
SELECT  CONVERT(INT, FORMAT(Data, 'yyyyMMdd')),
        Data, YEAR(Data), DATEPART(QUARTER, Data), MONTH(Data),
        DATENAME(MONTH, Data), DATEPART(WEEKDAY, Data),
        CASE WHEN DATEPART(WEEKDAY, Data) IN (1, 7) THEN 0 ELSE 1 END
FROM    Datas;

Power BI and Excel: the analysis layer

Power BI: model first, visuals second

The most common mistake is starting with the charts. What holds a Power BI report up is the model: tables related in a star, with a calendar dimension marked as a date table.

Some DAX measures that appear in every project:

Faturamento = SUM(fVendas[ValorLiquido])

Faturamento Ano Anterior =
CALCULATE([Faturamento], SAMEPERIODLASTYEAR(dCalendario[Data]))

Crescimento % =
DIVIDE([Faturamento] - [Faturamento Ano Anterior], [Faturamento Ano Anterior])

Ticket Médio =
DIVIDE([Faturamento], DISTINCTCOUNT(fVendas[PedidoID]))

Acumulado no Ano =
CALCULATE([Faturamento], DATESYTD(dCalendario[Data]))

Use DIVIDE() rather than the / operator: it handles division by zero by returning blank, instead of an error that spreads across the entire visual.

On the connection mode: Import brings the data inside the file (fast, but with a refresh schedule) and DirectQuery queries SQL Server on every interaction (always current, but it passes the query's slowness on to the user). Import is the right default until you have a concrete reason for the opposite.

Excel: underestimated and everywhere

Excel is still where the analyst actually works. Two capabilities are worth more than a hundred formulas:

Power Query (Data → Get Data) connects straight to SQL Server, applies transformations in named steps and refreshes with one click. It is ETL inside Excel, and it replaces that copy-and-paste process every month.

A pivot table over the connection lets you explore without bringing the rows into the sheet. Combined with Power Query, it delivers an analysis tool without needing a BI licence.

Formulas that solve most of the analytical work:

=XLOOKUP(A2, Base!A:A, Base!C:C, "não encontrado")
=SUMIFS(Vendas[Valor], Vendas[UF], "SP", Vendas[Mês], 3)
=COUNTIFS(Pedidos[Status], "ABERTO")
=IFERROR(B2/C2, 0)
=TEXT(A2, "dd/mm/yyyy")

XLOOKUP replaces VLOOKUP with advantages: it searches in any direction, does not break when someone inserts a column, and already has the "if not found" parameter.

ERPs: Dynamics AX and Navision from the inside

Anyone working in BI ends up querying an ERP's database. Microsoft's have quirks that trip up whoever arrives without knowing them.

Dynamics AX

• DATAAREAID is the column that separates the companies within the same database. Every query has to filter on it, otherwise you add up the revenue of every company in the group:

SELECT  SUM(LINEAMOUNT) AS Total
FROM    dbo.SALESLINE
WHERE   DATAAREAID = 'BR01'          -- without this, the number comes out wrong
  AND   CREATEDDATETIME >= '2026-01-01';

• RECID is the tables' real key (not the business field), and RECVERSION handles optimistic concurrency.
• The large tables follow the header/line pattern: SALESTABLE/SALESLINE, PURCHTABLE/PURCHLINE, INVENTTRANS for stock movement.
• Many fields are numeric enums: SALESSTATUS = 3 means "invoiced". The mapping is in AX's metadata, not in the database — document it in a lookup table in your DW.

Navision / Business Central

• The table names include the company and use $: CRONUS Brasil Ltda$Customer, CRONUS Brasil Ltda$Sales Header. That requires brackets:

SELECT No_, Name, [Country_Region Code]
FROM   [CRONUS Brasil Ltda$Customer]
WHERE  Blocked = 0;

• Column names with spaces also need brackets, and a trailing _ is how Navision escapes reserved words (No_ is "No.").
• Boolean values are tinyint (0/1), and "empty" dates usually come through as 1753-01-01, the minimum of datetime — filter on that rather than on IS NULL.

A general recommendation: never query the ERP's database directly from the report. Bring it into a staging area, normalise the enums and names, and build on top of that. Besides protecting the ERP's performance, isolating that layer stops the next ERP update breaking twenty reports at once.

QuickSight: the AWS cousin

If the company has data in AWS, Amazon QuickSight is the equivalent of Power BI in that ecosystem. Two concepts are enough to get your bearings:

• SPICE is the in-memory engine: you import the dataset and the queries become fast, with a scheduled refresh — the analogue of Power BI's Import mode.
• Datasets and Analyses separate preparing the data from assembling the visuals, and one dataset serves several analyses.

It connects to Redshift, RDS (SQL Server included), S3 via Athena and others. Anyone who understands dimensional modelling and can write SQL switches tools in days — what does not transfer quickly is a well-built data model.

A roadmap: where to start

If you are building this repertoire from scratch, this is the order that pays off fastest:

1. Solid T-SQL first. JOINs, aggregations, CTEs and window functions. Without that, none of the rest holds up.
2. Read execution plans. Knowing why a query is slow separates someone who writes SQL from someone who answers for a database.
3. Procedures and inline TVFs. Write them with SET NOCOUNT ON, TRY/CATCH and a transaction from day one — it becomes a habit.
4. SSIS with the staging → transform → load pattern. Build an incremental load with a watermark and an error output. It is the exercise that comes up most in interviews.
5. SSRS with a procedure, cascading parameters and a subscription. One complete report teaches more than ten tutorials.
6. Star schema and SCD type 2. It is the vocabulary of any conversation about a Data Warehouse.
7. Power BI afterwards. With the right model behind it, Power BI is the easy part.

One last piece of advice, worth more than any syntax in this guide: be suspicious of the number that adds up perfectly on the first try. When the report matches expectations on the very first run, there is nearly always a filter hiding rows — an INNER JOIN that should have been a LEFT, a WHERE that discards nulls, a company missing from the DATAAREAID. Reconcile against the source before you deliver. That check is what builds the trust of whoever depends on your number to decide.