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.

  • 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 – Mark Sinkinson Aug 5 '16 at 13:25
  • @MarkSinkinson: Please see my update. – Jim G. Aug 5 '16 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. – Mark Sinkinson Aug 5 '16 at 13:35
  • @MarkSinkinson: Does Option 2 satisify Query 1 as well? – Jim G. Aug 5 '16 at 14:00
  • @JimG. Like I say above, Option 2 satisfies query 2 only – Mark Sinkinson Aug 5 '16 at 14:02
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
  • Please see my update. As a constraint, I can't change visit_id from a uniqueidentifier to an int. – Jim G. Aug 5 '16 at 14:03
  • OK. Keep it as uniqueidentifier, but everything else is the same. – Slava Murygin Aug 5 '16 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

  • 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 – Scott Hodgin Aug 5 '16 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. – ypercubeᵀᴹ Aug 5 '16 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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