CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Catalin
Catalin

Reputation: 11721

Is it faster to filter by multiple columns in SQL Server?

I have a table which has the following columns

Id         (int)
UniqueId   (string)
CategoryId (int)

Id and UniqueId are unique for each record.

I want to find a record based on UniqueId value, and i also have the CategoryId value.

Is it faster to specify both filters in the SELECT statement, for example:

SELECT * FROM [Products] Where [UniqueId] = 'product' AND [CategoryId] = 4

Or should i just use the [UniqueId] filter?

The table has a lot of records

EDIT :

I don't have the [Id] column of the product

Upvotes: 0

Views: 1142

Answers (7)

Joe G Joseph
Joe G Joseph

Reputation: 24086

If you do not have any indexes, then 

SELECT * 
FROM [Products] 
WHERE [UniqueId] = 'product'

If you have an index on any of the columns, then use that column in the where clause.

If you have index on both UniqueId and CategoryId then your query

SELECT * 
FROM [Products] 
WHERE [UniqueId] = 'product' AND [CategoryId] = 4

is fine

Upvotes: 0

Apocalyp5e
Apocalyp5e

Reputation: 181

I think its better to use unique id filter that will do the thing, also don't forget to put an index on UniqueId to make your filtering faster.

I prefer putting index on the columns i want to filter(used in where clause).

Upvotes: 2

Manish Prajapati
Manish Prajapati

Reputation: 585

First of all

pull only the columns that you want to pull

I believe, you have [CategoryId] has cluster index. So you should pull record using filter [CategoryId]. Don't use Product filter

you query would be 

SELECT * FROM [Products] Where  [CategoryId] = 4

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166476

That will depend on if you have an index on the table, and which columns the index include, and the actual order of the index.

if you had an index with columns (UniqueId, CategoryId) with the appropriate included columns set, your query should be fine.

Adding the other required columns in the included columns will then avoid a key lookup to retrieve the columns in your select.

That is also why you should almost never use SELECT *

Your best option would be to look at the Execution Plan, and try avoiding any table scans/key lookups

Have a look at something like Execution Plan Basics

Upvotes: 2

Tony Basile
Tony Basile

Reputation: 419

Just filter on Uniqueid. SQL server won't even look at the other columns, because a unique index provides the fastest sort (O=log2N)

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

I suggest,Just create a non-clustered index on uniqueid and just use that field as filter

Upvotes: 0

Arno 2501
Arno 2501

Reputation: 9407

I think it's faster to filter on which column is indexed.

And if they are both indexed I think it's faster to filter on only one column because for each column filter the server has to do processing that will take time.

Upvotes: 0

Related Questions