Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Sitemap
Print E-mail
  • Index, if possible, on integer fields, rather than character. It makes for a much narrower index, which, in turn, means fewer pages.
-
  • Fewer indexes is are better, if you have a lot of data updating. Indexes carry overhead , and need to be updated when data in index fields changes. There is a trade off here.
-
  • When you create a primary key , or a unique key field, an index is automatically created. Don't duplicate indexes.
-
  • Don't keep indexes that are not used. Drop them- they can always be recreated.
-
  • When adding large amounts of data, or updating large amounts of data that involves indexed fields, drop the index and recreate after the data has been added or changed.
-
  • Index on columns that are used in JOIN, WHERE, ORDER BY and GROUP BY clauses.
-
  • Clustered indexes are better for queries that return a range of values, or GROUP BY and ORDER BY clauses. The clustered index can also be a composite index, but if it is too wide, the page space factor may begin to get costly in terms of performance. On the other hand, you want clustered indexes to be narrow because all of the non-clustered indexes contain the clustered index to locate data rows.
-
  • Clustered indexes are better on fields that are not updated frequently , because clustered index field updates require a physical resorting of the table.
-
  • Use non -clustered indexes on lookup queries and on fields with high selectivity.
-
  • Keep Index as narrow as possible to conserve disk and paging space.
-
  • SQL Server automatically create s a clustered index for the primary key. When key, when that key is a self-incrementing autonumber field. This tends to create a ‘hot spot' at the end of the table which that may become an I/O problem when new data is added to the table frequently. So specify NONCLUSTERED for the primary key constraint in those circumstances.
-
  • As a corollary to the preceding item, every table should have a clustered index to improve speed. It just should not the self
    -incrementing primary key.
-
  • Index only on fields with high selectivity.
-
  • In composite indexes, make the field that most where most clauses use as a search argument the leftmost column.
-
    May 23, 2006ve columns first. It increases the speed of the performance.
-
  • Look for the fields in JOIN clauses as likely candidates for indexing.
-
  • If an application uses the same query very frequently, create an index with all of the fields in that query.
-
  • Use the SQL Server Profiler Create Trace Wizard to outline indexing strategies.
-
  • Use DBCC REINDEX and DBCC INDEXDEFRAG as part of a regular maintenance program to recover fragmented indexes.

 

Resources

•  Tutorial: Introduction to Structured Query Language

This is an insightful resource on SQL.

•  Tutorial: SQL 7 Full-Text Indexing.

This is a comprehensive tutorial on SQL 7 Full Text Indexing.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080

 

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us

 

 
Bookmark and Share