SQL Server

1. List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:
  • Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
  • Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
  • Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.
  • Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.
2. What is UNION in SQL Server? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

3. What is SQL Profiler?

SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. OR
SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.
For More SQL Profiler

4. What are the different types of JOIN clauses supported in  SQL.

ANSI-standard SQL specifies five types of JOIN clauses as follows:

INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

5. What is Self Join and why is it required?

Self Join is the act of joining one table with itself. Self Join is often very useful to convert a hierarchical structure into a flat structure.
In our employee table example above, we have kept the manager ID of each employee in the same row as that of the employee. Now, suppose if we need to print out the names of the manager of each employee right beside the employee, we can use self join. See the example below:
Example -
WHERE e.mgr_id = m.id (+)

SELECT count(*) AS total FROM orders;
| total |
|  100  |

SELECT count(*) AS cust_123_total FROM orders WHERE customer_id = '123';

| cust_123_total |
|       15       |
Given the above query results, what will be the result of the query below?

SELECT count(*) AS cust_not_123_total FROM orders WHERE customer_id <> '123'

The obvious answer is 85 (i.e, 100 - 15). However, that is not necessarily correct. Specifically, any records with a customer_id of NULL will not be included in either count (i.e., they won’t be included in cust_123_total, nor will they be included in cust_not_123_total). For example, if exactly one of the 100 customers has a NULL customer_id, the result of the last query will be:
+--------- ----------+
| cust_not_123_total |
|         84         |

7. What is the difference between the WHERE and HAVING clauses?

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BYis used:
 - The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
 - The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

8. What is CHECK constraint?
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

9.  What is sub query and its properties?
A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as
  • A sub query should not have order by clause
  • A sub query should be placed in the right hand side of the comparison operator of the main query
  • A sub query should be enclosed in parenthesis because it needs to be executed first before the main query
  • More than one sub query can be included
10. What are the types of sub query?
There are three types of sub query –
  • Single row sub query which returns only one row
  • Multiple row sub query which returns multiple rows
  • Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
11. What is COALESCE in SQL Server? and what is difference between COALESCE and ISNULL?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

In SQL Server (Transact-SQL), the COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

Select COALESCE(empno, empname, salary) from employee;

ISNULL() Function
The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.

1.  The COALESCE() function is based on the ANSI SQL standard whereas ISNULL function is a Transact-SQL function.
2.  An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
3. The ISNULL() function contains only two parameters. The COALESCE() function contains multiple parameters. If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions.

Select COALESCE(managerId,'bhanu',199) from [Practic].[dbo].[employee1]

  Select ISNULL(managerId,'bhanu') from [Practic].[dbo].[employee1]

In above queries managerId is 'int' type. so ISNULL will give error because of data type mismatch but  COALESCE  non null records.

12. What is Index? Define Clustered and Non-Clustered index.
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. 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.
A table or view can contain the following types of indexes:   For more Index
  • Clustered
    • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
    • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
  • Nonclustered
    • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
    • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
13. How to delete Duplicate Records?

With CTE AS (
  select *,RN = ROW_NUMBER() over(partition by id Order by id) from Employee1

  delete from CTE where RN>1

id - is column by which u find duplicates

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records.
FROM MyTable
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

14. How to select top nth Records?

Example top 5th from down

select top 1 id from (select top 4 id from employee1 order by id desc)sub order by id asc

15. Give example of self join with following table structure.

  FROM [Practic].[dbo].[employee1]
  select e.ename, m.ename from employee1 e inner join employee1 m on e.managerId = m.id

16. What is the difference between the RANK() and DENSE_RANK() functions? Provide an example?

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

17. Write query to select all the Even and Odd number records from a table.

To select all the even number records from a table:
Select * from table where id % 2 = 0 
To select all the odd number records from a table:
Select * from table where id % 2 != 0

18. What is difference between Truncate, Delete and Drop?

Truncate - 
1. Truncate is used to delete the content of Table and free the space.
2. This Action can not be rolled back.

Delete -
1. Delete is used to delete records from a Table.
2. This action can be rolled back.

Drop - 
1. Drop is used to remove the object from Database.
2. can not rolled back.

19. replace male to female and female to male in below table.

 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
 Gender varchar(100) null
INSERT INTO Employee(EmpID,EmpName,Salary,Address,Gender) VALUES(1,'Mohan',12000,'Noida','Male')
INSERT INTO Employee(EmpID,EmpName,Salary,Address,Gender) VALUES(2,'Pavan',25000,'Delhi','Male')
INSERT INTO Employee(EmpID,EmpName,Salary,Address,Gender) VALUES(3,'Amit',22000,'Dehradun','Female')
INSERT INTO Employee(EmpID,EmpName,Salary,Address,Gender) VALUES(4,'Sonu',22000,'Noida','Female')
INSERT INTO Employee(EmpID,EmpName,Salary,Address,Gender) VALUES(6,'Deepak',28000,'Gurgaon','Female')

SELECT * FROM Employee


UPDATE    employee
gender = CASE gender WHEN 'male' THEN 'female' WHEN 'female' THEN 'male'
ELSE gender
SELECT * FROM Employee

20Triggers-in-sql-server/      Trigger-example   Trigger Alternatives/

21. Delete duplicate data from Table.

First method
if only one duplicate record and some unique primary key is available in table

delete from tablename where uniqueId in (select max(id) from tablename group by duplicate column having count(duplicate column) > 1)

if there are more than one duplicate record no primary key

create table #temp(id int ,name varchar(30))
insert into #temp select distinct * from testing
delete from testing
insert into testing select * from #temp
drop table #temp

select * from testing


select distinct * into #temp from testing
delete from testing
insert into testing select * from #temp
drop table #temp

select * from testing

To delete all duplicate data
  delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)

22. For Store Proc Performance check below.

1. Set NOCount ON |OFF

 - it wont allow to show some messages like - 0 rows affected.

2. Set ANSI_Nulls ON|OFF

 - it affects with = and <> comparision with NULL value in DB

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values incolumn_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <>NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

3. Set Quoted_Identifier ON|OFF

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

4. WITH(NOLOCK) fetch values from table with(nolock) keyword.

5. use Select count(1) instead of count(*) for count.

6. do not use prefix sp while creating stored procedures.

7. use indexing on tables.

23. Difference between CTE and Temp Table and Table Variable

24. sql-server-how-to-write-stored-procedures-with-out-parameter   how-to-use-trycatch-inside-sp   sing-try-catch-in-sql-server-stored-procedures/

25. Views-in-sql-server/    Views-In-SQL-Server

26. Function-operation-in-sql-database/   Different-types-of-sql-server-functions.

27. Row_Number, RANK, DENSE_Rank

USE [Practice]

/****** Object: Table [dbo].[Employee] Script Date: 31-05-2019 10:50:47 ******/


CREATE TABLE [dbo].[Employee] (
    [Id]     INT          NOT NULL,
    [Name]   VARCHAR (50) NULL,
    [Salary] VARCHAR (50) NULL

INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (1, N'bhanu',N'100')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (2, N'bhanu',N'200')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (3, N'Faizan',N'200')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (4, N'Faizan',N'200')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (5, N'Kap', N'300')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (6, N'Jap', N'100')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (7, N'Abhi', N'500')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (8, N'Shan', N'200')

;with CTE AS(select *, ROW_NUMBER() over(partition by Name,Salary order by Id) asRN from Employee)
select * from CTE
--select * from CTE where RN>1     -- here you can delete duplicate records where RN>1
-- delete from CTE where RN>1

select *, ROW_NUMBER() over(order by Id) as RN from Employee
select *, ROW_NUMBER() over(partition by Name,Salary order by Id) as RN fromEmployee

select *, RANK() over(order by Name) as rn from Employee

select *, RANK() over(partition by Name order by Name) as rn from Employee

select *, Dense_RANK() over(order by Name) as rn from Employee
select *, Dense_RANK() over(partition by Salary order by Name) as rn fromEmployee

-- find nth highest salary - always use dense rank as rank will skip some numbers.
-- so it will not give any result for those skipped numbers
select * from Employee
;with CTE1 as (select *, RANK() over (order by salary desc) as RN from Employee)
select top 1 * from CTE1 where RN=4   -- 4th Highest from highes to lowest

select * from Employee
;with CTE1 as (select *, DENSE_RANK() over (order by salary desc) as RN fromEmployee)
select top 1 * from CTE1 where RN=2  -- 2nd highest from

For More - Rank-denserank-and-rownumber-functions-in-sql-server/

28. Basics-of-cursors

Good Interview-questions