.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Sandeep Singh
Melody Anderson
Sharad
Eminent IT


Find questions, FAQ's and their answers related to .NET, C#, Vb.Net, Sql Server and many more.


Post New Question Subscribe to Interview Questions
 
Quick Links For Interview Questions Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  OOPs  JQuery  JavaScript/VBScript
BiztalkPatten/PracticesIISWCFWPFWWF
NetworkingAptitudeOthers  All    
 

Sql Server Interview Questions and Answers

What is correlated sub-query?

Author: volvo          Posted Date: November 28, 2013    Category: Sql Server     Points: 40

A correlated sub-query is a term used for specific types of queries in SQL. It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query.

Here is an example for a typical correlated sub-query. In this example we are finding the list of employees (employee number and names) having more salary than the average salary of all employees in that employee's department.

SELECT employee_number, name
FROM employee AS e1
WHERE salary > (SELECT avg(salary)
FROM employee
WHERE department = e1.department);

What are different types of cursors available in SQL Server?

Author: volvo          Posted Date: November 28, 2013    Category: Sql Server     Points: 40

Following are different types of cursors available in SQL Server 2005
Base table
Static
Forward-only
Forward-only/Read-only
Keyset-driven
Base table: Base table cursors are the lowest level of cursor available. Base table cursors can scroll forward or backward with minimal cost, and can be updated
Static: Cursor can move to any record but the changes on the data can't be seen.
Dynamic: Most resource extensive. Cursor can move anywhere and all the changes on the data can be viewed.
Forward-only: Cursor moves one step forward, can't move backwards.
Keyset-driven: Only updated data can be viewed, deleted and inserted data cannot be viewed.

What is difference Between GETDATE and SYSDATETIME

Author: volvo          Posted Date: November 27, 2013    Category: Sql Server     Points: 40

When we use GETDATE the precision is till miliseconds and in case of SYSDATETIME the precision is till nanoseconds.

What does the ERROR_PROCEDURE() function return?

Author: volvo          Posted Date: November 25, 2013    Category: Sql Server     Points: 40

The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH construct and returns the name of the procedure that caused an error to be thrown.

What does the CEILING() function do?

Author: volvo          Posted Date: November 25, 2013    Category: Sql Server     Points: 40

Returns the smallest integer greater than or equal to the value passed in.
CEILING() returns the smallest integer that is great than or equal to the value passed in.

What does @@textsize return?

Author: volvo          Posted Date: November 25, 2013    Category: Sql Server     Points: 40

The current value of the TEXTSIZE option for the SET command.
This returns the current value of the TEXTSIZE option that can be changed with the SET command. The default is 4096 bytes.

What does the DEALLOCATE statement do in SQL Server

Author: volvo          Posted Date: November 25, 2013    Category: Sql Server     Points: 40

Remove a reference to a cursor.
This statement is used to remove cursor references. If this is the last reference being removed, the cursor's resources are released.

How to get basic SQL Server table structure information

Author: volvo          Posted Date: November 23, 2013    Category: Sql Server     Points: 40

sp_help will give you a whole bunch of information about a table including the columns, keys and constraints.

For example

exec sp_help 'Employee'

will give you information about table Employee.

What is difference between Primary Key and Unique Key?

Author: volvo          Posted Date: November 21, 2013    Category: Sql Server     Points: 40

1.Primary Key can't accept null values Whereas Unique key can accept only one null value.

2.By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index whereas Unique key is a unique non-clustered index.

3.We can have only one Primary key in a table Whereas We can have more than one unique key in a table.

What are different types of SQL keys?

Author: volvo          Posted Date: November 21, 2013    Category: Sql Server     Points: 40

1.Primary Key
Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

2.Foreign Key
Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.


3.Super Key
Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys.

4.Candidate Key
A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

5.Alternate key
A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.


6.Composite/Compound Key
Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

7.Unique Key
Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.



Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend