SQL Server

differences between sqlserver2000 and 2005
1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.

2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.

3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL

SERVER 2000.It was a separate installation for SQL Server 2000.

4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the

server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or

Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.

***********

Fill factor is the term associated with indexes actually with clustured indexes

Whenever a clustured index is created sql server physically orders data in basis of the clustured column. As

you must be aware that the data is sql server is stored in 8K data page. Fill factor value setting advice SQL

Server to leave specified amount of space free on each page inorder to accommodate new data  if no fill factor

is set SQL Server data page will be filled completely and incase a new record comes it will have to make space

in the data page by reorganizing rest of the pages which is an over head. And hence it is suggested to keep

reasonable value for fill factor considering future requirements.

CREATE CLUSTERED INDEX … FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent

If no value is specified, the default is 0.A fill factor value of 0 does not mean that pages are 0 percent

full.It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with

full data pages and nonclustered indexes with full leaf pages
You can change the default FILLFACTOR setting by executing sp_configure.

If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100

percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is

never added.

Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL

Server redistributes the data when it creates the clustered index.

Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions

without requiring page splits.

****************

Composite key:- A primary key that consistsof two or more
attributes is known as composite key

candidate key:- A key that uniquely identifies rows in a table .Any of identified candidate keys can be used as

primary key.

Alternate Key:- Any of the candidate keys that is not part
of the primary key is called an alternate key.It accepts null Values

*************

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword “deleted”

DELETE Vtbl1
OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3
SELECT * FROM @DeletedTable

( you can’t use magic table outside of the trigger in sql 2000 without output clause)

**********

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records

returned are unique from your union, use UNION ALL instead, it gives faster results.

***

The following example forces the current identity value in the EmployeeID column in the Employee table to a
value of 300

DBCC CHECKIDENT ("HumanResources.Employee", RESEED, 300);
**********

 Using Inline Query Hint

USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO

option hint

===========

SELECT c.ContactID
FROM Person.Contact c
INNER JOIN Person.Contact pc
ON c.ContactID = pc.ContactID
OPTION (TABLE HINT(c, INDEX (AK_Contact_rowguid)),
TABLE HINT(pc, INDEX (PK_Contact_ContactID)))