Restrictions with View Definition in SQL Server 2008

We do have a few restrictions with the view’s definition in SQL Server. We cannot use each and every T-SQL statement inside a view’s definition. And, of course, this is one of the most commonly asked questions in technical interviews as well.

Here goes a list of restrictions with view’s definition:

1. Every column, basically derived columns must have a column name.
2. The INTO keyword cannot be used inside the SELECT statement.
3. The COMPUTE BY clause cannot be used with the SELECT statement.
4. Normally, the ORDER BY clause cannot be used with the SELECT statement. However, if we wish to use ORDER BY clause, we must use TOP(n) with the SELECT statement.
5. A local or global temporary table (tables with # and ## sign) cannot be used inside a view.                                                                                                                                      
6. AFTER triggers cannot be associated with a view. However, we may associate INSTEAD OF trigger with a view.
7. Rules and Defaults cannot be associated with a view.
8. Full-Text index cannot be defined on a view.
9. A view can have maximum of 1,024 column in its SELECT list.
10. A view can have maximum of 32 levels of view nesting i.e. called nested views. 
11. All columns names must be explicitly specified when using WITH SCHEMABINDING option with a view’s definition. Even, when we are trying to pull all the columns out, we can’t use (*) sign. Instead, we need to write all column names explicitly.

This is all what I can compile that my memory could recall so far. If you have something to add/remove, which I missed out over here, please do put it in Leave a comment/Leave a Reply section below. I would appreciate your valuable contribution to the community.