Microsoft® SQL Server® 2012 Bible

289

Chapter 11: Projecting Data Through Views

11

aliases, and build data using joins and subqueries, synonyms can assign only alternative

names to tables, views, and stored procedures.

Use synonyms primarily to simplify complex object names, particularly with lengthy schema

names. A synonym can change HumanResources.vEmployeeDepartmentHistory into

EmpHist. Which would you rather type 100 times? The following script i llustrates how to

create the EmpHist synonym:

CREATE SYNONYM EmpHist

FOR HumanResources.vEmployeeDepartmentHistory;

Synonyms are part of the SQL standard and are used frequently by Oracle DBAs. Oracle

includes both private and public synonyms. SQL Server synonyms are only public. Even

though they were introduced to SQL Server with version 2005, I’ve seen little acceptance or

use of synonyms in the SQL community.

Schemas enhance security and help prevent SQL injection attacks.

The hacker needs to guess the schema name as well as the table name. Little Bobby Tables

(a standard DBA joke: http://xkcd.com/327/ ) would need to know myschema

.students. Giving the table myschema.students an easy-to-guess synonym would

defeat the purpose of using the schema as a mechanism to prevent SQL injection.

You can manage synonyms using Object Explorer, or CREATE and DROP DDL commands.

Summary

Views are nothing more than stored T-SQL SELECT queries. There’s no magic in a view. You

can save any valid SELECT statement as a view, including subqueries, complex joins, and

aggregate functions.

Views are great for simplifying a complex schema and presenting a more useful picture of

the data for power users writing ad hoc queries and reports. Views can simplify complex

aggregate queries and hide nasty joins. Any well-planned abstraction layer should include

views. Be careful not to push the view too far. Don’t expect to sort data in a view, and

don’t make views the pillar of the front-end application or website. However, for those who

detest views, a view is infi nitely better than an ad hoc SQL statement that directly hits a

table without any abstraction layer.

The previous chapters have discussed retrieving data using the powerful SELECT state-

ment. Views store the SELECT statement for ad hoc queries. The next chapter continues the

discussion of SELECT, extending its power by adding data modifi cation verbs.

c11.indd 289c11.indd 289 7/30/2012 4:41:13 PM7/30/2012 4:41:13 PM

http://www.it-ebooks.info