September 20, 2004 at 1:10 pm
Hi all,
Just wanted to know how would it impact having a clustered index on a column with datatype varchar(40) or nvarchar(40) ? any help will be greatly appreciated.
TIA
September 20, 2004 at 2:39 pm
What is the role of the database? Is it OLTP (Online Transaction Processin) or OLAP (Decision Making, frequently queried database) ? If you do a lot of queries by this field by range like
select * from something where myvarcharfield more then thisvalue and myvarcharfield less then thatvalue
then you will be fine with the clustered index. But if you do a lot of inserts then maybe it is not such a good idea. Maybe you will be better off with fully covered query which uses non-clustered indexes. See also
Microsoft Knowledge Base Article - 110352
INF: Optimizing Microsoft SQL Server Performance
Regards,Yelena Varsha
September 21, 2004 at 7:11 am
Yelena I believe addressed clustering in general. There are a number of people who believe that almost all tables should be clustered for maintenance/performance purposes and there are likely lots of discussions about it at this site. The following link was in my virtual briefcase and addresses that approach: http://www.sqlservercentral.com/columnists/chedgate/clusterthatindex.asp
To continue, I am making an assumption here that your primary concern is with the data size/type. I don't think that having it be clustered vs non-clustered would make that big of a difference. If anything, a clustered index should reduce the significance of having larger index rows vs. a non-clustering index. The reason is that the leaf pages have the entire row and so the size of the index columns (individually) only come into play on the node pages.
Furthor, nvarchar would, of course, double the storage per used character, but the pages are not padded for the full possible length. I.E. some index node pages may contain more rows than others based upon how much of the variable length data is actually used. If your average column size is 5 characters vs 35 characters it will store more rows per page on average.
The following DBCC commands (most undocummented) can help you learn about your index structure if you wish to spend the time trying to figure out how to use DBCC PAGE.
DECLARE @dbid int, @objectid int
SELECT @dbid = DB_ID()
SELECT @objectid = object_id('MyTableToCheck')
DBCC SHOWCONTIG (@objectid) WITH NO_INFOMSGS, ALL_LEVELS, ALL_INDEXES, TABLERESULTS
DBCC TRACEON (3604)
DBCC prtipage(@dbid,@objectid,1,7)
DBCC TAB (@dbid,@objectid)
DBCC PAGE (@dbid, 12761, 1)
DBCC TRACEOFF (3604)
Some documenation on the undocumented DBCC commands can be found at http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp among many other places.
September 21, 2004 at 7:52 am
Using a large field for a clustered index does not have a significant impact unless the table has other indexes.
Creating a clustered index on a large field saves space initially, because the leaf nodes of the index are the table rows and the large field is not duplicated. But the clustered index key values are included in every non-clustered index, which could be a concern for a table with more than a few indexes.
Is this the only index? If so, it should be clustered IMHO; there are other forums that make the case that all tables should have a clustered index.
Are there a few indexes? Performance probably overrides storage, cluster the index that will have the most performance impact. Consider query usage (especially range queries) and uniqueness.
Are there many indexes? Storage is a bigger concern, so you have to decide what the performance/storage tradeoff is.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply