Top SQL Server interview questions and answers for developer

1. WHERE

- Filter data from a result, usually filter columns available in the table. 

- It filters before any groups are made.

- Can't use the aggregate function to filter.

2. HAVING

- Used to filter values from a group.

- Only use the aggregate function to filter.

- Can't using HAVING if not use to the GROUP BY clause.

- Performance is SLOW because the aggregate function needs to calculation.

1. IN

- Works on the list data set.

- It's doesn’t work on subqueries resulting in virtual tables with multiple columns.

- Compares every value in the result list.

- Performance is comparatively SLOW for larger resultset of a subquery.

2. EXISTS

- Works only on Virtual tables.

- It is used with co-related queries.

- Exits comparison when the match is found.

- Performance is comparatively FAST for larger resultset of the subquery.

It's very simple, you can insert data for a table base on a SELECT query from another table, look example below:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition;

We can use GROUP BY with COUNT aggregate function to count if it's greater than 1.

Case 1: Duplicate records only one field.

SELECT DisplayName, COUNT(PhoneNumber) 
FROM Customer
GROUP BY DisplayName
HAVING COUNT(PhoneNumber) > 1 

Case 2: Duplicate records with many fields.

SELECT DisplayName, PhoneNumber, COUNT(*) 
FROM Customer
GROUP BY DisplayName, PhoneNumber
HAVING COUNT(*) > 1

INNER JOIN: Only get data existed from both two tables.

LEFT JOIN: Get full data from the left table (table A). If data from the right table is not existing will return a null value.

1. Clustered Index

- Only has a clustered index in a table.

- It's created on the primary key.

- Data retrieval is faster than the non-cluster index.

- Sort the records and store them physically according to the order

- Do not need extra space to store the logical structure.

2. Non-Clusted

- Quicker for insert and update operations than a clustered index.

- Can create many non-cluster indexes in a table,  the maximum of 999 non-clustered indexes per table as per limitations.

- Use extra space to store logical structure.

Why we have to create the index for a table in the SQL server?

An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

If not have the index when we select data, the SQL server has to lost many times to scan all data in the table.

What column data type is better to create an index?

- Number data always index better than other data types.

- Column has short content always index better than column has long content. Such as TitleNews(max length 100) and LongContent(max length 4000) column, TitleNews column always index better

UNION: Merge two or more data from Select clause, always remove the duplicate records.

UNION ALL: Merge two or more data from Select clause, display all duplicate records

1. Using NEWID() function with ORDER BY

SELECT TOP 5 [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers] 
ORDER BY NEWID()

2. Using RAND() function with ORDER BY

SELECT TOP 5 [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers] 
ORDER BY RAND()

-- Set value to variable
DECLARE @fullName varchar(30);         
SELECT @fullName = FullName         
FROM Customer        
WHERE CustomerID = 1000;   

-- get value from variable
SELECT @fullName AS 'FullName';   

OR

-- Set value to variable
DECLARE @fullName varchar(30);         
SELECT @fullName = (SELECT FullName   
FROM Customer   
WHERE CustomerID = 1000);  

-- get value from variable
SELECT @fullName AS 'FullName';  

1. Advantages

- Very secure, you can avoid attack SQL injection.

- Faster, because SP is pre-compiled and it is store in the server so when we call SP, we only determine SP name and parameter, this help request is faster.

- Support full CRUD

2. Disadvantages

- Because it stores in the server so it makes the server heavy and only executes it in that server.

1. DELETE

- Delete data from the table, can delete one or more records by using the WHERE clause. 

- Not reset Identity increase value.

- Commit and Rollback can be performed after DELETE statement.

2. TRUNCATE

- Delete all data from a table, can't delete some records.

- Reset Identity increase value to 0.

- Can not Rollback data after delete.

You can use DISTINCT clause. It helps remove all duplicate records. Duplicate means that all value of all column has to same. For example:

SELECT DISTINCT CustomerId, Name, ProductId, ProductName FROM Customer;

To search text, you can use LIKE keyword and % prefix to search:

- Select with FirstName start with "Jonh"?

 SELECT CustomerId, Name FROM Customer WHERE Name LIKE'Jonh%';

- Select with FirstName end with "Jonh"?

SELECT CustomerId, Name FROM Customer WHERE Name LIKE'%Jonh';

- Select with FirstName contain "Jonh"?

SELECT CustomerId, Name FROM Customer WHERE Name LIKE'%Jonh%';

1. VIEW

- Not store data.

- Can return data from many tables.

- It's read-only, it can not insert, update, delete data.

- It's helpful to make a report.

2. TABLE

- It's help store data

- Can manage data with insert, update, delete data.

What are Constraints in SQL Server?

Constraints in SQL Server are rules and restrictions applied on a column or a table such that unwanted data can't be inserted into tables. This ensures the accuracy and reliability of the data in the database. We can create constraints on single or multiple columns of any table. Constraints maintain the data integrity and accuracy in the table.

How many types of constraints provided in the SQL server?

SQL Server contains the following 6 types of constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Constraint
• Foreign Constraint

It's wrong in GROUP BY clause because all columns  in SELECT have to appear in GROUP BY. We can fix it as below:

SELECT UserId, UserName, AVG(Total) AS AvgTotal
FROM Invoices
GROUP BY Userid, UserName
HAVING AVG(Total) >= 10

OR remove column UserName in SELECT clause

SELECT UserId, AVG(Total) AS AvgTotal
FROM Invoices
GROUP BY Userid
HAVING AVG(Total) >= 10

Below is the order execute of clauses above:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY
10. TOP

1. Aggregate

Aggregate functions determine and calculate values from multiple columns in a table and return a single value.

• AVG(): Returns the average value from specified columns.
• COUNT(): Returns number of table rows.
• MAX(): Returns the largest value among the records.
• MIN(): Returns smallest value among the records.
• SUM(): Returns the sum of specified column values.

2. Scalar

Scalar functions are used to return a single value based on the input values.

• FORMAT(): Specifies the display format.
• GETDATE(): Get current date-time of system.
• LEN(): Get length of string text.
• ROUND(): Rounds up the decimal field value to a number.
• DATEDIFF(): Return the difference between two date values, in years.

1. What is the relationship?

This is a relationship between two tables through foreign key, it helps using joining to retrieve data from two or many tables.

2. How many types of relationships are there in the SQL server?

We have 3 relationships between two tables as below :

- ONE To ONE(1:1)

 For example, the relationship between Customer and Customer Detail, one customer only one information detail about them.

- ONE To MANY(1:n)

See the relationship between Customer and Order, one customer can make many orders from an economic website.

- MANY To MANY(n:n)

See the relationship between Customer and Product, you can see that a customer can buy many products but one product can buy by many customers.