Best Sql Server Interview Questions 2000/2005/2008 : Part 1

Part 2
 

Part 3

1.      Difference between DBMS and RDBMS

DBMS – Data Base Management System	RDBMS –Relational Data Base Management System or Relational DBMS
DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.	RDBMS adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don’t support a tabular structure or don’t enforce relationships between tables.
DBMS does not impose any constraints or security with regard to data manipulation it is user or the programmer responsibility to ensure the ACID PROPERTY of the database	RDBMS defines the integrity constraint for the purpose of holding ACID PROPERTY. RDBMS may be or may not be Client Server Database System.
Ex: DBMS – File System, XML	Ex: RDBMS – SQL Server, Oracle

2.      Difference between Normalization and De-normalization

Normalization	De-normalization
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.	De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance.
Normalization is a technique to move from lower to higher normal forms of database modeling.	De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

3.      What is Difference between DELETE and TRUNCATE Commands?

DELETE	TRUNCATE
Delete command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. DELETE can be used with or without 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. Can’t apply where condition on Truncate
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. Delete is slower than Truncate	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 is faster
DELETE does not reset Identity property of the table.	TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
Possible	You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
It can be rolled back.	Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
DELETE is DML Command.	TRUNCATE is a DDL Command.

4.      How is ACID property related to Database?

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

Atomicity: is an all-or-none proposition.

Consistency: guarantees that a transaction never leaves your database in a half-finished state.

Isolation: keeps transactions separated from each other until they are finished.

Durability: guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

5.      What are the Different Normalization Forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

6.      Triggers inside Stored procedure

Got a query from users like, “Is it possible to use trigger inside stored procedure. If not why? If yes how?”

My answer is “No”

We can’t use trigger inside stored procedure because trigger is an object which is binded with table objects.

1.      The inherent property of the trigger is to fire automatically if any condition breaks and it’s associated with DML operations. But we are trying to create and initiate it from the stored procedure.

2.      It will loose its property as a System object.

My answer is “Yes”

1.      To provide an alternative way, Microsoft provides us a new concept named CLR integration in sql server 2005.

2.      We can create Managed trigger and wrap it in the stored procedure.

7.      What is a View?

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specific users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

8.      What is an Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

9.      What is an Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBAs leave these at 1. A GUID column also generates unique keys.

10.  What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data.

Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server.

11.  What is a Cursor?

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

In order to work with a cursor, we need to perform some steps in the following order:

·         Declare cursor

·         Open cursor

·         Fetch row from the cursor

·                           Process fetched row

·         Close cursor

·         Deallocate cursor

12.  What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.

13.  Types of Indexes

·         Clustered Index

·         Non-Clustered Index

14.  Difference between Clustered Index and Non-Clustered Index.

Clustered Index	Non-Clustered Index
A clustered index is a special type of index that reorders the way in which records in the table are physically stored.	It won’t touch the structure of the table.
Only one clustered index can be created for a table.	For a table, we can create 249 non clustered index.
The leaf nodes of a clustered index contain the data pages.	The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
By default, primary key will create clustered index on the table.	By default, Unique key will create clustered index on the table.

15.  Difference between Stored Procedure and Trigger

Stored Procedure	Trigger
Defination: A stored procedure is a collection of precompiled SQL statements that have been previously created and stored in the server database.	Defination: It will fire automatically when ever DML operations performed on the table or view. Triggers are basically used to implement business rules.
Supports Input and Output parameters	Not support
Stored procedures are explicitly executed by invoking a CALL to the procedure	Triggers are implicitly executed
Procedures can’t execute Triggers	Triggers can execute stored procedures

16.  Difference between Stored Proc and Function

Stored Proc	Function
Data manipulations are possible with in the procedure	Not Possible
Supports XML FOR clause	Not supports
If there is an error in SP it just ignores the error and moves to the next statement.	If there is an error in UDF its stops executing.
SP’s can make permanent changes to server environments	Can’t
Can’t	UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section
Supports all data types	Text, ntext, image and timestamp data types are not supported.
	Defination: User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

17.  Difference between Primary key and Unique key?

Primary key	Unique key
It won’t allow null value	It will accept null value but only one
Only one primary key can be created for the table	Any number of Unique key can be created for the table.
Primary Key creates a clustered index on the column.	Unique Key creates a non-clustered index on the column.
Ex: Create table with Primary Key: CREATE TABLE Authors ( AuthorID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT NULL )	Ex: Alter table to add unique constraint to column: ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)

18.  Difference between Temp tables and Table variables in Sql Server

1)      Transaction logs are not recorded for the table variables. They are variables and thus aren't bound to a transaction.

      Temp tables behave same as normal tables and are bound by transactions.

2)      Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

3)      Table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec (string) statements. Also, they cannot be used in an insert/exec statement.

4)      As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables.

5)      If we use Temporary Table in a stored procedure, we should drop it at the end. It is not necessary in the case of Table variable. 

A simple example shows this difference quite nicely:

BEGIN TRAN

declare @var table (id int, data varchar(20) )

create table #temp (id int, data varchar(20) )

insert into @var

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

insert into #temp

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

select * from #temp

select * from @var

ROLLBACK

select * from @var

if object_id('tempdb..#temp') is null

select '#temp does not exist outside the transaction'

We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked. 

19.  What are Different Types of Join?

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. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join: A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join: A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

·         Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.  

·         Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

·         Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join: This is a particular case when one table joins to itself with one or two aliases to avoid confusion. 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. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

20.  What are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

21.   What is a candidate key?

A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. During database design you can pick up one of the candidate keys to be the primary key. For example, in the supplier table supplierid and suppliername can be candidate key but you will only pick up supplierid as the primary key.

22.  What is User-defined Functions? What are the types of User-defined Functions that can be created?

User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

Different Types of User-Defined Functions created are as follows:

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.

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 it 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 encountered while using a stored procedure which can also return record sets.

23.  What is Dirty Read?

A dirty read occurs when two operations, say, read and write occur together giving the incorrect or unedited data. Suppose,

A changed a row but did not committed the changes.

B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

24.  Why can’t I use Outer Join in an Indexed View?

Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

25.  What is the Correct Order of the Logical Query Processing Phases?

The correct order of the Logical Query Processing Phases is as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. With {CUBE | ROLLUP}
7. HAVING
8. SELECT
9. DISTINCT
10. TOP
11. ORDER BY