SQL Interview Questions

1 Apr

These are the most common yet tricky questions that you can expect in a .NET/SQL interview.

1)      What is the difference between truncate and Delete?

  1.  Delete will fire delete trigger, truncate will not.
  2. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.Once you truncate you can’t rollback.
  3. If there is an identity column in the table truncate will reset identity to 1, delete will not.

2)      Name the different type of joins

  1. Inner Join
  2. Outer Join  (Left Outer Join and Right Outer Join)
  3. Cross join
  4. Self-Join

3)      What is an Index?

There are two types of indexes. Clustered index and Non Clustered Index. A table can have only 1 Clustered Index while it can have up to 249 & 999 nonclustered indexes on SQL Server 2005 & 2008 respectively. A clustered index stores the actual data rows at the leaf level of the index. Indexes can make Select statement fast but delete, update and insert statements will get slow.

4)      What are different types of Isolation levels in SQL Server

a)     READ UNCOMMITTED

b)      READ COMMITTED

c)       REPEATABLE READ

d)      SERIALIZABLE

e)       SNAPSHOT

 5)      What are different types of constraints in SQL Server?

  1.  Primary key Constraint
  2. Foreign key Constraint
  3. Check Constraint
  4. Unique Key Constraint

6)      What is the difference between where and having clause?

A having clause is typically used when you use group by clause.

Example:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = ‘CA’
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set

7)      How would you do “Error Handling” in SSIS?

SSIS package could mainly have two types of errors

a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.

b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

8)      What is the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column. Major difference is that, primary key does not allow NULLs, but unique key allows NULL.

9)      What are defaults? Is there a column to which a default cannot be bound?

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

You can create default and then bind a column to them.

Example:

//This will create default ” ZipCode ” in database

Create default ZipCode as “78746″

//This will bind the default we created to a column “PostalCode” in table “EmployeeData”
sp_bindefault ZipCode, “EmployeeData.PostalCode”
10)  What is SQL Blocking?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
Thanks for visiting my blog. Please feel free to leave suggestions/feedbacks/comments.
If you like my post please click “LIKE” button below.

Check back again for more SQL and .NET Interview Questions. Read more on .NET Questions here .NET Questions

Anyone on Twitter? Follow @MCADDeveloper @Consultpri

Advertisements

12 Responses to “SQL Interview Questions”

  1. oracrazy April 4, 2012 at 6:00 am #

    This is a great set of questions and answers for the candidates appearing for the interviews. The candidates looking for a job in Database may also visit this blog for concepts and fundas which are equally important from interview point of view. Visit : http://crazy4db.blogspot.in

  2. oracrazy April 4, 2012 at 6:03 am #

    This is incredible post for the job seekers. Also visit : http://crazy4db.blogspot.in

  3. Prashant Munshi April 4, 2012 at 6:05 am #

    This is incredible post for the job seekers. Also visit : http://crazy4db.blogspot.in

  4. Robert Young April 5, 2012 at 7:49 am #

    I’d be happier if you ordered 4) in isolation stringency order.

    • PriConnects April 5, 2012 at 9:14 am #

      Thanks for pointing that out.I have gladly made the suggested changes.

  5. dtvam April 10, 2012 at 9:39 am #

    Hi,

    Few corrections in above answers
    Under Q3. A table can have only 1 Clustered Index while it can have up to 249 non clustered indexes.
    Answer: From 2008 onwards, you can have 999 non clustered indexes

    Q8. Answer says “but unique key allows NULL.”
    Answer: you can have only 1 null value in the table for the column

    • PriConnects April 10, 2012 at 11:54 am #

      Thanks. I agree with SQL 2008 you can have up to 999 Indexes. However Unique key can be on multiple columns too.In that case a column may have more than 1 null for different combinations.

  6. suanmeiguo April 11, 2012 at 10:05 am #

    Reblogged this on suanmeiguo and commented:
    cool!

  7. Alexei April 27, 2012 at 9:24 am #

    >Once you truncate you can’t rollback.
    The most persistent myth. MSDN never said this. Truncation is a loggable operation. It is just logged on different (less granular) level then delete.
    Test for yourselves:

    /*
    create table ATest (ID INT identity, col1 varchar(10))
    insert into ATest (col1) values (‘Col1Value’)
    insert into ATest (col1) values (‘Col2Value’)
    */

    select ‘before’,* from ATest
    begin tran
    truncate table ATest
    rollback tran
    select ‘after’,* from ATest

Trackbacks/Pingbacks

  1. SQL INDEXING – Simplified | priconnects - April 3, 2012

    […] about SQL Technical Interview Questions here: Crack the SQL Interview Written by Amit […]

  2. SQL/.NET INTERVIEW – Be Ready for an In-person Interview | Technology, Social Media, Food & Health And more… - April 13, 2012

    […] about SQL Technical Interview Questions here: Crack the SQL Interview Written by Amit […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: