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];
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_includes on dbo.[@dog_rates] (ratedby)
    INCLUDE (ratingoutof10, doggo, dogid);
GO

  • 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. Greg

    I think question 3 is missing some quote marks around doggo

    Reply
    1. Kendra Little

      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.

      Reply
  2. Marcin

    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.

    Reply
    1. Kendra Little

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

      Reply
  3. Aimee

    I enjoyed that Kendra, thanks a lot! Good to get the ol’ brain going 🙂

    Reply
    1. Kendra Little

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

      Reply
  4. Milan

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

    Great Kendra Little, great quiz!
    Excellente. 🙂

    Reply

Leave a Reply

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