January 26, 2011 at 12:22 pm
I think I am looking for information about the internals of the "Query Designer" in SQL Server Management Studio. I have been using the "Query Designer" to convert "WHERE-clause joins" to "FROM-clause joins" for a long time. I have always verified the resulting query manually but have never validated its methods. It has not failed to come up with a working, equivalent query, for me yet but now I am curious enought to pursue finding out what it is doing behind the scenes.
If you have not used SSMS in this way it is very useful. Here is how you can see it working:
1. Right click any table in "Object Explorer" and choose "Edit Top ### Rows".
2. Press Ctrl+3 to open the "SQL Pane" (or use the menu item "Query Designer" > Pane > "SQL Pane").
3. Paste this into the "SQL Pane":
SELECT *
FROM sys.tables t,
sys.columns c
WHERE t.object_id = c.object_id
4. Now choose "Verify SQL Syntax" from the "Query Designer" menu to see the magic.
SELECT t.name, t.object_id, t.principal_id, t.schema_id, t.parent_object_id, t.type, t.type_desc, t.create_date, t.modify_date, t.is_ms_shipped, t.is_published,
t.is_schema_published, t.lob_data_space_id, t.filestream_data_space_id, t.max_column_id_used, t.lock_on_bulk_load, t.uses_ansi_nulls,
t.is_replicated, t.has_replication_filter, t.is_merge_published, t.is_sync_tran_subscribed, t.has_unchecked_assembly_data, t.text_in_row_limit,
t.large_value_types_out_of_row, c.object_id AS Expr1, c.name AS Expr2, c.column_id, c.system_type_id, c.user_type_id, c.max_length, c.precision,
c.scale, c.collation_name, c.is_nullable, c.is_ansi_padded, c.is_rowguidcol, c.is_identity, c.is_computed, c.is_filestream, c.is_replicated AS Expr3,
c.is_non_sql_subscribed, c.is_merge_published AS Expr4, c.is_dts_replicated, c.is_xml_document, c.xml_collection_id, c.default_object_id,
c.rule_object_id
FROM sys.tables AS t INNER JOIN
sys.columns AS c ON t.object_id = c.object_id
It converted the "WHERE-clause join" to a "FROM-clause join". I assume this involves a request to the server since it is also expanding the * to an explicit column list however I have never verified this by running a trace for server activity....maybe I will do that 🙂
Does anyone know off the top if a server or client operation is happening? And also which API(s) and object(s) are being using to do this?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 26, 2011 at 12:43 pm
It's both sides. It has to query the metadata from the server to get the column names, then it runs through a client-side operation to rewrite the query and format it.
There are 3rd party products that do this (and more). Red Gate has one. ApexSQL has one. Might be others, but those are the two I've used. Both work very well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2011 at 1:56 pm
I have heard of it but have not tried ApexSQL (and their website won't allow me to register for a trial period with my gmail account...lame). Do you know if it does query rewriting as I have described?
I use SSMS and RedGate SQL Prompt Pro. While RedGate SQL Prmopt Pro has really great code formatting features and can expand * into an explicit column list it does not do query rewriting as I have described.
Do you have further information about the Query Designer's "client-side operation" to which you referred?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 27, 2011 at 6:35 am
On the question about Query Designer, no. Microsoft pretty routinely hides their code.
It wouldn't be that hard to take an execution plan and turn it into a query in VB.NET. Ask Red Gate about a feature that does what you want. If they can't, a clever .NET dev probably can.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply