In SQL Server Identifiers refers to Database Objects, which are to be distinguished from Variables.

Note that in SQL Server database objects should be delimited by double quotes ("), whereas literals (which may be passed to variables) should be delimited by single quotes (').

Identifiers

Identifiers name nearly every "database" object in SQL Server including the following: Servers, Databases, Tables, Views, Columns, Indexes, Triggers, Procedures, Constraints, and Rules. Most database objects require names, but some, such as constraints, do not.

Identifiers must follow four rules:

  1. The first character must be Unicode letters, A-Z, a-z, _, @, or #.
  2. The following characters can also include 0-9 and $.
  3. The identifier cannot be a Transact-SQL reserved word.
  4. The identifier cannot have an embedded space or special characters.

Identifiers that do not follow these rules must be delimited by brackets or double quotes.

[My Table]  --requires delimiters because of space. "order"     --requires delimiters because it is reserved word.
[MyColumn]  --delimiters not required but may be used anyway.

Depending on how mixed an environment a developer is working, database objects may or may not be qualified. Here are the different ways a database object may be qualified.

[ [ [ server. ] [ DBorCatalog ] . ] [ OwnerOrSchema ] . ] object
server.db.schema.obj --this is fully qualified
server.db..obj
server..schema.obj
server..obj
db.schema.obj
db..obj
schema.obj
obj

Within a table or view columns can have up to three qualifiers. The variations include:

db.schema.obj.col
db..obj.col
schema.obj.col
obj.col
col

Database Objects

There cannot be ambiguity when identifying database objects (eg Servers, Databases, Tables, Views, Columns, Indexes, Triggers, Procedures, Constraints, Rules, etc., depending on the DBMS).

Ambiguity may occur if multiple objects have the same name in the system. EG:

To resolve this it is helpful to qualify objects as needed. Here are some possibilities of a database object that goes from unqualified to increasingly qualified to fully qualified:

Field Table.Field Database.Owner.Table.Field Server.Database.Owner.Table.Field

It becomes cumbersome to use qualified database objects all the time. It is useful to use an alias (aka variable, range variable, or correlation name). In the FROM clause for aliased tables or views. EG:

SELECT tblCustomer.Name
FROM MyDatabase.MyOwner.tblCustomer AS Cus
    INNER JOIN MyDatabase.MyOwner.tblOrders As Ord
    ON Cus.Name = Ord.Name
WHERE Ord.Name = 'George'

Variables

The following prefixes have special meanings in SQL Server:

There are two ways data can be passed between statements:

  1. Transact-SQL Variables can hold data within the batch or stored procedure, i.e. like local scope variables.
  2. Transact-SQL Parameters can input or output data between a stored procedure and the user, i.e. like global scope variables.

DECLARE variables has this syntax:

DECLARE { { @LocalVariable DataType } 
        | { @CursorVariableName CURSOR } 
        | { TableTypeDefinition } 
        } [, ...]

Parameters follow this syntax:

@parameterName datatype [= default] [OUTPUT]

Note that the default value can include wildcards (EG: % and _) that can be used with SQL keywords such as LIKE.

This example shows a local variable and two parameters: one for input and another for output.

CREATE PROCEDURE myStoredProcedure
@InputParameter int, @OutputParameter int OUTPUT
AS

DECLARE  @LocalVariable INT
SET @LocalVariable = 2

SELECT * FROM Table
WHERE Field = @InputParameter/@LocalVariable

SELECT @OutputParameter = MAX(Field)
GO

Page Modified: (Hand noted: 2007-11-04 23:41:47Z) (Auto noted: 2007-11-04 23:45:37Z)