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

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

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:

SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By 1 DESC)AC

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 is a table called, if it has no Cluster Index?

Unindexed table or Heap.

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.

Can we use NEWID(), or RAND() in function?

Can we use "Print" statement in function?

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.