Typically, you'd want columns used in the WHERE condition to be in the index keys, for the reasons noted by Mr. Gale.

select *from SaleOrderLines where OrderID='value1' and itemid=value2' and status='open'

However,...

Yeah, sorry, adjust all the "d." from the query.  You didn't provide any usable sample data for us to test with, so you can't expect fully tested code :-).

            
November 27, 2020 at 3:12 pm
            
#3814475

            
            
                                                
        

You can use a single table scan / lookup and will often get better performance from doing so:

select t.*, s.Description as SubTypeCD_DESC, 
    d.AOD_METHOD_DESC,...

As a bit of a sidebar, your WHERE clauses for the start (>=) and end dates (<) are CORRECT.  Your calculation for end date is not.  You could miss...

For best overall performance, it's extremely likely that you need to change the clustering index on the table to be ( OrderID, ItemID ).  Especially if the table is now...

This is the main test q I use to test query-writing skill.  It's not as trivial / obvious as it sounds at first.

A table contains 1, 2 or 3 rows...

• This reply was modified 5 years, 3 months ago by ScottPletcher. Reason: Reworded the description of the table to make it clearer

You guys must be suffering from Covid or something.  You guys know better than to banter about with claims of performance without demonstrative code to prove your position. Especially...

Yes, you can drop IDX1; IDX12 can cover all queries that would use IDX1.

Sure, you can do that.  And you should do that.

You could also move those columns (attributes) into the main table, but it's often useful to have a separate 1-1 table...

No, you'd use table DML triggers to handle table modifications (DELETE  and/or INSERT and/or UPDATE).

  IF (
        (
            (ABS(SIGN(@Var1-@VarA)))...

Yep, that is...

I would think that would be possible.

Can you bring that filegroup back online and REMOVE it?  I  think that would clear it up, if that was the issue.

The code given with the /15 and *15 integer math is to figure out the 15-minute time period that contains the current time.  As I write this, it is...

There must be some active transaction (or replication or other task that needs the log records).

What does DBCC OPENTRAN on that db show?