Query Analyzer Tricks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Steve Jones » Query Analyzer Tricks

55 posts, Page 1 of 6

1 2 3 4 5 »»»

Query Analyzer Tricks

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Friday, December 20, 2002 12:00 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

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

Bryan McElhenney

Posted Monday, January 06, 2003 12:05 PM

SSC 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

Steve Jones - SSC Editor

Posted Monday, January 06, 2003 12:35 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

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

simon78

Posted Monday, January 06, 2003 3:34 PM

Forum 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

Steve Jones - SSC Editor

Posted Monday, January 06, 2003 3:41 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

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

jwr4

Posted Monday, January 06, 2003 7:09 PM

Forum 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

Steve Jones - SSC Editor

Posted Monday, January 06, 2003 10:17 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

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

jwr4

Posted Thursday, January 09, 2003 8:13 PM

Forum 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

sushila

Posted Wednesday, February 05, 2003 12:37 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:44 AM
Points: 2,553, Visits: 513

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

sushila

Posted Wednesday, February 05, 2003 12:54 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:44 AM
Points: 2,553, Visits: 513

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 »

55 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions