Sql Server Interview Questions and Answers (1772) - Page 7

How to Get nth Record in a Table?

First Get the n records fron the table using

Select Top n FROM UserTable
Now Reverse the Order using identity Column like:
Select Top n FROM UserTable Order By 1 DESC

Now we need nth record that can be get as
SELECT TOP 1 * FROM (Select Top n FROM UserTable Order By 1 DESC)AC

For Example i need to get 5th record From userTable then quey will be like this:

What is Row_Number()?

ROW_NUMBER() returns a column as an expression that contain's the row number within the result set. This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change.
What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

Global temporary tables (created with a double “##”) are visible to all sessions. You should always check for existence of the global temporary table before creating it… if it already exists, then you will get a duplicate object error.

Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.
How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
What command do we use to rename a db, a table and a column?

To rename db
sp_renamedb ‘oldname’ , ‘newname’

To rename Table
SP_RENAME ‘oldTableName’, ‘NewTableName’

To rename Column
sp_RENAME ‘Table_First.Name’, ‘NameChange’ , ‘COLUMN’
Can we use Truncate command on a table which is referenced by FOREIGN KEY?

No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
Which of the following queries generates an error when executed? DECLARE @x VARCHAR(10), @y VARCHAR(10) SET @y = '' SELECT @x/0 -- query 1 SELECT @x/2 -- query 2 SELECT @y/0 -- query 3 SELECT @y/2 -- query 4

Answer: 3

Explanation: Queries 1 and 2 return NULL since @x is undefined. Query 4 returns 0 since the string '' is implicitly converted to 0. Query 3 returns a divide by 0 error.
What type of language is the SELECT command in SQL Server?


The SELECT statement is a Data Manipulation Language commend, not a Data Definition Language command.
Is There any other Methods in SQL Server 2005 to Read XML Data?

NOTE: This is objective type question, Please click question title for correct answer.
Name the method used in SQL Server to Read XML Data?

(1) OPENXML in SQL Server 2000

For SQL Server 2005:
(2) query()
(3) value()
(4) nodes()
(5) exists()
(6) Modify()
How we can Modify XMl Data in SQL Server 2005?

Using modify method we cam modify the data in XML.

set @value = 'val3'
set @xml.modify('replace value of (/root/item/@value)[1] with sql:variable("@value")')
select @xml
How to Insert XML Data into Existing XML Node?

declare @xml xml
set @xml = '<root/>'
select @xml

declare @value varchar(10)
set @value = 'val1'
set @xml.modify('insert <item value="{sql:variable("@value")}" /> into (/root)[1]')
select @xml
How we can generate Random number in SQL Server?

NOTE: This is objective type question, Please click question title for correct answer.
How we can get the List of System Tables in DataBase?

select * from Sys.Objects where Type='s'
How we can get the list of user tables?

select * from Sys.Objects where Type='u'
How We can Get List of Store Procedures?

select * from Sys.Objects where Type='p'
How We Can Get List of Scalar Functions?

select * from Sys.Objects where Type='fn'
Found this useful, bookmark this page to the blog or social networking websites. Page copy protected against web site content infringement by Copyscape

 Interview Questions and Answers Categories