Help with Old Syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Old Syntax

Author

Topic

mfemenel
Professor Frink

1421 Posts

Posted - 2003-12-18 : 09:37:10

I need some help gathering reasons why not to write things a certain way. The guys here where I started working do all ot their joins this way:

select * from
tblafassignment ta,tblpmshop tps
where tps.assignmentid=ta.assignmentid

Sometimes, I even see a few *= and *=, which literally, give me a headache. We're getting ready to start new development and I'm trying to make the case to them that they should be specifying their joins in what I consider the "right way" such as:

select * from
tblafassignment ta
inner join tblpmshop tps on ta.assignmentid=tps.assignmentid

My arguments so far are as follows:
1. The first version may not be supported in future versions
2. The optimizer goes through joins first, then where clauses, the having statements. Burying their join in a where clauses costs it time getting down to that level
3. Using =* and *= can lead to ambiguity in the query.
4. My way is Microsofts preferred method. Not that that usually means much, but I'd bet just about everything they've built is written to support that kind of join syntax.

What else can we think of here so when the time comes I have what I need to beat them over the head and get them to do it the right way?

Mike
"oh, that monkey is going to pay"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-18 : 10:28:53

Hi Prof.

Here is another couple of points you might want to add to your list.
There is no CROSS JOIN in T-SQL so if you make a mistake and have something like :


SELECT a.col1, a.col2, b.col1, b.col2, b.col3
FROM tbla a, tblb b

you'll get loads of rows if your two tables are sufficiently big, but this could only be a mistake of course as you might have left out the WHERE clause or you only highlighted up to the FROM clause

ANSI syntax you have to use the words CROSS JOIN.

Also, the T-SQL syntax has problems with NULLs since the WHERE clause acts as the join and the filter for the query while ANSI syntax the WHERE clause is reserved for filtering your data and so your =*, = and *= are replaced with JOIN, LEFT JOIN etc

[CODE]
CREATE TABLE TBLA (MYID INT, COL2 INT, COL3 INT)
INSERT INTO TBLA VALUES (1, 3, 3)
INSERT INTO TBLA VALUES (2, 23, 1978)
INSERT INTO TBLA VALUES (3, 25, 8)
INSERT INTO TBLA VALUES (4, 1124, 645)

CREATE TABLE TBLB (MYID INT, COL4 INT, COL5 INT)
INSERT INTO TBLB VALUES (3, 25, 8)
INSERT INTO TBLB VALUES (4, 1124, NULL)

SELECT A.COL2, A.COL3, B.COL4, B.COL5
FROM TBLA A,
TBLB B
WHERE A.MYID *= B.MYID
AND COL5 IS NULL

SELECT A.COL2, A.COL3, B.COL4, B.COL5
FROM TBLA A
LEFT OUTER JOIN TBLB B
ON A.MYID = B.MYID
WHERE B.COL5 IS NULL
[/CODE]

________________
Make love not war!

mfemenel
Professor Frink

1421 Posts

Posted - 2003-12-29 : 21:58:19

Anyone else have any suggestions. I was told today that I will use their syntax in new development. This is right after they said "when will you learn sometimes cursors are necessary?"

Needless to say I can use all the help I can get. SOS!

Mike
"oh, that monkey is going to pay"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-29 : 22:49:40

Expanding on point 3 of your arguments, (ambiquities occur when performing OUTER JOINS). The order of the terms in the WHERE clause is significant when constructing multi-level join using the legacy syntax. It is because of this ambiquity - whether the ordering of the WHERE clause predicates is significant (ie predicates in the WHERE clause are evaluated together) - that the SQL-92 standard moved join construction to the FROM clause.

Another point is interoperability (ie. Heterogeneous joins, passthrough queries, and vendor-to-vendor replication)

To quote Ken Henderson...

"It just makes sense to abandon proprietary syntax elements in favor of those that play well with others..."

http://www.developer.com/db/print.php/10920_2202631_6

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-12-29 : 23:07:51

I'd like to add another comment on here as well. This goes along with what ehorn was saying. If you use non-standard syntax, it might be hard to find people to use said syntax. You might turn off a really good developer due to the lack of following the reccomended standards.

Your #4 point is a big one too. If you want to upgrade to Yukon down the road, you might have to touch every piece of t-sql to convert it to the SQL-92 way (I'm not sure if they are going to support *= and =*). I know I've got a rather small database with about 1000 stored procs. If I had to go back and touch each one and re-test them, I think I'd find the closest bridge and jump. Think how many man hours it would take to touch and retest all of your T-sql.

Put a dollar figure on doing it right the first time vs fixing it down the road. Managers always seem to understand money.

<Yoda>Think long term you must. Sleep well you will.</Yoda>

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mfemenel
Professor Frink

1421 Posts

Posted - 2003-12-30 : 07:38:11

Thanks all. MichaelP, that's a great article, I think that along with an excerpt from Inside SQL Server and Ken's book will lend some more credability to what I'm saying. This killed me:

If you use non-standard syntax, it might be hard to find people to use said syntax. You might turn off a really good developer due to the lack of following the reccomended standards.

That's exactly the problem I'm having right now,only I'm the developer! I know what I'm talking about here but everyone else seems stuck in the old way. I shouldn't even have to spend the time arguing about this. I'm in a sea of crazy people who are making me think I'm crazy because I'm sane!(relatively speaking of course)

Mike
"oh, that monkey is going to pay"

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-30 : 09:27:14

Mike, Mike, Mike....

Can't you do it the ANSI way, and let them do it there way?

Let them get stuck and then come to the rescue...

A consultant friend once said to me...

Chaos means Money....

Unless you're an exempt employee, then it means hours of free time...

But you could always play dumb, because it's not your mess...

Brett

8-)

mfemenel
Professor Frink

1421 Posts

Posted - 2003-12-30 : 11:19:49

No, I don't have that option. They want us to do things all 1 way so we don't have mixed code. Unfortunately they're choosing their way without knowing what they're talking about. I'm exempt or I'd love the extra money. I'm just gathering my info, articles, book excerpts etc and will present it to them and let them make their choice. If they drive off the cliff after that, I can't do anything more than what I've done to try to show them. I'm thinking they just need to learn the hard way. I feel like the only person who's saying the earth is round and they think I'm crazy because they're convinced it's flat

Mike
"oh, that monkey is going to pay"

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-30 : 11:28:03

Well...

[good luck]

Those guys/gals must be some pretty hard core 4.x developers....

Did you mention what platform you're on...2k?

If so, they must be amazed that they don't get the blue screen of death all the time....

I've only seen 1 2k db that went suspect...and it repaired itself...

[/good luck]

And stay away from any high bridges...what floor do you work on?

Brett

8-)

mfemenel
Professor Frink

1421 Posts

Posted - 2003-12-30 : 12:15:41

Yeah, we're on SQL2K..... Damn 2nd floor, I'd just end up with a few days off. Not worth it, I'd have to go to job interviews on crutches.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources