SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BETWEEN


BETWEEN

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38849 Visits: 38508
VALEK (6/11/2008)
Lynn Pettis (6/11/2008)
To paraphrase what someone else once said on this site, "The proof is in the code." Here is the code:


...
while @LoopCnt < 100
...


Based on the results, the answer is still NO.
Cool


Replace the code above wi th the following and comeback to me us the result (if you are still alive)


...
while @LoopCnt < 1000000000000000000000000000000000000000000000
...



Don't forget to write another script to analyse the result, since the output will be quite long.


It is in your court now. Sorry, but even if the two queries come back once with the same results, the answer is still NO. The reason is consistently return the same results. If BETWEEN 3 AND 5 was the same as BETWEEN 5 AND 3, the results of each run would have been consistant between the two queries.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
VALEK
VALEK
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 Visits: 462

It is in your court now. Sorry, but even if the two queries come back once with the same results, the answer is still NO. The reason is consistently return the same results. If BETWEEN 3 AND 5 was the same as BETWEEN 5 AND 3, the results of each run would have been consistant between the two queries.

Cool

Don't forget the third option: NOT KNOWN.
We all know that the question was about BETWEEN, so why the hell did they use RAND() and some dodgy table?
I would phrase the question like this: do BETWEEN a AND b and BETWEEN b AND a in the WHERE clause have the same effect? YES/NO/UNKNOWN.

Let's put a fullstop on this. Everyone knows now the mechanics of BETWEEN and difference between BETWEEN a AND b and BETWEEN b AND a. The problem is that for me as a mathematician and a programmer, the original question contained redundant and confusing irrelevant information. Nevertheless I answered the question correctly since I guessed the purpose and ignored the dodgyness of the testbed. Though I decided to raise the conversation for the conversation sake so that everyone knows that not all questions comes clear to everyone as it does to the person asking the question. Thats all.

over

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84349 Visits: 41061
... and that's why I hate multiple-guess questions... gimme a good solid essay question or lemme write some code, instead!

That was a fun conversation to watch... thanks, Gents. Smile

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
majorbloodnock
majorbloodnock
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1429 Visits: 3062
VALEK (6/11/2008)
Let's put a fullstop on this.


Several people tried to do that a couple of pages ago, but you still wanted the last word. It was on page 4 of this thread that Lynn first said "I think we're going to have to agree to disagree".

Everyone knows now the mechanics of BETWEEN and difference between BETWEEN a AND b and BETWEEN b AND a. The problem is that for me as a mathematician and a programmer, the original question contained redundant and confusing irrelevant information. Nevertheless I answered the question correctly since I guessed the purpose and ignored the dodgyness of the testbed. Though I decided to raise the conversation for the conversation sake so that everyone knows that not all questions comes clear to everyone as it does to the person asking the question. Thats all.

over


Is this still part of the "fullstop", or shall we call it a postscript? An appendix, perhaps?

Semper in excretia, sumus solum profundum variat
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12083 Visits: 18567
Wow!~ my own mini religious war! I have to say I'm impressed with how riled up we've managed to get....

Anyway - like Steve has mentioned before - it might not be so easy as you think, so perhaps help bring up the quality of the questions by posting some of your own. Trying to ask something with a reasonable setup without necessarily shouting out the correct answer takes a bit of tuning, and I seem to have been a bit off-key on this one.

I do think there's no doubt it could have been worded more clearly, so I can take these comments to mind for any other questions I might put out there.

Anyway thanks! and I look forward to your collective contributions!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38849 Visits: 38508
Matt, I think to characterize this as a religious war is a little dramatic. I'd simply call it a strong difference of opinion.

As I said earlier, I'll agree to disagree. It was a good question, and I would not classify it as a trick question. It tested you ones knowledge of the BETWEEN clause as implemented in T-SQL.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12083 Visits: 18567
Oh fair enough Lynn. I just haven't been getting notified about posts, so coming back to find having ballooned out to 60 or so posts was astounding. But you're right - we're not talking NULLs handling or Identity fields, this is much milder....

Still amazing how "lively" the debate gets to beSmile

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Nick W*
Nick W*
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 180
Hmm.. Definitely open to interpretation.

"Would statements #1 and #2 consistently return the same result set?"

Yes, they would... Every time I run it, they consistently give me the same result set. The second one would just contain a zero.

Needs re-writing or removal, methinks.
kristen-711568
kristen-711568
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 46
I'm with you on this one! The wording on the question was ambiguous and I interpreted it in a way that the writer did not intend...

Kristen Hodges
b. http://bi-tch.blogspot.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84349 Visits: 41061
Lynn Pettis (6/13/2008)
I'd simply call it a strong difference of opinion.


Heh... isn't that the way religious wars get started. Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search