SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Analyzer Tricks


Query Analyzer Tricks

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84536 Visits: 19224
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
My Blog: www.voiceofthedba.com
Bryan McElhenney
Bryan McElhenney
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84536 Visits: 19224
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
My Blog: www.voiceofthedba.com
simon78
simon78
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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



Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84536 Visits: 19224
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
My Blog: www.voiceofthedba.com
jwr4
jwr4
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 63
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



Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84536 Visits: 19224
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
My Blog: www.voiceofthedba.com
jwr4
jwr4
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 63
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



sushila
sushila
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4793 Visits: 639
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 !!!**
sushila
sushila
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4793 Visits: 639
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 !!!**
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search