Welcome to Query Builder for SQL Server

dbforge-query-builder-header

Thank you for choosing dbForge Query Builder for SQL Server!

ExpandedToggleIcon(9x12) dbForge Query Builder for SQL Server Overview

dbForge Query Builder for SQL Server allows to build complex SQL queries via an intuitive visual query building interface easily and without manual code writing. Our visual SQL query tool is destined for simplification of developing SQL queries for SQL Server. This SQL query designer will be useful to those, who often create queries to databases.

ExpandedToggleIcon(9x12) Where to start

To start using dbForge Query Builder for SQL Server, see Getting Started section. The guide helps you get started quickly by describing how to setup a new database connection, and provides an explanation of the key steps for basic operations.

ExpandedToggleIcon(9x12) See Also

Getting Started

© 2010-2017 Devart


What's New in dbForge Query Builder for SQL Server v3.12

SQL Server Support

· SQL Server 2016 Service Pack 1 (SP1) new syntax support
· SQL Server 2017 CTP2 new syntax support
· Azure SQL Data Warehouse connectivity support

Query Execution History

The query execution history feature can store all the information about all SQL statements executed for the particular period of time. It allows users to track the duration of the executed statements, their status, as well as set filters by the execution date. In addition, you can check who ran the statement and when it was completed.

SQL Document

Each document uses its own connection.


Requirements

dbForge Query Builder for SQL Server requires the following:

· .NET Framework 4.5.2 or higher installed. You can download .NET Framework here.

note Note

.NET Framework is pre-installed in Microsoft Windows Vista and Windows 7/8/8.1/10.

· Microsoft Windows 7/8/8.1/10
· Windows Server 2008/2012

ExpandedToggleIcon(9x12) See Also

Compatibility


Compatiiblity

Microsoft SQL Server

· SQL Server 2017 CTP2.0
· SQL Server 2016 SP1
· SQL Server 2016
· SQL Server 2014, 2012, LocalDB
· SQL Server 2008, 2008R2 (including Express edition)
· SQL Server 2005 (including Express edition)
· SQL Server 2000
· SQL Azure
· Working with SQL Server on Amazon RDS

ExpandedToggleIcon(9x12) See Also

Copyright Information


Copyright Information

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user.

Devart may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Devart, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Copyright 2010-2017 Devart. All rights reserved.

Microsoft, Visual Studio, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

ExpandedToggleIcon(9x12) See Also

Previous Releases


Previous Releases

ExpandedToggleIcon(9x12) In This Section

What's New in dbForge Query Builder for SQL Server, v3.10

What's New in dbForge Query Builder for SQL Server, v3.9

What's New in dbForge Query Builder for SQL Server, v3.8

What's New in dbForge Query Builder for SQL Server, v3.5

What's New in dbForge Query Builder for SQL Server, v3.0

What's New in dbForge Query Builder for SQL Server, v2.0


What's New in dbForge Query Builder for SQL Server, v3.11

Query Profiler updated features

· Live Query Statistics added
· Wait Stats added
· Save/Open SQL plan files implemented
· Table I/O added
· Highlighting potentially resource-intensive statements
· Displaying clustered ColumnStore indexes in execution plan

Data Export/Import updated features

· Live Query Statistics added
· Wait Stats added
· Save/Open SQL plan files implemented
· Table I/O added
· Highlighting potentially resource-intensive statements
· Displaying clustered ColumnStore indexes in execution plan

Other improvements

· Trial product activation implemented
· Application visual style greatly improved
· FIPS compliance
· Azure Active Directory authentication

ExpandedToggleIcon(9x12) See Also

Requirements


What's New in dbForge Query Builder for SQL Server, v3.10
· Support for SQL Server 2016
· Objects suggestions in CROSS/OUTER APPLY statement
· Suggestion of compound phrases
· Greatly improved suggestion performance
· Several new Formatter options and profiles
· Both objects and schemas suggestions in the Completion List when writing cross-database queries
· Data type information to the column header in Data Grid added
· Data Grid now supports Generate Script As feature
· Generate Script As is now available for multiple objects in Database Explorer

ExpandedToggleIcon(9x12) See Also

What's New in dbForge Query Builder for SQL Server, v3.9


What's New in dbForge Query Builder for SQL Server, v3.9

Compatibility

· Windows 10 support

SQL Editing and Execution

· Query execution pane
· Easy way to hide data results
· Updated Find&Replace feature in documents and files

Data Editor

· Data Viewer, LOB Editor, and Redirect Data to Output are combined in a single control
· Zooming font size in Data Viewer and Editor Window

Database Explorer

· Displaying the objects number

Query Profiler

· Diagram printing options for a Query Profiler Plan Diagram

Data Export

· Updated Export wizard

User Interface

· Support of multiple monitors
· New skins
· Updated Start Page

Other Features and Improvements

· Improved application performance

ExpandedToggleIcon(9x12) See Also

What's New in dbForge Query Builder for SQL Server, v3.8


What's New in dbForge Query Builder for SQL Server, v3.8

The release of dbForge Query Builder for SQL Server 3.8 introduces the following improvements, and new features:

SQL Server 2014 is supported

Database

Take Offline and Bring Online database states are added

Database Explorer

Each database state has its corresponding icon now

Code Completion

· New CREATE COLUMNSTORE INDEX statement syntax is supported
· New keywords in the CREATE TABLE statement are supported
· New keywords in the CREATE INDEX statement are supported
· New keywords in the CREATE PROCEDURE statement are supported

SQL Profiler

· Query plans can be saved visually
· Results comparison is executed when the required elements are selected
· Various queries plans can be shown vertically in one view when comparing
· Buffer and plans cache refresh options are added
· Zoom menu in the Profiler diagram is available

User Interface

· Switching tabs by the CTRL+ALT+PAGE UP and CTRL+ALT+PAGE DOWN keys combination
· SSMS shortcut schema availability

Other Features and Improvements

· Coloring a database connection according to the document category is possible now
· Database connection properties are expanded
· SET connection options in query execution are added
· Possibility to switch from the wizard page to the main application window is added
· SQL document zooming by holding the CTRL key and rotating the mouse wheel is added
· Restoring documents from the last session is possible now

ExpandedToggleIcon(9x12) See Also

What's New in dbForge Query Builder for SQL Server, v3.5


What's New in dbForge Query Builder for SQL Server, v3.5

The release of dbForge Query Builder for SQL Server 3.50 introduces the following improvements, and new features:

SQL Server 2012 LocalDB is now supported

Query Profiler

Query bottlenecks will be easy to find with this tool, as it allows viewing query execution statistics. After you apply improvements, you can compare the current statistics with the previous results to consider if your changes were effective.

Generate Script As

Generate template SQL SELECT, UPDATE, INSERT, DELETE scripts for tables to simplify working with database objects. You can access this option from the context menu of Database Explorer.

Data export functionality to a CSV file is added

Now you can use column separator: tab, space, custom.

Bug Fixes

· Incorrect trial on Windows 8 is fixed
· Incorrect behaviour with identity columns on adding data in Data Editor is fixed
· The navigation from the Error List to the specific error in a script is fixed
· Computed columns are now excluded from synchronization by default
· Error report when Estimated Rows exceeds the limits of integer in the Profiler document view is fixed
· Error on changing connection in SQL document is fixed
· Error on setting alias name in upper case is fixed
· Work with memory is improved while redirecting data to the Output window
· Syntax error on executing the current statement using the Execute Current Statement command is fixed
· Syntax error on executing a script with @@ variables is fixed
· A problem with saving a file's encoding is fixed
· A problem with the installation of the product is fixed
· A problem related to the focus, while executing several statements is fixed
· A problem with highlighting of the IIF function is fixed
· A problem in data editor with not assigning default values is fixed
· A problem with clearing memory on selecting data from table is fixed
· A problem with running the application on .NET Framework 4.5 is fixed
· A problem with removing a record in Data Editor is fixed
· A problem with generating a schema name when exporting data using the MERGE mode is fixed
· A problem with applying a filter in Data Editor is fixed
· A problem with saving the Splitting options is fixed
· A problem with changing options for code editor is fixed
· A problem with connection to TFS 11 is fixed
· A problem with displaying 'Code Completion' options is fixed
· A problem with punctuation's visibility in code editor with black background being set is fixed
· OutOfMemoryException on searching data in Data Editor is fixed
· ArgumentNullException on finding the next occurrence in text is fixed
· ArgumentException on opening a file through the command line is fixed
· SqlTypeException on executing stored procedure with data entered out of the permissible range
· FileNotFoundException on opening the New File window is fixed
· NullReferenceException on editing data in the Data Editor is fixed
· NullReferenceException while working with a query that contains PIVOT (in Query Builder) is fixed
· NullReferenceException on executing a query is fixed
· NullReferenceException on editing a table in the editor is fixed
· NullReferenceException on invoking Help in the Command Line is fixed
· NullReferenceException on adding rows in Data Editor is fixed
· NullReferenceException on retrieving data from the tables containing a BIGINT field is fixed
· NullReferenceException on trying to find a search pattern in a document is fixed
· NullReferenceException on executing a script is fixed
· NullReferenceException on opening the SMTP options page is fixed
· NullReferenceException on profiling a query is fixed
· NullReferenceException on searching a text in SQL document is fixed
· NullReferenceException in Query Builder is fixed
· NullReferenceException on creating a SELECT query in Query Builder is fixed
· InvalidCastException on editing a subquery in Query Builder is fixed
· InvalidCastException on double-clicking a table in Database Explorer is fixed
· InvalidOperationException on executing a script is fixed
· InvalidOperationException while executing queries on SQL Azure is fixed
· InvalidOperationException on editing data in Data Editor is fixed
· InvalidOperationException on refreshing data in Database Explorer is fixed
· InvalidOperationException on closing a connection with invalid transaction is fixed
· InvalidOperationException on executing a query is fixed

ExpandedToggleIcon(9x12) See Also

What's New in dbForge Query Builder for SQL Server, v3.0


What's New in dbForge Query Builder for SQL Server, v3.0

The release of Review Assistant 3.00 introduces the following improvements, and new features:

· Support of visual building of INSERT, UPDATE, and DELETE statements
· Quick conversion of SELECT query into UPDATE or DELETE with saving of WHERE condition
· Turning SELECT queries into INSERT statements
· Executing of separate subqueries to check their correctness without leaving diagram interface
· Converting selected data into SQL queries (e.g. into INSERT statements)
· Usability of visual expression editor is improved
· Indication of type of query on a diagram
· Editing of multiple execution results of one script on separate tabs
· New start page is added

ExpandedToggleIcon(9x12) See Also

What's New in dbForge Query Builder for SQL Server, v2.0


What's New in dbForge Query Builder for SQL Server, v2.00

The release of Review Assistant 2.00 introduces the following improvements, and new features:

Customizable data export

Now, you can export data from one or even multiple tables at once to the following formats: CSV, DBF, HTML, MS Access, MS Excel, ODBC, PDF, RTF, Text, and XML. Before exporting you can customize export style attributes, such as text, background and border color, font, set output format of exported columns and preview the result.

You won't waste your time changing the exported data, the result will be just what you wanted.

Enhanced Data Editor

· Sorting and grouping tables now works on a sever side
· Ability to edit tables in JOIN statements is added
· Ability to view tables united with a foreign key in a popup window is added
· Many other minor improvements and extensions to existing functionality were made

User Reported Bug Fixes

· Behavior of the popup expression editor when working with functions was enhanced
· Rows in the popup expression editor are automatically quoted now
· NullReferenceException on editing a subquery in Query Builder was fixed

ExpandedToggleIcon(9x12) See Also

Previous Releases


Getting Started

ExpandedToggleIcon(9x12) In This Section

dbForge Query Builder for SQL Server Start Page
This section describes the basic functionality provided by dbForge Query Builder for SQL Server.

Installation Guide
This section describes gives overview of the install/uninstall processes.

How To: Connect to a Database
This topic explains how to create a new server connection.


dbForge Query Builder for SQL Server Start Page

The goal of the start page is to provide a location, available when the product first started, for the user to find out about dbForge Query Builder for SQL Server functionality. We also added some other features to this start page such as the ability to navigate to the product support page, blog, feedback page, etc. The Start page uses a multi-tab model that provides the relevant features and tools in a specific tab.

To access the Start page, in the View menu click Other Windows, and then click Start Page.

Section | Tool | Description
Quick Links | Help | Opens the built-in help file where you will find how to perform basic and advanced tasks in dbForge Query Builder for SQL Server. It gives you necessary information to get started with the application and broaden your database administration skills when you are familiar with simple tasks.
 | Product Support Page | On the support page you can find answers to any questions you may have about using dbForge Query Builder for SQL Server and provide comments and suggestions about the product.
 | Blog | Opens the Devart blog which contains a variety of technical articles on every product and theme.
 | Forum | Participate in the discussion forum of dbForge for SQL Server product line. Get answers to your technical questions and find more valuable information about dbForge Query Builder for SQL Server and other products' advantages.
 | Devart website | Opens the Devart company website.
 | Product Feedback Page | We are striving to build a set of tools that simplify SQL interaction and enhance opportunities for developers, DBAs, and other people dealing with databases. We are interested in how well dbForge Query Builder for SQL Server works for you and what features you want to see added.
 | Recent Projects | The section displays recently opened projects. Click a required project name link to open it.
NewQuery | New Query | Click the icon to start building new query.
open-file | Open File | Click the icon to choose a file path to an already existing query document and open it.
ExportDataIcon | Export Data | This tool allows you to export your data. Select the Database objects to export, and configure the related options.
ExecuteScriptIcon | Execute Large Script | The Execute Script wizard allows you to execute large scripts without opening them in editor and loading them to memory.

ExpandedToggleIcon(9x12) See Also

Getting Started

How To: Connect to a Database


Installation Guide

ExpandedToggleIcon(9x12) In This Section

How To: Install dbForge Query Builder for SQL Server
Describes how to install the application.

How To: Uninstall dbForge Query Builder for SQL Server
Describes how to uninstall the application.


How To: Install dbForge Query Builder for SQL Server

This topic describes how to install Query Builder for SQL Server on Windows OS with the help of Devart dbForge Query Builder for SQL Server Setup Wizard.

To Install Query Builder for SQL Server

1. Run querybuildersql.exe to begin installation.
2. On the Welcome to dbForge Query Builder for SQL Server page, click Install.
3. On the Select Destination page, select the directory where dbForge Query Builder for SQL Server will be installed. To change the default directory, click Browse. Optionally, click the License Terms link to learn End-User Agreement for dbForge Query Builder for SQL Server. If you agree to the Devart dbForge Query Builder for SQL Server License Terms, click Next.
4. Optionally, on the Shortcuts page, select the additional shortcuts you would like the Setup Wizard to create while installing. There are two options available: Create a desktop icon and Add a shortcut to the start menu. Click Next.
5. On the File Associations page, select the document types and file extensions that will be associated with this application when it is installed on your computer. Click Next.
6. Optionally, on the Installing page, you can interrupt the installation by clicking the Cancel button.
7. On the final page, click Finish to exit Setup wizard. Optionally, you can select the Launch "dbForge Query Builder for SQL Server" option to start the application immediately after installation.

ExpandedToggleIcon(9x12) See Also

Installation Guide

How To: Uninstall dbForge Query Builder for SQL Server


How To: Uninstall dbForge Query Builder for SQL Server

This topic describes how to uninstall dbForge Query Builder for SQL Server from Windows OS.

To uninstall dbForge Query Builder for SQL Server

1. To begin the uninstall, go to Control Panel and then Programs and Features.
2. Right click Devart dbForge Query Builder for SQL Server and select Uninstall.
3. The Uninstall dialog box appears. Click Yes, if you are sure you want to uninstall the application. This starts the Devart dbForge Query Builder for SQL Server Uninstaller.
4. Optionally, on the Uninstall options page, you can specify the following additional options and then click Uninstall.
· Registry options
· Connection list
· Configuration files
· Scripts and exported data
5. Optionally, select the Go to the product feedback page after uninstall option to leave a feedback about the application.
6. Click Uninstall to proceed with the uninstall.
7. If the uninstall is successful, the dialog box confirming that the application was successfully removed appears. Click OK to exit the uninstaller.

ExpandedToggleIcon(9x12) See Also

Installation Guide

How To: Connect to a Database


Activating dbForge Query Builder for SQL Server

Unless activated, dbForge Query Builder for SQL Server works on the trial basis for 30 days. When this time period expires, dbForge Query Builder for SQL Server must be activated with the purchased activation key.

dbForge Query Builder for SQL Server can be activated either online, or offline.

Online activation

This activation method requires an active Internet connection.

To activate dbForge Query Builder for SQL Server online:

1. If you have not purchased dbForge Query Builder for SQL Server, click Buy Now... on the pop-up window that appears at the start of dbForge Query Builder for SQL Server. If you already have your activation key, click Activate...
Alternatively, click Activate Product on the Help menu.

2. The Activation window opens. Copy your activation key from the e-mail that was sent to you after your purchase, and paste it into the edit box.
The activation key looks like a long sequence of letters and digits.

note Note

If you do not see the e-mail with the activation key, be advised to check your Junk/ Spam/Bulk folders.

You can always retrieve your activation key at your Customer Portal page.

3. Click Activate.

Offline activation

Offline activation allows activating dbForge Query Builder for SQL Server right at the Devart web site. This activation method helps activating the product when the online activation fails.

To activate dbForge Query Builder for SQL Server offline:

1. Click the Manual Activation button on the Activation window. It opens when online activation fails.

2. Copy the activation request text with the Copy to Clipboard button, or save it to a text file with the Save to File... button.

3. Open the Manual Product Activation page of the Devart web site, and paste the copied text in the text box. Click Download license. The license file with the .lic extension is downloaded to your computer.

4. Return back to the Activation window of dbForge Query Builder for SQL Server and click the Load license... button. Specify the location of the downloaded .lic file and click Open.


How To: Connect to a Database

Before you can connect to a database, you must create a server connection.

To create a connection:

1. On the Database menu, click New Connection. The Database Connection Properties dialog box opens.
2. In the Server combo box, select <Browse...>. The Browse for Servers window opens.
3. On the Local Servers tab, select an SQL Server instance (if any). Otherwise, click the Network Servers tab to select an SQL Server instance in the network.
4. Select the Authentication type, either SQL Server authentication or Windows authentication.
5. Enter the login credentials in the User and Password fields.
6. In Database, type or click the name of the database which you want to connect to.
7. In the Connection Name text box, the connection name should be generated automatically from the host name. You can create a distinctive name for your new connection.
8. (optional) You can assign a document category to the new connection.
9. (optional) You can click Advanced to configure advanced connection properties.
10. (optional) Click the Test Connection button to verify the connection.
11. Click OK to create the connection.

ExpandedToggleIcon(9x12) See Also

Getting Started


Building Queries with Query Builder

ExpandedToggleIcon(9x12) In This Section

Query Builder Overview
Provides basic information about Query Builder.

How To: Add Tables to Query
Describes how to add tables to SELECT list when visually building a query.

How To: Remove Tables from Query
Information on how to remove tables from SELECT statements.

How To: Include Columns in Query
Information on how to setup SELECT clause.

How To: Make Joins Between Tables
Information on how to create relations between tables.

How To: Build WHERE or HAVING Clause
Information on how to set up WHERE and HAVING clauses.

How To: Include Fields in GROUP BY or ORDER BY Clauses
Information on how to setup GROUP BY and ORDER BY clauses.

Building a Query in Visual Mode
Contains a step-by-step procedure to easily create inner and outer joins and customize a query.

Working with Different Query Types
Tells how to use types of queries.

Working with Query Diagram
Tells how to manage shapes on the diagram.

Working with Subqueries
Tells how to create and edit subqueries.

Navigating Query with Document Outline
Describes how to navigate queries with the Document Outline window.


Query Builder Overview

Query Builder is designed to enhance productivity and simplify SQL query building tasks. Query Builder provides a graphical user interface for creating SQL queries. You can drag-and-drop multiple tables, views and their columns onto a visual designer to generate SQL statements.

You can use Query Builder to perform the following tasks:

Working with a graphical representation of a query or with SQL code
Query Builder includes a pane that displays your query graphically and a pane that displays the SQL text of your query. You can work in either the graphical pane or the text pane. Query Builder synchronizes the views so that they are always current.

Joining related tables
If you add more than one table to your query, Query Builder automatically determines how the tables are related and constructs the appropriate join command.

Querying or updating databases
You can use Query Builder to return data by using SELECT statements and to create queries that update, add, or delete records in a database.

Viewing and editing results immediately
You can run your query and work with a recordset in a grid that allows you to scroll through and edit records in the database.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Add Tables to Query


How To: Add Tables to Query

When you create a query, you are retrieving data from a table or other objects structured like tables — views and certain user-defined functions. To work with any of these objects in your query, you add them to the diagram pane.

To add a table or table-valued object to a query

1. On the Start page select SQL Development, and then click Query Builder.
2. In Database Explorer, select a required table or several tables (or table-valued objects) and drag-and-drop them to the diagram pane.

Alternatively you can

1. In Database Explorer, select a required table. You can select multiple tables by holding the CTRL button.
2. Right click selected tables, and point to Send to in the shortcut menu, and then click Query Builder.

Tables and views are automatically added to the query when you reference them in the statement in the SQL editor.

note Note

A connection you are using while dragging a table must be the connection of the query document. You will not be able to perform a drag and drop operation from another connection.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Remove Tables from Query


How To: Remove Tables from Query

You can remove a table or any table-valued object from the query.

To remove a table or table-structured object

· In the Diagram Pane, select a table, view, user-defined function, synonym, or query, and then press DELETE, or right-click the object and then click Remove from the diagram. You can select and remove multiple objects at one time.

Alternatively

· Remove all references to the object in the SQL editor.

When you remove a table or table-valued object, the Query Builder automatically removes joins that involve that table or table-valued object and removes references to the object's columns in the SQL editor and.

note Note

Removing a table or table-valued object does not delete anything from the database, it only removes it from the current query.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Include Columns in Query


How To: Include Columns in Query

To use a column in a query, you must add it to the query. You may add a column to display it in query output, to use it for sorting, to search the contents of the column, or to summarize its contents.

To add an individual column

· In the Diagram Pane, select the check box next to the column that you want to include.

Alternatively

· In the Tabbed Editor, move to the first blank grid row, click the field in the Column column, and select a table and a column name from the drop-down list.

To add all columns for one table or table-valued object

· In the Diagram Pane, select the check box next to *(All Columns).

To add all columns for all tables and table-structured objects

1. In the Design Pane, select all tables.
2. Make sure no join lines in the Design Pane are selected.
3. Right-click any table and select Select all columns from the shortcut menu.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Make Joins Between Tables


How To: Make Joins Between Tables

A join between two tables describes how these tables are related. Depending on a join kind you can get different results for the same tables. Refer to SQL manual for information on joins and their effect. Query Builder for SQL Server allows you to visually create 5 kinds of joins: inner, left outer, right outer, natural, and cross join. You can also easily set very complex conditions for any kind of joins.

ExpandedToggleIcon(9x12) Creating Joins

To add a join between two tables, do either of these actions:

· Use the diagram - Drag-and-drop a source column from one table to a destination column in the second table.
· Use the Joins tab of the tabbed editor - Click the FilterGreyAdd button of the top tree node. A new empty join with an empty condition will appear. Click the <Enter table name> field and specify the join tables. You may type them or select them from the drop-down list. Then set the join kind by clicking the red-colored join kind and selecting a required item from the shortcut menu. You can remove a join by clicking its FilterGreyDel button.

To edit join properties, use the toolbar, connector shortcut menu, or Joins tab of the tabbed editor.

To change a join kind, use the select-all-tables-left Select all rows from "[table]" and select-all-tables-right Select all rows from "[table]" buttons on the Query toolbar.

To remove a join, select the connector, then either right-click on it and choose Remove from Diagram or hit the DELETE key.

note Note

Joins are automatically created when a table with foreign key and its referenced table is placed on the diagram.

ExpandedToggleIcon(9x12) Working with join conditions and condition groups

The tabbed editor shows joins as a tree with join, condition and group nodes. A join consists of conditions and condition groups. A group consists of several conditions or subgroups, united with a logical operator. You can change a group logical operator by clicking it and selecting another one from the menu. To navigate between conditions and groups, use the UP ARROW and DOWN ARROW keys. Use the LEFT ARROW and RIGHT ARROW keys to navigate between a criteria operator and operands.

To create a new group, click the upper-level group operator or join kind and choose Add Group from the appeared menu.

To create a new condition, do the following:

1. Navigate to the group or the join you want to enhance and click the FilterGreyAdd button or press the INSERT or PLUS SIGN key. Alternatively, click the upper-level group operator and choose Add Condition from the appeared menu.
2. Click the condition column and select a column you need from the appeared menu.
3. Click the condition criteria operator and select a criteria operator you want to apply.
4. If the condition operator requires operand value(s), specify it (them).

To remove a condition, click the FilterGreyDel condition button or navigate to the condition and press the DELETE or MINUS SIGN key. To remove a group, click its logical operator and choose Remove Group from the appeared menu or navigate to the group and press the DELETE or the MINUS SIGN key.

You also can copy, cut, and paste conditions and groups with CTRL+C, CTRL+X, and CTRL+V keys.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Build WHERE or HAVING Clause


How To: Build WHERE or HAVING Clause

When retrieving data, you may want to set up a filtering expression or relation between tables. You can do it using WHERE and HAVING clauses. Refer to SQL manual to learn what's different in these sections. In Query Builder for SQL Server visual interface for WHERE and HAVING clauses is the same.

In the tabbed editor conditions are displayed as a tree with condition and group nodes. A group consists of several conditions or subgroups, united with a logical operator. You can change a group logical operator by clicking it and selecting another one from the menu. To navigate between conditions and groups, use the UP ARROW and DOWN ARROW keys. Use the LEFT ARROW and RIGHT ARROW keys to navigate between criteria operator and operands.

To create a new group, do one of the following:

· Click the upper-level group operator and choose Add Group from the appeared menu.
· Press CTRL+PLUS SIGN.

ExpandedToggleIcon(9x12) Building WHERE and HAVING Clause

To create a new condition, do the following:

1. Navigate to the group you want to enhance and click the FilterGreyAdd button or press INSERT or PLUS SIGN key. Alternatively, click the upper-level group operator and choose Add Condition from the appeared menu.
2. Click the condition column and select the column you need from the appeared menu.
3. Click the condition criteria operator and select the criteria operator you want from the list.
4. If the condition operator requires operand value(s), specify it (them).

To remove a condition, click the FilterGreyDel condition button or navigate to condition and press DELETE or MINUS SIGN key. To remove a group click its logical operator and choose Remove Group from the appeared menu or navigate to the group and press DELETE or MINUS SIGN key.

You also can copy, cut, and paste conditions and groups with CTRL+C, CTRL+X, and CTRL+V keys.

The following clause is generated according to the sample image:

WHERE
Product.Color = 'silver' AND
ProductCategory.Name >= 'components'

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Include Fields in GROUP BY or ORDER BY Clauses


How To: Include Fields in GROUP BY or ORDER BY Clauses

When you need to group resulting data, you must specify the field you want to group on in GROUP BY clause. When you want to sort data by a field, you must include the field in ORDER BY list. In Query Builder for SQL Server, the interface for this clauses is approximately the same. The only difference is that ORDER BY tab has the Change Order button that you can use to alter sorting direction.

ExpandedToggleIcon(9x12) Including Fields in ORDER BY Clause

To include fields in ORDER BY clause, switch to the corresponding tab of the tabbed editor and perform the following steps:

1. Select the field you want to perform grouping/ordering on and click the right arrow button.
2. Repeat this operation for every field that should be added to the clause.
3. Use the up arrow and down arrow buttons to change the order of columns.
4. If you want to exclude a field from the list, select it and click the left arrow button.
5. For ordering lists, select a field and click the Change Order button to alter the sorting order.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

Building a Query in Visual Mode


Building a Query in Visual Mode

In this walkthrough you will learn how to use Query Builder in generating SELECT statements.

ExpandedToggleIcon(9x12) Retrieving Data from Tables Using Query Builder

Suppose you need to know what department the employee belongs to. To accomplish this task, it is needed to get employee's name and department internal code from one table (Emp) and name of the department from another table (Dept).

Creating Inner Join between Two Tables

1. In the Database Explorer make sure that connection to your server containing the base ("Demobase") is active.
2. On the Start page click NewQuery New Query. You can also click the NewQueryDataStudio New Query button on the Standard toolbar which will open a blank query document.
3. Drag the table Emp from the Database Explorer onto the query diagram of the document. A table shape should appear containing a list of table's columns. Do the same for Dept.
4. Select the EName check box field in the Emp table shape and the DName check box field in the Dept table shape to include them into the SELECT list. These fields appear in the Selection tab of the tabbed editor below the query diagram.
5. Drag the field DeptNo from the Emp table shape onto the same field at the Dept table shape. A connector between two tables appear, indicating the relation has been established.
6. Switch to the Joins tab of the tabbed editor to make sure the relation has been established properly. You can see "Emp.DeptNo=Dept.DeptNo" string.
7. Click the [image] Execute button on the Query toolbar to perform the query.
EName | DName
Smith | Sales
Allen | Sales2
Ward | Sales2
Jones | Sales
Martin | Sales2
Blake | Sales2
King | Accounting

Creating an Outer Join Between Two Tables

Now when data from several tables can be retrieved at the same query, let's have a closer look at the SQL statement that was just composed. A distinctive feature of the INNER JOIN is that if an employee has DeptNo, which is never encountered in the Dept table, then this employee will not be listed in the query result. You can set value of DeptNo to other than 10, 20 or 30 to make sure that after performing the same query the corresponding entry disappears from the returned data. An OUTER JOIN should be used to save the situation.

To create an OUTER JOIN:

1. Right-click the connector between two tables. The popup menu appears.
2. Estimate the position of table shapes on the query diagram. If the table shape representing table Emp is to the left of the connector, choose Select all rows from "Emp" from the popup menu. Notice that the middle of the connection has changed its form.
3. Switch to the Joins tab of the tabbed editor to see the changes. To click the red-colored join kind and to choose Left Outer Join from the popup menu is an alternative way to set up the relation.
4. Click the [image] Execute button on the Query toolbar to execute the query.

The result of this query should be exactly as of previous. To see how the outer join works, change a value of DeptNo in either of the tables to a unique value and re-execute the query.

Customizing a Query Using Query Builder

There are six clauses in the SELECT statement and for every clause there is a tab in the tabbed editor. The tabbed editor is the easiest way to adjust a query to your needs. Now the previous query should become a bit more complex. Suppose it is needed to find out how much money employees earn in each department. Let's exclude a president from the list and sort it by money amount in descending order.

1. Switch to the Design view of the query that was built in the previous part of the walkthrough.
2. In the Emp table shape, unselect the EName field to exclude it from SELECT list.
3. Switch to the Selection tab of the tabbed editor. Click the first empty cell and select Emp.Sal (SAL (emp)) field from the drop-down list.
4. In the same row, click the column Alias and enter a pseudonym for the field. Let it be "Money".
5. In the same row, click the Function column and choose Sum from the drop-down list. Pay attention to the changes that occurred in the table shape related to the Emp table.
6. On the Where tab of the tabbed editor, click the FilterGreyAdd button.
7. Click the left <enter a value> field, then select Emp.Job from the drop-down list.
8. Click the condition operator ('=') and choose does not equal from the drop-down list.
9. Click the right <enter a value> field and type 'President'.
10. On the Group By tab, select the field Dept.DName and click the RightArrowButton right arrow button. Use up arrow and down arrow buttons to change order of columns. If you have missed and added a wrong field to the Group By list, select it and click on the left arrow button. You should not do anything at the Having tab. It is absolutely similar to the Where tab.
11. On the Order By tab, click Money, then click the RightArrowButton right arrow button.
12. Select this field in the Ordered By list and click the ChangeOrder Change Order button. Notice what happened to table shapes after all these manipulations.
13. At any moment you can switch to the Text view of a document to inspect a query you have built using Query Builder. To do it, click on the Text button at the bottom part of a document. If the Text button is not visible, use a corresponding item in the popup menu. Another way to change document's view is to use commands from the View menu.
14. In either Design or Text view, click the the [image] Execute button.

If you have followed the instruction correctly and used source data provided in the previous topic, the following table should appear:

DName | Money
Sales2 | 6950
Sales | 3775

If you have made changes to the tables involved in the query, your results may differ.

note Note

Depend on the product you use the retrieval data may differ from this example.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

Working with Different Query Types


Working with Different Query Types

Query Builder for SQL Server gives you an ability to simplify your work with different types of SQL queries. Using these types, you can manipulate or control your data to your profit.

ExpandedToggleIcon(9x12) Query Types

The current type is specified on the label in the left upper part of any query document. To change it, use the Change type list on the toolbar or the Change type item in the shortcut menu of the document.

Name | Description
query-label-select SELECT | Use this type to create queries with the SELECT operator.
query-label-insert-results INSERT RESULTS | Use this query type to create queries with the INSERT INTO operator. Click the ellipsis button on the label to select the needed table or click the name of the selected table on the label to change it. Specify the needed values and conditions on the tabs below the diagram.
query-label-insert-values INSERT VALUES | Use this query type to create queries with the INSERT INTO operator. Click the ellipsis button on the label to select the needed table or click the name of the selected table on the label to change it. Specify the name and value you want to insert in the Insert tab.
query-label-update UPDATE | Use this query type to create queries with the UPDATE operator. Click the ellipsis button on the label to select the needed table or click the name of the selected table on the label to change it. Specify the needed values on the tabs below the diagram.
query-label-delete DELETE | Use this query type to delete records from the selected table. Click the ellipsis button on the label to select the needed table or click the name of the selected table on the label to change it. Specify the needed condition on the Where tab under the diagram.

note Note

You can add only one row to a table in one query.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

Working with Query Diagram


Working with Query Diagram

This section provides information about managing shapes on the query diagram.

ExpandedToggleIcon(9x12) In This Section

Diagram Overview
Provides basic information about the diagram.

Diagram Layout
Describes how to layout the diagram automatically and change shape order.

Aligning Shapes
Describes how to align shapes on the diagram.

Changing Shape Size
Describes shape size changing.

Controlling Spacing between Shapes
Describes how to change spacing between shapes.

Diagram Shortcuts
This section describes shortcuts for query builder diagram.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

Working with Subqueries


Diagram Overview

Query Builder diagram allows you to view and edit visual query representation. Table shapes on the diagram represent tables, views and subqueries. Joins are represented as connections between table shape fields.

Table controls can be moved (by dragging them with mouse or by keyboard), resized, aligned to the diagram grid. You also can zoom in and out of the diagram using either CTRL+Mouse Wheel or Zoom In and Zoom Out buttons on the Query toolbar. Use the Actual Zoom button to return to actual zoom.

The order of overlapping shapes can be controlled using the Bring to Front and Send to Back buttons of the Layout toolbar or use corresponding items of the Order submenu of the Layout menu.

You may control diagram layout both with mouse and keyboard. All operations can be executed with keyboard.

Diagram can be customized on theGeneral, Query Builder Options dialog box. You can enable or disable diagram grid and grid snapping, set grid step, enable or disable table shape shadow, and set diagram background color.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

Diagram Layout


Diagram Layout

Diagram layout can be controlled in a number of ways. Diagram shapes can be moved (by dragging them with mouse or by keyboard), resized, aligned to the diagram grid. Read Aligning Shapes and Changing Shape Size topics for more information.

Query Builder for SQL Server allows you to layout a diagram automatically or manually. To resize the diagram automatically, just click the Layout Diagram button on the Diagram toolbar.

You may change shape order either by right-clicking them and choosing Bring to Front or Send to Back from the Shape sub menu of the shortcut menu, or by using the corresponding buttons on the Layout toolbar.

ExpandedToggleIcon(9x12) See Also

Working with Query Diagram

Aligning Shapes


Aligning Shapes

Each shape may be aligned to the grid. To align a shape to the grid, select it and click the Align to Grid button on the Layout toolbar.

Several shapes can be aligned in a column or in a row.

· To align in a row, select shapes and click the Align Tops, Align Middles, or Align Bottoms button on the Layout toolbar to align the shapes by their top, center, or bottom of the primary selection respectively. Their horizontal position remains unchanged.
· To align in a column, select shapes and click the Align Rights, Align Lefts, or Align Centers button to align the shapes by their right side, left side, or center of the primary selection respectively. Their vertical position remains unchanged.

You may also use corresponding commands of the Align sub menu of the Layout menu.

ExpandedToggleIcon(9x12) See Also

Working with Query Diagram

Changing Shape Size


Changing Shape Size

To change shape size, select it and drag the square resize markers at the shape sides and corners. To fit shape size to grid, select it and click the Size to Grid button on the Layout toolbar or select Size to Grid from the Make Same Size submenu of the Layout menu. Shapes have minimal and maximal height and width. Minimal width and height are constant. Maximal width depends on the length of the name of the longest column in a table shape. Maximal height depends on the table shape column count.

You can also fit the same size for multiple shapes. To do this, select the shapes and click the Make Same Width or Make Same Height buttons on the Layout toolbar. You may also use corresponding items of the Make Same Size submenu of the Layout menu. When using this feature, the size of the selected shapes will be aligned with the size of primary selection.

note Note

A primary selection is one of the selected shapes. Its resize markers are blue, while resize markers of other selected shapes are black. If a selected shape cannot have the same size as the primary selection, its size will be changed to become as close to the primary selection size as possible.

ExpandedToggleIcon(9x12) See Also

Controlling Spacing between Shapes

Controlling Spacing between Shapes


Controlling Spacing between Shapes

Diagram provides flexible control over spacing between shapes on the diagram. You can proportionally increase and decrease horizontal and vertical spacing between the shapes, make it equal or remove it.

· To increase spacing between shapes, select shapes, which will be moved, and click Increase Horizontal Spacing or Increase Vertical Spacing on the Layout toolbar or choose Increase from the Horizontal Spacing or Vertical Spacing submenu of the Layout menu.
· To decrease spacing between shapes, select shapes, which will be moved, and click Decrease Horizontal Spacing or Decrease Vertical Spacing on the Layout toolbar or choose Decrease from the Horizontal Spacing or Vertical Spacing submenu of the Layout menu.
· To make spacing between shapes equal, select shapes, which will be moved, and click Make Horizontal Spacing Equal or Make Vertical Spacing Equal on the Layout toolbar or choose Make Equal from the Horizontal Spacing or Vertical Spacing submenu of the Layout menu.
· To remove spacing between shapes, select shapes, which will be moved, and click Remove Horizontal Spacing or Remove Vertical Spacing on the Layout toolbar or choose Make Equal from the Horizontal Spacing or Vertical Spacing submenu of the Layout menu.

note Note

When increasing or decreasing spacing or making it equal, spacing is considered as distance between centers of shapes. But when removing spacing it is considered as distance between shape borders. When you remove, for example, vertical spacing, shapes will be located in the following way: the top of the second shape will be located at the same height as the bottom of the highest shape, the top of the third one will be located at the same height as the bottom of the second shape and so on.

ExpandedToggleIcon(9x12) See Also

Working with Query Diagram

Diagram Shortcuts


Diagram Shortcuts

This section contains shortcuts for query builder diagram.

ExpandedToggleIcon(9x12) In This Section

Diagram Shortcuts Overview
This topic explains how to use shortcut keys to perform certain operations using keyboard.

Focus and Selection Shortcuts
Contains shortcuts for moving focus and selecting shapes or columns.

Zoom and Scroll Shortcuts
Contains shortcuts for moving and zooming the diagram sheet.

Shape and Column Managing Shortcuts
Contains shortcuts for manipulating shapes and columns.

ExpandedToggleIcon(9x12) See Also

Working with Query Diagram

Working with Subqueries


Diagram Shortcuts Overview

Diagram allows you to speed up your work with keyboard shortcuts. This topic explains how to use them to perform certain operations using keyboard.

Some diagram shortcuts can be customized.

ExpandedToggleIcon(9x12) Moving Focus and Selecting Shapes

To switch between shapes, use TAB (SHIFT+TAB) keys. TAB key moves focus and selection forward, SHIFT+TAB moves it backward. To select multiple shapes, move focus with CTRL+ALT+TAB or SHIFT+CTRL+ALT+TAB shortcut and add required shapes to selection with the SPACE key (these shortcuts are not available in Windows 7).

ExpandedToggleIcon(9x12) Moving and Resizing Shapes

To move selected shapes with snapping to the grid, use arrow keys. To move the shapes one pixel, press CTRL+arrow keys. To resize the shapes, press SHIFT+arrow keys or CTRL+SHIFT+arrow keys. To collapse/expand a table control, use ALT+RIGHT ARROW/ALT+LEFT ARROW keys.

ExpandedToggleIcon(9x12) Bookmarking a Viewing Area of the Diagram

You can bookmark the current viewing area of the diagram by pressing CTRL+SHIFT+{0-9}. Then you can scroll the sheet or change the zoom, but quickly come back to the saved viewing area by pressing CTRL+{0-9}.

ExpandedToggleIcon(9x12) Working with Columns

To add columns to the query:

1. Navigate to a table.
2. Switch to its columns with SHIFT+PERIOD keys.
3. Use arrow keys to navigate to the column to add to query.
4. Press ENTER to add column
5. Repeat steps 3-4 for all columns you want to add
6. Press the ESC key to move focus from the columns to the table.

ExpandedToggleIcon(9x12) Viewing Diagram

To scroll the diagram, switch focus to it with the ESC or SHIFT+COMMA keys and use arrow keys. You may also use PAGE UP and PAGE DOWN keys. To zoom in and out use CTRL+PLUS SIGN and CTRL+MINUS SIGN keys.

ExpandedToggleIcon(9x12) Other Shortcuts

You can assign shortcuts for all commands from the layout menu. These commands don't have shortcuts by default. Use the Keyboard page of the environment options to assign shortcuts. Type Diagram in the Show Commands Containing field to filter commands.

ExpandedToggleIcon(9x12) See Also

Diagram Shortcuts

Focus and Selection Shortcuts


Focus and Selection Shortcuts

Use following shortcut keys for changing focus and selection on the diagram.

Shortcut Keys | Action | Where is used
Ctrl+A | Selects all | Diagram
TAB | Moves focus and selection forward | Diagram
SHIFT+TAB | Moves focus and selection backward | Diagram
CTRL+ALT+TAB | Moves focus forward without moving selection (this shortcut is not available in Windows 7) | Diagram
CTRL+SHIFT+ALT+TAB | Move focus backward without moving selection (this shortcut is not available in Windows 7) | Diagram
HOME | Moves focus and selection to the first item (shape or column) | Diagram
END | Moves focus and selection to the last item (shape or column) | Diagram
SPACE | Toggle selection of the focused item | Diagram
UP ARROW | Moves focus and selection to the previous column or stored routine parameter | Diagram
DOWN ARROW | Moves focus and selection to the next column or stored routine parameter | Diagram
CTRL+UP ARROW | Moves focus without selection to the previous column or stored routine parameter | Diagram
CTRL+DOWN ARROW | Moves focus without selection to the next column or stored routine parameter | Diagram
SHIFT+UP ARROW | Moves focus to the previous column or stored routine parameter. Adds it to selection if the first selection column or stored routine parameter is below, otherwise, removes selection from the current column or stored routine parameter. | Diagram
SHIFT+DOWN ARROW | Moves focus to the next column or stored routine parameter. Adds it to selection if the first selection column or stored routine parameter is above, otherwise, removes selection from the current column or stored routine parameter. | Diagram
CTRL+SHIFT+UP ARROW | Moves focus to the previous column or stored routine parameter and extends the selection on it | Diagram
CTRL+SHIFT+DOWN ARROW | Moves focus to the next column or stored routine parameter and extends the selection on it | Diagram
SHIFT+PERIOD | Moves focus to the lower level | Diagram
SHIFT+COMMA | Moves focus to the upper level | Diagram
ESC | Moves focus to the upper level | Diagram
ALT+DOWN ARROW | Moves focus to the table shape name if the focus was on the table shape. Table shape expands if it was previously collapsed. | Diagram

ExpandedToggleIcon(9x12) See Also

Diagram Shortcuts

Zoom and Scroll Shortcuts


Zoom and Scroll Shortcuts

Use following shortcut keys for working with the diagram sheet.

Icon | Command Name | Shortcut Keys | Action | Where is used
 | Zoom In | CTRL+PLUS SIGN | Zoom in | Diagram
 | Zoom In | CTRL+MouseWheel forward | Zoom in | Diagram
 | Zoom Out | CTRL+MINUS SIGN | Zoom out | Diagram
 | Zoom Out | CTRL+MouseWheel backward | Zoom out | Diagram
 |  | UP ARROW | Scroll the sheet up when the sheet is focused | Diagram
 |  | DOWN ARROW | Scroll the sheet down when the sheet is focused | Diagram
 |  | LEFT ARROW | Scroll the sheet left when the sheet is focused | Diagram
 |  | RIGHT ARROW | Scroll the sheet right when the sheet is focused | Diagram
 |  | PAGE UP | Scroll the sheet one page up | Diagram
 |  | PAGE DOWN | Scroll the sheet one page down | Diagram
 |  | CTRL+SHIFT+{0-9} | Bookmark a current viewport (a visible region of the diagram) | Diagram
 |  | Ctrl+{0-9} | Return to a bookmarked viewport | Diagram

ExpandedToggleIcon(9x12) See Also

Diagram Shortcuts

Shape and Column Managing Shortcuts


Shape and Column Managing Shortcuts

Use following shortcut keys to manipulate shapes and columns on the diagram.

Shortcut Keys | Action | Where is used
UP ARROW | Moves the selected shapes one grid step up if some shapes are selected | Diagram
DOWN ARROW | Moves the selected shapes one grid step down if some shapes are selected | Diagram
LEFT ARROW | Moves the selected shapes one grid step left if some shapes are selected | Diagram
RIGHT ARROW | Moves the selected shapes one grid step right if some shapes are selected | Diagram
CTRL+UP ARROW | Moves the selected shapes one pixel up if some shapes are selected | Diagram
CTRL+DOWN ARROW | Moves the selected shapes one pixel down if some shapes are selected | Diagram
CTRL+LEFT ARROW | Moves the selected shapes one pixel left if some shapes are selected | Diagram
CTRL+RIGHT ARROW | Moves the selected shapes one pixel right if some shapes are selected | Diagram
SHIFT+UP ARROW | Decreases the height of the selected shapes one grid step if some shapes are selected | Diagram
SHIFT+DOWN ARROW | Increases the height of the selected shapes one grid step if some shapes are selected | Diagram
SHIFT+LEFT ARROW | Decreases the width of the selected shapes one grid step if some shapes are selected | Diagram
SHIFT+RIGHT ARROW | Increases the width of the selected shapes one grid step if some shapes are selected | Diagram
CTRL+SHIFT+UP ARROW | Decreases the height of the selected shapes one pixel if some shapes are selected | Diagram
CTRL+SHIFT+DOWN ARROW | Increases the height of the selected shapes one pixel if some shapes are selected | Diagram
CTRL+SHIFT+LEFT ARROW | Decreases the height of the selected shapes one pixel if some shapes are selected | Diagram
CTRL+SHIFT+RIGHT ARROW | Increases the height of the selected shapes one pixel if some shapes are selected | Diagram
ALT+LEFT ARROW | Expands the shape or table compartment | Diagram
ALT+LEFT ARROW | Collapses the shape or table compartment | Diagram
ENTER | Opens editor for the selected objects | Database Diagram
ENTER | Includes selected columns to the query if some columns are selected | Query Builder Diagram
DELETE | Excludes the selected shapes from the diagram when shapes are selected | Diagram
DELETE | Drops the selected columns, constraints, or triggers from database when table columns, constraints, or triggers are selected. | Database Diagram
CTRL+DELETE | Drops the selected database objects from the database | Database Diagram

ExpandedToggleIcon(9x12) See Also

Diagram Shortcuts

Working with Subqueries


Working with Subqueries

This section describes how to visually create subqueries in Query Builder for SQL Server.

ExpandedToggleIcon(9x12) In This Section

Subqueries Overview
Contains basic information on working with subqueries.

Subqueries in From Clause
Describes how to add subqueries to FROM clause.

Subqueries in Other Clauses
Describes how to add subqueries in SELECT, WHERE, and HAVING clauses.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

Navigating Query with Document Outline


Subqueries Overview

Query Builder for SQL Server offers full support for subqueries in all SELECT statement clauses. Each subquery can have its own subqueries.

When you create a subquery or open it for visual editing, a subquery tab of the query document appears. This offers you simple navigating subqueries using these tabs. You can navigate to the upper-level query from the subquery with the ParentQuery Parent Query button on the Query toolbar. You can also use the Document Outline window to navigate the complex queries.

note Note

If you want to include a table along with joins, conditions, and subqueries into a subquery, you have to set focus on the whole diagram area instead of the particular table and perform the Wrap to Subquery operation for your diagram.

ExpandedToggleIcon(9x12) See Also

Working with Subqueries

Subqueries in From Clause


Subqueries in From Clause

Subqueries in the FROM clause are represented as table controls on the diagram. They can be treated exactly as tables.

ExpandedToggleIcon(9x12) Creating Subqueries

To create a new subquery:

In the query document, do either of these actions to open the SubQuery tab:

· Right-click the diagram and choose Create Subquery from the shortcut menu
· Click the Create Subquery button on the Query toolbar
· Select Create Subquery from the Query menu

The SubQuery tab appears where you can create the subquery - add a table, select required columns, specify conditions, etc.

You can include a table into a subquery in two clicks. Just right-click a table on the diagram, and select Wrap to Subquery item from the menu. The table shape will change its color and the subquery will be added to the query. You can add an alias to your subquery by typing it into the caption of subquery table shape. When the subquery is created, you can switch to the Text view to check the code.

To quickly navigate to your main query and subquery, use the tabs of the query document or open the Document Outline window.

To edit a subquery, click the Query3PButton button on the subquery title bar or right-click the subquery and select Edit Subquery from the shortcut menu.

To rename a subquery, double-click its title and enter a required alias.

ExpandedToggleIcon(9x12) See Also

Working with Subqueries

Subqueries in Other Clauses


Subqueries in Other Clauses

ExpandedToggleIcon(9x12) Subqueries in SELECT, WHERE, and HAVING Clauses

Subqueries in these clauses are highlighted like hyperlinks. You can add subqueries to these clauses by typing them instead of columns. To open such subquery for visual editing, click them holding the CTRL key. Subqueries in the SELECT clause may be renamed by entering name into the Alias column.

ExpandedToggleIcon(9x12) Subqueries in GROUP BY and ORDER BY Clauses

Subqueries in these clauses should always return only one value (i.e. one row having one column). To add such subquery, type it into the expression box and press ENTER. You can open them for visual editing only by navigating to them with Document Outline.

ExpandedToggleIcon(9x12) Subquery in SELECT clause

To add a subquery to SELECT clause:

1. In the query document go to the Selection tab of the tabbed editor (it is placed under the diagram) and click the field in the Column field. The operand editor opens.
2. Click the CreateSubQuery Create Subquery button. The program inserts a subquery template into the condition and automatically moves you to the subquery tab of the query document.

ExpandedToggleIcon(9x12) Subquery in WHERE and HAVING Clauses

To add a subquery to WHERE and HAVING clauses:

1. In the query document create a condition on the WHERE or HAVING tab of the tabbed editor and click the CreateSubQuery Create Subquery button in the Operand editor.

The program inserts a subquery template into the condition and automatically moves you to the subquery tab of the query document.

2. Drag-and-drop a table to the subquery and add the subquery conditions. The subquery template will be populated with the added parameters.

ExpandedToggleIcon(9x12) See Also

Working with Subqueries

Navigating Query with Document Outline


Navigating Query with Document Outline

Use the Document Outline window to quickly navigate to the root query and clauses containing subqueries. If the Document Outline window is not visible, click the View menu, and then click Document Outline. The Document Outline window contains nodes, representing all the query clauses, selected columns, tables and views in the From clause, and all the subqueries on any nesting level. Double-click on the clause node activates the corresponding Query Builder tab for the corresponding subquery or the root query. Double-click on the subquery node opens the corresponding subquery for editing. Double-click on the table column or table node selects the corresponding column or table.

The Working with Subqueries window represents the root query and clauses (SELECTION, FROM, WHERE, and HAVING) containing subqueries as nodes in the tree. Double-click the nodes to go to the corresponding query tabs.

ExpandedToggleIcon(9x12) See Also

Working with Subqueries

Building Queries with Query Builder


Data Export

This section describes how to export data using the Data Export wizard.

ExpandedToggleIcon(9x12) In This Section

Data Export Overview
Gives the essential information to start exporting data.

How To: Export Data
Describes how to export data.

Saving and Using Templates
Tells how to save export settings into templates and save time.


Data Export Overview

With Query Builder for SQL Server you can easily export data from one or several tables to the following file formats:

· HTML (Hyper Text Markup Language)*- a data storage format used for the web pages.
· XLS - Microsoft Excel spreadsheet.
· XLSX - Microsoft Excel 2007 spreadsheet.

note Note

If you do not have Microsoft Office installed, you need to install Open XML SDK 2.5 for Microsoft Office to use the result Microsoft Excel spreadsheet.
· RTF - Rich Text Format from Microsoft.
· JSON - an open-standard format that uses human-readable text to transmit data objects consisting of attribute-value pairs.
· XML (XML Schema Definition) - XML data representation with data contained in attributes. Contains XML schema.
· ODBC (Open Database Connectivity)* - a data storage format used to access any data from any application.
· Text - plain text format.
· MS Access - database of well-known Microsoft DBMS Access (2003, 2007+).

note Note

If you do not have Microsoft Office installed, you need to install Microsoft Access Database Engine to use the result MS Access file.
· PDF (Portable Document Format) - file format, created by Adobe Systems for document exchange. (Only Latin charset data can be exported.)
· CSV (Comma-separated values)* - another name for the comma-delimited format of data storage.
· DBF - a database filename extension (Foxpro, dBase III, dBase IV, dBase 7). Other files can also be connected with DBF files, for example: .DBT (dBase III, IV, Clipper), .FPT (FoxBASE/FoxPro). While exporting data, ANSI charset is used.
· SQL - format of the file that contains a SQL script.

note Note

When using text/csv ODBC provider, on the Export Format page enter a file name with an extension in the Destination table name field, e.g., 'table.csv'.

note Note

When exporting data to MS Excel format through ODBC provider, the following export limits exist:

· for export to MS Excel 2003 format - 65536 records
· for export to MS Excel 2007 format - 1 048 576 records

Data export to MS Excel (not through ODBC provider) is limited only by available system memory.

Query Builder for SQL Server gives a simple way to export data both from Database Explorer and the grid. Using Database Explorer, you can select any table for export in seconds. Also you can compose a complex query, execute it, and then select exported data from the grid.

Query Builder for SQL Server includes the Data Export Wizard which allows you to select the following:

· columns
· data formats for each column
· rows
· additional options for each exported format

Based on your needs, you can save your selected export options into a template file for the further usage in the wizard.

ExpandedToggleIcon(9x12) See Also

Data Export

How To: Export Data


How To: Export Data

ExpandedToggleIcon(9x12) Export data to other formats

1. Open the Data Export wizard by doing either of these actions:
· In Database Explorer right click the required table or view (or CTRL+click several tables and/or views) you want to export data from and select Export Export Data from the shortcut menu.
· Right-click data grid and choose Export Data from the shortcut menu, or click the DataExport Export Data button on the Data Editor window toolbar.
· Click DataExport Export Data on the Database menu.

note Note

The appearance of the Data Export Wizard can differ depending on a product you use.

2. On the Export format page, select an export format or load export options from a template file if you saved them previously and click Next.
3. On the Source page, select a required SQL Server server connection, a database, table(s), view(s), which you want to export, and then click Next.

note Note

If you opened the wizard from the data grid, the Source tab would allow to specify only a name and a path of the destination file (the one where the exported data will be saved.
4. Set additional settings for the selected export format on the Options wizard page and click Next.
5. Select exported columns and click Next. (For CSV, HTML, PDF, RTF, and Text export formats, select formats of data representation, e.g., integer, date, time, etc. For Excel and Excel 2007 formats, the field is editable only for binary columns and includes only two data types (HexString and String) in the drop-down list).
6. Select exported rows. You can export all rows, only selected in the grid ones, or a specified range.
7. Specify how the application should behave when an error is encountered.
8. (Optional) To export options to a template file for future usage, click the Save Template button and specify a path and a name of the template file.
9. Define how dbForge Query Builder for SQL Server should handle errors during export and whether to write a log file. .
10. Click the Export button and see the result of export process.
11. Select whether to open the exported file or result folder, and click the Finish button. Data export is finished.

ExpandedToggleIcon(9x12) Filtered data export

There is a filter in the Data Export wizard window. It is not used to filter exporting tables and views but to ignore them while data exporting.

If you want to filter data, use the required column filter. To do this, firstly write a whole data query using the SELECT * FROM statement. Click the Execute button to implement the query. Then on the required column name, click the filter icon. In the drop-down list, choose the item to filter by. Right click the filtered grid, and select Export Data to call the Data Export wizard.

Another and the most reliable way to export filtered data is to filter it directly in the query. Execute the SELECT * FROM statement with the WHERE condition, where you can specify the required filter. And then implement already filtered data export with the Data Export wizard, calling it from the filtered grid shortcut menu.

Follow the Data Export wizard steps, click the Export button. If the data export is finished successfully, the Data export complete message appears.

ExpandedToggleIcon(9x12) See Also

Data Export

Saving and Using Templates During Data Export


Saving and Using Templates During Data Export

Template is a set of export settings stored in a file.

It's no use wasting your time on selecting the same settings each time you export data. Select once, save multiple settings to a file, and use it to have customized export without efforts.

For example, you need to export data from an existing table to text format once a day. You can set up the export options once, and next time all you need to do is to select the Text export format and a location of the data source and click Export. To illustrate the case, let's export data from the sales database to the sales_export.txt file, create a template, and then use it.

To create a template:

1. Click DataExport Export Data on the Database menu.


Working with Data in Data Editor

ExpandedToggleIcon(9x12) In This Section

Viewing Data in Grid
Describes how to use multiple grid editor's features to customize the data display and get more insight.

Editing Data in Grid
Describes how to quickly edit data using various grid editor's features.

Data Shortcut Keys
Lists useful shortcut keys for working with data.


Viewing Data in Grid

This section describes features for viewing data in dbForge Query Builder for SQL Server.

ExpandedToggleIcon(9x12) In This Section

Viewing Data in Grid Overview
Tells how to efficiently view data in Query Builder for SQL Server.

Sorting Data in Grid
Tells how to sort data in the grid.

Grouping Data in Grid
Tells how to use data grouping.

Filtering Data in Grid
Tells how to apply various filter conditions to filter data in the grid.

Server-side and Client-side Sorting and Filtering Data in Grid
Explains how sorting and filtering are processed on the client side and on the server side. Gives a list of problems that may be encountered.

Paginal Mode
Teaches how to use paginal mode to limit row count in the grid.

Auto-search Mode
Teaches how to use auto-search mode to quickly locate data.

Card View
Describes how to use card mode for graphical data representation.

ExpandedToggleIcon(9x12) See Also

Working with Data in Data Editor

Editing Data in Grid


Viewing Data in Grid Overview

The Data Editor allows you to view and quickly edit data right in the grid. For more information on editing data refer to topic Editing Data in Grid.

ExpandedToggleIcon(9x12) To Access Data

· Execute a query that returns data.
· Open the table or view editor and switch to the data view.

Use the rich set of data management options in the context menu.

note Note

Actual document look may slightly vary from the above image, depending on the product you use.

note Note

If you want to retrieve all data from a table with a large number of records, a message warning you about the possibility of the "Out of memory" exception is shown, and you can choose either of the following actions - continue retrieving data, cancel retrieving data, or turn on Paginal Mode.

To simplify data analysis, you can use the following features:

· Grouping Data in Grid
· Sorting Data in Grid
· Filtering Data in Grid
· Paginal Mode
· Card View
· Auto-search Mode
· Data Viewer

note Note

By default, Paginal Mode is enabled in data view with 1000 records per page, so only 1000 records are displayed at once. For more information, see Paginal Mode.

You can change columns order and width, group by order via dragging column headers into required place. You can fit the column's width to the column content by right-clicking on an appropriate header and choosing Best Fit from context menu. To adjust widths of all columns at once, choose Best Fit (all columns) instead. Note that this operation can take some time when there are many records in the grid.

To refresh data, click the Refresh command in the Data menu (for Data view), the context menu, or on the toolbar. To abort too long refresh operation, click the Stop Refresh command in the Data menu (for Data view) or on the toolbar.

Grid rows' background and foreground colors can be set. You can set them separately for odd and even rows, focused row, selected rows, focused cell, group rows.

Data row can be viewed, edited and printed as cards. To learn more about it, visit the Card View topic.

Query Builder for SQL Server allows you to view data as text. To retrieve data as text click Redirect Data to Output Window on the SQL toolbar, switch to the Data tab in the Output window and execute the query to get data. You cannot edit data in the Output window.

Tip: you can use down arrow to add an empty row to the table displayed in the grid. If you've added an empty row accidentally, you can easily delete it using the up arrow.

note Note

Null values 0000-00-00 in fields of DATE data type are displayed as 01.01.0001. Null values of TIMESTAMP fields are displayed as 01.01.0001 0:00:00.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Sorting Data in Grid


Sorting Data in Grid

By default, Query Builder for SQL Server retrieves unsorted data from tables if the Retrieve Data option was used. If data was retrieved after executing an SQL statement, it will be sorted according to the operators used in this statement (in case operators are sorting-related, e. g. ORDER BY).

To sort data

To sort data in grid or to change the sort order, click the header of a column. Alternatively, right-click a column header, and then click Sort Ascending or Sort Descending on the shortcut menu.

To assign a secondary sorting, click another column header, while holding down the SHIFT key. You can set as many sorting columns as you need. To stop sorting on a certain column, click it while holding the CTRL key.

To clear column sorting, click the Clear Sorting option on the table shortcut menu.

The current sort order of a column is indicated by the sort glyph (a small arrow displayed at the column header's right edge). If data is sorted in ascending order, the sort glyph represents an up-arrow. When sorting in descending order, the sort glyph is displayed as a down-arrow. If the column isn't sorted, the sort glyph is hidden.

note Note

Actual document look may slightly vary from the above image, depending on the product you use.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Grouping Data in Grid


Grouping Data in Grid

To group data by column, click ShowGroupByBox Show Group by Box on the document toolbar. The Group by Box pane will appear above the grid. Drag a column header you want the data to be grouped by to the pane.

Alternatively, right-click the required column header and click Group By This Column on the shortcut menu. The data is automatically displayed in groups marked with collapsed nodes. The column header is moved to the Group By Box pane.

note Note

Actual document look may slightly vary from the above image, depending on the product you use.

Group By Box is the area above the data grid where you can see the grouping order and perform further grouping-related operations. Drag a column header to Group By Box to group the data by that column. You can group the data by multiple columns. In this case the grid will display a tree of nested groups that you can expand by clicking '+' buttons. To collapse the groups, click '-' buttons.

To expand or collapse all groups at once, right-click the Group By Box pane, and then click Full Expand or Full Collapse on the shortcut menu.

To cancel grouping by column, drag-and-drop the column header from the Group By Box pane to the grid or right-click the column header, and then click Ungroup from the shortcut menu.

To remove all groupings, right-click Group By Box, and then click Clear Grouping on the shortcut menu.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Filtering Data in Grid


Filtering Data in Grid

ExpandedToggleIcon(9x12) Hiding Data of a Column

1. Right-click the required column, and then click Visible Columns on the shortcut menu. The Visible Columns dialog box opens.
2. Clear the check box next to the column you want to hide in the grid.
3. Click OK to close the dialog box and apply the filter condition.

ExpandedToggleIcon(9x12) Filtering Data by Column

· On a column header, click the filer-button Filter button. Select the filter value from the drop-down list box. The box includes a set of values encountered in the column and the universal items such as All, Custom, Blanks, and Non Blanks.
· Right-click a column, and point to Quick Filter on the shortcut menu, and then click the required filtering condition.

quickfilter

When the data is filtered, the column Filter button will be changed to active-filter to indicate the applied filter. The filter condition appears at the bottom of the grid providing you a one-click quick access to the filter condition parameters. You can manage them in the following ways:

· To remove all filter conditions, click cancel-filter at the bottom of the grid. To remove the filter condition of a column, right-click this column and click Clear Filter on the shortcut menu.
· To switch from the filtered data to the full data display, clear the [image] check box at the bottom of the grid.
· To edit the filter condition, click edit-filter . The Filter Editor dialog box opens.

ExpandedToggleIcon(9x12) Applying a Custom Filter Condition to Column

1. Right-click the required column, point to Quick Filter on the shortcut menu, and then click [image]. The Filter Editor dialog box opens.
2. Choose a relation type and type values to build a filter.

filtereditor-oracle

Filter conditions in the Filter Editor dialog box are displayed as a tree with condition and group nodes. A group consists of several conditions or subgroups, united with a logical operator. To change a group logical operator, click it and select another one from the menu. To navigate between conditions and groups, use the UP ARROW and DOWN ARROW keys. To navigate between a condition column, a criteria operator and an operand value, use the LEFT ARROW and RIGHT ARROW keys.

To remove a condition, click FilterGreyDel or navigate to the condition and press the DELETE or MINUS SIGN key. To remove a group, click its logical operator and select the Remove Group from the appeared menu. You can copy, cut, and paste conditions and groups with CTRL+C, CTRL+X, and CTRL+V keys.

3. Click OK to apply the filter condition to the data in the grid.

ExpandedToggleIcon(9x12) Creating a New Filter Condition

1. Create a new group:
· Click the upper-level group operator and select Add Group from the menu.
· Press CTRL+PLUS SIGN.
2. Create a new filter condition by doing one of the following:
· Click the upper-level group operator and select Add Condition from the menu.
· Navigate to a group you want to enhance, and press INSERT or PLUS SIGN key.
· Click FilterGreyAdd of the group you want to enhance.
3. Click the condition column, and then and select the column you need from the menu.
4. Click the condition criteria operator and select the criteria operator you want to apply.
5. If the condition operator requires operand values, specify them.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Server-Side and Client-Side Sorting and Filtering


Server-Side and Client-Side Sorting and Filtering

In Query Builder for SQL Server, sorting and filtering of data can be performed on the client or on the server side.

If there are not many records in a table, they are all shown in the result grid after retrieving data from a table or after executing a SELECT statement. If you want to sort these records or use a filter, the filtering and sorting procedures are performed on the client side and all available records are processed.

But if there are many records in a table, they are not displayed after executing a SELECT statement or retrieving data at once, because such action can cause hanging of the client machine or some other problems associated with too much data to operate.

The other case when not all records are displayed in the grid, is when Paginal Mode is on and there are more records then the number of records displayed on one page. In these cases, if you perform client-side sorting or filtering, only records available on the client machine are processed. That means you will not get a complete result of filtering or sorting. That's why Query Builder for SQL Server performs server-side filtering or sorting when not all records can be displayed in the grid.

When server-side filtering or sorting is performed, the application sends a query that is a modification of the starting query to the server. The shortcoming of this way is that server resources are taken, but the benefit is that a user will get a complete result of sorting or filtering.

But once all records are received, for example, after pressing CTRL+END or scrolling to the end, server-side filtering and sorting is turned off and client-side filtering and sorting is used.

Problems that may occur during server-side sorting and filtering:

In some cases the application can not modify the starting query to send it to the server and perform server-side sorting or filtering. These cases are the following:

· There is an expression without alias in a SELECT clause. Actually this is a restriction of MS SQL Server - one cannot modify a query that contains a statement without an alias in a SELECT clause. And if there is no alias for a column, one cannot sort or filter data in other columns as well, until all data is fetched or an alias is given.
· There is a query with an ORDER BY clause. In this case no server-side filtering can be performed, and it is a SQL Server restriction.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Paginal Mode


Paginal Mode

Paginal Mode splits data into pages and is used for displaying data with large number of records.

To enable Paginal Mode, click Paginal Mode.

To configure the number of record per a page, type or select the value from the Page Row Count drop-down list box.

To navigate through pages, use the corresponding buttons on the document toolbar.

Paginal mode toolbar buttons

By default, Paginal Mode is enabled for data views with 1000 records on a page, so only 1000 records are displayed at once. You may change default settings on the Data Editor, General tab of the Options window.

note Note

Paginal Mode can also be enabled by clicking the corresponding button on a message received when retrieving data from a table that has large number of records.

note Note

If not all records are displayed on one page when Paginal mode is on, the warning icon Caution is shown on the status bar. If you move the mouse pointer onto the icon, a notification informing that the data is split into pages will be shown.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Auto-search Mode


Auto-Search Mode

Auto-search mode allows you to search column data.

To enable or disable the Auto-Search mode, do any of the following:

· Press CTRL+I keys.
· On the Data menu, click Auto-search mode.
· Click Auto-search mode on the Data Editor toolbar.

To search for the data, select a cell in a column, and type first letters of the required data. If you made a mistake when typing, press BACKSPACE and type the string again.

To navigate to the next entry, press CTRL+DOWN keys. To return to the previous entry, press CTRL+UP. The entries will be highlighted.

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Card View


Card View

Data Editor allows you to view, edit, and print data in the Card View mode. In the Card View mode, each data row is represented as a card with caption 'Record N' and number.

To enable the Card View mode, click the Switch to Card View button on the Data Editor toolbar. To return the grid view mode, click the Switch to the Grid View button on the Data Editor toolbar.

You can view or edit data in the same way as in the grid mode. Data cards can be expanded and collapsed with help of corresponding button on a caption.

To sort or filter data in the card mode, click the Customize button, and then click either Sort, or Filter next to a column.

To customize the column display in cards, click the Customize button and select or clear check boxes next to the required columns.

Card View

ExpandedToggleIcon(9x12) See Also

Viewing Data in Grid

Editing Data in Grid


Editing Data in Grid

This section describes how to effortlessly edit data in dbForge Query Builder for SQL Server.

ExpandedToggleIcon(9x12) In This Section

Editing Data in Grid Overview
The topic describes how to quickly edit data using various grid editor's features.

Using Transactions
This topic explains how to use transactions in Query Builder for SQL Server for data editing. This functionality is not available in Devart Schema and Data Compare tools.

Cached Updates
This topic explains how to use cached updates mode.

ExpandedToggleIcon(9x12) See Also

Working with Data in Data Editor


Editing Data in Grid Overview

When dbForge Query Builder for SQL Server executes any statement returning a result set, the retrieved data is displayed in the Data Editor of the data view of the same SQL document. This topic shows how to edit the data in grid; for information about customizing data grid, as well as sorting, grouping and filtering, visit Viewing Data in Grid topic.

ExpandedToggleIcon(9x12) To insert, delete, copy and paste data:

Use the popup menu or the corresponding buttons under the grid. For example, you can do the following:

· To insert NULL (instead of empty string), select Set Value To ->Null from the popup menu. You can also use the CTRL+0 key combination.
Tip: Use Set Value To menu to quickly set an empty string, zero, or a current date.

· To add a new record, select the Append option from the popup menu or click the Append Append button under the grid. To delete a record from the grid, select the Delete option from the popup menu or click the '-' button under the grid or press CTRL+DEL keys.
· To copy and paste cell values, use the corresponding options from the popup menu.

ExpandedToggleIcon(9x12) To select data:

You can easily select and copy the data just like cells in a spreadsheet. Do either of these actions:

· Move the mouse pointer across the grid holding the left mouse button
· Click the first cell of the data range, press SHIFT, and, holding the SHIFT key, click the last cell. A rectangular range of cells will be selected.

Tip: Using the CTRL key, you may include and exclude cells to selection. Any number of rows may be copied and pasted to some other application, for example, a spreadsheet, but when you paste data in data grid, only the first row will be pasted.

note Note

Actual document look may slightly vary from the above image depending on the product you use.

note Note

Some queries return read only data, for example, queries with aggregate functions (count, avg etc.), or data, retrieved from non-updatable views. Such data can't be edited. It is also impossible to edit the result of executing of the script with several select statements.

ExpandedToggleIcon(9x12) Row and cell indicators

When working with grid, you can see special indicators near to the focused cell. These indicators reflect current editing state.

The row is focused.

The row has been edited.

Incorrect value was entered into a cell. You must either fix the value or press the ESCAPE key to cancel changes made to the cell.

ExpandedToggleIcon(9x12) See Also

Editing Data in Grid

Using Transactions


Using Transactions

Query Builder for SQL Server allows to control data editing with transactions. You can access the following buttons on the Connection toolbar: Begin Transaction, Commit, and Rollback.

Click on Begin Transaction button on the Connection toolbar or select Begin Transaction from the popup menu of the connection to start a new transaction. Since now all changes you make to a database object are not applied until you click Commit button on the Connection toolbar or select Commit from the popup menu of the connection. To revert all changes and return to the point where you have been at the moment of clicking Begin Transaction button, click on the Rollback button on the Connection toolbar or select Rollback from the the popup menu of the connection.

Commit is automatically issued when you save documents that represent database objects.

The transaction applies to the whole connection. Notice that the Output window reports all transaction events.

note Note

If you are using Devart Schema Compare or Data Compare tools, this functionality is not available.

ExpandedToggleIcon(9x12) See Also

Editing Data in Grid

Cached Updates


Cached Updates

Cached updates mode allows you to control data editing within single database object. For the object you have turned this mode on, updates are stored locally on the client side until you click the Apply Changes button. If you do not need the changes to be applied, click the Cancel Changes button. When you close the document in the cached updates mode, you cancel all changes made since the last Apply command.

ExpandedToggleIcon(9x12) See Also

Editing Data in Grid

Working with Data in Data Editor


Data Shortcut Keys

Use following shortcut key combinations when working with data in the Data Editor or data view of an SQL document.

Icon | Command Name | Shortcut Keys | Action | Where is used
AutoSearch | Auto-search mode | CTRL+I | Data auto-search mode | Data Editor
RefreshButton | Refresh | F5 | Refresh Data view | Data Editor
RefreshButton | Refresh | CTRL+R | Refresh Data view | Data Editor
 |  | CTRL+HOME | Focus on first record in data grid | Data Editor
 |  | CTRL+END | Focus on last record in data grid | Data Editor
 |  | CTRL+DELETE | Delete currently selected row in data grid | Data Editor
 |  | INSERT | Insert new record in data grid | Data Editor
 |  | CTRL+0 | Insert NULL value in current cell | Data Editor

These shortcuts are default shortcuts. All shortcuts can be customized.

ExpandedToggleIcon(9x12) See Also

Working with Data in Data Editor


Writing and Executing SQL Statements

ExpandedToggleIcon(9x12) In This Section

SQL Document Overview
Describes what SQL document is and how to treat it.

Writing SQL Statements
Basic tips on writing SQL statements ans working with SQL scripts.

Executing SQL Statements
Explains some special features for executing SQL statements.

Viewing Server Messages and Errors
Describes windows designed to display server messages and errors.

Using Parameters
Information on how to use parameters to simplify process of editing the query.

Executing Large Scripts
Tells how to execute a large script without opening it in the editor.

Opening Large Scripts in SQL Editor
Contains information about opening large scripts in the editor.

Associating Windows with Connection
Information on how documents and tool windows use connections.

SQL Document Shortcut Keys
Lists shortcut keys designed to facilitate your work with SQL documents and stored procedures.


SQL Document Overview

Most of the time you interact with a database by writing and executing SQL queries. In this way, SQL document is an essential part of every database tool in dbForge product line. It is a place where you create SQL statements in order to run them against a database server. This topic gives you an overview of SQL document layout, views, and functions.

To create a new SQL Document:

On the Standard toolbar, click New SQL.

The following image is the example of an SQL document.

note Note

Actual document look may slightly vary from the image above, depending on the product you use.

ExpandedToggleIcon(9x12) SQL Document Overview

Each SQL document includes different representations of the content. They are called document views. On the above screen-shot you can see two document views: Text and Data.

· Text view contains SQL editor which allows you to write SQL statements.
· Data view, typically shown after a query is executed, displays a data editor with result sets returned by a database server.

To switch to a specific view:

· Click a button with a view name on the bottom-panel of the document.
· Right-click document bottom-panel to open the shortcut menu and select a view from the list.

ExpandedToggleIcon(9x12) Representing Query Execution Results

The query execution results can be represented in two ways - in text and in grid formats.

To represent results as text, on the SQL toolbar, click the results-as-text Results as Text button before query execution. The following image represents a sample of query execution results in text format.

To represent results as grid, on the SQL toolbar, click the results-as-grid Results as Grid button before query execution. The following image represents a sample of query execution results in grid format.

CollapsedToggleIcon(9x12) Document Layout

SQL document has two types of view layout: Split Layout and Combined Layout. To switch document layout use appropriate buttons at the bottom-right corner of the document.

Split Layout

In the split layout a document has one prominent (main) view which is aligned to the top of the document view port. Other views are called additional, they are displayed in the bottom area of the document view port.

Main view remains always in split layout visible and you can toggle only additional views. But you can see two views at a time to avoid unnecessary switching.

To temporary hide additional document views, click the hideshow-views Hide/Show additional document views button at the bottom-right corner of the document, or just press CTRL+R.

To change the main document view, use the swap-views Swap main and additional documents views button to swap the main view and active additional view.

Combined Layout

In the combined layout you can see only one view at a time. A document view fills entire document view port. Typically, you need this layout when you a focusing on one specific document representation (for example, when you extensively work with data).

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements

Writing SQL Statements


Writing SQL Statements

Every SQL document contains the Text view with SQL editor in it. You switch to this view to edit your SQL queries.

When you type text in SQL editor, it automatically recognizes and highlights SQL syntax.

For ease of SQL writing and reviewing, you can also use such supported shortcut keys as: CTRL+PLUS (+) KEY (to zoom the query in), CTRL+MINUS (-) KEY (to zoom the query out), CTRL+0 (to return an SQL document to the one-to-one scale).

You write and execute SQL queries not only one at a time but you may as well compose SQL scripts. SQL scripts consist of multiple SQL statements separated by the appropriate statement delimiter.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements

Executing SQL Statements


Executing SQL Statements

ExpandedToggleIcon(9x12) How to Run Execution

Entire Script

To execute the entire script, click the Execute button on the SQL toolbar.

Alternatively, on the SQL menu, select the Execute command.

Selected Block of Text

To execute an arbitrary text block:

1. Select the text block using the mouse or keyboard.
2. Run the Execute command as when executing the entire script. Alternatively, select Execute Selection from the shortcut menu of SQL Editor

Current Statement

To execute current statement, click the Execute Current Statement button on the SQL toolbar.

Alternatively, right-click a required statement, and select Execute Current Statement command on the shortcut menu.

Execute to Cursor

To execute script to the current position of the cursor:

1. Move cursor to the required position.
2. Click Execute to Cursor on the shortcut menu or press Ctrl+Shift+F10.

ExpandedToggleIcon(9x12) How to Stop Execution

To stop SQL execution, click Stop Execution the SQL toolbar.

ExpandedToggleIcon(9x12) Representing Query Execution Results

The query execution results can be represented in two ways - in text and in grid formats.

To represent results as text, click Results as Text on the SQL toolbar before query execution.

To represent results as grid, click Results as Grid on the SQL toolbar before query execution.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements


Viewing Server Messages and Errors

When you run a query against a database server, it returns a response to the client. There are three types of server response to an SQL query:

· Tabular data
· Status (info) message
· Error

Tabular data is displayed in the data editor. Status messages and errors are logged to Output window. Errors are not only logged to Output window they are also shown in Error List window.

ExpandedToggleIcon(9x12) Using Output Window

Following image represents general view of Output window.

note Note

Depending on the product the Output window appearance may vary.

Output Panes

The window can contain up to four output panes (depending on the specific product):

· General pane contains most output messages, not only from a database server but from the application itself.
· Data pane displays query results that are represented as a formatted text. This pane remains empty unless Redirect Data to Output Window option is set in any SQL document.
· SQL Log pane displays all SQL queries executed by an application during the current session. But you need to turn on Write queries sent by the program to SQL Log option to see those queries.

As you can see from the above screen-shot, server errors are also logged to Output window. And you can navigate to the place of an error by double-click of from the pop-up menu.

Searching Output Messages

You can search text in Output window. To find specific text:

1. Focus Output window.
2. Open Find and Replace window by pressing CTRL+F.
3. Enter search text and press ENTER or click Search button.

ExpandedToggleIcon(9x12) Using Error List Window

When something goes wrong, typically you see an error. If this is a user or database server error, such as mistyped query, in most cases you will see it in Error List window.

Here is an example of an error displayed in Error List.

Error List shows not only errors, but also warnings and informational messages which require user attention. Warning is in essence a non-blocking error. And informational message is just a user notification.

You can filter messages by type using appropriate buttons on the toolbar of Error List window.

You can navigate to the palace of an error by double-clicking it in the list or by selecting Go to Error from the pop-up menu.

How to View Errors from Current Document Only

Error List aggregates all error messages in the application. And if you have multiple open documents with errors, the error processing can quickly become awkward.

But the window has one more filter button - In Active Document. This button allows to view only those errors that are located in current document window.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements

Using Parameters


Using Parameters

ExpandedToggleIcon(9x12) What is a Parameter?

Parameter is a placeholder for a variable that contains a value of some type that is passed to a database server along with the SQL text at the query execution time. Also, parameter can hold values returned by a server after query or stored procedure execution.

You can benefit from using of parameters in the following situations:

· When you execute a query multiple times with different input values
· When you debug a query from your application code

ExpandedToggleIcon(9x12) Adding Parameters to a Query Text

Parameters are declared using '@' prefix followed by name of the parameter. Prefix is included to the name of the parameter.

For example:

SELECT
Employee.*
FROM
HumanResources.Employee
WHERE
Employee.Title = @Job AND
Employee.VacationHours <= @Level

@Job and @Level are parameters in this query.

ExpandedToggleIcon(9x12) Modifying Parameter Values and Types

When you run a query that contains parameters with empty values, you will automatically be prompted to initialize them.

To set parameter values and types, select Edit Parameters on the SQL toolbar, or press F8. In the opened Edit Parameters dialog box, set parameters' type, value, and other properties.

note Note

When you execute a query with preset parameter values, you will not be automatically prompted to edit them. To edit parameters, press F8 or select Edit Parameters on the SQL toolbar.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements


Executing Large Scripts

Query Builder for SQL Server allows you to execute large scripts without opening SQL Editor and loading the whole script in memory. When you try to open a large script, you will be prompted to execute it with the help of the Execute Script Wizard.

To execute a large script:

1. On the Database menu, click ExecuteLargeScript Execute Large Script.
2. In Connection, select a connection to a required database server against which you want to execute your script.
3. In Database, select a required database from the drop-down list/
4. In File name, specify the path to the script you want to execute.
5. In SQL file encoding, ensure that the file encoding is selected correctly. The program automatically selects it basing on the script. If it fails, the default encoding (the current Windows encoding on the machine where the program is running) is selected. You can always select a required encoding from the drop-down list box.
6. Click Execute to execute the script.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements

Opening Large Scripts in SQL Editor


Opening Large Scripts in SQL Editor

To avoid performance slowdown, some features are disabled when working with large SQL scripts.

If script size exceeds 10 Mb, the SQL syntax highlight is disabled. It is not enabled when you reduce the script in the editor. You should reopen script to re-enable the feature.

note Note

Maximum file size the application can open depends on available memory on your computer.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements


Associating Windows with Connection

When several connection are established, you can execute queries or perform another actions with either of them. SQL documents are associated with a connection which you can see or set on the Connection toolbar. The drop-down list in the toolbar shows what connection is associated with the active document or tool window. If a tool window, that does not require a connection, is active, connection of the active document (or tool window in the document area) is displayed.

When creating or opening a SQL document, it is assigned a default connection. If you create SQL document with no connection, the document will have no connection and you can not execute SQL or retrieve data. You can change connection for SQL document by selecting required connection in the Document Connection box on the Connection toolbar.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements

SQL Document Shortcut Keys


SQL Document Shortcut Keys

Use following shortcut keys when working with an SQL document or stored procedure.

Icon | Command Name | Shortcut Keys | Action | Where is used
Execute | Execute | F5 | Execute query associated with current document | SQL editor
execcurrentstatement | Execute Current Statement | F8 | Execute current statement | SQL editor
PreviousSQL | Previous SQL | Ctrl+Shift+ Up Arrow | Previous SQL statement from history | SQL editor
NextSQL | Next SQL | Ctrl+Shift+ Down Arrow | Next SQL statement from history | SQL editor
History | SQL History | Ctrl+Alt+H | Open SQL History dialog | SQL editor
Parameters | Edit Parameters | Ctrl+Shift+P | Edit parameters | SQL editor
DataAsText | Redirect Data To Output Window | Ctrl+Shift+T | Show data as text in the Output window | SQL editor
Comment | Comment Selection | Ctrl+E, C | Comment selection | SQL Editor
Uncomment | Uncomment Selection | Ctrl+E, U | Uncomment selection | SQL Editor
 | Toggle Outlining Expansion | Ctrl+M, M | Toggle outlining expansion | SQL Editor
 | Toggle All Outlining | Ctrl+M, L | Toggle all outlining | SQL Editor
 | Stop Outlining | Ctrl+M, P | Stop outlining | SQL Editor

These shortcuts are default shortcuts. All shortcuts can be customized.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements


How To: Create and Edit a Query

This topic explains how to create and edit an SQL query in Query Builder for SQL Server

To create a query:

1. Create a server connection.
2. On the Standard toolbar, click NewSQL New SQL. An empty SQL document opens.
3. Start type a query to a database.
4. Click Execute or press CTRL+F5 to see the query results.

ExpandedToggleIcon(9x12) Coding Techniques Example

The Query Builder for SQL Server SQL editor provides many features that make it easier for you to write and manage your code. Let's explore some Intellisense features that assist you while coding.

We are going to create a sample query. In this example we will use the sakila database. Let's display all the films stored in the sakila database, that were released after 2004.

1. Open the SQL Editor.
2. Start typing the SELECT statement. Enter first three letters "sel". Query Builder for SQL Server shows you the list of the relevant keywords. You can select the SELECT keyword from the list and press ENTER. The keyword appears in the SQL editor.

3. Now, lets type a table name from which we want to retrieve the data. Start type "f". Query Builder for SQL Server shows you a list of valid members from the sakila database. If you continue typing characters, the list is filtered to include only the members that begin with those characters. After selecting an item, you can insert it into your code by pressing TAB (or ENTER) or by typing a space. Query Builder for SQL Server also shows a quick info for every highlighted item in the list. It allows you to see the object structure immediately.

The film table comes first on the list, so just press TAB or ENTER to insert it into the SQL editor.

4. We need to select films categories that is stored in a separate tables. In a similar way, type the JOIN keyword and press the SPACE key. Query Builder for SQL Server shows you all available solutions to implement JOIN. Select a required statement and press ENTER.

5. Continue typing the query. You can use the Quick Info feature while typing SQL code. The Quick Info feature is a tooltip with brief information about database objects, parameters, and variables. The tooltip appears when you place the mouse pointer on the required item in the SQL script.

In case of database objects, the tooltip shows their location and types. You will find this option useful, while working with large scripts.

6. Use CTRL + K, CTRL + D combination to format the query text. Now we can execute the query to see results. To do so, click the Execute button or press F5.

7. The last step is to add the WHERE condition. Add the WHERE f.release_year > 2004 statement at the end of the query and press F5 to execute it.

We got what we wanted.

As you can see that code completion feature provided by Query Builder for SQL Server allows you to create complex queries in a few keystrokes.

ExpandedToggleIcon(9x12) See Also

Writing and Executing SQL Statements

Writing and Executing SQL Statements


Optimizing SQL Queries

ExpandedToggleIcon(9x12) In This Section

Profiling SQL Queries
The topic describes how to troubleshoot, monitor, and measure your application's SQL statements and stored procedures.

Working with Plan Diagrams
The topic describes how to work with plan diagrams.


Profiling SQL Queries

You can use the Query Profiler tool to debug, troubleshoot, monitor, and measure your application's SQL statements and stored procedures. If your application has a performance problem that you think might be caused by a particularly long-running query, you can analyze query durations.

In this topic we will review how to profile a simple query. As an example we will use the AdventureWorks2012 test database from Microsoft.

Profiling an SQL statement

We are going to select all persons from the Person table where the first name is "Robin".

1. On the Start page, click Query Profiler. A new SQL document window opens.
2. In the text editor, type the following script:

SELECT
*
FROM AdventureWorks2012.Person.Person
WHERE FirstName = 'Robin'

3. Click Execute. The Plan Diagram window opens.

Note, the select icon contains the exclamation mark icon that warns about a shortcoming. A tooltip appears automatically, when you pause the mouse pointer over the Select icon. The bottom part of the tooltip contains the warning message, that tells us that the index is missing. To add the index, execute the following script:

USE AdventureWorks2012
GO

-- Create index "IDX_Person" on table "Person.Person"
--CREATE INDEX IDX_Person
ON Person.Person (FirstName)
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

Now we can get back to the Query Profiler and click Get-New-Results the Get New Results button.

Each time you get profiling results for the executed query, they appear in the tree view as a new node with time and date of query execution. When editing your query, you want to know whether your changes reduce query execution time or not. Query Profiler can quickly compare the profiling results. To compare results, hold the CTRL key and select both plan diagrams.

note Note

When you save the query file after getting the query profiling results, the latter will be automatically stored in the *.design file.

ExpandedToggleIcon(9x12) See Also

Working with Plan Diagrams


Working with Plan Diagrams

Query Profiler allows you to examine plans of different queries in a single view. For this, hold the CTRL key and select several plan diagrams in the query profiling tree.

You can set a number of plans that you want to compare with the most recent result. For this, click Compare-Selected-Results and then select a required number.

The following picture demonstrates that the Compare last two results option is selected. In this case, when you modify a query and then click Get-New-Results the Get New Results button, the most recent result will be automatically compared with Plan #2.

Plan #1 does not participate in comparison.

To zoom in and out a pan diagram, right click the plan diagram and then click Zoom.


User Interface Concepts

expandedtoggleicon(9x12) In This Section

Using Documentation
The topic explains how to use the current manual.

User Interface Elements
This topic provides you with basic information about managing your environment workspace.

Working with Text Editor
This topic explains how to work with the Text Editor.

Customizing Window Layout
This section provides information about window layouts and their customization.

Manipulating Documents
The topic describes how to manipulate documents in Query Builder for SQL Server.

Customizing Environment
This topic contains information on customizing environment to simplify your work and save time.

Find and Replace Text and Code
This section describes how to use Find and Replace utility.


Using Documentation

The application documentation is available from the official Devart website in the following formats:

· Online
· Offline CHM file (you can download it on the Download section of the product)

There are several ways to navigate through documentation:

· F1 Search
· Search
· Contents

expandedtoggleicon(9x12) F1 Search

F1 provides context help for the active window, dialog box. It always returns no more than one page.

expandedtoggleicon(9x12) Search

Use Search to return all documents that match any specified term or a set of terms.

expandedtoggleicon(9x12) Contents

The table of contents shows all documentation topics in a hierarchical tree view structure and provides a convenient way to browse help. If you opened topic from index or search or from the link and you want to know where this topic is located, use the Locate button or switch to the Contents tab.

expandedtoggleicon(9x12) See Also

Customizing Environments

User Interface Elements


User Interface Elements

This topic provides you basic information about managing your environment workspace. Environment workspace consists of toolbars, tool windows, menus and document area. To personalize menus and toolbars and to adjust their behavior to your needs, point to Toolbars in the View menu, and click Customize.

expandedtoggleicon(9x12) Toolbars

A toolbar is a container for a group of commands or controls that are typically related in their function.

Query Builder for SQL Server supports dockable toolbars. A dockable toolbar can be attached, or docked, to any side of its parent window, or it can be detached, or floated, in its own mini-frame window. This article explains how to use dockable toolbars in your applications.

Note Note

If you drag a toolbar to the right or left side of the window, toolbar drop-down lists or combo boxes will not be available. For example, if you drag the Connection toolbar to the right or left side of the window, the Connection box will not be available.

expandedtoggleicon(9x12) Menus

The environment has a set of toolbars where reside buttons, using which you can accomplish most frequent tasks. The toolbar you can access depends on kind of document currently active. If it is not visible, right-click on toolbars area and choose one from the shortcut menu.

You can control which toolbars are allowed to appear, and which must be always visible. By default, when you switch between views or documents, corresponding toolbars appear and disappear. When a toolbar is visible, right-click on toolbar area and deselect its item in the shortcut menu to prevent it from showing even if corresponding view is active. If you want to make a toolbar always visible, select it in the shortcut menu, when corresponding document or view is not active. To return a toolbar to its default state select it in the shortcut menu when corresponding document or view is active. You can edit any toolbar, delete, or add your own in Customization dialog box. To open it choose Customize from a toolbar shortcut menu.

expandedtoggleicon(9x12) Document Area

Document area typically displays SQL code or diagrams. Document area size can be manipulated by resizing docked tool windows. To free more space for documents, you can put docked tool windows to auto-hide mode by clicking the pushpin icon on the title bar of the window.

You can arrange documents in tab groups in document area. To create a tab group or move document to another tab group just drag a document to a drop point. Dragging a document to an arrow drop point will create new tab group; dragging it to the tab group central drop point will move the document to this tab group.

Multiple documents can be organized into horizontal or vertical tab groups for organizing a workspace for working with several documents at the same time. New document tab group can be created either by dragging document to the required place or by selecting New Horizontal Tab Group or New Vertical Tab Group from the document header shortcut menu.

Documents can be moved between tab groups by dragging-n-dropping or by selecting required command from the shortcut menu. You can expand a tab group to the whole document area and restore document tab groups' sizes by selecting Prominent from the document header shortcut menu.

expandedtoggleicon(9x12) Switching Between Windows

This section contains information about how to switch between documents and tool windows.

To cycle through open documents, use CTRL+TAB or CTRL+SHIFT+TAB key combinations. Press CTRL+TAB to switch to previously used document. Pressing CTRL+TAB again will return you to current document. Hit TAB key while holding CTRL key to cycle through recent documents stack. You can also use CTRL+SHIFT+TAB combination to browse documents in another direction.

To navigate from the current document tab to the next tab, use CTRL+ALT+PGDOWN key combination. To navigate from the current document tab to the previous tab, use CTRL+ALT+PGUP key combination.

You can also switch between documents in the following ways.

· By clicking document titles.
· By clicking the down arrow near the right upper corner of the document area and selecting document name from the appeared menu.
· By selecting document from the Window menu.
· By selecting document in the Windows dialog and clicking the Activate button.

To activate a tool window, use its shortcut key.

expandedtoggleicon(9x12) Switching to another skin

dbForge Query Builder for SQL Server provides you several types of a skin. To switch to another skin, on the Tools menu, choose Skin, and then click the skin name you like.

The following skins are available in dbForge Query Builder for SQL Server:

· Visual Studio 2013 Blue
· Visual Studio 2013 Light
· Visual Studio 2013 Dark
· Visual Studio 2010
· Metropolis
· Metropolis Dark
· Office 2010 Silver
· Office 2010 Blue
· Office 2010 Black
· Office 2013
· Office 2013 Dark Gray
· Office 2013 Light Gray
· Windows 7

expandedtoggleicon(9x12) See Also

Using Documentation

Working with Text Editor


Document Views

The topic describes how to work with documents inside Query Builder for SQL Server.

expandedtoggleicon(9x12) Document Views

Documents have different views. Views are different aspects of an object (document's content). You can switch between the views using the View menu or using the bottom part of the document area. Right-click on it to display the switch buttons if they are not visible yet.

You can cycle through available views using the F7 key. This key switches you to the next view visible in the document's bottom area. If you want to switch to an invisible view, you can do it with mouse by right-clicking in that area.

note Note

Appearance of this menu may slightly vary depending on the product you use.

expandedtoggleicon(9x12) Document Header Shortcut Menu

Document header shortcut menu appears when right-clicking on the document header. It consists of the following items.

Save <Name>.sql
Saves a current SQL document.

Close
Closes a current document.

Close All But This
Closes all documents excepting the selected one.

Copy Full Path
Copies the full path for the document.

Open Containing Folder
Opens the folder containing the document.

Float
Detaches the window.

New Horizontal Tab Group
Creates new horizontal tab group.

New Vertical Tab Group
Creates new vertical tab group.

expandedtoggleicon(9x12) See Also

User Interface Concepts

How To: Customize User Shortcuts


Working with Text Editor

The text editor is the word processor of the Query Builder for SQL Server. When the text editor is used to edit text, it is referred to as the text editor. When it is used to edit SQL source code, which is its more common use, it is referred to as the code editor. You can open multiple code editors to view or/and edit code. A list of all documents open for editing in instances of the code editor is available on the Windows menu.

To open the Text editor:

1. On the File menu, click New, and then click File. (Alternatively, on the Standard toolbar, click the New File button.)

2. The New File dialog box opens.

3. Select either the Text file or the XML file form the General Files section.

The only difference between XML and text editors is that XML editor provides syntax highlighting for XML code. You can customize XML highlighting and coloring in Fonts and Colors, Text Editor, Options Dialog Box.

expandedtoggleicon(9x12) User Interface

Code pane
The area where code or text is displayed for editing. It provides SQL statement completion.

Indicator margin
A gray column on the left side of the code editor where indicators for breakpoints and bookmarks are displayed. Clicking this area sets a breakpoint on the adjacent line of code.

Selection margin
A column between the indicator margin and the editing window where you can click to select lines of code.

Horizontal and vertical scroll bars
Lets you scroll the code pane horizontally and vertically so that you can view the code that extends beyond the viewable edges of the pane.
expandedtoggleicon(9x12) Text Editor Toolbar

The Text Editor toolbar contains tools related to the code completion features, commenting and uncommenting of code, bookmarks, etc.

Display an Object Member List
Displays a list of valid databases, tables, columns, and other database objects. When a member from the list is selected, you can insert that member into your code by pressing TAB, or by typing a space or period.

Display Parameter Info
This feature opens the Parameters list to give you information about the number, names, and types of parameters required by a function or attribute. The Parameters list also displays parameter information for nested functions. If you type a function as a parameter to another function, the Parameters list displays the parameters for the inner function. Then, when the inner function Parameters list is complete, the Parameters list reverts to displaying the outer function parameters.

To view Parameter Info for functions:

1. After the name of a function, type an open parenthesis as you normally would to open the Parameters list.
2. Query Builder for SQL Server displays the complete declaration for the function in a pop-up window just under the insertion point. The first parameter in the list appears in bold.
3. As you type the function parameters, the bold changes to reflect the next parameter that you need to enter.
4. Press ESC at any time to close the list, or continue typing until you have completed the function.
5. Typing the closing parenthesis also closes the Parameters list.

Display Quick Info
This option displays the complete declaration for any identifier in your code.

You can manually invoke Quick Info feature as follows:

1. On the Edit menu, click Code Completion, and then click Quick Info.
2. Use the keyboard shortcut CTRL+K, CTRL+I.
3. Select the Quick Info toolbar button from the Text Editor toolbar.

Display Word Completion
Complete word types the rest of a variable, command, or function name once you have entered enough characters to disambiguate the term. Type the first few letters of the name, and then press ALT+RIGHT ARROW or CTRL+SPACEBAR.

Refresh Local Cache
If you are writing a query on a database while someone else is making changes to the structure of that database, the suggestions offered by Query Builder for SQL Server may become out of date. For instance, if someone has added several tables to a database while you have been connected to that database, these tables will not appear in the suggestions box. To reload the database objects, click Refresh Suggestions. Refreshing suggestions applies only to the database to which you are currently connected.

Reset Suggestions Cache
If you connect to a number of different databases during the same session, Query Builder for SQL Server will load all the objects for each database as required, without unloading any of the previously stored objects. This means that you can go back to previous queries and continue to see suggestions for any database to which you have previously connected.
Query Builder for SQL Server loads database objects into its memory whenever you connect to a database, in order to display suggestions from that database. If you connect to several databases with a large number of objects, this may eventually result in running out of memory. To reset the memory at any time, click Reset Suggestion Cache.

Decrease Line Indent
Removes a tab (or the equivalent spaces) from the selected lines or the current line.

Increase Line Indent
Adds a tab (or the equivalent spaces) to the selected lines or the current line.

Comment out the selected lines
Adds comment characters to the selection or the current line.

Uncomment the selected lines
Removes comment characters from the selection or the current line.

Toggle a bookmark on the current line
Adds or removes a bookmark on the selected line of the document in the active Editor. Does not alter the line of code bookmarked.

Move the caret to the previous bookmark
Selects the previous bookmark. When the first bookmark is reached, jumps ahead to the last one.

Move the caret to the next bookmark
Selects the next bookmark. When the last bookmark is reached, jumps back to the first one.

Clear all bookmarks in current document
Removes all the bookmarks in the current document.

Format Document
Automatically formats SQL code in the currently opened document.

expandedtoggleicon(9x12) Selecting text and code

You can quickly select a single word, line of text, or multiple paragraphs while you work with code in the editor. Keyboard shortcuts also let you select text starting from the cursor to any other point in your document.

To select text:

· To select any amount of text, just drag the mouse over the text.
· To select a word, either double-click a word or put your cursor next to word and press CTRL+SHIFT+ either RIGHT ARROW or LEFT ARROW.
· To select a line of text, drag the mouse over a line.
· To select text from the cursor to the start or end of the line, press SHIFT+ either HOME or END.

expandedtoggleicon(9x12) Editing text and code

The Editor allows you to manipulate text in the same ways as most word processing applications or text editors. For example, you can:

· Copy and Paste text or code inside the editor and between several Editors or between the Editor and other text editing applications.
· Put the Code Editor into the overstrike mode using the INSERT key
· Use the DELETE and BACKSPACE keys to remove text or code

expandedtoggleicon(9x12) Navigating text and code

There are various ways to move through text or code in the Code Editor using the mouse and navigation keys:

· Use the arrow keys to move one character at a time, or the arrow keys in combination with the CTRL key to move one word at a time. Arrow keys move one line at a time also.
· Click a location with the mouse.
· Use the scroll bars or scroll wheel on the mouse to move through the code.
· Use the HOME, END, PAGEUP, and PAGEDOWN keys.
· Use CTRL+PAGE UP and CTRL+PAGE DOWN to move the insertion point to the top or bottom of the window, respectively.
· Use CTRL+UP ARROW and CTRL+DOWN ARROW to scroll the view without moving the insertion point.

Go to Line command

Use the Go to Line command to a specific line number:

1. In the General, Text Editor, Options Dialog Box, select the Line Numbers option.
2. With a document open in the Editor, select Go to Line from the Edit menu.
3. Enter the line number you want to move to.

expandedtoggleicon(9x12) Change Text Case in the Editor

You can use menu commands or keyboard shortcuts to convert the case of selected text to all upper case or to all lower case.

To switch text to upper case:

1. Select the text you want to convert.
2. On the Edit menu, select Advanced.
3. To convert text to all upper case, choose Make Uppercase, or press CTRL+SHIFT+U.

To convert text to all lower case, choose Make Lowercase, or press CTRL+U.

expandedtoggleicon(9x12) Setting Bookmarks

As the name suggests, bookmarking accelerate your code navigation in the text editor.

There may be some piece of code where you need to move to very frequently. Generally, you scroll the page and move to that section, but Query Builder for SQL Server provides great features by which you can move to a particular section very quickly using some shortcut keys. This is called Bookmarking of Code.

You can place a bookmark by clicking on the bookmark icon or use CTRL + B, CTRL + T.

To create or remove a bookmark using the Text Editor toolbar:

1. Open a file in the Editor.
2. Place the insertion point on the line you want to bookmark.
3. Click the Toggle a bookmark on the current line button on the Text toolbar.

To move to a bookmarked line:

1. Scroll to the bookmarked line.
2. On the Text toolbar, the Move the caret to the previous bookmark and Move the caret to the next bookmark buttons are enabled.
3. Click these buttons to navigate to bookmarks in files within the current solution or project.

expandedtoggleicon(9x12) View White Space and Word Wrap

You can turn View White Space and Word Wrap modes on or off by selecting corresponding commands from the Advanced submenu of the Edit menu.

View White Space mode displays spaces and tab characters.

Word Wrap automatically displays any portion of a line that extends horizontally beyond the viewable editor area on the next line.

expandedtoggleicon(9x12) Text Indenting

The Editor provides text indenting functionality. You can set the indent at some line, thus when you press ENTER, this indent is set at the next line. To change line indenting use Increase Line Indent and Decrease Line Indent options.

To increase or decrease indenting perform the following actions:

1. Select the text you want to indent.
2. On the Edit menu, select Advanced.
3. Select Increase Line Indent or Decrease Line Indent to manage the indentation.

collapsedtoggleicon(9x12) Code Outlining

SQL Editor allows you to hide parts of code. Outlining marks are shown left of the code editor window. Code blocks which belong to the same structural item can be collapsed by using the minus (-) symbol. Outlined code is not deleted, it is merely hidden from view.

You can then expand the outlined statement or user-defined region by clicking the plus sign (+) next to the symbol.

Outlining SQL Statements

The statement is outlined in the following cases:

· it spans over two or more lines
· it is a create statement

Nested SQL statement (for example, SELECT in CREATE VIEW) is outlined only if it starts from the new line.

Statement is not outlined if it is very short and takes only one line of code. Stored code condition and cycle operators are not outlined; the same is about nested BEGIN ... END blocks.

User-defined Outlining Regions

You can outline code block using Regions.

To create the region, type #region command at the start line of the region. No symbols except spaces are allowed at the same line before #region command. Region may have the name, so simply specify it after space at the same line. Each region statement has to be closed by the #endregion command. Type #endregion at the beginning of the line determining the region end.

You can create named regions using the #region <region name> and #endregion <region name> commands.

SQL code block, grouped by the specified region can be collapsed/expanded by user. You can create nested regions to operate several SQL code blocks.

expandedtoggleicon(9x12) See Also

User Interface Elements

Find and Replace Text and Code


Customizing Window Layout

This section provides information about window layouts and their customization.

expandedtoggleicon(9x12) Closing and Displaying Tool Windows

When a tool window is closed, it becomes invisible but preserves its content, that will be available when the window is opened again. Most tool windows are listed in the View menu and can be opened by selecting corresponding View menu command. Other tool windows are listed in Database menu. You can also use shortcut keys to display certain tool windows.

All layout changes are saved when you close the application and restored when you open it again.

expandedtoggleicon(9x12) Auto-hide Mode

Tool window auto-hide mode allows you to minimize tool windows along the edges of the application window, freeing more space for the document area.

To turn on auto-hide mode, perform one of the following.

· Activate the window and select Auto Hide from the Window menu
· Right-click the window title and select Auto Hide from the shortcut menu
· Click on the pushpin icon on the title bar of the window

When tool window is in auto-hide mode, it shows when you point on it and hides when you click somewhere out of the window. You can turn auto-hide mode on for all docked tool windows to free all the workspace for document area. To do it, select AutoHide All from the Window menu.

expandedtoggleicon(9x12) Layout Saving and Restoring

All layout changes are saved when you close the application and restored when you open it again. To restore layout to its default state select Reset Window Layout from the Windows menu.

expandedtoggleicon(9x12) See Also

User Interface Concepts

Manipulating Documents


How To: Customize User Shortcuts

The environment provides many useful shortcut key combinations to speed up your work. Default shortcut settings are similar to shortcuts in Visual Studio. Default keyboard mapping schema can be changed to the one similar to Visual C++ 6.0 or to Delphi. There are some more keyboard mapping schemas. Moreover, you can assign your own shortcut keys to every action.

Shortcuts can be changed on the Keyboard, Environment, Options dialog box.

To access this dialog box:

1. In the Tools menu, click Options. The dialog box appears.
2. Expand the Environment section, and click Keyboard.

expandedtoggleicon(9x12) Choosing a keyboard mapping schema

You can select a keyboard mapping schema from the Apply the following additional keyboard mapping scheme drop-down list on the Keyboard page of the Environment section in the Options dialog box.

expandedtoggleicon(9x12) Determining and removing the shortcut key combination for a command

To determine or remove the shortcut key combination for a command perform the following actions.

1. On the Keyboard, Environment, Options dialog box, enter the command name without spaces or a part of it in the Show commands containing box.
2. In the list, select the correct command.
3. Shortcuts for selected command drop-down list now contains the assigned key combination for the command if any combinations exist. You can view them in the list.
4. If you want to remove the combination, select a command in the list, and click the Remove button.

expandedtoggleicon(9x12) Assigning custom shortcut key combination for a command

To assign a shortcut key combination for a command:

1. On the Keyboard, Environment, Options dialog box, enter the command name without spaces or a part of it, in the Show commands containing text box.
2. In the list, select the correct command.
3. On the Use new shortcut in drop-down list, select the feature area where you want to use the shortcut.
4. Place the cursor in the Press shortcut keys box, and press the key combination to set for the selected command. The combination will be displayed in the box.
5. Click Assign.

expandedtoggleicon(9x12) See Also

User Interface Concepts


Find and Replace Text and Code

This section describes how to use Find and Replace utility.

expandedtoggleicon(9x12) In This Section

Finding and Replacing Text
The topic explains how to finding and replacing text in a file.

Searching and Replacing Text in Files
The topic describes searching and replacing text in files techniques.

Using Incremental Search
The topic explains how to use incremental search to find required text in a file.

Regular Expressions for Find and Replace
The topic explains how to use regular expressions while searching.

Wildcards for Find and Replace
The topic explains how to use wildcards while searching.


Finding and Replacing Text

You can find and replace text using Find and Replace window. To open this window press CTRL+F or CTRL+H. These techniques allow you to control the scope of find and replace and choose the method of reviewing search matches and replacements.

You should consider the following information when you search and replace text:

· Options set in the Find and Replace window are global; certain options selected in one dialog box are selected for all the dialogs. These options are Match case, Match whole word, Search all open documents, Search hidden text, Search up, Wildcards, and Regular Expressions.
· Undo is available only for documents left open after a replace operation.
· Undo for a Replace All operation rolls back all replacement changes in the current document.

You can search one or more open files or windows and move through the search matches one by one using the Find and Replace window. This technique allows you to review each individual search match in the context of the text around the match.

To search current document, current project, or all open documents:

1. Open the items you wish to search.
2. Press CTRL+F and switch to Find mode if necessary.
3. In the Look in box, select 'Current Document', 'Current Project' or 'All Open Documents'.
4. In the Find what text box, enter the text you intend to search for.
5. Select additional search options to increase the accuracy of the search.
6. Click Find Next to start the search and continue choosing Find Next until the last file has been searched.

note Note

You may search in the Output or Data Viewer and Editor windows in the same way. Just select Current Window on the step 3. In the Data Viewer and Editor window you may search both hex code and text. Hex code can be searched only when no wildcards or regular expressions are used.

When the search passes the beginning or end of the document, a message is displayed in the status bar. A message box appears when the search reaches the starting point of the search.

To replace in the current document or in all open documents:

1. Open the items you wish to search.
2. Press CTRL+H and switch to Replace mode if necessary.
3. In the Look in box, select 'Current Document', 'Current Project' or 'All Open Documents'.
4. In the Find what text box, enter the text you intend to search for.
5. In the Replace with text box, enter the text with which you intend to replace the search text.
6. Click Replace and continue clicking Replace until the last match in the last file has been replaced. Use Find Next to skip a match you do not want to replace.

You may click Replace All to replace all matches. A message box appears, listing the total number of replacements. The Replace All command replaces all search matches, including those you have skipped with the Find Next button. To reverse Replace All, choose Undo from the Edit menu in each changed file before closing any of the files.

expandedtoggleicon(9x12) See Also

Find and replace text and code

Searching and Replacing Text in Files


Searching and Replacing Text in Files

You can use the Find and Replace in Files window to perform search and replace operations. To open this window press CTRL+F or CTRL+H, then switch to Find in Files or Replace in Files mode.

Searching and replacing text in files has following peculiarities:

· You can enter disc folder as search location or even create and use named folder sets.
· You can only find all occurrences, you cannot use Find Next.

To create a named folder set:

1. In the Find and Replace in Files window, click the button button next to the Look In box.
2. Enter the folder set name.
3. Edit the folder list with LeftArrow and RightArrow buttons. To browse folders
4. Click the Apply button. Now you may use this folder set in the Look In box.

To delete named folder set, click the button button next to the Look In box, enter the folder set name, and click Delete button.

To search in files:

1. Press CTRL+F and switch to Find in Files mode if necessary.
2. In the Look in box, select needed location or enter folder path or name of the named folder set.
3. In the Find what text box, enter the text you intend to search for.
4. Select additional search options to increase the accuracy of the search.
5. You may specify the file mask for searching in the Look at these file types box.
6. If needed, customize the Result options.
7. Click the Find All button.

After search is finished, all results are displayed in the Find Results window. You may double-click on matches to navigate to them.

To replace in files

1. Press CTRL+H and switch to Replace in Files mode if necessary.
2. In the Look in box, select needed location or enter folder path or name of the named folder set.
3. In the Find what text box, enter the text you intend to search for.
4. Select additional search options to increase the accuracy of the search.
5. If needed, customize the Result options. Select the Keep modified files open after Replace All check box if you want to be able to undo changes.
6. You may specify the file mask for replacing in the Look at these file types box.
7. In the Replace with text box, enter the text with which you intend to replace the search text.
8. Click Replace and continue clicking Replace until the last match in the last file has been replaced. Use Find Next to skip a match you do not want to replace.

You may click Replace All to replace all matches. A message box appears, listing the total number of replacements. The Replace All command replaces all search matches, including those you have skipped with the Find Next button. To reverse Replace All, choose Undo from the Edit menu in each changed file before closing any of the files.

expandedtoggleicon(9x12) See Also

Find and replace text and code

Using Incremental Search


Using Incremental Search

You can search an individual document or window incrementally by entering a search string character by character, then observing the matches found as the search string lengthens. To activate an incremental search, choose Advanced from the Edit menu and choose Incremental Search.

An incremental search automatically searches the entire document or all text within a window, with the exception of text that has been collapsed or concealed. The editor highlights the first set of characters found in the document or window that match the search string entered.

Incremental searches are performed by default from the current location in the document downward, and from left to right. To move to the next match, press CTRL+I. To reverse the direction of the search, press CTRL+SHIFT+I.

To search incrementally:

1. Place the cursor in a blank section of the file or window you want to search.
2. From the Edit menu, choose Advanced, and then select Incremental Search.
3. The cursor icon changes to binoculars with an arrow indicating the search direction, and the status bar displays the text Incremental Search.
4. Begin typing the search string.
5. The status bar displays the search string you are entering, and the editor highlights the first match found for what you have typed so far.
6. Continue to add characters to the search string.

The editor searches again for a match of the augmented search string, and again highlights the first match found.

If no match is found, the status bar displays the following message: Incremental Search: <text> not found.

expandedtoggleicon(9x12) See Also

Find and replace text and code

Regular Expressions for Find and Replace


Regular Expressions for Find and Replace

The Find and Replace window contains a list of symbols and signs which you can use to quickly find and replace required patterns of text. They are called regular expressions.

To select a regular expression, do the following:

1. In the Find and Replace window, select the Use check box and choose Regular Expressions from the drop-down list. The [image] arrow button next to the Find what field becomes active.
2. Click the [image] arrow button and select a regular expression from the drop-down list. It is inserted into the Find what field.

The drop-down list contains the following regular expressions:

Syntax | Expression | Description
. | Any single character | Matches any single character except a line break.
* | Zero or more | Matches zero or more occurrences of the preceding expression, finding all possible matches.
+ | One or more | Matches at least one occurrence of the preceding expression.
^ | Beginning of line | Anchors the match string to the beginning of a line.
$ | End of line | Anchors the match string to the end of a line.
< | Beginning of word | Matches only when a word begins at this point in the text.
> | End of word | Matches only when a word ends at this point in the text.
\n | Line break | Matches a platform-independent line break. In a Replace expression, inserts a line break.
[] | Any one character in the set | Matches any one of the characters within the []. To specify a range of characters, list the starting and ending character separated by a dash (-), as in [a-z].
[^] | Any one character not in the set | Matches any character not in the set of characters following the ^.
| | Or | Matches either the expression before or the one after the OR symbol (|). Mostly used within a group. For example, (database|management)system matches "database system" and "management system".
\ | Escape Special Character | Matches the character that follows the backslash (\) as a literal. This allows you to find the characters used in regular expression notation, such as { and ^. For example, \^ Searches for the ^ character.
{} | Tagged Expression | Matches text tagged with the enclosed expression.
:i | SQL identifier | Matches the expression ([a-zA-Z_$][a-zA-Z0-9_$]*).
:q | Quoted string | Matches the expression (("[^"]*")|('[^']*')).
:b | Space or Tab | Matches either space or tab characters.
:z | Integer | Matches the expression ([0-9]+).

expandedtoggleicon(9x12) See Also

Find and replace text and code

Wildcards for Find and Replace


Wildcards for Find and Replace

The Find and Replace window contains a list of expressions to replace characters or digits in the Find what field. They are called Wildcards.

To select a required wildcard, do the following:

1. In the Find and Replace window, select the Use check box and choose Wildcards from the drop-down list. The [image] arrow button next to the Find what field becomes active.
2. Click the [image] arrow button and select a wildcard from the drop-down list. It is inserted into the Find what field.

The drop-down list contains the following wildcards:

Syntax | Expression | Description
? | Any single character | Matches any single character.
# | Any single digit | Matches any single digit. For example, 5# matches numbers that include 5 followed by another number, such as 51, but not 15.
[!] | Characters not in set | Matches any one character that is not specified in the set.
\ | Escape | Matches the character that follows the backslash (\) as a literal. This allows you to find the characters used in wildcard notation, such as * and #.
* | One or more characters | Matches any one or more characters. For example, test* matches any text that includes "test", such as testfile.txt.
[] | Set of characters | Matches any one of the characters specified in the set.

expandedtoggleicon(9x12) See Also

Find and Replace Text and Code

User Interface Concepts


Reference

ExpandedToggleIcon(9x12) In This Section

Database Connection
This section describes Data Connection user interface elements.

Database Explorer
This section describes Database Explorer user interface elements.

Data Editor
This sections provides information on data managing windows and dialogs.

Data Export Wizard
Use the wizard to perform a quick data export.

Diagram Overview Window
Describes the Diagram Overview window.

Execute Script Wizard
Describes how to execute large scripts without opening them in editor.

Find and Replace Window
The topic describes the Find and Replace window that allows you to search for text, expressions, or entity names within the code of your documents.

Options
The topics in this section describe dialog boxes and wizards that are to perform commands and to configure pages and controls.

Query Builder
This section contains information about main components of the query document - the diagram and the tabbed editor.

Query Profiling
This section gives description of Query Profiling interface elements.

Service Windows
This section describes user interface elements of service windows and dialogs.

Shortcut Keys
Describes shortcut keys.

SQL Document
This section describes SQL Document user interface elements.


Database Connection

This section describes Data Connection user interface elements.

note Note

Database Explorer is not available in the following products:

· dbForge Data Compare for SQL Server
· dbForge Schema Compare for SQL Server
· dbForge Data Generator for SQL Server
· dbForge Event Profiler for SQL Server

ExpandedToggleIcon(9x12) In This Section

Database Connection Properties Dialog Box
Information about connection parameters.

Connect to SQL Database Dialog Box
The topic helps you to connect to the server without having specified a password.

Browse for Servers Dialog Box
The topic helps you to select local and network servers.

Connection Manager Dialog Box
The topic helps you to create, edit, and delete database connections.

Connect to Server Dialog Box
The topic helps you to connect to the server when the Database Explorer window is not activated.


Database Connection Properties Dialog Box

Use the Database Connection Properties dialog box to setup or change the database connection parameters. There are several ways to access the Database Connection Properties dialog box:

· Click New Connection on the Database Explorer window toolbar.
· In Database Explorer, open the shortcut menu for the data connection you want to modify and choose Modify Connection.
· In the Database menu, click New Connection.
· Right-click in the Database Explorer, and then select New Connection on the shortcut menu.

note Note

Database Explorer is not available in the following products:

· dbForge Data Compare for SQL Server
· dbForge Schema Compare for SQL Server
· dbForge Data Generator for SQL Server
· dbForge Event Profiler for SQL Server

The Database Connection Properties dialog box includes the following tabs:

General, Database Connection Properties Dialog Box
Allows you to set the login information required to connect to the SQL Server.

Advanced, Database Connection Properties Dialog Box
Allows you to set additional options to tune database connections.

ExpandedToggleIcon(9x12) See Also

Database Connection

Browse for Servers Dialog Box


General, Database Connection Properties Dialog Box

Use the General tab of the Database Connection Properties dialog box to view and manage the following parameters of an SQL Server database connection:

Server
Select the server instance to connect to. The server instance last connected to is displayed by default.

Authentication
Two authentication modes are available when connecting to an instance of the Database Engine.

Windows Authentication
Microsoft Windows Authentication mode allows a user to connect through a Windows user account.

SQL Server Authentication
When a user connects with a specified login name and password from a non-trusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails, and the user receives an error message.

Active Directory Password Authentication
Allows connecting to MicrosoftAzure SQL Database with help of identities in Azure Active Directory. Use this method for connecting to SQL Database if you are logged into Windows using credentials from a domain that is not federated with Azure, or when using Azure AD authentication using Azure AD based on the initial or the client domain.

Active Directory Integrated Authentication
Allows connecting to MicrosoftAzure SQL Database by using identities in Azure Active Directory. Use this method for connecting to SQL Database if you are logged into Windows using your Azure Active Directory credentials from a federated domain.

Login
Enter the login to connect with. This option is not available if you have selected to connect using Windows Authentication.

Password
Enter the password for the login. This option is not available if you have selected to connect using Windows Authentication.

Allow saving password
If selected, the password is stored among the other connection parameters.

Database
Select a name of an SQL Server database you want to connect to.

Show all databases
Select the check box to show all databases.

Connection Name
Use this text box, to specify the connection name.

Document Category
You can assign a category for a document to associate it with a specific connection.

Test Connection
Click this button to make the test connection to a database.

ExpandedToggleIcon(9x12) See Also

Database Connection Properties Dialog Box

Advanced, Database Connection Properties Dialog Box


Advanced, Database Connection Properties Dialog Box

The Advanced properties tab provides the following options:

Connection timeout
Sets the length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15 seconds.

Execute timeout
Sets the length of time (in seconds) to wait for a query execution before terminating the attempt and generating an error.

Encrypt connection
Forces encryption of the connection.

To add more connection parameters to the connection string, use the additional connection parameters.

Keyword

Default

Description

Asynchronous Processing

When true, enables asynchronous operation support. Recognized values are true, false, yes, and no.

AttachDBFilename

"false"

The name of the primary database file, including the full path name of an attachable database. AttachDBFilename is only supported for primary data files with an .mdf extension. The attachment will fail if the primary data file is read-only. The path may be absolute or relative.

Connection Timeout

15

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

Context Connection

"false"

true if an in-process connection to SQL Server should be made.

Current Language

The SQL Server Language record name.

Data Source

The name or network address of the instance of SQL Server to which to connect. The port number can be specified after the server name: server=tcp:servername, portnumber. When specifying a local instance, always use (local). To force a protocol, add one of the following prefixes: np:(local), tcp:(local), lpc:(local)

Encrypt

"false"

When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no.

Failover Partner

The name of the failover partner server where database mirroring is configured.

Initial Catalog

The name of the database.

Integrated Security

"false"

Whether the connection is to be a secure connection or not. Recognized values are 'true', 'false', and 'sspi', which is equivalent to 'true'.

MultipleActiveResultSets

"false"

When true, an application can maintain multiple active result sets (MARS). When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection. Recognized values are true and false.

MultiSubnetFailover

Always specify multiSubnetFailover=Yes when connecting to the availability group listener of a SQL Server 2012 availability group or a SQL Server 2012 Failover Cluster Instance. multiSubnetFailover=Yes configures SQL Server Native Client to provide faster detection of and connection to the (currently) active server. Possible values are Yes and No.

Network Library

The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol, Windows RPC), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory, local machine only) and dbmsspxn (IPX/SPX), dbmssocn (TCP/IP) and Dbmsvinn (Banyan Vines).

The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, "." or "(local)"), shared memory is used.

Packet Size

Size in bytes of the network packets used to communicate with an instance of SQL Server.

Password

The SQL Server password.

Persist Security Info

"false"

When set to 'false' (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password.

Replication

"false"

true if replication is supported using the connection.

TrustServerCertificate

"false"

When set to true, SSL is used to encrypt the channel when bypassing walking the certificate chain to validate trust. If TrustServerCertificate is set to true and Encrypt is set to false, the channel is not encrypted. Recognized values are true, false, yes, and no.

Type System Version

A string value that indicates the type system the application expects. Possible values are:

· Type System Version=SQL Server 2000;
· Type System Version=SQL Server 2005;
· Type System Version=SQL Server 2008;
· Type System Version=Latest;

When set to SQL Server 2000, the SQL Server 2000 type system is used. The following conversions are performed when connecting to a SQL Server 2005 instance:

· XML to NTEXT
· UDT to VARBINARY
· VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) to TEXT, NEXT and IMAGE respectively.

When set to SQL Server 2005, the SQL Server 2005 type system is used. No conversions are made for the current version of ADO.NET.

When set to Latest, the latest version than this client-server pair can handle is used. This will automatically move forward as the client and server components are upgraded.

User ID

The SQL Server login account.

User Instance

"false"

A value that indicates whether to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller.

Workstation ID

The local computer name

The name of the workstation connecting to SQL Server.

ExpandedToggleIcon(9x12) See Also

Database Connection Properties Dialog Box

Browse for Servers Dialog Box


Connect to SQL Database Dialog Box

The dialog box appears when you connect to the server without having specified a password.

Authentication
Specify the required authentication mode here.

User
Enter the name for the SQL login account.

Password
Enter the password for the SQL login account.

Server
Specify the server you want to connect to.

Database
Specify the database you want to work with.

ExpandedToggleIcon(9x12) See Also

Database Connection

Browse for Servers Dialog Box


Browse for Servers Dialog Box

Use the Browse for Servers dialog box to select the local and network servers.

This dialog box appears when you click <Browse...> in the Server combo-box (which is located on the General tab of the Database Connection Properties dialog box)

The Database Connection Properties dialog box provides the next members:

Local Servers
Displays the list of local servers.

Network Servers
Allows you to select an SQL Server instance in the network.

Refresh
Refreshes the list of network servers.

ExpandedToggleIcon(9x12) See Also

Database Connection

Connect to Server Dialog Box


Connection Manager Dialog Box

Connection Manager dialog box allows you to create, edit, and delete database connections.

ExpandedToggleIcon(9x12) Dialog Box Controls

Dialog box contains the following controls:

Connections
The list of the existing database server connections.

New
Click to open Database Connection Properties dialog box for creating a new connection.

Edit
Click to open Database Connection Properties dialog box for editing a connection.

Delete
Click to delete the connection.

Connection String
Displays a connection string, containing the connection parameters, for the selected connection.

ExpandedToggleIcon(9x12) See Also

Database Connection

Connect to Server Dialog Box


Connect to Server Dialog Box

Use the dialog box to connect to the server when the Database Explorer window is not activated.

note Note

Database Explorer is not available in the following products:

· dbForge Data Compare for SQL Server
· dbForge Schema Compare for SQL Server
· dbForge Data Generator for SQL Server
· dbForge Event Profiler for SQL Server
· dbForge Documenter for SQL Server

Connections
Displays information on available server connections.

Server
Specify the server connection you want to create a new SQL document in.
Database
Specifies a database you want ot connect to.
User
Specifies a connection user login.
Name
Specifies a selected server database name.

ExpandedToggleIcon(9x12) See Also

Database Connection


Database Explorer

ExpandedToggleIcon(9x12) In This Section

Database Explorer Window
Database Explorer is a connection management console. Use the Database Explorer window to open data connections and to log on to servers and explore their databases.

Filter Settings Dialog Box
Use this dialog to create a filter for database objects in accordance with certain criteria.

Node Icons
This section lists and describes the icons of Database Explorer.

Database Explorer Shortcut Keys
Use the shortcuts to speed up the work in the Database Explorer.

Database Explorer Nodes' Properties
This section describes what properties are owned by Database Explorer's nodes.


Database Explorer Window

Database Explorer is a connection management console. Use the Database Explorer window to open data connections and to log on to servers and explore their databases.

To make the Database Explorer window close automatically when not in use, select Auto Hide from the Window menu.

Toolbar

You can find the following buttons on the toolbar in the Database Explorer window:

Refresh button Refresh - to synchronize the current view with changes that other users might be applying to the server.

Stop Refresh Stop Refresh - to stop refreshing when it takes too long.

New Connection - to Database Connection Properties dialog box.

OpenConnectionDatabaseExplorer Open Connection - open a connection selected in Database Explorer.

CloseConnectionDatabaseExplorer Close Connection - close a connection selected in Database Explorer.

Show-AllObjects Show All Objects - to show all the objects of the selected connection, otherwise Query Builder for SQL Server shows only the objects of the default database for the selected connection.

Filter Filter - filter the child elements of selected node.

ObjectViewerWindow Object Viewer - show the Object Viewer window.

note Note

The availability of the toolbar options depends on the product you use.

Shortcut Menu

The shortcut menu of the Database Explorer window provides quick access to a wide range of options that differs depending on the selected database object. For example, for tables and views available in a database you can choose the Send To... option, and open the selected object in the Query Builder.

ExpandedToggleIcon(9x12) See Also

Database Explorer

Node Icons


Filter Settings Dialog Box

Use the Filter Settings dialog to create a filter for database objects in accordance with certain criteria. To open the dialog, right-click a required object category node in the Database Explorer and select Filter from the menu, or select an object category node and click Filter on the Database Explorer toolbar.

Server
Displays the server version.

Database
Displays the database.

Criteria

Property
Specify the statistics name, owner, or schema to be filtered.

Operator
Choose the operator for the filter.

Pattern
Specify the pattern which goes with the corresponding criteria.

All
If checked, the filtered object will comply with all criteria.

Any
If checked, the filtered object will comply with any criteria.

ExpandedToggleIcon(9x12) See Also

Database Explorer

Node Icons


Node Icons

This table lists and provides explanations of the Database Explorer icons.

Icons | Description
Connection | A connection to a server
closed-connection | A closed connection
InvalidConnection | A stand-by or read-only database
Database | A database on a server
restoring-database | A database in the restoring state
offline-database | An offline database
Table | A table
PartitionedTable | A partitioned table
NonexistentObject | A non-existent object - one that does not exist in the schema or the server any longer
InvalidObject | An invalid object - one that is defined as invalid in the database or the program can't get all its properties
Column | A column that is contained by a table or referenced by an index
view | A view
view-lock | An encrypted view
scalar-valued-function | A scalar-valued function
table-valued-function | A table-valued function
aggregate-function | An aggregate function

ExpandedToggleIcon(9x12) See Also

Database Explorer

Database Explorer Shortcut Keys


Database Explorer Shortcut Keys

Use following shortcut key combinations when working in the Database Explorer to speed up your work

Shortcut Keys | Action | Where is used
F4 | View properties of currently selected Database Explorer node. | Database Explorer
F5 | Refresh selected node and its child nodes in the Database Explorer | Database Explorer
Enter | Same as double-click. | Database Explorer

These shortcuts are default shortcuts. Most shortcuts can be customized.

ExpandedToggleIcon(9x12) See Also

Database Explorer

Database Explorer Nodes' Properties


Database Explorer Nodes' Properties

This section describes what properties are owned by Database Explorer's nodes.

ExpandedToggleIcon(9x12) In This Section

Connection Properties
Lists properties of a connection.

Table Properties
Lists properties of a table.

Column Properties
Lists properties of a column.

Index Properties
Lists properties of an index.

Function Properties
Lists properties of a function.

View Properties
Lists properties of a view.

Database Properties
Lists properties of a database.

ExpandedToggleIcon(9x12) See Also

Database Explorer


Connection Properties

This node represents a connection to SQL Server and has the following properties:

Client version
Displays version of the SQL Server client, that is used by this connection.

Connection String
Represents connection parameters in a single string.

Database
The name of the master database.

Document Category
The name of the document category.

Open
Identifies whether a connection is active or not.

Save Password
Shows whether the password should be saved.

Server Version
Displays version of the server connected.

ExpandedToggleIcon(9x12) See Also

Table Properties


Table Properties

This node represents SQL Server table and has the following properties:

(Name)
The name of the table.

Backed Up
Indicates whether the table was backed up.

Cluster Name
Shows name of a cluster if one is used to store table data.

Compression
Shows whether data segments in the table are compressed.

Created
Shows creation date.

Flashback Archive Name
Shows the name of the flashback data archive.

Id Is Primary Key
Indicates that the object identifier (OID) of the object table is based on the primary key of the table.

Index Organized
Shows whether the table is index-organized.

Is Object Based Table
Shows whether the table is object-based.

Is Read Only
Shows whether the table is in read-only mode.

Modified
Shows when the table was modified last.

Object Name
Name of the object, which table is based on.

Object Owner
Owner of the object, which table is based on.

Owner
Shows schema that contains the table.

Partitioned
Shows whether the table is partitioned.

Result Cache
Shows the result cache size.

Row movement
Shows if the row movement mode is enabled in the table.

Status
Indicates whether the table is valid or not.

Temporary
Indicates whether the table is temporary.

Temporary Session
If selected, the rows of the table will be preserved. Applicable only for temporary tables.

ExpandedToggleIcon(9x12) See Also

Column Properties


Column Properties

This node represents table column and has the following properties:

(Name)
The name of the column.

Char Used
Specifies the measurement type for Char. Values are CHAR and BYTE

Cluster Name
Shows name of cluster that holds data for the column.

Compute Expression
Generates totals that appear as additional summary columns at the end of the result set.

Data Type
This is the type of the column.

Data Type Owner
If DataType is user-defined, this property indicates its owner, otherwise it is empty.

Default On Null
Shows whether the column default value is NULL.

Default Value
Specifies a default value that will be entered in the column.

Is Nested Table
Indicates whether the column is NESTED TABLE column or not.

Is Persistent
Determines whether or not an event persists with its document.

Length
Shows size of the column in bytes.

Nullable
Shows whether the column can be of NULL value.

Owner
Table that contains the column.

Precision
Displays precision for the column if set.

Reference
Indicates whether the column has REF attribute.

Scale
This property shows how many decimals are in the column.

XML Schema Collection
Displays the name of XML schema collection, that is assigned to the column.

ExpandedToggleIcon(9x12) See Also

Index Properties


Index Properties

These nodes represent SQL Server indexes. Unique indexes are marked by blue key icon UniqueIndex, non-unique indexes are rendered with icon CommonIndex. Indexes have the following properties:

(Name)
Specifies the name of the index.

Allow Page Locks
Specifies whether or not to allow page level locking.

Allow Row Locks
Specifies whether or not to allow row level locking.

Clustered
Reports index clustering on Microsoft SQL Server primary keys.

File Stream Option
Exposes a stream around a file, supporting both synchronous and asynchronous read and write operations.

Filegroup
Specifies the filegroup where the index will be created.

Fillfactor
Specifies how full SQL Server should make the leaf level of each index page when creating the index.

Filter
Returns the set resulting from filtering a specified set based on a search condition.

Ignore Dup Key
This option specifies the error response when an insert operation attempts to insert duplicate key values into a unique index.

Is Disabled
Sets the Boolean property value that specifies whether the index is disabled.

Is Filegroup Default
Specifies a default filegroup. If it is not specified, the default is the primary filegroup.

Is Primary
This filegroup contains the primary data file and any other files not placed into another filegroup. All pages for the system tables are allocated from the primary filegroup.

Is System Named
Gets the boolean property value that specifies whether the index was named automatically by the system.

Is Unique Constraint
Use the constraint to make sure that no duplicate values are entered in specific columns that do not participate in a primary key.

Owner
The table that contains the index.

Pad Index
Specifies the space to leave open on each page in the intermediate levels of the index.

Partition Column
Stores the index in a partition column.

Partition Scheme
Stores the index in a partition scheme.

StatisticsNorecompute
The NoRecompute property controls statistics generation when the Index object is used to create a Microsoft SQL Server index.

Unique
Selecting this check box makes the index unique.

ExpandedToggleIcon(9x12) See Also

Database Explorer Nodes' Properties

Function Properties


Function Properties

This node represents SQL Server function and and has the following properties:]

(Name)
Specifies the name of a function.

Owner
The database that contains a function.

ExpandedToggleIcon(9x12) See Also

Database Explorer Nodes' Properties

View Properties


View Properties

This node represents SQL Server view and has the following properties:

(Name)
The name of the view.

Create Date
Shows creation date.

Modify Date
Shows modification date.

Owner
Database that contains the view.

Principal Name
Specifies the name of a view principal.

ExpandedToggleIcon(9x12) See Also

Database Explorer Nodes' Properties


Database Properties

Represents a database and has the following properties:

Collation Name
A single string that specifies the collation name for a SQL collation.

Compatibility Level
Sets certain database behavior to be compatible with a specified earlier version of SQL Server,

Containment
Controls database containment options.

Create Date
Specifies the database creation date.

Default Fulltext Language
Specifies a default language value for full-text indexed columns.

Default Language
Specifies the default language for all newly created logins.

Directory Name
Specifies a directory name for the data associated with the selected database.

Is Ansi Null Default
Alters the session's behavior to override default nullability of new columns when the ANSI null default option for the database is false.

Is Ansi Nulls
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

Is Ansi Padding
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

Is Ansi Warnings
Specifies ISO standard behavior for several error conditions.

Is Arithabort
Terminates a query when an overflow or divide-by-zero error occurs during query execution.

Is Auto Cleanup
Indicates whether change tracking information is automatically removed after the specified retention period.

Is Auto Close
Specifies whether a database shuts down cleanly and frees resources after the last user exits.

Is Auto Create Stats
Specifies whether a database automatically creates missing optimization statistics.

Is Auto Shrink
Specifies whether a database files are available for periodic shrinking.

Is Auto Update Stats
Specifies whether a database automatically updates out-of-date optimization statistics.

Is Auto Update Stats Async
Subsequent queries will use the updated statistics when they are available.

Is Concat Null Yields Null
Controls whether concatenation results are treated as null or empty string values.

Is Cursor Close On Commit
Controls the behavior of the Transact-SQL COMMIT TRANSACTION statement. The default value for this setting is OFF. This means that the server will not close cursors when you commit a transaction.

Is Cursor Default
Specifies the default cursor behavior.

Is Db Chaining
Configures the database ownership chaining for the instance of SQL Server.

Is Numeric Roundabort
Specifies the level of error reporting generated when rounding in an expression causes a loss of precision.

Is Quoted Identifier
Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

Is Read Commited Snapshot
Isolation level based on row versioning.

Is Snapshot Isolation
Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.

Name
Displays a current database name.

Nested Triggers
Creates a DML, DDL, or logon trigger.

Non Transacted Access
The available level of the non-transactional access.

Owner
Specifies an owner of the current database.

Principal Name
The value set for this attribute is equal to the length of the user's ID and the domain name.

Recovery
Specifies a recovery model.

Retention Period
Specifies the minimum period for keeping change track information in the database. Data is removed only if the Auto Clean-Up value is True. The default value is 2.

Retention Period Units
Specifies the units for the Retention Period value. You can select Days, Hours, or Minutes.

Transform Noise Words
Suppresses an error message if noise words, that are stopwords, cause a Boolean operation on a full-text query to return zero rows.

Two Digit Year Cutoff
Indicates the highest year number that can be entered as a two-digit year. The year listed and the previous 99 years can be entered as a two-digit year. All other years must be entered as a four-digit year.

ExpandedToggleIcon(9x12) See Also

Database Explorer Nodes' Properties

Database Explorer


Data Editor

This section provides information on data managing windows and dialogs.

ExpandedToggleIcon(9x12) In This Section

Data Viewer and Editor
Describes how to view and edit fields of large data types.

Custom AutoFilter Dialog Box
Tells how to build a custom filter for a data column.

Filter Editor Dialog Box
Describes how to build complex filter conditions for table data.

Visible Columns Dialog Box
Tells how to filter data by hiding some columns.

Data Editor Window
Describes user interface elements of the Data Editor window.


Data Viewer and Editor Window

Data Viewer and Editor is a tool which allows simple viewing and editing fields.

The control automatically detects type of the content of the BLOB field and represents it as an image, text, rich text or hex dump. To access Data Viewer and Editor, on the View menu, click Data Viewer and Editor. Then just select the text or BLOB field, and its content appears in the Data Viewer and Editor window.

load-data Load Data
Replaces the cell content with the data from the file. It opens the Open File dialog where you can select a required file and specify the encoding which should be applied to open and save the file content.

Save Save Data
Opens the Save File As dialog to save data to a file.

note Note

If you try to save to database more data than can be stored in the selected column type, the data will be truncated.

Save Data at the Left
Saves data displayed in the left part of the window to a file.

Save Data at the Right
Saves data displayed in the right part of the window to a file.

note Note

The Save Data at the Left and Save Data at the Right options are available for comparison results.

null-data Null Data
Sets the cell value to NULL.

Apply Changes
Click to apply changes.

cancel-changes Cancel Changes
Click to cancel changes.

copy-cell Copy Entire Cell Value
Copies the cell data into the clipboard.

Paste Entire Cell Value
Replaces the cell data with the data from the clipboard. The format of the pasted data is automatically detected and the required view is set.

cut-cell Cut Entire Cell Value
Cut the cell data placing it to the clipboard.

Hexadecimal View
Shows cell data as hex dump.

Text View
Shows cell data as a text.

xml-view XML View
Shows the cell data as structured XML.

json-icon JSON View
Shows cell data as a JSON string.

Rich Text View
Shows cell data as a rich text.

Image View
Shows cell data as an image.

geometry-viewer Spatial View - Geometry
Shows geometry data as an image. The image in the view can be moved with the mouse pointer. The image can be also zoomed in and out. To zoom the image in, hold down the Ctrl key and roll the mouse wheel forward. To zoom the image out, hold down the Ctrl key and roll the mouse wheel backward.

FitScale Fit Image
Fits an image to the size of the window.

View White Space
Shows each white space with a green character.

Type | Size
Shows the type and size of the displayed data. If data cannot be displayed in the selected view, the Type will be set to Unknown.

note Note

You can copy all data in the cell by using the CTRL+SHIFT+C shortcut. Format of the copied data will depend on the selected view. For example, if the Hexadecimal view is selected, the data will be copied in hex order; data in the Text view, will be copied in text format, etc.

ExpandedToggleIcon(9x12) See Also

Data Editor

Custom AutoFilter Dialog Box


Custom AutoFilter Dialog Box

Custom AutoFilter dialog is used to create a custom filter for a data grid column. It is displayed when choosing Custom in the grid drop-down filter list.

Using this dialog, you can build a custom data filter with two conditions for a column. Condition relation can be selected from the drop-down list in the left box and value for relation can be entered in the right box. Select And or Or option, and click OK to apply the filter.

ExpandedToggleIcon(9x12) See Also

Data Editor

Filter Editor Dialog Box


Filter Editor Dialog Box

Use the Filter Editor dialog to create complex filter conditions to filter data in the data grid. To open this dialog, right-click a required column and select the [image] Condition item from the Quick Filter sub-menu.

Filter conditions in the Filter Editor dialog box are displayed as a tree with condition and group nodes. A group consists of several conditions or subgroups, united with a logical operator.

Condition Tree

The upper part of the Filter Editor dialog is a condition tree. Its nodes are red-colored condition groups and conditions, started with blue-colored column name.

Condition Group

It is displayed as a red-colored logical operator and a FilterGreyAdd button. Clicking the FilterGreyAdd button adds a new condition to group. Clicking the logical operator displays the following menu:

And
Sets And logical operator for the group. All group conditions must be true.

Or
Sets And logical operator for the group. At least one condition from the group must be true.

Not And
Sets Not And logical operator for the group. At least one condition from the group must be false.

Not Or
Sets Not Or logical operator for the group. All group conditions must be false.

Add Condition
Adds a new condition to the group.

Add Group
Adds a new condition group to the current group.

Clear All
This command is available for the top-level condition group only. Removes all conditions and subgroups, leaving only the top-level group node.

Remove Group
This command is available for the non-top-level condition group only. Removes the condition group.

Condition

It consists of the following parts:

Condition column
Blue-colored name of the grid column, to which condition will be applied. You can set another column for the condition by clicking the column name and choosing the column from the appeared menu.

Criteria operator
Green-colored criteria operator. You can set another operator for the condition by clicking the operator name and choosing the operator from the appeared menu.

Operand value
Click and enter the operand value here. Some operators have no operand values (for example Is blank operator), some may have several values.

FilterGreyAdd
This button appears for operators that can have one or more operand values. Click it to add one more operand value.

FilterGreyDel
Click this button to delete the condition. You can also navigate to the condition and press the DELETE or MINUS SIGN key

To navigate between conditions and groups, use the UP ARROW and DOWN ARROW keys. To navigate between a condition column, a criteria operator and a operand value, use the LEFT ARROW and RIGHT ARROW keys.

Buttons

OK
Apply the filter and close Filter Builder dialog.

Cancel
Close Filter Builder dialog and cancel all filter changes.

Apply
Apply the filter and without closing Filter Builder dialog.

ExpandedToggleIcon(9x12) See Also

Data Editor

Visible Columns Dialog Box


Visible Columns Dialog Box

Use the Visible Columns dialog to simplify data analysis and filter data by hiding some columns in the grid. To open this dialog, select Visible Columns from the column shortcut menu. The Visible Columns dialog contains the following:

List of columns
Clear a check box next to a column to hide the column data in the grid. Select a check box to show the column data again.

Select / Deselect All
Select this check box to show all the columns at once. Clear the check box to manually select required columns.

Sort alphabetically
Select it to show the columns in alphabetical order.

ExpandedToggleIcon(9x12) See Also

Data Editor

Data Editor Window


Data Editor Window

This topic describes the Data Editor window user interface elements.

RefreshButton Refresh Data
Use this option to refresh data.

Stop Refresh Data
Use this option to stop refresh operation.

Cached Updates Mode
Allows you to control data editing within single database object.

Apply Changes
Click to store your changes to the server.

Cancel Changes
Click in case you do not want the changes to be applied.

Paginal Mode
Use the Paginal Mode to simplify data view by dividing it into parts.

Go To First Page
Click the Go To First Page button to open the first part page of data.

Go To Previous Page
Click the Go To Previous Page button to open the previous part page of data.

Go to Next Page
Click the Go to Next Page button to open the next part page of data.

Go To Last Page
Click the Go To Last Page button to open the last part page of data.

Switch To Grid View
Click the Switch To Grid View button on the data view toolbar to switch to the Grid View mode.

Switch To Card View
Click the Switch To Card View button to switch to the Card View mode.

Show Group By Box
Click the Show Group By Box button to group data by column.

Auto-search mode
Click the Auto-search mode button to switch to the Auto-search mode. You can also switch to the Auto-search mode by pressing CTRL+I keys.

Data Editor Grid
Contains editable data. It could be represented as a table in the Grid View mode as well as cards in the Card View mode.

First First
Click the First button to switch to the first record field.

Previous Previous
Click the Previous button to switch to the previous record field.

Next Next
Click the Next button to switch to the next record field.

Last Last
Click the Last button to switch to the last record field.

Append Append
Click the Append button to insert more records.

Delete Delete
Click this button to delete the records.

End Edit
Click this button to confirm the changes.

Cancel Edit
Click the Cancel Edit button to discard the changes.

ExpandedToggleIcon(9x12) See Also

Data Editor


Data Export Wizard

Make your data export quick and convenient using the Data Export wizard. You can access the Data Export wizard by doing either of these actions:

· On the Start Page, click Export Data.
· In Database Explorer, right click the required table or view (or Ctrl+click several tables and/or views) you want to export data from and select Export Data from the shortcut menu.
· In Data Editor Window, select Export Data from the shortcut menu, or click the DataExport Export Data button on the Data Editor Window toolbar.
· On the Database menu , select Export Data.

See the list of the wizard's pages with their options below.

ExpandedToggleIcon(9x12) In This Section

Source, Data Export wizard
Select a required SQL Server server connection, a database, and a table where the data you want to export is located.

Export Format, Data Export wizard
Choose a required data export format on this page.

Options, Data Export wizard
Tells how to set options to customize data export into any of 10 available export formats.

Table columns, Data Export Wizard
Choose columns and specify the key for export.

Table, Data Export wizard
Choose a table for data export. Available only for MS Access and ODBC formats.

Data Formats, Data Export wizard
Choose columns for export, change their name and data type, and customize formats for data representation (if available) on this page.

Output Settings, Data Exprot Wizard
Set output settings for exported data on this page. Available for the JSON, XML, TXT and CSV formats only.

Page Print Settings, Data Export wizard
Choose a page size and orientation, set margins, and enter a text for a table's header and footer on this page. Available only for MS Excel, RTF, and PDF formats.

Exported Rows, Data Export wizard
Specify a range of exported rows on this page.

Errors handling, Data Export wizard
Specify the errors handling behavior.

Export Progress, Data Export wizard
See the export progress on this page.

Finish, Data Export wizard
See the result of export process and select whether to open the exported file after the export on this page.


Export Format, Data Export Wizard

Use this wizard page to select an export format, load a user template, and specify a destination file or table. The page contains the following items:

Categories

All Items
Select it to see both export formats and user templates in the Templates section.

Formats
Select it to see only the list of export formats in the Templates section.

User Templates
Select it to see only the list of user templates in the Templates section.
Templates allow to view and select the items of the selected category. Click an export format or a saved template to use them. To load a user template, double-click the Load Template... icon.

note Note

To export data into MS Access, ensure that Microsoft Data Access Components (MDAC) version 2.6 or later is installed on your PC. If you don't have MDAC, click here to download

Large icons
Click it to see the items marked with large icons in the Templates section.

Small icons
Click it to see the items marked with small icons in the Templates section.

ExpandedToggleIcon(9x12) See Also

Options, Data Export Wizard

Data Export Wizard


Source, Data Export Wizard

Use this wizard page to select a required SQL Server server connection, a database, and a table/view where the data you want to export is located. The page contains the following details:

Connection

Click the field to select a required SQL Server server connection from the drop-down list. By default the connection highlighted in the Database Explorer is selected. In the drop-down list you can click the <Manage...> link to open the Connection Manager dialog box. It will help you to create, edit, and delete database connections.

Edit
Click to edit the properties of the selected connection. The Database Connection Properties dialog box opens. For more information, go to How To: Manage Database Connections topic.

New
Click to create a new SQL Server server connection. The Database Connection Properties dialog box opens. For more information, go to How To: Manage Database Connections topic.

Database

Select a database located on the selected SQL Server server. In the drop-down list you can click the <Refresh...> link to refresh the list of databases available on the selected SQL Server server, if any changes were made.

Tables and Views
Select a table or view you want to export the data from. The list shows all the tables of the selected database.

Filter
Type the filter criteria in this field, and tables and views answering this criteria will be displayed in the Tables and Views field of this page.

File name
Click the Browse button to specify a name and a path of the destination file (the one where the exported data will be saved.) This field is not available for ODBC and MS Access destination formats.

Folder name
Click the Browse button to specify the path to store the destination files (the one where the exported data will be saved.) This field is available more than one table is selected.

Export selected tables into single file
Check this option to export selected tables into a single file. This option is available for SQL destination format only.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Export Format, Data Export Wizard

Data Export Wizard


Options, Data Export Wizard

Find how to set options to customize data export into any available export format in this section.

ExpandedToggleIcon(9x12) In This Section

CSV Document Options, Data Export wizard
Choose values for a column separator and a quoted string. Set Unicode support for the exported data on this page.

dBase, FoxPro Database Options, Data Export wizard
Specify a number of digits after the decimal point on this page.

HTML Format Options, Data Export wizard
Customize a header, borders, and rows for the exported data. Choose colors for a table's text and background, set a font, and preview the results on this pages.

Microsoft Access Database Options, Data Export wizard
Enter a table's name and password on this page.

Microsoft Excel Format Options, Data Export wizard
Customize a header, borders, and rows for the exported data. Choose colors for a table's text and background, set a font, and preview the results on this page.

ODBC Data Providers Options, Data Export wizard
Specify a system data source name or customize a connection string on this page. Provide login information if your data source requires.

Portable Document Format Options, Data Export wizard
Customize a header, borders, and rows for the exported data. Choose colors for a text and a background, set a font, and preview the results on this page.

Rich Text Format Options, Data Export wizard
Customize a header, borders, and rows for the exported data. Choose colors for a table's text and background, set a font, and preview the results on this page.

Text Document Options, Data Export wizard
Choose a text alignment and a column length, include or hide a table's header and a grid, and set Unicode support on this page.

XML Document Options, Data Export wizard
Set Unicode support for the exported data and select whether to export information about a table on this page.

SQL Document Options, Data Export Wizard
Specify a table name and select a statement type for exported data.

ExpandedToggleIcon(9x12) See Also

Data Export Wizard

Table columns, Data Export Wizard


HTML Format Options, Data Export Wizard

Use this wizard page to specify options for data export into HTML format and preview the results in the Preview section. The Options page contains the following details:

Header

Customize a header's text and background choosing a required color from the drop-down lists.

1. Click the arrow button in the field to open the drop-down list.
2. Switch to either the Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Click the Font button to change the font settings. Select a font, a style, a size, and a script; add font effects and click OK. The selected font is shown next to the Font button.

Borders

Choose a width and color of borders and preview the results.

Rows

Choose required colors for the text and background of rows and change font settings as was described in Header section. See any changes in the Preview section.

Preview
Here you can look at the sample data with applied format options before exporting.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Text Document Options, Data Export Wizard

Data Export Wizard


Text Document Options, Data Export Wizard

Use this wizard page to specify options for data export into Text format and preview the results in the Sample section. The Options page contains the following details:

Use Unicode
Select this check box to ensure the exported data will support Unicode.

Tab
Choose either to keep tabs or insert spaces.

Text alignment
Select a text alignment from the drop-down list. Preview your changes in the Sample section.

Column length
Use the arrow buttons in the field to choose a column length.

Show a table header
Clear the check box to hide a table header. See the result in the Sample section.

Show a grid
Clear the check box to hide a table grid. See the result in the Sample section.

Horizontal header
If required, change the default character for horizontal header border. Preview your changes in the Sample section.

Vertical header
If required, change the default character for vertical header cell border. Preview your changes in the Sample section.

Horizontal
If required, change the default character for horizontal cell border. Preview your changes in the Sample section.

Vertical
If required, change the default character for vertical cell border. Preview your changes in the Sample section.

Sample
Here you can look at the sample data with applied format options before exporting.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Microsoft Excel Format Options, Data Export Wizard

Options, Data Export Wizard


Microsoft Excel Format Options, Data Export Wizard

Use this wizard page to specify options for data export into Microsoft Excel format and preview the results in the Preview section. The Options page contains the following details:

Header

Select to include header to the export file.

Text
Select the text color from the drop-down list. Switch to either Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Background
Select the background color from the drop-down list. Switch to either Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Font
Click the button to to change the font settings. Select a font, a style, a size, and a script; add font effects and click OK. The selected font is shown next to the Font button.

Borders

Choose a width and color of borders and preview the results.

Rows

Choose required colors for a text and a background of rows and change font settings as was described in Header section. See any changes in the Preview section.

Preview

Here you can look at the sample data with applied format options before exporting.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Microsoft Access Database Options, Data Export Wizard

Options, Data Export Wizard


Microsoft Access Database Options, Data Export Wizard

Use this wizard page to specify a file name and a password for a database here. The Options page contains the following details:

Password
Enter a password for a database.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Rich Text Format Options, Data Export Wizard

Options, Data Export Wizard


Rich Text Format Options, Data Export Wizard

Use this wizard page to specify options for data export into RTF format and preview the results in the Preview section. The Options page contains the following details:

Header

Select to include header to the export file.

Text
Select the text color from the drop-down list. Switch to either Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Background
Select the background color from the drop-down list. Switch to either Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Font
Click the button to to change the font settings. Select a font, a style, a size, and a script; add font effects and click OK. The selected font is shown next to the Font button.

Borders

Choose a width and color of borders and preview the results.

Rows

Choose required colors for a text and a background of rows and change font settings as was described in Header section. See any changes in the Preview section.

Use Unicode
Select this check box to ensure the exported data will support Unicode.

Preview

Here you can look at the sample data with applied format options before exporting.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Portable Document Format Options, Data Export Wizard

Options, Data Export Wizard


Portable Document Format Options, Data Export Wizard

Use this wizard page to specify options for data export into portable document format (PDF) and preview the results in the Preview section. The Options page contains the following details:

Header

Select to include header to the export file.

Text
Select the text color from the drop-down list. Switch to either Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Background
Select the background color from the drop-down list. Switch to either Custom, or Web, or System tab to select a required color. You can see the selected color in the Preview section at the bottom of the page.

Font
Click the button to to change the font settings. Select a font, a style, a size, and a script; add font effects and click OK. The selected font is shown next to the Font button.

Borders

Choose a width and color of borders and preview the results.

Rows

Choose required colors for a text and a background of rows and change font settings as was described in Header section. See any changes in the Preview section.

Preview
Here you can look at the sample data with applied format options before exporting.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

XML Document Options, Data Export Wizard

Options, Data Export Wizard


XML Document Options, Data Export Wizard

Use this wizard page to specify options for data export into XML format here. The Options page contains the following details:

Use Unicode
Select this check box to ensure your data will support Unicode.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Options, Data Export Wizard

CSV Document Options, Data Export Wizard


CSV Document Options, Data Export Wizard

Use this wizard page to set format options for export data to a CSV file here. The Options page contains the following details:

Use Unicode
Select this check box to ensure your data will support Unicode.

Show a table header
Select or clear this check box to show/hide a table's header.

Quote string
Specify a character to be used for quotation.

Force quote
If selected, all the data will be quoted (not only text).

Field Separator
Select how to separate fields: Tab, Space, Comma, or Custom.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

ODBC Data Providers Options, Data Export Wizard

Options, Data Export Wizard


ODBC Data Providers Options, Data Export Wizard

Use this wizard page to set options for ODBC data source. You can set the following:

Data source specification

Set the ODBC connection first. Select either a system data source name or a user data source name from the drop-down list. Click the Refresh button to refresh the list.

Use system or user data source name
You can choose any data source created in the operation system. Click the Refresh button to refresh the list available data sources.

Use a connection string
Click the Build button to customize a connection string.

Login information

Based on the selected data source name, you should enter a user name and a password.

Test connection
Click this button to ensure your connection is successfully set.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

dBase, FoxPro Database Options, Data Export Wizard

Options, Data Export Wizard


dBase, FoxPro Database Options, Data Export Wizard

Use this wizard page to specify the number of digits after the decimal point from 0 to 18. You can see the following:

Decimal scale
Click the arrows to specify the number of digits.

Encoding
Select a required encoding from the drop-down list box. By default, the current Windows encoding is selected.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

SQL Document Options, Data Export Wizard

Options, Data Export Wizard


SQL Document Options, Data Export Wizard

Use this wizard page to specify options for data export into SQL format. The Options page contains the following details:

Destination table name
Specify a table name that will be generated in the SQL file. This field is disabled when more than one table are selected.

Statement type
Choose a type of the statements that will be generated in the SQL file.

INSERT
Select it to export data as INSERT statements. Insert data into the table.

UPDATE
Select it to export data as UPDATE statements. Update data into the table.

DELETE
Select it to export data as MERGE statements. Delete data from the table.

MERGE
Select it to export data as MERGE statements. Merge data with the table.

Include database name in script
Select to include database name into script. The option is available only when exporting multiple tables.

Save Template
Click it to save the settings as a template for future uses.

note Note

The Include database name in script check box appears instead of a text box, while selecting several objects for export.

ExpandedToggleIcon(9x12) See Also

Table columns, Data Export Wizard

Options, Data Export Wizard


Table columns, Data Export Wizard

Use this wizard page to select columns for export. The tab is available for SQL destination format only.

Column
Select or clear the check boxes next to columns to include them into export or exclude.

Key
Select or clear the check boxes next to columns to include them into the key for export or exclude.

Destination Column
Specify the destination column names that will be generated in the SQL file.

Alias
Specify alias for the column here.

Data Type
Select data type of the column.

ExpandedToggleIcon(9x12) See Also

Data Formats, Data Export Wizard

Data Export Wizard


Table, Data Export Wizard

This page is available only for MS Access and ODBC formats and if only one object for export is selected. You can choose a destination table for data export here.

Existing table
Choose a table to export data to from the list of existing tables.

New table
Create a new table to export data to.

Filter
Type the filter criteria in this field, and objects answering this criteria will be displayed in the list of existing tables.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Page print settings, Data Export Wizard

Data Export Wizard


Data Formats, Data Export Wizard

ExpandedToggleIcon(9x12) Columns

On the Columns tab select columns for export. You can see the grid with the following details:

Field
Select or clear the check boxes next to columns to include them into export or exclude.

Alias
To export a column with another name, click the Alias field and enter a new column name.

Data Type
You can select the required type from the drop-down list. (This field is editable only for Text, HTML, PDF, RTF, and CSV formats. For Excel and Excel 2007 formats, the field is editable only for binary columns and includes only two data types (HexString and String) in the drop-down list).

ExpandedToggleIcon(9x12) Formats

This tab is available only for Excel, Excel 2007, HTML, Text, RTF, PDF, CSV and JSON export formats. On the Formats tab specify data representation formats for the exported data.

If necessary, change the default types of data representation. Enter new values into the fields.

For more detailed information about how to customize formats, see the Custom Numeric Strings article.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Table, Data Export Wizard

Data Export Wizard


Output Settings, Data Exprot Wizard

Use this page to set output settings for exported data. This page is available for the JSON, XML, TXT and CSV formats only.

Folder
Specify a name and a path of the destination file (the one where the exported data will be saved).

Export data into a single file
Select this option to export selected data into a single file.

Export data into separate files
Select this option to export selected tables and views into a separate files.

Append timestamp to the file name
Select it to include a timestamp to the name of the generated file.

Auto delete old files
If the Append timestamp to the file name option is checked, you can choose to delete old output files automatically when generating new files.

Files older than
Set the number of days the files should be older to be deleted.

Quantity more than
Backups are auto deleted if their quantity is more than the number specified.

Use compression (ZIP)
Check this option to compress the exported data to a ZIP file.

Level
Select the level of compression here. You can select one of the following levels of compression: No Compression, Best Speed, Fast, Default, Good, Maximum.

Comment
Add a comment to the created archive.

Exported files preview
Allows to preview the exported files.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Table, Data Export Wizard

Data Export Wizard


Page Print Settings, Data Export Wizard

Use this wizard page to specify page print options and preview the results in the Preview section. These options are available only for the MS Excel, RTF, and PDF formats. You can see the following details:

Page size
Select a required page size from the drop-down list. Preview the result.

Repeat a table header
Select it to print a table header on each page.

Orientation
Specify a page orientation, either a portrait or landscape one. Preview the result.

Margins
Set margins using the arrow buttons. Preview your changes.

Footer/Header
Enter a text for a table's footer and header. If required, select the check box at the top of the window to repeat a table's header on each page.

Preview
Here you can look at the sample data with applied settings before exporting.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Exported rows, Data Export Wizard

Data Export Wizard


Exported Rows, Data Export Wizard

Choose options for exported rows here.

Export all rows
Select this option to export all rows. Both selected and not selected rows in the grid will be included into export.

Export selected rows only
If you select several rows in a table and then click Export Data, this option will be selected automatically. All applied filters (if any) will be saved in the output file.

Export a range of rows
Select a range of exported rows.

Start from
Specify the row's number to start exporting from.

Count
Specify the quantity of rows for export. (Note: The specified quantity will include the starting exported row.). If 0 is specified, all rows after the Start from value are exported.

Save Template
Click it to save the settings as a template for future uses.

ExpandedToggleIcon(9x12) See Also

Errors Handling, Data Export Wizard

Data Export Wizard


Errors Handling, Data Export Wizard

On this page, you can specify the application behaviour when an error is encountered during data export and logging options.

Errors handling

Prompt a user for an action
Select it to be prompted by dbForge Query Builder for SQL Server in case of any errors. You will be able to ignore the errors and continue exporting data or cancel the process.

Ignore all errors
Select it to make dbForge Query Builder for SQL Server ignore all errors during export.

Abort at the first error
Select it to cancel export if any error occurs.

Log settings

Write a report to a log file
Select it to get a log file with details about the export session. This will allow you to see the information about the errors appeared during the export session and their causes.

Log file
Click the Browse button and specify the path to the log file or enter it manually.

ExpandedToggleIcon(9x12) See Also

Export progress, Data Export Wizard

Data Export Wizard


Export Progress, Data Export Wizard

You can see the export progress on this page. To cancel the export, click the Cancel button.

ExpandedToggleIcon(9x12) See Also

Finish, Data Export Wizard

Data Export Wizard


Finish, Data Export Wizard

You can see data export results on this page.

Errors count
Displays the total number of errors.

Rows exported
Displays the total number of exported rows.

Show log file
Click to open the log file. This button appears only if you selected Write a report to a log file on the Errors Handling wizard page.

Open result file
Click to open the result file. The option is disabled if several objects are selected for import.

Open result folder
Click to open the result folder that contains result file. This option is not available when exporting to the ODBC format.

Export more
Click this button to perform export data process once more. When you click this button, you are redirected to the Export Format page of the wizard. To select new tables and/or views to export data from, go to the Source page of the wizard.

If an error occurred during the export process, its text will be displayed in the upper part of the page.

ExpandedToggleIcon(9x12) See Also

Data Export Wizard


Diagram Overview Window

Use this window to get a better overview of large diagrams while working with them. You can quickly zoom in and out the diagram to see where the required diagram items are, move to them, and grasp the whole picture.

To open the Diagram Overview window, click the [image] Diagram Overview icon on the Diagram toolbar or in the View menu click Other Windows, and then click Diagram Overview. Alternatively, you can use the Ctrl+W,V hot key combination to call this window.By default the window will be docked to the right corner of Query Builder for SQL Server, but you can move or dock it the same way as you do with other windows in Query Builder for SQL Server.

The Diagram Overview window has a blue selection box that highlights the area currently displayed on the diagram. Use this box to move to a required item on the diagram. To move in a strictly horizontal or vertical direction, move the blue selection box holding down the Shift key. You can re-size the box to change the zooming level on the diagram.

To zoom in and out the diagram, you can hold down the Ctrl key and roll the mouse wheel forward to zoom in the diagram or backward to zoom out.

ExpandedToggleIcon(9x12) See Also

Reference

Execute Script Wizard


Execute Script Wizard

Use this wizard for executing large scripts without opening them in the editor. To access the Execute Script Wizard, on the Database menu, click ExecuteLargeScript Execute Large Script. The wizard pages are listed below.

ExpandedToggleIcon(9x12) Script Location, Execute Script Wizard

Use this page to choose a location and connection parameters of a large script to execute it without opening in the text editor. This will significantly save your time while working with large scripts.

Options

Connection
Click the field to select a connection to a required server where you want to execute your script. In the drop-down list of the field you can click the <Manage...> link to open the Connection Manager dialog box. It will help you to create, edit, and delete existing connections.

Database
Click the field to select a required database from the list of all databases available on the selected SQL Server.

File Name
Enter the path to a script you want to execute or click the Browse button to select the path in the Open dialog.

SQL file encoding
Select the encoding which should be applied to open and execute the script. The program automatically selects it based on the script, if it fails, the default encoding (the current Windows encoding on the machine where dbForge Query Builder for SQL Server is running) is selected.

Execute
Click it to execute the selected script.

Cancel
Click it to close the wizard and discard the script execution.

ExpandedToggleIcon(9x12) Execution Progress, Execute Script Wizard

See script execution progress on this page. To stop the script execution, click the Cancel button.

ExpandedToggleIcon(9x12) Operation Complete, Execute Script Wizard

See the results of script execution on this page.

ExpandedToggleIcon(9x12) See Also

Reference


Find and Replace Window

You can use the Find and Replace window to search for text, expressions, or entity names within the code of your documents. To access this window, from the Edit menu, click Find and Replace, and then select one of the options listed.

The Find and Replace window contains a toolbar with two drop-downs, one for find operations and one for replace operations. When you select an operation, the corresponding options for the operation are displayed. You can search and replace in one or more files or an entire project for text, SQL code, or symbols.

ExpandedToggleIcon(9x12) In This Section

Find, Find and Replace Window
Used to search the active file for a string or expression. Jumps from match to match within each document.

Find in Files, Find and Replace Window
Used to search for a string or expression in files stored within a set of search folders, or across the active solution. Displays matches in the Find Results windows.

Choose Search Folders Dialog Box
The topic describes Choose Search Folders dialog box that allows you to create your own set of search folders, and to specify the order in which they are searched.

Find Results Window
The topic describes the Find Results window that allows you to navigate text search results


Find, Find and Replace Window

Use Find and Replace dialog box for searching and replacing text in the code editor.

note Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition.

To access the Find and Replace dialog box:

1. In the Edit menu, click Find and Replace.
2. Select required option from the list.

An alternative way to access the Find and Replace window is by pressing CTRL+F shortcut key.

ExpandedToggleIcon(9x12) Menus

Find
Allows to select Find or Find in Files dialog.

Find
The search will be executed in an open document.

Find in Files
The search will be executed in the selected files or a project. If this option is selected, the Find and Replace window provides additional options. You can create a set of selected folders, specify search file types, and set result options.

Replace
Allows to select Replace or Replace in Files dialog.

Replace
The search will be executed in the document. The occurrences will be replaced with the replacement string.

Replace in Files
The search will be executed in the files or a project. The occurrences will be replaced with the replacement string.

ExpandedToggleIcon(9x12) General Options

Find what
To search for a new text string or expression, specify it in the box. To search for any of the strings that you searched for most recently, open the list, and select the string for which you want to search. You can also:

· Reuse the search strings by selecting any from the drop-down list in the field
· Enter a new search string into the field
· Use regular expressions as search strings
· Use wildcards in your search strings

Replace with
Replacement string.

Look in
Select search location here.

Current Document
Search will be executed in current document only.

Current Window
Search will be executed in current tool window. It can be either the Output window or the Data Viewer and Editor window.

All Open Documents
Search will be executed in all open documents.

Selection
Search will be executed in the selected text.

Current Project
Search will be executed in the opened project.

ExpandedToggleIcon(9x12) Find Options

Match case
Case sensitive search.

Match whole word
The string will not be found/replaced if it is not a whole word.

Search up
Search will be performed in reverse direction.

Search hidden text
Search in collapsed text areas (such as regions, definitions and others) will be also performed.

Use
Use regular expressions or wildcards in search.

Regular expressions
A regular expression is a pattern of text that consists of ordinary characters (for example, letters a through z) and special characters, known as metacharacters. The pattern describes one or more strings to match when searching text. If this option is selected, the arrow button next to the Find what field becomes active. You can click the button and select a required regular expression to add to the Find what field.

Wildcards
This is a list of expressions to replace characters or digits in the Find what field.

ExpandedToggleIcon(9x12) Buttons

Find Next
Searches the next occurrence in document.

Bookmark All
Sets bookmarks for the lines, that contain occurrences in the scope. Available for the Find mode.

Replace
Searches the next occurrence in document or replaces the selected one if on replace dialog, shows replace dialog if on search dialog. Available for the Replace mode.

Replace All
Replaces all occurrences in search location. Available for the Replace mode.

ExpandedToggleIcon(9x12) See Also

Find and Replace Window

Find in Files, Find and Replace Window


Find in Files, Find and Replace Window

Use the Find and Replace window to find and replace text in the selected files.

note Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition.

To access the Find in Files, Find and Replace dialog box:

1. In the Edit menu, click Find and Replace.
2. Select required option from the list.

An alternative way to access the Find in Files, Find and Replace window is by pressing CTRL+F shortcut key, and then select Find in Files from the Find menu.

ExpandedToggleIcon(9x12) Menus

Find
Allows to select Find or Find in Files dialog.

Find
The search will be executed in the opened document.

Find in Files
The search will be executed in the selected files or a project. If this option is selected, the Find and Replace window provides additional options. You can create a set of selected folders, specify search file types, and set result options.

Replace
Allows to select Replace or Replace in Files dialog.

Replace
The search will be executed in the document. The occurrences will be replaced with the replacement string.

Replace in Files
The search will be executed in the files or a project. The occurrences will be replaced with the replacement string.

ExpandedToggleIcon(9x12) General Options

Find what
String that will be searched. This field allows you to use a string or a regular expression that will be matched. You can do the following:

· Reuse the search strings by selecting any from the drop-down list in the field
· Enter a new search string into the field
· Use regular expressions as search strings
· Use wildcards in your search strings

Replace with
Replacement string.

Look in
Select a search location here, either selecting options from the drop-down list or creating a new set of folders in the Choose Search Folders window. The drop-down list contains the following options:

Current document
Search will be executed in the current document only.

All open documents
Search will be executed in all open documents.

Current Project
Search will be executed in the opened project.

Button
Click to open the Choose Search Folders dialog box for creating custom named folder list.

Include sub-folders
Select this check-box to include the sub-folders within the selected folder set in the Look in field.

ExpandedToggleIcon(9x12) Find Options

Match case
Case sensitive search.

Match whole word
The string will not be found/replaced if it is not a whole word.

Use
Select this check-box to activate the use of regular expressions or wildcards in search strings.

Regular expressions
A regular expression is a pattern of text that consists of ordinary characters (for example, letters a through z) and special characters, known as metacharacters. The pattern describes one or more strings to match when searching text. If this option is selected, the arrow button next to the Find what field becomes active. You can click the button and select a required regular expression to add to the Find what field.

Wildcards
This is a list of expressions to replace characters or digits in the Find what field.

Look at these file types
Select the required file types from the drop-down list. The search will be executed only in files of the selected type.

ExpandedToggleIcon(9x12) Result Options

Display file names only
Select this check-box to show one entry per a file containing a search match.

Keep modified files open after Replace All
Select this check-box to leave all modified files open after Replace All is performed. It will allow you to undo or save the changes in the files.

ExpandedToggleIcon(9x12) Buttons

Find Next
Searches the next occurrence in document.

Find All
Searches for all occurrences in the scope. Available for the Find in Files mode.

Replace
Searches the next occurrence in document or replaces the selected one if on replace dialog, shows replace dialog if on search dialog. Available for the Replace in Files mode.

Replace All
Replaces all occurrences in search location. Available for the Replace in Files mode.

ExpandedToggleIcon(9x12) See Also

Find and Replace Window

Choose Search Folders Dialog Box


Choose Search Folders Dialog Box

This dialog allows you to create, save, and revise your own set of search folders, and to specify the order in which they are searched. To open this dialog, click the Button button, next to the Look in field.

Folder set
Shows the folders available for search. To create a new folder set, enter a name, add a set of search folders to the Selected folders list, and then click the Apply button.

Apply
Saves the set of search folders in the Selected folders list as a named folder set. This folder set then can be chosen in the Look in field on all tabs of the Find and Replace window. Leaves the Choose Search Folders dialog box open.

Delete
Removes the selected folder set from the Folder set field, and from the Look in field on all tabs of the Find and Replace window.

Available folders
Select a previously selected folder from the drop-down list to populate the Selected folders list.

List of Folders
Shows the drives and folders available within the folder selected in the Available folders drop-down list. Double-click to expand any drive or folder listed. Select a folder, or hold down the SHIFT or CTRL key to select multiple folders. To add a selected folder to the Selected folders list, click the arrowright right-arrow button. To remove a selected folder, click the arrowleft the left-arrow button.

Up
Moves the selection in the Available folders list one level higher in the folder hierarchy.

Selected folders
Lists folders added from the Available folders list. These folders will be included in the named folder set.

Arrow buttons
arrowright - Moves folders to the Selected folders list.

arrowleft - Selects folders from the Selected folders list.

arrowup - Moves the selected folder to one position higher in the Selected folders list so that the folder will be searched earlier.

arrowdown - Shifts the selected folder to one position down in the Selected folders list so that the folder will be searched later.

OK
Saves the set of search folders listed in Selected folders list as a named folder set. This folder set then can be chosen in the Look in field on all tabs of the Find and Replace window. Closes the Choose Search Folders window.

Cancel
Discards all changes and closes the Choose Search Folders window.

ExpandedToggleIcon(9x12) See Also

Find and Replace Window

Find Results Window


Find Results Window

Find Results window allows you to navigate text search results. It provides you following search results: file name, line number and part of the text, containing the match. You may double-click on matches to navigate to them. Find Results window has following toolbar buttons.

Go to the location on the current line
Moves caret to the selected line location of the match found.

Go to the previous location in the list
Moves caret to the selected line location of the previous match found.

Go to the next location In the list
Moves caret to the selected line location of the next match found.

Clear All
Clears Find Results window.

ExpandedToggleIcon(9x12) See Also

Find and Replace Window


Options

This section describes different options that are available in dbForge Query Builder for SQL Server.

ExpandedToggleIcon(9x12) In This Section

Environment Options
This section describes Environment options which determine how certain elements of the dbForge Studio display and behave.

Text Editor Options
This section describes Text Editor options that provide a variety of ways to customize the performance and appearance of the editor.

Data Editor
This section describes the Data Editor options.

Database Explorer Window
This section describes the Database Explorer options.

Query Builder Options
This section describes options for Query Builder's diagram.

Query Execution Options
The section describes a variety of ways to customize the performance of query execution.

Query Profiler Options
This section explains code formatting options provided in the Options window.


Environment Options

You can use the pages in the Environment folder in the Options dialog box to set how certain elements of the Query Builder for SQL Server display and behave. You can access the Environment pages by clicking Options on the Tools menu, and then clicking Environment.

ExpandedToggleIcon(9x12) In This Section

General, Environment, Options Dialog Box
Lists general environment preferences and explains them.

Keyboard, Environment, Options Dialog Box
Describes the tab where you can customize shortcut keys.

Output, Environment Options Dialog Box
Describes the tab where you can customize application output.

Documents, Environment, Options Dialog Box
Describes the tab where you can customize the document view layout.

Web Browser, Environment, Options Dialog Box
Describes the tab where you can set Query Builder for SQL Server web browser options.

ExpandedToggleIcon(9x12) See Also

Options

Text Editor Options


General, Environment, Options Dialog Box

Use this page of the Options dialog box to change settings for the Query Builder for SQL Server. You can access the Options dialog box by clicking Options on the Tools menu and then selecting the General page in the Environment folder.

Visual Settings

Skin
Select the application skin you prefer to use while working. The skin list contains the following themes:

· Office 2010 Silver
· Office 2010 Blue
· Office 2010 Black
· Office 2013
· Office 2013 Light Gray
· Office 2013 Dark Gray
· Windows 7
· Metropolis
· Metropolis Dark
· Visual Studio 2010
· Visual Studio 2013 Light
· Visual Studio 2013 Dark
· Visual Studio 2013 Blue

Use styled images
Select the check box to apply styled images.

note Note

Changes will be applied after you restart the application.

Enable main form skin
Select the check box to apply a chosen skin to the main application form. If not selected, the Windows theme will be applied to the application main form.

Environment Settings

At startup
Select what to do when Query Builder for SQL Server starts up.

Show Startup page
A Start Up page will be opened at startup.
Load Last Opened Project
Last opened project will be loaded at startup.
Create New SQL Document
A new empty SQL document will be created at startup.

Check updates on startup once a week
Select this option so that dbForge Query Builder for SQL Server will check if any updates are available for the product every week

Show splash screen on startup
Clear to turn off splash screen displaying.

Show status bar
Determines whether status bar should be visible.

Display X items shown in Window menu
Customizes the number of windows that appear in the Windows list of the Window menu. Type a number between 1 and 24. By default, the number is 10.

Display X items shown in recently used lists
Customizes the number of most recently used projects and files that appear on the File menu. Type a number between 1 and 24. By default, the number is 10. This is an easy way to retrieve recently used projects and files.

ExpandedToggleIcon(9x12) See Also

Environment Options

Keyboard, Environment, Options Dialog Box


Keyboard, Environment, Options Dialog Box

The Keyboard page of the Options dialog box allows you to customize the keyboard scheme for the Query Builder for SQL Server. Use this option to set shortcut keys for frequently used commands or to establish your favorite binding for a particular command. To access this dialog box, select Options from the Tools menu and choosing the Keyboard page from the Environment folder.

ExpandedToggleIcon(9x12) Options

Keyboard mapping scheme
Lists the set of all available collections of shortcut keys:

· Embarcadero RAD Studio
· Microsoft Visual Studio
· Eclipse
· Quest Software SQL Navigator
· Quest Software Toad
· Oracle SQL Developer
· Microsoft SQL Server Management Studio

To load a specific scheme, select it from the list. When you are ready to leave the options dialog box, click OK.

Reset
Resets all shortcuts to the defaults of the selected keyboard mapping scheme.

Show commands containing
Specifies which command is selected and shows a list of all commands available in the Query Builder for SQL Server that contain the specified characters, or all commands if this edit box is empty. As you type a complete word, the result list displays all entries containing the typed word. For example, you can enter save, locate the command, File.Save, and see that the associated shortcut is CTRL+S.

Shortcut(s) for selected command
Lists mapped shortcuts for the command selected in the Show commands containing list. Some commands have more than one mapped shortcut.

Remove
Removes the selected shortcut key combination.

Use new shortcut in
Specifies the scope of the shortcut key. For example, you can use the shortcut key globally in the Query Builder for SQL Server or only within a specific context (or window); Global is the default setting. If you choose a specific context, the shortcut key remains in effect only while the context is active. If you choose Global, the shortcut key works in any active window in the Query Builder for SQL Server, unless the same shortcut is mapped in another editor, in which case the editor overrides the shortcut.

Press shortcut key(s)
Allows you to press a key combination to be used for the currently selected command. You must use one or more modifier keys such as CTRL, ALT, or SHIFT. The modifier keys appear in the order in which you press them. For example, pressing CRTL, then SHIFT, and then F2 creates the shortcut key, CTRL+SHIFT+F2. You can enter one or two key combinations that include a modifier key. For example, you can enter CTRL+Y, or enter CTRL+Y, CTRL+W. Use BACKSPACE to delete the whole shortcut key combination at one time.

note Note

The following keys cannot be assigned: ALT, SHIFT, or CTRL without another key; ESC, PRINT SCRN/SYS RQ, SCROLL LOCK, PAUSE/BREAK, TAB, CAPS LOCK, INSERT, HOME, END, PAGE UP, PAGE DOWN, Windows logo keys, Application key, any of the ARROW keys, or ENTER; NUM LOCK, DEL, or CLEAR on the numeric keypad; or CTRL+ALT+DELETE.

Assign
Binds the new key combination to the selected command in the Keyboard mapping scheme displayed. The new assignment is added to the Shortcut(s) for selected command list and you can specify multiple key combinations for one command. This button is active after you type a key combination in the Press shortcut key(s) box. If you attempt to bind a key in the [Default Settings] scheme, you are prompted to make a copy of the scheme and give it a name.

note Note

Changes made using the Assign button are not canceled if you click the Cancel button.

Shortcut currently used by
Displays the command to which the key combination is currently assigned. Appears only when you assign a key or key combination that is already assigned. To replace the shortcut with a custom shortcut key combination you must define a new Keyboard mapping scheme.

ExpandedToggleIcon(9x12) See Also

Environment Options

Output, Environment, Options Dialog Box


Output, Environment, Options Dialog Box

Use Options window to adjust Query Builder for SQL Server behavior to your needs. To access this dialog box, select Options from the Tools menu and choosing the Output page from the Environment folder.

ExpandedToggleIcon(9x12) Settings

Turn on 'Redirect Data to Output Window' by default
Enables displaying of selected data as text in the Output window instead of the Data view for every SQL document by default.

Show time for output events
If selected, the time stamp is added at the beginning of every line in the Output window.

ExpandedToggleIcon(9x12) SQL Log

Write queries sent by program to the SQL Log
Allows to log all the queries sent by program to a database.

ExpandedToggleIcon(9x12) Event Tracing

Log application errors
Check this option to write all errors and processes that took place during the application work to a file. This file is placed in the directory with the application executable file.

It can help a user to provide more detailed information on the encountered problem to the developers so that they are able to reproduce and fix the problem faster. If you have encountered an error and are going to send us an error report, this file will be attached to the letter with the error report.

Delete application log after closing
Clear this checkbox if you want the application log be available after application closing.

ExpandedToggleIcon(9x12) See Also

Environment Options

Documents, Environment, Options Dialog Box


Documents, Environment, Options Dialog Box

Use the Options window to adjust the Query Builder for SQL Server behavior to your needs.

ExpandedToggleIcon(9x12) In This Section

SQL Documents Dialog Box
Describes options available for SQL document view.

Categories
Describes options available for windows coloring.

Restore
Describes options available for automatic files saving.

ExpandedToggleIcon(9x12) See Also

Environment Options

Web Browser, Environment, Options Dialog Box


SQL Documents Dialog Box

The SQL Documents subsection of Environment Documents of the Options window contains the following options:

ExpandedToggleIcon(9x12) Default SQL Document View Layout

One main document view
All views corresponding to the opened SQL document are visible. You can activate the needed view by clicking the corresponding tabs.

All views tabbed
Only one view corresponding to the document is visible, all others are tabbed. Click the needed tab for the corresponding view to be shown on the screen.

note Note

You can also change the document view layout from the popup menu that can be called from the lower part of the document view.

Show Data view for every SQL document
Check this option for the Data view tab to be displayed for every opened or newly created SQL document.

Activate Data editor after query execution
Check this option to focus on the Data editor after a query is executed. If this option is unchecked, focus stays in the Text view.

Redirect data to Data window if Data view is closed
If this option is checked, data returned after query execution is displayed into the Data window if the Data view is closed.

ExpandedToggleIcon(9x12) See Also

Documents, Environment, Options Dialog Box

Categories


Categories

Document categories
Displays the list of available document categories.

Name
Category name.

Shape
Category shape.

Color
Category color.

New Category
Adds a new category to the list.

Delete Category
Deletes a selected category.

Reset to Default
Resets all changes to defaults.

ExpandedToggleIcon(9x12) See Also

Environment Options

Restore


Restore

Use the topic to customize the automatic files saving. It will allow you to return to the documents which you forgot to save during the last session.

Settings

Save restore information every ... min
Set the time after which the data will be saved automatically.

Restorable file types

SQL files (*.sql)
If checked, the *.sql files will be saved automatically.

Text files (*.txt)
If checked, the text files will be saved automatically.

XML files (*.xml)
If checked, the XML files will be saved automatically.

ExpandedToggleIcon(9x12) See Also

Documents, Environment, Options Dialog Box

Web Browser, Environment, Options Dialog Box


Web Browser, Environment, Options Dialog Box

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To access this dialog box, select Options from the Tools menu and choosing the Web Browser page from the Environment folder.

ExpandedToggleIcon(9x12) Settings

Home page
Set here a home page for Query Builder for SQL Server web browser. It opens when you call Query Builder for SQL Server web browser window or click Home button on Web toolbar.

Use default
Use the product home page.

Search page
Set here a search page for Query Builder for SQL Server web browser. It opens when you click Online Search button on Web toolbar.

Use default
Use Google as a search page.

ExpandedToggleIcon(9x12) See Also

Environment Options

Options


Text Editor Options

The Text Editor options in the Options dialog box provide a variety of ways to customize the performance and appearance of the editor.

ExpandedToggleIcon(9x12) In This Section

General, Text Editor, Options Dialog Box
This dialog box lets you change global settings for the Query Builder for SQL Server.

Tabs, Text Editor, Options Dialog Box
This dialog box allows you to change the default behavior of the Code Editor.

Font & Colors, Text Editor, Options Dialog Box
This dialog box allows you to change the default setting for a custom font and color scheme for various user interface elements.

Printing, Text Editor, Options Dialog Box
This dialog box allows you to change default printing options.

Advanced, Text Editor, Options Dialog Box
This dialog box describes how to set up tabulation and indenting editor preferences.

Code Completion
This section describes statement completion options available in Query Builder for SQL Server.

Formatting
This section describes code formatting options available in Query Builder for SQL Server.

ExpandedToggleIcon(9x12) See Also

Options

Data Editor Options


General, Text Editor, Options Dialog Box

This dialog box lets you change global settings for the Code and Text. To display this dialog box, click Options on the Tools menu, expand the Text Editor node, and then click General.

Settings

Virtual whitespace
When selected, it allows you to move the cursor beyond the physical end of a line of text into character spaces that do not exist. If you move your cursor out into this area and begin typing, the void between your current position and the end of the last word is automatically filled with space characters. When this setting is not selected, you cannot move the cursor beyond the last character in a line.

Bracket matching
If selected, dbForge Query Builder for SQL Server finds a corresponding bracket for the one that is currently near the cursor.

Display

Line numbers
Displays line numbers in the left of Text view of a document.

Indicator margin
Reveals the indicator margin on the left of Text view of a document.

Vertical scroll bar
Shows or hides a vertical scrollbar in Text view of a document.

Horizontal scrollbar
Shows or hides horizontal scrollbar in Text view of a document.
Navigation bar
Check this option to enable a navigation bar. It can be useful if you have a large amount of CREATE TABLE instances in your script and you need to navigate between columns and tables in it.
Hide if empty
Check this option to show navigation bar only when two or more objects are created. If only one object is created in a SQL statement the bar remains hidden as it is not needed.

ExpandedToggleIcon(9x12) See Also

Tabs, Text Editor, Options Dialog Box

Text Editor Options


Tabs, Text Editor, Options Dialog Box

This dialog box allows you to change the default behavior of the Code Editor. To display this dialog box, click Options on the Tools menu, expand the Text Editor node, and then click Tabs.

ExpandedToggleIcon(9x12) Indenting

None
When selected, no automatic indenting occurs when you press ENTER to move to a new line of text, and the cursor is placed at the first column of the next line.

Block
When selected and you press ENTER, the new line of text is automatically indented to the same tab stop as the line preceding it.

Smart
When selected, new lines of text are automatically indented taking into account selecting formatting options.

ExpandedToggleIcon(9x12) Tabs

Tab size
Sets the distance in spaces between tab stops. The default is 2 spaces.

Indent size
Sets the size in spaces of an automatic indentation. The default is 2 spaces. Tab characters, space characters, or both will be inserted to fill the specified size.

Insert spaces
When selected, indent operations insert only space characters, not TAB characters. If the Indent size is set to 5, for example, then five space characters are inserted whenever you press the TAB key or the Increase Indent button on the Formatting toolbar.

Keep tabs
When selected, indent operations insert as many TAB characters as possible. Each TAB character fills the number of spaces specified in Tab size. If the Indent size is not an even multiple of the Tab size, space characters are added to fill in the difference.

ExpandedToggleIcon(9x12) See Also

Text Editor Options

Font & Colors, Text Editor, Options Dialog Box


Font & Colors, Text Editor, Options Dialog Box

The Fonts and Colors page of the Options dialog box lets you establish a custom font and color scheme for various user interface elements in the Query Builder for SQL Server. To access this dialog box, click Options on the Tools menu, and then select the Font &Colors page in the Text Editor folder.

Options

Font (bold type indicates fixed-width fonts)
Lists all the fonts installed on your system. Fixed fonts appear in bold.

Size
Specify the size of the font for the selected display item here.

Use Defaults
Resets text editor font and color settings to the default values.

Display items
Select a display item here to change its font and color settings.

Brace Matching
Matching braces, for example ().
Built-in Data Type
Built-in data type, for example, INT or VARCHAR.
Built-in Function
Built-in function, for example, COUNT.
Call Return
Source statement that indicates call return points when context is switched to a non-top stack frame when debugging.
Comment
Code comment.
Current Statement
Current code line when debugging.
Identifier
Identifier, such as database object name, stored procedure parameter, etc.
Keyword
SQL keyword.
Number
Number.
Operator
Operators and separators (, ;: * + = / - . (<>!~&^%\|{})).
Plain Text
Text, when editing a file as text.
Program Block Label
Labels.
Region
Region or endregion command.
Select Marker
Frame around the select statements.
Selected Line
Line, selected in the Find Results window.
String
SQL string constant.
Visible Whitespace
White space characters when View White Space is turned on.
XML Attribute Name
Tag attribute in XML editor.
XML Comment
Comment in XML editor.
XML Indent
Indent in XML editor.
XML Number
Number in XML editor.
XML Special Tag
Special XML tag.
XML String
String constant in XML editor.
XML Tag Name
XML attribute name.

Bold
Select it to display the text of selected display items in bold.

Italic
Select it to display the text of selected display items in italic.

Item foreground
Font color of a selected display item.

Item background
Background color of a selected display item.

Sample
Sample of a display item appearance.

ExpandedToggleIcon(9x12) See Also

Text Editor Options

Printing, Text Editor, Options Dialog Box


Printing, Text Editor, Options Dialog Box

This dialog box allows you to change default printing options. To access this dialog box, click Options on the Tools menu, and then select the Printing page in the Text Editor folder.

ExpandedToggleIcon(9x12) Settings

Print header
Check it to print the document name.

Print line numbers
Check it to print line numbers in the document.

Print white space
Check it to print white space symbols, tabulation symbols, and symbols of line breaks.

ExpandedToggleIcon(9x12) See Also

Text Editor Options

Advanced, Text Editor Options Dialog Box


Advanced, Text Editor, Options Dialog Box

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu.

The Advanced section of the Text Editor options allows you to setup outlining and SELECT statement highlighting. You can use the following options:

ExpandedToggleIcon(9x12) Outlining

Enter outlining mode when files open
If selected, outlining is enabled when you open a file.

Collapse --region blocks when files open
If selected, --region blocks in an opened file will be collapsed.

ExpandedToggleIcon(9x12) Syntax highlighting

Highlight SELECT statements
If selected, a frame will be drawn around SELECT statements.

Underline errors in the editor
If selected, syntax errors will be underlined with a red curved line.

ExpandedToggleIcon(9x12) Editor performance

Enable syntax highlight
Clear a check box to disable syntax highlight.

Max script size
Specify max script size for syntax highlight support.

Max script size
Specify max script size for code completion, navigation and refactoring support.

ExpandedToggleIcon(9x12) See Also

Text Editor Options

Code Completion Options


Code Completion Options

Code Completion

This section explains statement completion options provided in the Options window.

The Code Completion section of the Text Editor options contains the following subsections:

ExpandedToggleIcon(9x12) In This Section

General, Code Completion Text Editor Options Dialog Box
Lists general code completion preferences and explains them.

Advanced, Code Completion Text Editor Options Dialog Box
Describes advanced code completion options.

List Members, Code Completion Text Editor Options Dialog Box
Describes options for list members.

ExpandedToggleIcon(9x12) See Also

Text Editor Options

Formatting


General, Code Completion Text Editor Options Dialog Box

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu.

The General subsection of the Code Completion section of the Text Editor options allows you to set up options for automatic code formatting when typing code. Use the following options:

Settings

Enable Code Completion
Select to toggle automatic code completion. Selected by default.

Auto-list members
Toggles the display of valid databases, tables, columns, and other database objects in the suggestion box when you are typing. Selected by default.
Auto Parameter Info
Toggles the display of the Parameter Info hint when the left bracket is typed after the procedure or object method name. Selected by default.
Highlight Occurrences
Toggles identifier occurrence highlighting.
Automatically trigger after ... millisec
Defines a delay used by dbForge Query Builder for SQL Server to show the suggestion box after you stop typing. To change the default value, use the arrow buttons in the field or enter the value.

Refresh

Automatically refresh suggestion cache every ... minute(s)
Select this checkbox to enable automatic refresh within specified interval.

Detect changes on a server before refreshing suggestion cache
If the option is enabled, the suggestion cache will be refreshed in case of any changes on a server. If the option is disabled, the suggestion cache will be refreshed when a user selects the respective command (irrespective of changes on the server).

Note Note

This option works on SQL Server 2005 or higher.

ExpandedToggleIcon(9x12) See Also

Code Completion Options

Advanced, Code Completion Text Editor Options


Advanced, Code Completion Text Editor Options

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu.

The Advanced subsection of the Code Completion section of the Text Editor options allows you to setup options for automatic code completion when typing code. The following options are available:

Settings

Qualify column name with table alias
Defines that column names should be committed with table alias.

Qualify column name (no table alias)
Defines how column names should be committed.

With table name
The name of the table the column belongs to is specified before the column name: TableName.ColumnName.
With schema name and table name
The name of the table the column belongs to and the schema name are specified before the column name: SchemaName.TableName.ColumnName.
With database, schema and table name
The name of the table the column belongs to, the database and schema names are specified before the column name: DatabaseName.SchemaName.TableName.ColumnName.

Include default schema in object names
Includes the default schema into an object name when pasting it into the text.

Reverse order in 'JOIN'-condition
Applies reverse order in JOIN conditions.

Add parentheses when inserting functions
If the option is checked, parentheses are added when you insert functions.

Inclose identifiers within square brackets

Quote identifiers
Choose the way of identifying a quote here.

Always
If the option is enabled, all the identifiers, aliases, and database objects will be enclosed in parentheses.
If an identifier concurs with a keyword
If the option is enabled, all the identifiers, aliases, and database objects will be enclosed in parentheses, in case their names concur with reserved keywords or system objects.
If bracketing is required
If the option is enabled, all the identifiers, aliases, and database objects will be enclosed in parentheses, if required. For instance, if a column name is declared with white spaces.

ExpandedToggleIcon(9x12) See Also

Code Completion Options

List Members, Code Completion Text Editor Options


List Members, Code Completion Text Editor Options

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu.

The List Members subsection of the Code Completion section of the Text Editor options allows you to setup options for automatic code completion when typing code. The following options are available:

ExpandedToggleIcon(9x12) Settings

Sort keywords by relevance
When selected, keywords in the suggestion box will be sorted to display the most relevant at the top of the list. It is selected by default.

ExpandedToggleIcon(9x12) Filtering Rule

These options define how to filter the list of suggestions in the suggestion box. If neither of them is selected, Query Builder for SQL Server will display only suggestions that start with the typed character(s).

By Matching
When selected, Query Builder for SQL Server filters the list of suggestions to display those that start with the typed character(s) or contain it in the top of the list.

Camel Case
When selected, Query Builder for SQL Server filters the list of suggestions to display those that are compound word which elements are joined without spaces and start with the typed characters.

ExpandedToggleIcon(9x12) Commit selection in completion list by typing

When you type any of the symbols specified below, Query Builder for SQL Server will insert a highlighted suggestion from the suggestion box into code.

Tab
The highlighted suggestion will be inserted when you press the TAB key.

Whitespace
The highlighted suggestion will be inserted when you press the SPACE key.

Following characters:
Enter characters here, and the highlighted suggestion will be inserted when you type any of the specified characters.

Add new line on enter at end of fully typed word
Cursor will be moved to the new line if you type a word completely and press ENTER.

Select items in the list even if a user input is expected
When selected, the relevant keyword will always be selected in the suggestion list, even if a user input is expected.

ExpandedToggleIcon(9x12) See Also

Code Completion Options

Formatting


Formatting

This section explains code formatting options provided in the Options window. The Formatting section of the Text Editor options contains the following subsections:

ExpandedToggleIcon(9x12) In This Section

General, Formatting, Text Editor Options Dialog Box
Lists general code formatting preferences and explains them.

Profiles, Formatting, Text Editor Options Dialog Box
Describes options to customize case formatting profiles.

ExpandedToggleIcon(9x12) See Also

Text Editor Options

Data Editor Options


General, Formatting, Text Editor Options Dialog Box

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu, expand the Text Editor node, and in the Formatting node click General.

ExpandedToggleIcon(9x12) Settings

The General subsection of the Text Editor Formatting section allows you to setup options for automatic code formatting when typing code. You can use the following options:

Automatically format on paste
If checked, pasted code will be automatically formatted. Selected by default.

Automatically format completed statement on delimiter
If checked, code will be automatically formatted when the delimiter is entered (semicolon or backslash). Selected by default.

Automatically format while typing
If the option is checked, code formatting while typing statement elements in SQL editor is supported. Start typing first letters for the required statement, and press TAB or a space to see the result. All objects and keywords are formatted in correspondence with formatting active profile settings. By default, the option is selected.

Right Margin
Line breaks will be inserted automatically after the Right Margin number of characters on a line. 80 is by default.

Put syntax errors to Output window
Select it if you want syntax errors to be redirected to the Output window.

Show syntax error notification window
Select it if you want the syntax error notification window to appear upon formatting a document.

Dictionary file
Specify file with dictionary to adjust formatting to your needs. You can use default dictionary or create a new one. Also you can modify the existing dictionary. This dictionary is used to format identifiers when the Text Case -> Identifier Case option is set to Initial Caps each word.

Available prefixes
Specify prefixes here. If one of the prefixes is used in a word, the application looks in the dictionary for the word that is used after the prefix. If the word is found, its first letter is written in uppercase. These prefixes can be used to format identifiers when the Text Case -> Identifier Case option is set to Initial Caps each word.

Reset Page Defaults
Resets all options on this page to their default values.

ExpandedToggleIcon(9x12) See Also

Formatting

Text Editor Options


Profiles, Formatting, Text Editor Options Dialog Box

Use the Profiles option to customize code formatting provided by Query Builder for SQL Server. To access it, go to Tools menu, select Options, and then click Text Editor. Navigating to Formatting, click Profiles.

ExpandedToggleIcon(9x12) Formatting Profiles

Select a profile from the table.

Name
Profile name.

File
Profile file name.

Edit Profile...
Opens the Edit Profile Options window that allows editing options available in the profile.

New Profile...
Opens the New Profile window to create a new formatting profile.

Activate
Click to activate the selected profile.

Show in Folder
Opens folder containing the selected profile.

ExpandedToggleIcon(9x12) Context Menu

Edit Profile
Click to open the Edit Profile window.

Reset To Defaults
Click to reset settings to the default values.

Activate
Activates the selected profile.

Rename
Click to change the profile name.

Show In Folder
Opens folder containing the file in Windows Explorer.

Copy File Path
Copies file path to the clipboard.

Delete
Click to delete selected formatting profile.

ExpandedToggleIcon(9x12) See Also

Formatting

Text Editor Options


Edit Profile, Formatting Options

Use this window to edit formatting options in the selected formatting profile.

ExpandedToggleIcon(9x12) Edit Profile Window

Filter
Allows to quick search the needed options/option groups.

collapse-all Collapse/Expand all option groups
Click to expand/collapse the option groups tree.

Show white spaces button
Click to toggle white spaces in the formatting profile editor.

Show Original Sample
Click to restore the code sample to its original state.

Format Text
Formats code according to the selected options.

Options tree
Includes various code formatting options. Allows customizing default text formatting options the way user likes.

Code field
Shows how the code looks like after choosing the corresponding text formatting options.

ExpandedToggleIcon(9x12) Edit Profile Context Menu

Format Selection
Formats selected code according to the chosen options.

Format Current Statement
Formats a current statement.

Convert EXEC to Script
The option allows to simplify debugging by replacing a call to a stored procedure with the stored procedure body.

Cut
Cuts selected text and places it to the clipboard.

Copy
Copies selected text to the clipboard.

Paste
Pastes the clipboard content into the cursor position.

ExpandedToggleIcon(9x12) See Also

Profiles, Formatting, Text Editor Options Dialog Box

New Profile, Formatting Options


New Profile, Formatting Options

Use this window to create and add new formatting profile to the list of profiles.

ExpandedToggleIcon(9x12) New Profile Window

Name
Specify the new profile name here.

Copy settings from
Select an existing profile to copy settings from.

File Path
Set the path to save your new formatting profile.

Set Active
Activate this profile in the Profiles, Options Dialog Box.

ExpandedToggleIcon(9x12) See Also

Profiles, Formatting, Text Editor Options Dialog Box

Data Editor Options


Data Editor Options

The Data Editor options in the Options dialog box provide a variety of ways to customize the performance and appearance of the editor.

ExpandedToggleIcon(9x12) In This Section

General, Data Editor, Options Dialog Box
Use the Options window to adjust the products behavior to your needs.

Font & Colors, Data Editor, Options Dialog Box
The Fonts and Colors page of the Options dialog box lets you establish a custom font and color scheme for Data Editor.

Printing, Data Editor, Options Dialog Box
This dialog box allows you to change default printing options.

Formatting, Data Editor, Options Dialog Box
Use the Options window to adjust the products behavior to your needs.

ExpandedToggleIcon(9x12) See Also

Options

Query Execution Options


General, Data Editor, Options Dialog Box

Use the Options window to adjust the products behavior to your needs. To access this dialog box, select Options from the Tools menu and choosing the General page from the Data Editor folder.

The Data Editor General section of the Options window contains the following options:

ExpandedToggleIcon(9x12) Settings

Automatically adjust column width
Automatically adjusts column width for every data grid.

Use cached updates mode by default
If selected, cached updates mode will be enabled by default in data view of every SQL document created.

Use paginal mode by default
If selected, paginal mode will be enabled by default in data view of every SQL document created.

Initial page size
Default page record count for paginal mode for data view.

Use auto-search mode by default
Activates auto-search in Data view by default.

Show row numbers
Adds a column with row numbers (#) to the data editor grid.

ExpandedToggleIcon(9x12) Data Editing

Open Data Editor in the editing mode
Select to open the Data Editor is opened in the editing mode by default.

Open Data Editor in the read-only mode
Select to open the Data Editor is opened in the read-only mode by default.

Show a dialog window to choose a mode
Select to invoke a dialog window asking to choose the mode in which Data Editor should be opened (editing or read-only).

ExpandedToggleIcon(9x12) See Also

Data Editor

Font & Colors, Data Editor, Options Dialog Box


Font & Colors, Data Editor, Options Dialog Box

The Fonts and Colors page of the Options dialog box lets you establish a custom font and color scheme for Data Editor. You can access this dialog box by clicking Options on the Tools menu, and then selecting the Font &Colors page in the Data Editor folder.

ExpandedToggleIcon(9x12) Options

Data Editor Font & Colors section of the Options window contains the following options:

Font

Select a font for the selected display item here.

Size

Specify a font size for the selected display item here.

Use Defaults

Click this button to reset specified font and color settings to the default values.

Display item

Select a display item here to change its background and foreground color settings.

Item foreground

Select a font color for the selected display item.

Item background

Select a background color for the selected display item.

Sample

Preview the display item appearance here.

ExpandedToggleIcon(9x12) See Also

Data Editor

Printing, Data Editor, Options Dialog Box


Printing, Data Editor, Options Dialog Box

This dialog box allows you to change default printing options. You can access this dialog box by clicking Options on the Tools menu, and then selecting the Printing page in the Data Editor folder.

ExpandedToggleIcon(9x12) General

Auto width

Select to fit the printed data to the page width.

ExpandedToggleIcon(9x12) Grid

Print horizontal lines

Select to print grid horizontal lines.

Print vertical lines

Select to print grid vertical lines.

ExpandedToggleIcon(9x12) Card

Print card captions

Select to prints card captions.

Print empty fields

Select to prints card empty fields.

ExpandedToggleIcon(9x12) See Also

Data Editor

Formatting, Data Editor, Options Dialog Box


Formatting, Data Editor, Options Dialog Box

Use the Options window to adjust the products behavior to your needs. You can access this dialog box by clicking Options on the Tools menu, and then selecting the Formatting page in the Data Editor folder.

ExpandedToggleIcon(9x12) DateTime Format

Data type

Select either Date or DateTime format here.

Edit format

Set the datetime format here. It can be one of the predefined formats or custom format. Predefined formats are described below. The following table contains all available format specifiers and their description.

Format Specifier

Description

d

A placeholder for the day of the month. If the day is a single digit only (1-9), then it's displayed as a single digit.

Note that if the 'd' specifier is used alone, without other custom specifiers, it's interpreted as the standard mask (short date pattern). If the 'd' specifier is passed with other custom specifiers or the '%' character, it's interpreted as a custom mask specifier.

dd

A placeholder for the day of the month. If the day is a single digit only (1-9), it is formatted with a preceding 0 (01-09).

ddd

Displays the abbreviated name of the current day.

The value represented by this specifier cannot be edited.

dddd (plus any number of additional "d" characters)

Displays the full name of the current day.

The value represented by this specifier cannot be edited.

f

A placeholder for seconds fractions represented in one digit.

Note that if the 'f' specifier is used alone without other custom specifiers, it's interpreted as the standard mask (full date/time pattern ). If the 'f' specifier is passed with other custom specifiers or the '%' character, it's interpreted as a custom mask specifier.

ff

A placeholder for seconds fractions represented in two digits.

fff

A placeholder for seconds fractions represented in three digits.

ffff

Displays seconds fractions represented in four digits.

The value represented by this specifier cannot be edited.

fffff

Displays seconds fractions represented in five digits.

The value represented by this specifier cannot be edited.

ffffff

Displays seconds fractions represented in six digits.

The value represented by this specifier cannot be edited.

fffffff

Displays seconds fractions represented in seven digits.

The value represented by this specifier cannot be edited.

g

Displays the era (A.D. for example) for the current date/time value.

The value represented by this specifier cannot be edited.

h

A placeholder for the hour part of the date/time value in the range 1-12.

hh

A placeholder for the hour part of the date/time value in the range 1-12. This pattern inserts two placeholders to enter a value. If a single digit is entered it is preceded with a '0'.

H

A placeholder for the hour part of the date/time value in the range 0-23.

HH

A placeholder for the hour part of the date/time value in the range 0-23. This pattern inserts two placeholders to enter a value. If a single digit is entered it is preceded with a '0'.

m

A placeholder for the minutes of the date/time value in the range 0-59.

mm

A placeholder for the minutes of the date/time value in the range 0-59. This pattern inserts two placeholders to enter a value. If a single digit is entered it is preceded with a '0'.

M

A placeholder for the month in this position, measured as a number in the range 1-12.

MM

A placeholder for the month in this position, measured as a number in the range 1-12. This pattern inserts two placeholders to enter a value. If a single digit is entered it is preceded with a '0'.

MMM

A placeholder for the abbreviated name of the month.

MMMM

A placeholder for the full name of the month.

s

A placeholder for the seconds part of the date/time value in the range 0-59.

Note that if the 's' specifier is used alone without other custom specifiers it's interpreted as the standard mask (sortable date/time pattern). If the 's' specifier is passed with other custom specifiers or the '%' character, it's interpreted as a custom mask specifier.

ss

A placeholder for the seconds part of the date/time value in the range 0-59. This pattern inserts two placeholders to enter a value. If a single digit is entered it's preceded with a '0'.

t

A placeholder for the first character of the A.M./P.M. designator of the date/time value.

Note that if the 't' specifier is used alone without other custom specifiers it's interpreted as the standard mask (short time pattern). If the 't' specifier is passed with other custom specifiers or the '%' character, it's interpreted as a custom mask specifier.

tt

A placeholder for the A.M./P.M. designator of the date/time value.

y

A placeholder for entering the last two digits of the year of the date/time value.

Note that if the 'y' specifier is used alone without other custom specifiers, it's interpreted as the standard mask (year month pattern). If the 'y' specifier is passed with other custom specifiers or the '%' character it's interpreted as a custom mask specifier.

yy

A placeholder for entering the last two digits of the year of the date/time value. This pattern inserts two placeholders to enter a value. If a single digit is entered it is preceded with a '0'.

yyyy

A placeholder for entering the year part of the date/time value including the century.

z

Displays the time zone offset for the system's current time zone in whole hours only. The offset is always displayed with a leading sign (zero is displayed as "+0"), indicating how many hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-) the current time zone is. The range of values is -12 to +13. If the offset is a single digit (0-9), it's displayed as a single digit with an appropriate leading sign.

The value represented by this specifier cannot be edited.

zz

Displays the time zone offset for the system's current time zone in whole hours only. The offset is always displayed with a leading or trailing sign (zero is displayed as "+00"), indicating how many hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-) the current time zone is. The range of values is -12 to +13. If the offset is a single digit (0-9), it's formatted with a preceding 0 (01-09) with an appropriate leading sign.

The value represented by this specifier cannot be edited.

zzz

Displays the time zone offset for the system's current time zone in hours and minutes. The offset is always displayed with a leading or trailing sign (zero is displayed as "+00:00"), indicating how many hours ahead of Greenwich mean time (+) or hours behind Greenwich mean time (-) the current time zone is. The range of values is -12:00 to +13:00. If the offset is a single digit (0-9), it's formatted with a preceding 0 (01-09) with an appropriate leading sign.

The value represented by this specifier cannot be edited.

:

Time separator.

/

Date separator. The actual date separator is determined by the DateTimeFormatInfo.DateSeparator property of the current culture.

"abc" or 'abc'

Quoted string. Inserts the string enclosed with single or double quotes literally to the edit box. The string cannot be edited.

%c

Where c is one of the standard mask specifiers, makes the specifier be interpreted as a custom mask specifier.

Note that if a mask specifier is used alone as a single character, it's interpreted as a standard mask specifier. Only mask specifiers consisting of two or more characters are interpreted as custom specifiers. In order to use a custom specifier on its own which matches one of the standard specifiers it should be preceded with the % character.

\c

The backslash is used as the escape character. It inserts the following character literally to the edit box.

Any other characters.

The other characters are inserted to the edit box literally.

ExpandedToggleIcon(9x12) Predefined Formats

You can choose one of the predefined datetime formats here. The following table contains their descriptions and samples.

Standard Mask | Name | Sample. Culture: English (USA)
d | Short date | 8/20/2007
D | Long date | Monday, August 20, 2007
t | Short time | 10:23 AM
T | Long time | 10:23:35 AM
f | Full date/time (short time) | Monday, August 20, 2007 10:23 AM
F | Full date/time (long time) | Monday, August 20, 2007 10:23:35 AM
g | General date/time (short time) | 8/20/2007 10:23 AM
G | General date/time (long time) | 8/20/2007 10:23:35 AM
M or m | Month day | August 20
R or r | RFC1123 | Mon, 20 Aug 2007 10:23:35 GMT
s | Sortable date/time | 2007-08-20T10:23:35
u | Universal sortable date/time | 2007-08-20 10:23:35Z
Y or y | Year month | August, 2007

ExpandedToggleIcon(9x12) See Also

Data Editor

Options


Database Explorer Options

This section describes Database Explorer options.

ExpandedToggleIcon(9x12) In This Section

General, Database Explorer Options Dialog Box
Describes the tab, where you can customize Database Explorer behavior.

ExpandedToggleIcon(9x12) See Also

Options

Query Execution Options


General, Database Explorer Options Dialog Box

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu. Then go to the Database Explorer node.

The General section of the Database Explorer node contains following options.

ExpandedToggleIcon(9x12) Settings

Show detailed column name in tree
Shows column type, and whether it is not null in the tree.

ExpandedToggleIcon(9x12) Table and View

Customize Query Builder for SQL Server default behavior when double-clicking on table or view node in Database Explorer.

Open object editor
Opens object editor for the table or view.

Retrieve data
Opens document with data of the table or view.

ExpandedToggleIcon(9x12) Procedure and Function

Customize Query Builder for SQL Server default behavior when double-clicking on procedure or function node in Database Explorer.

Open object editor
Opens object editor for the procedure or function.

Execute
Executes the procedure or function.

ExpandedToggleIcon(9x12) See Also

Database Explorer Options

Options


Query Builder Options

This section describes options for Query Builder's diagram.

ExpandedToggleIcon(9x12) In This Section

General, Query Builder Options Dialog Box
Describes options to tune Query Builder's diagram.

ExpandedToggleIcon(9x12) See Also

Options

Query Execution Options


General, Query Builder Options Dialog Box

Use the Options window to adjust Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu. Then go to the Query Builder node.

The General section of the Query Builder node allows you to customize diagram appearance and diagram grid options. You can use the following options:

Enable shadows

Select to enable shadows for diagram table shapes.

Enable grid

Select to enable grid on the diagram.

Grid size

Set grid size in pixels.

Snap to grid

Select to enable snapping shapes to grid when dragging them.

Background color

Set the diagram background color.

Skin type

Choose Default or Classic skin type to adjust the diagram appearance to your needs.

Use Defaults

Click to reset options on this page to their default values.

ExpandedToggleIcon(9x12) See Also

Query Builder Options

Options


Query Execution Options

The Query Execution options in the Options dialog box provide a variety of ways to customize the performance of query execution.

ExpandedToggleIcon(9x12) In This Section

General, Query Execution, Options Dialog Box
Use the Options window to adjust the query execution to your needs.

ExpandedToggleIcon(9x12) See Also

Options

Data Editor Options


General, Query Execution, Options Dialog Box

Use the Options window to adjust the query exectution behavior to your needs. To access this dialog box, select Options from the Tools menu and choosing the General page from the Query Execution folder.

The Query Execution General section of the Options window contains the following options:

ExpandedToggleIcon(9x12) General

ROWCOUNT
Causes SQL Server to stop processing the qurey after the specified number of rows are returned.

TEXTSIZE
Specifies the size of varchar (max), varbinary (max), text, ntext and image data returned by a SELECT statement.

ExpandedToggleIcon(9x12) Advanced

ARITHABORT
Select it to terminate a query when an overflow or divide-zero error occurs during query execution.

CONTACT_NULL_YIELDS_NULL
Controls whether concatenation results are treated as null or as empty string values.

DEADLOCK_PRIORITY
Specifies the relative importance that the current session continue processing if it is deadlocked with another session.

LOCK_TIMEOUT
Specifies the number of milliseconds a statement waits for a lock to be released. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means to not wait at all and return a message as soon, as a lock is encountered.

NOCOUNT
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure form being returned as a part of the result set.

NOEXEC
Complies each query, but does not execute it.

PARSEONLY
Examines the syntax of each Transact-SQL statement and returnes any error messages without compiling or executing the statement.

QUERY_GOVERNOR_COST_LIMIT
Overrides the currently configured query governor cost limit value for the current connection. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely. The query governor disallows exectution of any query that has an estimated cost exceeding that value.

SHOWPLAN_TEXT
Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.

STATISTICS IO
Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

STATISTICS TIME
Displays the number of milliseconds required to parse, compile and execute each statement.

TRANSACTION ISOLATION LEVEL
Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.

ExpandedToggleIcon(9x12) ANSI

ANSI_NULL_DFLT_ON
Modifies the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is false.

ANSI_NULLS
Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server.

ANSI_PADDING
Controls the way the column stores values shorter, than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary and varbinary data.

ANSI_WARNINGS
Specifies ISO standard behavior for several error conditions.

CURSOR_CLOSE_ON_COMMIT
Controls the behavior of the Tranasact-SQL COMMIT TRANSACTION statement. The default value for this settign is OFF. This means that the sevrver will not close cursors when you commit a transaction.

IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.

QUOTED IDENTIFIER
Causes SQL Server to follow the ISO rules regarding the quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

ExpandedToggleIcon(9x12) Other

Reset to Default
Click to reset options on this page to their default values.

ExpandedToggleIcon(9x12) See Also

Query Execution Options

Options


Query Profiler Options

This section explains code formatting options provided in the Options window.

ExpandedToggleIcon(9x12) In This Section

General, Query Profiler Options Dialog Box
Describes the options to customize Query Profiler behavior.

ExpandedToggleIcon(9x12) See Also

Options


General, Query Profiler Options Dialog Box

Use the Options window to adjust the Query Builder for SQL Server behavior to your needs. To open it, select Options from the Tools menu. Then go to the Query Profiler node.

The General section of the Query Profiler node contains the following options:

Maximum history size

Set the number of query profiling results that can be stored on the Profiler tab.

Do not highlight differences less than X.xxx s. during comparison

Set the difference in time between performing actions when they should be highlighted.

Display SQL query while printing SQL results

Select it to print the SQL query text with the query profiling results.

Free the query plan cache

Select it to empty query plan cache.

Remove all clean buffers from the buffer pool

Select it to remove all clean buffers from the buffer pool.

ExpandedToggleIcon(9x12) See Also

Query Profiler Options

Options


Query Builder

This section contains information about main components of the query document - the diagram and the tabbed editor.

ExpandedToggleIcon(9x12) In This Section

Query diagram
Describes what you can see on the query diagram.

Selection Tab
Explains the details of the Selection tab of the tabbed editor.

Joins Tab
Explains the details of the Joins tab of the tabbed editor.

Where And Having Tabs
Explains the details of the Where and Having tabs of the tabbed editor.

Group By Tab
Explains the details of the Group By tab of the tabbed editor.

Order By Tab
Explains the details of the Order By tab of the tabbed editor.

Select To Tab
Explains the details of the Select To tab of the tabbed editor.

Insert Tab
Explains the details of the Insert tab of the tabbed editor.

Update Tab
Explains the details of the Update tab of the tabbed editor.

Operand Editor, Query Builder
Explains how to specify operand parameters using the operand editor.

Choose Target Table
Describes the Choose Target Table window.


Query Diagram, Query Builder

Query diagram is the main part of Query Builder that introduces visual query representation. The query diagram contains tables, views, and subqueries participating in a query and connections between them.

The Select icon in the diagram shows what type of a query is executing now. To change the current type, right click the icon, select Change Type, and then click the type you need from the shortcut menu. Select, Insert Results, Insert Values, Update, Delete types are available.

To add a table or a view to the query, just drag-and-drop a required one from the Database Explorer.

You can see two kinds of controls on the diagram:

ExpandedToggleIcon(9x12) Table Shape

Represents a database table, view, or subquery that is included to FROM clause. It contains the list of fields that you can include to SELECT clause as well as grouping and sorting marks, and marks of the constraints. You can drag a table shape anywhere on the query diagram.

A table shape caption contains an object type icon, a name (alias), an object type, and a button that allows you to minimize table shape and restore it back.

For more information on working with table shapes, visit the Adding Tables to Diagram and the Removing Tables from Diagram topics.

ExpandedToggleIcon(9x12) Connector

Represents a join relation between two tables. To establish a connection, drag a column from one table shape to another. To make it any kind of OUTER JOIN, right-click on a connector and setup the relation. Visit the Making Joins Between Tables topic for details.

ExpandedToggleIcon(9x12) See Also

Query Builder

Selection, Query Builder


Selection, Query Builder

Use this tab to edit the select list of the query. The Selection tab contains the following details:

Unique records

Defines whether records in a result set are unique, same as DISTINCT modifier.

Column

Represents a column (columns) in the select list. This can be either column from a table or an arbitrary expression. You can choose it from the Operand Editor or enter manually.

Alias

Specify an alias for the selected column.

Table

Represents a table or a subquery from which the column is selected. Not applicable for arbitrary expressions in the select list.

Aggregate

Specifies grouping function to apply to the selected column in the select list.

Sort

Defines whether a column is sorted and specifies sorting direction.

Filter

Allows adding a simple condition on the column to the WHERE clause. Filter should have the following form: <operator> <value>. Operator can be the following: >, <, =, <>, >=, <=, IS NULL , IS NOT NULL. Value must be a constant value with the same type as the column.

Icon | Description
MoveUp | Moves the selected column up in the select list and changes accordingly the order of columns' names in the query.
MoveDown | Moves the selected column down in the select list and changes accordingly the order of columns' names in the query.
RemoveButton | Removes the selected column both from the select list and the query.

ExpandedToggleIcon(9x12) See Also

Query Builder

Joins, Query Builder


Joins, Query Builder

ExpandedToggleIcon(9x12) Joins Tab

Use this tab to establish, edit or remove joins between tables. Joins tab contains a tree with FilterGreyAdd button root node, joins as second level nodes and conditions and condition groups as further level nodes.

These buttons are in a root node. Use the FilterGreyAdd button to add joins. The template will be inserted to help you create your join. To remove the join, click the FilterGreyDel button.

Join

Joins are the second level nodes. They consist of the following parts:

Table name

Black colored names of the joined tables, views, or subqueries, located on both sides of the join kind.

Condition kind

Red-colored condition kind, for example, Join-type. You can set another condition kind for the condition by clicking the operator name and choosing the condition kind from the appeared menu. The following kinds are available: Left Outer, Right Outer, Inner, Cross, Full Outer.

Icon | Description
FilterGreyAdd | Click this button to add a condition to the join.
FilterGreyDel | Click this button to remove the join.

ExpandedToggleIcon(9x12) Condition Group

You may add a condition group if you need to add several conditions.

A condition group is displayed as a red-colored logical operator, for example, Condition-Group- and a FilterGreyAdd button. To add a group, click the red-colored condition and select Add Group from the menu. Besides, the menu offers the following options: clicking the FilterGreyAdd button adds a new condition to the group. Clicking the logical operator displays the following menu:

And

Sets the And logical operator for the group. All group conditions must be true.

Or

Sets the Or logical operator for the group. At least one condition from the group must be true.

Not And

Sets the Not And logical operator for the group. At least one condition from the group must be false.

Not Or

Sets the Not Or logical operator for the group. All group conditions must be false.

Add Condition

Adds a new condition to the group.

Add Group

Adds a new condition group to the current group.

Clear All

This command is available for the top-level condition group only. Removes all conditions and subgroups, leaving only the top-level group node.

Remove Group

This command is available for the non-top-level condition group only. Removes the condition group.

ExpandedToggleIcon(9x12) Condition

Condition consists of the following parts:

Operand

Click the operand and enter a column name as the value or select it from the drop-down list. To edit the operand values, move to the required operand and press F2.

fx (function)

Click and select function for operand from the appeared menu. If function is already selected, it is marked with blue. To change function, click the function and select the function you need again.

Criteria operator

Green-colored criteria operator, for example CriteriaOperator. To select another operator for the condition, click the operator name and choose the operator from the appeared menu.

Icon | Description
FilterGreyAdd | This button appears for operators that can have a variable number of operands. Click it to add one more operand value.
FilterGreyDel | If this button is located at the end of the condition, clicking this button deletes the condition. If this is an operator that can have variable number of operands and the button is after the operand value, clicking this button deletes this operand value.

ExpandedToggleIcon(9x12) See Also

Query Builder

Where, Having, Query Builder


Where, Having, Query Builder

Where and Having tabs contain the same controls. The only difference between them is that they edit criteria in WHERE and HAVING clauses appropriately. You may use Where and Having tabs to build very complex conditions almost without typing.

ExpandedToggleIcon(9x12) Condition group

Condition group is displayed as a red-colored logical operator and the FilterGreyAdd button. Clicking the FilterGreyAdd button adds a new condition to a group. Clicking the logical operator displays the following menu:

And
Sets And logical operator for the group. All group conditions must be true.

Or
Sets Or logical operator for the group. At least one condition from the group must be true.

Xor
Sets Xor logical operator for the group. At least one condition from the group must be true, and at least one condition must be false.

Not And
Sets Not And logical operator for the group. At least one condition from the group must be false.

Not Or
Sets Not Or logical operator for the group. All group conditions must be false.

Add Condition
Adds a new condition to the group.

Add Group
Adds a new condition group to the current group.

Clear All
This command is available for the top-level condition group only. Removes all conditions and subgroups, leaving only the top-level group node.

ExpandedToggleIcon(9x12) Condition

Condition consists of the following parts:

Operand
Click the operand and enter a column name as the value or select it from the drop-down list. To edit the operand values, move to the required operand and press F2.

fx (function)
Click and select function for operand from the appeared menu. If function is already selected, it is marked with blue. To change function, click the function and select the function you need again.

Criteria operator
Green-colored criteria operator, for example CriteriaOperator. To select another operator for the condition, click the operator name and choose the operator from the appeared menu.

Icon | Description
FilterGreyAdd | This button appears for operators that can have a variable number of operands. Click it to add one more operand value.
FilterGreyDel | If this button is located at the end of the condition, clicking this button deletes the condition. If this is an operator that can have variable number of operands and the button is after the operand value, clicking this button deletes this operand value.

ExpandedToggleIcon(9x12) See Also

Query Builder

Group By, Query Builder


Group By, Query Builder

Use this tab to edit GROUP BY clause.

Columns
Lists all columns that are not grouped.

Grouped By
Lists all grouped columns and grouping expressions.

Expression
Type grouping expression and click the button near the edit box to add expression to GROUP BY clause.

Add Expression
Click to add the expression to GROUP BY clause.

RightArrow
Adds a column to GROUP BY clause.

LeftArrow
Removes a column or grouping expression from the list.

UpArrow
Moves grouping expression up in the list.

DownArrow
Moves grouping expression down in the list.

ExpandedToggleIcon(9x12) See Also

Query Builder

Order By, Query Builder


Order By, Query Builder

Use this tab to edit ORDER BY clause.

Columns
Lists all columns that do not participate in sorting.

Grouped By
Lists all columns and expressions that participate in sorting.

Expression
Type expression and click the button near the edit box to add expression to ORDER BY clause.

Add Expression
Click to add the expression to ORDER BY clause.

Button RightArrow
Adds column to ORDER BY clause.

Button LeftArrow
Removes column or expression from ORDER BY clause.

Button UpArrow
Moves ordering expression up in the list.

Button DownArrow
Moves ordering expression down in the list.

Sorting direction button
Toggles sorting direction of the ORDER BY item.

ExpandedToggleIcon(9x12) See Also

Query Builder

Select to, Query Builder


Select to, Query Builder

Use this tab to edit the insert into list of the query. The Select to tab contains the following details:

Insert to
Represents a list of columns into which results should be inserted.

Column
Represents a column (columns) in the select list. This can be either column from a table or an arbitrary expression. You can choose it from the Operand Editor or enter manually.

Alias
Specify an alias for the selected column.

Table
Represents a table or a subquery from which the column is selected. Not applicable for arbitrary expressions in the select list.

Aggregate
Specifies grouping function to apply to the selected column in the select list.

Sort
Defines whether a column is sorted and specifies sorting direction.

Filter
Allows adding a simple condition on the column to the WHERE clause. Filter should have the following form: <operator> <value>. Operator can be the following: >, <, =, <>, >=, <=, IS NULL , IS NOT NULL. Value must be a constant value with the same type as the column.

MoveUp Button
Moves the selected column up in the select list and changes accordingly the order of columns' names in the query.

MoveDown Button
Moves the selected column down in the select list and changes accordingly the order of columns' names in the query.

RemoveButton Button
Removes the selected column both from the select list and the query.

ExpandedToggleIcon(9x12) See Also

Query Builder

Insert, Query Builder


Insert, Query Builder

Use this tab to edit the insert into statement in the query. The Insert tab contains the following details:

Name
Represents a list of columns to insert values into.

Value
Represents values to be inserted.

Default
Indicates that the value that is being inserted is default.

ExpandedToggleIcon(9x12) See Also

Query Builder

Update, Query Builder


Update, Query Builder

Use this tab to edit the update statement in the query. The Update tab contains the following details:

Name
Represents a list of columns to insert values into.

Value
Represents values to be inserted.

Default
Indicates that the value that is being inserted is default.

Filter
Allows adding a simple condition on the column to the WHERE clause. Filter should have the following form: <operator> <value>. Operator can be the following: >, <, =, <>, >=, <=, IS NULL , IS NOT NULL. Value must be a constant value with the same type as the column.

ExpandedToggleIcon(9x12) See Also

Query Builder

Operand Editor, Query Builder


Operand Editor, Query Builder

Use this operand editor to add the operands to your query. To open the editor, go to the Joins, Where or Having tabs of the tabbed editor and click the operands fields in the template. The operand editor contains the following elements:

close-editor
Hides/Collapses the operand editor.

Add-Subquery
Creates a subquery and opens the subquery tab of the query document.

ExpandedToggleIcon(9x12) Table

<All Tables>
The list of tables added to the diagram. Click the button and select a required table from the drop-down list. See the columns of the selected table under the field.

Columns
Columns of tables added to the diagram. To select a column, double-click it in the list.

ExpandedToggleIcon(9x12) Function

<All Functions>
The list of function categories to add to your query. Click the button and select a required category from the drop-down list. See the functions related to the selected category under the field.

Functions
Functions related to the selected category. To add a function to your query, double-click it in the list.

ExpandedToggleIcon(9x12) Operators

The list of operators such as (), +, -, to add to your query. Double-click a required operator in the list to add to your query.

note Note

The appearance of the Operand Editor may slightly vary depending on a product you use.

ExpandedToggleIcon(9x12) See Also

Query Builder

Choose Target Table, Query Builder


Choose Target Table, Query Builder

This window appears when you are selecting different query types and you need to select a table to insert results, values, update or delete records.

Database
Choose the database to select a table from.

Tables and Views
Choose the needed table from the list.

Filter
Specify a filter for tables, if needed.

Click OK if you selected the table or Cancel to cancel the operation.

ExpandedToggleIcon(9x12) See Also

Query Builder


Query Profiling

Use the Profiler document view to see the results of EXPLAIN command along with plan diagram, top operations, plan XML related to the executed query. Each time you get profiling results for the executed query, they appear in the tree view as a new node with time and date of query execution. You can easily distinguish and navigate to them. Besides execution date and time, they contain the connection properties, duration of execution in seconds and the server version used.

ExpandedToggleIcon(9x12) Plan Diagram

Shows the graphical representation of execution plan. The representation is read from right to left. Each query in the batch that is analyzed is displayed as well as the cost of each query as a percentage of the total cost of the batch. Hover the mouse over the needed node to get actual costs.

ExpandedToggleIcon(9x12) Plan Tree

Shows the EXPLAIN results returned by the executed query. Use the Plan Tree tab to obtain information about how SQL Server executes a SELECT statement. It shows when you need to add an index to a table to increase SELECT performance, optimize joining between tables, etc.

ExpandedToggleIcon(9x12) Top Operations

Shows a simple list of all plan operations. By default they are ordered by highest estimated cost first.

ExpandedToggleIcon(9x12) Table I/O

Shows the input-output statistics for all tables involved in the execution plan.

ExpandedToggleIcon(9x12) Plan XML

Shows the execution plan in the XML format.

ExpandedToggleIcon(9x12) See Also

Reference


Service Windows

This section describes user interface elements of service windows and dialogs.

ExpandedToggleIcon(9x12) In This Section

New File Dialog Box
The topic describes the New File dialog box.

Error List
The topic describes the Error List that helps you speed application development.

Output Window
The topic describes the Output window that displays status messages for various features.

Properties Window
The topic describes the Properties window.

Edit Value Dialog Box
The topic describes the Edit Value dialog box.

Save of Read-Only File Dialog Box
The topic describes the Save Prompt dialog box.

Save Prompt Dialog Box
The topic describes the Save of Read-Only File dialog box.

Windows Dialog Box
This topic describes the dialog box that allows you to display, close, and arrange documents that are open in editors and designers.

Product Update Manager
This topic describes the dialog box that allows you to see the update information of dbForge Query Builder for SQL Server.

Document Outline Window
Use the topic to navigate through an opened SQL document outline.


New File Dialog Box

Use this dialog box to add an item to the currently selected project. When you select an item from the Categories list, the appropriate files and references are added to your project.

To access this dialog box, choose New on the File menu, and then click File.

Categories
Lists the project item hierarchy.

Large Icons
Displays large icons for items in the templates pane.

Small Icons
Displays small icons for items in the templates pane.

Name
Enter the name of the file you want to create.

Create
Use this button to add the item to the current project.

Cancel
Click this button to close the New File dialog box.

Help
Click this button to invoke help.

ExpandedToggleIcon(9x12) See Also

Service Windows

Error List


Error List

The Error List contains errors, warnings, and messages with their location. To display the Error List, click the Error List button in the View menu. You can double-click error messages to locate errors quickly in multiple files.

The Error List window allows you to:

· Display the Errors, Warnings, and Messages produced as you edit and compile code.
· Find syntax errors
· Double-click any error message entry to open the file where the problem occurs, and move to the error location.
· Filter which entries are displayed, and which columns of information appear for each entry.

ExpandedToggleIcon(9x12) Error List Columns

Icon
Displays error or warning icon for the message.

Description
Display text of error or warning.

Location
Contains name of the document where the error occurred.

Line
Displays the line in the document, where the problem occurs.

Column
Shows the column on the indicated line where the problem begins.

ExpandedToggleIcon(9x12) Error List Shortcut Menu Commands

Goto Error
Opens the document on the place, where the error have occurred.

Copy
Copies the message to the clipboard.

Word Wrap
Enables or disables word wrap in the Description column of Error List.

Clear All
Clears Error List.

ExpandedToggleIcon(9x12) Error List Toolbar

Use the Errors, Warnings, and Messages buttons to select which entries to display.

Errors
Indicates the number of errors in the list. Click to toggle whether errors are displayed.

Warnings
Indicates the number of warnings in the list. Click to toggle whether warnings are displayed.

Messages
Indicates the number of messages in the list. Click to toggle whether messages are displayed.

In Active Document
Allows to review the errors, warnings, and messages either in the current document or in other opened documents.

ExpandedToggleIcon(9x12) See Also

Service Windows

Output Window


Output Window

This window can display status messages for various features. To display the Output window, select Output from the View menu.

Output messages can be divided into several categories. To view messages of the certain category click on its name on the Output window toolbar. If there is no such a name on the toolbar, right-click on the toolbar and select a required name from the shortcut menu.

General
Displays message statuses on general operations.

SQL Log
Displays all SQL queries executed by the application during the current session.

ExpandedToggleIcon(9x12) See Also

Service Windows

Properties Window


Properties Window

Properties window reflects properties of currently selected item in Database Explorer or Table Editor. Its content changes while you navigate through Database Explorer, switch between Table Editor views, alter objects.

To open it, click Properties on the View menu or press F4.

All properties in the grid are read-only. To modify a property of a database object, double-click it in Database Explorer. The changes are applied and rendered in the Properties window once you save the modified document.

ExpandedToggleIcon(9x12) See Also

Service Windows

Edit Value Dialog Box


Edit Value Dialog Box

Use this dialog box to enter the option value. This dialog can be opened by clicking the button button for corresponding option.

ExpandedToggleIcon(9x12) See Also

Service Windows

Save of Read-Only File Dialog Box


Save of Read-Only File Dialog Box

When an attempt is made to save a read-only file, Save of Read-Only File dialog box appears. It contains following buttons.

Save As
Click to save the file to a different location.

Overwrite
Click to remove file's read-only attribute and replace original file with the changed one.

Cancel
Click to cancel save operation.

ExpandedToggleIcon(9x12) See Also

Service Windows

Save Prompt Dialog Box


Save Prompt Dialog Box

When an attempt is made to close a document or project that is modified and not saved, Save Prompt dialog box appears. It comprises of list, containing modified project and documents, and buttons you can use to proceed. Documents are grouped by project and miscellaneous files.

Yes
Click Yes button to save selected documents and project. If a document is not implicitly named, a Save dialog will appear where you can give a name to the document.

No
Click No to discard all changes made to the documents you are closing.

Cancel
Click Cancel button to return to IDE without closing any of the documents.

ExpandedToggleIcon(9x12) See Also

Service Windows

Windows Dialog Box


Windows Dialog Box

Use this dialog box to activate, save, and close documents. To access this dialog box, in the Window menu, click Windows. Note that you must have at least one document open for this option to be available.

The Windows dialog box allows you to manipulate several documents at single click. The dialog is not closed when you apply any actions, so it is extremely useful when you have many documents currently open.

Windows list
All documents currently open are enumerated in the list. Click on a document's line to select or deselect it. Use Ctrl and Shift keys to perform group operations.

Activate
Click this button to view the selected document without closing the dialog. Available only when single document is selected in the list.

Save
Save selected documents.

Close Window(s)
Close selected documents. If some are not saved, a dialog appears where you can save them if you need.

Close
Close the current dialog.

ExpandedToggleIcon(9x12) See Also

Service Windows

Product Update Manager


Product Update Manager

Use this dialog to see the update information of dbForge Query Builder for SQL Server.

To open the Product Update Manager dialog window, click the Check for Updates button in the Help menu.

To make Query Builder for SQL Server check for updates automatically:

1. In the Tools menu, click Options.
2. Expand the Environment folder in the Options window.
3. Check the Check updates on startup once a week check box.

The Product Update Manager window contains the following details:

Product information
This section shows the product's name, version, and file size.

Refresh
Click it to update the product information.

View revision history
Click it to view the history file of dbForge Query Builder for SQL Server.

Devart authentication
Contains the Login, and the Password text boxes. Allows registered users to pre-save the login credentials.

Save downloaded installation
Check it if you want to save an installation file. Then you should specify the file location in the File name field.

File name
Enter the location of the installation file or click the Browse button to select it in the Browse for Folder dialog.

Browse
Click it to select the installation file location in the Browse for Folder dialog.

Just download a file (do not start installation)
Select it to download the installation file without starting it.

Update
Click it to start downloading the installation file.

Cancel
Click it to close the dialog. Note, this won't stop installation download. To stop downloading, close dbForge Query Builder for SQL Server.

ExpandedToggleIcon(9x12) See Also

Service Windows

Reference


Document Outline Window

The Document Outline window shows a logical structure of an opened SQL document. To open the window, select Document Outline from the View menu.

You can use the Document Outline window to view and navigate through the following items:

· statements in SQL documents.
· queries in Query Builder.
· table shapes and their connections on a database diagram.

ExpandedToggleIcon(9x12) See Also

Service Windows


Shortcut Keys

Several pre-defined keyboard shortcut collections are included in Query Builder for SQL Server. All shortcuts can be customized.

ExpandedToggleIcon(9x12) Global Shortcut Keys

The following shortcut key combinations can be used in any Query Builder for SQL Server document or tool window.

Icon | Command Name | Shortcut Keys | Description | Where is used
 | Database Explorer | Ctrl+W, L | Reveal Database Explorer window | Global
 | Data Window | Ctrl+W, D | Reveal Data window | Global
 | Properties Window | Ctrl+W, P | Reveal Properties window | Global
 | Output | Ctrl+W, O | Reveal the Output window | Global
 | Error List | Ctrl+W, E | Reveal Error List window | Global
 | Document Outline | Ctrl+W, U | Reveal Document Outline window | Global
 | Data Viewer | Ctrl+W, R | Reveal Data Viewer window | Global
 | Web Browser | Ctrl+W, W | Reveal Web Browser | Global
 | New Connection | Ctrl+D, N | Show Database Connection Properties dialog box for a new connection | Global
 | Diagram Overview | Ctrl+W, V | Reveal Diagram Overview window | Global
 |  | CTRL+Mouse Wheel | Zoom font size in or out | Global
 |  | CTRL+PLUS SIGN (+) key | Zoom font size in | Global
 |  | CTRL+MINUS SIGN (-) key | Zoom font size out | Global
 |  | CTRL+0 | Reset font size to the default value | Global
ExpandedToggleIcon(9x12) Document Management Shortcut Keys

These are default shortcuts. All shortcuts can be customized.

Icon | Command Name | Shortcut Keys | Action | Where is used
 |  | Ctrl+Tab | Switch to last used document | Global
 |  | Ctrl+Alt+ Page Down | Switch to next open document | Global
 |  | Ctrl+Alt+ Page Up | Switch to previous open document | Global
 |  | Ctrl+Shift+Tab | Cycle through all open documents | Global
 |  | F7 | Cycle through document views | Global
 |  | Shift+F7 | Cycle through document views backwards | Global
Save | Save | Ctrl+S | Save current document | Global
SaveAll | Save All | Ctrl+Shift+S | Save all documents | Global
 | Open | Ctrl+O | Open document | Global
 | Close Document | Ctrl+F4 | Close current document | Global
new-sql-window | New SQL | Ctrl+N | New SQL document | Global
Print | Print | Ctrl+P | Print | Text or data view of a document Data window

ExpandedToggleIcon(9x12) See Also

Reference


SQL Document

This section describes Query Editor user interface elements.

ExpandedToggleIcon(9x12) In This Section

SQL Editor Window
This topic describes the SQL document window interface elements.

Edit Parameters Dialog Box
This topic describes content of dialog box where you can edit query parameters.


SQL Editor

The SQL editor is a word processing utility where you enter and edit SQL statements. The statements are colorized so you can easily read them.

You can open multiple SQL editors to view SQL code and copy and paste among them. A list of all open SQL editors is available in the Windows menu.

Every Query Builder for SQL Server document has Text View that is shown in SQL editor. You can use SQL History to speed up your work. To learn how to use SQL Editor features, see the Writing and Executing SQL Statements section.

You can launch SQL editor in several ways. For example:

1. Click File -> NewSQL New SQL in the application main menu.
2. Click NewSQL New SQL on the Standard toolbar.
3. Open an SQL file by selecting File -> Open File.

You can always save a SQL document for future use.

SQL Editor Interface Elements

Code Pane

An area where SQL code is displayed and edited.

Margin Indicator Bar

A grey area at the left of the editor. Margin indicators, such as bookmarks are displayed there. You can show or hide it by setting the corresponding option in the General, Text Editor, Options dialog box.

Selection Margin

White area between the margin bar and code. Click here to select the line.

Horizontal and Vertical Scroll Bars

Allows scrolling the code. You can show or hide them by setting corresponding options in the General, Text Editor, Options dialog box.

ExpandedToggleIcon(9x12) See Also

SQL Document

Edit Parameters Dialog Box


Edit Parameters Dialog Box

This window allows you to add, delete, or modify existing parameters in the query. Click edit-parameters Edit Parameters button on the SQL toolbar to invoke this window.

Name
Displays the list of parameters that are currently used. Click on a parameter to select it and fill the fields to the right of the list with values that correspond to the selected parameter. Click it again to rename the parameter.

The following fields are specific for every parameter. Their content depends on what parameter is currently selected.

Type
Lets you choose the data type of the parameter.

Direction
Indicates whether the parameter is input, output, or bidirectional.

Nullable
Specifies whether the value of this parameter is NULL. Checking this option clears the Value field.

Value
Value of the parameter. Note that character or date and time values should not be quoted. If you want to set value sample text you should just enter sample text without quotes.

ExpandedToggleIcon(9x12) See Also

SQL Document


Add Tables to Query

When you create a query, you are retrieving data from a table or other objects structured like tables — views and certain user-defined functions. To work with any of these objects in your query, you add them to the diagram pane.

To add a table or table-valued object to a query

1. On the Start page select SQL Development, and then click Query Builder.
2. In Database Explorer, select a required table or several tables (or table-valued objects) and drag-and-drop them to the diagram pane.

Alternatively you can

1. In Database Explorer, select a required table. You can select multiple tables by holding the CTRL button.
2. Right click selected tables, and point to Send to in the shortcut menu, and then click Query Builder.

Tables and views are automatically added to the query when you reference them in the statement in the SQL editor.

note Note

A connection you are using while dragging a table must be the connection of the query document. You will not be able to perform a drag and drop operation from another connection.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Remove Tables from Query


Customizing Environment

The environment provides many useful shortcut key combinations to speed up your work. Default shortcut settings are similar to shortcuts in Visual Studio. Default keyboard mapping schema can be changed to the one similar to Visual C++ 6.0 or to Delphi. There are some more keyboard mapping schemas. Moreover, you can assign your own shortcut keys to every action.

Shortcuts can be changed on the Keyboard, Environment, Options dialog box.

To access this dialog box:

1. In the Tools menu, click Options. The dialog box appears.
2. Expand the Environment section, and click Keyboard.

expandedtoggleicon(9x12) Choosing a keyboard mapping schema

You can select a keyboard mapping schema from the Apply the following additional keyboard mapping scheme drop-down list on the Keyboard page of the Environment section in the Options dialog box.

expandedtoggleicon(9x12) Determining and removing the shortcut key combination for a command

To determine or remove the shortcut key combination for a command perform the following actions.

1. On the Keyboard, Environment, Options dialog box, enter the command name without spaces or a part of it in the Show commands containing box.
2. In the list, select the correct command.
3. Shortcuts for selected command drop-down list now contains the assigned key combination for the command if any combinations exist. You can view them in the list.
4. If you want to remove the combination, select a command in the list, and click the Remove button.

expandedtoggleicon(9x12) Assigning custom shortcut key combination for a command

To assign a shortcut key combination for a command:

1. On the Keyboard, Environment, Options dialog box, enter the command name without spaces or a part of it, in the Show commands containing text box.
2. In the list, select the correct command.
3. On the Use new shortcut in drop-down list, select the feature area where you want to use the shortcut.
4. Place the cursor in the Press shortcut keys box, and press the key combination to set for the selected command. The combination will be displayed in the box.
5. Click Assign.

expandedtoggleicon(9x12) See Also

Customizing Environment

© 2010-2017 Devart


Include Columns in Query

To use a column in a query, you must add it to the query. You may add a column to display it in query output, to use it for sorting, to search the contents of the column, or to summarize its contents.

To add an individual column

· In the Diagram Pane, select the check box next to the column that you want to include.

Alternatively

· In the Tabbed Editor, move to the first blank grid row, click the field in the Column column, and select a table and a column name from the drop-down list.

To add all columns for one table or table-valued object

· In the Diagram Pane, select the check box next to *(All Columns).

To add all columns for all tables and table-structured objects

1. In the Design Pane, select all tables.
2. Make sure no join lines in the Design Pane are selected.
3. Right-click any table and select Select all columns from the shortcut menu.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Make Joins Between Tables


Join Tables Automatically

When you add two or more tables to a query, the Query Builder attempts to determine whether they are related. If they are, the Query Builder automatically adds join lines between the rectangles representing the tables or table-structured objects.

The Query Builder recognizes tables as joined in the following cases:

1. The database contains information that specifies that the tables are related.
2. If two columns, one in each table, have the same name and data type. The column must be a primary key in at least one of the tables. For example, if you add person and password tables, if the password_id column is the primary key in the password table, and if each table has a column called password_id with the same data type, the Query Builder will automatically join the tables.
3. The Query Builder detects that a search condition (e.g. the WHERE clause) is actually a join condition. For example, you might add the tables person and password, then create a search condition that searches for the same value in the password_id column of both tables. When you do, the Query Builder detects that the search condition results in a join, and then creates a join condition based on the search condition.

If the Query Builder has created a join that is not suitable to your query, you can modify the join or remove it. If the tool does not automatically join the tables in your query, you can create a join yourself.

note Note

The Query Builder will create only one join based on columns with the same name and data type. If more than one join is possible, the Query Builder stops after creating a join based on the first set of matching columns that it finds.

ExpandedToggleIcon(9x12) See Also

Query with Joins

Join Tables Automatically

© 2010-2017 Devart


Join Tables Manually

When you add two (or more) tables to a query, the Query Builder attempts to join them based on common data or on information stored in the database about how tables are related. However, if the Query Builder has not joined the tables automatically, or if you want to create additional join conditions between tables, you can join tables manually.

You can create joins based on comparisons between any two columns, not just columns that contain the same information.

note Note

Joins work fastest if the columns in the join condition have been indexed. In some cases, joining on unindexed columns can result in a slow query. it finds.

To manually join tables or table-structured objects:

1. Add to the Design Pane the objects you want to join.
2. Drag the name of the join column in the first table or table-structured object and drop it onto the related column in the second table or table-structured object. You cannot base a join on text, ntext, or image columns.

note Note

The join columns must be of the same (or compatible) data types. For example, if the join column in the first table is a date, you must relate it to a date column in the second table. On the other hand, if the first join column is an integer, the related join column must also be of an integer data type, but it can be a different size. The Query and View Designer will not check the data types of the columns you use to create a join, but when you execute the query, the database will display an error if the data types are not compatible.


Manipulating Documents

The topic describes how to manipulate documents in Query Builder for SQL Server.

expandedtoggleicon(9x12) Managing Documents

The Windows Dialog Box allows you to switch between documents and manage them. To open it, select Windows from the Window menu. This dialog allows you to save and close multiple documents at once, and activate required document.

To save document(s) select required document(s) and click the Save button.

To close document(s) select required document(s) and click the Close Window(s) button.

note Note

To close an opened document, click the document title with middle mouse button (mouse wheel).

To activate required document, select the document and click the Activate button.

expandedtoggleicon(9x12) Navigating through the Document Views

Each SQL document includes different representations of the content - document views.

To switch to a specific view, click a button with the view name on the bottom pane of the document. Alternatively, select the required view from the shortcut menu of the document bottom pane.

You can also cycle through available views with the F7 key.

expandedtoggleicon(9x12) Working with Document Tab Groups

Multiple documents can be organized into horizontal or vertical tab groups for organizing a workspace for working with several documents at the same time. New document tab group can be created either by dragging document to the required place or by selecting New Horizontal Tab Group or New Vertical Tab Group from the document header shortcut menu.

Documents can be moved between tab groups by dragging-n-dropping or by selecting required command from the shortcut menu.

expandedtoggleicon(9x12) Restoring Tabs

Document tabs can be saved to cash and eventually be restored after the application crush or accidental closing. To customize the tab restore options, on the Tools menu, select Options. In the Options dialog box, expand Environment, then expand Documents and select Restore. Set the time after which the data will be saved automatically and select restartable file types.

Document tabs will be restored automatically at the run of the application.

expandedtoggleicon(9x12) Document Header Shortcut Menu

The document header shortcut menu includes the following options:

Save <Name>.sql
Saves a current SQL document.

Close
Closes a current document.

Close All But This
Closes all documents excepting the selected one.

Copy Full Path
Copies the full path for the document.

Open Containing Folder
Opens the folder containing the document.

Float
Detaches the window.

New Horizontal Tab Group
Creates new horizontal tab group.

New Vertical Tab Group
Creates new vertical tab group.

expandedtoggleicon(9x12) See Also

Customizing Window Layout

Customizing Environment


Query with Joins

A query result can include data from multiple tables or table-valued objects. To combine data from multiple table-valued objects, you use the JOIN operation from SQL.

ExpandedToggleIcon(9x12) In This Section

Join Tables Automatically
Describes automatic table join.

Join Tables Manually
Describes how to join tables manually.


Remove Tables from Query

You can remove a table or any table-valued object from the query.

To remove a table or table-structured object

· In the Diagram Pane, select a table, view, user-defined function, synonym, or query, and then press DELETE, or right-click the object and then click Remove from the diagram. You can select and remove multiple objects at one time.

Alternatively

· Remove all references to the object in the SQL editor.

When you remove a table or table-valued object, the Query Builder automatically removes joins that involve that table or table-valued object and removes references to the object's columns in the SQL editor and.

note Note

Removing a table or table-valued object does not delete anything from the database, it only removes it from the current query.

ExpandedToggleIcon(9x12) See Also

Building Queries with Query Builder

How To: Include Columns in Query