Operations with aliases
Last modified: June 9, 2025
SQL Complete automatically assigns an alias to each table, view, table-valued function, and synonym referenced in a SQL statement. You can set up alias generation in the Options dialog.
Open the Options dialog
To open the Options dialog:
1. Select SQL Complete > Options.
2. Go to the Alias page.
For instructions on setting up alias generation behavior, see Alias.
Automatic alias assignment
The table displays options available on the Alias page of the Options dialog.
Name | Description | Default state |
---|---|---|
Generate alias on commit | Automatically creates an alias for tables or database objects when a query is committed. | On |
Generate aliases in UPPER case | Automatically creates aliases in uppercase letters. | Off |
Automatically generate AS clause | Includes the AS keyword when aliases are generated. |
Off |
When the Generate alias on commit checkbox is selected, SQL Complete operates as follows:
-
Adds aliases to tables, views, table-valued functions, and synonyms: When you select a database object from the suggestion list, SQL Complete automatically assigns an alias to it.
-
Adds table alias to column names: When you select all columns using the asterisk (*) wildcard or individually select specific columns from the suggestion list, SQL Complete adds a table alias to each column name.
Alias generation rules
SQL Complete applies a set of naming rules and heuristics to automatically generate aliases. Whenever possible, SQL Complete generates aliases using the first letter of a table object name.
The table explains the rules for alias generation.
Name | Description | Example |
---|---|---|
For names with underscores | When an object name includes underscores, SQL Complete uses the first letter of each word to form the alias. | Table_Customer → tc |
For names with hyphens | Hyphenated names are split into parts, and the alias is generated from the initials. | Table-Customer → tc |
For names with CamelCase | The alias is created from the uppercase letters (initials of each word). | TableCustomer → tc |
For object names that contain numbers | If the object name consists only of numbers or begins with a digit, SQL Complete assigns a default alias, such as a. | 111 → a |
For names with prefixes | For names with prefixes, such as tbl, v, fnc at the beginning of the name, SQL Complete assigns aliases without these prefixes. | tblCustomerSupport → tc |
For object names that would produce duplicate aliases | If an alias already exists in the current query, for example, in a multi-table JOIN, SQL Complete adds a numeric suffix to avoid conflicts. | If ta already exists, the following alias becomes ta1, then ta2, and so on. |
No Alias | If No Alias is specified in the Action column for a given condition, no alias will be generated for matching objects. |
SQL Complete creates aliases with additional symbols 1, 2…n to avoid ambiguity, for instance, in SELF JOINS:
Custom alias mapping
You can manually define conditions that determine when an alias should be inserted while writing a query using the Completion List in a SQL document.
To add a custom alias:
1. Select SQL Complete > Options.
2. Navigate to the Alias page.
3. In the Condition column, specify the database object name (you may use a mask) to which you want to assign an alias.
4. In the Action column, specify the custom alias you prefer.
5. Select OK to save the changes.
When a custom alias is created, SQL Complete assigns the alias name to an object referenced in a SQL statement according to a specified alias mask.
Note
Custom aliases have precedence over automatically created ones.
Alias refactoring
To rename an alias:
1. Right-click the required alias and select Rename, or select the alias and press F2 - the alias will be highlighted.
2. Type a new name for the alias in the SQL Editor.
As you type, a tooltip appears instructing to Press F2 to preview changes or Enter/Tab to apply.
3. Press F2 to open the Preview Changes - Rename dialog and preview code changes.
4. To apply changes, select Apply, or press Enter/Tab.
For more information, see Rename aliases.
Alias masks
SQL Complete supports alias masks, allowing you to define custom rules for alias generation based on object naming patterns. These masks help manage how aliases are applied, especially when dealing with objects that share similar or identical names.
By using alias masks, you can define conditions and corresponding actions to control how aliases are assigned in different scenarios.
The following examples illustrate how condition masks work:
<product>
- exact match.<*product>
- matches object names ending with product.<product*>
- matches object names starting with product.<*product*>
- matches object names, including product.<*>
- matches any valid object name.<schema>.<product>
- specifies an exact match for the object name product with schema as its prefix.<schema*>.<product*>
- matches object names where the prefix starts with schema and the object name starts with product.<database>.<schema>.<product>
- matches the object name product with a database and schema in the prefix.
The following list demonstrates examples of the action masks:
Id<1>
- adds the prefix Id to a generated alias.<1>.<2>
- generates an alias with a prefix.<[Dept]1>
- excludes Dept from a generated alias.
Mask prioritization
User-defined alias masks take precedence over automated alias generation in SQL Complete. Within the custom mask list, masks are evaluated from top to bottom, meaning the condition at the top of the list in the Condition column has the highest priority.
When multiple conditions match a database object, the first matched condition in the list determines the alias to be applied.
Example 1: Condition with higher priority
Now, consider the SQL statement:
SELECT * FROM Person.BusinessEntity
Since both conditions match (BusinessEntity starts with Business), Alias1 applies because its condition appears higher in the list:
SELECT * FROM Person.BusinessEntity AS Alias1
Note
The
AS
clause is included automatically when the Automatically generate AS clause checkbox is selected on the Options > Alias dialog page.
Example 2: Reversed priority
If you reverse the order of the two conditions:
Then Alias2 will be applied instead, because <Business*>
is now at the top:
SELECT * FROM Person.BusinessEntity AS Alias2
Note
The
AS
clause is included automatically when the Automatically generate AS clause checkbox is selected on the Options > Alias dialog page.
Tip
You can change the order of aliases using Move up and Move down next to the Alias grid on the Options > Alias dialog page.
Example: Prioritization in practice
Alias assignment logic
alias1
applies to all objects containing Person and takes the highest priority.alias2
applies to all objects whose names start with Product.alias3
applies to objects starting with Product that belong to the Sales schema.
Rules for defining a condition in the Condition column
In the Condition column, you can define the criteria under which an alias will be applied based on the corresponding rule in the Action column. These rules are triggered when an identifier, such as a table, view, table-valued function, or synonym, is inserted into a SQL document from the Completion List.
For a condition to apply, its criteria must be fully satisfied. If the condition is met, SQL Complete generates the alias specified in the Action column.
Note
Each identifier in the condition must be enclosed in angle brackets
< >
.
Note
The condition is case-insensitive, meaning it can be written in either lowercase or uppercase. The rule applies based on the identifier name, regardless of its case.
Consider several examples to understand how to create custom aliases in the Options dialog.
Case 1: Generate an alias for the database object regardless of prefixes
To assign a custom alias, for example, NewAlias, to a database object, such as the Employee table, regardless of its server, schema, or database, use the following configuration.
Note
This rule applies to any object named Employee, regardless of which database or schema it belongs to.
Alias application example
When you write a SQL query and select Employee from the Completion List, SQL Complete automatically inserts the alias NewAlias:
SELECT * FROM HumanResources.Employee NewAlias
SELECT * FROM Person.Employee NewAlias
SELECT * FROM dbo.Employee NewAlias
This ensures consistent aliasing of the employee object across different databases and schemas.
Case 2: Generate an alias for the database object from a specific schema
To assign a custom alias, for example, NewAlias, specifically to the employee table from the HumanResources database, use the following configuration:
This condition applies only to the employee object in the HumanResources database. Objects with the same name in other databases will follow default alias generation rules.
Alias application example
The custom alias NewAlias applies only when the employee table belongs to the HumanResources database. In all other cases, SQL Complete generates aliases according to the standard rules.
SELECT * FROM HumanResources.Employee NewAlias
SELECT * FROM Person.Employee e
SELECT * FROM dbo.Employee e
Case 3: Generate an alias for the database object with multiple prefixes
The Condition column can include up to three prefixes, such as a server, a database, and a schema, depending on how the object is referenced in the script. An alias is generated only when all specified prefixes are present in the SQL statement. However, the maximum number of prefixes allowed is three. For example:
<server name or linked server name>.<database name>.<schema name>.<object name>
uses three prefixes for a table.<database name>.<schema name>.<object name>
uses two prefixes for a table.<schema name>.<object name>
uses one prefix for a table.
If the prefixes listed in the Condition column exactly match the SQL statement, the corresponding alias is applied. Otherwise, no alias is generated.
Alias application example
The NewAlias alias is generated for the Address table only when the expression includes all prefixes specified in the Condition column, such as the linked server name, database name, and schema name:
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address NewAlias
However, if the expression does not include the LinkToSQLServer2022 prefix, the NewAlias alias will not be generated:
SELECT * FROM AdventureWorks2022.Person.Address a
If any prefix is missing from the object reference, no alias will be generated. This ensures precise alias control when working with objects that may exist across multiple environments or schemas.
Case 4: Use an asterisk (*) for any characters in the condition
In the Condition column, you can use an asterisk *
as a wildcard to match any sequence of characters before or after an identifier. The wildcard can be placed anywhere within angle brackets < >
.
Alias application example
In this case, the NewAlias alias is generated for all servers and databases, which contain the Address identifier in the Person schema. For example:
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address NewAlias
SELECT * FROM LinkToSQLServer2022.AdventureWorks2019.Person.Address NewAlias
Case 5: Use an asterisk (*) in the identifier name
You can use an asterisk *
within an identifier name in the Condition column to match partial names. This allows you to generate aliases for multiple objects that share a common naming pattern.
This condition applies the alias NewAlias to all objects in the Person database whose names start with Business.
Alias application example
SELECT * FROM Person.BusinessEntity NewAlias
SELECT * FROM Person.BusinessEntityAddress NewAlias
SELECT * FROM Person.BusinessEntityContact NewAlias
Note
The asterisk
*
can be used in any part of the identifier within angle brackets< >
, enabling flexible matching patterns, such as<*name>
,<na*me>
, or<name*>
.
Rules for defining an action in the Action column
The Action column lets you control how aliases are generated when a matching condition from the Condition column is met. In the Action column, you can do the following:
- Define a custom alias format
Specify the characters or structure that will be automatically applied as an alias when an identifier is inserted from the Completion List.
- Exclude parts of the identifier
To omit specific parts of an identifier from the alias, enclose them in square brackets [ ]
. These excluded elements will not be used in alias generation.
- Target specific identifier segments/Use specific identifier elements/Define alias structure using identifier parts
You can refer to specific parts of the identifier, such as server, database, schema, or object name, to customize how the alias is constructed.
- Disable alias generation
Use No Alias to explicitly prevent alias generation for objects matching the corresponding condition in the Condition column.
The simple case can be as follows:
In this case, the NewAlias alias is generated for the ContactType table from the Person schema.
SELECT * FROM Person.ContactType NewAlias
Case 1: Use one element in the Action column
If the Condition column contains only one element, for example, an object identifier, the Action column will have the <1>
value by default. This means that if you specify <1>
in the Action column for this condition, an alias will be generated according to the standard alias generation algorithm.
Alias application example
SELECT * FROM Person.ContactType ct
Case 2: Add characters before or after one element
Adding characters before or after <1>
will automatically add those characters to the generated alias.
Example 1: Add characters after <1>
For example, add NewAlias after <1>
:
Alias application example
SELECT * FROM Person.ContactType ctNewAlias
Example 2: Add characters before <1>
Writing NewAlias before <1>
will generate the alias as follows:
Alias application example
SELECT * FROM Person.ContactType NewAliasct
Case 3: Use multiple elements in the Action column
If the Condition column contains one condition where the prefixes include the server, database, schema, and table, it looks as follows:
<LinkToSQLServer2022>.<AdventureWorks2022>.<Person>.<Address>
The conditional numbering of each element occurs from left to right. This means that the Action column will take these elements as <1>
, <2>
, <3>
, and <4>
, respectively.
To specify an action for an identifier in the Condition column, it is important to know its conditional number in the Condition column.
For example, if an alias should be generated based on the database name - AdventureWorks2022 - specify <2>
, which is a conditional number of the database, in the Action column:
Alias application example
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address aw
The aw alias was generated for AdventureWorks2022 using the standard alias generation algorithm - CamelCase for the identifier.
If it is necessary to generate an alias based on the schema name and table - Person and Address - specify <3>
and <4>
in the Action column:
Alias application example
In this case, the pa alias will be generated based on the capitalization of the Person and Address identifiers.
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address pa
So, if <*>.<*>.<*>.<*>
is specified in the Condition column, while <1><2><3><4>
will be set in the Action column, the aliases will be generated according to the common algorithm for all elements, for example:
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.BusinessEntityAddress ltsawpbea
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Sales.Customer ltsawsc
SELECT * FROM LinkToSQLServer2022.DWQueue.dbo.MessageQueue ltsddmq
Case 4: Exclude any characters from the identifier element
To exclude a specific part of the identifier from any element defined in the Condition column, specify the part to be excluded in the Action column within angle brackets < >
.
According to the configuration rules, the excluded part must be enclosed in square brackets [ ]
.
For example, exclude Add from the fourth identifier in this expression:
<LinkToSQLServer2022>.<AdventureWorks2022>.<Person>.<Address>
The fourth identifier is Address. So, the following must be written in the Action column:
Alias application example
Thus, the next character after Add in the fourth identifier will be r. The alias will be generated as follows:
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address r
Following the same rule, conditions can be added for other identifiers. For example, add the alias generation based on the second identifier - a database name - to the existing action <[Add]4>
while excluding Adv.
The Action column must contain the following:
The next character after Adv is e. However, according to alias generation rules, if an uppercase letter appears in the subsequent characters of the identifier, the alias will be generated based on that uppercase letter rather than the next character after the exclusion.
Thus, if the identifier is Adventureworks2022, the alias will be generated as follows based on the specified action:
SELECT * FROM LinkToSQLServer2022.Adventureworks2022.Person.Address er
However, since the identifier is AdventureWorks2022 (where an uppercase letter W appears immediately after the excluded part), the alias generated based on the specified action will be wr.
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address wr
Alias application example
So, in this case, the following alias will be generated for the <[Adv]2><[Add]4>
action:
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address wr
Case 5: Add a static set of characters to the action
You can append a static set of characters to the alias by including them directly in the Action column. To add the static value NewAlias to the end of the generated alias, use the following configuration:
Alias application example
SELECT * FROM LinkToSQLServer2022.AdventureWorks2022.Person.Address wrNewAlias
The generated alias consists of the auto-generated value (kl) followed by the static string NewAlias.
Note
You can place static characters before, after, or between placeholder elements (
<1>
,<2>
, etc.) to construct aliases in a consistent format.
Helpful materials
SQL Aliases: Improving Query Efficiency and Clarity
Find out what SQL aliases are and how to use them to enhance the efficiency of SQL scripts.
Want to find out more?
- Operations with aliases
- Open the Options dialog
- Automatic alias assignment
- Alias generation rules
- Custom alias mapping
- Alias refactoring
- Alias masks
- Mask prioritization
- Example 1: Condition with higher priority
- Example 2: Reversed priority
- Example: Prioritization in practice
- Alias assignment logic
- Rules for defining a condition in the Condition column
- Rules for defining an action in the Action column
- Helpful materials
- SQL Aliases: Improving Query Efficiency and Clarity