Microsoft SQL Server 2000 (Single Marks questions of Msc(IT)-1)

[1]. What is SQL Server 2000?

SQL Server 2000 is an RDBMS that uses Transact-SQL to send requests between a client computer and a SQL Server 2000 computer. An RDBMS includes databases, the database engine, and the applications necessary to manage the data and the components of the RDBMS. The RDBMS organizes data into related rows and columns within the database.

[2]. What language is commonly used to work with data in a database?

SQL

[3]. What is XML?

XML is a standard format for data on the Internet. XML consists of tags within a text document that define the structure of the document. XML documents can be easily processed through HTML. Although most SQL statements return their results in a relational (tabular) result set, the SQL Server 2000 database component supports a FOR XML clause that causes the results to be returned as an XML document. SQL Server 2000 also supports XPath queries from Internet and intranet applications.

[4]. Which edition of SQL Server 2000 includes the complete SQL Server offering?

SQL Server 2000 Enterprise Edition

[5]. What is the purpose of the SQL Server 2000 relational database engine?

The SQL Server 2000 relational database engine is a modern, highly scalable engine for storing data. The database engine stores data in tables. Applications submit SQL statements to the database engine, which returns the results to the application in the form of a tabular result set. Internet applications submit either SQL statements or XPath queries to the database engine, which returns the results in the form of an XML document. The relational database engine provides support for common Microsoft data access interfaces, such as ADOs, OLE DB, and ODBC.

[6]. What SQL Server 2000 technology helps you build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources?

DTS

[7]. What are at least four administrative tasks that you can use the Enterprise Manager to perform?

Any four of the following tasks:

Defining groups of servers running SQL Server

Registering individual servers in a group

Configuring all SQL Server options for each registered server

Creating and administering all SQL Server databases, objects, logins, users, and permissions in each registered server

Defining and executing all SQL Server administrative tasks on each registered server

Designing and testing SQL statements, batches, and scripts interactively by invoking Query Analyzer

Invoking the various wizards defined for SQL Server

[8]. Which tool is commonly used to create queries and execute them against SQL Server databases?

Query Analyzer

[9]. What are at least five objects that can be included in a logical database?

Table,Data type,View,Stored procedure,Function,Index,Constraint,Rule,Default,Trigger

[10]. What are the major components involved in processing a SQL statement received from a SQL Server client?

The client, the tabular data stream, the server Net-Library, and SQL Server (the relational database engine)

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.

Store Procedures Vs User Defined Functions

Store Procedures		User Defined Functions
1	A stored procedure is a program (or procedure) which is physically stored within a database.	A user-defined function is a routine that encapsulates useful logic for use in other queries.
2	Procedure can return zero or n values.	Function returns only one value which is mandatory.
3	Procedure can have Input, Output Parameter.	UDF can have only Input Parameters.
4	Procedure allow select as well as DML statement in it	Functions allow only select statement in it.
5	Procedure can not be called from function.	Functions can be called from Procedure
6	We can go for transaction management in procedure	In function We can’t
7	Procedures can not be utilized in a select statement	Functions can be embedded in a select statement.

Where Vs Having

WHERE		HAVING
1	It is used for restricting rows from table.	It is used for restricting rows which are Grouped.
2	No need to use Group by clause when you are using Where clause.	You have to use Group by Clause When you are using Having Clause.
3	Example : SELECT ENAME,SALARY FROM EMP WHERE SALARY>10000 It returns all employee whose salary > 10000	SELECT DEPTNO,SUM(SALARY) FROM EMP GROUP BY DEPTNO HAVING SUM(SALARY)>10000 It returns all department whose total salary > 10000

Rollup Vs Cube

Rollup		Cube
1	The ROLLUP operator is useful for generating reports that contain subtotals and totals	The Cube operator is useful for generating reports that contain totals
2	ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.	CUBE generates a result set showing aggregates for all combinations of values in the selected columns.
3	Example : SELECT DEPTNO,JOB,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPTNO,JOB WITH ROLLUP It will give u total and subtotal of all department as well as job.	Example : SELECT DEPTNO,JOB,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPTNO,JOB WITH CUBE It will give u total of all departments.

Charindex Vs Patindex

CharIndex		PatIndex
1	Returns the Starting position of specified Expression in Character String.	Returns the Starting position of First Occurrence of a pattern in Specified Expression, Or Zeros if Pattern is not found.
2	In Charindex you can specify Start location.	In PatIndex you can not specify starting location. It will return you First Occurrence of a pattern
3	SELECT CHARINDEX(‘;’,’KRISHNA;RADHA’) Output : 8 It will give u position of Semicolon.	Example : SELECT PATINDEX(‘%A%’,’KRISHNA;RADHA’) Output : 7 It will give u position of First occurrence of A.

System Datatype Vs User Defined Datatype

System Datatype		User Defined Datatype
1	System Data type is defined by the System.	User Defined Data type defined by the User.
2	There are more than 25 System Data type available in SQL Server.	User Defined Data type is also System data type, just name of that Data type is Changed by the User.
3	Example : Col1 BIGINT In this Example BIGINT is a System Data type	Example : SP_ADDTYPE ‘pk’, BIGINT In this Example ‘PK’ is User Defined Data type. You can use PK Anywhere instead of BIGINT.