Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms Flashcards Preview

70-461 > Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms > Flashcards

Flashcards in Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms Deck (41)
Loading flashcards...

What does DDL stand for?

DDL stands for "Data Definition Language". These statements are used to create and modify the structure of objects in the database, e.g. CREATE, ALTER, DROP.


Can you reference a synonym in a DDL statement such as ALTER?

No. You cannot reference a synonym in a DDL statement. You must reference the base object instead.


How do you drop a synonym?

You can drop a synonym using the DROP SYNONYM statement, e.g. DROP SYNONYM dbo.Categories.


Is there an ALTER SYNONYM statement?

No. There is no ALTER SYNONYM statement. You must drop it and re-create it to make any changes.


Does the object that a synonym points to need to actually exist?

No. The object does not need to actually exist, and SQL Server doesn't test it. This is because of the late binding behavior of synonyms. The synonym is actually checked for existence when you use it in a T-SQL statement.


How can synonyms simplify queries?

Synonyms can refer to objects in other databases in addition to objects referenced by linked servers. That makes it possible to drastically simplify queries in your database and potentially remove the need for 3/4-part references.

ReportsDB.Sales.Reports. to Sales.Reports.


What happens when you drop an object referenced by a synonym.

If you drop an object in the database, it will be dropped whether or not a synonym references it. Any synonyms referencing the object are effectively orphans; they fail to work when someone tries to use them.


What permission do you need to create a synonym?

To create a synonym, you must have the CREATE SYNONYM permission which inherits from the CONTROL SERVER permission. After you've created a synonym, you can grant other users permissions such as EXECUTE or SELECT to the synonym depending on the type of object the synonym stands for.


Does a synonym store T-SQL or any data?

No, a synonym is just a name. All that is stored with a synonym is the object it refers to.


Does a synonym expose metadata?

A synonym does not expose the metadata of the underlying table or view it points to. This could be seen as an advantage or disadvantage depending on the context.


What does the SCHEMA_NAME function do?

You can use the SCHEMA_NAME function to display the schema name without having to join to sys.schemas table, e.g.

SELECT SCHEMA_NAME(schema_id) as schemaname