CODEWITHSUNDEEP
sql-serverindexing
Didier Levy
Didier Levy

Reputation: 3453

SQLServer indexing multiple columns for one result

I have a large table (100000+ rows) in which are 4 columns of same conceptual value.

Say a record represents a competition between players whose names are in columns A, B, C and D. And A + B plays vs C + D.

When I query that table for all competitions played by a guy named "DOE", I need to inspect the 4 column values for the name:

... Where (Column A like 'DOE%' OR Column B like 'DOE%' OR Column C like 'DOE%' OR Column D like 'DOE%') AND ... Other conditions...

And since the table is huge, I need a way to index on all those 4 columns.

I know I can create a secondary table storing each player name together with his position A, B, C or D and the main table's RowID, but I suspect there should be a better way...

Any clue from a SQLServer guru?

Upvotes: 1

Views: 290

Answers (3)

Ken Downs
Ken Downs

Reputation: 4827

"I know I can create a secondary table storing each player name together
 with his position A, B, C or D and the main table's RowID, but I suspect
 there should be a better way..."

In a Relational Database this is the better way. Reason is, it makes your application code simpler and your DBA function simpler:

1) Index question? Index on playerID.

2) Query? Select ... from playerGames where playerId = X

3) The current design allows for only one simple query, finding all players in position A, B, C, or D, and this is still easy: Select .... from ... where position='A'

This is why database gurus will often try to point out that normalization is not some chore you do to satisfy some vague theory, but actually normalization makes your entire system simpler.

Upvotes: 1

DForck42
DForck42

Reputation: 20387

I would put an index on each column with the ID as an included column as @ttoni stated, and then make a covering index on the id and include the 4 columns, and then run this query:

declare @string varchar(50)
set @string='Karen'

select
    a.ID
    A,
    B,
    C,
    D
from dbo.Players a
    inner join
    (
        select
            ID
        from dbo.Players
        where A = @string

        union all
        select
            ID
        from dbo.Players
        where B = @string

        union all
        select
            ID
        from dbo.Players
        where C = @string

        union all
        select
            ID
        from dbo.Players
        where D = @string
    ) b
        on a.ID=b.ID

you can switch the equal sign with like and then just add the percent to the end of the variable, it just takes a bit more processing for sql server if you do that.

here's the first index:

CREATE NONCLUSTERED INDEX [IDX_A] ON [dbo].[Players] 
(
    [A] ASC
)
INCLUDE ( [ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

and here's the covering index:

CREATE NONCLUSTERED INDEX [IDX_All] ON [dbo].[Players] 
(
    [ID] ASC
)
INCLUDE ( [A],
[B],
[C],
[D]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Upvotes: 0

TToni
TToni

Reputation: 9391

Just put an index on each column (4 indexes total).

That way the server can query each index and put together the results according to the conditions. This allows for a wide range of queries and is still reasonably fast.

Upvotes: 3

Related Questions