# Database Discussions > Microsoft SQL Server 2005 >  only clusted index used !

## Alex_G

Hi we have table with 10000000  rows

it take 40 second to execute  unless  we use WITH (INDEX option 

I create empty table with all indexes ,still execution plan show that only clusted index scan used...

We tried to run same statement on 12 different servers with same result

Why optimiser ignore other indexes ?





Here is table and query 

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors

GO

CREATE TABLE #tmpErrors (Error int)

GO

SET XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

GO

PRINT N'Creating [dbo].[TransactionsPOS]'

GO

CREATE TABLE [dbo].[TransactionsPOS]

(

[trn_TransactionID] [bigint] NOT NULL,

[trn_ShiftID] [int] NOT NULL,

[trn_FinanceTransTypeCode] [int] NOT NULL,

[trn_RetrievalRefNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_R__4C0144E4] DEFAULT (' '),

[trn_SequenceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_S__4CF5691D] DEFAULT (' '),

[trn_TransSettlementStatusCode] [int] NOT NULL,

[trn_Date] [datetime] NOT NULL,

[trn_TransTypeCode] [int] NOT NULL,

[trn_TransInfoCode] [int] NOT NULL,

[trn_ModalityTypeCode] [int] NOT NULL,

[trn_PaymentModalityCode] [int] NOT NULL,

[trn_CardTypeRetailerNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_C__4DE98D56] DEFAULT (' '),

[trn_CardNoLenght] [int] NOT NULL CONSTRAINT [DF__Transacti__trn_C__4EDDB18F] DEFAULT ((0)),

[trn_CardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_C__4FD1D5C8] DEFAULT (' '),

[trn_CardAdditionalInfo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_C__50C5FA01] DEFAULT (' '),

[trn_CardExpiryDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[trn_ResponseCode] [int] NOT NULL,

[trn_Amount] [numeric] (13, 4) NOT NULL CONSTRAINT [DF__Transacti__trn_A__51BA1E3A] DEFAULT ((0)),

[trn_ApprovalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_A__52AE4273] DEFAULT (' '),

[trn_IsAuthorisedOnly] [tinyint] NOT NULL CONSTRAINT [DF__Transacti__trn_I__53A266AC] DEFAULT ((0)),

[trn_ReversalCode] [int] NOT NULL,

[trn_InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_I__54968AE5] DEFAULT (' '),

[trn_OperatorNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_O__558AAF1E] DEFAULT (' '),

[trn_OptionalData] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_O__567ED357] DEFAULT (' '),

[trn_IsManuallyEntered] [tinyint] NOT NULL CONSTRAINT [DF__Transacti__trn_I__5772F790] DEFAULT ((0)),

[trn_LastUpdatedTS] [datetime] NOT NULL CONSTRAINT [DF__Transacti__trn_L__58671BC9] DEFAULT (getdate()),

[trn_SourceFeedID] [int] NULL

)

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating primary key [XPKTransactionsPOS] on [dbo].[TransactionsPOS]'

GO

--ALTER TABLE [dbo].[TransactionsPOS] ADD CONSTRAINT [XPKTransactionsPOS] PRIMARY KEY NONCLUSTERED ([trn_TransactionID])

ALTER TABLE [dbo].[TransactionsPOS] ADD CONSTRAINT [XPKTransactionsPOS] PRIMARY KEY CLUSTERED ([trn_TransactionID])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_ShiftID] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ShiftID] ON [dbo].[TransactionsPOS] ([trn_ShiftID])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_ShiftNo] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ShiftNo] ON [dbo].[TransactionsPOS] ([trn_ShiftID])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_Date] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_Date] ON [dbo].[TransactionsPOS] ([trn_Date] DESC)

--CREATE CLUSTERED INDEX [IX_TransactionsPOS_Date] ON [dbo].[TransactionsPOS] ([trn_Date] DESC)

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_TransTypeCode] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_TransTypeCode] ON [dbo].[TransactionsPOS] ([trn_TransTypeCode])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_ModalityTypeCode] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ModalityTypeCode] ON [dbo].[TransactionsPOS] ([trn_ModalityTypeCode])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_PaymentModalityCode] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_PaymentModalityCode] ON [dbo].[TransactionsPOS] ([trn_PaymentModalityCode])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TransactionsPOS_ReversalCode] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ReversalCode] ON [dbo].[TransactionsPOS] ([trn_ReversalCode])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating index [IX_TRansactionsPos_InvoiceNo] on [dbo].[TransactionsPOS]'

GO

CREATE NONCLUSTERED INDEX [IX_TRansactionsPos_InvoiceNo] ON [dbo].[TransactionsPOS] ([trn_InvoiceNo])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Adding foreign keys to [dbo].[Payments]'

GO

--ALTER TABLE [dbo].[Payments] WITH NOCHECK ADD

--CONSTRAINT [FK_pmt_trn] FOREIGN KEY ([pmt_TransactionID]) REFERENCES [dbo].[TransactionsPOS] ([trn_TransactionID]) NOT FOR REPLICATION

--GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT>0 BEGIN

PRINT 'The database update succeeded'

COMMIT TRANSACTION

END

ELSE PRINT 'The database update failed'

GO

DROP TABLE #tmpErrors

GO

-- this query show that execution plan use clusted index scan, qurry run for 40 seconds

SELECT TOP 1000

dbo.TransactionsPOS.*

FROM 

dbo.TransactionsPOS

--WITH (INDEX(IX_TransactionsPOS_Date))

WHERE

trn_Date >= '2008-03-01'

AND

trn_Date <= '2008-03-11'







-- this query show that execution plan use X_TransactionsPOS_Date index seek, qurry run for 3 seconds

SELECT TOP 1000

dbo.TransactionsPOS.*

FROM 

dbo.TransactionsPOS

--WITH (INDEX(IX_TransactionsPOS_Date))

WHERE

trn_Date >= '2008-03-01'

AND

trn_Date <= '2008-03-11'

----------


## skhanal

Try order by trn_date.

----------


## Alex_G

order by trn_Date works 
when 
with 
SELECT TOP 1000
and 
up to 
SELECT TOP 22500

if I  run 
SELECT TOP 22501
or 
SELECT *
it still scan


but if range is small (1day)
query use correct index 

trn_Date >= '2008-03-01'
AND
trn_Date <= '2008-03-02'

----------


## skhanal

index is not useful for large result set, so optimizer decides to use clustered index scan.

you can see total logical io by setting

set statistics_io on

--run query with and without hint

----------

