If you are creating queries using Microsoft® SQL Server™, you can take advantage of features specific to that server. The following guidelines outline differences you will notice in the Query Designer when you work with SQL Server, and provide information about SQL Server-specific features that you can use.
Below you will find information about:
The Query Designer is designed to support version 6.5 of the Microsoft SQL Server, but is fully compatible with earlier versions as well. If you are using a version of SQL Server earlier than 6.0, the Query Designer will not recognize SQL Server-specific features (such as syntax elements or functions). Instead, the Query Designer will recognize only standard ODBC syntax.
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 yourself. 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.
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
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.
For compatibility with other databases, you can use [ and ] around identifier names in the SQL pane. When the SQL statement is reformatted, [ and ] are replaced with double quotation marks ("), which are used to mark database identifiers.
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 (see Identifying Database Objects, above).
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.
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.
In the SQL pane, you can use the = operator to specify an alias, as in this example:
SELECT tax = price * .1
FROM table
When you change panes or execute the query, the SQL statement is reformatted and the = is replaced with AS.
The SQL Server supports all standard operators for expressions, including % for modulus. In addition, you can use the following bitwise operators in expressions:
| Operator | Meaning |
| & | BITAND |
| | | BITOR |
| ^ | BITXOR |
| ~ | BITNOT |
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:
@@CONNECTIONS and @@CURSOR_ROW into the Grid and SQL panes.CURRENT_TIMESTAMP and CURRENT_USER in either pane.NULL in the Grid or SQL panes, it is treated as a literal value, not a constant.
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.
If you are working with SQL Server version 6.5 or earlier, using the DISTINCT keyword creates a query that cannot be updated in the Results pane.
In the SQL pane, you can use the SQL Server-specific *= and =* operators to specify an outer join. When you change panes or execute the query, the Query Designer reformats your SQL statement, and replaces *= with LEFT OUTER JOIN and =* with RIGHT OUTER JOIN.
In SQL Server, 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 'abd def'.
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 details about optimizer hints, refer to the Microsoft SQL Server documentation.
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 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, ODBC settings on the local (client) computer specify whether the data is stored in ANSI or OEM format. The option is specified using the Convert OEM to ANSI Characters option in the ODBC SQL Server Setup dialog box.
By default, this option is not selected for the SQL Server ODBC driver, a choice which causes the data to be stored in ANSI format. However, if this option has been selected, the ODBC driver converts 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 ODBC SQL Server Setup dialog box. Depending on these variables:
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 incorrect, the OEM conversion setting is probably wrong. Close the query and the project, change the setting in the ODBC SQL Server Setup dialog box, and then open the project and query again.
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.
The following types of SQL Server queries are not supported graphically:
The following types of queries cannot be represented graphically, so you must enter them in the SQL pane. They do not result in errors when you execute them or change panes.