Tech Point Fundamentals

Friday, August 26, 2022

Home / SQL / SQL-Interview-Questions / sql-views / SQL Interview Questions and Answers - Part 14

SQL Interview Questions and Answers - Part 14

by on in , ,

SQL Interview Questions and Answers - Part 14

sql-interview-questions-answers

Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:




Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.




Introduction


This is the 14th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:





SQL Interview Questions and Answers - Part 14


Q121. What is View in SQL? 

A view is nothing but a SQL statement that is stored in the database with an associated unique name. Views in SQL are considered a virtual table as a view also contains rows and columns. Unless indexed, a view does not exist as a stored set of data values in a database.

Views can be created from a single base table, multiple base tables, or from another view itself. A view can contain all rows of a table or only select rows from a table.

The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced. Distributed queries can also be used to define views that use data from multiple heterogeneous sources. 



To create the view, the user needs to have the CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created. 

If the base table resides within a different schema, the REFERENCES permission on the table is required as a minimum. 

If the User creating the Index differs from the User who created the View, for the Index creation alone the ALTER permission on the View is required.

CREATE VIEW vwGetEmplyeeHavingMoreThan50KSalary
AS
SELECT ID, Name, Salary
FROM Employee
WHERE Salary > 5000;

You can execute the view as the table:

SELECT * FROM vwGetEmplyeeHavingMoreThan50KSalary;




View Fundamental Points:

A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch. A view can have a maximum of 1,024 columns only.

The SELECT Statement in a view definition cannot use INTO keyword, OPTION clause, COUN() function, Temp Table, Table Variable, and Order By clause unless the TOP clause is used.

When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules. 

If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.








If a view is not created with the SCHEMABINDING clause, run sp_refresh view when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

The Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are used to parse the view when the view is used. Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed.








Q122. What are the Advantages and Disadvantages of view?

Advantages of View:

  1. A view acts as a filter on the underlying tables referenced in the view. Views are also helpful in hiding data complexity. 
  2. It is used to focus, simplify, and customize the perception each user has of the database. A view in SQL Server helps to represent a subset of the data from a table.
  3. Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view. 
  4. Views can be used to provide a backward compatible interface to emulate a table that used to exist but whose schema has changed.
  5. Views can also be used when you copy data to and from SQL Server to improve performance and to partition data.
  6. A view can be used to structure data in a way that users or classes of users find natural or intuitive.
  7. View restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
  8. A view can summarize data from various tables which can be used to generate reports.



Disadvantages of View:

  1. A view can be created only in the current database.
  2. The view is slow compared to the physical table.
  3. A  view can have a maximum of 1,024 columns only.
  4. There is a lot of restriction on TSQL statements that can not be used in the view.
  5. We can not use Temp Tables and Table Variables in view.
  6. We can not use the COUNT() function in view. Only COUNT_BIG() is allowed.








Q123. What are the different types of views in SQL?

There are different types of views based on the implementation. 

Simple View: If the view is created only based on a single underlying base table it is known as a simple view. A simple view is always updatable.

Complex View: If the view is created based on multiple underlying base tables it is known as a complex view. A complex view is not always updatable. There are some conditions for updating the records in the complex view. 



Indexed Views: A view is an indexed view if it has a unique clustered index on it.  Indexed views can dramatically improve the performance of some types of queries. Please watch the unique index video here for more details.

Partitioned Views: A partitioned view is a view defined by a UNION ALL of the member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

System View: System views expose catalog metadata. You can use system views to return information about the instance of SQL Server or the objects defined in the instance. For example sys.databases catalog view returns information about the user-defined databases available in the instance.




Q124. What is Schema Bounded View? What are its advantages?

The SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. 

The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. 



When you use SCHEMABINDING, the SELECT statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. 

The main key point about schema bounded view is that all referenced objects must be in the same database.

CREATE VIEW vwGetEmplyeeList
WITH SCHEMABINDING
AS
SELECT ID, Name, RoleName, Salary
FROM [dbo].[Employee];



Advantages of SCHEMABINDING View:

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.  Otherwise, the Database Engine raises an error. 

Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fails when these statements affect the view definition.

Schema binding your view will ensure that the underlying tables can’t be changed in a way that would affect the view. Without schema binding, the underlying tables or other objects could be modified or even deleted.




Q125. What is Indexed View or Materialized View in SQL?

We can create an index on a view as well like a table. But you must have to create a unique clustered index on it first. After the unique clustered index has been created, you can create more nonclustered indexes. 

CREATE UNIQUE CLUSTERED INDEX IXVW_EmpId ON vwGetEmplyeeList;

An indexed view is a view that has been materialized. A materialized view means the view definition has been computed and the resulting data stored just like a table. 

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. 








Indexes can only be created on views that have the same owner as the referenced table or tables. This is also called an intact ownership chain between the view and the table(s). 

Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema. 

The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. 

Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions can participate in deterministic expressions. 

Evaluating the same expression can produce different results in the Database Engine when different SET options are active when the query is executed.  To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. 








Materialized View Fundamental Points:

  1. The view must be created by using the WITH SCHEMABINDING option.
  2. The view must reference only base tables that are in the same database as the view. The view cannot reference other views.
  3. The user that executes CREATE INDEX must be the owner of the view. When you create the index, the IGNORE_DUP_KEY index option must be set to OFF (the default setting).
  4. All the tables must be referenced by two-part names identifier names in the view definition.
  5. All the UDFs referenced in the view must be created by using the WITH SCHEMABINDING option only. And they must be accessed by using a two-part identifier name only.
  6. If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.







  7. If the GROUP BY clause is present, the view definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. 
  8. The SELECT statement in the view definition must not contain some TSQL commands and objects like: COUNT, TOP, DT, DISTINCT, SUM, AVG, MIN, MAX, Table Variable, TVF, PIVOT, UNION, EXCEPT, INTERSECT, ORDER BY, OUTER JOIN, CTE, OVER, CUBE, ROLLUP, etc.
  9. You can’t use EXISTS, NOT EXISTS, OUTER JOIN, COUNT(*), MIN, MAX, subqueries, table hints, TOP, and UNION in the definition of your indexed view.
  10. You can’t use the text, ntext, image, and XML, data types in your indexed views.  
  11. A float data type can be used in the indexed view but can’t be used in the clustered index. 
  12. Any Non-deterministic columns can’t be used in the indexed view definition. These are the columns that don’t return the same value each time, like the GETDATE() function.
  13. Some SET Options like ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER  options should be ON and the NUMERIC_ROUNDABORT  option should be OFF before creating an indexed view.








Q126. What is a Partitioned View? What is its use in SQL?

A partitioned view is a view defined by a UNION ALL operator of the member tables which have the same structure but are stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers. 

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.






When it is needed, the Database Engine distinguishes local partitioned views as the views in which all participating tables and the view are on the same instance of SQL Server and distributed partitioned views as the views in which at least one of the tables in the view resides on a different or remote server.

In designing a partitioning scheme, it must be clear what data belongs to each partition. 

CREATE VIEW VotersList  
AS  
SELECT UserName, Age, Gender FROM [ST01].[User]
UNION ALL  
SELECT UserName, Age, Gender FROM [ST02].[User]
UNION ALL   
SELECT UserName, Age, Gender FROM [ST03].[User]



Updatable Partitioned View:

If the view is a partitioned view, the view is updatable, subject to certain restrictions.

The INSERT statement supplies values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow for null values. For those member table columns that have DEFAULT definitions, the statements cannot explicitly use the keyword DEFAULT.

The value being inserted into the partitioning column satisfies at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.

UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.



Columns in the view that are identity columns in one or more of the member tables cannot be modified by using an INSERT or UPDATE statement.

If one of the member tables contains a timestamp column, the data cannot be modified by using an INSERT or UPDATE statement.

If one of the member tables contains a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULT or ON DELETE CASCADE/SET NULL/SET DEFAULT constraint, the view cannot be modified.

INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

Bulk importing data into a partitioned view is unsupported by BCP or the BULK INSERT and INSERT INTO... SELECT * FROM OPENROWSET(BULK...) statements. However, you can insert multiple rows into a partitioned view by using the INSERT statement.









Q127. Can you UPDATE or DELETE the records from View? What is the updatable view?

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

  1. Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  2. The columns being modified in the view must directly reference the underlying data in the table columns. 
  3. The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
  4. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
  5. The SELECT clause of the view should not contain DISTINCT, Aggregate Functions, SET functions, and operators, ORDER BY clause.
  6. The view should not contain subqueries and multiple base tables.
  7. All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.






UPDATE vwGetEmplyeeHavingMoreThan50KSalary 
SET Salary = 99000
WHERE ID = 6;

If the above restrictions prevent you from modifying data directly through a view, you can use INSTEAD OF Triggers or Partitioned Views as well. You can also insert and delete the records from view as well.



Q128. Can you add the Primary Key Constraint in the View? 

No, Primary Key cannot be added to the view but an index can be created on a view. Please watch the complete video here.







Q129. What is the difference between View and Table?

A view is not an actual table as it does not exist in any physical location. A view is just a SQL statement stored in a database with a specific name. On the other hand, a table is a physical database object in which data is physically recorded.

However, in SQL Server, we cannot create a table and a view with the same name because the table and view both share the same namespace. And if we try to do so, then SQL Server will return an error indication object already exists.

In SQL Server, we cannot create a Foreign Key relationship between a table and a view. The foreign key relationship can only be created between two or more physical tables in SQL. 



A view in SQL is a virtual entity, and it renders the data from the table every time we query it. Due to this reason, a view produces slow results as compared to a table in SQL. On the other hand, a table in SQL is a physical entity of a database, and data is actually stored in it. So it is faster compared to view.

Like a physical table, we can create an index on view and we can also perform a JOIN between a table and view.

You can see all the tables using the INFORMATION_SCHEMA.TABLES system view:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';

Similarly, you can see all the views as well:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='VIEW'



Q130. What is the difference between View and Temp Table?

The temporary tables are also an alternative to presenting end-users with a subset of data from base tables. Like a table, a view consists of a set of named columns and rows of data.

Temp tables are stored in temp DB and exist only as long as the database session is active. We need to use the INSERT statement at the start of each session to add some data to the temp table. On the other hand, a view in SQL Server is stored as a SQL query rather than with data.

A temp table has almost all the features like any other physical table, but a view has only limited features. Please watch the temp table video here for more details.






To Be Continued Part-15...


Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK  for regular updates.

    

You May Also Like:
By at
Labels: , ,

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.

Subscribe to: Post Comments (Atom)