Given this table:
CREATE TABLE [dbo].[visit]( [visit_id] [uniqueidentifier] NOT NULL, [group_id] [uniqueidentifier] NOT NULL, [visitor_company] [nvarchar](50) NOT NULL, -- Other columns PRIMARY KEY CLUSTERED ( [visit_id] ASC -- per @Mark Sinkson's comment, I agree that this is a poor choice for a clustered index, but this is a different can of worms )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
And given that the application executes these two queries frequently:
SELECT * -- not literally *; just don't want to complicate my question FROM [dbo].[visit] WHERE group_id = @group_id SELECT DISTINCT (visitor_company) FROM [dbo].[visit] WHERE group_id = @group_id
- Does a non-clustered index on
group_id, visitor_companyoptimize both queries?
- Or should I create a non-clustered index on
EDIT 1: This is not homework. I'm a full-stack developer (not a DBA). I'm torn between Options 2 and 3 and I'd like some experts to weigh in.
EDIT 2: Constraint: I can't change
visit_id to an