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.

Truncate Vs Delete

Truncate		Delete
1	It is DDL(Data Definition Language) Statement.	It is DML(Data Manipulation Language) Statement.
2	It Delete all rows from table.	You Can Delete All rows as well as specified rows using Delete.
3	You can not Rollback all raw.	Using Rollback you can retrieve all rows from the point of last commit.
4	It release all raw including Memory space, Just Structure remains there.	It delete all raw but, not Memory space.
5	Example : Truncate Table Emp	Example : Delete From Emp Where Empno=7369

Small DateTime vs DateTime

Small DateTime		DateTime
1	It Occupies 4 byte Size	It Occupies 8 byte Size
2	The precision of small datetime is one minute	The precision of datetime is 3.33 milli Seconds
3	It Stores Date From 1st January 1900 through 6th June 2079. Which is usually more than enough	It Stores Date From 1st January 1753 through 31st December 9999.

Matching Data Between Rows and Columns

DECLARE @Cols TABLE(Col INT)

INSERT INTO @Cols VALUES (1)
INSERT INTO @Cols VALUES (2)
INSERT INTO @Cols VALUES (3)
INSERT INTO @Cols VALUES (4)
INSERT INTO @Cols VALUES (5)
INSERT INTO @Cols VALUES (6)
INSERT INTO @Cols VALUES (7)
INSERT INTO @Cols VALUES (8)
INSERT INTO @Cols VALUES (9)

DECLARE @Rows TABLE(Row INT)

INSERT INTO @Rows VALUES (100)
INSERT INTO @Rows VALUES (104)
INSERT INTO @Rows VALUES (101)
INSERT INTO @Rows VALUES (99)
INSERT INTO @Rows VALUES (77)
INSERT INTO @Rows VALUES (20)
INSERT INTO @Rows VALUES (10)

Col
-----------
1
2
3
4
5
6
7
8
9

Row
-----------
100
104
101
99
77
20
10

The challenge is to mark a coordinate, with a value of X,
if and only if the row value is divisible by the col value,
i.e. it has a modulo of zero. The additional requirements are:
the final query must work with random row values.

Row         1    2    3    4    5    6    7    8    9   Total
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
10          x    x              x                          3
20          x    x         x    x                          4
77          x                             x                2
99          x         x                             x      3
100         x    x         x    x                          4
101         x                                              1
104         x    x         x                   x           4
----------------------------------------------------------------
Total       7    4    1    3    3     0    1   1     1     21

Challenge Requirements Summary

1. Only coordinates where the column value modulo the row value equals zero should be marked, with an "X"
2. Number of rows in the table is not fixed. The query should work with variable number of rows in the table.