December 20, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/queryanalyzertricks.asp
January 6, 2003 at 12:05 pm
Possibly someone has already commented on this (I can't find such), but the code snipet included in the article has some of its contents in less-than and greater-than signs which makes it unreadable on the browser. You have to view the source to see all of it.
January 6, 2003 at 12:35 pm
January 6, 2003 at 3:34 pm
just a general comment in regards to syntax. You gave the following code snippet as a shortcut for when you are doing table joins:
and p.productkey = oi.productkey
and p.usprice = oi.usprice
and oi.orderid = o.orderid
and o.customerid = c.customerid
My question really has little to do with the article itself but I was under the impression that joining tables in the WHERE clause as appears to be happening here is less efficient than using the JOIN syntax.
Can anyone confirm/deny this?
e.g. is it generally a better idea to use INNER JOIN than joining tables in the WHERE clause?
Thanks in advance
January 6, 2003 at 3:41 pm
Kind of off topic, but yes, you do want to do the joins in the join clause. I was shooting for a simple example, but in reality I'd probably have a snippet of
inner join orderitem oi
on p.productkey = oi.productkey
inner join price p
on p.usprice = oi.usprice
inner join orderstaken o
on oi.orderid = o.orderid
inner join customers c
on o.customerid = c.customerid
Steve Jones
January 6, 2003 at 7:09 pm
Templates are cool and a great timesaver. What I would like to do is alter how QA builds a select query from a table definition<right click>. I want the fields separated by Comma and CRLF for readabliity. I end up doing this by hand all the time and it is annoying me...
jwr4
January 6, 2003 at 10:17 pm
Oh, how I agree. That is annoying. I wish I could get the scripting engine to format differently as well. Don't have a solution other than a custom scripting tool. could post that in an article if you're interested.
Steve Jones
January 9, 2003 at 8:13 pm
quote:
Don't have a solution other than a custom scripting tool.
I find that Word has a very powerful Find and replace, so for big jobs, I paste the query into Word, replace , with ,^p and voila.
jwr4
February 5, 2003 at 12:37 pm
I loved every single tip in this article not having had the chance to use some of them before - I found the "short cuts" really really cool and have already started using them.
A couple of things that I thought I'd mention: The first is that the Query Analyzer has a debugging tool that is really neat to use when you don't want to test something through the application interface - when you display the object browser and right click on a stored procedure you can go to the last option which is "Debug" - enter your parameters if any and take it from there. (You might want to read up on the details on how best to use this debugging tool)
Also, I know many readers actually use scripted files for each database object (splly. those using version control) so if you open a stored procedure in the Query Analyzer window you can use the "Edit" menu for Find, Replace etc.. and then save back the edited file.
**ASCII stupid question, get a stupid ANSI !!!**
February 5, 2003 at 12:54 pm
Forgot to add one more thing - if you right click on a table name on the object browser and open it in the Query Analzyer you can edit the contents in the table - something you CANNOT do when you query the table and get the result set in the lower window.
This has nowhere near the flexibility of editing rows using the Enterprise Manager but is always good to know that the QA does have some editing capability albeit restricted.
**ASCII stupid question, get a stupid ANSI !!!**
February 5, 2003 at 3:31 pm
Thanks for the comment and tips. Glad you liked the article.
Steve Jones
February 6, 2003 at 12:10 pm
Great tips! A little off topic, but I think QA could be a lot better with these simple additions:
Color code variables (@)
Check parentheses for you
Allow editing of query results like EM
An option to turn off results/messages
And here is a tip that I'm always surprised people don't know:
You can double click a syntax error message to go to the line with the problem
February 6, 2003 at 2:14 pm
And intellisense!!!!!
Send suggestions to sqlwish@microsoft.com
Steve Jones
February 6, 2003 at 5:39 pm
not sure what you mean? are you replying to the article or a post?
Steve Jones
February 10, 2003 at 11:43 am
quote:
According to Microsoft,A SQL statement without a where clause will not use indexes, so how can it be faster putting joins in the Where clause?
The manual probably means that a NON-JOIN statement w/o a WHERE clause won't use indexes. If you specify a join, you are implicitly requiring the equivalent of a WHERE comparison, even though the word "WHERE" doesn't appear in the syntax.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply