Cursor Optimization Tips

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

Do not forget to close SQL Server cursor when its result set is not needed.

To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

*****

Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.

To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

*****

Try to reduce the number of records to process in the cursor.

To reduce the cursor result set, use the WHERE clause in the cursor's select statement. It can increase cursor performance and reduce SQL Server overhead.

*****

Try to reduce the number of columns to process in the cursor.

Include in the cursor's select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.

*****

Use READ ONLY cursors, whenever possible, instead of updatable cursors.

Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

*****

Try avoid using insensitive, static and keyset cursors, whenever possible.

These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

*****

Use FAST_FORWARD cursors, whenever possible.

The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

*****

Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.

*****

Bulk Copy Optimization Tips

Use nonlogged bulk copy whenever possible.

The nonlogged bulk copy is much faster than logged one, but to use it you must provide all the following conditions:

1. The database option 'select into/bulkcopy' is set to true.
2. The target table is not being replicated.
3. The TABLOCK hint is specified.
4. The target table has no indexes, or if the table has indexes, it is empty when the bulk copy starts.

*****

Use native mode bulk copy whenever possible.

This can improve performance in comparison with the character mode.

*****

Try to use BULK INSERT command instead of bcp or DTS to load data into SQL Server.

The BULK INSERT command is much faster than bcp or the data pump to perform text file import operations, however, the BULK INSERT statement cannot bulk copy data from SQL Server to a data file.

*****

Use the bcp utility instead of DTS when you need to export data from the SQL Server table into a text file.

The bcp utility is much faster than DTS, so try to use it whenever possible.

*****

Specify the number of the first and the last row to bulk copy, if you need to bulk copy not all the rows from the specified data file.

This can results in good performance benefits, because the total amount of data copied will be less.

*****

Specify the number of rows per batch of data copied, if the transaction log was filled before the bulk copy is complete.

Because each batch is copied to the server as one transaction, SQL Server commits or rolls back the transaction for every batch. When you bulk copy large data files, the transaction log can be filled before the bulk copy is complete. In this case, enlarge the transaction log, allow it to grow automatically or specify the number of rows per batch of data copied.

*****

Try to increase the packet_size option.

The packet_size option specifies the number of bytes, per network packet, sent to and from the server. The packet_size can be from 4096 to 65535 bytes with the default of 4096. Increased packet size can enhance performance of bulk copy operations. Try to set the packet_size option to 8192 bytes and continue monitoring.

*****

Use the ORDER hint, if the clustered index exists on the table and the data file is sorted according to the clustered index.

This can significantly improve performance of the bulk copy operation, because SQL Server will load data in the clustered index order without any reorders operations.

*****

If you create a new table and bulk copy data into it, try to bulk load data first and only after that create any indexes.

This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

If you load data into an empty table with the existing nonclustered indexes, try to drop the nonclustered indexes, bulk load data and only after that re-create the nonclustered indexes.

This can significantly improve performance of the bulk copy operation, because data will be loaded into SQL Server table without any index pages creation during the bulk copy.

*****

If you load data into a nonempty table with the existing clustered and/or nonclustered indexes, and the amount of data added is large, it can be faster to drop all indexes on the table, perform the bulk copy operation, and then re-create the indexes after the data is loaded.

Check the time needed to load data with dropping/re-creating indexes and without dropping/re-creating indexes on your test server before run bulk copy operation on the production server.

*****

If your SQL Server box has multiple CPUs, try to divide loaded data into two or more sources and run multiple instances of BCP on separate clients to load data in parallel.

Because SQL Server allows data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement, try to use parallel data loads whenever possible. To bulk copy data into SQL Server in parallel, you must provide all the following conditions:

1. The database option 'select into/bulkcopy' is set to true.
2. The TABLOCK hint is specified.
3. The target table does not have any indexes.

*****

Specify the TABLOCK hint, if you bulk copy data into an empty table from a single client.

This can improve performance of the bulk copy operation, because this causes a table-level lock to be taken for the duration of the bulk copy operation.

*****

Try to avoid using CHECK_CONSTRAINTS and FIRE_TRIGGERS hints.

Using these hints can significantly degrade performance of the bulk copy operation, because for each row loaded the constraints and insert triggers defined on the destination table will be executed.

Optimization for designing tables

Normalize your tables to the third normal form.

A table is in third normal form (3NF) if it is in second normal form (2NF) and if it does not contain transitive dependencies. In most cases, you should normalize your tables to the third normal form. The normalization is used to reduce the total amount of redundant data in the database. The less data there is, the less work SQL Server has to perform, speeding its performance.

*****

Consider the denormalization of your tables from the forth or fifth normal forms to the third normal form.

Normalization to the forth and fifth normal forms can result in some performance degradation, especially when you need to perform many joins against several tables. It may be necessary to denormalize your tables to prevent performance degradation.

*****

Consider horizontal partitioning of the very large tables into the current and the archives versions.

The less space used, the smaller the table, the less work SQL Server has to perform to evaluate your queries. For example, if you need to query only data for the current year in your daily work, and you need all the data only once per month for the monthly report, you can create two tables: one with the current year's data and one with the old data.

*****

Create the table's columns as narrow as possible.

This can reduce the table's size and improve performance of your queries as well as some maintenance tasks (such as backup, restore and so on).

*****

Try to reduce the number of columns in a table.

The fewer the number of columns in a table, the less space the table will use, since more rows will fit on a single data page, and less I/O overhead will be required to access the table's data.

*****

Try to use constraints instead of triggers, rules, and defaults whenever possible.

Constraints are much more efficient than triggers and can boost performance. Constraints are more consistent and reliable in comparison to triggers, rules and defaults, because you can make errors when you write your own code to perform the same actions as the constraints.

*****

If you need to store integer data from 0 through 255, use tinyint data type.

The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint data types accordingly. For example, if you design tables for a small company with 5-7 departments, you can create the departments table with the DepartmentID tinyint column to store the unique number of each department.

*****

If you need to store integer data from -32,768 through 32,767, use smallint data type.

The columns with smallint data type use only two bytes to store their values, in comparison with four bytes and eight bytes used to store the columns with int and bigint data types respectively. For example, if you design tables for a company with several hundred employees, you can create an employee table with the EmployeeID smallint column to store the unique number of each employee.

*****

If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type.

The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table with a BookID int column to store the unique number of each book.

*****

Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647.

The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.

*****

Use smalldatetime data type instead of datetime data type, if you need to store the date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute.

The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types. For example, if you need to store the employee's hire date, you can use column with the smalldatetime data type instead of datetime data type.

*****

Use varchar/nvarchar columns instead of text/ntext columns whenever possible.

Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, it can take more time to get the text/ntext values.

*****

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.

The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

*****

Consider setting the 'text in row' SQL Server 2000 table's option.

The text, ntext, and image values are stored on the Text/Image pages, by default. This option specifies that small text, ntext, and image values will be placed on the Data pages with other data values in a data row. This can increase the speed of read and write operations and reduce the amount of space used to store small text, ntext, and image data values. You can set the 'text in row' table option by using the sp_tableoption stored procedure.

*****

If you work with SQL Server 2000, use cascading referential integrity constraints instead of triggers whenever possible.

For example, if you need to make cascading deletes or updates, specify the ON DELETE or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE statements. The cascading referential integrity constraints are much more efficient than triggers and can boost performance.

*****

Transact-SQL Optimization Tips

Try to restrict the queries result set by using the WHERE clause.

This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

*****

Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.

This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

*****

Use views and stored procedures instead of heavy-duty queries.

This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

*****

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

*****

If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.

Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < style="text-align: center; font-weight: bold;">*****

Try to use constraints instead of triggers, whenever possible.

Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.

*****

Use table variables instead of temporary tables.

Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

*****

Try to avoid the HAVING clause, whenever possible.

The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

*****

Try to avoid using the DISTINCT clause, whenever possible.

Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.

*****

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.

This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

*****

Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.

This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

*****

Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.

You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.

*****

Try to use UNION ALL statement instead of UNION, whenever possible.

The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

*****

Do not use optimizer hints in your queries.

Because SQL Server query optimizer is very clever, it is very unlikely that you can optimize your query by using optimizer hints, more often, this will hurt performance.

*****

MS SQL Server Shortcut keys

Menu Activation Keyboard Shortcuts

Action	Standard	SQL Server 2000
Move to the SQL Server Management Studio menu bar	ALT	ALT
Activate the menu for a tool component	ALT+HYPHEN	ALT+HYPHEN
Display the context menu	SHIFT+F10	SHIFT+F10
Display the New File dialog box to create a file	CTRL+N	No equivalent
Display the New Project dialog box to create a new project	CTRL+SHIFT+N	CTRL+SHIFT+N
Display the Open File dialog box to open an existing file	CTRL+O	CTRL+SHIFT+INS
Display the Open Project dialog box to open an existing project	CTRL+SHIFT+O	No equivalent
Display the Add New Item dialog box to add a new file to the current project	CTRL+SHIFT+A	No equivalent
Display the Add Existing Item dialog box to add an existing file to the current project	ALT+SHIFT+A	No equivalent
Display the Query Designer	CTRL+SHIFT+Q	CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action	ESC	ESC

Windows Management and Toolbar Keyboard Shortcuts

Action	Standard	SQL Server 2000
Close the current MDI child window	CTRL+F4	CTRL+F4
Print	CTRL+P	CTRL+P
Exit	ALT+F4	ALT+F4
Toggle full screen mode	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Close the current tool window	SHIFT+ESC	SHIFT+ESC
Cycle through the next MDI child windows	CTRL+TAB	CTRL+TAB
Cycle through the previous MDI child windows	CTRL+SHIFT+TAB	CTRL+SHIFT+TAB
Move to the current tool window toolbar	SHIFT+ALT	No equivalent
Move to the next tool window	ALT+F6	ALT+F6
Move to the previously selected window	SHIFT+ALT+F6	SHIFT+ALT+F6
Opens a new query editor window	No equivalent	CTRL+O
Display Object Explorer	F8	F8
Display Registered Servers	CTRL+ALT+G	No equivalent
Display Template Explorer	CTRL+ALT+T	CTRL+ALT+T
Display Solution Explorer	CTRL+ALT+L	CTRL+ALT+L
Display the Summary Window	F7	F7
Display the Properties Window	F4	F4
Display the Toolbox	CTRL+ALT+X	CTRL+ALT+X
Display the Bookmarks Window	CTRL+K, CTRL+W	No equivalent
Display the Browser Window	CTRL+ALT+R	CTRL+ALT+R
Display the Error List Window (Transact-SQL Editor only)	CRTL+\, CTRL+E	CRTL+\, CTRL+E

Cursor Movement Keyboard Shortcuts

Action	Standard	SQL Server 2000
Move the cursor left	LEFT ARROW	LEFT ARROW
Move the cursor right	RIGHT ARROW	RIGHT ARROW
Move the cursor up	UP ARROW	UP ARROW
Move the cursor down	DOWN ARROW	DOWN ARROW
Move the cursor to the beginning of the line	HOME	HOME
Move the cursor to the end of the line	END	END
Move the cursor to the beginning of the document	CTRL+HOME	CTRL+HOME
Move the cursor to the end of the document	CTRL+END	CTRL+END
Move the cursor up one screen	PAGE UP	PAGE UP
Move the cursor down one screen	PAGE DOWN	PAGE DOWN
Moves the cursor one word to the right	CTRL+ RIGHT ARROW	CTRL+ RIGHT ARROW
Moves the cursor one word to the left	CTRL+ LEFT ARROW	CTRL+ LEFT ARROW

Text Selection Keyboard Shortcuts

Action	Standard	SQL Server 2000
Select text from the cursor to the beginning of the document	CTRL+SHIFT+ HOME	CTRL+SHIFT+ HOME
Select text from the cursor to the end of the document	CTRL+SHIFT+END	CTRL+SHIFT+END
Select text from the cursor to the start of the current line	SHIFT+HOME	SHIFT+HOME
Select text from the cursor to the end of the current line	SHIFT+END	SHIFT+END
Select text down line by line starting from the cursor	SHIFT+ DOWN ARROW	SHIFT+ DOWN ARROW
Select text up line by line starting from the cursor	SHIFT+UP ARROW	SHIFT+UP ARROW
Move the cursor up one line, extending the selection	SHIFT+ALT+ UP ARROW	SHIFT+ALT+ UP ARROW
Extend selection up one page	SHIFT+ PAGE UP	SHIFT+ PAGE UP
Extend selection down one page	SHIFT+ PAGE DOWN	SHIFT+ PAGE DOWN
Select the entire current document	CTRL+A	CTRL+A
Select the word containing the cursor, or the closest word	CTRL+W	CTRL+W
Select the current location in the editor, back to the previous location in the editor	CTRL+=	CTRL+=
Extend the selection to the top of the current window	CTRL+SHIFT+ PAGE UP	CTRL+SHIFT+ PAGE UP
Move the cursor to the last line in view, extending the selection	CTRL+SHIFT+ PAGE DOWN	CTRL+SHIFT+ PAGE DOWN
Extend the selection one word to the right	CTRL+SHIFT+ RIGHT ARROW	CTRL+SHIFT+ RIGHT ARROW
Extend the selection one word to the left	CTRL+SHIFT+ LEFT ARROW	CTRL+SHIFT+ LEFT ARROW
Move the cursor to the right one word, extending the selection	CTRL+SHIFT+ALT+ RIGHT ARROW	CTRL+SHIFT+ALT+ RIGHT ARROW
Move the cursor to the left one word, extending the selection	CTRL+SHIFT+ALT+ LEFT ARROW	CTRL+SHIFT+ALT+ LEFT ARROW

Bookmark Keyboard Shortcuts

Action	Standard	SQL Server 2000
Set or remove a bookmark at the current line	CTRL+K, CTRL+K	CTRL+F2
Next bookmark	CTRL+K, CTRL+N	F2
Previous bookmark	CTRL+K, CTRL+P	SHIFT+F2
Clear bookmarks	CTRL+K, CTRL+L	CTRL+SHIFT+F2

Tree Control Keyboard Shortcuts

Action	Standard	SQL Server 2000
Collapse tree nodes	- (on the numeric keypad)	- (on the numeric keypad)
Expand all tree nodes	* (on the numeric keypad)	* (on the numeric keypad)
Scroll the tree control up in the window	CTRL+UP ARROW	CTRL+UP ARROW
Scroll the tree control down in the window	CTRL+ DOWN ARROW	CTRL+ DOWN ARROW

Code Editor Keyboard Shortcuts

All shortcuts are not implemented in all types of code editors.

Action	Standard	SQL Server 2000
Toggle the full-screen display	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Scroll text up one line	CTRL+UP ARROW	CTRL+UP ARROW
Scroll text down one line	CTRL+ DOWN ARROW	CTRL+ DOWN ARROW
Reverse the last editing action	CTRL+Z	CTRL+Z
Restore the previously undone edit	CTRL+SHIFT+Z or CTRL+Y or ALT+SHIFT+BACKSPACE	CTRL+SHIFT+Z or CTRL+Y or ALT+SHIFT+BACK SPACE
Insert the body of the specified file into the current window. The window name is not changed to match the specified file. Note:	No equivalent	CTRL+SHIFT+P
Save the selected item	CTRL+S	CTRL+S
Save all	CTRL+SHIFT+S	CTRL+SHIFT+S
Close	CTRL+F4	CTRL+F4
Print	CTRL+P	CTRL+P
Exit	ALT+F4	ALT+F4
Delete all text in the current file	CTRL+SHIFT+DEL	CTRL+SHIFT+DEL
Display the Go To Line dialog box	CTRL+G	CTRL+G
Increase line indent	TAB	TAB
Decrease line indent	SHIFT+TAB	SHIFT+TAB
Make the selected text upper case	CTRL+SHIFT+U	CTRL+SHIFT+U
Make the selected text lower case	CTRL+SHIFT+L	CTRL+SHIFT+L
Make the selected text a comment	CTRL+K, CTRL+C	CTRL+SHIFT+C
Uncomment the selected text	CTRL+K, CTRL + U	CTRL+SHIFT+R
Open a new query with current connection	CTRL+Q	CTRL+N
Open database in Object Explorer	ALT+F8	ALT+F8
Specify values for template parameters	CTRL+SHIFT+M	CTRL+SHIFT+M
Execute the selected portion of the query editor or the entire query editor if nothing is selected	F5 or CTRL+E or ALT+X	F5 or CTRL+E or ALT+X
Parse the selected portion of the query editor or the entire query editor if nothing is selected	CTRL+F5	CTRL+F5
Display the estimated execution plan	CTRL+L	CTRL+L
Cancel the executing query	ALT+BREAK	ALT+BREAK
Include actual execution plan in the query output	CTRL+M	CTRL+K
Open the Query Option dialog box	No equivalent	CTRL+SHIFT+O
Output results in a grid	CTRL+D	CTRL+D
Output results in text format	CTRL+T	CTRL+T
Output results to a file	CTRL+SHIFT+F	CTRL+SHIFT+F
Show or hide the query results pane	CTRL+R	CTRL+R
Toggle between query and results pane	F6	F6
Move to the next active window in Management Studio	CTRL+F6	CTRL+F6
Auto hide all	ALT+ENTER	No equivalent
Windows	CTRL+W	No equivalent
Open SQL Server Profiler	CTRL+ALT+P	CTRL+ALT+P

Text Manipulation in Code Editor Keyboard Shortcuts

Action	Standard	SQL Server 2000
Insert a new line	ENTER or SHIFT+ENTER	ENTER or SHIFT+ENTER
Swap the characters on either side of the cursor (Does not apply to the SQL Editor.)	CTRL+T	No equivalent
Delete on character to the right of the cursor	DELETE	DELETE
Delete one character to the left of the cursor	BACKSPACE or SHIFT+ BACKSPACE	BACKSPACE or SHIFT+ BACKSPACE
Insert the number of spaces configured for the editor	TAB	TAB
Insert a blank line above the cursor	CTRL+ENTER	CTRL+ENTER
Insert a blank line below the cursor	CTRL+SHIFT+ ENTER	CTRL+SHIFT+ ENTER
Change the selected text to lowercase	CTRL+SHIFT+L	CTRL+SHIFT+L
Change the selected text to uppercase	CTRL+SHIFT+U	CTRL+SHIFT+U
Toggle between insertion mode and overtype mode	INSERT	INSERT
Move selected lines to the left on tab stop	SHIFT+TAB	SHIFT+TAB
Delete the word to the right of the cursor	CTRL+DELETE	CTRL+DELETE
Delete the word to the left of the cursor	CTRL+BACKSPACE	CTRL+BACKSPACE
Transpose the words on either side of the cursor (Does not apply to the SQL Editor.)	CTRL+SHIFT+T	CTRL+SHIFT+T

Transact-SQL Debugger Keyboard Shortcuts

Action	Standard	SQL Server 2000
Start or continue debugging	ALT+F5	No equivalent
Stop debugging	SHIFT+F5	No equivalent
Step into	F11	No equivalent
Step over	F10	No equivalent
Step out	SHIFT+F11	No equivalent
Implement the Run To Cursor command	CTRL+F10	No equivalent
Display the QuickWatch dialog box	CTRL+ALT+Q	No equivalent
Toggle breakpoint	F9	No equivalent
Delete all breakpoints	CTRL+SHIFT+F9	No equivalent
Display the Breakpoints window	CTRL+ALT+B	No equivalent
Break all	CTRL+ALT+BREAK	No equivalent
Display the Watch 1 window	CTRL+ALT+W, 1	No equivalent
Display the Watch 2 window	CTRL+ALT+W, 2	No equivalent
Display the Watch 3 window	CTRL+ALT+W, 3	No equivalent
Display the Watch 4 window	CTRL+ALT+W, 4	No equivalent
Display the Autos window	CTRL+ALT+V, A	No equivalent
Display the Locals window	CTRL+ALT+V, L	No equivalent
Display the Immediate window	CTRL+ALT+I	No equivalent
Display the Call Stack window	CTRL+ALT+C	No equivalent
Display the Threads window	CTRL+ALT+H	No equivalent

Microsoft IntelliSense Keyboard Shortcuts

Action	Standard	SQL Server 2000
List members	CTRL+SPACE or CTRL+J	CTRL+J
Complete word	ALT+RIGHT ARROW	ALT+RIGHT ARROW
Parameter information	CTRL+SHIFT+SPACE	No equivalent
Jump between syntax pairs	CTRL+]	No equivalent

Document Window and Browser Keyboard Shortcuts

Action	Standard	SQL Server 2000
Toggle full-screen mode	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Move to the next pane of a split pane view of a document	F6	F6
Move to the previous document in the editor or designer	CTRL+SHIFT+F6 CTRL+SHIFT+TAB	CTRL+SHIFT+F6 CTRL+SHIFT+TAB
Move to the previous pane of a document in split pane view	SHIFT+F6	SHIFT+F6
Display the Browser Window	CTRL+ALT+R	CTRL+ALT+R
Back, display the previous page in the viewing history	ALT+LEFTARROW	ALT+LEFTARROW
Forward, display the next page in the viewing history	ALT+RIGHTARROW	ALT+RIGHTARROW

Solution Explorer Keyboard Shortcuts

Action	Standard	SQL Server 2000
Display Solution Explorer	CTRL+ALT+L	CTRL+ALT+L
Display the New File dialog box to create a new file	CTRL+N	CTRL+SHIFT+INS
Display the New Project dialog box to create a new project	CTRL+SHIFT+N	CTRL+SHIFT+N
Display the Open File dialog box to open an existing file	CTRL+O	No equivalent

Help and Books Online Keyboard Shortcuts

Action	Standard	SQL Server 2000
Help	F1	F1
Help on "How Do I"	CTRL+F1	CTRL+F1
Books Online Contents	CTRL+ALT+F1	CTRL+ALT+F1
Books Online Index	CTRL+ALT+F2	CTRL+ALT+F2
Help Search	CTRL+ALT+F3	CTRL+ALT+F3
Dynamic Help	CTRL+ALT+F4	CTRL+ALT+F4
Help Favorites	CTRL+ALT+F	CTRL+ALT+F

Search Keyboard Shortcuts

Action	Standard	SQL Server 2000
Display the Find dialog box	CTRL+F	CTRL+F
Display the Replace dialog box	CTRL+H	CTRL+H
Start incremental search. Type the characters to search for or press CTRL+I to search for characters from the previous search	CTRL+I	CTRL+B
Find the next occurrence of the previous search text	F3	F3
Find the previous occurrence of the search text	SHIFT+F3	SHIFT+F3
Find the next occurrence of the currently selected text	CTRL+F3	CTRL+F3
Find the previous occurrence of the currently selected text	CTRL+SHIFT+F3	CTRL+SHIFT+F3
Display the Replace in Files dialog box	CTRL+SHIFT+H	CTRL+SHIFT+H
Reverse incremental search so it starts at the bottom of the file and searches to the top	CTRL+SHIFT+I	CTRL+SHIFT+I
Select or clear the Search up option in Find and Replace	ALT+F3, B	ALT+F3, B
Stop the Find in Files search	ALT+F3, S	ALT+F3, S
Select or clear the Find whole word option in Find and Replace	ALT+F3, W	ALT+F3, W
Selects or clears the Wildcard option in Find and Replace	ALT+F3, P	ALT+F3, P

Cut and Paste Keyboard Shortcuts

Action	Standard	SQL Server 2000
Cut (delete the currently selected item and place it to the Clipboard)	CTRL+X or SHIFT+DELETE	CTRL+X or SHIFT+DELETE
Copy to the Clipboard	CTRL+C or CTRL+INSERT	CTRL+C or CTRL+INSERT
Paste from the Clipboard at the insertion point	CTRL+V or SHIFT+INSERT	CTRL+V or SHIFT+INSERT

Log Viewer Keyboard Shortcuts

Action	Standard	SQL Server 2000
Refresh	F5	F5
Move between the Select logs pane and the Log file summary pane	F6	F6
Move to the Log file summary pane	ALT+S	ALT+S
Load a new log	CTRL+SHIFT+L	CTRL+SHIFT+L
Export a log	CTRL+SHIFT+E	CTRL+SHIFT+E
Filter a log	CTRL+SHIFT+F	CTRL+SHIFT+F
Search in a log	CTRL+SHIFT+S	CTRL+SHIFT+S

Activity Monitor Keyboard Shortcuts

Action	Standard	SQL Server 2000
Refresh	F5	F5
Filter the monitor display	CTRL+SHIFT+F	CTRL+SHIFT+F

Replication Monitor Keyboard Shortcuts

Action	Standard	SQL Server 2000
Refresh	F5	F5
Open a detail window from a grid	ENTER	ENTER

Replication Conflict Viewer Keyboard Shortcuts

Action	Standard	SQL Server 2000
Define filter	F6	F6
Apply filter	F7	F7
Show all columns	F8	F8

Sysobjects Information

Use Sysobjects in SQL Server to Find Useful Database Information

SQL Server sysobjects Table contains one row for each object created within a database. In other words, it has a row for every constraint, default, log, rule, stored procedure, and so on in the database. Therefore, this table can be used to retrieve information about the database. We can use xtype column in sysobjects table to get useful database information. This column specifies the type for the row entry in sysobjects.

For example, you can find all the user tables in a database by using this query:

 select * from sysobjects where xtype='U'

Similarly, you can find all the stored procedures in a database by using this query:

 select * from sysobjects where xtype='P'

This is the list of all possible values for this column (xtype):

• C = CHECK constraint
  
• D = Default or DEFAULT constraint
  
• F = FOREIGN KEY constraint
  
• L = Log
  
• P = Stored procedure
  
• PK = PRIMARY KEY constraint (type is K)
  
• RF = Replication filter stored procedure
  
• S = System table
  
• TR = Trigger
  
• U = User table
  
• UQ = UNIQUE constraint (type is K)
  
• V = View
  
• X = Extended stored procedure