39 SQL Server Interview Questions (ANSWERED) Devs Need To Know

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.

• A PRIMARY KEY constraint is a unique identifier for a row within a database table.
• Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.
• The primary key constraints are used to enforce entity integrity.

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

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.

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;

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.

Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption.

There is a number of normalization levels from 1. normal form through 5. normal form. Each normal form describes how to get rid of some specific problem.

By having a database with normalization errors, you open the risk of getting invalid or corrupt data into the database. Since data "lives forever" it is very hard to get rid of corrupt data when first it has entered the database.

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

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.

• Atomicity: It ensures all-or-none rule for database modifications.
• Consistency: Data values are consistent across the database.
• Isolation: Two transactions are said to be independent of one another.
• Durability: Data is not lost even at the time of server failure.

The difference between SP and UDF is listed below:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

Simply group on both of the columns:

SELECT
    name, email, COUNT(*) as CountOf
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

or using MERGE:

MERGE INTO YourTable T
   USING other_table S 
      ON T.id = S.id
         AND S.tsql = 'cool'
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

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

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.

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; 

• With a Clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index. A clustered index means you are telling the database to store close values actually close to one another on the disk.
• With a Non Clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.
• It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
• Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

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

It is the process of improving the performance of the database by adding redundant data.

In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.

For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

Cursors are a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as such.

However, while they may be more comfortable to use for programmers accustomed to writing While Not RS.EOF Do ..., they are typically a thing to be avoided within SQL Server. If you can write a query without the use of cursors, you give the optimizer a much better chance to find a fast way to implement it.

Consider example:

DECLARE @eName varchar(50), @job varchar(50)

DECLARE MynewCursor CURSOR -- Declare cursor name

FOR
Select eName, job FROM emp where deptno =10

OPEN MynewCursor -- open the cursor

FETCH NEXT FROM MynewCursor
INTO @eName, @job

PRINT @eName + ' ' + @job -- print the name

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM MynewCursor 
INTO @ename, @job

PRINT @eName +' ' + @job -- print the name

END

CLOSE MynewCursor

DEALLOCATE MynewCursor

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works correctly.

As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. It is a rather primitive rule, but it is useful in more than 90% of the cases.

While you're at it, you may want to re-write your query using ANSI version of the join:

SELECT  L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)

This would eliminate WHERE that was used as a theta join condition.

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