SQL Server Interview questions

SQL Server Interview questions

1- Similarity between Truncate and Delete in SQL

These both command will only delete data of the specified table, they cannot remove the whole table data structure.



2- Difference between Truncate and Delete in SQL

TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.

TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.

We can use any condition in WHERE clause using DELETE but you can’t do it with TRUNCATE.

If table is referenced by any foreign key constraints then TRUNCATE will not work.



3- What are cursors and when they are useful?

When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used. When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.

There are two of cursors

1. Implicate Cursor
   SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.
2. Explicit Cursor
   When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.

They are managed by OPEN, FETCH and CLOSE.

%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors.



4- What are the advantages of using Stored Procedures?

– Stored Procedures help in reducing the network traffic and latency which in turn boosts application performance.
– They help in promoting code reuse.
– They provide better security to data.
– It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients.
– It is possible to reuse stored procedure execution plans, which are cached in SQL Server’s memory. This reduces server overhead.



5- What do you mean by ACID?

ACID (Atomicity Consistency Isolation Durability) is a quality sought after in a reliable database. Here’s the relevance of each quality:

1. Atomicity is an all-or-none proposition.
2. Consistency – it guarantees that your database is never left by a transaction in a half-finished state.
3. Isolation – it keeps transactions separated from each other until they’re finished.
4. Durability – it ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination.

6- What is SQL Injection?

• SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website.
• This attack can happen with the applications in which SQL queries are generated in the code.
• The attacker tries to inject their own SQL into the statement that the application will use to query the database.

7- What is difference between clustered and non clustered index?

• A table can have only one Clustered Index at a time which is generally created on primary key and can have more than one non clustered indexes (maximum up to 999)
• The leaf level of clustered index is actual data pages of the table. Whereas in case of non-clustered index the leaf level is a pointer to the data.
• Non-clustered index is faster than clustered index because when we use DML statement on clustered index, performance issues may occurred since it has to update the index every time a DML statement is executed.

8- What is Scheduled job and how to create it?

If we want to execute any procedural code automatically on specific time either once or repeatedly then we can create a Scheduled job for that code.

Following are the steps to create a Scheduled Job.

1. Connect to your database of SQL server in SQL Server Management Studio.
2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.
3. A New Job window will appear. Give a related name for the job.
4. Click next on the “Steps” in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.
5. Click on the “Schedules” in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.

Using scheduled job you can also create alert and notifications.



9- Differentiate between a primary key and a unique key.

UPDATE_STATISTICS command is used when a large processing of data has occurred. If any large amount of deletions, any modifications, or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.



10- How would apply date range filter?

We can use simple conditions like >= and <=, or use between/and but the trick here is to know your exact data type.

To increase query performance you may still want to use between however you should be aware of proper format.



11- Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY (‘productversion’), SERVERPROPERTY (‘productlevel’) and SERVERPROPERTY (‘edition’)

12- What is a temp table in SQL?

A temp table is a temporary storage structure. It means you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.

13- How will you find out 7th highest salary in a table?

SELECT * FROM

(SELECT ROW_NUMBER () OVER (ORDER BY sal_amount DESC) row_number, emp_id,

sal_amount

FROM Salary) a

WHERE row_number = 7



14- Differentiate between a primary key and a unique key.

Primary Key:

• There can only be one primary key in a table
• In some DBMS it cannot be NULL – e.g. MySQL adds NOT NULL
• Primary Key is a unique key identifier of the record

Unique Key:

• Can be more than one unique key in one table
• Unique key can have null values
• It can be a candidate key
• Unique key can be null and may not be unique

By default, clustered index on the column are created by the primary key whereas non-clustered index are created by unique key.



15- What is Triggers in SQL?

Triggers in SQL is the procedural code that executed when you INSERT, DELETE or UPDATE data in the table. We can say, Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications.

Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.



16- Differentiate between HAVING and WHERE CLAUSE

HAVING CLAUSE

– HAVING CLAUSE is used only with the SELECT statement.
– It is generally used in a GROUP BY clause in a query.
– If GROUP BY is not used, HAVING works like a WHERE clause.

WHERE Clause

– It is applied to each row before they become a part of the GROUP BY function in a query.



17- What do you mean by an execution plan in SQL?

An execution plan in SQL can be called as a road map that graphically or textually shows the data retrieval methods which have been chosen by the SQL Server query optimizer, for a stored procedure or ad- hoc query.

Why is it used?

It is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure.

How would you view it?

There is an option called “Display Estimated Execution Plan” in Query Analyser. If this option is turned on, it will display query execution plan in separate window when the query is run again.

18- Mention the difference between clustered and a non-clustered index?

1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
2. 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. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

19- How will you find the 3rd max salary in the employment table?

Select distinct salary from employment e1 where 3= (select count (distinct salary) from employment e2 where e1.salary<=e2.salary)

20- Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.