toad for sql server vs query analyzer

Toad for SQL Server vs. Query Analyzer NTC 2008

1

CHAPTER 1

INTRODUCTION

1.1 Background

Most SQL Server development is done using SQL Server Enterprise Manager

or Query Analyzer. The former provides a nice, hierarchical view of the entire server

and its attendant database objects, but has many limitations. Before working in a

Query Analyzer, we should to join to the server because all data which related with database

that will be made stored all in a server. Many way to read, maintain data, and other in a server

includes Toad for SQL Server.

Quest Software Inc.'s TOAD for SQL Server was written to address many of

these problems and solve a few others for good measure. TOAD is a development

environment for SQL Server that also works as an object explorer, dependency

checker and object manager.

The free version of TOAD is fully functional, but it expires after 90 days and

must be reinstalled. The commercial version includes advanced features such as

tuning and modeling functions, a powerful stored procedure debugger and more. This

is one of the choices or tool alternative to perform database management system in

every environment.

1.2 Problem Formulation

From these explanations we can get the simple formulation to solve the

problem inside the network file system bellow:

1. What is the history of Toad and Query analyzer?

2. What are the features of Toad and Query analyzer?

3. What are considerations for each program that are degree implementation, tool

options, and interface?

http://www.quest.com/toad_for_sql_server/index.asp


2

1.3 Purpose

The purposes of creating this article are:

1. To know what is the history of Toad and Query analyzer

2. To know the features of each tools program those are Toad and Query analyzer

3. To know consideration for each tools program

1.4 Benefit

1. Give tool alternative to user not only query analyzer for maintaining database

2. Gives and Increases SQL developer of database productivity and maintenance

3. Give information about Toad and Query Analyzer in SQL Server 2000 also

each benefit inside

1.5 Problem Boundaries

We should give limitations in the explanation to minimize time effectiveness

in order to time and our ability is limited. And we call it problem boundaries, here

we just explain about:

1. Short historical about Toad for SQL Sever and Query Analyzer in SQL Server

2000

2. We do not explain the other version and specification in Toad products.

3. Explaining features and benefit in each tools

4. The considerations between Toad for SQL Server and Query Analyzer in

which implementations to maintain database

1.6 Writing Systematic

This is the complete writing systematic:

CHAPTER 1

INTRODUCTION

This chapter explains about background, problem formulation, purpose

benefit, problem boundaries and writing systematic in creating ISAS.


3

CHAPTER 2

CONTENT

We explain about the history of Query Analyzer and Toad for SQL Server,

benefits of each tools program, and the features that are facilitated in every

program.

CHAPTER 3

ANALISYS

We will explain about some considerations inside the programs, which are

about features, tool option, and interface of every program.

CHAPTER 4

CLOSING

We explain conclusion and suggestion about these explanations above.

This chapter explains about conclusion and suggestion.

BIBLIOGRAPHY

1.7 Time Schedule

The things was

discussed

Time Schedule of Making ISAS

Presented on 15th

of March 2008

3 4 5 6 7 8 9 10 11 12 13 14 15

Looking of data

Made the

Construction of

Chapter I

CHAPTER II &

CHAPTER III

CHAPTER IV

Designing Power

Point


4

CHAPTER 2

CONTENT

2.1 The History of Toad for SQL Server

Toad for SQL Server is a database development and administration tool that

increases user productivity and ensures optimal code quality for the SQL Server

environment. Toad for SQL Server also is an application development tool built

around a single integrated SQL, T-SQL, and Procedure Editor. Using Toad,

developers can build and test T-SQL code and can use the Browser to quickly access,

create and edit database objects. Toad lets you view tables, indexes, stored procedures

and more - all through a multi-tabbed browser.

One of the most frequent requests from Toad for Oracle customers is to extend

Toad to cover additional database technologies. Toad for SQL Server, Toad for DB2,

and Toad for MySQL are several new versions of Toad, which address this need in

the Database Technology market.

Quest’s Database Development Best Practices for SQL Server delivers a

repeatable and measurable process that even novices can use to manage the database

development process, ensuring that optimal code is deployed to production.

With Toad SQL Server users can reduce the bugs in their applications, build

the highest quality code faster, and maximize application performance – all while

integrating with current version control software for collaboration. The result is

reduced time to market.

More than one million database professionals have chosen Toad® as their

integrated environment for development and administration. From its simple

navigation and smooth workflow, to its advanced T-SQL debugging, optimization and

administration capabilities, Toad for SQL Server is ideal for SQL Server users of all

skill sets and experience levels


5

2.2 The History of Query Analyzer

The most common operation in SQL databases is the query, which is

performed with the declarative SELECT keyword. SELECT retrieves data from a

specified table, or multiple related tables, in a database. While often grouped with

Data Manipulation Language (DML) statements, the standard SELECT query is

considered separate from SQL DML, as it has no persistent effects on the data stored

in a database. SQL Query Analyzer is a graphical user interface for designing and

testing Transact-SQL statements, batches, and scripts interactively. SQL Query

Analyzer can be called from SQL Server Enterprise Manager.

SQL Query Analyzer offers:

a. A Free-form text editor for keying in Transact-SQL statements.

b. Color-coding of Transact-SQL syntax to improve the readability of complex

statements.

c. Object browser and object search tools for easily finding the objects in a

database and the structure of the objects.

d. Templates that can be used to speed development of the Transact-SQL

statements for creating SQL Server objects. Templates are files that include

the basic structure of the Transact-SQL statements needed to create objects in

a database.

e. An interactive debugger for analyzing stored procedures.

f. Results presented in either a grid or a free-form text window.

g. Graphical diagram of the show plan information showing the logical steps

built into the execution plan of a Transact-SQL statement.

This allows programmers to determine what specific part of a poorly

performing query is using a lot of resources. Programmers can then explore changing

the query in ways that minimize the resource usage while still returning the correct

data. Index Tuning Wizard to analyze a Transact-SQL statement and the tables it

references, to see if adding additional indexes will improve the performance of the

query.

../wiki/Select_(SQL)

../wiki/Table_(database)

../wiki/Data_Manipulation_Language


6

SQL queries allow the user to specify a description of the desired result set,

but it is left to the devices of the database management system (DBMS) to plan,

optimize, and perform the physical operations necessary to produce that result set in

as efficient a manner as possible. An SQL query includes a list of columns to be

included in the final result immediately following the SELECT keyword. SELECT is

the most complex statement in SQL, with several optional keywords and clauses,

including:

1. The FROM clause which indicates the source table or tables from which the

data is to be retrieved. The FROM clause can include optional JOIN clauses to

join related tables to one another based on user-specified criteria.

2. The WHERE clause includes a comparison predicate, which is used to restrict

the number of rows returned by the query. The WHERE clause is applied

before the GROUP BY clause. The WHERE clause eliminates all rows from

the result set where the comparison predicate does not evaluate to True.

3. The GROUP BY clause is used to combine, or group, rows with related values

into elements of a smaller set of rows. GROUP BY is often used in

conjunction with SQL aggregate functions or to eliminate duplicate rows from

a result set.

4. The HAVING clause includes a comparison predicate used to eliminate rows

after the GROUP BY clause is applied to the result set. Because it acts on the

results of the GROUP BY clause, aggregate functions can be used in the

HAVING clause predicate.

5. The ORDER BY clause is used to identify which columns are used to sort the

resulting data, and in which order they should be sorted (options are ascending

or descending). The order of rows returned by an SQL query is never

guaranteed unless an ORDER BY clause is specified.

The following is an example of a SELECT query that returns a list of

expensive books. The query retrieves all rows from the books table in which the price

column contains a value greater than 100.00. The result is sorted in ascending order

by title. The asterisk (*) in the select list indicates that all columns of the books table

should be included in the result set.

../wiki/Database_management_system

../wiki/Query_plan

../wiki/Query_optimizer

../wiki/From_(SQL)

../wiki/Join_(SQL)

../wiki/Where_(SQL)

../wiki/Group_by_(SQL)

../wiki/Having_(SQL)

../wiki/Order_by_(SQL)


7

2.3 Features inside Tools Program

2.3.1 Features of Toad for SQL Server

2.3.1.1 General

Performance Improvements. We are excited to announce major

memory improvements for queries that return large result sets in the Editor

Result sets and the Database Browser Data tab. The memory only increases by

a couple of megabytes initially regardless of the size of the result set.

Subsequent scrolling of the result set only reads into memory what is needed

to display.

Default window to open when connecting-A separate window no longer

prompts you to associate a default window to open when initially connecting

to Toad. You now specify the default window to open in the Configuration

wizard.

2.3.1.2 Query Development

1. Editor:

A. View scripts in a full size window.

Use docking controls to auto-hide or group the Editor panel, Code

Navigator, and result sets. To display the Editor and Results panels in a

separate, full size window, drag-and-drop the Results tab into the Editor

window. Press F6 to switch between the two panels.

B. Error line highlighting.

The ability to highlight the line in the Editor that caused an execution

error. To do this, double-click the error message in the Script Output tab.

C. Change case and add comments.

You can now change the case of text or comment or uncomment a line

by selecting Editor Advanced or from the Editor's right-click menu.


8

2. Query Builder:

A. From clause sub query support.

You can now add a sub query to a From Clause using the Query

Builder toolbar or the right-click menu. A Queries panel displays on the left

side of the window with the From Clause sub query. You can add tables and

views and create joins the same way you normally use the Query Builder.

After creating the sub query, click the parent query in the Queries

panel to return to the original query. A From Clause Sub query table now

displays in the Diagram panel with all the columns in the sub query. Use this

new table as you would any other database object.

B. Date ranges.

Date Range tags are also useful when executing SQL statements from

Automation. You no longer have to edit regularly executed queries to change a

date range when you need to retrieve data every month for the previous month.

2.3.1.3 Database Administration

A. Generate SQL drop script.

You can now select Drop Script from the Generate SQL right-click

menu in the Database Browser or Object Palette.

B. Generate SQL retains Formatter settings.

When using the right-click menu to generate SQL in the Database

Browser or Object Palette, the settings specified in the Formatter Options

pages are now retained.

C. Log Reader.

Log Reader server components are now supported on x64 servers and

Windows Vista.


9

Data Compare. Provides the ability to:

1. Compare tables and views that have different owners, different names,

or different column names.

2. Compare tables that do not have primary keys.

3. Compare specific columns of a table.

4. Change Data Compare project parameters through the Change Data

wizard.

5. Connections listed in Data Compare are better integrated with

environment in that it now displays connections based on the

connections in the Connection Manager.

2.3.1.4 Data Management

A. Grid Customization.

You can now customize the grid style when a skin is applied to Toad.

You can modify this setting in the Configuration wizard (Tools | Configuration

Wizard) or in Interface Options (Tools | Options | Environment | Interface).

B. Read-only data.

You can now set an option to make all data grids read-only by default.

C. Search data.

Ability to search data grids for word or phrase matches by pressing

CTRL+F. Clicking Mark All in the Find window highlights all cells in the grid

that match the find criteria.

2.3.2 Features of Query Analyzer

Microsoft SQL Server 2000 SQL Query Analyzer is a graphical tool that allows you to:

a. Create queries and other SQL scripts and execute them against SQL Server

databases. (Query window)

b. Quickly create commonly used database objects from predefined scripts.

(Templates)

c. Quickly copy existing database objects. (Object Browser scripting feature)

d. Execute stored procedures without knowing the parameters. (Object Browser

procedure execution feature)

e. Debug stored procedures. (T-SQL Debugger)


10

f. Debug query performance problems. (Show Execution Plan, Show Server

Trace, Show Client Statistics, Index Tuning Wizard)

g. Locate objects within databases (object search feature), or view and work with

objects. (Object Browser)

h. Quickly insert, update, or delete rows in a table. (Open Table window)

i. Create keyboard shortcuts for frequently used queries. (custom query shortcuts

feature)

j. Add frequently used commands to the Tools menu. (customized Tools menu

feature)

You can run SQL Query Analyzer directly from the Start menu, or you run it from

inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer from

the command prompt by executing the “isqlw” utility.


11

CHAPTER III

ANALYSIS

3.1 Benefit of Toad for SQL Server

a. Reduces the amount of bugs and enables IT professionals to shared settings,

project files and queries

b. Enables users to get up to speed quickly, build code faster and improve coding

quality

c. Reduces the amount of performance bottlenecks

d. Ensures that the SQL Server environment is maintained and administered

effectively

e. Brings applications to market quicker by ensuring that the code written is of

the highest quality and scaled for production.

f. Reduces overall IT costs

g. Keeps end-users happy

h. Offers a Best Practices Workflow that can be measured

For Developers Toad for SQL Server have many benefits, such as:

a. Build high-quality code quickly and efficiently

b. Integrate with Version Control

c. Format scripts and SQL statements

d. Access SQL Server Expertise

e. Optimize queries

f. Validate and test application code for performance and scalability

g. Compare and synchronize data, schemas and servers

h. Compare scripts and database objects

i. Visualize tables, dependencies and database relationships

j. Build and share project files

k. Create/Edit/View/Alter objects and data


12

3.2 Benefit of Query Analyzer

a. This component enables programmers to develop and test Transact-SQL

statements interactively.

b. It includes aids such as a graphical display of the execution plan and

performance statistics of a Transact-SQL statement.

c. It color-codes the different syntax elements to increase the readability of

Transact-SQL statements, and includes an integrated Transact-SQL debugger.

d. It also has an Object Browser that determines the attributes of the tables,

views, stored procedures, and other objects in a database, and supports

templates used to speed the building of complex statements.

3.3 The Consideration between Toad for SQL Server and Query Analyzer

3.3.1 The Interfaces

Commonly the interfaces in the Toad for SQL Server are more attractive than

Query Analyzer. Toad for SQL Server gives more reliable pictures to the user,

moreover in the version 3.2.0.325 let’s we go to look up the differences.

1. Query Analyzer

3.1 Query Analyzer essential tool options.


13

3.2 Query Analyzer working area.

2. Toad for SQL Server

Here we can look the first interface of Toad for SQL Server, it was little bit

interesting than Query Analyzer. As same as the other programs Toad for SQL Server

has a special criterion which helps the user to operate program called Toad Studio

Online. The user can share and organize the database via online connection while can

ask to the software developer about function and many others inside the program.

Picture 3.1 Toad’s start up program in windows

Picture 3.3 Toad for SQL Server when it is starting up


14

Picture 3.4 Toad for SQL Server Interfaces

Picture 3.3 Toad for SQL Server Working Area


15

3.3.2 The Tools Option

Generally the options in Toad are more complex than Query Analyzer, but the

tool options in Toad for SQL Server is commonly used with us. Exactly the basic

function are same, it just the addition from developer of software to make easy

maintain in complex database.

Picture 3.4 Toads for SQL Server and Query Analyzer Tools Option

No. Tools in Toad for

SQL Server

Tools in Query Analyzer

1. Editor Object Browser

2. Database Browser Object Search

3. Query Builder Manages Indexes

4. Web Browser Manage Statistic

5. Object Search Options

6. External Tools Customize

7. Configuration Wizard

8. SQL Trace

9. Customize

10. Options


16

CHAPTER IV

CLOSING

4.1 Conclusion

After simple explanations above we can conclude that:

1. Generally the program which are Toad for SQL Server and Query

Analyzer made for helping the user that are programmer, DBA,

Analyst, or an organization to create, maintain, compare, update and

many more about complex database.

2. SQL Query Analyzer is an interactive, graphical tool that enables a

database administrator or developer to write queries, execute multiple

queries simultaneously, view results, analyze the query plan, and

receive assistance to improve the query performance.

3. Toad for SQL Server is a database development and administration

tool that increases user productivity and ensures optimal code quality

for the SQL Server environment. Toad for SQL Server also is an

application development tool built around a single integrated SQL, T-

SQL, and Procedure Editor

4. From these tools we can conclude that in Toad have features more than

Query Analyzer have and in operating or maintaining Query analyzer

is better than Toad because Toad is not commonly used.


17

4.2 Suggestion

Here the suggestion that commonly need to the next arrangement to make

better which are in reporting, writing, arranging they are:

1. We suggest that the explanations of steps to start these programs,

creating query, executing database must be cleared to give good

understanding to the user globally.

2. We suggest that for the beginner which interests in DBMS to use

Query Analyzer because the command is still easy. Eventually, for the

expert person you should to try Toad because there so many new

feature to make the task more easy.

3. The real implementation for each program is needed to be exposed to

the public moreover to the beginner who interests in Database

Management System.

4. Giving the explanations about the way to solve the conflict or error

problems appears in the phase of making database.


18

BIBLIOGRAPHY

1. Chapple, Mike. SQL Fundamentals (HTML). About.com: Databases.

About.com. Retrieved on 2008- 03-09

2. Structured Query Language (SQL) (HTML). International Business Machines

(October 27, 2006). Retrieved on 2008-03-09.

3. http://www.sony-ak.com/articles/3/what_is_sql.php Retrieved 2008-03-10

4. http://www.quest.com/toad/toad-for-sql-server.aspx Retrieved 2008-03-10

5. http://www.quest.com/toad-for-sql-server/configurations-checklist.aspx

Retrieved 2008-03-11

6. http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm Retrieved 2008-03-

11

7. http://toadsoft.com/get2knowSS/index.html Retrieved 2008-03-11

8. http://wss-id.org/files/folders/tutang/entry344.aspx Retrieved 2008-03-12

9. Asfi, Marsani. Modul SQL Server.pdf Retrieved 2008-03-12

http://databases.about.com/od/sql/a/sqlfundamentals.htm?terms=SQL

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0004100.htm

../wiki/2007

http://www.sony-ak.com/articles/3/what_is_sql.php

http://www.quest.com/toad/toad-for-sql-server.aspx%20Retrieved%202008-03-10

http://www.quest.com/toad-for-sql-server/configurations-checklist.aspx

http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm

http://toadsoft.com/get2knowSS/index.html

http://wss-id.org/files/folders/tutang/entry344.aspx%20Retrieved%202008-03-12

toad for sql server vs query analyzer

Documents