ORDER BY Should be same as my input in IN()

Question

Post reply

ORDER BY Should be same as my input in IN()

Viewing 15 posts - 31 through 45 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Yggaz SSChasing Mays Points: 635 More actions · Answer 1

- Used UPDATE...FROM more than once

I definitely do not have the slightest resembalnce to "SQL Expert", but this phrase punched me hard.

I have no idea why using UPDATE...FROM is bad. Is it? Why?

Of course there is always Google, but I cannot resist the temptation to ask the community. People, you got style.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Yggaz (12/19/2012)
- Used UPDATE...FROM more than once
I definitely do not have the slightest resembalnce to "SQL Expert", but this phrase punched me hard.
I have no idea why using UPDATE...FROM is bad. Is it? Why?
Of course there is always Google, but I cannot resist the temptation to ask the community. People, you got style.

There are two main reasons why some take exception to it.

One is that it's SQL Server proprietary and will not port to most other RDBMS engines. That doesn't bother me because my personal experience is that true portability of a lot of useful code is a myth.

Another reason is that it will allow you to make mistakes (just like all other code does). For example, it will allow you to update one row from a randomly selected choice of more than one row in another table. Which row is used is largely unpredictable and the UPDATE FROM gives no warning about this type of thing happening whereas the non-proprietary forms of UPDATE will give you a warning and won't allow such a "mistake" to occur. That doesn't bother me either because I know to look for that type of thing as any good programmer should.

The UPDATE FROM also allows for some extraordinary functionality that would take way too long to explain on a single thread like this. That same functionality frequently can't be done in other RDBMS's because it would be trapped out as an "error".

A lot well meaning people want to get rid of UPDATE FROM because of the reasons above. I'm not one of those and, it would appear from the votes on the related CONNECT item, that the people that actually work with and like the UPDATE FROM grossly outnumber the people that want to get rid of it in favor of MERGE.

--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)

Yggaz SSChasing Mays Points: 635 More actions · Answer 3

Jeff Moden (12/19/2012)
One is that it's SQL Server proprietary and will not port to most other RDBMS engines.
Another reason is that it will allow you to make mistakes (just like all other code does).

Jeff, thank you very much. Now I know more - and now I have an idea where to look for more information. The latter is much more important.

Josh Ashwood SSC Enthusiast Points: 108 More actions · Answer 4

I would hate to see UPDATE... FROM removed. MERGE is so long winded.

The one I am most curious about is the semicolon statement terminator stated by Microsoft to be "required in a future version"

Where does the poor old semi-colon stand in the world of ANSI SQL? Is it legal to use it as a statement terminator, or is the humble semi-colon also proprietary evil?

Can you imagine all the little humble

SELECT Foos

FROM FooBars

statements out there in the wild that would just stop working if Microsoft made the semi-colon a requirement in a future version?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Josh Ashwood (12/20/2012)
Can you imagine all the little humble
SELECT Foos
FROM FooBars
statements out there in the wild that would just stop working if Microsoft made the semi-colon a requirement in a future version?

Can you imagine all the simple

SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y

statements that would just stop working if Microsoft removed the *= in a future version?

It's happened, the future version is 2012. There have been hints for some time that the ; would eventually be required. My suggestion, start using it now, then there will be less code to change in 10, 15 or more years time when (if) the ; finally becomes mandatory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

GilaMonster (12/20/2012)
... My suggestion, start using it now, then there will be less code to change in 10, 15 or more years time when (if) the ; finally becomes mandatory.

Or don't! Let be "SQL ;-problem" in 2025, it may let SQL consultants to cash in as one did prior to 2000 for COBOL ones :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Phil Parkin SSC Guru Points: 246963 More actions · Answer 7

Someone at MS will write a conversion utility for sure if this is implemented. I don't think it's worth worrying about - there is no agreement as to exactly when they should be used, even if we all decided it was a good idea, so it's not going to be consistent until the rules are enforced.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Yggaz (12/19/2012)
- Used UPDATE...FROM more than once
I definitely do not have the slightest resembalnce to "SQL Expert", but this phrase punched me hard.
I have no idea why using UPDATE...FROM is bad. Is it? Why?
Of course there is always Google, but I cannot resist the temptation to ask the community. People, you got style.

Just remember... it's not the fault of the UPDATE statement... it's the fault of people who don't actually know how it behaves when they write code. I will sometimes purposely use it in the "mistake mode" to get certain things done. As I've said about many things, "It's not a fault... it's a FEATURE"!

--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)

Josh Ashwood SSC Enthusiast Points: 108 More actions · Answer 9

GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y

haha *=

Now that is real dinosaur stuff!!

Can you imagine the unspeakable evils Joe has committed in his time before the advent of SQL-92?? :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Josh Ashwood (12/20/2012)
GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y
haha *=
Now that is real dinosaur stuff!!

You'd think so, but I'm still finding it in a SQL 2005 database that's scheduled to go to SQL 2012 next year.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

carlosaamaral Old Hand Points: 325 More actions · Answer 11

Aberration *= e (+)(Oracle), terrible ... *= Dead in SQL2012.....

has happened in a database 2005 (left outer join urgent!) 😀

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

GilaMonster (12/21/2012)
Josh Ashwood (12/20/2012)
GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y
haha *=
Now that is real dinosaur stuff!!
You'd think so, but I'm still finding it in a SQL 2005 database that's scheduled to go to SQL 2012 next year.

Good! It generates more work in a current economic down-turn :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Josh Ashwood (12/20/2012)
GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y
haha *=
Now that is real dinosaur stuff!!
Can you imagine the unspeakable evils Joe has committed in his time before the advent of SQL-92?? :w00t:

BWAAA-HAAA!!!! How about the ones he still does like storing a "year" as '2012-00-00' .

--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)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 14

CELKO (12/21/2012)
Remember all indexes have to be kept constant, since index order matters (totally non-relational approach from 1970's Sybase/UNIX file doing loops). Nobody can alter the tables. If there is a VIEW in it, things get worse. Etc.

What on this good green Earth are you talking about??? Normal updates don't rely on index order. Only "Quirky Updates" rely on that and THAT's NOT the reason why people are trying to get rid of UPDATE FROM nor does it require that "all indexes have to be kept constant".

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Jeff Moden (12/21/2012)
Josh Ashwood (12/20/2012)
GilaMonster (12/20/2012)
Can you imagine all the simple
SELECT Foo FROM Foo, Bar WHERE Foo.X *= Bar.Y
haha *=
Now that is real dinosaur stuff!!
Can you imagine the unspeakable evils Joe has committed in his time before the advent of SQL-92?? :w00t:
BWAAA-HAAA!!!! How about the ones he still does like storing a "year" as '2012-00-00' .

But MySQL supports this and properly sorts it with other valid dates! Ask Mr. Celko, he'll be more than happy to tell you that. Oh what, he does every time someone posts a question with dates involved.