SQL Interview questions

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 optimizer 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
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
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 tabl.

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 accordingly.

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 OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

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.
HAVING criteria is applied after the grouping of rows has occurred.

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-query.

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 performances 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 defining 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 Table-Valued 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 value.

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 T-SQL 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.

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 –> Port number. both on client and the server.

What are the authentication modes in SQL Server? How can it be
changed?Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, and Microsoft SQL Server and click
SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL
Server program group. Select the server then from the Tools menu select SQL
Server Configuration Properties, and choose the Security page.

Where SQL server user’s names and passwords are are stored in sql
server?They get stored in master db in the sysxlogins table.

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

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

What is SQL server agent?
SQL Server agent plays an important role in the day-to-day tasks of a
database administrator (DBA). It is often overlooked as one of the main tools
for SQL Server management. Its purpose is to ease the implementation of tasks
for the DBA, with its full-function scheduling engine, which allows you to
schedule your own jobs and scripts.