Interview Question of the Week #023 – Error Handling with TRY…CATCH

Here is one of the most popular question I see people discussing in SQL Interviews.

Question – How do you handle errors with the help of TRY…CATCH?

Answer –

TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

Functions to be used in CATCH block are :

  • ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
  • ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
  • ERROR_STATE: returns the state number of the error.
  • ERROR_LINE: returns the line number where the error occurred.
  • ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
  • ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

{ sql_statement |
statement_block }
{ sql_statement |
statement_block }

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

You can read more about this subject over here: Explanation of TRYCATCH and ERROR Handling.

Reference: Pinal Dave (

Interview Question of the Week #022 – How to Get Started with Big Data?

Big data is one of the most popular subject in recent time and everybody wants to get started on this subject. During recent interviews there are plenty of the questions with related to Big Data. Here is the most popular question which I receive on this subject.

Question: How to get started with Big Data?

Answer: Earlier last year I wrote timeless series on the subject Big Data. Here is the link to the entire series.

 bigdataimages Interview Question of the Week #022   How to Get Started with Big Data?

Reference: Pinal Dave (

Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

Question: What is the difference between Index Seek and Index Scan?

Answer: Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan  in the execution plan.

Here are few other related articles on this subject which you may find useful:

Reference: Pinal Dave (

Interview Question of the Week #020 – What is the Difference Between DISTINCT and GROUP BY?

Question: What is the Difference Between DISTINCT and GROUP BY?

Answer: A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates, then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

USE AdventureWorks2014
-- Example of DISTINCT:
FROM [HumanResources].[Employee]

interview 20 1 Interview Question of the Week #020   What is the Difference Between DISTINCT and GROUP BY?

USE AdventureWorks2014
-- Example of GROUP BY:
SELECT JobTitle, Gender
FROM [HumanResources].[Employee]
GROUP BY JobTitle, Gender

interview 20 2 Interview Question of the Week #020   What is the Difference Between DISTINCT and GROUP BY?

USE AdventureWorks2014
-- Example of GROUP BY with aggregate function:
SELECT JobTitle, Gender, COUNT(*) EmployeeCount
FROM [HumanResources].[Employee]
GROUP BY JobTitle, Gender

interview 20 3 Interview Question of the Week #020   What is the Difference Between DISTINCT and GROUP BY?

Reference: Pinal Dave (

Interview Question of the Week #019 – How to Reset Identity of Table

Here is the question which I received in email:

Question: I have a table which has an identity column. I have recently deleted old and archive it to another place. Now I want to reset the identity of the table to original value of 1. How should I do that?

Answer: It is very easy to do so – you can use the command DBCC CHECKIDENT to do the same.

If a table has to start with an identity of 1 with the next insert then the table should be reseeded with the identity to 0. If identity seed is set below values that currently are in the table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

DBCC CHECKIDENT (yourtable, reseed, 0)

Here is the quick video I have created to demonstrate the same example.

Reference: Pinal Dave (

Interview Question of the Week #018 – Script to Remove Special Characters – Script to Parse Alpha Numerics

If you ask me – there are certain questions not appropriate for an interview as they are often very subjective. There are some questions, which really do not help to identify if the candidate has the necessary skills or not. Here is one such question, if you ask me in the interview, I may not get it right too, as this requires some experience in writing scripts as well as trial and error mechanics to get it right.

Question: How to write will remove all the special characters and parses Alpha Numeric values only?

Answer: Honestly, it is not that easy to write this script. However, if you are stars are not bright you may end up with an interviewer who believes writing this script is the ultimate test. Do not get me wrong, writing this script is not a bad test, I believe it is not the best question for the interview.

@string VARCHAR(8000)
@IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
@string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
@string = @string
RETURN @string

You can test above function with the help of following test:

-- Test
SELECT dbo.UDF_ParseAlphaChars('AB"_I+{D[]}4|:e;"5,<.F>/?6')

The above query will return following result set:


You can read about this function and additional comments in my earlier blog post over here: Function to Parse AlphaNumeric Characters from String

Reference: Pinal Dave (

Interview Question of the Week #017 – Performance Comparison of Union vs Union All

Here is what I just learned from email. One of the very prestigious organization asks one of their candidate following questions –

Question: If you have options to use Union or Union All – which one of the option will use keeping performance as a top most requirement for the query.

Answer: Though many of you may be surprised to know that this kind of questions can exist, I am personally not surprised to see this in an interview. Here is my answer – UNION and UNION ALL can’t be compared as they are absolutely different things – they are like apples and oranges. Just like Apples and Oranges are fruits Union and Union All our operators, but they both are there for very different purposes.

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

You can’t compare their performance as they do an absolutely different task.

Here are articles you can read for further understanding this issue.

Performance comparison: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

Different between Union and Union All: SQL SERVER – UNION ALL and UNION are Different Operation

Other relevant articles:

Reference: Pinal Dave (

Interview Question of the Week #016 – How to Take Database Offline

Here is the question, I received in the recent conference where I was presenting on Database Technology.

Question: What does it mean by Taking Database Offline and How to do it?

Answer: Taking database offline means, it will be no more available for database operations. Here are is how you can take your database offline.

-- Take the Database Offline

If you have previously taken database offline, you can once again take it online by running the following command:

-- Take the Database Online

You can read more about it in following blogs:

Reference: Pinal Dave (

Interview Question of the Week #014 – How to DELETE Duplicate Rows

Here is another very common question I often received, which candidates face in the interview.

Question: How to delete duplicate rows in SQL Server (considering there is an identity column in table)?

Answer: Well, there is no better answer than following a script. If you ask me this question in the interview, I will even have to look up online and find following script:

FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Here is the quick video I have recorded where I demonstrate the same functionality in simple words.

Reference: Pinal Dave (

Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure

There are some questions which are so old that they should not be asked in the interview and gets old. Here is one of the questions I have spotted so many times in the interview that if co-interviewer asks to the candidate, I often feel bored (well, I was caught yawning last time). Here is the question:

Question: “How do you create stored procedure? and What are the advantages of the stored procedure?”

Answer: Well, as I said, I find this question are age old, so if you search on the internet you will find many different answers. Just make sure you read the answers which are from the latest version of SQL Server as stored procedure have changed a bit since the inception of the same.

Here are few of the advantages of the stored procedure:

  • Execution plan retention and reuse
  • Query auto-parameterization
  • Encapsulation of business rules and policies
  • Application modularization
  • Sharing of application logic between applications
  • Access to database objects that is both secure and uniform
  • Consistent, safe data modification
  • Network bandwidth conservation
  • Support for automatic execution at system start-up
  • Enhanced hardware and software capabilities
  • Improved security
  • Reduced development cost and increased reliability
  • Centralized security, administration, and maintenance for common routines

You can read the original article over here.

If you want to create stored procedure you can create with following simple script:


You can execute above stored procedure by following a script:


The above stored procedure will return results as a current date time.

 spcreate Interview Question of the Week #013   Stored Procedure and Its Advantages   How to Create Stored Procedure

Reference: Pinal Dave (