T-SQL Interview Questions & Answers

  1. Question 1. What Is Tsql?

    Answer :

    TSQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL) including transaction control, exception and error handling, row processing, and declared variables. Microsoft’s SQL Server and Sybase’s SQL server support T-SQL statements.

  2. Question 2. How To Use Sql Query To Copy Only Structure?

    Answer :

    select * into table2 from table1 where 1 = 2

  3. PL/SQL Interview Questions

  4. Question 3. How To Query A String Contains %?

    Answer :

    SELECT Name FROM tblPlayer WHERE Name Like ‘%[”%”]’.

  5. Question 4. What Is Patindex?

    Answer :

    Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found.
    Syntax – PATINDEX ( ‘%pattern%’ , expression )
    Eg: USE AdventureWorks;

    USE AdventureWorks;
    GO
    SELECT PATINDEX('%ensure%', DocumentSummary)
    FROM Production.Document
    WHERE DocumentID = 3;
    GO
  6. SQL Database Tutorial

  7. Question 5. How To Update ‘yes’ To ‘no’ And Viceversa In A Query?

    Answer :

    Update tablename set ColumnName1 = (case ColumnName1 when ‘Yes’ then ‘No’ else ‘Yes’ end).

  8. MSBI Interview Questions

  9. Question 6. How To Create Temporary Table? How Do We Apply Noncluster Index? What Is Nolock? When And Where Is Nolock Applied Normally?

    Answer :

    Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply “nolock”. nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.

    CREATE TABLE #tmpTable
    (
    OfficeName varchar(50)
    , officeid int
    , CustID int
    , AgentID int
    , mlsid varchar(4)
    , RequestMoreDetails int null
    , Emails int null
    )
    CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)
    
    select
    OfficeName
    , officeid
    , o.CustID
    , AgentID -
    , o.mlsid
    , PrintBrochure_Views = null
    , RequestMoreDetails = null
    , Emails = null
    into #ForOffices from #Offices 
    LEFT JOIN dbo.planparts WITH (NOLOCK)
    ON bppa.officeid = o.RID
    CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)
    
  10. Question 7. Difference Between Sql Server 2000 & Sql Server 2005 Features Or New Features Of 2005 Vs 2000?

    Answer :

    • Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
    • Exception handling (TRY-CATCH block)
    • CTE (Common Table Expressions)
    • PIVOT, UNPOVIT
    • CUBE, ROLLUP & GROUPING SET
    • SYNONYMS.
  11. MySQL Tutorial
    SQL Database Interview Questions

  12. Question 8. What Tools Do You Use For Performance Tuning?

    Answer :

    SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:

    • Query Analyzer
    • Profiler
    • Index Wizard
    • Performance Monitor.
  13. Question 9. How Can You Execute An Sql Query From Command Prompt?

    Answer :

    By using OSQL & SQLCMD we can execute an sql query from command prompt.

  14. MySQL Interview Questions

  15. Question 10. Difference Between Delete & Truncate Statement? Which Statement Can Be Rollbacked?

    Answer :

    • With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
    • TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
    • Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
    • DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP.
  16. DB2 Using SQL Tutorial

  17. Question 11. How Can You Execute A Dos Command From Sql Or Through Sql Query By Using Xp_cmdshell?

    Answer :

    exec xp_cmdshell ‘dir c:*.exe’
    xp_cmdshell {‘command_string’} [, no_output]
    Arguments
    ‘command_string’
    Is the command string to execute at the operating-system command shell. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
    no_output
    Is an optional parameter executing the given command_string, and does not return any output to the client.

  18. DB2 Using SQL Interview Questions

  19. Question 12. Why You Should Not Use A Cursor? What Are Its Alternatives?

    Answer :

    If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. Here are some alternatives to using a cursor:

    • Use WHILE LOOPS
    • Use temp tables
    • Use derived tables
    • Use correlated sub-queries
    • Use the CASE statement
    • Perform multiple queries.
  20. PL/SQL Interview Questions

  21. Question 13. What Are The Multiple Ways To Execute A Dynamic Query?

    Answer :

    EXEC sp_executesql, EXECUTE()
    EXECUTE (or EXEC) vs sp_executesql
    EXECUTE:
    EXECUTE executes a command string or character string within a TSQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar data type.
    sp_executesql:
    sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain embedded parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be prefixed with N.

  22. DocumentDB SQL Tutorial

  23. Question 14. Difference Between Coalesce() & Isnull()?

    Answer :

    • ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.
    • COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter.
    • ISNULL does not implicitly converts the datatype if both parameters datatype are different. On the other side COALESCE implicitly converts the parameters datatype in order of higher precedence.
    • Similar to above point ISNULL always returns the value with datatype of first parameter. Contrary to this, COALESCE returns the datatype value according to the precedence and datatype compatibility.
  24. Question 15. What Should Be The Ideal Combination With In & Union (all) In Terms Of Performance?

    Answer :

    SELECT *FROM
    WHERE
    IN (SELECT… UNION SELECT…)
    OR
    SELECT * FROM
    WHERE
    IN (SELECT… UNION ALL SELECT…).
  25. SQL DBA Interview Questions

  26. Question 16. What Are Cube & Rollup Sets?

    Answer :

    CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.

  27. Question 17. What New Indexes Are Introduced In Sql Server 2005 In Comparison To 2000?

    Answer :

    • Spatial
    • XML.
  28. SQL Interview Questions

  29. Question 18. How Many Types Of Functions (udf) Are There In Sql Server?

    Answer :

    SQL Server supports mainly 3 types of UDFs:

    • Scalar functions
    • Inline Table-valued functions
    • Multi-statement Table-valued functions.
  30. MSBI Interview Questions

  31. Question 19. How Will You Handle Exceptions In Sql Server Programming?

    Answer :

    By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block.

  32. Question 20. What Are The Virtual Tables In Triggers?

    Answer :

    The virtual tables in Triggers are:

    • Inserted
    • Deleted.
  33. PL/SQL and Informatica Interview Questions

  34. Question 21. Can Stored-procedures Be Recursive? And Upto How Much Level?

    Answer :

    Yes, 32 levels.

  35. Question 22. How Can You Load Large Data In Sql Server?

    Answer :

    BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

  36. Question 23. How Can You Copy Schema From One Sql Server To Another?

    Answer :

    • DTS, import/export wizard.
    • Scripting out Database objects.
  37. SSIS(SQL Server Integration Services) Interview Questions

  38. Question 24. What Is A Table Called That Has ## Before Its Name, What Is Its Scope?

    Answer :

    Table with ## (double pound signs) is called Global Temp table. Scope is outside the session but only till the original session lasts.

  39. SQL Database Interview Questions

  40. Question 25. What Is The Scope Of A Temporary Table?

    Answer :

    Scope is limited to its session only.

  41. Question 26. What Are The Various Isolation Levels?

    Answer :

    • Read Uncommitted Isolation Level
    • Read Committed Isolation Level
    • Repeatable Read Isolation Level
    • Serializable Isolation Level
    • Snapshot Isolation Level
    • Read Committed Snapshot Isolation Level.
  42. MYSQL DBA Interview Questions

  43. Question 27. How Would You Know That If A Cursor Is Open Or Closed?

    Answer :

      declare @mycursor cursor 
      declare @FirstName varchar(12) 
      select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) 
      set @mycursor = cursor for 
      select FirstName from Person.Contact
      select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed)
      open @mycursor 
      select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row) 
      fetch next from @mycursor into @FirstName 
      select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row) 
      close @mycursor 
      select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed) 
      deallocate @mycursor 
      select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) 
      select CURSOR_STATUS('variable','@nocursor') --// -3 (A cursor with the specified name does not exist) 
  44. MySQL Interview Questions

  45. Question 28. How Many Non-clustered Indexes Can You Have In A Table?

    Answer :

    Upto 249 non-clustered indexes can be created in a table.

  46. Question 29. What All Indexes Can You Have In A Table?

    Answer :

    One Clustered Index, one or more than one non-clustered index, unique index, filtered, spatial, xml, etc.

  47. PL/I Interview Questions

  48. Question 30. Difference Between Varchar & Varchar2?

    Answer :

    VARCHAR2 is specific to Oracle. MS SQL Server has VARCHAR & VARCHAR(MAX) data types.

  49. Question 31. What Is De-normalization?

    Answer :

    De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMS implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

  50. Question 32. Types Of Replication?

    Answer :

    Microsoft SQL Server 2005 provides the following types of replication for use in distributed applications:

    • Transactional Replication
    • Merge Replication
    • Snapshot Replication.
  51. DB2 SQL Programming Interview Questions

  52. Question 33. What Can You Do With Colasce Function?

    Answer :

    Returns the first nonnull expression among its arguments.
    Syntax
    COALESCE ( expression [ ,…n ] )
    expression
    Is an expression of any type.

  53. DB2 Using SQL Interview Questions

  54. Question 34. What Are Integrity Constraints?

    Answer :

    Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity allows to define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements, the RDBMS will not allow so.
    A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

  55. Question 35. What Is The Sequence For Logical Query Processing, What Is The Order?

    Answer :

    FROM, [JOIN CONDITION, JOIN TABLE …], ON, OUTER, WHERE, GROUP BY, CUBE/ROLLUP/GROUPING SETS, HAVING, SELECT, DISTINCT, ORDER BY, TOP.

  56. Question 36. How Will You Rename A Table?

    Answer :

    By using sp_rename stored procedure.

  57. SQL DBA Interview Questions

  58. Question 37. What Are Acid Properties, Define Them?

    Answer :

    A – Atomicity (Transaction is atomic, if one part fails, then the entire transaction fails).
    C – Consistency (Any transaction the database performs will take it from one consistent state to another, only valid data will be written to the database).
    I – Isolation (Other operations cannot access data that has been modified during a transaction that has not yet completed).
    D – Durability (On a transaction’s success the transaction will not be lost, the transaction’s data changes will survive system failure, and that all integrity constraints have been satisfied).

  59. Question 38. What Is The Highest, Lowest & Default Isolation Level?

    Answer :

    Highest: SERIALIZABLE
    Lowest: READ UNCOMMITTED
    Default: READ COMMITTED

  60. Question 39. How Will You Know Index Usage On Tables?

    Answer :

    • Execution plan
    • SET STATISTICS PROFILE ON.
  61. Question 40. What Are The Various Ssis Logging Mechanisms?

    Answer :

    • Text file
    • SQL Server Profiler
    • SQL Server
    • Windows Event Log
    • XML File.
  62. SQL Interview Questions

  63. Question 41. What Is Collation?

    Answer :

    Each SQL Server collation specifies three properties:

    • The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
    • The sort order to use for non-Unicode character data types (char, varchar, and text).
    • The code page used to store non-Unicode character data.
  64. Question 42. What Output Will “select 1/2” Statement Give?

    Answer :

    It will give zero.

  65. PL/SQL and Informatica Interview Questions

  66. Question 43. What Are The Steps Involved In Database Partitioning?

    Answer :

    This involves 4 steps:

    1. Create Database with different file groups
    2. Create Partition Function
    3. Create Partition Scheme
    4. Create Partitioned Table or Index.
  67. Question 44. How Many Types Of Recovery Models Are Available For A Database?

    Answer :

    1. Simple
    2. Bulk logged
    3. Full.
  68. Question 45. How Many Types Of Temporary Tables Are There In Sql Server?

    Answer :

    • Local Temp tables (#)
    • Global temp tables (##)
    • Table variables (@).
  69. Question 46. What Are The Required/mandatory Parameters That Have To Be Passed With Raiseerror Statement?

    Answer :

    • message _id or message_str
    • severity, and
    • state.
  70. Question 47. What Are Row Constructors?

    Answer :

    New way to insert multiple records in a table with INSERT statement. Also known as Table Value Constructor.

  71. Question 48. How Many Types Of Internal Joins Are There In Sql Server?

    Answer :

    • Nested loop join
    • Merge join
    • Hash join.
  72. Question 49. What Are Pages In Sql Server? How Many Types Of Pages Are There?

    Answer :

    A page in SQL Server is an 8 KB data storage area.
    There are 8 types of pages:

    1. Data
    2. Index
    3. Text/Image
    4. Global Allocation Map, Shared Global Allocation Map
    5. Page Free Space
    6. Index Allocation Map
    7. Bulk Changed Map
    8. Differential Changed Map.
  73. Question 50. How Many Data Sources Are Available In Ssis?

    Answer :

    • DataReader Source in 2005 & ADO NET Source in 2008 & above.
    • Excel Source
    • Flat File Source
    • OLE DB Source
    • Raw File Source
    • Script Component
    • XML Source.
  74. Question 51. How Will You Deploy An Ssis Package In Testing, Staging & Production Environments?

    Answer :

    The dtutil is the command prompt utility which is used to manage SSIS packages.
    This utility can copy, move, delete or verify the existence of a package.