Geeks With Blogs
Server Error in Application An unhandled exception occurred during the execution of the current web request

SQL Server 2005 Interview Questions

1. What is DESCRIBE command in SQL Server 2005? What is its purpose? How to use it?

DESCRIBE is used to see table structure. In SQL server 2005 we can use sp_columns, sp_tables or sp_help.

sp_columns

will show list of columns and its details in table.
sp_tables will show list of tables in the databas

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

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

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

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

 6. 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).

 7. How is the error handling in stored procedures of SQL Server 2005? 

In previous versions of SQL Server you would handle exceptions by checking the @@error global variable immediately after an INSERT, UPDATE or DELETE, and then perform some corrective action if @@error did not equal zero.

SQL Server 2005 provides structured exception handing through TRY CATCH block as other programming language like JAVA, C# etc.

BEGIN TRY
 RAISERROR ('Yaa, I ma the problem', 16,1)
END TRY
 
BEGIN CATCH
 SELECT ERROR_NUMBER() as ERROR_NUMBER,
   ERROR_SEVERITY() as ERROR_SEVERITY,
   ERROR_STATE() as ERROR_STATE,
   ERROR_MESSAGE() as ERROR_MESSAGE
END CATCH
 

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.

 

Related Post:

Association, Aggregation, Composition object relationship

Talent vs Attitude – Harsha Bhogle

What we can learn from Babies

 

Posted on Friday, September 19, 2008 2:49 AM ASP.NET , C# , Web Services , Design Pattern , BizTalk , unhandled exception | Back to top


Comments on this post: SQL Server 2005 (Database) Interview Questions

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Mahesh Singh | Powered by: GeeksWithBlogs.net