SQL Multiple query hints

[dkaplan]

Is it possible to include more than one hint in a query?

If so, what is the syntax?

When I try
select * from a inner join b (index = name1,name2)
I get an error

 

[vbkris]

why would you want to do that?

if you want to include more than one column in the filter clauses have a look at covering indexes...

Known is handfull, Unknown is worldfull

 

[dkaplan]

At this point this is something between a test and a learning exercise.

The query I am using uses a udf in the where clause (I'll spare you the reasons) and this seems to prevent SQL from using the indexes. I've managed to add one hint, but haven't been able to use two.

 

[vbkris]

>>The query I am using uses a udf in the where clause (I'll spare you the reasons) and this seems to prevent SQL from using the indexes

try using a covering index. that is the only solution i can see right now.

however sometimes SQL Server will ignore your hints if it feels that it can hinder performance. Since your clause uses a UDF it is not SARGABLE.

i suggest that you set this hint only if you are very sure that the index will be the fastest way to pull out such data...

Known is handfull, Unknown is worldfull

 

[Crowley16]

quite often hints can be avoided by just structuring your queries in a different way.

especially look at joins, temp/variable tables and filtering criterias...

only recently, I turned a query which can take a minuite or more to run down to about 3 seconds or less just by changing the order of the table joins...

--------------------
Procrastinate Now!

 

[vbkris]

>> less just by changing the order of the table joins...

hey crowley,

i thoguht the optimiser automatically does this for you???

Known is handfull, Unknown is worldfull