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

Temporary table interview questions and answers

Temporary table interview questions and answers

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

1. What is Temporary table in sql server? Why we use temp table?

With temporary table you can store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables.

Use:

These tables can be created at run time and can do the all kinds of operations that one normal table can do.

More info.. http://www.codechef4u.com/post/2015/04/30/Temporary-table-in-sql-server

2. What is the Difference between a Local and a Global Temporary Table?

Scope:

Local:
 Local temporary tables are only available to the current connection to the database for the current user.
Global:
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Syntax:

Hash (#) sign is used while creating local temporary table.
 Example:
CREATE TABLE #ProductDetails(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))
Double Hash (##) sign is used while creating local temporary table.
Example:
CREATE TABLE ##ProductDtlGlbl(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))

 

3. What is local temp table?

Local temporary tables are only available to the current connection to the database for the current user. They are automatically deleted when the user disconnects from instances.  

Hash (#) sign is used while creating local temporary table.

More info..http://www.codechef4u.com/post/2015/04/30/Temporary-table-in-sql-server

4. What is global temp table?

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Double Hash (##) sign is used while creating local temporary table.

http://www.codechef4u.com/post/2015/04/30/Temporary-table-in-sql-server

5. How to insert stored procedure result into Temporary Table?

1.   If Schema known by creating #temp table Schema.
2.  For Unknown Schema – Table Created at Runtime, Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshaling data.

I prefer first solution over OPENROWSET due to overhead issue.  

More info… http://www.codechef4u.com/post/2015/06/28/How-to-insert-results-of-a-Stored-Procedure-into-a-Temporary-Table

6. Can you create foreign key constraints on temporary tables?

No, that is not allowed.

7. in which database, the temporary tables stored and get created?

Microsoft SQL Server Compact creates a temporary database (tempdb Database) for storing and creating temporary data such as temporary tables.

8. How Check if temp table exist and delete (drop) if it exists?

You can check if table exists or not in Temp DB, and if exits then use drop command for table deletion.

Example:

--check if table exits
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
--drop table
DROP TABLE #TempTable

 

9. Do you have to manually delete temporary tables?

Not required, temporary tables are automatically dropped, when the session that created the temporary tables is closed.

Note: If connection pooling is enabled or if you maintain a persistent connection then suggestion is drop temporary table explicitly.

10. What is the Difference between CTE (common table expression) and a Temporary Table?

Scope and Use:

Temporary Table:
 In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table.
CTE:
A Common Table Expression (CTE in SQL) is a temporary result set that, derived from query (T-SQL query in MS SQL Server) And defined within the execution scope of a Select, Insert, Update, Delete statement.

Syntax:

Temp Table:
Hash (#) and double (##) sign used while creating temporary table.
Example:
CREATE TABLE #ProductDetails(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))
 
CTE:
It is created by using t-sql WITH statement.
Syntax:
WITH
expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

 

Use in views and user defined functions:

1. You can’t use temp table in user defined function and views.
2. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Limitations of the sql server view

Limitations and restrictions of the sql server view

In this article I will explain limitations of using view in detail, following are some main restrictions on view I listed with detail.

Definition:

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

Following are some limitations with sql server views

1. SELF JOIN is not possible with Indexed View

2. ORDER BY now allowed with views.

Example:

Create View Orders_View
As
select OrderId,ProductId,Quantity,UnitPrice,Username FROM OrderDetail ORDER BY OrderId desc
go

Error :

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

3. Outer Join Not Allowed in Indexed Views.

4. With Index View union not allowed

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

Example:

Create View Orders_View
@OrderID  int
As
SELECT OrderId,ProductId,Quantity,UnitPrice,Username FROM OrderDetail where OrderId=@OrderID
Go

Error:

Must declare the scalar variable "@OrderID".

6. Cross Database Queries Not Allowed in Indexed View

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

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

Example:

CREATE TABLE ##ProductDetails(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))

 

insert into  ##ProductDetails values (1,'CodeChef4u e book','by NM Kendre')

View:

Create View Products_View
As
SELECT Id,Product FROM ##ProductDetails
Go


Error:

Views or functions are not allowed on temporary tables

 

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.