DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to increase performance by indexing?

If a Microsoft SQL Server 2005 database has two or more tables that are frequently joined together, then the columns used for the JOINs should have an index configured. If the columns used in the join are "wide", for example Email varchar(70), then use a surrogate key for the JOINs such as

EmailID INT IDENTITY(1,1) PRIMARY KEY

INT is 4 bytes fixed length column yields extremely good performance in JOINs. Example for index create:

-- T-SQL create index - create nonclustered index on contactid column

CREATE NONCLUSTERED INDEX [idxContactPK] ON [Person].[Contact] ([ContactID] ASC)

GO

Regular index maintenance should include rebuilding indexes every weekend. Extremely dynamic OLTP tables should be rebuilt with FILLFACTOR 70-90.

-- MSSQL index rebuild with fillfactor

USE AdventureWorks;

GO

ALTER INDEX ALL ON Sales.SalesOrderDetail

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

GO

Covering indexes are good for queries which are executed very frequently, business critical or mission critical. SQL Server 2005 allows the inclusion of nonkey (not indexed) columns in covering indexing. That is, all columns referenced in the query are included in the index as either key or nonkey columns. Composite index can also cover a query (pre-2005). This allows the query optimizer to locate all the required information from an index seek or scan; the table or clustered index data will not be accessed. Example: Category and Subcategory form a composite index key. Frequent query includes Category, Subcategory, Color and Size. Including Color and Size as nonkey columns in the index will speed up the query significantly.

-- SQL convering index demo - INCLUDE option in create index

USE AdventureWorks;

GO

IF EXISTS (SELECT name FROM sys.indexes

            WHERE name = N'idxContactCovering')

    DROP INDEX idxContactCovering ON Person.Contact;

GO

-- Create index covers all the columns in the following query

CREATE NONCLUSTERED INDEX idxContactCovering

    ON Person.Contact (ContactID)

    INCLUDE (FirstName, LastName, EmailAddress);

GO

-- SQL covered query

SELECT FirstName+' '+ LastName as FullName, EmailAddress, ContactID

FROM Person.Contact

WHERE ContactID BETWEEN N'10000' and N'11000'

ORDER BY FullName;

GO

/* Partial results

 

FullName          EmailAddress                        ContactID

Alexandra Allen   [email protected]     10115

Alexandra Hall    [email protected]     10058

Alexandra Smith   [email protected]     10494

Alexandra Young   [email protected]     10181

Allison Allen     [email protected]       10160

*/

------------


Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE