SQL Server

What are the difference between DDL, DML and DCL commands?
DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
o CREATE - to create objects in the database
o ALTER - alters the structure of the database
o DROP - delete objects from the database
o TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
o COMMENT - add comments to the data dictionary
o RENAME - rename an object
DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
o SELECT - retrieve data from the a database
o INSERT - insert data into a table
o UPDATE - updates existing data within a table
o DELETE - deletes all records from a table, the space for the records remain
o MERGE - UPSERT operation (insert or update)
o CALL - call a PL/SQL or Java subprogram
o EXPLAIN PLAN - explain access path to data
o LOCK TABLE - control concurrency
DCL

Data Control Language (DCL) statements. Some examples:
o GRANT - gives user's access privileges to database
o REVOKE - withdraw access privileges given with the GRANT command
TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
o COMMIT - save work done
o SAVEPOINT - identify a point in a transaction to which you can later roll back
o ROLLBACK - restore database to original since the last COMMIT
o SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use


Why is a UNION ALL faster than a UNION?

UNION ALL faster than a UNION because for union operation server needs to remove the duplicate values but for union all its not. That why the UNOIN ALL is faster than UNION Operation. It is recommended that if you know that the union set operation never returns duplicate values than you must use UNION ALL instead of UNION.

What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
Define candidate key, alternate key, and composite key.
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row prcessing of the resultsets.Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing,

What is a join and explain different types of joins?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is a Stored Procedure?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

What is the basic difference between clustered and a non-clustered index?
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

What are cursors?
Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.

Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.
Can we use Truncate command on a table which is referenced by FOREIGN KEY?
No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. Can you link only other SQL Servers or any database servers such as Oracle?
We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that Microsoft provides to add it as a linked server to the sql server group.

How do you troubleshoot SQL Server if its running very slow?
First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes.

What is log shipping?
Can we do logs hipping with SQL Server 7.0 - Logs hipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.
Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?
For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db.

What is BCP? When do we use it?
BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.


The Different Types of Joins in SQL Server
1. Inner join or Equi join
2. Outer Join
3. Cross join
Let's suppose we have two tables Employee and Department whose description is given below:-
Collapse
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL,
[Departmentid] [INT]
)
CREATE TABLE [dbo].[Department](
[Departmenttid] [int] IDENTITY (1, 1) NOT NULL primary key,
[DepartmentName] [nvarchar](255) NOT NULL
)
After the creation of the tables we need to insert the data into these tables. To insert the data the following queries are used:-
Collapse
insert into Employee
(EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A001','Samir','Singh','samir@abc.com',2,2)
insert into Employee
(EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A002','Amit','Kumar','amit@abc.com',1,1)
insert into Employee (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A003','Neha','Sharma','neha@abc.com',1,2)
insert into Employee (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A004','Vivek','Kumar','vivek@abc.com',1,NULL)

insert into Department(DepartmentName)
values('Accounts')
insert into Department(DepartmentName)
values('Admin')
insert into Department(DepartmentName)
values('HR')
insert into Department(DepartmentName)
values('Technology')
Inner Join
This type of join is also known as the Equi join. This join returns all the rows from both tables where there is a match. This type of join can be used in the situation where we need to select only those rows which have values common in the columns which are specified in the ON clause.
Now, if we want to get employee id, employee first name, employee's last name and their department name for those entries employee which belongs to at least one department, then we can use the inner join.
Query for Inner Join
Collapse
SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DepartmentName
FROM Employee Emp
INNER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid
Result
Collapse
Empid EmpFirstName EmpLastName DepartmentName
1 Samir Singh Admin
2 Amit Kumar Accounts
3 Neha Sharma Admin
Explanation
In this query, we used the inner join based on the column "Departmentid" which is common in both the tables "Employee" and "Department". This query will give all the rows from both the tables which have common values in the column "Departmentid". Neha Sharma and Samir Singh has the value "2" in the Departmentid column of the table Employee. In the Department table, the Department "Admin" has the value "2" in the Departmentid column. Therefore the above query returns two rows for the department "Admin", one for Neha Sharma and another for Samir Singh.
Self Join
Sometime we need to join a table to itself. This type of join is called Self join. It is one of the type of inner join where both the columns belong to the same table. In this Join, we need to open two copies of a same table in the memory. Since the table name is the same for both instances, we use the table aliases to make identical copies of the same table to be open in different memory locations. For example if we need to get the employee name and their manager name we need to use the self join, since the managerid for an employee is also stored in the same table as the employee.
Query for the Self Join
Collapse
SELECT Emp1.Empid,
Emp1.EmpFirstName+' '+Emp1.EmpLastName as EmployeeName,
Emp2.EmpFirstName+' '+Emp2.EmpLastName as ManagerName
FROM Employee Emp1
INNER JOIN Employee Emp2
ON Emp1.Managerid=Emp2.Empid
Result
Collapse
Empid EmployeeName ManagerName
1 Samir Singh Amit Kumar
2 Amit Kumar Samir Singh
3 Neha Sharma Samir Singh
4 Vivek Kumar Samir Singh
Explanation
Since the employee and the manager information is contained in the same table (Employee, since both are employees), we have to use the Self Join. In the self join query, we make two copies of the table Employee by using the aliases Emp1 and Emp2 and then use Inner join between them by using the managerid column of the Emp1 and Empid column of the table Emp2.In this example, we use managerid and empid columns of the Employee table since the employee id of the manager of an employee is stored in the managerid of the Employee table.
Outer Join
This type of join is needed when we need to select all the rows from the table on the left (or right or both) regardless of whether the other table has common values or not and it usually enter null values for the data which is missing.
The Outer join can be of three types
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
Left Outer Join
If we want to get employee id, employee first name, employes last name and their department name for all the employees regardless of whether they belong to any department or not,then we can use the left outer join. In this case we keep the Employee table on the left side of the join clause. It will insert NULL values for the data which is missing in the right table.
Query for Left Outer Join
Collapse
SELECT Emp.Empid,
Emp.EmpFirstName,
Emp.EmpLastName,
Dept.DepartmentName
FROM Employee Emp
LEFT OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid
Result
Collapse
Empid EmpFirstName EmpLastName DepartmentName
1 Samir Singh Admin
2 Amit Kumar Accounts
3 Neha Sharma Admin
4 Vivek Kumar NULL
Explanation
Since we have use the Left Outer Join, this query will give the information (Employee id, Employee first name, Employee last name and their department name) for all the employee from the Employee table and it insert NULL value in the DepartmentName column where the employee does not belong to any department. In the table Employee, since Samir Singh, Amit Kumar and Neha Sharma have values in their Departmentid column, therefore the above query will display their Department name under the heading DepartmentName.But since Vivek Kumar doesn't belongs to any department and has null value in the column Departmentid therefore the above query will Display the NULL value under the column heading DepartmentName.
Right Outer Join
If we want to get all the departments name and employee id, employee first name, and employees last name of all the employees belonging to the department regardless of whether a department have employees or not, then we can use the right outer join. In this case we keep the Department table on the right side of the join clause. It will insert NULL values for the data which is missing in the left table (Employee).
Query for Right Outer Join
Collapse
SELECT Dept.DepartmentName,
Emp.Empid, Emp.EmpFirstName,
Emp.EmpLastName
FROM Employee Emp
RIGHT OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmentid
Result
Collapse
DepartmentName Empid EmpFirstName EmpLastName
Accounts 2 Amit Kumar
Admin 1 Samir Singh
Admin 3 Neha Sharma
HR NULL NULL NULL
Technology NULL NULL NULL
Explanation
Since we have use the Right Outer Join, this query will join the two tables Employee and Department on the basis of the values contains in the column Departmenttid. It will give the department name from the Department table and the Employee id, Employee first name, and Employee last name of all the employees that belong to that department. If any department does not contain any employee then it insert NULL value in the columns coming from the Employee table. Since no employee is connected to the departments HR and Technology, this query will display NULL values under the columns Empid, EmpFirstName and EmpLastName for the Departments HR and Technology. Since the department Admin and Accounts contains the employees therefore the columns Empid, EmpFirstName and EmpLastName contains the information, employee id, employee first name and employee last name respectively.
Full Outer Join
If we want to get all the departments name and the employee id, employee first name, employes last name of all the employees regardless of whether a department have employees or not, or whether a employee belong to a department or not, then we can use the full outer join. It will insert null values for the data which is missing in both the tables.
Query for Full Outer Join
Collapse
SELECT Emp.Empid,
Emp.EmpFirstName,
Emp.EmpLastName,
Dept.DepartmentName
FROM Employee Emp
FULL OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid
Result
Collapse
Empid EmpFirstName EmpFirstName DepartmentName
1 Samir Singh Admin
2 Amit Kumar Accounts
3 Neha Sharma Admin
4 Vivek Kumar NULL
NULL NULL NULL HR
NULL NULL NULL Technology
Explanation
Since we have used the Full Outer Join, this query will give the name of all the departments from the Department table and the Employee id, Employee first name, Employee last name of all the employees from the Employee table. If any department does not contain any employee, then it insert NULL value in the columns Empid, EmpFirstName, EmpLastName columns and if any employee doesn't belong to any department then it insert NULL value in the column DepartmentName. Here since Vivek Kumar doesn't belong to any department, the result displays NULL value under the column DepartmentName. Since the departments HR and Accounts don't contain any employees, the result of the above query displays NULL values under the columns Empid, EmpFirstName and EmpLastName for the departments HR and Technology..
Cross Join
This join combines all the rows from the left table with every row from the right table. This type of join is needed when we need to select all the possible combinations of rows and columns from both the tables. This type of join is generally not preferred as it takes lot of time and gives a huge result that is not often useful.
Query for the Cross Join
Collapse
SELECT Emp.Empid,
Emp.EmpFirstName,
Emp.EmpLastName,
Dept.DepartmentName
FROM Employee Emp
CROSS JOIN Department dept
Results
Collapse
Empid EmpFirstName EmpLastName DepartmentName
1 Samir Singh Accounts
2 Amit Kumar Accounts
3 Neha Sharma Accounts
4 Vivek Kumar Accounts
1 Samir Singh Admin
2 Amit Kumar Admin
3 Neha Sharma Admin
4 Vivek Kumar Admin
1 Samir Singh HR
2 Amit Kumar HR
3 Neha Sharma HR
4 Vivek Kumar HR
1 Samir Singh Technology
2 Amit Kumar Technology
3 Neha Sharma Technology
4 Vivek Kumar Technology
Explanation
This Cross Join query will give combines all the rows from the Employee table with every row of the Department table. Since the Employee table contains 4 rows and the Department table contains 4 rows, therefore this result will returns 4*4=16 rows. This query doesn't contain any ON clause.