在SQL SERVER中导致索引查找变成索引扫描的问题分析

SELECT NationalIDNumber, LoginID 
FROM HumanResources.Employee 
WHERE NationalIDNumber = 112457891 

SELECT nationalidnumber,
    loginid
FROM  humanresources.employee
WHERE nationalidnumber = N'112457891' 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES 
  (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
  stmt.value('(@StatementText)[1]', 'varchar(max)'), 
  t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), 
  t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), 
  t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), 
  ic.DATA_TYPE AS ConvertFrom, 
  ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
  t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
  t.value('(@Length)[1]', 'int') AS ConvertToLength, 
  query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
  ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') 
  AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') 
  AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

SELECT nationalidnumber,
    loginid
FROM  humanresources.employee
WHERE SUBSTRING(nationalidnumber,1,3) = '112'

SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260

SELECT * FROM Person.Person WHERE BusinessEntityID < 250

SELECT * FROM Person.Person WHERE LastName LIKE 'Ma%'

SELECT * FROM Person.Person WHERE LastName LIKE '%Ma%'

What is the tipping point"no longer selective enough". SQL Server chooses NOT to use the nonclustered index to look up the corresponding data rows and instead performs a table scan.

SET NOCOUNT ON;
DROP TABLE TEST
CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR(8));
CREATE INDEX PK_TEST ON TEST(OBJECT_ID)
DECLARE @Index INT =1;
WHILE @Index <= 10000
BEGIN
  INSERT INTO TEST
  SELECT @Index, 'kerry';
  SET @Index = @Index +1;
END
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE OBJECT_ID= 1

UPDATE TEST SET OBJECT_ID =1 WHERE OBJECT_ID<=2000;
UPDATE STATISTICS TEST WITH FULLSCAN;
SELECT * FROM TEST WHERE OBJECT_ID= 1

SELECT * FROM Sales.SalesOrderDetail_Tmp
WHERE SalesOrderID=43659 AND SalesOrderDetailID<10

SELECT * FROM Sales.SalesOrderDetail_Tmp WHERE SalesOrderDetailID<10