Top 52 T-SQL Interview Questions And Answers

Q: Name 5 commands that can be used to manipulate text in T-SQL code

* CHARINDEX( findTextData, textData, \[startingPosition\] ) - Returns the starting position of the specified expression in a character string. The starting position is optional. * LEFT( character\_expression , integer\_expression ) - Returns the left part of a character string with the specified number of characters. * LEN( textData ) - Returns integer value of the length of the string, excluding trailing blanks. * LOWER ( character\_expression ) - Returns a character expression after converting uppercase character data to lowercase. * LTRIM( textData) - Removes leading blanks. PATINDEX( findTextData, textData ) - Returns integer value of the starting position of text found in the string. * REPLACE( textData, findTextData, replaceWithTextData ) - Replaces occurrences of text found in the string with a new value. * REPLICATE( character\_expression , integer\_expression ) - Repeats a character expression for a specified number of times. * REVERSE( character\_expression ) - Returns the reverse of a character expression. * RTRIM( textData) - Removes trailing blanks. SPACE( numberOfSpaces ) - Repeats space value specified number of times. * STUFF( textData, start , length , insertTextData ) - Deletes a specified length of characters and inserts another set of characters at a specified starting point. * SUBSTRING( textData, startPosition, length ) - Returns portion of the string. * UPPER( character\_expression ) - Returns a character expression with lowercase character data converted to uppercase.

Question 1

Is it possible to rename a database?  If so, how would you rename the database?

Accepted Answer

*   Yes - Databases can be renamed in similar manners as other relational database objects.
    *   It is possible to rename a database by one of these options:
        *   Issue the sp\_renamedb system stored procedure.
        *   Issue the sp\_rename system stored procedure and specify 'database' as the parameter.
        *   Use Management Studio by right clicking on the database and selecting the 'Rename' option.

Question 2

Mention what is `TOP` in T-SQL?

Accepted Answer

`TOP` limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When TOP is used in combination with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, it retrieves the first N number of rows in an undefined order.

Question 3

What is _Blocking_?

Accepted Answer

SQL Server **blocking** occurs when one connection holds a lock on a record and another connection tries to fetch the record or update the record.

Question 4

When should I use _primary key_ or _index_?

Accepted Answer

Basically, a primary key is (at the implementation level) a special kind of index. Specifically:

* A table can have only one primary key, and with very few exceptions, every table should have one.
* A primary key is implicitly `UNIQUE` - you cannot have more than one row with the same primary key, since its purpose is to uniquely identify rows.
* A primary key can never be `NULL`, so the row(s) it consists of must be NOT NULL

A table can have multiple indexes, and indexes are not necessarily `UNIQUE`. Indexes exist for two reasons:

- To enforce a uniquness constraint (these can be created implicitly when you declare a column UNIQUE)
 - To improve performance. Comparisons for equality or "greater/smaller than" in WHERE clauses, as well as JOINs, are much faster on columns that have an index. But note that each index decreases update/insert/delete performance, so you should only have them where they're actually needed.

Question 5

Could you explain the difference between _Primary Key_ and _Unique Index_?

Accepted Answer

The differences between the two are:

* Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
* There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
* Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.

Question 6

Name 5 commands that can be used to manipulate text in T-SQL code

Accepted Answer

*   CHARINDEX( findTextData, textData, $$startingPosition$$ ) - Returns the starting position of the specified expression in a character string. The starting position is optional.
*   LEFT( character\_expression , integer\_expression ) - Returns the left part of a character string with the specified number of characters.
*   LEN( textData ) - Returns integer value of the length of the string, excluding trailing blanks.
*   LOWER ( character\_expression ) - Returns a character expression after converting uppercase character data to lowercase.
*   LTRIM( textData) - Removes leading blanks. PATINDEX( findTextData, textData ) - Returns integer value of the starting position of text found in the string.
*   REPLACE( textData, findTextData, replaceWithTextData ) - Replaces occurrences of text found in the string with a new value.
*   REPLICATE( character\_expression , integer\_expression ) - Repeats a character expression for a specified number of times.
*   REVERSE( character\_expression ) - Returns the reverse of a character expression.
*   RTRIM( textData) - Removes trailing blanks. SPACE( numberOfSpaces ) - Repeats space value specified number of times.
*   STUFF( textData, start , length , insertTextData ) - Deletes a specified length of characters and inserts another set of characters at a specified starting point.
*   SUBSTRING( textData, startPosition, length ) - Returns portion of the string.
*   UPPER( character\_expression ) - Returns a character expression with lowercase character data converted to uppercase.

Question 7

Explain what are the differences between SQL and T-SQL?

Accepted Answer

* SQL is a query language to operate on sets.
* TSQL is a proprietary procedural language used by Microsoft in SQL Server.

T-SQL adds a number of features that are not available in SQL.

This includes procedural programming elements and a local variable to provide more flexible control of how the application flows. A number of functions were also added to T-SQL to make it more powerful; functions for mathematical operations, string operations, date and time processing, and the like. These additions make T-SQL comply with the Turing completeness test, a test that determines the universality of a computing language. SQL is not Turing complete and is very limited in the scope of what it can do.

Another significant difference between T-SQL and SQL is the changes done to the DELETE and UPDATE commands that are already available in SQL. With T-SQL, the DELETE and UPDATE commands both allow the inclusion of a FROM clause which allows the use of JOINs. This simplifies the filtering of records to easily pick out the entries that match a certain criteria unlike with SQL where it can be a bit more complicated.

Question 8

Mention what are the limitations of `IDENTITY` column?

Accepted Answer

The limitations of the IDENTITY column is that column values cannot be updated once generated. Also, it may require to specify this column as a PRIMARY KEY, as such, there is a possibility of duplication of values within a table.  Identity property is applicable for integer based column only.

Question 9

What’s the difference between a _Local Temp Table_ and a _Global Temp Table_?

Accepted Answer

* **Local tables** are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. 
* **Global temp** tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.

Question 10

What are the new error handling commands introduced with SQL Server 2005 and beyond?

Accepted Answer

*   The new commands are TRY and CATCH.
*   Although they do not directly replace any specific command, in many respects the TRY and CATCH has been used over the RAISERROR command.
*   The TRY block is for the business logic and the CATCH logic is for capturing the error.

Top 52 T-SQL Interview Questions

Mention what is TOP in T-SQL?

Is it possible to rename a database? If so, how would you rename the database?

What is Blocking?

What is the difference between Data Definition Language (DDL) and Data Manipulation Language (DML)?

Mention what is OFFSET-FETCH filter in tsql?

Mention what is Subquery?

What are the two commands to remove all of the data from a table? Are there any implications with the specific commands?

What are the three ways that Dynamic SQL can be issued?

Mention what are the limitations of IDENTITY column?

What’s the difference between a Local Temp Table and a Global Temp Table?

When should I use primary key or index?

Name 5 commands that can be used to manipulate text in T-SQL code

Could you explain the difference between Primary Key and Unique Index?

What are the new error handling commands introduced with SQL Server 2005 and beyond?

Explain what are the differences between SQL and T-SQL?

What is TSQL Window functions?

What is a Cursor and how does it work?

What’s the difference between Azure SQL Database and Azure SQL Managed Instance?

What are the advantages of using Stored Procedures?

What are bitwise operators and what is the value from a database design perspective?

Mention what are ROLLUP and CUBE in T-SQL?

How do I perform an IF…THEN in an SQL SELECT?

What's the difference between TRUNCATE and DELETE in SQL?

How can you delete duplicate records in a table with no primary key?

Mention what are the Join Types in TSQL?

What are types of XML indexes in SQL Server?

What two commands were released in SQL Server 2005 related to comparing data sets from two or more separate SELECT statements?

Mention what is uncommittable transactions?

What are Row Constructors?

In what version of SQL Server were synonyms released, what do synonyms do and when could you make the case for using them?

Is there a difference between T-SQL linked server and a synonym?

Mention what does the T-SQL command IDENT_CURRENT does?

Explain Function vs. Stored Procedure in SQL Server

What are the practical differences between COALESCE() and ISNULL(,'')?

Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services? If so, what are the commands?

What is a Filegroup?

What is the difference between PARTITION BY and GROUP BY

Name some types of Triggers

What is the difference between EXEC vs sp_executesql?

What do Clustered and Non Clustered index actually mean?

What is the use of GO in Transact SQL?

What is a Linked Server?

From a T-SQL perspective, how would you prevent T-SQL code from running on a production SQL Server?

What is the native system stored procedure to issue a command against all databases?

What are the best practices for using a GUID as a primary key, specifically regarding performance?

Why you should never use GUIDs as part of clustered index?

Is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?

Provide an example of Left Outer Join with Exclusions

How do I UPDATE from a SELECT in SQL Server?

How can you capture the length of a column when it is a Text, NText and/or Image data type?

How does TRUNCATE and DELETE operations effect Identity?

Insert results of a stored procedure into a temporary table

Mention what is `TOP` in T-SQL?

Mention what is `OFFSET-FETCH` filter in tsql?

Mention what are the limitations of `IDENTITY` column?

Mention what are `ROLLUP` and `CUBE` in T-SQL?

How do I perform an `IF…THEN` in an SQL `SELECT`?

What's the difference between `TRUNCATE` and `DELETE` in SQL?

What two commands were released in SQL Server 2005 related to comparing data sets from two or more separate `SELECT` statements?

Mention what does the T-SQL command `IDENT_CURRENT` does?

What are the practical differences between `COALESCE()` and `ISNULL(,'')`?

What is the difference between `PARTITION BY` and `GROUP BY`

What is the difference between `EXEC` vs `sp_executesql`?

What is the use of `GO` in Transact SQL?

What are the best practices for using a `GUID` as a primary key, specifically regarding performance?

Is it correct/best practice to have the `TRY/CATCH` block inside the transaction or should the transaction be inside the `TRY` block?

How do I `UPDATE` from a `SELECT` in SQL Server?

How can you capture the length of a column when it is a `Text`, `NText` and/or `Image` data type?

How does `TRUNCATE` and `DELETE` operations effect Identity?