Friday, May 8, 2009

Frequently asked interview questions in SQL Server 2005

This article explains the frequently asked (FAQs) interview questions in SQL Server 2005 with answers. This covers basic and advanced concept of SQL Server

1. What are the differences between User Defined function (UDF) and Stored Procedure?

  • Stored Procedure is pre compiled execution plan where as functions are not.
  • Stored Procedure returns more than one value at a time while function returns only one value at a time.
  • We can call the UDFs in sql statements (select max (sal) from emp) whereas SP is not so
  • Function parameters are always IN, no OUT is possibleFunctions MUST return a value, procedures need not be
2. How to Create and Run a CLR SQL Server User-Defined Function?

  • Create a SQL Server Project in Visual Studio
  • From the Project menu, select Add New Item.
  • Select User-Defined Function in the Add New Item Dialog Box.
  • Type a Name for the new user-defined function.

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions{
public static SqlDouble addTax(SqlDouble originalAmount) {
SqlDouble taxAmount = originalAmount * .3;
return originalAmount + taxAmount;

Select Deploy project from the Build menu
Note : The common language runtime (CLR) integration feature is turned off by default in Microsoft SQL Server and must be enabled in order to use SQL Server project items.
To enable CLR integration, use the clr enabled option of the sp_configure stored procedure.

EXEC sp_configure 'clr enabled' , '1'

To learn more, click here

3. What is SQL Cache Dependency in ASP.NET 2.0?

SQL cache dependencies are new technique in ASP.NET 2.0 which can automatically invalidate a cached data object just like a Dataset. when the related data is modified in the database. So for instance if you have a dataset which is tied up to a database tables any changes in the database table will invalidate the cached data object which can be a dataset or a data source.
To enable this we need a syntax that is as follows:- aspnet_regsql -ed -E -d Northwind

4.What are the different types of temporary tables in SQL Server?
  • Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends.
  • Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

5.What is the difference between UNION ALL and UNION Statement?

The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION. The reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

6. What is the use of Cascade and Restrict when we use DROP table in SQL SERVER?

When we are using Drop table in SQL the syntax is simple. SQL92 specifies some additional capabilities for DROP TABLE:
Drop table table_name(CASCADE / RESTRICT)

If we use cascade to drop table although it have some dependencies just like triggers, views, stored procedure, primary key, foreign key it will delete first.But if we use restrict a error message is shown on using of DROP if the table have relation Trigger or stored procedure

7. What is the use of SCHEMABINDING Option in creation of view in SQL Server?

Imagine that you have created a view without SCHEMABINDING option and you have altered the schema of underlying table (deleted one column). Next time when you run your view, it will fail. Here is when SCHEMABINDING comes into picture. Creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema.

E.g CREATE VIEW testview
SalesTerritoryID, CustomerID,
FROM testtable a INNER JOIN testtable2 b
ON (a.column1 = b.column1)

8. What are the differences between DELETE TABLE and TRUNCATE TABLE commands?

  • DELETE TABLE syntax logs the deletes thus make the delete operation slow. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table so TRUNCATE table is faster as compared to delete table.
  • DELETE table can have criteria while TRUNCATE cannot.
  • TRUNCATE table cannot trigger

9. What is Normalization and what are the advantages?

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of normalizing your database will include:

  • Avoiding repetitive entries
  • Reducing required storage space
  • Preventing the need to restructure existing tables to accommodate new data.
  • Increased speed and flexibility of queries, sorts, and summaries.

10. What are the different types of Keys?

Different types of Keys

  • Primary key:- The attribute or combination of attributes that uniquely identifies a row or record.
  • Foreign Key:- an attribute or combination of attribute in a table whose value match a primary key in another table.
  • Composite key:- A primary key that consists of two or more attributes is known as composite key
  • Candidate key:- is a column in a table which has the ability to become a primary key. Candidate Key (Primary Key) is a Key which Maintains the Row Unique .
    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.
  • Alternate Key:- Any of the candidate keys that is not part of the primary key is called an alternate key. Alternate Key or Unique Key is similar to PK , except it accepts null Values .

11. What is DBCC and explain it's use?

DBCC (Database Consistency Checker Commands) is used to check logical and physical consistency of database structure. DBCC statements can fix and detect problems.
They are grouped in to four categories:-

  • Maintenance commands like DBCC DBREINDEX , DBCC DBREPAR etc ,they are mainly used for maintenance tasks in SQL SERVER.
  • Miscellaneous commands like DBCC ROWLOCK , DBCC TRACEO etc ,they are mainly used for enabling row-level locking or removing DLL from memory.
  • Status Commands like DBCC OPENTRAN , DBCC SHOWCONTIG etc ,they are mainly used for checking status of the database.
  • Validation Commands like DBCC CHECKALLOC, DBCCCHECKCATALOG etc, they perform validation operations on database.

12. What are the types of replication supported by SQL SERVER 2005?

  • Snapshot Replication takes snapshot of one database and moves it to the other database. After initial load data can be refreshed periodically. The only disadvantage of this type of replication is that all data has to be copied each time the table is refreshed.
  • In transactional replication data is copied first time as in snapshot replication, but later only the transactions are synchronized rather than replicating the whole database. You can either specify to run continuously or on periodic basis.
  • Merge replication combines data from multiple sources into a single central database. Again as usual the initial load is like snapshot but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly.

Configuring SQL server Replication using SQL Server Publication and Subscription, click here

13.What is BCP in SQL Server?

BCP (Bulk Copy Program) is a command line utility by which you can import and export large amounts of data in and out of SQL SERVER database.

14. What is the use of SQL Server Agent?

It is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

15. What are the types of Triggers?

1. DML Triggers

These triggers are fired when a Data Manipulation Language (DML) event takes place. These are attached to a Table or View and are fired only when an INSERT, UPDATE and/or DELETE event occurs. The trigger and the statement that fires it are treated as a single transaction. Using this we can cascade changes in related tables, can do check operations for satisfying some rules and can get noticed through firing Mails. We can even execute multiple triggering actions by creating multiple Triggers of same action type on a table.

  • AFTER Triggers: As the name specifies, AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. AFTER triggers can be specified on tables only. Here is a sample trigger creation statement on the Users table.

E.g To Create a DML trigger in T-SQL


CREATE TABLE UserTable (User_ID int IDENTITY, User_Name varchar(30), Type varchar(10))




INSERT UserTable (User_Name, Type) VALUES ('James', 'ADMIN')

  • INSTEAD OF Triggers INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

2. DDL Triggers

DDL triggers are new to SQL Server 2005. This type of triggers, like regular triggers, fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. Certain stored procedures that perform DDL-like operations can also fire this. These are used for administrative tasks like auditing and regulating database operations.

3. CLR Triggers

A CLR triggers can be any of the above, e.g. can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger. Here we need to execute one or more methods written in managed codes that are members of an assembly created in the .Net framework. Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.

Creating CLR Trigger is available here