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
---
_______________________________________________
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/CAJidMYLEU-M8T1WiczFgz0eRqzSj9w9gNorF+yN0Vx6zqJ-gUA@mail.gmail.com
** 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.
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.
On Thu, Nov 14, 2013 at 4:05 PM, Rafael Copquin <rcopquin@fibertel.com.ar>wrote:
> 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?
>
> ----------------
The answer here goes over the basics:
http://stackoverflow.com/questions/18304376/sql-server-when-to-use-clustered-vs-non-clustered-index
Brent Ozar is one to the top consultants in M$SS and this page on his site
can start to answer that real big question you asked. Indexes are the #1
tweakable aspect of a schema that will cost you nothing to change and give
you great return if you do it well. Indexes follow the Less Is More
concept.
http://www.brentozar.com/sql/index-all-about-sql-server-indexes/
You have to pay attention to the data type as well as the use of the index
to determine what is best. How adding data to the table may poor for one
type of index and better for the other. the page of data in an index is
better described here.
http://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
In general Clustered index on an Int key is good. Clustered index on a
Guid key is bad. Why takes a while to explain. :)
HTH.
--
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
---
_______________________________________________
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/CAJidMYLa+5c8jeu8rwYRfSVKsXTRKvtKQrZGcangduhWTpOkiA@mail.gmail.com
** 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.
Like I said: you're a pro! :-)
Thank you
Rafael Copquin
El 14/11/2013 19:30, Stephen Russell escribió:
> On Thu, Nov 14, 2013 at 4:05 PM, Rafael Copquin <rcopquin@fibertel.com.ar>wrote:
>
>> 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?
>>
>> ----------------
>
> The answer here goes over the basics:
> http://stackoverflow.com/questions/18304376/sql-server-when-to-use-clustered-vs-non-clustered-index
>
>
> Brent Ozar is one to the top consultants in M$SS and this page on his site
> can start to answer that real big question you asked. Indexes are the #1
> tweakable aspect of a schema that will cost you nothing to change and give
> you great return if you do it well. Indexes follow the Less Is More
> concept.
>
> http://www.brentozar.com/sql/index-all-about-sql-server-indexes/
>
> You have to pay attention to the data type as well as the use of the index
> to determine what is best. How adding data to the table may poor for one
> type of index and better for the other. the page of data in an index is
> better described here.
> http://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
>
> In general Clustered index on an Int key is good. Clustered index on a
> Guid key is bad. Why takes a while to explain. :)
>
> HTH.
>
_______________________________________________
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/5285579D.3010609@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.
Author: MB Software Solutions General Account
Posted: 2013-11-15 11:33:12 Link
On Thu, November 14, 2013 12:41 pm, Stephen Russell wrote:
> Only create non-clustered indexes on tables when you
> know theyll be used through testing. You can seriously hurt performance by
> creating too many indexes on a table.
Worked with this female boss at IDP in Wyncote, PA who would index nearly
every field in a table. I explained how that wasn't a good idea. When
she blasted me for using ASSERTS in my programs saying that the
end-user/customer would see them, that was the last straw. Could not
STAND the woman. She'd read the first line of my email then shout
questions from her cube to me that were answered beyond the first sentence
of the email. Infuriating that they gave her HUGE bucks to keep her sorry
ass. Later on I had learned of her being out of work. Finally, somebody
could see the truth. I don't wish anyone to be out of work, but she had
her job WAY too long. Had she not shot her venom at me and thus made
working there painful for me, I probably wouldn't have cared.
_______________________________________________
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/7864fb271a7f4b75f48ef4b820b1ffd7.squirrel@webmail.dssco.net
** 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.