T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

2. T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

Hey Now,

This is the second T-SQL post of four to provide a stellar understanding of database development using T-SQL. We'll discuss the difference between the Query Editor Window vs. Design View. We can accomplish many tasks by using either, its good know now the strengths & limitations of both. The fundamental difference between the two is Query Editor Window is just text & the design view is more of a GUI feel.

1. Design View
   1.a Creating Tables
   1.b Modifying Tables
   1.c Building Queries
2. Query Editor Window
   2,a General
   2.b Execute & Parse
   2.c Highlighting Text
   2.d Scripting

1. Design View - The design view's strength is that it has a relatively nice GUI graphical user interface. This in my opinion is nice especially when a person has less experience developing in T-SQL. Two good times to use the Design view when creating or editing a table or when querying within a single database & building a query (user can click & drag vs. typing specific syntax).

1.a Creating Tables – We can create a table in design view by right clicking the ‘Table’ node in Object Explorer & selecting ‘New Table’. In this view it’s a nice little interface where we can name the columns, select the data types, create keys, and create indexes & much more. When you save the table it will be reflected in the list of tables. It’s a nice way to start building table since its more point & clicky rather than all syntax, text & code.

1.b Editing Tables – We can also edit tables in design view which is a common task. In Object Explorer right click the name of the table & select edit, this will open up the design view so we’re able to alter the name or datatype of a column. We could always use the query editor window and use a script such as ‘Alter Table … lots of code…etc’. but it can be a little more complex. For example if you are adding a constraint on a column in a table it may be a little easier to accomplish this in the design view rather than a script in a query window.

1.c Building Queries – Building Queries is another powerful use of the design window, especially for those whose may have less experience in the query window. To open design view for building a query in Object Explorer right click on the table name then select ‘Edit Top 200’. This will open up the results grid with the top 200 records. Then there are four panes: Diagram, Criteria, SQL & Results which we can open by pressing Ctrl + 1, Ctrl + 2, Ctrl + 3. When opening design view for a table the default is just the results pane in a grid, then we add the other three to assist in building the query. The diagram pane is nice since we can graphically see the table, we can simply place a check in the checkbox of the column we’d like to select & it will build the syntax of the query in the SQL pane. Similarly we can use the Criteria pane to build the query. We can select the column we’d like to select in the column column. We can easily sort the query even by descending with the sort order column. We can use the alias column to create an alias quickly. We can create a where clause with the filter column. As we see the develop queries using the diagram & criteria panes. When starting to use joins or changing query types the design view accomplishes this without having to know the syntax.

2. Query Editor Window - Every task in T-SQL can be accomplished by scripts in a Query Editor Window. It is more powerful than the design view. For example if you are joining tables from two different servers we could not accomplish this in the design view, we'd have to use a Query Window because we can only add tables in design view from one server.

2. a General – The Query window is more powerful than design view in many ways. A simple way to open a new query window is in Object Explorer when you select/highlight the database then press Ctrl+N. Another way is to right click on a table & click ‘Select Top 1000 Rows’. The window is all text then there is also a results pane which can be displayed toward the bottom of the window. The results will display a grid of the results. The results grid can be resized by default it slits the screen, typically I prefer to resize it so the results screen takes up a smaller amount of screen real-estate about the lower quarter of the screen.

2.b Execute & Parse - F5 is execute & Ctrl + F5 is parse When in the query window an import task to know is to be able to execute the script. We can execute a script by clicking the ‘!Execute’ button or pressing F5. A common practice I perform is to parse my query before I run them which will check for syntax errors. Parsing will take half a second & can be performed by Ctrl+F5 or clicking the check mark.

2.c Highlighting Selected Text – We are able to highlight or select text in a query editor window then execute or parse only the selected text. This is a very useful feature since often times the scripts we are working become long & we only want to execute a portion of the script. Another time to highlight text is when there is a longer script & I want to execute a stored procedure, we can select/highlight the proc’s name then execute only the proc not the entire script.

2.d Scripting - Scripting is a very useful feature in SSMS. For example if we wanted a script to create a current table in Object Explorer we could right click on a table --> script table as --> Create --> New Query Window. This will display the script with the syntax to create this table. We can also script other objects such as Stored Procedures. Another scripting option is to script a table as a insert into, update & drop.

We really just covered some very important fundamentals of T-SQL including the difference between design view & Query Editor Window. In the next section we’ll check out Stored Procedures.


Below is the other posts in the series:
T-SQL Database Development IDE & Queries (1 of 4)

What do YOU think is the biggest difference between design view vs. query editor window?

Bye for Now,
Catto