Um MCT (Microsoft Certified Trainer) de um grupo que participo fez a seguinte pergunta:
Tenho uma tabela com dois campos: data e dados. Preciso buscar a última linha inserida na tabela, ou seja, a maior data.
Como informação adicional, temos que as datas são inseridas em ordem crescente, isto é, já estão ordenadas. A solução adotada por ele foi fazer um select top 1 ordenando pela data, descendente.
Claro que o volume de dados na tabela afeta de forma negativa o resultado. Quanto mais registros, pior o desempenho.
Pensei nas seguintes situações:
- Criar um índice nonclustered;
- criar um índice nonclustered, incluindo o campo data;
- criar um índice clustered no campo data;
- criar uma chave primária int com autonumeração e buscar o valor da última chave inserida, usando funções do SQL. <<< a melhor opção, na minha opinião
Segue o código com os testes:
USE MASTER
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'EX')
DROP DATABASE EX
CREATE DATABASE EX
GO
USE EX
CREATE TABLE TABELA
(
DATA DATETIME,
DADO VARCHAR(50)
)
DECLARE @X INT = 10
WHILE @X >= 1
BEGIN
INSERT INTO TABELA VALUES (DATEADD(D, -@X, GETDATE()) , NEWID())
SET @X -= 1
END
SET SHOWPLAN_TEXT ON
SELECT * FROM TABELA
/*
|--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
*/
SELECT MAX(DATA) FROM TABELA
/*
|--Stream Aggregate(DEFINE:([Expr1004]=MAX([EX].[dbo].[TABELA].[DATA])))
|--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
*/
SELECT * FROM TABELA WHERE DATA IN (SELECT MAX(DATA) FROM TABELA)
/*
|--Nested Loops(Inner Join, WHERE:([Expr1008]=[EX].[dbo].[TABELA].[DATA]))
|--Stream Aggregate(DEFINE:([Expr1008]=MAX([EX].[dbo].[TABELA].[DATA])))
| |--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
|--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA
/*
|--Sort(TOP 1, ORDER BY:([EX].[dbo].[TABELA].[DATA] ASC))
|--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA DESC
/*
|--Sort(TOP 1, ORDER BY:([EX].[dbo].[TABELA].[DATA] DESC))
|--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
*/
SET SHOWPLAN_TEXT OFF
CREATE CLUSTERED INDEX IDX1 ON TABELA(DATA)
EXEC sp_helpindex TABELA
/*
index_name index_description index_keys
------------- --------------------------------- ------------
IDX1 clustered located on PRIMARY DATA
*/
SET SHOWPLAN_TEXT ON
SELECT * FROM TABELA
/*
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]))
*/
SELECT MAX(DATA) FROM TABELA
/*
|--Stream Aggregate(DEFINE:([Expr1004]=MAX([EX].[dbo].[TABELA].[DATA])))
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]),
WHERE:([EX].[dbo].[TABELA].[DATA] IS NOT NULL) ORDERED BACKWARD)
*/
SELECT * FROM TABELA WHERE DATA IN (SELECT MAX(DATA) FROM TABELA)
/*
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Seek(OBJECT:([EX].[dbo].[TABELA].[IDX1]),
SEEK:([EX].[dbo].[TABELA].[DATA] IsNotNull) ORDERED BACKWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA
/*
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]), ORDERED FORWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA DESC
/*
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]), ORDERED BACKWARD)
*/
SET SHOWPLAN_TEXT OFF
DROP INDEX TABELA.IDX1
CREATE NONCLUSTERED INDEX IDX1 ON TABELA(DATA)
EXEC sp_helpindex TABELA
/*
index_name index_description index_keys
------------- --------------------------------- ------------
IDX1 nonclustered located on PRIMARY DATA
*/
SET SHOWPLAN_TEXT ON
SELECT * FROM TABELA
/*
|--Table Scan(OBJECT:([EX].[dbo].[TABELA]))
*/
SELECT MAX(DATA) FROM TABELA
/*
|--Stream Aggregate(DEFINE:([Expr1004]=MAX([EX].[dbo].[TABELA].[DATA])))
|--Top(TOP EXPRESSION:((1)))
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]),
WHERE:([EX].[dbo].[TABELA].[DATA] IS NOT NULL) ORDERED BACKWARD)
*/
SELECT * FROM TABELA WHERE DATA IN (SELECT MAX(DATA) FROM TABELA)
/*
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(OBJECT:([EX].[dbo].[TABELA].[IDX1]),
SEEK:([EX].[dbo].[TABELA].[DATA] IsNotNull) ORDERED BACKWARD)
|--RID Lookup(OBJECT:([EX].[dbo].[TABELA]),
SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA
/*
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]), ORDERED FORWARD)
|--RID Lookup(OBJECT:([EX].[dbo].[TABELA]),
SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA DESC
/*
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]), ORDERED BACKWARD)
|--RID Lookup(OBJECT:([EX].[dbo].[TABELA]),
SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
*/
SET SHOWPLAN_TEXT OFF
DROP INDEX TABELA.IDX1
CREATE NONCLUSTERED INDEX IDX1 ON TABELA(DATA) INCLUDE (DADO)
EXEC sp_helpindex TABELA
/*
index_name index_description index_keys
------------- --------------------------------- ------------
IDX1 nonclustered located on PRIMARY DATA
*/
SET SHOWPLAN_TEXT ON
SELECT * FROM TABELA
/*
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]))
*/
SELECT MAX(DATA) FROM TABELA
/*
|--Stream Aggregate(DEFINE:([Expr1004]=MAX([EX].[dbo].[TABELA].[DATA])))
|--Top(TOP EXPRESSION:((1)))
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]),
WHERE:([EX].[dbo].[TABELA].[DATA] IS NOT NULL) ORDERED BACKWARD)
*/
SELECT * FROM TABELA WHERE DATA IN (SELECT MAX(DATA) FROM TABELA)
/*
|--Top(TOP EXPRESSION:((1)))
|--Index Seek(OBJECT:([EX].[dbo].[TABELA].[IDX1]),
SEEK:([EX].[dbo].[TABELA].[DATA] IsNotNull) ORDERED BACKWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA
/*
|--Top(TOP EXPRESSION:((1)))
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]), ORDERED FORWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA DESC
/*
|--Top(TOP EXPRESSION:((1)))
|--Index Scan(OBJECT:([EX].[dbo].[TABELA].[IDX1]), ORDERED BACKWARD)
*/
SET SHOWPLAN_TEXT OFF
DROP TABLE TABELA
GO
CREATE TABLE TABELA
(
COD INT IDENTITY CONSTRAINT PK PRIMARY KEY,
DATA DATETIME,
DADO VARCHAR(50)
)
DECLARE @X INT = 10
WHILE @X >= 1
BEGIN
INSERT INTO TABELA VALUES (DATEADD(D, -@X, GETDATE()) , NEWID())
SET @X -= 1
END
EXEC sp_helpindex TABELA
/*
index_name index_description index_keys
------------- ---------------------------------------------------- ------------
PK clustered, unique, primary key located on PRIMARY COD
*/
SET SHOWPLAN_TEXT ON
SELECT * FROM TABELA
/*
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[PK]))
*/
SELECT MAX(COD) FROM TABELA
/*
|--Stream Aggregate(DEFINE:([Expr1003]=MAX([EX].[dbo].[TABELA].[COD])))
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[PK]), ORDERED BACKWARD)
*/
SELECT * FROM TABELA WHERE COD IN (SELECT MAX(COD) FROM TABELA)
/*
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[PK]), ORDERED BACKWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY COD
/*
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[PK]), ORDERED FORWARD)
*/
SELECT TOP 1 * FROM TABELA ORDER BY DATA DESC
/*
|--Sort(TOP 1, ORDER BY:([EX].[dbo].[TABELA].[DATA] DESC))
|--Clustered Index Scan(OBJECT:([EX].[dbo].[TABELA].[PK]))
*/
SET SHOWPLAN_TEXT OFF
SELECT @@IDENTITY
--NULL
SELECT SCOPE_IDENTITY()
--10
SELECT IDENT_CURRENT('TABELA')
--10
INSERT INTO TABELA VALUES (GETDATE() , NEWID())
SELECT @@IDENTITY
--11
SELECT SCOPE_IDENTITY()
--11
SELECT IDENT_CURRENT('TABELA')
--11
SET SHOWPLAN_TEXT ON
SELECT * FROM TABELA WHERE COD IN (SELECT IDENT_CURRENT('TABELA'))
/*
|--Clustered Index Seek(OBJECT:([EX].[dbo].[TABELA].[PK]),
SEEK:([EX].[dbo].[TABELA].[COD]=ident_current(N'TABELA')) ORDERED FORWARD)
*/
SELECT * FROM TABELA WHERE COD = IDENT_CURRENT('TABELA')
/*
|--Clustered Index Seek(OBJECT:([EX].[dbo].[TABELA].[PK]),
SEEK:([EX].[dbo].[TABELA].[COD]=ident_current(N'TABELA')) ORDERED FORWARD)
*/
SET SHOWPLAN_TEXT OFF