TSQL Interview Questions


Database

What is DBMS?

What is RDBMS?

What are the ACID properties?

What is Normalization?

What is D-normalization and when do we need it?

What are the different Normal Forms?


Table Design

What is Table? *

What is Data type? *

What are the different types of data types in SQL Server? *

What is the difference between Float, BIG Int and Integer? *

Explain the Decimal Data types and its uses. *

What is the use of different types of Integer data types? *

What is the difference between bit, Char and Varchar? *

What is Unicode? **

What is the difference between Varchar and Nvarchar? **

What are different date time data types? *

What are unique identifiers in SQL Server Table? **

What is check constraint in SQL Server? **

What is default constraint? *

What is Null ability? *

Explain table relationship? *

What is Primary Key? *

What is Foreign Key? *

What is composite key? *

What are Primary Key and Foreign Key Constraint? *

How to get the list of primary key and foreign key of the table? ****

Can we insert null value in primary column? *

No we cant insert null value in a single column primary key while in composite primary key only one column can be null
What is difference between Primary Key and Unique Key? **

We can insert one null value in unique key but not in primary key.

What is Identity Column in Table? *

Syntax to check current Identity of the table? *

What is the difference between Scope of Identity on @@identity? ***

Can we change identity key values for a table or reset the identity key value. ***

What is function Ident_INCR? ***

What is times stamp data type is SQL Server? **

What is the alternative of timestamp? **


JOINS AND SELECT

What is select statement in TSQL? *

What is the Join in SQL Server? *

What are the different types of join? *

Difference between Left join and Outer Join? *

What is full outer join? *
The Full Outer Join logical operator returns each row satisfying the join predicate from the first (top) input joined with each row from the second (bottom) input. It also returns rows from:

The first input that had no matches in the second input.
The second input that had no matches in the first input.

The input that does not contain the matching values is returned as a null value.

What is inner join? *

What is cross join? *

Write one example for self-join? **

What is where clause? *

What are the sub queries? *

What are the nested queries in SQL Server? **

What is inline query? **

What are the aggregate functions in SQL Server? *

What is Group By? *

What is Having clause? **

What is difference between where and having clause? **

What is the table variable? **

What is the temporary table? **

What is the global temporary table? **

Difference between temporary table and table variable? **

What is the union all clause? **

What is union Clause? **

Difference between union all and union clause? **

What is the Ranking function in SQL Server? ****

Difference between rank and dense rank? ****

What is top operator in SQL Server? **

What is table sample in SQL Server? ***

What is the delete command? *

What is truncate command? **

Difference between delete and truncate? **

What are dynamic queries? ***

What is distinct clause? How it works? **

How to use Order by in Sub Queries? **

What is the Limit of small date time function? **

What is the @@error? **

What is the @@raise error? **

What is the @@row count? **

What is Date Diff function? **

What is Date Add Function? **

What is date part function? ***

What is coalesce function in SQL Server? ***

What is difference between stuff and Replace function? ***

What are the sparse column and when we use it? ****

How to Convert Integer to String in SQL Server?


VIEWS

What is the view in SQL Server? **

What are the updated views? ***

What are the materialized views or Indexed Views in SQL Server? ****

What is view with check option? ****
The with check option causes the where clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved. In a sense, it makes the where clause a two-way restriction.
This option is useful when the view should limit inserts and updates with the same restrictions applied to the where clause.

Write the Create syntax for Views. **

What is common table expression (CTE)? ***

What is difference between CTE and View? ****


STORED PROCEDURE

What is the User defined functions? **

What are the difference types of UDF? **

What are difference between UDF and stored procedure? ***

Can we create a table in function? ****


TRIGGERS

What is a trigger in SQL Server? **

What is the nesting level of triggers? **

What are the different types of triggers in SQL Server? ***


CURSORS
What is the cursor in SQL server? **

What are the different types of cursor in SQL Server? ***

When to use Cursor in SQL Server? ***


INDEX

What is Index in SQL Server? ***

Explain different types of index in SQL Server? ***

What is filter index in SQL Server? ****

What is Covering Index? ****

What is index scan and index seek? ****

What is B+ tree index? ****

Explain the architecture of the index? ****

Explain the performance impact of index? ****

Can we create clustered index on null value column? ****

What are indexes in SQL Server? ****

How many clustered index can be created on the table? ****

How to enforce index on a query? ****

What are the different index configurations a table can have?

A table can have one of the following index configurations:

No indexes

A clustered index

A clustered index and many nonclustered indexes

A nonclustered index

Many nonclustered indexes


PERFORMANCE TUNING

What is SQL Profiler?


What is Database Tuning Advisor?

What is Execution Plan?

How to Read Execution Plan? What are the statistics and the impact on SQL Server?

What are the table hint and how we can provide it on table?

How to read execution plan?

What are the algorithms does SQL Server used to fetch data?

What is bookmark lookup?

How do you tell SQL Server to preserve the join order in the FROM clause during

optimization?

You can use OPTION (FORCE ORDER)

TRANSACTION

What is Transaction Server Implicit?
Implicit: when the transaction is in implicit mode, a new transaction starts automatically after the current transaction is committed or rolled back. Nothing needs to be done to define the start of the transaction. It generates continues chain of transactions.

What is Transaction Server Auto commit?
Auto commit: This is the default management mode. Every SQL statement is either committed or rolled back when complete. If it completes successfully it is committed else it is rolled back. Auto commit is default mode.

What is Transaction Server Explicit transaction?
Explicit transaction: transactions that have a START and END explicitly written are called as an explicit transaction. They last only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started

What is Transaction Server Consistency?
Consistency: This property ensures the data is consistent before the transaction and left in a consistent state after the transaction. If the transaction violates the rules, it must be rolled back.

What is Transaction Server Isolation?
Isolation: This property means that the transaction should be isolated. I.e. until the transaction is over other data or operations cannot access the transaction. This is to maintain the performance.

Explain Atomicity?
Atomicity: This property of a transaction ensures that a transaction either completely or does not happen at all. E.g. transferring money from one account to another.

What are the different types of transaction errors?

What is No lock?

What happens if a transaction is failed in nested transaction, then other outer transactions will execute or not?

Explain Atomicity, Consistency,Isolation,Durability?

What is nested transaction?

What is distributed transaction?

What is isolation level in SQL Server?

What are different types of isolation level in SQL Server?

What is default isolation Level for SQL Server?

RANDOM

How Left Join with count(*) works.

Write a query to find the nth minimum and maximum.

Write a query to get the last record of the table.

Write a query to get the no rows of a table without using count or any other clause.

How many columns can be used with roll up or with cube command?

10 columns

How many columns can be selected in a select statement?

4096 columns can be selected.

How many columns can be selected in an insert statement?

4096 columns can be selected.

What level of nesting is possible for sub Queries?

32 level of nesting.

What level of nesting is possible for Triggers?

32 level of nesting.

How many non-clustered indexes can be created on a table?

Only one clustered index can be created

How many parameters can be provided to a stored procedure?

2100 parameters can be provided to a SP.

How many parameters can be provided to a UDF?

2100 parameters can be provided to a UDF.

How many columns can be taken in update statement?

4096 columns can be updated in an update statement

What is NO COUNT used for?

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement. The setting of SET NOCOUNT is set at execute or run time and not at parse time

What is SET ANSI NULL ON used for?

What is arithmetic abort used for?

What is SET ROW COUNT used for?

What is SET ANSI padding?

What is SET ANSI WARNING on?

What is SET NO EXEC?

Which command using Query Analyzer will give you the version of SQL server and operating system?

How to delete duplicate records from table using query?

What are different types of Collation Sensitivity?.



OLDER LINKS



SQL Server TSQL Interview Questions Part 1
SQL Server TSQL Interview Questions Part 2
SQL Server TSQL Interview Questions Part 3
SQL Server TSQL Interview Questions Part 4
SQL Server TSQL Interview Questions Part 5
SQL Server TSQL Interview Questions Part 6
SQL Server TSQL Interview Questions Part 7
SQL Server TSQL Interview Questions Part 8
SQL Server TSQL Interview Questions Part 9
SQL Server TSQL Interview Questions Part 10
SQL Server TSQL Interview Questions Part 11
SQL Server TSQL Interview Questions Part 12







21 comments:

  1. Good collection of TSQL Questions. Please provide the questions on catagories basis , it will be really helpfull.

    Thanks

    ReplyDelete
  2. Thanks a lot!! Appreciate your efforts...Continue the Good Work!!

    ReplyDelete
  3. Good one...Really useful! Thanks for posting them!

    ReplyDelete
  4. these are great and very helpful

    thank you

    ReplyDelete
  5. thanks all of you for such a wonderful feedback

    ReplyDelete
  6. Good one. please share some more on performance

    ReplyDelete
  7. Thanks dear i crack t-sql interview 2day after prepare of this such of questions

    ReplyDelete
  8. thanks and all the best for your new jobs and many more interviews.. :)

    ReplyDelete
  9. I liked on FB. Can you pls share file akhilstar1@gmail.com?

    ReplyDelete
  10. really nice job......can u send me file at mohd_imran11189@yahoo.in plz?????????????

    ReplyDelete
  11. Hi Randheer. Thanks for the good work. Can you kindly send me the file as well at mci.kashif@yahoo.com. I will be glad. appreciate it!

    ReplyDelete
  12. Thanks a lot, This is very big and latest collection of sql interview question. It is very helpful for preparation sql interviews.

    ReplyDelete
  13. The maximum number of nonclustered indexes that can be created per table is 999. [sql server 2014]

    ReplyDelete
    Replies
    1. thanks for identifying the verbage i will correct it...

      Delete
  14. Thank you! Very helpful!

    ReplyDelete
  15. Very good set of Interview Question

    ReplyDelete
  16. Thank you very much. Really appreciated.

    ReplyDelete
  17. Thanks for providing the very big and latest collection of sql interview question. Please send the file to rajatsdp@gmail.com

    ReplyDelete
  18. Good collection but answers are missing

    ReplyDelete
  19. thanks for useful information. can you send me the complete document to sankasani@live.com

    ReplyDelete