Blog – Fabiano Neves Amorim

DECLARE @VendorID Int,

        @Name     VarChar(80)

DECLARE Cur_Vendor CURSOR FOR

 SELECT VendorID, Name

   FROM Purchasing.Vendor

OPEN Cur_Vendor

FETCH NEXT FROM Cur_Vendor

 INTO @VendorID, @Name

WHILE @@FETCH_STATUS = 0

BEGIN

       PRINT @Name

             FETCH NEXT FROM Cur_Vendor

    INTO @VendorID, @Name

END

CLOSE Cur_Vendor

DEALLOCATE Cur_Vendor

GO

DECLARE @ROWID Int,

        @Name  VarChar(80)

SET @ROWID = 0;

SELECT TOP 1

       @ROWID = VendorID,

       @Name  = Name     

  FROM Purchasing.Vendor

 WHERE VendorID > @ROWID

 ORDER BY VendorID

WHILE @@ROWCOUNT > 0

BEGIN

  PRINT @Name

  SELECT TOP 1

         @ROWID = VendorID,

         @Name  = Name     

    FROM Purchasing.Vendor

   WHERE VendorID > @ROWID

   ORDER BY VendorID

END

GO

DECLARE @TMP TABLE(Nome VarChar(80) PRIMARY KEY)

DECLARE @Nome NVarChar(80)

SET @Nome = ‘Teste’

— Gera FULL SCAN

SELECT * FROM @TMP

 WHERE Nome = @Nome

GO

DECLARE @TMP TABLE(Nome VarChar(80) PRIMARY KEY)

DECLARE @Nome VarChar(80)

SET @Nome = ‘Teste’

— Gera SEEK

SELECT * FROM @TMP

 WHERE Nome = @Nome

— Create a checksum index.

SET ARITHABORT ON;

USE AdventureWorks;

GO

ALTER TABLE Production.Product

ADD cs_Pname AS CHECKSUM(Name);

GO

CREATE INDEX Pname_index ON Production.Product (cs_Pname);

GO

/*Use the index in a SELECT query. Add a second search

condition to catch stray cases where checksums match,

but the values are not the same.*/

SELECT *

FROM Production.Product

WHERE CHECKSUM(N’Bearing Ball’) = cs_Pname

AND Name = N’Bearing Ball’;

GO

IF OBJECT_ID(‘tempdb.dbo.#TMP’) IS NOT NULL

BEGIN

  DROP TABLE #TMP

END

CREATE TABLE #TMP (ID Int IDENTITY(1,1) PRIMARY KEY)

GO

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

INSERT INTO #TMP DEFAULT VALUES

GO

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SELECT * FROM #TMP

 WHERE ID IN (1,2,3,4,5)

/*

Coluna argument

OBJECT:([tempdb].[dbo].[#TMP]),

       SEEK:(

               [tempdb].[dbo].[#TMP].[ID]=(1) OR

               [tempdb].[dbo].[#TMP].[ID]=(2) OR

               [tempdb].[dbo].[#TMP].[ID]=(3) OR

               [tempdb].[dbo].[#TMP].[ID]=(4) OR

               [tempdb].[dbo].[#TMP].[ID]=(5)

            )

       ORDERED FORWARD

Repare no argument que o SQL gerou, ou seja irá acessar uma vez a tabela #TMP para cada opção do IN

podemos confirmar isso no Scan Count do statistics io

— IO

Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/

GO

SELECT * FROM #TMP

 WHERE ID BETWEEN 1 AND 5

/*

OBJECT:([tempdb].[dbo].[#TMP]),

       SEEK:(

              [tempdb].[dbo].[#TMP].[ID] >= (1) AND

              [tempdb].[dbo].[#TMP].[ID] <= (5)

            )

       ORDERED FORWARD

Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Repare na diferença de IO.

*/