Quiz: Nonclustered Rowstore Index Examples – Part 1

Quiz: Rowstore Nonclustered Index Examples

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

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)

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

CREATE INDEX [email protected]_rates_ratedby_includes on dbo.[@dog_rates] (ratedby)
    INCLUDE (ratingoutof10, doggo, dogid);
  • SELECT ratedby
    FROM dbo.[@dog_rates] 
    WHERE doggo = 'Mister';
  • SELECT ratedby
    FROM dbo.[@dog_rates] 
    WHERE ratedby = N'Mister';
  • SELECT ratedby
    FROM dbo.[@dog_rates] 
    WHERE ratedby = doggo;
  • SELECT ratedby, ratingoutof10
    FROM dbo.[@dog_rates] 
    WHERE ratedby = 'Kendar';;;;;;

7 thoughts on “Quiz: Nonclustered Rowstore Index Examples – Part 1

  1. I think question 3 is missing some quote marks around doggo

    1. Great question. doggo is a column name in this case, not a literal — it could be [doggo], but it’s not meant to be ‘doggo’

      So the comparison in question 3 is between two columns.

  2. I love these quizzes.
    Just as you think you know a little, you realize you know a little less than you thought!
    Thanks, keep them going.

    1. Ha, that’s one of my favorite feelings, and often the quiz questions come from me realizing I didn’t know something!

  3. I enjoyed that Kendra, thanks a lot! Good to get the ol’ brain going πŸ™‚

    1. Yay! Writing the quizzes does the same thing for me.

  4. I made a mistake in Q3 thinking that the comparison would be performed on the same row.

    Great Kendra Little, great quiz!
    Excellente. πŸ™‚

Leave a Reply

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