SQL Interview Questions and Answers set 2


 SQL Interview Questions and Answers set 2

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.

How to implement one-to-one, one-to-many and many-to-many relationships while
designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary

and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and

foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables

forming the composite primary key of the junction table.

What is a NOLOCK?

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve

concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are

taken when data is read. The result is a Dirty Read, which means that another process could be

updating the data at the exact time you are reading it. There are no guarantees that your query will

retrieve the most recent data. The advantage to performance is that your reading of data will not block

updates from taking place, and updates will not block your reading of data. SELECT statements take

Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but

other processes are blocked from modifying the data. The updates will queue until all the reads have

completed, and reads requested after the update will wait for the updates to complete. The result to

your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE

clause. Truncate will actually remove all the rows from a table and there will be no data in the table

after we run the truncate command.


TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the

page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes

and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.


DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition

and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE Can be Rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

Difference between 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.

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of

deletions any modification 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


What types of Joins are possible with Sql Server?

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


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

Specifies a search condition for a group or an aggregate. 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. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is sub-query? Explain properties of sub-query.

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed

arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of

parentheses. Sub-queries are generally used to return a single row as an atomic value, though they

may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT

statement if executed independently of the T-SQL statement, in which it is nested, will return a result

set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in

which it is nested. A subquery SELECT statement can return any number of values, and can be found

in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a

T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery

can be used anywhere an expression can be used.

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, and

A subquery cannot contain a ORDER-BY clause.

A query can contain more than one sub-queries.

What are types of sub-queries?

Single-row subquery, where the subquery returns only one row.

Multiple-row subquery, where the subquery returns multiple rows,.and

Multiple column subquery, where the subquery returns multiple columns.

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. For example, you can monitor a production environment to see which stored procedures

are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too

large, you can filter them based on the information you want, so that only a subset of the event data is

collected. Monitoring too many events adds overhead to the server and the monitoring process and can

cause the trace file or trace table to grow very large, especially when the monitoring process takes

place over a long period of time.

What is User Defined Functions?

User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters

and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline TableValued and Multi-statement Table-valued.

Scalar User-Defined Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp

data types are not supported. These are the type of user-defined functions that most developers are

used to in other programming languages. You pass in 0 to many parameters and you get a return

Inline Table-Value User-Defined Function

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative

to a view as the user-defined function can pass parameters into a T-SQL select command and in

essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional

alternative to a view as the function can support multiple T-SQL statements to build the final result

where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized,

non-updateable view of the data in the underlying tables. Within the create function command you

must define the table structure that is being returned. After creating this type of user-defined function,

It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored

procedure which can also return record sets. 

Post a Comment