/*============================================================================
  File:     SQL 2000 Issue - Always Supply a WHERE clause.sql

  Summary:  See how covering can help low selectivity queries.

  Date:     June 2006

  SQL Server Version: 9.00.2047.00 (SP1)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE CREDIT
go

-- These samples use the Credit database. You can download and restore the
-- credit database from here:
-- http://www.sqlskills.com/resources/conferences/CreditBackup80.zip

-- NOTE: This is a SQL Server 2000 backup and MANY examples will work on 
-- SQL Server 2000 in addition to SQL Server 2005.

SET STATISTICS IO ON
-- Turn Graphical Showplan ON (Ctrl+K)
go

-- Create an interesting covering index
CREATE INDEX MemberNameIndex 
	ON Member(LastName, FirstName, MiddleInitial)
go
SELECT LastName, FirstName, MiddleInitial
FROM Member
-- NO WHERE Clause
-- Results In Table Scan
go
SELECT LastName, FirstName, MiddleInitial
FROM Member
WHERE LastName > ''
-- Index Seek (even though it's really a LEAF SCAN of the NC Index!)
go
-- Compare the two 
-- Highlight both and execute with Execution Plan On

-- This is a bug in SQL Server 2000 RTM/SP1/SP2/SP3/SP4...
-- but it could be due to table size. However, it DOES not
-- happen in SQL Server 2005. 

-- SQL Server 2000 may not consider a covering index 
-- when a WHERE clause doesn't exist. But if you can 
-- come up with an interesting hint toward an index then 
-- it might actually help. This is your best type of HINT.
-- TRY to avoid forcing indexes!

-- DROP INDEX Member.MemberNameIndex 