Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Query Analyzer Tricks Expand / Collapse
Author
Message
Posted Friday, December 20, 2002 12:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/queryanalyzertricks.asp






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #8942
Posted Monday, January 6, 2003 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 26, 2009 3:16 PM
Points: 39, Visits: 11
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.


Post #50096
Posted Monday, January 6, 2003 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
Yikes, will take a look at this.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #50097
Posted Monday, January 6, 2003 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 19, 2004 12:30 AM
Points: 6, Visits: 1
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



Post #50098
Posted Monday, January 6, 2003 3:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #50099
Posted Monday, January 6, 2003 7:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:19 AM
Points: 3, Visits: 60
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



Post #50100
Posted Monday, January 6, 2003 10:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #50101
Posted Thursday, January 9, 2003 8:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:19 AM
Points: 3, Visits: 60
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




Post #50102
Posted Wednesday, February 5, 2003 12:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:37 AM
Points: 2,553, Visits: 567
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 !!!**
Post #50103
Posted Wednesday, February 5, 2003 12:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:37 AM
Points: 2,553, Visits: 567
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 !!!**
Post #50104
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse