Skip to main content

Interview Questions and Answers on SQL Server View


    What is a View?

       Views are virtual tables that hold data from one or more tables. It is a query stored in the database as object. A view does not contain any data itself, it is a set of queries that are applied to one or more tables that are stored within the database as an object 

What is the use of view?

  1. It allows you to reuse code without  having to write the same complex SQL code over and over.
  2. Views are used for security purposes in databases. Views restrict the user from viewing certain columns and rows. In other words, using a view we can apply the restriction on accessing specific rows and columns for a specific user 

Can we use ORDER BY clause inside a view? 
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is specified.

 Can we update a view?
     If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.  

You can't modify data in views that use GROUP BY or DISTINCT statements. 

 All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
      
The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view.
    

 Can anyone update a table which is used in a view? How you can prevent that?

     Schema binding binds your views to the dependent physical columns of the accessed tables specified in the contents of the view, i.e. if a view say vw_CustomerBill is schema bind no one will be able to alter the base table dbo.Customer table unless they drop the table.

 Note: Why would we need Schemabinding?

It prevents your views from being orphaned. Just think that someone drops/alters the table dbo.Customer without paying any heed to our view. Now that would leave our view nowhere. Hence schema bind it, this will prevent any such accidents from happening.Also to be able to create an index on the view you need it essentially schema bound.

ALTER VIEW vw_billing
WITH SCHEMABINDING
AS
SELECT C.FName
,C.LNme
,P.ProductDesc
,B.DateOfBooking
,P.Price
,B.QTY
,(B.QTY*P.Price) AS TotalAmountPayable
FROM dbo.BookMaterials B
INNER JOIN dbo.PRODUCT P
ON B.ProductID=P.ProductID
INNER JOIN dbo.Customer C
ON B.CustID=C.CustID;


Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server- CHAR,VARCHAR, NVARCHAR

SQL Server String Data Types CHAR CHAR is a fixed data type. So when a column is declared as CHAR, irrespective of  the actual characters stored in that variable/column, it will use all the declared space. In the above example you can see that even though the actual length of the variable is 6, It occupies 30 bytes of space.This is because when the variable @name is declared as CHAR(30), 30 bytes of space got reserved for the variable.  Also, you can observe that CHAR type column uses 1 byte of space to store each character. Summary: 1. It is a fixed data type 2. Occupies 1 byte of space for each character 3. It is used to store non-unicode character. VARCHAR VARCHAR is a variable length data type. So when a variable or column is declared as VARCHAR, irrespective of declared space, it will use only space require to store all characters. In the above example you can see that even though the declare...
Post a Comment
Read more

SQL Server Backups

1. Full Backup: This is the most common and simplest back up method, It contains all the data in a specific database and enough log that is required to recover the database. It is the base of both differential back up and transaction log back up. 2. Differential Backup: It depend on the latest full backup of data. It contains all the changes that have been made since the last full backup of dat a.  3. Transaction Log Backup: The transaction log is a record of  all the transactions that have been performed against the database since the last transaction log backup. That means it includes all log records that were not backed up in the last transaction log. With transaction log backup you can recover the database to a specific point of time. This SQL Server backup type is possible only with full or bulk-logged recovery model. 4. File and File group Backup: This backup type allows you to backup one or more database file or file groups.  5.Parti...
Post a Comment
Read more