SQL Server and BI: 70 Questions and Answers

This guide is made of questions — the ones that really come up when someone starts working with SQL Server, T-SQL, SSIS, SSRS and BI. Each answer explains what the term means, how the thing works underneath and how it is done in practice, with an example ready to test.

You do not have to read it in order. Use the contents, look up your question and come back when another one appears.

The examples use an imaginary sales database with Clientes, Pedidos, PedidoItens, Produtos and Vendedores. They all run on SQL Server 2016 or newer.

Contents

• 1–10 · Fundamentals: what each thing is
• 11–24 · Everyday queries
• 25–29 · Views
• 30–39 · Stored Procedures
• 40–44 · Functions
• 45–52 · Indexes and performance
• 53–58 · Investigating wrong data
• 59–63 · SSIS and ETL
• 64–67 · SSRS
• 68–70 · BI, Data Warehouse and ERPs

Fundamentals: what each thing is

1. What is SQL Server?

It is a relational DBMS — Database Management System — from Microsoft. In plain terms: a program that runs on a server, stores data organised into tables and answers read and write requests coming from other programs.

It does four things an ordinary file does not: it guarantees two users writing at the same time do not get in each other's way, it guarantees an incomplete operation leaves no rubbish behind, it controls who can see what, and it finds one row among millions in milliseconds.

What you install is not just the database. The package includes the Database Engine (the database itself), SSIS (the data loading tool), SSRS (reporting) and SSAS (analytical cubes). They are separate products that usually live together.

2. What is the difference between SQL and T-SQL?

SQL is the standard language for querying relational databases — SELECT, INSERT, UPDATE, DELETE. It works on any database, with small variations.

T-SQL (Transact-SQL) is Microsoft's version: standard SQL plus everything that lets you program inside the database — variables, IF, WHILE, error handling, its own functions.

-- Standard SQL: runs on any database
SELECT Nome FROM Clientes WHERE UF = 'SP';

-- T-SQL: a variable, a conditional and a Microsoft-specific function
DECLARE @Total INT;
SELECT @Total = COUNT(*) FROM Clientes WHERE UF = 'SP';
IF @Total > 100
    PRINT 'Muitos clientes em SP: ' + CAST(@Total AS VARCHAR(10));

In practice, anyone working with SQL Server writes T-SQL all the time, without noticing. TOP, ISNULL, GETDATE() and APPLY are T-SQL, not standard SQL.

3. What is an instance, a database and a schema?

They are three levels of organisation, from largest to smallest:

• An instance is the SQL Server service running on the machine. One server can have several, each with its own port, its own settings and its own databases. You connect to an instance.
• A database is one system's set of tables. VendasDB, RH, Financeiro — each isolated from the others.
• A schema is a folder inside the database, grouping objects and controlling permissions. dbo.Clientes and rpt.Clientes are two different tables.

-- An object's full name: server.database.schema.object
SELECT * FROM VendasDB.dbo.Clientes;

-- Create a schema to separate what belongs to reporting
CREATE SCHEMA rpt;
GO

4. What is this dbo that appears before the table names?

dbo stands for database owner and is the default schema of every SQL Server database. When you create a table without stating the schema, it is born in dbo.

Writing dbo.Clientes instead of just Clientes is not fussiness. Without the schema, SQL Server looks first in your user's schema and only then in dbo — that costs an extra lookup and, worse, it stops the execution plan being reused across different users. In a query that runs thousands of times, it makes a measurable difference.

5. How do I choose a column's data type?

The wrong type causes two problems: it wastes space and it produces wrong results. The cases that come up most:

CREATE TABLE dbo.Pedidos (
    PedidoID    INT           IDENTITY PRIMARY KEY,
    ClienteID   INT           NOT NULL,
    DataPedido  DATETIME2(3)  NOT NULL,
    ValorTotal  DECIMAL(18,2) NOT NULL,   -- money is never FLOAT
    Observacao  VARCHAR(500)  NULL,
    Ativo       BIT           NOT NULL DEFAULT 1
);

6. What is NULL and why does it behave so strangely?

NULL is neither zero nor empty text. It means "unknown". And that is why it breaks normal logic: any comparison with the unknown results in unknown, not in true or false.

SELECT 1 WHERE NULL = NULL;      -- returns nothing
SELECT 1 WHERE NULL <> 'texto';  -- also returns nothing

To test for null, use IS NULL / IS NOT NULL. To replace it with a value, ISNULL or COALESCE:

SELECT  Nome,
        ISNULL(Telefone, 'não informado')      AS Telefone,
        COALESCE(Celular, Telefone, 'sem contato') AS MelhorContato
FROM    dbo.Clientes
WHERE   Email IS NOT NULL;

ISNULL takes two arguments; COALESCE takes several and returns the first that is not null.

The detail that trips up the most queries: NOT IN with a subquery containing a single NULL returns empty, silently. Use NOT EXISTS.

7. In what order does SQL Server execute a query?

You write it in one order, the database executes it in another. Understanding that explains half of all SQL errors:

1. FROM / JOIN      -> assembles the set of rows
2. WHERE            -> filters row by row
3. GROUP BY         -> groups
4. HAVING           -> filters the groups
5. SELECT           -> picks and calculates the columns
6. ORDER BY         -> sorts
7. TOP / OFFSET     -> trims

That is why you cannot use an alias created in the SELECT inside the WHERE — when the WHERE runs, the SELECT has not happened yet:

-- ERROR: 'Margem' does not exist yet when the WHERE is evaluated
SELECT ValorTotal - Custo AS Margem FROM dbo.Pedidos WHERE Margem > 100;

-- Right: repeat the expression, or use a subquery/CTE
SELECT ValorTotal - Custo AS Margem FROM dbo.Pedidos WHERE ValorTotal - Custo > 100;

And it is also why the ORDER BY can use the alias: it runs after the SELECT.

8. What is a collation?

A collation is the rule that defines how the database compares and sorts text: whether it distinguishes upper from lower case, whether it ignores accents, what the alphabetical order is.

Its name tells you everything: in SQL_Latin1_General_CP1_CI_AS, the CI is case insensitive (it ignores capitals) and the AS is accent sensitive (it distinguishes accents).

-- With CI: both lines come back
SELECT * FROM dbo.Clientes WHERE Nome = 'joão';
SELECT * FROM dbo.Clientes WHERE Nome = 'JOÃO';

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

The problem shows up when joining tables from databases with different collations: the JOIN fails with "Cannot resolve the collation conflict". The way out is to force one of the two:

SELECT a.Nome
FROM   BancoA.dbo.Clientes a
JOIN   BancoB.dbo.Clientes b
    ON a.Codigo = b.Codigo COLLATE SQL_Latin1_General_CP1_CI_AS;

9. What is a primary key and a foreign key?

A primary key (PK) is the column that identifies the row uniquely. It does not accept null and does not repeat. Every table should have one.

A foreign key (FK) is the column pointing at another table's primary key. It is what prevents an order existing for a customer who does not.

CREATE TABLE dbo.Clientes (
    ClienteID INT IDENTITY PRIMARY KEY,   -- primary key
    Nome      VARCHAR(200) NOT NULL
);

CREATE TABLE dbo.Pedidos (
    PedidoID  INT IDENTITY PRIMARY KEY,
    ClienteID INT NOT NULL,
    CONSTRAINT FK_Pedidos_Clientes
        FOREIGN KEY (ClienteID) REFERENCES dbo.Clientes (ClienteID)
);

With that FK in place, trying to insert an order for customer 9999 (who does not exist) gives an error. It is the database protecting the data from a bug in the application.

10. What is IDENTITY and what is SEQUENCE?

IDENTITY makes the column number itself on every INSERT. It is the most common way of generating an id.

CREATE TABLE dbo.Clientes (ClienteID INT IDENTITY(1,1) PRIMARY KEY, Nome VARCHAR(200));
--                                          ^ starts at 1, adds 1 per row

INSERT INTO dbo.Clientes (Nome) VALUES ('Maria');
SELECT SCOPE_IDENTITY();   -- the id just generated in this session

Use SCOPE_IDENTITY() and not @@IDENTITY: the second returns the id generated by anything at all, including a trigger that ran afterwards — and then you store the wrong id.

SEQUENCE is a counter independent of any table, useful when two tables need to share the same numbering:

CREATE SEQUENCE dbo.NumeroDocumento AS INT START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR dbo.NumeroDocumento;

In both cases, gaps in the numbering are normal: a cancelled transaction consumes the number and does not give it back.

Everyday queries

11. What is the difference between WHERE and HAVING?

WHERE filters rows, before grouping. HAVING filters groups, after grouping. That is the only difference, and it decides which to use.

SELECT   ClienteID, SUM(ValorTotal) AS Total
FROM     dbo.Pedidos
WHERE    DataPedido >= '2026-01-01'   -- discards old orders BEFORE summing
GROUP BY ClienteID
HAVING   SUM(ValorTotal) > 10000;     -- discards small customers AFTER summing

You cannot swap one for the other: WHERE SUM(...) > 10000 gives an error, because at the time of the WHERE the sum does not exist yet.

12. What is a JOIN and what are the types?

A JOIN combines rows from two tables using a condition. The four that matter:

-- INNER: only what exists on both sides
SELECT c.Nome, p.PedidoID
FROM dbo.Clientes c INNER JOIN dbo.Pedidos p ON p.ClienteID = c.ClienteID;

-- LEFT: every customer; with no order, the order columns come back NULL
SELECT c.Nome, p.PedidoID
FROM dbo.Clientes c LEFT JOIN dbo.Pedidos p ON p.ClienteID = c.ClienteID;

-- FULL: everything from both sides
SELECT c.Nome, p.PedidoID
FROM dbo.Clientes c FULL OUTER JOIN dbo.Pedidos p ON p.ClienteID = c.ClienteID;

-- CROSS: every row of A with every row of B (the Cartesian product)
SELECT v.Nome, m.Mes FROM dbo.Vendedores v CROSS JOIN dbo.Meses m;

RIGHT JOIN exists, but in practice nobody uses it: it is the LEFT with the tables swapped, and it is harder to read.

Use LEFT JOIN when the question is "all the Xs, with the Ys they have" — for instance, "every customer and how much each bought, including whoever bought nothing".

13. Why does my LEFT JOIN sometimes behave like an INNER JOIN?

Because the right-hand table's condition ended up in the WHERE. Since the WHERE runs after the join, it discards the rows where that column came back null — which are exactly the ones the LEFT JOIN had preserved.

-- It becomes an INNER without warning: a customer with no order has a NULL Status and is discarded
SELECT c.Nome, p.PedidoID
FROM   dbo.Clientes c
LEFT JOIN dbo.Pedidos p ON p.ClienteID = c.ClienteID
WHERE  p.Status = 'FATURADO';

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

Rule of thumb: in a LEFT JOIN, a condition on the left-hand table goes in the WHERE; on the right-hand one, it goes in the ON.

14. What is the difference between UNION and UNION ALL?

Both stack the results of two queries. UNION removes duplicates; UNION ALL removes nothing.

SELECT Email FROM dbo.Clientes
UNION ALL                      -- keeps duplicates, and is faster
SELECT Email FROM dbo.Fornecedores;

Removing duplicates costs a sort of the entire result. If you know there is no repetition — or if repetition is not a problem — always use UNION ALL. The difference on large tables is enormous.

15. When should I use EXISTS instead of IN?

IN compares against a list of values. EXISTS only checks whether the subquery returns any row, and stops at the first one it finds.

-- IN: builds the whole list
SELECT Nome FROM dbo.Clientes
WHERE ClienteID IN (SELECT ClienteID FROM dbo.Pedidos WHERE ValorTotal > 1000);

-- EXISTS: stops at the first hit
SELECT Nome FROM dbo.Clientes c
WHERE EXISTS (SELECT 1 FROM dbo.Pedidos p
              WHERE p.ClienteID = c.ClienteID AND p.ValorTotal > 1000);

In performance terms, SQL Server's optimiser usually treats the two similarly. The serious difference is in the negation: NOT IN returns an empty result if the subquery contains a single NULL, because "X is not in a list containing the unknown" is indeterminate. NOT EXISTS does not have that problem. Always prefer NOT EXISTS.

16. What is a CTE?

A CTE (Common Table Expression) is a named temporary result, declared with WITH before the query. It serves to break a large query into readable steps, read from top to bottom.

WITH TotalPorCliente AS (
    SELECT ClienteID, SUM(ValorTotal) AS Total
    FROM   dbo.Pedidos
    GROUP BY ClienteID
)
SELECT c.Nome, t.Total
FROM   TotalPorCliente t
JOIN   dbo.Clientes c ON c.ClienteID = t.ClienteID
WHERE  t.Total > 5000;

It exists only during that query. And it stores no data: the optimiser expands its text wherever it is used. If you reference the same CTE three times, it runs three times. When the intermediate result is expensive and reused, a #temporary table usually comes out cheaper.

17. What is a recursive CTE?

It is a CTE that calls itself, used to walk hierarchies: an org chart, categories with subcategories, a product structure.

WITH Hierarquia AS (
    SELECT FuncionarioID, Nome, GerenteID, 0 AS Nivel
    FROM   dbo.Funcionarios
    WHERE  GerenteID IS NULL            -- the anchor: where it starts

    UNION ALL

    SELECT f.FuncionarioID, f.Nome, f.GerenteID, h.Nivel + 1
    FROM   dbo.Funcionarios f
    JOIN   Hierarquia h ON h.FuncionarioID = f.GerenteID   -- the recursive step
)
SELECT REPLICATE('  ', Nivel) + Nome AS Estrutura FROM Hierarquia
OPTION (MAXRECURSION 100);

MAXRECURSION is the safety catch. Without it, a cycle in the data (A manages B, B manages A) runs forever.

18. What is a window function?

It is a calculation made over a set of rows without collapsing the rows. With GROUP BY, ten orders become one total row. With a window function, the ten orders are still there, each carrying the total beside it.

The OVER clause defines the window: PARTITION BY says how to group, ORDER BY says in what order.

SELECT  PedidoID,
        ClienteID,
        ValorTotal,
        SUM(ValorTotal)   OVER (PARTITION BY ClienteID)                    AS TotalDoCliente,
        AVG(ValorTotal)   OVER (PARTITION BY ClienteID)                    AS MediaDoCliente,
        ValorTotal - LAG(ValorTotal) OVER (PARTITION BY ClienteID
                                           ORDER BY DataPedido)            AS DiferencaAnterior
FROM    dbo.Pedidos;

LAG takes the previous row's value and LEAD the next one's — the two solve "how much did it change against the last order" without needing to join the table to itself.

19. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows; the difference is in the ties:

• ROW_NUMBER: sequential numbering, no ties. Always 1, 2, 3, 4.
• RANK: tied rows get the same position, and the next one skips.
• DENSE_RANK: tied rows get the same position, and the next one does not skip.

SELECT  Nome, Total,
        ROW_NUMBER() OVER (ORDER BY Total DESC) AS Linha,
        RANK()       OVER (ORDER BY Total DESC) AS Posicao,
        DENSE_RANK() OVER (ORDER BY Total DESC) AS PosicaoDensa
FROM    dbo.ResumoVendedores;

Use ROW_NUMBER when you need a unique number (to deduplicate, for instance) and DENSE_RANK when you want "the three highest values", counting ties as a single position.

20. How do I take the last record of each group?

Two good ways. The first, with ROW_NUMBER:

WITH Numerados AS (
    SELECT  p.*,
            ROW_NUMBER() OVER (PARTITION BY ClienteID ORDER BY DataPedido DESC) AS rn
    FROM    dbo.Pedidos p
)
SELECT * FROM Numerados WHERE rn = 1;

The second, with OUTER APPLY — which is usually faster when there is an index, because it fetches one row per customer instead of numbering everything:

SELECT  c.Nome, ult.PedidoID, ult.DataPedido
FROM    dbo.Clientes c
OUTER APPLY (
    SELECT TOP (1) p.PedidoID, p.DataPedido
    FROM   dbo.Pedidos p
    WHERE  p.ClienteID = c.ClienteID
    ORDER BY p.DataPedido DESC
) ult;

21. What is CROSS APPLY?

It is a JOIN in which the right-hand side can see each row of the left-hand side. A normal JOIN cannot do that — the right-hand subquery is independent.

CROSS APPLY discards the left-hand row when the right-hand side returns nothing (like INNER JOIN); OUTER APPLY keeps it, filling in with null (like LEFT JOIN).

Besides "the last of each group", it serves to call a function with the row's value:

SELECT  c.Nome, itens.ProdutoID, itens.Quantidade
FROM    dbo.Clientes c
CROSS APPLY dbo.tvf_ItensDoCliente(c.ClienteID) AS itens;

22. How do I paginate a result?

With OFFSET and FETCH NEXT, which require an ORDER BY:

DECLARE @Pagina INT = 3, @Tamanho INT = 20;

SELECT   PedidoID, DataPedido, ValorTotal
FROM     dbo.Pedidos
ORDER BY DataPedido DESC
OFFSET   (@Pagina - 1) * @Tamanho ROWS
FETCH NEXT @Tamanho ROWS ONLY;

A warning: a large OFFSET is slow, because the database has to scan and discard all the skipped rows. Page 5,000 costs far more than page 2. On genuinely large lists, the better pattern is keyset pagination — instead of "skip 100,000", ask for "the 20 after the last one I saw":

SELECT TOP (20) PedidoID, DataPedido
FROM   dbo.Pedidos
WHERE  DataPedido < @UltimaDataVista
ORDER BY DataPedido DESC;

23. What is the difference between DELETE, TRUNCATE and DROP?

DELETE FROM dbo.Pedidos WHERE DataPedido < '2020-01-01';  -- deletes part of it
TRUNCATE TABLE stg.Pedidos;                               -- empties staging
DROP TABLE stg.PedidosAntigo;                             -- the table is gone

TRUNCATE is the right one for clearing a staging table before each load. But it does not work if the table is referenced by a foreign key, and it does not fire triggers.

And the first two can be undone if they are inside an uncommitted transaction — including TRUNCATE, contrary to what many people believe.

24. What is a transaction and what does ACID mean?

A transaction is a set of statements that is all or nothing. If one fails, they all roll back.

BEGIN TRANSACTION;
    UPDATE dbo.Conta SET Saldo = Saldo - 100 WHERE ContaID = 1;
    UPDATE dbo.Conta SET Saldo = Saldo + 100 WHERE ContaID = 2;
COMMIT;   -- or ROLLBACK to undo it

Without a transaction, a failure between the two statements would make the money disappear. ACID are the four guarantees the database gives:

• Atomicity — either everything happens, or nothing does.
• Consistency — the database never ends up in a state that violates its rules (FKs, checks).
• Isolation — simultaneous transactions do not see the middle of each other's work.
• Durability — after the COMMIT, the data survives a power cut.

Views

25. What is a View?

It is a saved query with a name, which you use as if it were a table.

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

SELECT * FROM dbo.vw_PedidosFaturados WHERE ValorTotal > 1000;

It serves three purposes: hiding complexity (the join is written once), providing security (a person accesses the view without accessing the table, and you leave out the sensitive columns) and creating a stable contract (if the table changes, you adjust the view and whoever consumes it never notices).

26. Does a View store the data?

No. The view stores only the text of the query. Every time you query it, the query behind it runs again, on the current data.

The exception is the indexed view, which we will see next.

27. Can I INSERT or UPDATE through a View?

Yes, as long as the view is simple: a single table, no aggregation, no DISTINCT, no GROUP BY, and the table's mandatory columns present in it.

CREATE OR ALTER VIEW dbo.vw_ClientesSP AS
SELECT ClienteID, Nome, UF FROM dbo.Clientes WHERE UF = 'SP';
GO

UPDATE dbo.vw_ClientesSP SET Nome = 'Maria Silva' WHERE ClienteID = 10;

A treacherous detail: without WITH CHECK OPTION, you can update a row so that it leaves the view — changing the state to 'RJ' makes the record disappear, and the statement is accepted. With the option, the database refuses:

CREATE OR ALTER VIEW dbo.vw_ClientesSP AS
SELECT ClienteID, Nome, UF FROM dbo.Clientes WHERE UF = 'SP'
WITH CHECK OPTION;

28. What is an indexed view?

It is the exception to the rule: a view with a unique clustered index, whose result is written to disk and kept up to date automatically by SQL Server. It is for a heavy aggregation that is queried all the time.

CREATE OR ALTER VIEW dbo.vw_VendasPorDia
WITH SCHEMABINDING AS                     -- mandatory: it binds the view to the tables
SELECT   CAST(DataPedido AS date) AS Dia,
         COUNT_BIG(*)             AS Pedidos,   -- COUNT_BIG, not COUNT
         SUM(ValorTotal)          AS Total
FROM     dbo.Pedidos                      -- schema mandatory
WHERE    Status = 'FATURADO'
GROUP BY CAST(DataPedido AS date);
GO

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

The price is that every INSERT/UPDATE on the base table gets a little slower, because it has to update the view too. It pays off when reading is far more frequent than writing.

29. How many views can I stack on top of each other?

Technically many; in practice, two. A view on a view on a view looks like organisation, but the optimiser has to expand it all and ends up reading tables nobody asked for in that query. When you can no longer say off the top of your head which tables a view reads, the performance has already gone.

Stored Procedures

30. What is a Stored Procedure?

It is a block of T-SQL stored in the database, with a name and parameters, that you execute when you want to.

CREATE OR ALTER PROCEDURE dbo.usp_PedidosPorCliente
    @ClienteID INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT PedidoID, DataPedido, ValorTotal
    FROM   dbo.Pedidos
    WHERE  ClienteID = @ClienteID
    ORDER BY DataPedido DESC;
END
GO

EXEC dbo.usp_PedidosPorCliente @ClienteID = 42;

It is worth using for four reasons: the execution plan stays in cache and gets reused; the rule lives in one place instead of being repeated on every screen; you grant permission to execute the procedure without granting access to the tables; and the network carries a short call instead of a whole query.

31. How do I return a value from a procedure?

Three routes, with different purposes:

CREATE OR ALTER PROCEDURE dbo.usp_ContaPedidos
    @ClienteID INT,
    @Total     INT OUTPUT      -- 1) an output parameter
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Total = COUNT(*) FROM dbo.Pedidos WHERE ClienteID = @ClienteID;
    RETURN 0;                  -- 2) a return code: INT only, use it for status
END
GO

DECLARE @Qtd INT;
EXEC dbo.usp_ContaPedidos @ClienteID = 42, @Total = @Qtd OUTPUT;
SELECT @Qtd;

The third route is simply to do a SELECT inside the procedure — the result comes back as a row set. It is the most common when the procedure feeds a report.

Use RETURN only for status (0 = ok, 1 = error); it accepts an integer only.

32. What is SET NOCOUNT ON for?

By default, each statement returns a "(N rows affected)" message. That wastes network and, worse, some clients — including the SSIS component — interpret that message as one more result, which breaks the read.

SET NOCOUNT ON switches those messages off. Put it as the first line of every procedure. It changes nothing in the result, it only removes the noise.

33. How do I handle an error inside a procedure?

With TRY...CATCH, and always alongside a transaction when there is writing involved:

CREATE OR ALTER PROCEDURE dbo.usp_FaturarPedido
    @PedidoID INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;      -- guarantees a rollback on an error that would not abort on its own

    BEGIN TRY
        BEGIN TRANSACTION;

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

        UPDATE dbo.Pedidos SET Status = 'FATURADO' WHERE PedidoID = @PedidoID;

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

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

        THROW;   -- rethrows the original error to the caller
    END CATCH
END
GO

THROW with no arguments rethrows the error preserving its number and message. XACT_STATE() distinguishes a still-usable transaction from one already doomed — checking only @@TRANCOUNT does not cover that case.

34. What is parameter sniffing?

It is SQL Server compiling the procedure's plan based on the first parameter value it received, and reusing that plan for every value afterwards.

It works fine when the values are similar. It breaks when they are not: if the first call was for a customer with 3 orders, the chosen plan reads row by row; when a customer with 2 million orders arrives, that same plan is used and the query hangs.

It is the most common explanation for "the procedure got slow and nobody touched anything". The most direct way out:

SELECT PedidoID, ValorTotal
FROM   dbo.Pedidos
WHERE  ClienteID = @ClienteID
OPTION (RECOMPILE);   -- recompiles only this statement, on every execution

Alternatives: OPTION (OPTIMIZE FOR UNKNOWN), which uses the average from the statistics, or copying the parameter into a local variable.

35. What is the difference between a Procedure and a Function?

A simple rule: if it does something, it is a procedure. If it calculates something to be used in a query, it is a function.

36. What is the difference between a temporary table and a table variable?

CREATE TABLE #Temporaria (ID INT, Nome VARCHAR(100));   -- temporary table
DECLARE @Variavel TABLE (ID INT, Nome VARCHAR(100));    -- table variable

The deciding point: statistics. Without them, the optimiser estimates a fixed number of rows for the table variable and chooses bad plans when the volume is large. For a few rows, @variable is lighter. For many, use #table.

37. What is a cursor and why avoid it?

A cursor is the construct that walks a result row by row, like a programming loop.

DECLARE @ID INT;
DECLARE c CURSOR FOR SELECT PedidoID FROM dbo.Pedidos WHERE Status = 'ABERTO';
OPEN c;
FETCH NEXT FROM c INTO @ID;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.usp_FaturarPedido @PedidoID = @ID;
    FETCH NEXT FROM c INTO @ID;
END
CLOSE c; DEALLOCATE c;

Relational databases are built to work with sets, not with loops. The same work in a single UPDATE is usually dozens of times faster:

UPDATE dbo.Pedidos SET Status = 'FATURADO' WHERE Status = 'ABERTO';

A cursor is justified when each row really does need a different, indivisible treatment — calling an external service, for instance. Beyond that, there is nearly always a set-based version.

38. What is SQL injection and how does a parameter protect against it?

SQL injection is when the text the user types becomes a command, instead of staying as data. It happens whenever the query is assembled by concatenation:

-- VULNERABLE: if @Nome is  ' OR 1=1 --  the query returns every customer
DECLARE @Sql NVARCHAR(500) = 'SELECT * FROM dbo.Clientes WHERE Nome = ''' + @Nome + '''';
EXEC (@Sql);

With a parameter, the value travels separately from the statement's text and is never interpreted as code:

-- SAFE
SELECT * FROM dbo.Clientes WHERE Nome = @Nome;

That is why a procedure with parameters is safe by construction — as long as it does not concatenate the parameter into dynamic SQL.

39. And when I really do need dynamic SQL?

Use sp_executesql, which accepts real parameters:

DECLARE @Sql NVARCHAR(MAX) = N'SELECT PedidoID, ValorTotal FROM dbo.Pedidos WHERE 1 = 1';

IF @ClienteID IS NOT NULL
    SET @Sql += N' AND ClienteID = @ClienteID';
IF @DataInicio IS NOT NULL
    SET @Sql += N' AND DataPedido >= @DataInicio';

EXEC sp_executesql @Sql,
     N'@ClienteID INT, @DataInicio DATE',   -- the parameter declarations
     @ClienteID = @ClienteID,               -- the values
     @DataInicio = @DataInicio;

Notice the value never enters the string — only the parameter's name. And if you need to build a table or column name dynamically (which cannot be a parameter), pass it through QUOTENAME(), which escapes brackets and closes the door on injection.

Functions

40. What are the types of function in SQL Server?

Three, and the choice changes performance drastically:

Scalar — returns a single value:

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

Inline table-valued (iTVF) — returns a table, with a single RETURN:

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

Multi-statement table-valued (MSTVF) — returns a table assembled in several steps:

CREATE OR ALTER FUNCTION dbo.mstvf_Resumo (@Ano INT)
RETURNS @R TABLE (Mes INT, Total DECIMAL(18,2))
AS
BEGIN
    INSERT INTO @R SELECT MONTH(DataPedido), SUM(ValorTotal)
    FROM dbo.Pedidos WHERE YEAR(DataPedido) = @Ano GROUP BY MONTH(DataPedido);
    RETURN;
END

41. Why does a scalar function make the query slow?

Because it is executed once per row. On a query over a million rows, the function runs a million times, and each execution is a context switch inside the engine.

Worse: up to SQL Server 2017, the presence of a scalar blocked parallelism on the entire query — even in the parts that had nothing to do with it.

SQL Server 2019 introduced automatic inlining, which rewrites some scalars as an expression and solves the problem. But only some (no table access, no WHILE), and you do not always control the server's version.

The way out is to turn the scalar into an iTVF and call it with APPLY:

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

SELECT p.PedidoID, d.Desconto
FROM   dbo.Pedidos p
CROSS APPLY dbo.tvf_Desconto(p.ValorTotal) d;

42. What makes a function "inline"?

The absence of BEGIN/END. An iTVF has a single RETURN (query) and nothing else. It is that form which lets the optimiser expand the function inside the calling query, as if it were a view with a parameter — the cost becomes practically zero.

The same logic written with BEGIN ... RETURN ... END becomes multi-statement, and the optimiser starts treating it as a black box: it cannot see the content and estimates a fixed number of rows (1 up to SQL Server 2012, 100 afterwards), which produces bad plans.

Rule of thumb: if it fits in one query, make it an iTVF. If it needs several steps, make it a procedure writing into a #table.

43. Can a function change data?

No. No type of function can do an INSERT, UPDATE or DELETE on a permanent table, nor call a procedure that changes data, nor control a transaction.

That is deliberate: since the function is used inside a SELECT, the database needs to be able to run it as many times as it likes, in any order, with no side effects. If you need to change data, use a procedure.

44. When should I use a view, a function or a procedure?

• View — the query is always the same and takes no parameter.
• Inline TVF — the query is always the same, but it takes a parameter. It is the "view with a parameter".
• Procedure — you need to do something in several steps, change data, handle errors or control a transaction.

Indexes and performance

45. What is an index and how does it work underneath?

It is an auxiliary structure that lets you find rows without reading the whole table. Internally it is a B-tree: a root pointing at intermediate pages, which point at leaves with the data in order.

To find a value among a million rows, instead of looking a million times, the database descends 3 or 4 levels of the tree. It is the same idea as the index at the back of a book: you do not read the whole book looking for the word.

CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente ON dbo.Pedidos (ClienteID);

The price: every INSERT, UPDATE and DELETE has to keep the index up to date. Too many indexes make writing slow; too few make reading slow.

46. What is the difference between a clustered and a nonclustered index?

The clustered one defines the physical order of the rows in the table — the data is the index's leaves. That is why there can only be one per table. When you create a primary key, SQL Server creates a clustered index on it by default.

The nonclustered one is a separate structure, holding the indexed columns and a pointer to the row. There can be several.

The analogy that works: the clustered index is the order of the book's chapters; the nonclustered one is the index at the back, saying which page each subject is on.

47. What is INCLUDE in an index and what is a key lookup?

When the index finds the row but does not have all the requested columns, the database has to go back to the table for the rest. That return trip is the key lookup, and it costs dearly when it repeats thousands of times.

INCLUDE solves it: the included columns are stored in the index's leaves, without being part of the search key.

CREATE NONCLUSTERED INDEX IX_Pedidos_Cliente_Data
ON dbo.Pedidos (ClienteID, DataPedido)   -- key: used for filtering and sorting
INCLUDE (ValorTotal, Status);            -- along for the ride: only returned

When the index has everything the query needs, we say it covers the query. The key lookup disappears from the plan.

The order of the columns in the key matters: (ClienteID, DataPedido) serves for filtering by customer and by customer + date. It does not serve for filtering by date alone.

48. What does it mean for a query to be SARGable?

SARGable comes from Search ARGument able: it means the filter can take advantage of the index. It stops being SARGable when you apply a function or a calculation to the column.

-- Not SARGable: it has to compute YEAR() on every row
WHERE YEAR(DataPedido) = 2026

-- SARGable: the index on DataPedido is used
WHERE DataPedido >= '2026-01-01' AND DataPedido < '2027-01-01'

Other common cases: WHERE UPPER(Nome) = 'MARIA' (the collation already handles that), WHERE Codigo + '' = '123' and WHERE ISNULL(Valor, 0) > 100. In all of them the fix is the same — leave the column alone on one side of the comparison.

Note too the range with < at the end, instead of BETWEEN '2026-01-01' AND '2026-12-31'. With date and time, BETWEEN loses everything that happened after midnight on the last day.

49. What is the execution plan and how do I read it?

It is the step-by-step route SQL Server decided to follow to answer the query. In SSMS, Ctrl+M turns on the actual plan and Ctrl+L the estimated one.

What to look for, in order of importance:

• A scan on a large table with a selective filter — a missing index, or a predicate that is not SARGable.
• A Key Lookup with many executions — solve it with INCLUDE.
• An estimate very different from the actual number of rows — stale statistics.
• A thick arrow between operators — a lot of rows travelling; filter earlier.
• An expensive Sort — sometimes it disappears with an index already in the right order.

To measure without depending on the machine's clock:

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

STATISTICS IO shows the logical reads per table. It is the most honest number: time varies with the server's load, reads do not. Did it drop from 400,000 to 300? Then it genuinely improved.

50. What are statistics?

They are the histograms SQL Server keeps about the distribution of each indexed column's values. It uses them to estimate how many rows a filter will return and, from the estimate, to choose the plan.

Stale statistics generate a wrong estimate, which generates a wrong plan. It is what happens after a large load: the database thinks the table has a thousand rows and it has ten million.

UPDATE STATISTICS dbo.Pedidos WITH FULLSCAN;   -- one table
EXEC sp_updatestats;                           -- the whole database

Running UPDATE STATISTICS at the end of a large load is one of the highest-effect, lowest-effort actions there is.

51. What is index fragmentation?

Over time, inserts and deletes leave the index's pages out of order and with empty space. The database ends up reading more pages for the same data.

-- See how fragmented it is
SELECT  OBJECT_NAME(ips.object_id) AS Tabela,
        i.name                     AS Indice,
        ips.avg_fragmentation_in_percent AS Fragmentacao
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN    sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

The usual rule: up to 5%, ignore it; between 5% and 30%, REORGANIZE; above 30%, REBUILD.

ALTER INDEX IX_Pedidos_Cliente ON dbo.Pedidos REORGANIZE;
ALTER INDEX IX_Pedidos_Cliente ON dbo.Pedidos REBUILD;

REORGANIZE is light and online. REBUILD is more thorough, and it locks the table in the Standard edition — leave it for a maintenance window.

52. How do I find the server's heaviest queries?

Through the DMVs (Dynamic Management Views), which expose what the engine is doing:

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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Sort by total_worker_time to find who consumes CPU and by total_logical_reads to find who reads too much. A fast query running 100 thousand times an hour usually weighs more than a slow one running once.

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

Investigating wrong data

53. How do I find duplicate records?

Group by the column that should be unique and count:

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 route — 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 running it in production: swap the DELETE for a SELECT *, check the count and take a backup.

54. How do I find orphan records?

An orphan is a child with no parent — the order whose customer no longer exists:

SELECT p.PedidoID, p.ClienteID
FROM   dbo.Pedidos p
WHERE  NOT EXISTS (SELECT 1 FROM dbo.Clientes c WHERE c.ClienteID = p.ClienteID);

If the foreign key existed and were active, this would not happen. An orphan is a sign of a missing FK, or of an FK that was disabled during a load and never revalidated.

55. The report's total does not match the system's. Where do I start?

Compare the two sides at the same level of detail and look at the difference, instead of looking only at the totals:

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

The most frequent causes, in order:

1. An INNER JOIN where it should be a LEFT — records disappear without warning.
2. A join that multiplies rows — a child table with more than one record makes the parent's value be summed several times.
3. A date filter with BETWEEN — it loses the last day.
4. A NULL in a sum — SUM ignores nulls, + propagates them.
5. A missing status or company filter — it includes cancelled rows, or sums another branch.

The > 0.01 instead of <> 0 is not laziness: with currency rounding, one-cent differences appear from numeric representation.

56. Spaces and capitals are breaking my JOIN. How do I find them?

'ABC ' and 'ABC' look the same on screen. LEN() ignores trailing spaces, DATALENGTH() does not — when the two disagree, you have found it:

SELECT  ClienteID,
        '[' + Codigo + ']' AS ComDelimitador,
        LEN(Codigo)        AS Tamanho,
        DATALENGTH(Codigo) AS Bytes
FROM    dbo.Clientes
WHERE   Codigo <> LTRIM(RTRIM(Codigo));

On SQL Server 2017 or newer, TRIM() cleans both sides at once. For capitals, a CI collation already handles it — if yours is CS, normalise both sides with UPPER().

57. What is DBCC CHECKDB and what is an "untrusted" FK?

DBCC CHECKDB checks the database's physical integrity: corrupted pages, broken pointers, indexes inconsistent with the data. It is heavy — run it in a maintenance window.

DBCC CHECKDB ('VendasDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

An untrusted FK, on the other hand, is a logical problem. It happens after a BULK INSERT or an ALTER TABLE ... NOCHECK CONSTRAINT: the constraint is still there, but SQL Server knows data got in without passing through it.

SELECT name, is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1;

Two consequences: it stops guaranteeing what it promises, and the optimiser stops using it to simplify plans. 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.

58. How do backup and restore work?

Three types, which combine:

-- Full: everything, the basis of any restore
BACKUP DATABASE VendasDB TO DISK = 'D:\bkp\VendasDB_full.bak' WITH INIT, COMPRESSION;

-- Differential: only what changed since the last full backup
BACKUP DATABASE VendasDB TO DISK = 'D:\bkp\VendasDB_diff.bak' WITH DIFFERENTIAL;

-- Log: the transactions since the last log backup (requires the FULL recovery model)
BACKUP LOG VendasDB TO DISK = 'D:\bkp\VendasDB_log.trn';

To restore to a point in time, apply them in order: full → the most recent differential → all the following logs. Use NORECOVERY on all but the last.

RESTORE DATABASE VendasDB FROM DISK = 'D:\bkp\VendasDB_full.bak' WITH NORECOVERY;
RESTORE DATABASE VendasDB FROM DISK = 'D:\bkp\VendasDB_diff.bak' WITH NORECOVERY;
RESTORE LOG      VendasDB FROM DISK = 'D:\bkp\VendasDB_log.trn'
        WITH STOPAT = '2026-03-19T14:30:00', RECOVERY;

An uncomfortable truth: a backup that has never been restored is not a backup. Test the restore periodically, on a separate server.

SSIS and ETL

59. What is ETL and what is SSIS?

ETL stands for Extract, Transform, Load — extract from a source, transform it into the shape you need and load it into a destination. It is the process that takes data from the source system to where it will be analysed.

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

A typical package does this: it reads a file or a table from another system, cleans and converts the data, and writes it into a table in your database — recording what worked and what did not.

60. What is the difference between Control Flow and Data Flow?

They are a package's two surfaces, and confusing them is the most common mistake:

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

The rule that saves the most time: whatever you can do in SQL, do in SQL. An SSIS Sort transformation loads everything into the integration server's memory; an ORDER BY at the source uses the database's index. Leave the Data Flow for moving data and for what the database does badly — reading a file, calling an API, splitting to several destinations.

61. What is staging and why use it?

Staging is an intermediate area: tables where the raw data is dumped with no transformation at all, before it becomes the final data.

It is worth it for three reasons: if something goes wrong, you still have the original material to investigate; transforming with T-SQL over the staging table is faster and easier to test than doing it in a graphical component; and the source is freed up quickly, instead of being held for the whole processing run.

The pattern that handles nearly every load:

1. TRUNCATE the staging table
2. Extract from the source into staging (no transformation)
3. Transform with T-SQL inside staging
4. Load from staging into the destination, inside a transaction
5. Validate and record the outcome

62. What is the difference between a full and an incremental load? What is a watermark?

A full load deletes everything and reloads. Simple and always correct, but unfeasible when the table has millions of rows.

An incremental load brings only what changed since the last run. To know what changed, you store a watermark: the date/time up to which you have already loaded.

CREATE TABLE dbo.ControleCarga (
    Tabela      SYSNAME      NOT NULL PRIMARY KEY,
    UltimaCarga DATETIME2(3) NOT NULL
);

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

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

One detail that prevents losing records: store as the new watermark the time the load started, not the time it finished. Whatever is changed at the source while the package runs would fall into a blind gap if you used the end time.

63. How do I handle a row with an error without bringing the whole package down?

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:

CREATE TABLE stg.LinhasRejeitadas (
    RejeitadaID   INT IDENTITY PRIMARY KEY,
    Pacote        SYSNAME       NOT NULL,
    LinhaOriginal NVARCHAR(MAX) NOT NULL,
    ErroCodigo    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.

After the load, validate what got in:

SELECT 'Pedidos sem cliente' AS Verificacao, COUNT(*) AS Qtd
FROM   stg.Pedidos s
WHERE  NOT EXISTS (SELECT 1 FROM dbo.Clientes c WHERE c.ClienteID = s.ClienteID)
UNION ALL
SELECT 'Valor negativo', COUNT(*) FROM stg.Pedidos WHERE ValorTotal < 0
UNION ALL
SELECT 'Data no futuro', COUNT(*) FROM stg.Pedidos WHERE DataPedido > SYSDATETIME();

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

SSRS

64. What is SSRS and what is a paginated report?

SSRS (SQL Server Reporting Services) is Microsoft's reporting tool. You design in Report Builder or in Visual Studio and publish to a web portal, from which people run the report or receive it by email.

A paginated report is a report with a fixed layout, designed to fit into pages — to be printed or turned into a PDF. An invoice, a statement, a 300-page accounting report with the header repeating on each one. That is where the name comes from: the content is organised into pages, not onto a screen that scrolls.

The generated file is an RDL (Report Definition Language), an XML describing the query, the parameters and the layout.

65. What is the difference between SSRS and Power BI?

They solve different problems:

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", it is SSRS. If it is "I want to understand why the margin fell in the South", it is Power BI. Confusing the two generates months of rework.

66. What are a Data Source, a Dataset and parameters in a report?

• A Data Source is the connection to the database: server, database, credential. It can be shared across several reports.
• A Dataset is the query that brings the data — ideally a procedure, not loose SQL inside the report.
• A Parameter is what the user chooses before running it: a period, a branch, a salesperson.

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

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

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.

A cascading parameter is when one depends on another — choosing the state filters the cities. You create a dataset for each list, and the second receives the value from the first:

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

-- "Cidades" dataset, which receives @UF
SELECT DISTINCT Cidade FROM dbo.Clientes WHERE UF = @UF ORDER BY Cidade;

For a multi-value parameter, SSRS delivers a comma-separated list; on the T-SQL side, use STRING_SPLIT:

WHERE c.UF IN (SELECT value FROM STRING_SPLIT(@Ufs, ','));

67. What is a subscription, and what is the difference between Table, Matrix and List?

A subscription is the schedule: the report runs on its own at the set time 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. 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 filtered by their own region.

On the layout components:

• Table — fixed columns, rows vary. It is the classic report.
• Matrix — the columns vary with the data too. Use it when the months become columns.
• List — a free block repeated per row. Good for a label, a record card, a badge.

SSRS expressions use Visual Basic syntax and start with =:

=Sum(Fields!ValorTotal.Value)
=Format(Fields!ValorTotal.Value, "C2")
=IIf(RowNumber(Nothing) Mod 2 = 0, "#F5F5F5", "White")
="Página " & Globals!PageNumber & " de " & Globals!TotalPages

Beware of IIf: unlike the if of a normal language, it evaluates both sides before choosing. In a division, that means the divide-by-zero error happens even when the condition says not to divide — protect the divisor, not just the condition.

BI, Data Warehouse and ERPs

68. What is BI, what is a Data Warehouse and why not use the production database?

BI (Business Intelligence) is the set of practices that turns raw data into information for deciding: collecting, organising, analysing and presenting.

A Data Warehouse is the database built for that. It exists separately from the production database for three reasons:

1. Performance — a report scanning five years of sales would freeze the system that is recording orders right now.
2. Modelling — the transactional database is normalised to write fast; the analytical one is denormalised to read fast.
3. History — the system stores the current state; the DW stores how things were at each moment.

That difference has a name: OLTP (Online Transaction Processing) is the day-to-day system, with many small writes. OLAP (Online Analytical Processing) is the analytical one, with a few enormous reads.

In dimensional modelling, the standard design is the star schema: a fact table at the centre, surrounded by dimensions.

• A fact measures: quantity, value, discount. It has many rows and few columns.
• A dimension describes: customer, product, date, salesperson. It has few rows and many columns.

CREATE TABLE dim.Cliente (
    ClienteSK INT IDENTITY PRIMARY KEY,   -- surrogate key: the DW's own key
    ClienteBK INT NOT NULL,               -- business key: the source system's id
    Nome      VARCHAR(200) NOT NULL,
    Segmento  VARCHAR(50)  NULL,
    ValidoDe  DATE NOT NULL,
    ValidoAte DATE NULL,                  -- NULL = the current version
    Corrente  BIT  NOT NULL DEFAULT 1
);

CREATE TABLE fato.Vendas (
    VendaSK    BIGINT IDENTITY PRIMARY KEY,
    DataSK     INT NOT NULL,
    ClienteSK  INT NOT NULL,
    ProdutoSK  INT NOT NULL,
    Quantidade INT NOT NULL,
    ValorBruto DECIMAL(18,2) NOT NULL
);

The surrogate key exists so the DW does not depend on the source's id, which can change, repeat across companies or vanish in a migration.

The fact table's granularity is what each row represents — a sale? a sales line item? a day per product? Defining that before creating the table is the model's most important decision, because changing it afterwards means redoing everything.

69. What is an SCD, and what is the difference between type 1 and type 2?

SCD stands for Slowly Changing Dimension: a dimension that changes slowly. The type says what to do when an attribute changes.

• Type 1 — overwrites. The old value is lost. Right for correcting a typo.
• Type 2 — closes the current version and creates a new one. The history is preserved.

Type 2 is what guarantees that last year's revenue keeps adding up under the segment the customer was in at the time, and not the current one:

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

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

70. What are Power BI, DAX and QuickSight? And what changes when querying an ERP?

Power BI is Microsoft's visualisation and analysis tool. What holds up a good report in it is not the charts, but the model: tables related in a star, with a calendar dimension marked as a date table.

DAX (Data Analysis Expressions) is Power BI's calculation language. It looks like an Excel formula, but it works over the whole model:

Faturamento = SUM(fVendas[ValorLiquido])

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

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

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

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

QuickSight is the AWS equivalent. Two terms are enough: SPICE is the in-memory engine, the analogue of Import mode; dataset and analysis separate preparing the data from assembling the visuals. Anyone who understands dimensional modelling and can write SQL switches tools in days.

An ERP (Enterprise Resource Planning) is the system that integrates a company's operations — sales, stock, finance, tax. Microsoft's have quirks that cause errors in whoever arrives without knowing them:

In Dynamics AX, the DATAAREAID column separates the companies within the same database. Every query has to filter on it, otherwise you add up the whole group's revenue:

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

Still on AX: RECID is the tables' real key, and many fields are numeric enums — SALESSTATUS = 3 means "invoiced", and the meaning lives in the system's metadata, not in the database.

In Navision / Business Central, the table name includes the company and uses $, which requires brackets:

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

The trailing _ in No_ is how Navision escapes reserved words, and "empty" dates usually come through as 1753-01-01 — the minimum of datetime — instead of NULL.

The recommendation holds for any ERP: do not query its database directly from the report. Bring it into a staging area, normalise the enums and the names, and build on top of that. Besides protecting the performance of the system the company uses to work, it stops the next ERP update breaking twenty reports at once.