Question 1: What is the difference between Primary Key and Unique Key?
Answer: Following are the major differences between these two:
1. By default Primary key creates Clustered Index in the column whereas Unique key creates Non Clustered Index.
2. Primary key doesn’t allow NULL value whereas Unique key allow one NULL value.
3. We can have only one Primary key per table where as we can have multiple Unique key per table.
4. Primary key can be used in Foreign key relationships whereas Unique key cannot be used.
Question 2: What is the difference between correlated and non-correlated subquery in SQL?
Answer: Following are the differences between correlated and non-correlated subquery:
1. In case of correlated subquery inner query depends on outer query while in case of non-correlated query inner query or subquery doesn’t depends on outer query and run by its own.
2. In case of correlated subquery, outer query executed before inner query or subquery while in case of non-correlated subquery inner query executes before outer query.
3. Correlated sub queries are slower than non-correlated subqueries and should be avoided in favor of joins.
4. Common example of correlated subquery is using exits and not exists keyword while non-correlated query mostly use IN or NOT IN keywords.
Question 3: Can we have multiple unique, foreign, and/or primary keys in a single table?
Answer: A table can have multiple unique and foreign keys. However, a table can have only one primary key.
Question 4: What is the difference between Delete and Truncate command?
Answer: Both Delete and Truncate commands are used to remove rows from a table. There are many differences between these:
1. Truncate is faster than Delete.
2. Truncate doesn’t log whereas Delete logs an entry for every record deleted in Transaction Log.
3. We can rollback the Deleted data whereas Truncated data cannot be rolled back.
4. Truncate resets the Identity column whereas Delete doesn’t.
5. Delete command can have WHERE clause whereas for Truncate we cannot have WHERE Clause.
6. Delete activates TRIGGER whereas TRUNCATE cannot.
7. Truncate is a DDL statement whereas Delete is DML statement.
Question 5: Difference between Stored Procedure and User Defined Function (UDF) in SQL Server?
1. Stored Procedure may or not return a value whereas for a function its must to return a value.
2. Function doesn’t allow to use try catch block whereas we can use try catch block in stored procedure.
3. Stored procedure can call a function whereas a function cannot call a stored procedure.
4. Stored procedure allow to use transaction whereas function doesn’t allow it.
5. Stored procedure allow DML statements in it whereas function doesn’t allow DML statement.
6. Stored procedure can have input/output parameters whereas function can have only input parameters.
7. Stored procedure cannot be called from a SELECT statement whereas a function can be called from SELECT statement.
8. Function returning a Table can be used as another rowset, and can be used in joins with other tables whereas a Stored Procedure cannot be used like this.
9. Stored procedure can use table variables as well as temporary tables whereas a function cannot use temporary tables.
Question 6: What is the difference between WHERE and HAVING clause in SQL Server?
1. Where clause can be used with other than Select statement whereas Having can be used only with Select statement.
2. Where applies to each and every row whereas Having applies to summarized rows (summarized with GROUP BY).
3. When Where and Having clause are used together in a Select query with aggregate function, Where clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, Having clause is used to filter groups based upon condition specified.
4. Where clause is used before GROUP BY clause and HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query.
Question 7: What is the difference between TRUNCATE TABLE and DROP TABLE command?
Answer: The difference between Truncate and DROP Table is that, after executing Truncate command the entire data of table is removed but the structure is intact, but in case of DROP command both the contents and structure are removed.
Question 8: Difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT.
Answer: Difference is explained below:
@@IDENTITY() returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session, while it is not limited to the current scope. It will return the last identity value that is either explicitly created (created by any trigger or user defined function).
SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection and within the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY returns the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created (not includes the identity created by any trigger or user defined function).
IDENT_CURRENT(‘tablename’) returns the last IDENTITY value produced in a table. It does not depend on any session or scope, instead it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
Question 9: Difference between Clustered and Non-Clustered index.
1. A clustered index determines the order in which the rows of the table will be stored on disk, while a non-clustered index has no effect on which the order of the rows will be stored.
2. We can have only one clustered index per table, while we can have multiple non-clustered index per table.
3. Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in it’s leaf nodes.
Question 10: How many types of joins are available in sql server?
Answer: Following three types of joins are supported in sql server:
1. Inner Join: Returns only those rows which exists or match in both tables.
2. Outer Join: There are three sub types of Outer Join:
- 2.1 Left Outer Join: Returns all rows from left table and only matching rows from right table.
- 2.2 Right Outer Join: Returns only matching rows from left table but all rows are right table.
- 2.3 Full Outer Join: Return all matching/non-matching rows from both the tables.
3. Cross Join: This join is a cartesian join and doesn’t need any condition to join the tables. Result of this join is the cartesian product of both tables.
Question 11: What is a trigger? What are the advantages and disadvantages of trigger?
Answer: Triggers are basically special type of stored procedures which are executed automatically whenever a DDL or DML command statement related with the trigger is executed.
Advantages of Trigger:
1) Triggers can be used as an alternative method for implementing integrity check.
2) By using triggers, business rules and transactions are easy to store in database and can be used consistently even if there are future updates to the database.
3) It controls on which updates are allowed in a database.
4) When a change happens in a database a trigger can adjust the change to the entire database.
5) Triggers are used for calling stored procedures.
Disadvantages of Trigger:
1) Programmers don’t have full control: Since business rules are hidden, programmers don’t have full control over the database. BY this, a program can generate several actions.
2) Increase in complexity: Triggers increase the complexity of a database as they have hard coded rules already implemented.
3) Decrease in performance of the database: By the complex nature of the database programs take more time to execute and there can be hidden performance downtimes.
Question 12: What are the advantages of Stored Procedures?
1. Stored procedure can reduce network traffic and latency, boosting application performance.
2. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
3. Stored procedures help promote code reuse.
4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
5. Stored procedures provide better security to your data.
Question 13: How many types of locks are there in sql server?
Answer: There are mainly three types of locks in sql server:
1. Shared Lock: Used for operations that doesn’t changes data. For example, SELECT statement.
2. Update Lock: Used when sql server intends to modify a page, and later promotes the update lock to exclusive lock before actually making the changes.
3. Exclusive Lock: Used for data modification operations. For example, INSERT, UPDATE or DELETE.
Question 14: Write a query to select Nth highest salary and name from employee table without using TOP keyword.
Answer: SELECT DISTINCT (a.empsalary), a.Employee_Name FROM employee A WHERE N = (SELECT COUNT (DISTINCT (b.empsalary)) FROM employee B WHERE a.empsalary <= b.empsalary)
–where N is your number (2nd or 5th)
e.g SELECT DISTINCT (a.empsalary), a.Employee_Name FROM employee A WHERE 5 = (SELECT COUNT (DISTINCT (b.empsalary)) FROM employee B WHERE a.empsalary <= b.empsalary)
Question 15: Write a Select query without using “Like” operator to get employee records where employee name start with ‘j’.
Answer: Select * FROM employee WHERE CHARINDEX(‘j’, Employee_name) = 1
Question 16: What is a view? What are the advantages and disadvantages of views in database?
Answer: View is basically a virtual table, that consists of columns from more or more real tables.
1. Views doesn’t store data in a physical location.
2. View can be used to hide some of the columns from the table.
3. Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.
1. When a table is dropped, associated view become irrelevant.
2. Since the view is created when a query requesting data from view is triggered, its a bit slow.
3. When views are created for large tables, it occupies more memory.
Question 17: What is a deadlock?
Answer: Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each piece would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process.
Question 18: What is the difference between CHAR and VARCHAR data types?
Answer: CHAR datatype is suitable for fixed length strings. If a string is less than the set length, then it is padded with extra characters so that it’s length is the set length.
VARCHAR data type is suitable for variable length strings where a maximum length is specified. If string is less than the maximum length, then it is stored verbatim without any extra characters.
Question 19: What are the restrictions of using subquery in SQL Server?
Answer: Following are the restrictions of using subquery:
1. Select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operator on SELECT * or a list respectively)
2. If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
3. The ntext, text and image data types cannot be used in subquery.
4. The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
5. The COMPUTE and INTO clause cannot be specified.
6. ORDER BY can only be specified when TOP is also specified.
7. A view created by using subquery cannot be updated.
Question 20: Name of the stored procedure to get list of current users.
Question 21: What is a HEAP table?
Answer: A table without any CLUSTERED INDEX is called as HEAP table. The data rows of a heap table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure of the heap table usually increases the overhead of accessing a large heap table, when compared to accessing a large nonheap table (a table with clustered index). So, prefer not to go with HEAP tables.
Question 22: Can we insert a value explicitly into an identity column? If yes, then how?
Answer: Yes, we can insert a value explicitly into an identify column. For that first we need to set IDENTITY_INSERT property to ON. Syntax of this property is given below:
SET IDENTITY_INSERT <tablename> ON
After executing above command we can explicitly insert value into an identity column. Once data is inserted, remember to set it to OFF.
Question 23: What is a Temporary table?
Answer: A temporary table is a database object that is temporarily stored and managed by the database system. There are two types of Temp tables.
Question 24: What are the differences between Local Temp table and Global Temp table?
Answer: Both tables are used to store data temporarily and are stored in tempdb database. Following differences exists between these two:
1. Local temp table is prefixed with # whereas Global temp table with ##.
2. Local temp table is valid for the current connection i.e the connection where it is created whereas Global temp table is valid for all the connection.
3. Local temp table cannot be shared between multiple users whereas Global temp table can be shared.
Question 25: What is the difference between UNION ALL statement and UNION?
Answer: The main difference between UNION ALL statement and UNION is that UNION ALL doesn’t look for duplicate rows, but on the other hand UNION statement look for duplicate rows, whether they exists or not. That’s why UNION ALL is faster than UNION.
Question 26: Query to find duplicate records of a table.
Answer: SELECT employeename, COUNT(*) FROM dbo.employee GROUP BY employeename HAVING COUNT(*) > 1
Question 27: How we can get the list of user tables?
Answer: SELECT * FROM Sys.Objects WHERE Type = ‘u’
Question 28: How we can implement Row Lock explicitly in SQL Server?
Answer: By using “With (ROWLOCK)” we can implement Row Lock explicitly. With this prevent the command to lock whole page or table.
Question 29: What are Magic tables?
Answer: Sometimes we need to know about the data which is being inserted/deleted by triggers in database. Whenever a trigger fires in response to the INSERT, DELETE, or UPDATE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the Magic tables. These are the conceptual tables and are similar in structure to the table on which trigger is defined (the trigger table).
The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.
Question 30: What is the use the UPDATE_STATISTICS command?
Answer: UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.