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


Top 5 Contributors of the Month
Lee Hnetinka
mithun


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

How to delete duplicate rows from table in sql

Author: Ashutosh Jha          Posted Date: July 30, 2014    Category: Sql Server     Points: 40

One can achieve this like below-

DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);

What are different Types of Join?

Author: Nargani          Posted Date: July 22, 2014    Category: Sql Server     Points: 40


Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

. Left Outer Join: In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
. Right Outer Join: In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
. Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

What's the difference between a clustered and a non-clustered index

Author: Ashutosh Jha          Posted Date: July 01, 2014    Category: Sql Server     Points: 40

A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.

What is Magic Table in SQL Server?

Author: abhays          Posted Date: June 12, 2014    Category: Sql Server     Points: 40

Magic Tables are invisible tables which created on MS SQL Server, during INSERT/UPDATE/DELETE operations on any table. These tables temporarily persists values before completing the DML statements.

SQL Server allows you to define a Magic Table. Magic Tables are invisible tables or virtual tables. You can see them only with the help Triggers in SQL Server. Magic Tables are those tables which allow you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server. So let's have a look at a practical example of how to use Magic Tables in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

These are the two Magic Tables:
1.Inserted
2.Deleted

Generally Magic Tables are invisible tables, we can only see them with the help of Trigger's in SQL Server.


What are Cursors?

Author: Ashutosh Jha          Posted Date: May 30, 2014    Category: Sql Server     Points: 40

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

What is the default port number for SQL Server

Author: Ashutosh Jha          Posted Date: May 30, 2014    Category: Sql Server     Points: 40

Basically, when SQL Server is enabled the server instant listens to the TCP port 1433.

What's the difference between a clustered and a non-clustered index

Author: Ashutosh Jha          Posted Date: May 30, 2014    Category: Sql Server     Points: 40

clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.

Define a temp table. What's the difference between a local temp table and a global temp table?

Author: Ashutosh Jha          Posted Date: May 30, 2014    Category: Sql Server     Points: 40

In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.
Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.

What is the difference between JOIN and UNION

Author: Ashutosh Jha          Posted Date: May 30, 2014    Category: Sql Server     Points: 40

SQL JOIN allows us to "lookup" records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.

What is the difference between UNION and UNION ALL

Author: Ashutosh Jha          Posted Date: May 30, 2014    Category: Sql Server     Points: 40

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.



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