Sql Server Interview Questions and Answers (1758) - Page 9

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

Can we use shared lock with Update Statement?

No We can't use the shared lock to Update or modifty data. shared lock is used for select operation.
e.g of shared lock is : WITH (HOLDLOCK)

What is the name of the System table from where we can read the Master Files (.mdf, .ldf) Path?

From sys.master_files table we can find the master file information for mdf file file_id is 1 and for ldf its value is 2.

Name of the table that contains the Configuration value of SQL Server ?

sys.configurations

What is the Name of table that contains available data type in SQL Server?

sys.systypes contains the Data type available in SQL Server.

What is the use of WAITFOR in SQL SERVER ?

Suppose we want to execute one query now one one after some time then we need to use WAITFOR TIME for this.

Sample Query:

BEGIN

   SELECT 'Lakhan Pal'

	DECLARE @time char(5)

	SET @time=CONVERT(char(5),DateAdd(mi, 1, GetDate()),108)

   WAITFOR TIME @time

   SELECT ' Garg'

END

In the above Code SELECT 'Lakhan Pal' will be execute first and then after 1 min second select statement SELECT ' Garg' will be executed.

What is the name of the Store procedure to get the listing of current users?

sp_who

Maximum number of parameters you can pass in a Store procedure in SQL Server 2005?

2100.

Are DDL Triggers available with SQL Server 2005 ?

Yes. They can be defined with create, alter, drop and other DDL statements.
For an example :
CREATE TRIGGER DDL_Trigger
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
PRINT 'Get permissions to drop/alter/create table!!'
ROLLBACK ;

How to pass table name as stored procedure parameter in SQL Server 2005 (Write stored procedure) ?

create PROCEDURE Pass_TableName_As_Param --create procedure
@TableName varchar(50) -- Parameter of table name
AS
BEGIN
SET NOCOUNT ON;
SET @TableName= RTRIM(@TableName) --Trim the parameter of table name
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * -- select query
FROM'+ @table_name
END

What is referential integrity in SQL Server ?

To avoid logically corruption of data we need to maintain relationships with most of the databases. Building and maintaining logical relationships between tables is essential part to work with relational databases and when such relationships exist, we say that the data has referential integrity. With such database one table is referenced table and the other is the referencing table and values with both the tables (referencing table and referenced table) must match.
We can enforce such referential integrity through foreign key constraints.

For an example,…

CREATE TABLE Table_Referenced 

(

id INT

CONSTRAINT pk_id PRIMARY KEY(id)--Create Primary Key here

);

GO



CREATE TABLE Table_Referencing

(

RID Int

CONSTRAINT fk_RID FOREIGN KEY(RID)REFERENCES Table_Referenced(id)--Creates foreign key names 'RID' which is referring primary key of Table_Referenced.id

);

GO

What maximum size of row is allowed in SQL Server ?

NOTE: This is objective type question, Please click question title for correct answer.

Write a SQL Statement to Get the Definition of the System Store Procedures?

SELECT definition FROM sys.system_sql_modules

Thanks & Regards
Lakhan Pal Garg

Write a Query in SQL Server to get the Parameter list of given Store Procedure.

Suppose we want to get the name of the parameter for the Store Procedure
Course_Insert_sp then folllowing needs to be executed.

SELECT * FROM sys.parameters D INNER JOIN Sys.Objects O ON O.object_id=D.object_id WHERE O.name='Course_Insert_sp'

Thanks & Regards
Lakhan Pal Garg

Which system procedure is used to find out lock details about the databases, it's table and procedures.

sp_lock

Which system table holds the details of all the processes running on the Microsoft sql server?

The name of the system table is sysprocesses .

Select * from sysprocesses

Make sure you run this query in master database.

How can we find the open transactions details in sql server?

Yes, it is possible.

select * from sysprocesses where open_tran > 0

This query will provide list of all the open transaction details.

What is the name of command in sql server 2005 which is used to kill any process?

The command name is kill.

Syntax is below:

Kill [Process ID]

Name of the command to view the current amount of free (unallocated) space in the database in SQL Server?

Name of command is :sp_spaceused

This command displays the database_size in MB, and it's also shows unallocated space for the database.

What is the command name to shrink the data file and log file size in SQL server 2005?

The command name is : DBCC SHRINKDATABASE (Database Name)

This command will shrink the data file and log file size. With this command you can specify that how much percentage space you want to free.

dbcc shrinkdatabase (TempDAabase,10)

This command will free only 10% space.

What are magic tables?

Sometimes we need to know about the data which is being inserted/deleted by triggers in database. With the insertion and deletion of data, tables named “INSERTED” and “DELETED” gets created in SQL Server which contains modified/deleted data.
Here, “INSERTED” and “DELETED” tables are called magic tables.