Quiz: Nonclustered Rowstore Index Examples – Part 2

Quiz: Rowstore Nonclustered Index Examples - Part II

These questions are based on the following schema and data:
DROP TABLE IF EXISTS dbo.[@dog_rates];
GO

CREATE TABLE dbo.[@dog_rates] (
    dogid int identity not null,
    doggo varchar(128) not null,
    ratingoutof10 int not null,
    ratedby varchar(128) not null,
    constraint [email protected]_rates primary key (dogid)
);
GO

INSERT dbo.[@dog_rates] (doggo, ratingoutof10, ratedby) 
VALUES 
    ('Stormy', 14, 'Kendar'),
    ('Stormy', 13, 'Fletcher'),
    ('Fletcher', 14, 'Kendar'),
    ('Mister', 14, 'Mister'),
    ('Mister', 14, 'Kendar');
GO

CREATE INDEX [email protected]_rates_ratedby_doggo_includes on dbo.[@dog_rates] (ratedby, doggo)
    INCLUDE (ratingoutof10, dogid);
GO
  • SELECT COUNT(*) as ct
    FROM dbo.[@dog_rates] 
    WHERE doggo = 'Mister' 
    and ratedby = 'Mister';
  • SELECT COUNT(*) as ct
    FROM dbo.[@dog_rates] 
    WHERE ratedby = 'Mister';
  • SELECT COUNT(*) as ct
    FROM dbo.[@dog_rates] 
    WHERE doggo = 'Mister';
  • SELECT ratedby
    FROM dbo.[@dog_rates]
    WHERE ratedby = 'Fletcher' 
    and ratingoutof10 > 1;
Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.