SQLAuthority News – Best Articles on SQLAuthority.com

SQL SERVER – Cursor to Kill All Process in Database

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure

SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Simple Example of Cursor

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

SQL SERVER – T-SQL Script to find the CD key from Registry

SQL SERVER – Delete Duplicate Records – Rows

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

SQL SERVER – Union vs. Union All – Which is better for performance?

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

SQL SERVER – Performance Optimization of SQL Query and FileGroups

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling

SQL SERVER – Script to find SQL Server on Network

SQL SERVER – Stored Procedures Advantages and Best Advantage

SQL SERVER – CASE Statement/Expression Examples and Explanation

SQL SERVER – Raid Configuration – RAID 10

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

SQL SERVER – Six Properties of Relational Tables

SQL SERVER – TRIM() Function – UDF TRIM()

SQL SERVER – Difference between Unique Index vs Unique Constraint

SQL SERVER – 2005 Locking Hints and Examples

SQL SERVER – Good, Better and Best Programming Techniques

SQL SERVER – Random Number Generator Script – SQL Query

SQL SERVER – 2005 TOP Improvements/Enhancements

SQL SERVER – 2005/2000 Examples and Explanation for GOTO

SQL SERVER – Explanation SQL Commando GO

SQL SERVER – 2005 – List all the database

SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

SQL SERVER – Disable Index – Enable Index – ALTER Index

SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

SQL SERVER – SQL Code Formatter Tools

SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases

SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

SQL SERVER Database Coding Standards and Guidelines Complete List Download

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

SQL SERVER – 2005 Row Overflow Data Explanation

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

SQL SERVER – Count Duplicate Records – Rows

SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

SQL SERVER – One Thing All DBA Must Know

SQL SERVER – 2005 – List Tables in Database Without Primary Key

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SQL SERVER – UDF – Validate Integer Function

SQL SERVER – What is SQL? How to pronounce SQL?

SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL SERVER – Find Last Day of Any Month – Current Previous Next

SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers

Just a day ago, I received question in email regarding my article SQL SERVER – 2005 Disable Triggers – Drop Triggers.

Question : How to disable all the triggers for database? Additionally, how to disable all the triggers for all servers?
Answer:
Disable all the triggers for a single database:
USE AdventureWorks;
GO
DISABLE
TRIGGER Person.uAddress ON AdventureWorks;
GO

Disable all the triggers for all servers:
USE AdventureWorks;
GO
DISABLE
TRIGGER ALL ON ALL SERVER;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL-Triggers

SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Update: This article is re-written with SQL Server 2008 R2 instance over here: SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.

Step 1 : Start

databasecopy1 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 2 : Welcome Screen

databasecopy2 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 3 : Select One or Multiple Database
If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)

databasecopy3 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 4 : Select database options

databasecopy4 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 5 : Select output option

databasecopy5 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 6 : Review Summary

databasecopy6 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 7 : Observe script generation process

databasecopy7 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Step 8 : Database object script generation completed in new query window

databasecopy8 SQL SERVER   2005   Create Script to Copy Database Schema and All The Objects   Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Reference : Pinal Dave (http://blog.SQLAuthority.com), All images are protected by copyright owner SQLAuthority.com

SQL SERVER – 2005 – Server and Database Level DDL Triggers Examples and Explanation

I was asked following questions when discussing security issues in meeting with off-shore team of large database consultancy company few days ago. I will only discuss one of the security issue was discussed accidental data modification by developers and DBAs themselves.

How to alter modification in database by system admin himself?

How to prevent accidents due to fat fingers? (Accidental execution of code)

How to display message to contact another developers when another developer tries to modify object another developer working on?

It was interesting conversation. Answer to all the questions is correct assignment of permissions and (when permissions are not an issue) Server and Database Level DDL Triggers. If developers have their own login to SQL Server and it does not have permissions to drop or create objects this will not be issue at all. However, there are still risk of System Admin himself making accidental mistakes. The solution to this will be use Server and Database Level DDL Triggers.

DDL is abbreviation of Data Definition Level. DDL contains schema of the database object. It was always dream of all DBA, when change in mission critical schema of the database or server is attempted it is prevented immediately informing DBA and users automatically. DDL Trigger can now make this dream true. Definition of DDL Trigger (from BOL) is DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

DML is abbreviation of Data Manipulation Level. DML contains the actual data which is stored in the database schema. UPDATE, INSERT, DELETE, SELECT are clause are used to manipulate database. There is following different between DML and DDL triggers.

  • DDL triggers do not support INSTEAD OF feature DML triggers.
  • DDL triggers do not have feature of inserted and deleted tables like DML triggers as it does not participate in database manipulations.

Following example demonstrates how DDL trigger can be used to prevent dropping stored procedure.

Step 1 :

First create any sample stored procedure.
USE AdventureWorks; 
GO 
CREATE PROCEDURE TestSP             
AS 
SELECT 1 test; 
GO 

Step 2 :

Create DDL trigger which will prevent dropping the stored procedure.

USE AdventureWorks 
GO 
CREATE TRIGGER PreventDropSP             
ON DATABASE             
FOR DROP_PROCEDURE             
AS
PRINT 'Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script. 
Script : DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP>; ENABLE TRIGGER PreventDropSP ON DATABASE;' 
ROLLBACK; 
GO 

Step 3 :

Now test above trigger by attempting to drop the stored procedure.

USE AdventureWorks 
GO 
DROP PROCEDURE TestSP; 
GO 

This should throw following message along with error code 3609 :

Dropping Procedure is not allowed.

DDL Trigger is preventing this from happening.

To drop stored procedure run following script.

Script :

DISABLE TRIGGER PreventDropSP ON DATABASE;

<Run your DROP SP>;

ENABLE TRIGGER PreventDropSP ON DATABASE;

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

Step 4 :

Now DISABLE above trigger and it will let you successfully drop the stored procedure previously attempted to drop. Once it is dropped enable trigger again to prevent future accidents.

USE AdventureWorks 
GO             
DISABLE TRIGGER PreventDropSP ON DATABASE;
DROP PROCEDURE TestSP;
ENABLE TRIGGER PreventDropSP ON DATABASE; 
GO 

List of all the DDL events (DROP_PROCEDURE in example above) to use with DDL Trigger are listed on MSDN.

If you want to download local copy click here.

Reference : Pinal Dave (http://www.SQLAuthority.com), BOL

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

This is another popular question I receive. How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

Syntax
sp_helptext @objname = 'name'

sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name
Displaying the definition of a trigger or stored procedure
sp_helptext 'dbo.nameofsp'

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – Disadvantages (Problems) of Triggers

One of my team member asked me should I use triggers or stored procedure. Both of them has its usage and needs. I just basically told him few issues with triggers. This is small note about our discussion.

Disadvantages(Problems) of Triggers

  • It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.
  • Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
  • It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
  • It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
  • Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.

I do not use triggers. In my whole career I am able to get my work done using Stored Procedures instead of Triggers. (Implementation and Architecture changes are required if either of is to be used to support business logic).

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers SQL Server supports the nesting of triggers up to a maximum of 32 levels. Nesting means that when a trigger is fired, it will also cause another trigger to be fired. If a trigger creates an infinitive loop, the nesting level of 32 will be exceeded and the trigger will cancel with an error message. Recursive triggers When a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur.

Disable Nesting/Recursing Triggers
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

There is also alternate way to stop Trigger Recursion

ALTER DATABASE
databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting to certain level
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Good, Better and Best Programming Techniques

A week ago, I was invited to meeting of programmers. Subject of meeting was “Good, Better and Best Programming Techniques”. I had made small note before I went to meeting, so if I have to talk about or discuss SQL Server it can come handy. Well, I did not get chance to talk on that as it was very causal and just meeting and greetings. Everybody just talked about what they think about their job. I talked very briefly about SQL Server, my current job and some funny incident at work.

Everybody laughed big when I talked about funny bug ticket I received which was about – Client does not receive Email sent by system. Well, at the end it was resolved without any programming as client did not have email address and needed to open one.

Well, here is my note which I prepared to discuss in meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.

Notes prepared for “Good, Better and Best Programming Techniques” meeting

Do not prefix stored procedure with SP_ prefix. As they are first searched in master database, before it is searched in any other database.

Always install latest server packs and security packs.

Make sure your SQL Server runs on optimal hardware. If your operating system supports 64 bit SQL Server, install 64 bit SQL Server on it. Raid 10 Array.

Reduce Network Traffic by using Stored Procedure. Return only required result set from database. If application needs paging it should have done in SQL Server instead of at application level.

After running query check Actual Execution Plan for cost of the query. Query can be analyzed in Database Engine Tuning Advisor.

Use User Defined Functions sparsely, use Stored Procedures instead.

Stored Procedure can achieve all the tasks UDF can do. SP provides much more features than UDFs.

Test system with realistic data rather than sample data. Realistic data provides better scenario for testing and reveals problems with real system before it goes to production.

Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Avoid Cursors as it results in performance degradation. Sub Query, derived tables, CTE can perform same operation.

Reduces the use of nullable columns.

NULL columns consumes an extra byte on each column used as well as adds overhead in queries. Also NULL is not good for logic development for programmers.

Reduce deadlocks using query hints and proper logic of order in columns.

Normalized database always increases scalability and stability of the system. Do not go over 3rd normal form as it will adversely affect performance.

Use WHERE clauses to compare assertive logic. Use IN rather than NOT IN even though IN will require more value to specify in clause.

BLOBS must be stored filesystem and database should have path to them only. If path is common stored them in application variable and append with filename from the BLOBColumnName.

Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.

SQL Server optimizer will use an index scan if the ORDER BY clause is on an indexed column.

Stored Procedure should return same numbers of resultset and same columns in any input parameters. Result Set of Stored Procedure should be deterministic.

Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.

Format SQL Code. Make it readable. Wrap it.

Use Column name in ORDER BY clause instead of numbers.

Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).

Join tables in order that they always perform the most restrictive search first to filter out the maximum number of rows in the early phases of a multiple table join.

Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.

Do not use temp tables use CTE or Derived tables instead.

Always take backup of all the data.

Never ever work on production server.

Ask someone for help if you need it. We all need to learn.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers.

Download SQL Server Interview Questions and Answers Complete List

Thank you all for your appreciation about the my recent series of SQL Server Interview Questions and Answers. I enjoyed writing questions and answers. I have got many emails about complete series.

Top most request was to collect series in one big post so they can be easily used. I was asked to provide links to download them so they can be printed and referred.

Another question I received is do I ask the same questions in interviews which I administrate.
Answer is YES. Though, I have few other questions, which I ask. All of them I came up myself and answers are unique to questions and not available on-line. Well, long story short, I have compiled list of questions in one PDF. Please download them and use them in your next interview or just for reading purpose.

Download SQL Server Interview Questions and Answers Complete List

Complete Series of SQL Server Interview Questions and Answers
SQL Server Interview Questions and Answers – Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download


Other popular Series

SQL SERVER Database Coding Standards and Guidelines Complete List Download
SQL SERVER – Data Warehousing Interview Questions and Answers Complete List Download
DBA Database SQL Job List Search

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server Interview Questions and Answers – Part 6

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers.

What are the properties of the Relational tables?
Relational tables have six properties:

  • Values are atomic.
  • Column values are of the same kind.
  • Each row is unique.
  • The sequence of columns is insignificant.
  • The sequence of rows is insignificant.
  • Each column must have a unique name.

What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

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 Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution.  If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins?
Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

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.

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

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.

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.

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.

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

List few advantages of Stored Procedure.

  • 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.

What is DataWarehousing?

  • Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
  • Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
  • Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
  • Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What is OLTP(OnLine Transaction Processing)?
In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)
You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.

OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

Complete Series of SQL Server Interview Questions and Answers
SQL Server Interview Questions and Answers – Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)