Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Views interview questions and answers

interview questions and answers on sql server Views

In this article I will share some frequently asked views interview questions and answers in SQL server.  

1. What is a View?

View is a virtual table based on the result-set of an SQL statement and that is Stored in the database with some name.

Example:

CREATE VIEW StudentProducts AS
SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName='Books')
GO  

2. What are Different Types of views?

Basically there are two types of views

1. System views
2. User defined views.

You can create following views:

a. Standard Views
b. Indexed Views
c. Partitioned Views

3. Explain System views?

Microsoft SQL Server provides the ready collections of system views that expose metadata.
System Views that already exist in the Master database of Sql Server.
Example: Catalog views

4. Explain User defined views?

As name suggests these view are defined by users.

Example:

CREATE VIEW StudentProducts AS
SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categorieswhere CategoryName='Books')
GO  

 5. What is indexed view?

Creating a unique clustered index on a view is an indexed view.
If the view is indexed then any queries that can be answered using the index only will never need to refer to the underlying tables. This can lead to an enormous improvement in performance.

More info…http://www.codechef4u.com/post/2015/09/05/indexed-views

6. What is Catalog Views?

Catalog views retrieves information used by the SQL Server. They are the most general interface to the catalog metadata and provide efficient way to obtain, transform and present custom forms of this information.

Note: Catalog view do not contain information about replication, backup or maintenance plans

7. What are Various Limitations or Restriction of the Views?

Following are some limitations with sql server views

1. SELF JOIN is not possible with Indexed View

2. ORDER BY now allowed with views.

3. Outer Join Not Allowed in Indexed Views.

4. Count(*) not allowed in views , you can use COUNT_BIG(*) instead.

5. With Index View union not allowed

6. You can't create a view with a parameter or parameterized view.

7. Cross Database Queries Not Allowed in Indexed View

8. You can’t use keyword View Definition with Index View.

9. Views are not based on temporary tables, when you try you will get error message like

Views or functions are not allowed on temporary tables


8. What are partitioned views and distributed partitioned views?

A partitioned view:

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table

Local partitioned view:

In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server.

Distributed partitioned view:

In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server.

Distributed partitioned views can be used to implement a federation of database servers. A federation is a group of servers administered independently, but which cooperate to share the processing load of a system.

9. Describe the functionalities that views support? Or Views features

View Builder allows you to build views visually. It allows you to create and edit views without knowledge of SQL

Simple to use:

A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.

Table joins:

Views can subset data in a table
They can join multiple tables into one virtual table

Security:

Views can provide more security and decrease complexity.

Performance:

Better performance compared with complex queries.
They save space because only their definition is stored.

Abstraction:

They can also be used to create abstraction
Materialized views are commonly used in data warehousing. They represent a snapshot of the data from remote sources.

Data Integrity

If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.


10. Explain Indexed views and partitioned view with their syntax?

Tasks required to create partitioned view:

a. Adding linked server definitions on each member server that contains the connection information required to run distributed queries on the other member servers.
b. Setting the lazy schema validation option, by using sp_serveroption, for each linked server definition that is used in distributed partitioned views.
c. Creating a distributed partitioned view on each member server.

Example:

CREATE VIEW ProductsData AS
   SELECT * FROM CompanyDatabase.TableOwner.Products_33
UNION ALL
   SELECT * FROM Server2.CompanyDatabase.TableOwner.Products_66
UNION ALL
   SELECT * FROM Server3.CompanyDatabase.TableOwner.Products_99


Tasks required to create Indexed view:

The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

1. Verify the SET options are correct for all existing tables that will be referenced in the view.
2. Verify that the SET options for the session are set correctly before creating any new tables and the view.
3. Verify that the view definition is deterministic.
4. Create the view by using the WITH SCHEMABINDING option.
5. Create the unique clustered index on the view.

Joins interview questions and answers

In this article I will share some frequently asked table join interview questions and answers in SQL server.

1. What is table join?

A SQL join clause combines records from two or more tables in a relational database.

Basically there are three types of table joins

1. Inner join
2. Outer join
3. Cross join

2. What are Different Types of table joins?

Following different types of table joins used in sql server
a. Inner join
b. Outer join
Outer join categorized in three types
1. Left outer join
2. Right outer join
3. Full outer join
c. Cross join
d. Self join

3. What is inner join?

This join returns common or matching records/rows from both the database tables (left table and right table).This s the most used join in the SQL. In the join condition, you can also use other operators like <,>,<>.

Example:

SELECT

ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName

AS [Product Category],ImagePath

AS [Image]
 
FROM Products

P
INNER JOIN Categories C ON

P.CategoryID=C.CategoryID

More info.. http://www.codechef4u.com/post/2015/04/10/inner-join

4. What is outer join?

Outer join return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.

 All rows from both tables are returned in a full outer join

5. What is cross join?

A cross joins that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this.

More info.. http://www.codechef4u.com/post/2015/04/10/cross-join

6. What is self join?

A self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table.

More info.. http://www.codechef4u.com/post/2015/04/10/self-join

7. Explain left outer join and right outer join?

 The LEFT JOIN keyword returns all rows from the left table (Products), with the matching rows in the right table (Categories). The result is NULL in the right side when there is no match.

More info.. http://www.codechef4u.com/post/2015/04/10/left-outer-join-or-left-join

8. What is full outer join?

Full outer join returns all the rows from both tables whether it has been matched or not.The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SELECT ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P
FULL OUTER JOIN Categories C ON P.CategoryID=C.CategoryID

More info.. http://www.codechef4u.com/post/2015/04/10/full-outer-join

9. Equi join and non equi join is possible with sql server?

Yes that is supported in sql server, check more info http://www.codechef4u.com/post/2015/04/10/equi-join-and-natural-join

10. Inner vs outer joins?

Inner join:

This join returns common or matching records/rows from both the database tables (left table and right table).

Inner joins return rows only when there is at least one row from both tables that matches the join condition.

Inner joins eliminate the rows that do not match with a row from the other table

Outer join:

Outer join return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.

 All rows from both tables are returned in a full outer join.

11. Natural join is possible with sql server?

Not supported by sql server,check more info.. http://www.codechef4u.com/post/2015/04/10/equi-join-and-natural-join


12. How to use JOIN in SQL with one-to-many relationship and one to one relationship?

In One-to-many relationship you can use foreign key and one to one relationship is quite simple ,here you can use two table’s primary key for join.

For more details use following link that explained question with good examples…

https://www.deskbright.com/sql/sql-joins-interview-questions/

Difference between delete and truncate

In SQL Server there are ways you can delete rows from a table.   You can use the TRUNCATE and DELETE command.

In this article I will share key difference between DML delete statement and DDL Truncate command.

Delete:

The DELETE statement is used to delete records in a table, using where clause you can delete specific rows.

Truncate:

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions

Difference:

Difference

Delete

Truncate

Condition

It deletes specified data using where condition.

Removes all records, Cannot use Where Condition.

Rollback

Rollback is possible.

Rollback is not possible.

DDL or DML

DELETE is a DML Command.

TRUNCATE is a DDL command.

Lock

DELETE statement is executed using a row lock; each row in the table is locked for deletion.

 

TRUNCATE TABLE always locks the table and page but not each row.

Performance

Slower than truncate because, it keeps logs.

Faster in performance wise, because it doesn't keep any logs.

Trigger

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

 

Delete activates a trigger because the operation is logged individually.