T-SQL Interview Questions

Source: Books Online from Microsoft

1. What is SQL Server?
SQL Server is a vast, easy, powerful Relational Database Management (also Datawarehouse Management) application from Microsoft. It offers Database Development, Database Management and Business Intelligence capabilities. This wonderful technology is very easy to master and manage. This offers following advantages:

It is positioned Number 2 in the RDBMS market for FY 2013 as per Microsoft Latest Press News. Next version is SQL Server 2014 (currently in CTP - Community Technology Preview)

2. What are the System Requirements in SQL server?
SQL Server from Microsoft is mainly intended to be used on Windows Systems. Prior versions of SQL Server are compatible on Linux (Redhat Linux) as well. SQL Server comes with different Editions (Flavors) in each Version (Product Release). SQL Server is available in 32 bit and 64 bit compilers. Generic (Minimal)Requirements include:

3. What are Editions of SQL Server 2012
OS System requirements depend on the Version and Edition you are using. Enterprise and Datacenter Editions require Windows Server 2003 SP2 or Windows Server 2008 for complete features support.

Core Server Editions of SQL Server 2012 Specialized Editions of SQL Server 2012
  • SQL Server 2012 Datacenter (64-bit) IA64
  • SQL Server 2012 Enterprise (64-bit) IA64
  • SQL Server 2012 Datacenter (64-bit) x64
  • SQL Server 2012 Enterprise (64-bit) x64
  • SQL Server 2012 Standard (64-bit) x64
  • SQL Server 2012 Datacenter (32-bit)
  • SQL Server 2012 Enterprise (32-bit)
  • SQL Server 2012 Standard (32-bit)
  • SQL Server 2012 Developer (64-bit) IA64
  • SQL Server 2012 Developer (64-bit) x64
  • SQL Server 2012 Workgroup (64-bit) x64
  • SQL Server 2012 Web (64-bit) x64
  • SQL Server 2012 Express (64-bit) x64
  • SQL Server 2012 Express Advanced (64-bit) x64
  • SQL Server 2012 Developer (32-bit)
  • SQL Server 2012 Workgroup (32-bit)

4. Difference between DELETE & TRUNCATE statement?
DELETE is used for conditional removal of data records from Tables (Also applicable for Views). These operations are logged.
TRUNCATE is used for unconditional removal of data records from Tables. Truncate Operations are not logged.

5. What are System Stored Procedures?

These are special type of Stored Procedures created in MASTER database and accessible in any other database within the server. Rules to create System Stored Procedures:

6. What are extended stored procedures?
Extended stored procedures are used to execute external / non SQL Server routines. They are actually DDLs that are executed dynamically on demand.The extended stored procedures are similar to regular stored procedures where Parameters can be passed to extended stored procedures, and can return results and return status. They have the naming prefix: XP_ and executed like any other generic Stored Procedure using EXEC / EXECUTE commands.

7. How to insert or load the result of a Query or a Procedure into an existing Table?

INSERT INTO TABLE_NAME( ) SELECT FROM SOURCE_TABLE
INSERT INTO TABLE_NAME( ) EXECUTE PROCEDURE_NAME

8. What is Left Anti Semi Join Operator?
The Left Anti Semi Join operator returns the result set for each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row. Left Anti Semi Join is a logical operator.

9. What are the precautions for Cursor Usage?
As per the Memory Usage of Cursor Variable, Dynamic Cursors requires less RAM and presents update, dynamic data compared to Static Cursors that requires More RAM and offer better performance.

10. Why you should not use a cursor? What are its alternatives?
Microsoft requests us to avoid using cursors due to Memory and RAM overhead including comparatively more DISK IO.
Alternatives: WHILE LOOP / CASE / Temprary Tables / Derived Tables / Sub Queries can be used in place of Cursors

11. What are Dynamic Queries in T-SQL?
Dynamic T-SQL Queries are those queries designed on the fly / at run time using Variables or using CTE or other sources. We use SP_EXECUTESQL Stored Procedure or EXECUTE function to execute such queries.

12. When to use COALESCE() & ISNULL() Functions?
The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

13. What are limitations of IDENTITY Column?
IDENTITY column can be used as a surrogate key. This column values cannot be updated once generated (automatically or manually). As we have possibility to duplicate these values within a table we may require to define this column explicitly as a PRIMARY KEY. This Identity property is applicable for integer based columns only. /p>

14. What are generic guidelines to define a Primary Key
Primary Key is usually defined for unique identification of rows within a Table. As creation of Clustered Index creates a Index ( Clustered Index by default but can also be defined as Non-Clustered Index) we generally consider the selectivity of column before deciding the Index Column. If possible, better to have this on a Integer Based Column for efficient Indexing and Reference Operations.

15. What is CROSSAPPLY Operator?
We use INNER / OUTER / CROSS JOINS for joining Tables and/or Views. CROSSAPPLY operator in T-SQL is used to join a Table or a View with a Table Value returning Function

16. What are CUBE & ROLLUP in T-SQL?
CUBE & ROLLUP are the grouping sets used in conjunction with GROUP BY clause to generate summarized aggregations. These are mainly used for Report Generation and Data Audits.

17. What are types of XML indexes in SQL Server?
Microsoft SQL Server supports different types of XML indexes. An XML index is different than a relational index. There are basically TWO types of XML Indexes viz., Primary XML Indexes and Secondary XML indexes. The Primary XML index is a clustered index on an internal table known as the node table that users cannot use directly from their T-SQL statements. To enhance search performance, we create secondary XML indexes. These create secondary links (RID) at leaf level for existing clustered index based KEY pages. A primary XML index should be created prior to creating the Secondary XML Indexes.

18. How you can load large data in SQL Server?
IMPORT and EXPORT Wizard, BULK INSERT and BCP are the possible options. We need to set the RECOVERY MODEL to BULKLOGGED before we perform such activities.

19. What are the various Isolation levels in SQL Server?
a) Read Uncommitted Isolation Level
b) Read Committed Isolation Level
c) Repeatable Read Isolation Level
d) Serializable Isolation Level
e) Snapshot Isolation Level
f) Read Committed Snapshot Isolation Level

20. What are advantages of SQL Server latest release?
a) delivers an ROI of up to 189% with a 1 year payback period.
b) Microsoft DBAs run more mission critical databases, when compared to Oracle DBAs
c) SQL Server delivers six nines (99.9999%) uptime availability.
d) SQL Server is the most secure of any of the major database platforms.
e) SQL Server delivers a 460% savings in annual cost of administration per database over Oracle.
f) SQL Server reduces downtime by over 20% by migrating an SAP ERP environment to SQL Server. Source

Other T-SQL Queries

Common Interview Questions from Interview Panels

  1. What are RAID Levels and how they are helpful for SQL Server Database Design?
  2. What is Normalization process? What are Anomalies in DB Design
  3. What is benefit of a having stored-procedure?
  4. What is the benefit of a Temporary Table, how would you define it?
  5. Can stored-procedures be recursive? And upto how much level?
  6. What is the trade-offs of a BCP command, when various users are loading data in a particular table at same time?
  7. How can you copy schema from one SQL Server to another?
  8. What is the scope of a temporary table?
  9. How many non-clustered indexes can you have in a table?
  10. What is the benefit of cross joins? How would you use a where clause with Cross Joins?
  11. Is there VARCHAR2 support in SQL Server? Any alternative?v
  12. How do we perform Error Handling in SQL Server?
  13. How to update 'Yes' to 'No' and viceversa in a query?
  14. How to disable a Constraint in SQL Server?
  15. Can we disable PRIMARY KEY Constraint? If not, why?
  16. Can Foreign Key Constraint allow duplicates? If so, why?
  17. Hoe to duplicate Identity Values?
  18. How to load historical data w/o enforcing existing constraints?
  19. What is REFRESH of a View? Any limitations of this operation?
  20. How to create System Functions? What are the advantages?
  21. How will you know what indexes a particular table is using?
  22. What are implicit & explicit cursors in SQL Server?
  23. What is Mutex error in Triggers? MSDN Source
  24. What is the use of NOLOCK locking hint? BOL Source
  25. What is the use of READPAST locking hint? BOL Source
  26. When to use WITH RESULT SETS in SQL Server 2012 (DENALI)?
  27. What is the difference between PARSE and CONVERT in SQL Server 2012 (DENALI)?

Register Today For Free Demo