SQL Server View Related Interview Questions Answers

MS 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:


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:
Create View vEmployeeDetail
WITH ENCRYPTION
AS
Select EmpID, Sum(Amount) as Total From Emp Group by EmpID

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?
CREATE VIEW vProduct_WithSchemabinding
WITH SCHEMABINDING
AS
SELECT * FROM [Person].[Person]
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:
CREATE VIEW vProduct_WithSchemabinding
WITH SCHEMABINDING
AS
SELECT FirstName,LastName FROM [Person].[Person]

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



Comments

  1. Thank you so much for sharing this post.It was very helpful and helped me a lot in preparing for my interview.Keep sharing such awesome stuff.
    buy college essay

    ReplyDelete
  2. Question No. 11 --> WITH SCHEMABINDING can also be used in Stored Procedure

    ReplyDelete
  3. Your post give me lots of advise it is very useful for me. I want to introduce for you about the game- sims 4 cheats. in this game, you can create character and operate as sames as in the real world. Click link to participate games.

    ReplyDelete

Contact Form

Send