Table Scan, Index Scan, Index Seek and RID/Key Lookup operators

Introduction

Currently studying for some Microsoft SQL Server exams and I ran into some articles about tablescan-, indexscan-, index seek- and RID/key lookup operators. I was looking for information about the differences between the types of executionplan operators. This blogpost is a description about queryplan operators regarding indexes.

Table Scan

Table Scan means that the whole table is scanned and every row is returned. Typically, this happens when there is no index defined on the table.

Clustered Index Scan

A Clustered Index Scan is when SQL server reads the whole index looking for matches.  Since a scan touches every row in the index, the cost is proportional to the number of rows in the table. This is only useful for small tables.

There seems some discussion on the Pinal Dave's blog about the whether the Index Scan is the same as a Table Scan. It seems to me that there are some slight differences but these can be ignored.

Clustered Index Seek

An Clustered Index Seek is where SQL server uses the B-tree of the index to seek directly for matching records. Clustered Index seeks are preferred for selective queries and this means that fewer rews are returned when this is used. General speaken the Clustered Index Seek step is used when the optimizer decides that the Clustered index can be used for the index seek otherwise a Index Scan is done (which is mainly the same as a Table Scan).

Non-Clustered Index Seek

Whereas the Clustered Index Seek retrieves records at the leaf levels of the B-tree, the non-clustered index has pointers to the actual data in the clustered index or table (heap).

Key Lookup

A Key Lookup happens when a index does not contains all the information to answer a query and the query optimizer decides to use the information of another index to complete the requested information.

RID lookup

Sames as Key Lookup but then the information of a table is used, instead of a index.

Conclusion

In my opinion the following operators can be ordered by best practice when optimizing your queries:

1. Clustered Index seek (in case of large selective tables).
2. Non Clustered Index Seek.
3. Key lookup.
4. RID lookup.
5. Index scan (may be useful for small tables)
6. Table scan

Greetz,
Hennie

Importing data from MS Access into SQL Server with TSQL

Introduction
In my current client project i have to create an import procedure for data from a MS Access database. This MS Access database is protected with a Workgroup file (.mdw). The .mdw file stores usernames and passwords of users allowed to open a specific MS Access database and it must be linked to an Access database to be effective. In this blogpost i'll explain three methods of importing data from a MS Access database:

1. Simple MS Access database import.
2. MS Access database import with password.
3. MS Access database import with an .mdw file.

For importing a MS Access file i'll be using the OPENDATASOURCE statement. This provides ad hoc connection information without using a linked server name. 

Simple MS Access database
I've created a small MS  Access database with two lines. See the screenshot below:

You can retrieve the data from the Access file with the following code:



sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb"')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go

Resulting in:

MS Access database import with password
The next step is to load data from the MS Access file that is protected with a password.  First open de MS Access database in an exclusive mode.

Below you can see how you set the password in MS Access database.

The next step is to execute the following code :


sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:Database Password=test;')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go

Resulting in 

MS Access database import with .mdw file
And now we come to the final case in which the MS Access database is protected with a workgroup file. This workgroup file enables securing the MS Access database with encrypted PID's, usernames and passwords. This generates a unique code that MS Access uses to determine the permissions

You can create a Workgroup file with the menuoption Tools in MS Access. It will show the following window: The text is in the dutch language..

Next.

Select the relevant database objects:

Next

Next

Next

Next

Next

The following files are created : Test.mdb, Test.snp, Test.ldb, beveiliging.mdw and test.bak.

If you use the wrong mdw file an error occurs.

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\wrong.mdw";User ID=testuser;Password=test;')...test;
GO



OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

If you use the right MDW file  the data is returned. In the below snippet you'll see a disadvantage of using the OPENDATASOURCE statement: The name and password is visible.

sp_configure 'show advanced options',1   

reconfigure with override   

go   

sp_configure 'Ad Hoc Distributed Queries',1   

reconfigure with override   

go  

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\Beveiliging.mdw";User ID=testuser;Password=test;')...test;

GO

sp_configure 'show advanced options', 1 

reconfigure with override   

go

sp_configure 'Ad Hoc Distributed Queries', 0

reconfigure with override   

go

The result :

Conclusion
This blogpost is about reading data from a table in MS Access database, a MS Access database protected with a password, and a MS Access database protected with a mdw file.

The OPENDATASOURCE statement is less secure because the name and password should be entered in the statement. In one of the next blogposts i'll show the Linked server option.

Greetz

Hennie

SSIS : WITH RESULT SETS in Denali

In a blog of Derek Dieter i've read something about executing stored procedures with result sets. As it seems now to me, the WITH RESULT SETS clause of the EXEC statement makes it possible to specify explicitly the result set of a stored procedure (and not change it with casting). At this moment the following websites seems to have some interesting information about this option:

• Microsoft SQL Server forum.
• James Rowland-Jones.
• MSDN.

The EXEC statement is enhanced with the following clauses:

• RESULT SETS UNDEFINED 
• RESULT SETS NONE
• RESULT SETS ( <result_sets_definition> [,...n ] )

And the <result_set_definition> does have the following possibilities specifying the result:

• Datatype.
• Length.
• NOT NULL| NULL 
• Collation.

Although it's possible to do some casting of datatypes of fields, it's only possible when the data fits into this sort of a cast. Below you can see some examples i experimented with.

First let's create a stored procedure:

CREATE PROCEDURE spGetPerson
AS
BEGIN
    SELECT FirstName, MiddleName, LastName FROM Person.Person
END 

and execute it :

EXEC spGetPerson
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50),
        NameLast NVarchar(50)
    )
);


Execute this will give an error:

EXEC spGetPerson
WITH RESULT SETS NONE
GO

Msg 11535, Level 16, State 1, Procedure spGetPerson, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.


And executing this is ok :


EXEC spGetPerson
WITH RESULT SETS UNDEFINED
GO

You can also use this for executing this :

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameMiddle nVarchar(50) NOT NULL,
        NameLast NVarchar(50)
    )
);


And this will give an error:

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50),
        NameLast NVarchar(50)
    )
);

Msg 11537, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.


And  the following script shows all the possibilities you have with RESULT SETS (except multiple Result sets):

EXECUTE sp_executesql  N'SELECT FirstName, MiddleName, LastName FROM Person.Person'
WITH RESULT SETS
(
    (
        NameFirst NVarchar(50) NOT NULL,
        NameMiddle nVarchar(50) NULL,
        NameLast NVarchar(50) COLLATE Latin1_General_100_CI_AS
    )
);


And the final example shows multiple result sets:

--Suppose you have two resultsets:
CREATE PROC Production.ProductList @ProdName nvarchar(50)
AS
-- First result set
SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE Name LIKE @ProdName;
-- Second result set
SELECT Name, COUNT(S.ProductID) AS NumberOfOrders
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS S
        ON P.ProductID  = S.ProductID
    WHERE Name LIKE @ProdName
    GROUP BY Name;
GO

-- Execute the procedure
EXEC Production.ProductList '%tire%'
WITH RESULT SETS
(
    (ProductID int,   -- first result set definition starts here
    Name Name,
    ListPrice money)
    ,                 -- comma separates result set definitions
    (Name Name,       -- second result set definition starts here
    NumberOfOrders int)
);


Conclusion:
It sounded as a promising feature to me but i'm a bit disappointed. There are only four options available and i realize that it has some potential. But selecting some columns is not possible, and many more things.

Greetz,
Hennie

Varchar(MAX)

Today i had a discussion with some guys from the test team. The discussion was about how they were going to test some the import fields and specific, the length of a field. Our ETL extracts XML messages into dimensions and facts. Some of fields (Elements) in a XML message doesn't have a length restriction so how much characters should some varchar fields be? Hmmmmm Why shouldn't we make all the varchar fields Varchar(MAX)? Why not?!! But first let's do some investigation with my friend Google. And this is what i found out.

Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005/2008 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types.

The following problems are identified with varchar fields on the blogs, websites, etc:

• For data values less than 8000 bytes, this is the common choice as it avoids wasted space. However, if the application can change the size of the data after the initial creation of the row, then there is the possibility of fragmentation occuring through page-splits. Answer: We do only insertions in the dimensions and the fact.

• With the added benefit that the value can grow larger than 8000 bytes. In that case it will be pushed off-row automatically, and start incurring the extra I/O for each access. Answer: Okay this could happen but if the fields are smaller than 8000 (and mostly it is) will be there no problem. So no problem!

• An additional benefit of storing the data off-row is that it can be placed in a separate filegroup, possibly on less expensive storage (e.g. RAID 5) - but then there's the drawback that it can't be moved after being created except with an export/import operation. This option has the same online operations drawback as storing the data in-row. Answer: hmmm not sure how you can store off row data in another filegroup. But may be in the future we could this. Let's keep this mind.

So this is it. I can't think of any disadvantages for storing strings in varchar(MAX) fields.  So, i'm really thinking for using this....

Greetz,
Hennie