Interview Questions → SQL SERVER → SQL Server Questionnaire III

SQL Server Questionnaire III

Which virtual table does a trigger use?

Inserted and Deleted

How can we rename the database?

  EXEC sp_renamedb 'oldName', 'newName'

How can we rename a table?

  EXEC sp_rename 'oldName', 'newName'

What is heap table?

A table does not have any index i.e. clustered and non clustered is called a heap table

What are the basic functions for master, msdb, tempdb and model databases?

The Master database holds information for all databases located on the SQL Server instance.

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

The model is a template database used in the creation of any new user database created in the instance

Which TCP/IP port does SQL Server run on?

1433

Which command using Query Analyzer will give you the version of SQL server and operating system?

Select @@version

What is deterministic and nondeterministic functions in sql server?

Deterministic functions are those which returns the same output all the time it is executed with the same input values.

For example

select datediff(dd,getdate(),getdate()+2)

will always return 2.

Nondeterministic functions are those which may return different output each time they are executed.

For example

select rand()

always gives different values each time they are executed.

What is @@error?

@@error is a built in function which returns the error number of last T-SQL command executed.

Consider a table

CREATE TABLE UniqueConstraintTable
(
	pk_id INT IDENTITY(1,1),
	other_id INT UNIQUE
)

Insertion into this table throws an error when an attempt is made to insert a duplicate value in other_id column.

CREATE PROC testuniqueness
AS
  BEGIN
  INSERT INTO UniqueConstraintTable VALUES(1)
  INSERT INTO UniqueConstraintTable VALUES(1)
  SELECT @@ERROR
END

This proc will insert duplicate value into the table. So on calling the proc

EXEC testuniqueness

The following error will be displayed

Msg 2627, Level 14, State 1, Procedure testuniqueness, Line 4

Violation of UNIQUE KEY constraint 'UQ__UniqueCo__722B49D907F6335A'. Cannot insert duplicate key in object 'dbo.UniqueConstraintTable'. The duplicate key value is (1).

The statement has been terminated.

Msg 2627, Level 14, State 1, Procedure testuniqueness, Line 5

Violation of UNIQUE KEY constraint 'UQ__UniqueCo__722B49D907F6335A'. Cannot insert duplicate key in object 'dbo.UniqueConstraintTable'. The duplicate key value is (1).

The statement has been terminated.

The statement @@ERROR returns 2627 which is the error code for the error raised when duplicate value is inserted into the table.

Can a stored procedure calls itself?

Yes. Consider an example

create proc recursive_proc
@num int
as
print @num
if @num > 1
begin
	set @num = @num - 1
	exec recursive_proc @num
end

The above proc when called, will execute recursively till if condition is not satisfy.

Posted By -

Karan Gupta

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**