sql server interview qustion & answer

 1. What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships created and maintained across tables between data. Interdependencies between these tables are defined by the data values.


RDMS is based upon relational modal of E.F.Codd

 2. Can UNIQUE KEY in SQL Server 2005 have two or more NULL?

SQL server 2005 can not have more then one NULL, because in SQL server 2005 every null is having same value. UNIQUE KEY in ORACLE can have more then one NULL values as every NULL in ORACLE is having unique value.

3. What is a "trigger" in SQL Server 2005?

In  any database including SQL Server 2005 a trigger is procedure that initiates on INSERT, DELETE or UPDATE actions. Before SQL Server 2000 Triggers are also used to maintain the referential integrity. We can not execute triggers explicitly; the DBMS automatically fires the trigger when data modification events (INSERT, DELETE or UPDATE) happened in the associated table.

Triggers are same as stored procedures in term of procedural logic that is stored at the database level. Stored procedures are executed explicitly and triggers are event-drive.

Triggers can also execute stored procedures.


Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. See Create Trigger for more

4. What is View in Database (SQL Server 2005, ORACLE)?

 As per the theory of database (which includes SQL Server 2005, Oracle etc.) a view can be thought of stored SQL query which result can be accessible as a table. It can be used for retrieving data, as well as updating or deleting rows.

But database view does not have physical schema


The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

See more on Database Views

 5. How do you optimize stored procedures in SQL Server 2005?

1.    Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization

2.    Select only those fields which really require.

3.    Joins are expensive in terms of time. Make sure that use all the keys that relate the two tables together and don't join to unused tables, always try to join on indexed fields. The join type is important as well (INNER, OUTER).