Query Designer Considerations for SQL Server Databases

Visual Database Tools

previous page next page
Visual Database Tools

Query Designer Considerations for SQL Server Databases

The following guidelines provide information about SQL Server-specific features that you can use.

Below you will find information about:

SQL Syntax in Query Designer

When the Query Designer builds a statement in the SQL pane, it will use syntax specific to SQL Server whenever possible. For example, database objects such as tables and views are qualified using SQL Server owner names.

You can also type SQL Server-specific syntax in the SQL pane. In some cases when you verify a query, the Query Designer converts server-specific syntax to ANSI standard syntax. However, the changed query will always return the same results.

Identifying Database Objects

When you enter the names of database objects (tables, views, and columns) in the SQL pane, you must provide sufficient information for SQL Server to identify the object you want. Database objects are identified with unique names that consist of up to three parts (for tables and views) or four parts (for columns):

database.owner.table

database.owner.table.column

Note   You can join tables from different databases on the same server. In that case, database objects can have four part names. For more details, see Working with Tables from Different Data Sources.

In general, you need to provide only enough qualifiers to uniquely identify the object you want to work with. For example, if you are working with a column called price in the titles table in the current database, you can simply reference the column by name, as in this SQL statement:

SELECT price
FROM titles

However, if you are working with two tables, such as orders and products, and each has a column called price, you must qualify references to the column with the appropriate table name, as in this example:

SELECT products.prod_id, orders.price
FROM orders INNER JOIN products ON 
 orders.prod_id = products.prod_id

When you use the Diagram pane and Grid pane to work with tables in the current database, the Query Designer automatically adds owner and table qualifiers for you. If you are not the owner of a table that you are working with, the owner's name will appear in the table names. For example, if you work in the pubs database, the owner name dbo will appear in front of table names. If you are working with multiple tables, the Query Designer adds table name qualifiers to column names.

Using Quotation Marks

The standard delimiters for literal strings in SQL are single quotation marks ('). By default, SQL Server reserves double quotation marks (") as delimiters for database objects.

The SQL Server ODBC driver supports a Quoted Identifiers setting for the session or connection. If this setting is on, double quotation marks are interpreted as delimiters for identifiers. However, if you turn this setting off, double quotation marks are interpreted instead as delimiters for literal strings.

To avoid ambiguity, the Query Designer always sets Quoted Identifiers on, so that double quotation marks are always interpreted as database object delimiters. If you have previously turned Quoted Identifiers off, the Query Designer overrides your setting.

Therefore, in the Query Designer, always use single quotation marks to enclose string literals. Use double quotation marks only as needed for database objects delimiters.

Case Sensitivity

Text information in a SQL Server database can be stored in uppercase letters, lowercase letters, or a combination of both. For example, a last name can appear as "SMITH," "Smith," or "smith."

Depending on how SQL Server was installed, databases can be case-sensitive or case-insensitive. If a database is case-sensitive, when you search for text data, you must construct your search conditions using the exact combination of uppercase and lowercase letters. For example, if you are looking for a name such as "Smith," you cannot use the search conditions "=smith" or "=SMITH."

In addition, if the server was installed as case-sensitive, you must provide database, owner, table, and column names using the correct combination of uppercase and lowercase characters. If the case of the name you provide does not match exactly, SQL Server returns an error reporting an "invalid object name."

When you create queries using the Diagram and Grid panes, the Query Designer will always accurately reflect the case-sensitivity of your server. However, if you enter queries in the SQL pane, you must be careful to match names to the way they will be interpreted by the server.

If the server was installed with a case-insensitive option, you can enter database object identifiers and search conditions using any combination of uppercase and lowercase characters.

Tip   To determine the case sensitivity of a server, execute the stored procedure sp_server_info, and then examine the contents of row 18. If the server has been installed with the case-insensitive setting, the option for sort_order will be set to nocase.   You can run a stored procedure from the Query Analyzer.

Entering Keywords in the Grid and SQL Panes

The Query Designer supports the use of certain SQL Server constants, variables, and reserved column names in the Grid or SQL panes. Generally, you can enter these values by typing them in, but the Grid pane will not display them in drop-down lists. Examples of supported names include:

  • IDENTITYCOL   If you enter this name in the Grid or SQL pane, the SQL Server will recognize it as a reference to an auto-incrementing column.

  • Predefined global values   You can enter values such as @@CONNECTIONS and @@CURSOR_ROW into the Grid and SQL panes.

  • Constants (niladic functions)   You can enter constant values such as CURRENT_TIMESTAMP and CURRENT_USER in either pane.

  • NULL   If you enter NULL in the Grid or SQL panes, it is treated as a literal value, not a constant.

Entering Currency Values in the Grid Pane

In the Grid pane, to specify that you want data interpreted as money, precede the value with $ or $- (for negative values). Do not include a comma or other delimiter to indicate thousands. Formatting values this way alerts the Query Designer that you are entering values to be treated as or compared to data in money or smallmoney type columns. Values are rounded to the nearest hundredth of a unit.

You can use $ no matter what currency you are working with. When a query displays values from money columns in the Results pane, it does not include the $ prefix. Depending on the setting in the Windows Regional Settings dialog box, currency data might or might not include a comma or other delimiter for thousands.

Using the GUID Data Type

You can include references to the GUID data type, which is used to store globally unique identifiers. In Update and Insert From queries you can call the newid( ) function to generate a new GUID to be stored in the database.

When you are creating a Select query, the only operations allowed with a GUID type column are comparisons based on equality (= and <>).

Entering Blanks

You can specify a zero-length string in an Update or Insert Into query by entering two single quotation marks, as in the following example:

UPDATE  employee
SET     minit = ''
WHERE   emp_id = 'CFS88322F'

In versions of SQL Server 6.5 or earlier, two single quotation marks are treated as a single space. For example, you can use quotation marks in the following expression: 'abc' + '' + 'def'. The resulting value would be 'abc def'.

Including Optimizer Hint Comments in the SQL Pane

If you are entering a query directly in the SQL pane, you can add optimizer hints to specify the use of specific indexes, locking methods, and so on. However, when reformatting the contents of the SQL pane, the Query Designer might not maintain these comments. Optimizer comments are not represented graphically.

For more information, see Hints.

ANSI to OEM Character Conversion

Data containing extended characters — that is, characters outside the ASCII range 32 (space) to 126 (~), including international characters such as "ä," "ç," "é," "ñ," and "ß" — can require special handling when you are working with SQL Server.

The representation of extended characters in a result set depends on the code page in use. A code page is a character set that a computer uses to interpret and display data properly. Code pages usually correspond to different platforms and languages and are used in international applications. For example, the ASCII value 174 might appear as the symbol "®" in one code page but as a chevron character in another code page.

In general, code pages are divided into ANSI code pages and OEM code pages. ANSI code pages, in which high-numbered ASCII values represent international characters, are used in Windows. OEM code pages, in which high-numbered ASCII values represent line-drawing and punctuation characters, were designed for MS-DOS®.

When data is entered into a SQL Server database, SQL Server settings on the local (client) computer specify whether the data is stored in ANSI or OEM format. The option is specified using the Automatic ANSI to OEM conversion option on the DB Library Options tab in the SQL Server Client Configuration dialog box. This dialog box is available by clicking the Microsoft SQL Server Client Network Utility from Programs on the Start menu. (For more information, see Using the DB-Library Automatic ANSI to OEM Conversion Option.)

By default, this option is selected for the SQL Server Client, a choice which causes the data to be converted from high-numbered ASCII characters to OEM characters. For example, if the OEM conversion option is set and you enter the name "Günther" in a column and then save the row, the character "ü" will be converted to another character before the row is stored in the database.

The results of queries that you create in the Query Designer are affected by the format in which extended-character data is stored in combination with the setting of the OEM conversion option in the SQL Server Client Configuration dialog box. Depending on these variables:

  • You might not be able to search for data that includes high-order ASCII characters.

  • Your query results might appear in the Results pane with incorrect characters substituted for high-order ASCII characters.

In general, if data is stored in OEM format, you should set the OEM conversion option so the data will display properly and so you can search it. If data is stored in ANSI format (that is, it was not converted to OEM format) but you have set the OEM conversion option, the data will not display properly and you will not be able to search for it.

To determine whether data was stored in OEM format, you can use a query to display the contents of the table or tables you are working with. If extended characters appear incorrectly, the OEM conversion setting is probably wrong. Close the query and the project, change the setting in the SQL Server Client Configuration dialog box, and then open the project and query again.

Query Designer Unsupported and Partially Supported Query Types

Some types of legal SQL Server queries cannot be represented graphically in the Query Designer. You can still enter them in the SQL pane, and they will execute correctly. However, the Query Designer will display the Query Definitions Differ dialog box and report an error when you execute your query or change panes.

Several types of SQL Server queries are not supported graphically, including:

  • Queries using INTERSECT.

  • Queries using UNION [ALL].

  • Queries using CASE.

  • Any data definition (DDL) query, including CREATE TABLE, ALTER TABLE, CREATE PROCEDURE, ALTER PROCEDURE, and so on. CREATE VIEW and ALTER VIEW queries are not supported graphically, but you can use the View Designer to create and edit views.

  • Update and Delete queries that include an extra FROM clause (FROM table FROM table) that specifies the list of rows to update or delete.

  • Queries using the FOR BROWSE clause.

  • Queries that include UPDATE as a search condition.

  • Queries including CURRENT OF.

Working with Tables from Different Data Sources

You can create distributed, heterogeneous queries —queries from tables and table-structured objects outside the server to which you have created a data connection. SQL Server can access any data source that supports OLE DB. You can use tables and table-structured objects from these outside data sources as you would any tables available on the base server (if you have proper permissions to access to the outside data source).

Microsoft SQL Server can access outside data sources in two ways. The first is using a linked server, which is defined in the SQL Server database to point to the outside data source. A linked server makes the data source accessible using a naming convention similar to that of native SQL Server data objects. The second is to use a dynamic reference to the outside source using the OpenRowset( ) function, which allows you to connect to any accessible data source in your query, even if no linked server is defined for it.

Tables and table-structured objects from outside servers do not appear as part of the list of tables. Instead, to use them, you use syntax in the SQL pane of the Query Designer to refer to the linked server or to include the OpenRowset( ) function. However, when you refer to an outside data source in the SQL pane, the Query Designer adds a rectangle representing the table or table-structured object to the Diagram pane to represent the outside data source.

To refer to an outside data source using a linked server

  • In the SQL pane, use the following syntax to refer to the table: 
    linkserver.catalog.schema.object

    Where:

    • linkserver represents the name on the local Microsoft SQL Server data source given to the linked server in OLE DB.

    • catalog represents the name of the database containing the object.

    • schema represents the owner of the object.

    • object represents the table or view in the database.

Note   You must define the SQL Server data source (the linkserver part of the name) before you use this name in the query.

The following is an example of an SQL statement that joins data from tables from the local database with a table on a server called "hrserver":

SELECT e.id, e.lname, h.hiredate
FROM employee AS e INNER JOIN hrserver.hr.dbo.hiredata AS H
ON e.emp_id = h.emp_id

To refer to an outside data source dynamically

  • In the SQL pane, use the OpenRowset( ) function in place of a table reference, with the following syntax: 
    OpenRowset(provider,connectString,object)

    Where:

    • provider represents the friendly name of the OLE DB provider.

    • connectString represents a string that includes information for connecting to the outside data source. This parameter is optional if an ODBC connection is already established between the local server and the outside data source. The connectString parameter can take these forms:

      datasource;user id;password, which lists specific connection attributes.

      -or-

      provider string, which is a single string of named attributes with values for creating the connection, similar to the string used in a .dsn file.

    • object represents the name of a database object. You can refer to a table, view, or other database object using the standard naming convention recognized by the outside data source.

    Tip   The data source referenced by OpenRowset( ) is easier to work with if you assign it a table alias.

The following examples illustrate variations on using OpenRowset( ) to dynamically access data from an outside data source. The first shows access using a set of attributes for the connect string to a Microsoft® Jet (Access) database. The second example shows how you can pass a connect string.

SELECT n.* 
FROM OpenRowset('Microsoft.jet.OLEDB.3.51', 
    'c:\nwind.mdb';'admin';'pwd', authors)
AS n

SELECT a.* 
FROM OpenRowset('MSDASQL', 'Driver=SQL Server;Server=Test;
    UID=user1;PWD=pwd', pubs.dbo.authors)
AS a

See Also

Creating Make Table Queries | Creating Queries | Designing Queries | Specifying Parameter Marker Characters | Supported Query Types | Using Expressions in a Query

previous page start next page