Synonyms

Question 1

What is a synonym?

Answer 1

It’s basically an alias for an object name. Much like you would alias a column name.

SQL Server 70-461 09-02

Question 2

Syntax to create a synonym

Answer 2

USE TSQL2012
GO
CREATE SYNONYM dbo.Categories FOR Production.Categories;
GO

dbo.Categories synonym name
Production.Categories object you are creating a synonym for

SQL Server 70-461 09-02

Question 3

Does the object the synonym will be an alias for need to exist when the synonym is created?

Answer 3

• No. Because of the late binding of synonyms
• When you actually use the synonym in a tsql statement, SQL Server will check for the objects existence.

SQL Server 70-461 09-02

Question 4

What rules must be complied with when naming a synonym?

Answer 4

Rules for tsql identifiers

SQL Server 70-461 09-02

Question 5

Do synonyms store any data or code?

Answer 5

No

SQL Server 70-461 09-02

Question 6

If you don’t specify a schema when you create the synonym, what schema will it be assigned to?

Answer 6

The default schema associated with the user name.

SQL Server 70-461 09-02

Question 7

What 5 types of objects can synonyms be used for?

Answer 7

1. Tables, including temporary tables
2. Views
3. User-defined functions (Scalar, table-valued, inline)
4. Stored procs (T-SQL, extended stored procedures and replication filter procedures)
5. CLR Assemblies (stored procedures; table-valued, scalar, and aggregate functions)

SQL Server 70-461 09-02

Question 8

Can synonyms refer to synonyms?

Answer 8

No! They can only refer to database objects.

SQL Server 70-461 09-02

Question 9

Where can you use synonyms?

Answer 9

In TSQL statements that refer to the types of objects that synonyms stand for.

EXECUTE
SELECT
INSERT
UPDATE
DELETE

SQL Server 70-461 09-02

Question 10

What statement can you not reference a synonym in?

Answer 10

ALTER. You must reference the base object instead.

SQL Server 70-461 09-02

Question 11

How do you ALTER a synonym itself?

Answer 11

• You can’t. There is no ALTER SYNONYM statement.
• You have to drop it and recreate it.

SQL Server 70-461 09-02

Question 12

Statement to drop synonym

Answer 12

DROP SYNONYM synonym_name

SQL Server 70-461 09-02

Question 13

Does a synonym have to refer to an object in the same database it is created in?

Answer 13

No. It can refer to an object in another database, in addition to objects referenced by linked servers.

SQL Server 70-461 09-02

Question 14

Can an object be dropped even if there is a synonym that refers to it?

Answer 14

• Yes, unfortunately
• There is nothing similar to SCHEMABINDING for synonyms

SQL Server 70-461 09-02

Question 15

What is one advantage of being able to create a synonym for an object that doesn’t yet exist?

Answer 15

• You can use a single synonym for many different objects. Just recreate the synonym for each object as you need it.
• Or you could create the same synonym in multiple databases to reference the same object.

SQL Server 70-461 09-02

Question 16

What permission must you have to create a synonym?

Answer 16

CREATE SYNONYM

SQL Server 70-461 09-02

Question 17

If you have CREATE SYNONYM permission, can you grant other users permission to the synonym?

Answer 17

Yes. You could grant permissions such as EXECUTE or SELECT depending on the type of object the synonym stands for.

SQL Server 70-461 09-02