SQLTeam.com | Weblogs | Forums

Nonclustered Index question

Hi,

I have this table;

CREATE TABLE [dbo].[GameRequests](
[Id] [int] IDENTITY(1,1) NOT NULL,
[referenceId] [uniqueidentifier] NULL,
[productCode] nvarchar NULL,
[quantity] [int] NOT NULL,
[version] nvarchar NULL,
[signature] nvarchar NULL,
[ApplicationCode] nvarchar NULL,
[requestDateTime] [datetime] NULL,
[merchantProductCode] nvarchar NULL,
[customerID] [int] NULL,
[password] nvarchar NULL,
[shopNo] nvarchar NULL,
[safeNo] nvarchar NULL,
[cashierNo] nvarchar NULL,
[validatedToken] nvarchar NULL,
[service] nvarchar NULL,
CONSTRAINT [PK_dbo.GameRequests] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am using this query below;

SELECT MAX([RequestDateTime]) FROM GameRequests WHERE[ShopNo] = @shopNo and RequestDateTime >= CONVERT(DATE, GETDATE())

Should I add indexes for shopNo and RequestDateTime? If so shopNo is varchar(max), changing the max to 200 (actual data is 999999) affects the data inside?

Thank you.

will there ever be ShopNO that have alpha in them?
Like

Şiş Kebap 001
Döner 9999
İskender Shop 023

probably NOT but it is not guaranteed.

Is that why the it is data type varchar ?

To answer the above question: Are you working on a production server or a development server?

The first thing you should do is cluster the table on ( requestDateTime, Id ) [rather than on just id]. If you want, you can retain the current PK, just make it nonclustered.

That will help with the query above. If you add a lot of rows in a day, across all shops, you may still want to consider adding a nonclustered index on ( shopNo, requestdatetime ).

First I will try on the dev then on the test and prod server. ShopNO might be 12345ABC at most I think.

I changed the query;

SELECT DATEDIFF(SECOND, [RequestDateTime], GETDATE()) As [SecondsSinceLastRequest] FROM [dbo].[GameRequests] WHERE [ShopNo] = @shopNo AND DATEDIFF(SECOND, [RequestDateTime], GETDATE() ) < @delay

Should I still need to add an index? if so why?