Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,461 through 41,475 (of 59,098 total)

RE: Get index scan on zip code radius query - not sure why.. ! ?

matt6749 (10/19/2009)
Ok thanks Jeff - I am going to try, and try hard, to implement multiple CTEs like you suggest.. (gulp, wish me luck!) - Matt
You'll do fine... just...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 20, 2009 at 5:40 am

#1068100
RE: Get index scan on zip code radius query - not sure why.. ! ?

On second thought, since you need the calculated distance as part of the result set, you may want to include the calculated distance in the first CTE which is limited...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 11:25 pm

#1067962
RE: Get index scan on zip code radius query - not sure why.. ! ?

Actually, it's probably not...
In a CTE, create a result set using the "Narrow Horizontal Corridor" formulas we talked about. Then, in another CTE, do the radius calculations on the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 11:19 pm

#1067958
RE: Get index scan on zip code radius query - not sure why.. ! ?

To be clear, you'll still need to do the radius calculation but the addition of the MAX variables I spoke of should really cut down on the number of complex...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 11:12 pm

#1067952
RE: Get index scan on zip code radius query - not sure why.. ! ?

matt6749 (10/19/2009)
Wow, Jeff, you are the man! I will try to implement the "narrow corridor".. so i should just calculate the Max vars and add "WHERE Lat2 BETWEEN...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 11:04 pm

#1067951
RE: Compare yesterday's data to today's

Shairal-763732 (10/19/2009)
Newbie question...
Our home office overwrites a table every night with new data. I want to know when a new row appears in the table, but if they...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 10:50 pm

#1067949
RE: Selecting data from multiple rows into a single row

mspiegel (10/19/2009)
Bru,
I'm not familiar with the Pivot syntax. Could you provide a sample for me?
Thanks!
Yep... same priciple as in the following...
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 10:46 pm

#1067947
RE: Querying rows by Date with StartDate and EndDate fields (aka What would Jeff Moden do?)

Heh... don't forget the false physicians. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 10:42 pm

#1067944
RE: Good testing practices in SQL server

Heh... at least you're doing SOME testing. A lot of shops that I've done work for generally let the users find the problems for them... we all know how...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 10:14 pm

#1067940
RE: Get index scan on zip code radius query - not sure why.. ! ?

The biggest reason why you're getting a SCAN of any sort instead of a SEEK is because of the forumula itself... it must calculate every zip code to find which...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 10:00 pm

#1067937
RE: Are the posted questions getting worse?

WayneS (10/19/2009)
Hey Jeff... can you FedEx me your biggest, baddest pork chop launcher? I think I'm in dire need of it.....
Just had a "developer" send me some code.
SP "a" has...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 8:50 pm

#1067924
RE: TSQL Question about getting data

Ok, I guess I have a question or two...
If a Case code (like 29119) has two values of CurrentGluedPieces of 0 and 91520 for each day and shift, how do...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 7:51 pm

#1067904
RE: TSQL Question about getting data

Thanks Brad... just got home from work and looking at this now...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 7:30 pm

#1067895
RE: Discussion: Removing the option to ' SELECT * FROM OBJECT'

I do it the same way... and every step is tested that way as Gail and Gus. The side benefit is that every step is tested.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 7:28 pm

#1067893
RE: Are the posted questions getting worse?

Steve Jones - Editor (10/19/2009)
Bob Hovious 24601 (10/18/2009)
Where'd you get the hat? I have to make do with a colander reinforced with tin foil.
I think we'd like pictures...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

October 19, 2009 at 7:15 pm

#1067888

Viewing 15 posts - 41,461 through 41,475 (of 59,098 total)