Chapter 11: Views: Limiting Access to Data

View definition with an aggregate or built-in function

A view definition that includes an aggregate or built-in function must include column names in the create clause. For example:

create view categories1 (category, average_price) 
as select type, avg(price) 
from titles 
group by type

If you create a view for security reasons, be careful when using aggregate functions and the group by clause. The Transact-SQL extension that does not restrict the columns you can include in the select with group by may also cause the view to return more information than required. For example:

create view categories2 (category, average_price) 
as select type, avg(price) 
from titles 
where type = "business"

In the above case, you may have wanted the view to restrict its results to “business” categories, but the results have information about other categories. For more information about group by and this group by Transact-SQL extension, see “Organizing query results into groups: the group by clause”.

View this book as PDF