Nonclustered Index question

Transact-SQL
1

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.

2

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

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

3

probably NOT but it is not guaranteed.

4

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?

5

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 ).

1 Like
6

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

7

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?