Alex,

Nice article especially for the proverbial "newbie".  I do agree that the examples were a bit overdone but how are ya going to learn if you don't try?

I've found that that is NOT true... not with INT ID's anyway... In my testing of the various solutions posted against this article, I get INDEX SCAN's, not table scans using WHERE (CustID...

Sorry... it's a "Moden-ism" pronounced "ree-bar" and stands for "Row By Agonizing Row"    Why "ree-bar"?  Because it's like the metal stakes stuck in...

Thanks for the feedback, Steve.

p.s. You would be amazed at just how useful that Tally table is gonna be...  and, a lot of the time, it will greatly enhance the performance of your code......

Aye... and thank you for the feedback, Perry.

p.s.  The above was from your original query... it does not include the change due to the "blond-attack"

Ugh!  Correlated Sub-Queries = Instant RBAR.  Not good... real performance killer.  Should be a derived table.  WHERE IN... not good... should be an equi-join (inner join).

Haven't tested it but I'm...

I don't use debuggers in SQL Server... just the ol' Mil Spec Mark I Mod I Eyeball.  Probably not what you wanted to hear, though...

What are you trying to debug...

I loved the examples... nice and simple.  Great job, DC.

Yep... this is about 3 times faster (I tested both)...

 CREATE FUNCTION dbo.GetDOWCount

        (

        @StartDate DATETIME,

        @EndDate DATETIME,

        @DOW VARCHAR(9) --Monday, Tuesday, Wednesday, etc

        )

RETURNS INT

     AS

  BEGIN

 RETURN (SELECT...

Actually, a function would slow stuff down here... you want the full table to be split... why do it a line at a time?  Try this, instead...

--==================================================================================================

--      This...

For what?  Inserts, Updates, Deletes, or Selects?

Try something like this, instead...

SELECT

locationId, locationId AS original_locationId, locationCode, description, divisionCode, companyId

FROM locations

ORDER BY companyId,

CASE

WHEN locationCode NOT LIKE '%[^0-9]%' THEN STR(locationcode,10)

ELSE locationcode

END,

locationCode

ISNUMERIC does NOT mean IS ALL DIGITS... you're making a BIG mistake using it for that.  If you don't think so, try this...

SELECT NULL AS [ASCII#],'12D45' AS [Character(s)],ISNUMERIC('12D45')...