0

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_company optimize both queries?
  • Or should I create a non-clustered index on group_id as well?

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

7
  • How many 'other columns'? Do you really need to run SELECT *? Is the Group_ID a Foreign Key to another table? UniqueIdentifier is a poor choice for a Clustered Index as you'll get page splits and fragmentation (could cause issues on highly used systems). There's quite a few issues that could be fixed Aug 5, 2016 at 13:25
  • @MarkSinkinson: Please see my update.
    – Jim G.
    Aug 5, 2016 at 13:32
  • @JimG. If you don't tell us the actual queries that are run, any index suggestions are just guesses. It's not complicating the question by providing this info. At the minute you have no optimal suggestions to solve both issues. Option 1 satisfies nothing efficiently as it will result in Key Lookups, Option 2 satisfies query 2 only. Option 3 is no better as Option 1 is no good. Aug 5, 2016 at 13:35
  • @MarkSinkinson: Does Option 2 satisify Query 1 as well?
    – Jim G.
    Aug 5, 2016 at 14:00
  • @JimG. Like I say above, Option 2 satisfies query 2 only Aug 5, 2016 at 14:02

2 Answers 2

0

At first I'd suggest to have group_id and visit_id in INT or at least BIGINT.
If that table has only 3 columns it might worth to create not-unique clustered index by group_id and unique constraint on visit_id:

CREATE TABLE [dbo].[visit](
    [visit_id] [uniqueidentifier] NOT NULL,
    [group_id] INT NOT NULL, 
    [visitor_company] [nvarchar](50) NOT NULL
);
GO
CREATE CLUSTERED INDEX CLIX_Visit ON [dbo].[visit]([group_id]);
GO
ALTER TABLE [dbo].[visit] ADD CONSTRAINT UQ_Visit UNIQUE ([visit_id]);
GO
2
  • Please see my update. As a constraint, I can't change visit_id from a uniqueidentifier to an int.
    – Jim G.
    Aug 5, 2016 at 14:03
  • OK. Keep it as uniqueidentifier, but everything else is the same. Aug 5, 2016 at 14:05
0

Based on your table definition and queries, you only need to define a non-clustered index on GROUP_ID, but INCLUDE visitor_company. That should allow both queries to be 'covered' by one non-clustered index.

By INCLUDING visitor_company, you eliminate the overhead of maintaining that column in any kind of order

The non-clustered key will already have the primary key since you have defined it as the clustering key

2
  • i just noticed you comment in the create table statement about 'other columns'. to get a full covering non-clustered index, those additional columns would have to be 'included' in the non-clustered index Aug 5, 2016 at 16:01
  • 2
    I think an index on (group_id, visitor_company) will be more efficient than the (group_id) INCLUDE (visitor_company), for the DISTINCT query. Aug 5, 2016 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.