Post reply

Very basic indexing question

  • June 9, 2020 at 8:21 am

    #3761580

    I have a table below:

    CREATE TABLE [dbo].[PropertyProduct](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Warehouse] [char](2) NOT NULL,
    [Product] [char](20) NOT NULL,
    [PropertyId] [int] NOT NULL,
    [Value] [nvarchar](200) NOT NULL,
     CONSTRAINT [PK_PropertyProduct] PRIMARY KEY CLUSTERED 
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    It contains about 100,000 rows and is likely to grow (but by manual upserts rather than automated processes).

    I have a web application that retrieves data based on warehouse and product (it's actually LINQ but the SQL is essentially:

    SELECT
        *
    FROM
        PropertyProduct
    WHERE
        ((Warehouse = '00') AND
        (Product = 'Widget2'));

    My naive question is - how should I index this table, if at all?

    You will have noticed that I am NOT a DBA!

    Thanks

    Edward

  • June 9, 2020 at 1:13 pm

    #3761648

    You probably want a covering index like:

    CREATE NONCLUSTERED INDEX ncix_PropertyProduct_Cover ON dbo.PropertyProduct
    (
    Warehouse ASC,
    Product ASC
    )
    INCLUDE(Id,PropertyId,Value);

  • June 9, 2020 at 4:09 pm

    #3761718

    Thanks Rick - that's perfect!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply