Shulou(Shulou.com)06/01 Report--

SQL Prompt

Is a SQL intelligent prompt function of SQL Server and VS plug-ins. SQL Prompt can automatically retrieve the database according to the object name, syntax and code fragments written by the user, and intelligently provide users with the only appropriate code choice. Automatic script settings provide users with simple code readability-this is especially useful if developers are using scripts that are not familiar with. SQL Prompt is immediately available and can greatly increase productivity.

Table alias

The main purpose of table aliases (except in cases where aliases are required) is to make SQL queries easier to read and understand. No, it's not my typing error. Aliases do not make typing SQL queries easier and faster. This is especially true if you are using SQL Prompt, as it does all typing for you.

If you enable its assign aliases option, SQL Prompt automatically suggests an alias of one or two characters when you enter a SQL statement, simply because it must provide something but no meaning. This is not because short, meaningless names are better. This only means that the database developer should improve the default alias to provide meaning.

Where can I use a table alias?

In standard SQL, aliases are used only in SELECT statements. It can be said that you can only use them in statements that have FROM clauses, because the syntax of SQL Server allows it to use the From clause in UPDATE and DELETE statements. When you use a table alias, you only provide a specific name for the instance referenced by the table, not the actual table. Only this reference is an alias.

Do I always need to qualify the list?

In SQL, if the column name itself is ambiguous, you only need to qualify the column name with a reference to the table source, because the column name itself may come from one or more table sources in the FROM clause. In SQL Server, using qualified column names makes the parser easier and the query easier to read anyway.

However, without the JOIN clause, adding any kind of table qualifier (with or without an alias) to the column name is completely redundant and should not be used.

Do I always need to use a table alias?

For a column to qualify for its table, it is usually not necessary to use a table alias (also known as an association name in ANSI SQL). You can use only the name of the table source that provides the column.

Alias-valued functions must be used only if you reference a table source that does not have a name (such as a derived table or rowset function) or use a named table source (such as a table, view, or table -), more than once in the same query. In general, why provide another name if the table source already has an appropriate name? Typically, you do this just to make the intention of the query clearer. If not, then it will become an obstacle.

When do you need an alias?

One of the reasons for using aliases is that the name of the base table contains illegal characters. You may look angry and say that you will never succumb to this, but on the other hand, using "@" at the beginning of the table name qualifier is illegal, so table variables are a problem. For example, if you do not provide an alias for a table variable, you must provide a square bracket delimiter to reference it by its full name. Of course, if the table does contain illegal characters, the table alias can save you from annoying square brackets.

You must also qualify a column with a table alias, rather than relying on the table name (if the table is in a table that is used twice in JOIN). For example:

SELECT Object_Schema_Name (TablesEtc.object_id) +'.' + TablesEtc.name AS TableName, Constraints.name +'('+ RTrim (Constraints.type) +') 'COLLATE DATABASE_DEFAULT AS [Constraint] FROM sys.objects AS TablesEtc INNER JOIN sys.objects AS Constraints ON Constraints.parent_object_id = TablesEtc.object_id WHERE TablesEtc.is_ms_shipped=0 ORDER BY TableName

Sys.objects is combined with sys.objects, so it won't work properly without using aliases. By choosing meaningful names for them, we can also avoid mistakes.

If you want to add a table source (such as a query), you need to provide a name. There is no default name. Here, we will create two table sources from multiple rows of values and then join them.

SELECT numbers.number, Coalesce (Names.NameForFrequency,'frequently') AS HowManyTimes FROM (VALUES (0), (1), (2), (3), (4), (5)) AS numbers (number) LEFT OUTER join (VALUES), (1) AS Names (number,NameForFrequency) ON numbers.number=Names.number

In the first example, we used the AS keyword to indicate that we were introducing aliases. It is optional, so you can omit it, but this is a bad practice because it makes your intentions less clear.

How does SQL Prompt handle aliases

When the assign aliases option (SQL Prompt > options > inserted Code > aliases) is enabled, SQL Prompt automatically assigns aliases to the table source. However, it cannot guess the story behind SQL, so it cannot provide you with meaningful aliases, only abbreviations. Unless you provide the current naming convention as a custom alias, it generates aliases using the first letter of the table or view name whenever possible. If the initial character is already in use, it will use two characters.

If you are unfortunately stuck in the "tibbling" convention (all tables have tbl prefixes), you can tell SQL Prompt to ignore them (by adding them to the "Prefixes to ignore" list). If the name is underlined or hyphen, or consists of two CamelCase words, such as CustomerAccounts, SQL Prompt takes it into account when generating a two-letter alias.

Otherwise, when you use SQL Prompt in a self-join, SQL Prompt creates another alias for the same table. This is the same code we saw earlier to get the name of the table and its constraints, but with the alias suggestion for SQL Prompt. It eliminates ambiguity, but when you read this version, will you be so confident that you can make sure that the ON clause is correct and even understand what you intend to provide and how?

SELECT Object_Schema_Name (O.object_id) +'. + O.name AS TableName, O2.name +'('+ RTrim (O2.type) +') 'COLLATE DATABASE_DEFAULT AS [Constraint] FROM sys.objects AS O INNER JOIN sys.objects AS O2 ON O2.parent_object_id = O.object_id WHERE O.is_ms_shipped = 0 ORDER BY TableName

When SQL Prompt assigns an alias, it remembers the alias in the current query Editor window, as you can see from the suggestions given to you in the suggestion box when you process the query and add clauses to it.

If existing code uses joins and does not use table aliases, SQL Prompt underlines the table reference with a green curve, indicating a violation of its ST010 code analysis style rules. Personally, I prefer to disable this rule unless I follow a style guide that requires each SELECT involving multiple table sources to use an alias as a qualifier. More directly, Prompt also highlights unqualified column names that violate MI003.

If you add a table alias and then run the Format SQL command, it automatically uses the alias to qualify the relevant column names (if the qualify object name operation is enabled).

Add a custom alias to SQL Prompt

You can specify a user-defined alias for a table or view based on an established naming convention. Apart from other prompt options, SQL Prompt currently does not support the import or export of custom aliases, so you must type one at a time.

To add a user-defined alias, in the options window, at the head of the aliases window. Under the Custom aliases heading, click New, enter a name for the object and its alias, and then click Save.

Even if you can provide meaningful custom aliases, they may be added long enough to prevent them from fitting into the suggested ON expressions in the suggestion box. In the screenshot below, I used a stupid alias TheTablesAsListedInTheSystemViews, just to illustrate what I mean. It overflowed the suggestion box.

In this case, a simpler solution is to use search and replace to change the default values provided by Prompt after the query or routine completes but before saving.

Summary

Table aliases are sometimes essential and are often useful to help clarify the purpose of more complex SQL queries. However, they are not always necessary, and without JOIN, there is no benefit in using them in the FROM clause.

Using SQL Prompt, I chose to change the suggested short alias to provide more explanatory meaning. It doesn't hold a grudge, but gives me advice based on the custom alias I choose. This will save a lot of monotonous typing, for which I am deeply grateful.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.