SQL Server Interview Questions - D

4.1 Can SQL Servers linked to other servers like Oracle?
SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

4.2 How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints

4.3 How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

4.4 What is 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 have a hierarchal reporting structure whereby one member of staff reports to another.

4.5 What is 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.

4.6 Which virtual table does a trigger use?
Inserted and Deleted.

4.7 List few advantages of Stored Procedure. table?
Stored procedure can reduced network traffic and latency, boosting application performance. · Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
· Stored procedures help promote code reuse.
· Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
· Stored procedures provide better security to your data.

No comments: