29 T-SQL Interview Questions Devs Must Know (ANSWERED)

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.

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

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.

In TSQL OFFSET-FETCH filter is designed similar to TOP but with an extra element. It helps to define how many rows you want to skip before specifying how many rows you want to filter.

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

• Writing a query with parameters.
• Using EXEC.
• Using sp_executesql.

• TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
• The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

A window function is a function that's applied to a set of rows defined by a window descriptor and returns a single value for each row from the underlying query. The purpose of the window descriptor is to define the set of rows that the function should apply to. You provide the window specification using a clause called OVER.

SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
        ORDER BY ordermonth
        ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;

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.

• Use the DATALENGTH command to capture the length.
• The LEN command is invalid for Text, NText and Image data types.

If you want to just get number of characters excluding blanks you would use LEN() function, while in all other cases DATALENGTH(). Even LEN() documentation has an information that to get number of bytes to represent the extension you should use DATALENGTH().

Use the SET ROWCOUNT command.  So if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.

• You use a linked server to connect to a database on a different server.
• You use a synonym to specify the object (e.g. table) you want to access in SQL, it is like an alias.

You can let point a synonym to an object of a linked server, but you still need that linked server.

ROLLUP and CUBE are the grouping sets used along with GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.

Join Types in TSQL are,

• Inner join
• Outer join
• Left outer join
• Right outer join
• Left outer join with Exclusions
• Right outer join with Exclusions
• Full outer join
• Full outer joins with Exclusions
• Cross join

The TSQL command IDENT_CURRENT returns the last identity value produced for a specified table or view. The last identity value created can be for any session and any scope.

When an error occurs in a transaction within a TRY block, and if the error is not serious it enters into a status open and Uncommittable. In an uncommittable state, the transactions cannot perform any action that would generate a write to the transaction log.

This type of join lets you find the data in one table that doesn't exist in another table. It's an alternative to using NOT IN or NOT EXISTS in a WHERE clause like this:

SELECT p.PeopleID, p.Name
  FROM dbo.People p
  WHERE p.PeopleID NOT IN (SELECT n.PeopleID
    FROM dbo.PhoneNumbers n
    WHERE n.PeopleID IS NOT NULL); 

Here's how you can accomplish the same goal using a left outer join with an exclusion:

SELECT p.PeopleID, p.Name
  ,n.PhoneNumberID, n.PeopleID, n.Number
  FROM dbo.People p
LEFT JOIN dbo.PhoneNumbers n
  ON p.PeopleID = n.PeopleID
  WHERE n.PhoneNumberID IS NULL; 

• The bitwise operators in SQL Server are:
  • & (Bitwise AND)
  • ~ (Bitwise NOT)
  • | (Bitwise OR)
  • ^ (Bitwise Exclusive OR)
• From a database design perspective, bitwise operators can be used to store a complex set of criteria as a single value as opposed to having numerous lookup tables or numerous columns used as a 'flag' or condition indicator.

• COALESCE() - Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
• ISNULL() - Replaces NULL with the specified replacement value.

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently:

• COALESCE() is in the SQL '92 standard and supported by more different databases. If you go for portability, don't use ISNULL.
• COALESCE() can have multiple inputs and it will evaluate in order until one of them is not null such as COALESCE(Col1, Col2, Col3, 'N/A'). It's recommended to use this by MS instead of ISNULL()
• ISNULL() can only have one input, however it's been shown to be slightly faster than COALESCE.

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:

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

The difference between truncate and delete is listed below:

+----------------------------------------+----------------------------------------------+
|                Truncate                |                    Delete                    |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing     | We can Rollback after delete.                |
| Truncate.                              |                                              |
|                                        |                                              |
| Example:                               | Example:                                     |
| BEGIN TRAN                             | BEGIN TRAN                                   |
| TRUNCATE TABLE tranTest                | DELETE FROM tranTest                         |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
| ROLLBACK                               | ROLLBACK                                     |
| SELECT * FROM tranTest                 | SELECT * FROM tranTest                       |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table.      | Delete does not reset identity of table.     |
+----------------------------------------+----------------------------------------------+
| It locks the entire table.             | It locks the table row.                      |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language)      | Its DML(Data Manipulation Language)          |
| command.                               | command.                                     |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it.     | We can use WHERE to filter data to delete.   |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate.   | Trigger is fired.                            |
+----------------------------------------+----------------------------------------------+
| Syntax :                               | Syntax :                                     |
| 1) TRUNCATE TABLE table_name           | 1) DELETE FROM table_name                    |
|                                        | 2) DELETE FROM table_name WHERE              |
|                                        |    example_column_id IN (1,2,3)              |
+----------------------------------------+----------------------------------------------+