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

SQL Server Exception Handling Interview Question and Answers

SQL Server Error Handling Interview Question and Answers

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

1. How will you Handle Error in SQL SERVER ?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql.
Before SQL server 2012 release tray…catch block used to handle error with RAISERROR.
SQL Server 2012 introduced the THROW statement. You now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.

More.. http://codechef4u.com/post/2015/04/13/throw-t-sql

http://codechef4u.com/post/2015/04/06/raiserror

2. What is RAISEERROR?

Used to return error message back to application, system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

 More detail… http://codechef4u.com/post/2015/04/06/raiserror

3. Explain Try...Catch with sql server?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql. Statement enclosed in catch block.

Syntax:

BEGIN TRY
     { T-Sql_ statement | statement_block }
END TRY
BEGIN CATCH
     [ { T-Sql _statement | statement_block } ]
END CATCH
[ ; ]

More info… http://codechef4u.com/post/2015/04/13/try-catch

4. Explain THROW statement in sql server 2008?

THROW statement raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server (SQL Server 2012+)

You don’t have to specify any parameters and the results are more accurate. You simply include the statement as is in the CATCH block.

 more info...http://www.codechef4u.com/post/2015/04/13/throw-t-sql.aspx

5. Explain following error properties?

ERROR_NUMBER():

The number assigned to the error.

ERROR_LINE():

The line number inside the routine that caused the error.

ERROR_MESSAGE():

The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY():

The error’s severity.

ERROR_STATE():

The error’s state number.

ERROR_PROCEDURE():

The name of the stored procedure or trigger that generated the error.

6. Explain Error and Transaction Handling in SQL Server?

You can use try-catch block for Error and Transaction Handling, inside try block write your sql statements or queries check "@@TRANCOUNT > 0"  if that’s is greater than "0" rollback transaction in catch block else commit transaction.

More… http://www.codechef4u.com/post/2015/04/06/raiserror

http://codechef4u.com/post/2015/04/13/try-catch

7. Can we use two or more catch block with associated one try block?

No that is not allowed.

8. Can we use TRY..CATCH block with transaction?

Yes ,TRY..CATCH blocks can be used with transactions.

9. Can we use try catch block inside user defined function?

No, TRY...CATCH construct cannot be used in user defined function.

 

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