cross

TSQL Interview Questions


Database


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 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 cross join? *
Write one example for self-join? **
What is truncate command? **
What are dynamic queries? ***
What is the Limit of small date time function? **
What is Date Add Function? **
What is date part function? ***
What are the sparse column and when we use it? ****


VIEWS

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.

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

STORED PROCEDURE


FUNCTION
What are the difference types of UDF? **
Can we create a table in function? ****


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 Covering Index? ****
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

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 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

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?




OLDER LINKS









24 comments:

  1. AnonymousMay 21, 2013 at 6:18 AM

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

    Thanks

    ReplyDelete
  2. AnonymousMay 24, 2013 at 1:34 PM

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

    ReplyDelete
  3. AnonymousMay 26, 2013 at 7:37 PM

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

    ReplyDelete
  4. AnonymousJune 30, 2013 at 5:35 PM

    these are great and very helpful

    thank you

    ReplyDelete
  5. randheerJuly 4, 2013 at 9:07 AM

    thanks all of you for such a wonderful feedback

    ReplyDelete
  6. AnonymousJuly 25, 2013 at 5:04 AM

    Good one. please share some more on performance

    ReplyDelete
  7. AnonymousAugust 5, 2013 at 10:40 AM

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

    ReplyDelete
  8. randheerAugust 5, 2013 at 8:55 PM

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

    ReplyDelete
  9. UnknownAugust 8, 2013 at 12:54 PM

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

    ReplyDelete
  10. misJanuary 15, 2014 at 12:35 AM

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

    ReplyDelete
  11. DOCITFebruary 2, 2014 at 12:56 PM

    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. yogesh jiFebruary 22, 2014 at 1:39 AM

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

    ReplyDelete
  13. UnknownSeptember 1, 2014 at 12:51 PM

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

    ReplyDelete
    Replies
    1. randheerSeptember 13, 2014 at 11:30 AM

      thanks for identifying the verbage i will correct it...

      Delete
  14. AnonymousSeptember 16, 2014 at 9:55 AM

    Thank you! Very helpful!

    ReplyDelete
  15. AnonymousOctober 30, 2014 at 12:30 AM

    Very good set of Interview Question

    ReplyDelete
  16. AnonymousApril 20, 2015 at 7:55 AM

    Thank you very much. Really appreciated.

    ReplyDelete
  17. PavanSinghMay 20, 2015 at 12:35 AM

    Liked.. can you send me file on pariharp009@gmail.com

    ReplyDelete
  18. AnonymousJuly 19, 2015 at 11:14 PM

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

    ReplyDelete
  19. AnonymousOctober 21, 2015 at 8:07 PM

    Good collection but answers are missing

    ReplyDelete
  20. Srinivas Reddy SankasaniOctober 19, 2016 at 8:22 PM

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

    ReplyDelete
  21. UnknownApril 19, 2018 at 9:35 PM

    Hello There,


    10/10 !!! Thank you for making your
    blogs     an embodiment of perfection and simplicity. You make everything so easy to follow.

    I am working on a little project for automatic receiving e-mails.
    I have managed to download mail from a pop3 mailaccount.
    My problem is that I cant figure out how to receive mail with both text in message field and attachment in the same mail.
    I am able to receive mail with just message or saving attachment. I would like to do both in the same mail.


    Awesome! Thanks for putting this all in one place. Very useful!


    Regards,
    Morgan

    ReplyDelete
  22. UnknownApril 22, 2018 at 11:02 PM

    Hi There,


    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.


    I use Powerpivot every day with a lot of SQL Server queries, some of them pretty long or complex and I have never the following issue : I had to write a 1,500-row SQL Server query that works fine on my editor and when I run it in Powerpivot, I am getting either of the following messages :
    - "one or more formulas in this workbook are longer than the allowed limit of 8192 characters" : I tried to save the file in regular format or binary format but still can't save it.
    - after closing the Powerpivot back-end menu (via "Manage"), when I want to access it again, I get a ""Powerpivot is unable to load the Data Model" error message.
    There are no formula on the front-end of Excel, all the data resides in the back-end, so what does it mean? Is it counting the total characters in the SQL Server query itself?
    I couldn't find any specific support     on this, is it due to the size of the SQL Server query? To specific tables inside the query? To some formatting that need to be done in the SQL query?

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
    Please keep providing such valuable information.


    Ciao,
    Irene Hynes

    ReplyDelete

Subscribe to: Posts (Atom)