SQL Server View Related Interview Questions Answers

1). What do you understand by View in SQL Server?
Ans: A view is a virtual table whose contents are defined by a query. or a view is a stored SELECT statement that works like a virtual table.

2). What are the types of view?
Ans:

• Indexed Views
• Partitioned Views
• System Views
• Click here for more detail

3). How many column a view can contain?
Ans: 1024

4). The tables that makes up a view are called as?
Ans: Base tables

5). Can you create a view by using temporary table?
Ans: No

6). Can you create a view by using another view(nesting views)?
Ans: Yes! you can build view on other views and nest them up to 32 levels, Basing a view on another view is known as nesting views.

7). What are the limitations of a View?

8). How you will encrypt a view, so that people can utilize view to run reports, but can't see the underlying code?
Ans: We can encrypt our view by using WITH ENCRYPTION keyword
Ex:

9). If you are going to change or drop a table, but you don't know how many views/proc etc are depend on this particular table, then how you will you find dependencies?
Ans: To check dependencies there is a system-supplied stored procedure, sp_depends, which will list all dependent objects for the object name you pass in.

10). What is the purpose of the WITH SCHEMABINDING clause and where can it be used?
Ans: WITH SCHEMABINDING can be used in Views and T-SQL Functions.
Objects that are schema bound can have their definition changed, but objects that are referenced by schema bound objects cannot have their definition changed.
Schema binding effectively states that the meta-data which is created at object creation time can then be relied upon to be accurate at all times, and use of sp_refreshsqlmodule is not necessary. Schema binding can also significantly increase the performance of user defined functions in SQL Server 2005 and above. However, caution should be applied, as this is definitely not always the case.

11). Can we use WITH SCHEMABINDING in Stored Procedures?
Ans: WITH SCHEMABINDING can't be used in Stored Procedures.

12). Will below script correct or not? If not what is wrong with it?
Ans: If we are using WITH SCHEMABINDING then we can't use "Select *";
This will throw "Syntax '*' is not allowed in schema-bound objects." error

Correct:

13). Is view store data physically apart from table or not?
Ans: A view is just a macro, it has no persistent storage.The underlying table data is stored in the MDF file. But its not always true, Creating a clustered index on the view materializes its data on disk, giving the view a physical dimension, as opposed to its normal virtual role.

14). What are the purpose of creating view?
Ans: View is used for the following purposes:
a) Security
b) Faster Response
c) Complex Query solve