Frequently Asked SQL Server Interview Questions

1.What is Sql server?

SQL – Structured query language is the standard command set used to communicate with the relational database management system.

Sql server – is commonly used as the backend system for websites and corporate CRMs and can support thousands of concurrent users.SQL Server is much more robust and scalable than a desktop database management system such as Microsoft Access.

2.What are the System Database in Sql server 2005?

Master – Stores system level information such as user accounts, configuration settings, and info on all other databases.
Model – database is used as a template for all other databases that are created
Msdb – Used by the SQL Server Agent for configuring alerts and scheduled jobs etc
Tempdb – Holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.

3.What is the difference between TRUNCATE and DELETE commands?

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

4.What is OLTP?

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

5.Define Normalisation?

Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.

6.What are the difference between clustered and a non-clustered index?

A Clustered index is a special type of index that reorders the way in which each records in the table are physically stored.
A Non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

7.What are the System Database in Sql server 2008?

Master
Model
Msdb
Tempdb
Resource

8.What is denormalization and when would you go for it?

Denormalization is the process of attempting to optimize the performance of a database by adding redundant data or by grouping data.Denormalization is the reverse process of normalization.

9.What are the different types of Sub-Queries?

Single row subquery
Multiple row subquery
Correralted row subquery

10.What are constraints? Explain different types of constraints?

Constraints is a rule or restriction concerning a piece of data that is enforced at the data level. A Constraint clause can constrain a single column or group of columns in a table.

There are five types of Constraint namely

Null / Not Null
Primary Key
Unique
Check or Validation
Foreign Key or References Key

11.What are the different types of BACKUPs avaialabe in SQL Server 2005?

In SQL Server 2005 Backup Types are

Full
Transaction Log
Differential
Partial
Differential Partial
File and Filegroup
Copy Only Database Backups.

12.What are Data files?

This is the physical storage for all of the data on disk. Pages are read into the buffer cache when users request data for viewing or modification. After data has been modified in memory (the buffer cache), it is written back to the data file during the checkpoint process.

13.Define Primary Key?

The primary key is the columns used to uniquely identify each row of a table.
A table can have only one primary key.
No primary key value can appear in more than one row in the table.

14.What is cursors? and what are the different types of cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

Implicit cursors
Explicit cursors
Paramaeteried cursors

15.What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later.

16.Define Unique Key?

Unique key is a one or more column that must be unique for each row of the table.
It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will accept a null values.

17.Define Joins?

A Join combines columns and data from two or more tables (and in rare cases, of one table with itself).

18.Define Indexes?

Index is a general term for an Oracle/SQL features used to primarily to speed execution and imposes uniqueness upon certain data.
The most important of an index is to ensure uniqueness of rows and help in speedy retrieval of data.

19.What are the difference between primary keys and foreign keys?

The primary key is the columns used to uniquely identify each row of a table.A table can have only one primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

20.Define Clusters?

Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk.
A cluster contains one or more tables, which have one or more column in common among them.

21.What are the transaction properties?

Atomicity
Consistency
Isolation
Durability

22.Define Synonym?

Synonym is an alternative method to creating a view that includes the entire table or view from another user it to create a synonym.
A synonym is a name assigned to a table or view that may thereafter be used to refer to it.

23.What is an active database?

Active database is a database that includes active rules, mostly in the form of ECA rules(Event Condition rules). Active database systems enhance traditional database functionality with powerful rule processing cabalities, providing a uniform and efficient mechanism for database system applications.

24.What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

25.What are the purpose of Normalisation?

Minimize redundancy in data.
Remove insert, delete and update anamoly during the database activities.
Reduce the need to reorganize data it is modified or enhanced.
Normalisation reduces a complex user view to a set of small and stable subgroups of fields or relations.

26.Define Self Join?

Self join means joining one table with itself.
The self join can be viewed as a join of two copies of the same table.

27.What the difference between UNION and UNIONALL?

Union will remove the duplicate rows from the result set while Union all does’nt.

28.What are different type of Collation Sensitivity?

The different phases of transaction are :

Case sensitivity
Accent sensitivity
Kana Sensitivity
Width sensitivity

29.What is the difference Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF›s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

30.What is the difference between a local and a global variable?

A Local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A Global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

31.What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

32.What is log shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server.

33.What is Cross Join?

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

34.What is Self Join?

A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table.

35.Define Normalisation?

Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.

36.What is the difference between Triggers and Stored Procedure?

Stored Procedures are called by the programmer wherever it wants to fire but triggers fired automatically when insert,delete,update occured. And triggers can be implemented to tables & views only where as stored procedure used in the database independently.

37.What are the properties of Sub-Query?

A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

38.Where are SQL server users names and passwords are stored in sql server?

They get stored in master db in the sysxlogins table.

39.What are the types of subscriptions in SQL Server replication?

There are two types of replication in sql server are :

Push
Pull

40.What does REVERT do in SQL Server 2005?

Restores your previous execution context.If you have changed your execution context with EXECUTE AS, the REVERT statement will restore the last context prior to the EXECUTE AS.

41.What does NULL mean?

The value NULL means UNKNOWN; it does not mean (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL.

42.What are the Different Types of Normalization?

First Normal Form
Second Normal Form
Third Normal Form
Boyce’s Normal Form
Fourth Normal Form
Fifth Normal Form

43.What are Page Splits?

Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as “Page Split”.

44.What is an Identity?

Identity is a column that automatically generates numeric values.

45.What is the difference between SET and SELECT?

Both SET and SELECT can be used to assign values to variables. It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.

Examples

declare @i int
set @i=1
This is used to assign constant values.

select @i=max(column_name)from table_name
for ex.
select @i=max(emp_id) from table_emp.

46.What is the difference between char , varchar and nvarchar?

char(n)Fixed length non unicode character data with length of n bytes.n must be a value from 1 through 8,000.

varchar(n)variable length non unicode character data with length of n bytes.

nvarchar(n)variable length unicode character data of n characters. n must be a value from 1 through 4,000.

47.How many types of triggers are there?

There are three types of triggers.

DML Triggers
–> AFTER Triggers
–> INSTEAD OF Triggers
DDL Triggers
CLR Triggers

48. How to optimize stored procedures in SQL Server?

Ans: I. Use where clause
II. Select only required fields
III. Do join on indexed key fields

49. What is the difference between Stored procedure and User defined functions?

Stored Procedure:

A Stored procedure is a set of sql statements with a given name , which is physically stored in database in the compiled form. Once the stored procedure has been “stored”, client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again. Stored procedures improve performance by reducing network traffic and CPU load.
1. CREATE PROCEDURE SPName
2. @param1 int,
3. @param2 int OUTPUT,
4. @param3 varchar(50) INOUT
5. AS
6. BEGIN
7. —-Sql statemt(s)—-
8. END

User Defined Function(UDF):

User defined function is a database object which encapsulates one or more sql statements for reuse , which can accept zero or more parameters and return either a value or table. User defined fuctions contain useful logic for using another quaries or other database objects like stored procedures.
01. CREATE FUNCTION FunName (
02. @param1 int,
03. @param2 varchar(50),
04. )
05. RETURNS int
06. AS
07. BEGIN
08. Declare @RetVal int
09. —sql statement(s)—
10. Return @RetVal
11.
END

Differences:

1. Procedures can accept input(default), output and inout type parameters for it.
Functions can accept only input type parameters.
2. Procedures may or may not return a value or may return more than one value using the OUTPUT and/or INOUT parameters. A procedure may return upto 1024 values through OUTPUT and/or INOUT parameters.
Function always returns only one value.
3. Stored procedure returns always integer value by default zero.
Function return type could be scalar or table or table values.
4. Stored procs can create a table but can’t return table.
Functions can create, update and delete the table variable. It can return a table
5. Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
6. Stored procedures are stored in database in the compiled form.
Function are parsed and conpiled at runtime only.
7. Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause.
Function are called from select/where/having clause. Even we can join two functions.
8. Normally stored procedure will be used for perform specific tasks.
Functions will be used for computing value.
9. Stored procedure allows getdate () or other non-deterministic functions can be allowed.
Function won’t allow the non-deterministic functions like getdate().
10. In Stored procedures we can use transaction statements. We can’t use in functions.
11. The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.
The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.
12. Temporary tables (derived) can be created in stored procedures.
It is not possible in case of functions.
13. when sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code.
In case of functions, T-SQL will stop execution of next statements.

50. Why should we go for Stored Procedures? Why not direct queries?

Ans: SP are precompiled and contain an execution plan with it. Hence, they are faster.

51. How many NULL values we can have in a Unique key field in SQL Server?

Ans: Only one. In case of Oracle, we can have multiple NULL values in a Unique key field.

52. What is correlated subquery?

select empno,ename from emp where deptno in(select deptno from dept where dept.deptno=emp.deptno)

when inner subquery has an reference to outer query then this is know as Correlated sub-query.

53. What is an index. What are the types?

Indexes in databases are very much similar to Indexes in Books. Indexes help in searching data faster.

Types: Clustered Index

Non-Clustered Index

54. What is the difference between a clustered index and a non-clustered index?

Clustered Index:

1 Only one clustered index allowed per table
2 Physically rearranges the data
3 For use on columns that are frequently searched for range of data

Non-clustered Index:

1 Upto 249 non-clustered index allowed per table
2 Doesn’t rearrange the data. Keeps a pointer to the data.
3 For use on columns that are searched for single value.

55. What is a join? What are the types of joins?

Joins are used to retrieve data from multiple tables.

Following are the types of joins:

Inner join
Left outer join
Right outer join
Full outer join
Cross join

56. What is a transaction?

A SQL transaction is a sequence of operations performed as a single unit of work. If all the tasks are completed successfully, then the transaction is committed (saved). If a single task fails, the transaction is rolled back (discarded).

57. What is ACID property of transaction?

A SQL transaction must exhibit ACID property, i.e Atomicity, Consistency, Isolation, and Durability.

Atomicity: A transaction is always treated as a single unit of work, i.e. either all the tasks are performed or none of them, no intermediate stage.

Consistency: When a transaction is completed, it must leave all data in a consistent state.

Isolation: Modifications made by a transaction must be isolated from the modifications made by other transactions.

Durability: After a transaction is completed, it’s effects are permanently in place in the system.

58. What is SET NOCOUNT ON?

When we perform a SELECT, INSERT, UPDATE or DELETE query, it returns a COUNT (number of rows affected) when SET NOCOUNT OFF. If SET NOCOUNT ON, it doesn’t return the COUNT.

59. How to delete exactly duplicate records from a table?

There are many ways.

Simplest answer is:

i. Let the table tab1 contains duplicate records.
ii. Insert distinct records from tab1 in a temporary table #temp

INSERT INTO #temp
SELECT DISTINCT * FROM tab1

iii. Delete all rows from original table tab1
DELETE FROM tab1

iv. Insert from temporary table
INSERT INTO tab1
SELECT * FROM #temp

Try other solutions yourself.

60. How to get nth highest salary from employee table.

The query below demonstrates how to find the 5th highest salary.

Replace 5 with any integer to get nth salary.

SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 5 SALARY
FROM EMPLOYEE ORDER BY SALARY DESC) a
ORDER BY SALARY ASC