Update Query and Joins in EM vs. Query Analyser
-
Why is it that I can create a SELECT query in Enterprise Manager with joins but not an UPDATE query? EM won't let me add any extra tables when I am building an UPDATE query. Am I missing something? I always end up building the UPDATE queries by scratch in Query Analyzer instead and it just seems like a waste of time. This is so opposite from MS Access.
All the best,
Dale
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Dale. You are right. Although I have never myself actually used the Query Builder in EM to build a query I did confirm that the Change Type -> Update removes the Add Table option from the context menu. I guess the designers found it too difficult to implement
. As for Access, I would say that EVERYTHING in SQL Server is opposite to Access, and, IMHO, for good reason.... -
Well, I'll be honest with you. When I'm creating complex queries in MS Access, I feel much more secure that my syntax is correct by using Query By Example. Besides, typing in attributes, table names, etc. are eliminated leaving less room for error. There are just times that I find it to be more accurate for "typo" errors.
Edited by - DALEC on 07/24/2003 08:33:35 AM
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Agreed. MS Access has some great tools to enable quick query building. But here's one for you: ever tried scraping the SQL out of Access' SQL view (clicking SQL button in toolbar) and then trying to convert that to T-SQL? It's a major PITA since Access has its own variation of SQL called Jet-SQL.
-
Ha! Ha! You got me laughing on that one. Yes, I have and it's like scratching fingernails on a chalk board.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Hi Dale,
quote:
Well, I'll be honest with you. When I'm creating complex queries in MS Access, I feel much more secure that my syntax is correct by using Query By Example. Besides, typing in attributes, table names, etc. are eliminated leaving less room for error. There are just times that I find it to be more accurate for "typo" errors.yes, the Query Editor in Access is really good! But sometimes makes things too easy.
Maybe the SQL Server developers take a lesson from this. I guess they have already, when designing the SQL2K EM table designer. Looks pretty similar.
If you feel more secure with Access why not use the Upsizing wizard after creating a query?. It's one step ahead, two steps back
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oh, jeez, Frank. Now you're giving me a headache!
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
quote:
Oh, jeez, Frank. Now you're giving me a headache!ohoh, I guess it's better for me to go home now
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
No, seriously folks. I did a research paper on the guy who came up with Query By Example in the 70's, Dr. Mosh Zloof of IBM, and there are some really good points about it.
1.) Visually seeing the relationships and joins.
2.) Elimination of typo errors.
3.) Eliminates reference ambiguity.
I don't mind keying in a complex query, but a nice visual tool with the relationships/joined mapped out and the elimination of reference ambituity would make me feel a little better before I hit the Execute Query button. Sometimes the Parse Query button does not exactly give me that warm fuzzy feeling but it helps.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
You can stay, Frank. I just get queezy when I see the words "Upsizing Wizard".
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Here's one argument against it, for devil's advocate sake. Sometimes, when doing performance tuning there are multiple ways to refine a particular query that can lead to different execution plans. In Query By Example, how would these VERY different statements be represented? :
-- LEFT JOIN syntax will do a
-- MERGE JOIN if indexed properly
SELECT Column1 FROM Table1
LEFT JOIN Table 2 ON Table1.PK = Table2.FK
WHERE Table2.FK IS NULL
-- SUBSELECT syntax will do
-- a NESTED LOOP JOIN if indexed properly
SELECT Column1 FROM Table1
WHERE Table1.PK IN (SELECT Table2.FK FROM Table2)
-- EXISTS syntax will also do
-- a NESTED LOOP JOIN or better JOIN if
-- inedexed properly
SELECT Column1 FROM Table1
WHERE EXISTS (SELECT * FROM Table2 WHERE Table2.FK = Table1.PK)The point is, Query By Example won't help identify performance gains from slight syntax variations that Query Analyzer coding will.
-
Good point. In that example, I agree. However, for example, if I'm doing a Right Outer Join between two tables (or more) that have multiple columns involved, I'd choose QBE anyday over having to key in all the column names. Call me lazy!
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Another query I hate to key in is INSERT INTO especially if there are different column names involved.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
See SQL2K QA's INSERT INTO template...
-
I could only find a template for an INSERT INTO TRIGGER in QA. Not really what I meant before but thanks.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
. As for Access, I would say that EVERYTHING in SQL Server is opposite to Access, and, IMHO, for good reason....
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply