Posted by Hugo Kornelis on 12/11/05 23:17

On Sun, 11 Dec 2005 20:31:37 +0100, stig wrote:

>hi.
>coming from postgresql, i am used to textual references to most of the
>things i do with the database. i feel a little lost with all the graphical.

Hi Stig,

Answers inline.


>i have few questions regarding MS SQL 2000
>1. what is the best (or easiest) way of getting a table definition in text?
>it could be either a CREATE TABLE sql-query or a just a definition,
>something like:
>TABLE thisTable
>id integer
>value varchar(10)
>etc. etc.

John Bell already addressed this question.


>2a. how do i get a query plan and how do i get it in text.

To get an execution plan in text, add
SET SHOWPLAN_TEXT ON
go
before your query.
When using Query Analyzer, you can also use either Query | Current
connection properties (for one connection) or Tools | Options |
Connection properties (to change the defaults) and add a tick before the
"Set showplan_text" option.

>2b. are there planner modes that show more or less of what actually
>happened, verbose mode perhaps?

I personally prefer the graphical version offered by Query Analyzer over
the text version offered by SET SHOWPLAN_TEXT ON, but both carry
essentially the same information.

>2c. if i ask for a query plan, will SQL server actually run the query or
>will it only produce a plan. if the query is run, does it commit or
>rollback by default?

In QA, you can use Query | Display Estimated Execution Plan (Ctrl-L) to
get an estimate of the query plan without running the query, or you can
get the real execution plan, including statistics on actually processed
numbers of rows, by running the query with the option Query | Show
Execution Plan (Ctrl-K) turned on - this will actually run the query.

If you use the SET SHOWPLAN_TEXT ON option, the query will actualy be
run, unless you also activate the fmtonly option with the commands
SET FMTONLY ON
go


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]