SQL Server Interview Questions

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 Application Programming Interface

Q.4 What are the difference in MSSQL Server 2000 and its previous version 7.0?
A. there are many differences which can be seen in "whats new in SQL Server 2000" topic of the BOL available wid MSSQL Server 2000 installation , few major are given below

• OLAP services in 7.0 is now names SQL Server 2000 Analysis Services , Analysis Services also includes a new data mining component
• SQL Server 2000 introduces support for XML
• The programmability of Transact-SQL can be extended by creating your own Transact-SQL functions. A user-defined function can return either a scalar value or a table
• INSTEAD OF and AFTER Triggers
  INSTEAD OF triggers are executed instead of the triggering action (for example, INSERT, UPDATE, DELETE). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER triggers fire after the triggering action. SQL Server 2000 introduces the ability to specify which AFTER triggers fire first and last.
• Cascading Referential Integrity Constraints
  You can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. This is controlled by the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.
• Support for multiple instances
  SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server version 6.5 or 7.0, along with one or more instances of the database engine from SQL Server 2000. Each instance has its own set of system and user databases
• Support for creating indexes on Computed Columns
• 64 GB Memory Support
  Microsoft SQL Server 2000 Enterprise Edition can use the Microsoft Windows 2000 Advanced Windows Extension (AWE) API to support up to 64 GB of physical memory (RAM) on a computer.

Q.5 How many dataype SQL Server 2000 Supports for date & time
A SQL server 2000 supports two datatypes for storing date and time :
datetime and smalldatetime

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

Q.7 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.8 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.9. Can a user defined function return table ?
A. it is possible to design a user-defined function that returns a table.

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

Q.11 How many type of triggers are there in Sql Server 2000.
A. There are two type of triggers
• After Triggers
• Instead of Triggers

Q.12 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.13 What are the limits of Sql Server 2000.
A.When you are creating or changing a stored procedure,please keep in mind that
The name of the procedure is a standard Transact-SQL identifier. The maximum length of any identifier is 128 characters.
Stored procedures may contain up to 1,024 input and output parameters.
The body of the stored procedure consists of one or more
Transact-SQL statements. The maximum size of the body of the stored procedure is 128MB.

Q.14 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.15 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.16 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.17 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.18 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.19 How many type of indexes are there ?
A. there are two type of indexes Clustered and Non-Clustured

Q.20 How many Index can be created on a table
A. 249 Non Clustured and 1 Clustered index and 5 Reserved for future Use.