Search content within the blog

Friday, April 17, 2009

SqlServer Interview Questions Part-I

Q.1 What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
A. 1024

Q.2 how many system datatypes are in SQL Server.
A. 27

Q.3 What ODS API Stands For ?
A. Open Data Services API

Q.4 How can you generate GUID in in Transact-SQL ?
A. GUIDs can be generated using the NEWID function.

Q.5 How many type of authentication method are there in SQL Server 2000
A. There are two type of authentication method in SQL Server 2000

Q.6 Whats the Difference between datetime and smalldatetime datatype in SQL Server 2000 .
A . The main difference between these two datatypes is in the amount of space they occupy. datetime occupies eight bytes and smalldatetime only four. The difference in size is due to a difference in precision. The precision of smalldatetime is one minute, and it covers dates from January 1, 1900 , through June 6, 2079 , which is usually more than enough. The precision of datetime is 3.33 ms, and it covers dates from January 1, 1753 , to December 31, 9999 .

Q.7. Can a user defined function return table ?
A. it is possible to design a user-defined function that returns a table.

Q.8. Whats is the nesting limit of Sql Server stored procedure.
A. SQL Server 2000 have a limit of 32 stored procedure nesting levels.

Q.9 In how many ways you can recieve information from stored procedure
A. there are 4 ways to receive information from a stored procedure:
Resultset , Parameters , Return value , A global cursor that can be referenced outside the stored procedure.

Q.10 Whats the limitation of user defined funtion ?
A User-defined functions have one serious limitation. They cannot have side effects. A function side effect is any permanent change to resources (such as tables) that have a scope outside of the function (such as a non-temporary table that is not declared in the function). Basically, this requirement means that a function should return a value while changing nothing in the database. it means in short that "user defined function can not use UPDATE / DELETE on permament table objects in MSSQL"

Q.11 what is @@Fetch_status ?
A. @@fetch_status is a function (or global variable) that returns the success code of the last Fetch statement executed during the current connection. It is often used as an exit criterion in loops that fetch records from a cursor.

Q.12 How you can trap error in Sql Server 2000
A. by using @@error .
After each Transact-SQL statement, the server sets the variable to an integer value:
0—if the statement was successful
Error number—if the statement has failed

Q.13 How many type of Contraints are in MSSQL.
A. SQL Server 2000 supports five classes of constraints.
1) NOT NULL
2) CHECK
3) UNIQUE
4) PRIMARY KEY
5) FOREIGN KEY

Q.14 How you can get the last identity value inserted in any table ?
A. SQL Server 2000 has a System Variable @@IDENTITY which gives the last identity element value inserted in any table

Q.15 How many type of indexes are there ?
A. there are two type of indexes Clustered and Non-Clustured

Q.16 How many Index can be created on a table
A. 249 Non Clustured and 1 Clustered index

No comments:

Post a Comment