2.6. Synonyms

In the last section, we discussed one way of referring to a tablethrough the use of table aliases. Table aliases are not permanent, in the sense that they do not exist after the query has been executed. In this section, we show you another way of referring to a table--synonyms . Synonyms are more permanent; they are available for use until they are deleted. In this section, we show you how to create, use, and delete synonyms.

SQL Server allows you to create synonyms for your tables. Synonyms are usually shorter names that can be used in place of the table name. If a change is made in the original table or its data, this change will be reflected when the synonym is used. And, if a change is made in the data of the table using a synonym, this change will be reflected in the original table. But, you cannot alter the table's definition using the synonym. Alter table commands (covered in Chapter 3) can be used only on the actual tables.

The general syntax to create a synonym is:

CREATE SYNONYM synonym_name
FOR Table_name

For example, to create a synonym for the Student table called s1, type:

CREATE SYNONYM s1
FOR Student

To view the synonym that you just created, from the Object Explorer, expand Student_course database and then Synonyms (as shown in Figure 2-2), and you will see the synonym, s1.

Figure 2-2. The synonym

You can now type:

SELECT *
FROM s1

And you will get the same output as if you typed:

SELECT *
FROM Student

A synonym will exist until you delete it. The general syntax to delete a synonym is:

DROP SYNONYM synonym_name

So, if you want to delete the synonym s1, type:

DROP SYNONYM s1

You can also delete the synonym by right-clickingon the synonym and selecting Delete.

If you forget which synonym has been created for which table, right-clickon the synonym and select Properties.