SQL Server — SQL Query Analyzer
Query Analyzer (and Enterprise Manager) has been superseded by SQL Server Management Studio.
The SQL Query Analyzer is the main interface for running SQL queries against your database. You can use the SQL Query Analyzer to create and run adhoc scripts, or you can create SQL scripts and save them for later use.
Accessing SQL Query Analyzer
You can open Query Analyzer from Enterprise Manager by clicking Tools > Query Analyzer.
SQL Query Analyzer looks like this:
Tip: Before you open Query Analyzer, use Enterprise Manager to navigate to the database you’d like to work with. That way, Query Analyzer will open using that database.
Object Browser
SQL Query Analyzer also has an «Object Browser» that you can use to browse and edit tables, views, and stored procedures. The Object Browser also displays a list of common SQL Server functions and their parameters.
To open the Object Browser, press F8. Alternatively, you can click the Object Browser icon on the toolbar. Another way of showing the Object Browser is to go Tools > Object Browser > Show/hide. The Object Browser displays to the left of your workspace.
Writing Queries
You are now ready to write SQL queries against your database. You can use this interface to create database objects (such as databases, tables, views etc), insert data into a database table, select data, update data, delete data.
The following screen shot shows an example of using a SQL ‘select’ statement to select data from a database:
When I started creating the above example, I forgot the name of the table that I wanted to select data from. That wasn’t a problem. All I needed to do was navigate through the Object Browser until I saw the names of the tables in the left pane. When I saw the table I needed, I simply clicked and dragged it onto the workspace area (and releasing the mouse in the right spot). This is quite a nice feature of Query Analyzer/Object Browser as it can save you time.
As you can see, the results of the query are displayed in the bottom pane.
The above ‘select’ statement is an example of a SQL query. Apart from the occasional example, SQL queries are outside the scope of this tutorial. If you’d like to learn more about writing SQL queries, check out the SQL tutorial.
Database Administration Tasks
Most of the database administration tasks that can be performed in Enterprise Manager (through the user interface) can be performed (programatically) in Query Analyzer. This tutorial concentrates on the Enterprise Manager method, mainly because it’s usually a lot easier for new users to get their head around. Once you become more familiar with SQL Server, you may find yourself using Query Analyzer to perform many of the tasks that you started out doing in Enterprise Manager.
Where is the Query Analyzer in SQL Server Management Studio 2008 R2?
I have some SQL thats getting run and it is taking to long to return the results / parse / display, etc. in a asp.net c# application.
I have SQL Server Management Studio 2008 R2 installed to connect to a remote SQL Server 2000 machine. Is there a Query Analyzer or profiler I can use to see whats going on? I’m not sure if I’m sending too many requests, if the requests are taking too long, if there are additional indexes I can add to speed things up etc.
Any free tools out there that are replacements for the Microsoft tools?
7 Answers 7
To analyze a query you already have entered into the Query editor, you need to choose «Include Actual Execution Plan» (7th toggle button to the right of the «! Execute» button). After executing the query, you need to click on the «Execution Plan» tab in the results pane at the bottom (above the results of the query).
Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio for Query Analyzer. Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler for profiler.
Упражнение 1. Работа с sql Query Analyzer и выполнение запроса
В меню Start выберите Programs, затем — Microsoft SQL Server и щелкните Query Analyzer. После запуска SQL Query Analyzer выводится диалоговое окно Connect To SQL Server.
В раскрывающемся списке SQL Server выберите Local. Если этот пункт отсутствует в списке, щелкните кнопку с эллипсом чтобы найти локальный компьютер. Убедитесь, что установлен переключатель Windows Authentication.
Щелкните ОК. Появляется интерфейс SQL Query Analyzer с двумя окнами: Object Browser и Query. Если окно Object Browser не появилось, щелкните кнопку Object Browser на панели инструментов. Откроется окно Object Browser.
Настройка размера окон sql Query Analyzer
Размер окон SQL Query Analyzer, Object Browser и Query должен быть таким, чтобы без труда удалось просмотреть компоненты интерфейса. Щелкните кнопку Show Results Pane на панели инструментов. Теперь окно Query разделено на две панели: Editor и Results. Поместите курсор мыши над разделителем двух панелей и измените размер этих панелей, перетащив разделитель.
Настройка панели Results
Щелкните на панели инструментов кнопку Show Results Pane. Панель Results исчезает, а ее место в окне Query займет панель Editor.
На панели инструментов щелкните кнопку Execute Mode, а затем — Show Execution Plan.
Щелкните кнопку Execute Mode на панели инструментов и просмотрите список параметров. Обратите внимание, что по умолчанию установлены оба параметра — Results In Grid и Show Execution Plan. Параметры из списка, вызываемого кнопкой Execute Mode, определяют, какая информация и каким образом будет выводиться.
Установите параметр Show Server Trace. На панели инструментов щелкните кнопку Execute Mode, а затем — Show Client Statistics.
Поиск объекта в базе данных
Щелкните кнопку Object Search на панели инструментов. Появляется окно Object Search. Из раскрывающегося списка Database выберите Northwind. Убедитесь, что в разделе All Object Types установлен флажок напротив User Table, после этого пометьте флажком параметр System Table.
Щелкните Find Now. Результат поиска появится в нижней части окна Object Search. Обратите внимание, что в столбце имени БД (db name) перечислены только объекты базы данных Northwind, a в столбце типа объекта (object type) — только пользовательские таблицы (user fable) или системные таблицы (system table).
Закройте окно Object Search.
Для просмотра содержимого таблицы
В окне Object Browser раскройте узел Northwind, а затем — узел User Tables.
Щелкните правой кнопкой dbo.Employees и выберите Open. Появляется окно Open Table со списком содержимого таблицы Employees из базы данных Northwind.
Просмотрите таблицу, прокручивая содержимое окна Open Table. Закройте окно Open Table.
Отладка хранимой процедуры
Раскройте в окне Object Browser узел Northwind, а затем — узел Stored Procedures.
Щелкните правой кнопкой dbo.CustOrderHist, затем щелкните Debug. Появляется диалоговое окно Debug Procedure.
Щелкните Execute. В начале операции отладки хранимой процедуры CustOrderHist из базы данных Northwind выводится окно отладчика Transact-SQL. В нижней части окна выводится сообщение об ошибке. В нем указано, что для запуска этой хранимой процедуры необходимо задать параметр @CustomerID. Просмотрите результаты отладки.
Закройте окно отладчика Transact-SQL.
Закройте окно Object Browser. Теперь на экране должна быть только окно Query с панелью Editor.
Starting Query Analyzer
I heard in 2-3 hundred years an inexpensive robot will do administration and maintenance of any database (MS SQL Server 2005, Oracle10G, and UDB). Companies will not need DBAs and computer programmers — they will need only entrepreneurs. I imagine — I will wake up in the morning and my inexpensive robot will greet me: «Good morning Dr. Shlafman, all servers are up and running, no performance or other issues happened». I will go for golf, I will have breakfast, nap, dinner, movie, supper, a lot of fun with my family, I will go to bed and before I will fall asleep I will ask myself; «Should I ask for a raise
or should I update my resume and start looking for a new assignment?» And, I will hear my inexpensive robot’s voice:»Dr.Shlafman, today, you got a raise. You get a raise any time you want». I will fall asleep happily, cheerfully, and peacefully.
In 2-3 hundred years. Meantime I think how to start the SQL Query Analyzer. Here is the «Just do it» traditional way to open the SQL Query Analyzer:
- Click Start
- Click All Programs
- Click Microsoft SQL Server
- Click Query Analyzer
- In Connect to SQL Server Window select Server
- You may want to select Authentication type for your connection (Windows Authentication is preferred type)
- Click OK.
- Click Change Database Icon on Tool bar, double click on selected database (If you have more then 30 databases on your server it is faster to run USE command to change the database. For example:
- In the Editor Pane type in: USE pubs
- Press F5 key to run the command)
If most of the time you work on one specific project it’s wise to setup a default database:
Now, when user Scott opens the SQL Query Analyzer database Northwind will automatically become current database without an explicit execution of USE statement.
Using this sequence of steps you should do at least 5 clicks to open the SQL Query Analyzer. If you can do it using three clicks — you can save 2500 clicks for a year based on assumption that you open the SQL Query Analyzer only 5 times every day. (50 weeks * 5 days * 5 times * 2 clicks to save each time) = 2500 clicks.
The second traditional method «I can do it» to start the SQL Query Analyzer is –
- Click Show desktop icon on Taskbar
- Double click the SQL Query Analyzer shortcut
and then steps 5-8 from «Just do it» method. Using «I can do it» method you should do at least 4 clicks to open the SQL Query Analyzer.
The third method «Preferred» to start the SQL Query Analyzer allows you to open the SQL Query Analyzer using 2-3 clicks! To make this method working all you need to have is a SQL Query Analyzer icon on the Task Bar. Here are the steps detailing how to place a SQL Query Analyzer icon on the Taskbar:
Figure 1.
Figure 2.
Figure 3.
Figure 4.
Figure 5.
Figure 6.Using «Preferred» method you can start Enterprize Manager, Internet Explorer, Microsoft OUtlook or any other application you use on every day basic. Just one click and you have Enterprize Manager running! In SQL Query Analyzer, my favorite icon from Tool bar is Connect.
In 2-3 hundred years. Meantime I think how to set up my PC to start the SQL Query Analyzer, Enterprize Manager or any other applications without a click. Here are the steps of «Let it do it» method:
Figure 7.
Figure 8.
Figure 9.
Figure 10.
Figure 11.In 2-3 hundred years. Meantime I am working with multiple servers and databases and I think how to set up my PC to start the SQL Query Analyzer with minimum clicks. Here are the steps of «I’m a DBA» method:
Figure 12.
Figure 13.
Figure 14.
Figure 15.
Figure 16.