Author: Rafael Copquin
Posted: 2013-11-14 at 16:05:16
I would like an explanation of what a clustered index is and the other
types of indexes in SQL Server.
I really don't understand the differences or their significance,
especially in relation to what I have known for years on VFP indexes.
You seem to be quite a pro in SQL Server so can you help?
TIA
Rafael Copquin
El 14/11/2013 14:41, Stephen Russell escribió:
> Stealing from a Tips book or white paper.
>
> SELECT * is not always a bad thing, but it’s a good idea
> to only move the data you really need to move and only
> when you really need it, in order to avoid network, disk,
> and memory contention on your server.
>
> For small sets of data that are
> infrequently updated such as lookup
> values, build a method of caching
> them in memory on your application
> server rather than constantly querying
> them in the database.
>
> *--**- this is a big one in that every DB has the same problem.
> A function on columns in the WHERE clause or JOIN
> criteria means that SQL Server can’t use indexes
> appropriately and will lead to table scans and slow
> performance.
>
> Avoid using a column in a clustered index that has
> values that are frequently updated.
>
> You do not have to make the primary key the clustered
> index. This is default behavior but can be directly
> controlled.
>
> Clustered indexes work well on columns that are used
> a lot for ‘range’ WHERE clauses such as BETWEEN and
> LIKE, where it is frequently used in ORDER BY clauses
> or in GROUP BY clauses.
>
> If you join to a Date|Time table adding that dtmAdded column to the primary
> key is very helpful and if you filter on that column it will greatly help
> your throughput.
>
> Only create non-clustered indexes on tables when you
> know they’ll be used through testing. You can seriously
> hurt performance by creating too many indexes on a
> table.
>
> While it is possible to over-normalize a database,
> under-normalization is much more prevalent. This
> leads to repetition of data, inefficient storage, and poor
> performance. Data normalization is a performance
> tuning technique as well as a storage mechanism.
>
>
> All from Red Gate.
>
>
>
> Stephen Russell
> Sr. Analyst
> Ring Container Technology
> Oakland TN
>
> 901.246-0159 cell
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/5285491C.5080002@fibertel.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.