I am trying to analyze queries on this table:
CREATE TABLE [dbo].[Values]( [tid] [smallint] NOT NULL, [t] [datetime2](3) NOT NULL, [v] [real] NOT NULL, CONSTRAINT [PK_Values_Unique] PRIMARY KEY CLUSTERED ( [t] ASC, [tid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
I am reviewing some Estimated Execution Plans using SQL Server Management Studio, first query.
SELECT * FROM [dbo].[Values] WHERE [tid] = 1
Displaying Estimated Execution Plan for this query yields:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Values] ([tid]) INCLUDE ([timestamp],[v])
Which, I can understand, it wants to have a quick look up by
tid, altough I don't fully why the
INCLUDE is suggested.
Then I tried a second query (another tid), which is pretty the same with another
SELECT * FROM [dbo].[Values] WHERE [tid] = 1 or [tid] = 2
But now, SSMS, doesn't suggest the previous index.
Why does SSMS suggests an INDEX for the first query but not for the second now although they are pretty the same ?
Should adding this suggested INDEX be helpful for both types of queries?
EDIT: This table has over 2 * 10^9 rows.