To Index or Not To Index or How To Index
I was wondering if someone knows a better way to explain how different indexes
work. Such as, is it better to have an index with int type or char or varchar
etc? Is it better to have composite indexes or indexes with individual columns?
How does optimizer handle different indexes and what can we do help it? Indexes
are very efficient most of the time but they can be quite detrimental to
server's performance, if abused. Perhaps you know of a good article about
indexing that you could share with us. Or, perhaps Mr. Rob Verschoor would like
to issue an article in his very usefu...To Index or not to Index (that is the question...)
I'd like to clear up an assumption I have.
If I have a Primary Key Constraint on a Table I assume that I will not need
to create an Index on the field(s) of the constraint as the action of
creating the primary key constraint produces what is the equivalent of an
Index (be the constraint clustered or not).
Is this the case?
Also I'm looking to Pivot some data, are there any SQL tricks etc. I should
be aware of to be able to do this (I've searched the Newsgroups for 'Pivot'
but don't understand any of the answers posted - some SQL examples would be
gr...clustered index vs nonclustered index
I have a 16 million row table in which we do lots of inserts throughout the
day and a purge at night (no updates). The nightly delete of 300K - 400K
rows takes between 6 and 10 hours. I was thinking of replacing the
clustered index with a nonclustered index since the table uses row level
locking. Generally speaking, is there a performance difference between
clustered and nonclustered indexes on a row level locked table? This is ASE
Thanks. Carl Britton.
Comment. Apparently you mean that the table has DataRow (or possibly
In which case the clustered...A question about clustered indexes forcing rebuild of non-clustered indexes.
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if...Clustered Index against non clustered indexes in OLTP
I have a question, I read in an article, that OLTP systems runs better with
few indexes, however my question is, is better to have clustered indexes in
OLTP than non clustered??, or how can I determine in which tables I have to
use clustered indexes or non clustered indexes ??.
I'm new in performance, I have a lot of problems of performance with our OLT
Thanks in advance,
This is a multi-part message in MIME format.
Content-Type: text/plain; charset=us-ascii
> ...Clustered index and identical non-clustered index
I have come across a table with the following indexes on it
CREATE TABLE banana
It has the following indexes
unique clustered index banana_idx_1 ON banana ( b_id )
unique non-clustered index banana_idx_2 ON banana (b_id )
i.e. two unique indexes one clustered, the other non-clustered ** on the
same field **
Does anyone know the benefits (if any) of doing this ?
it seems suspicious. Anyhow it could make sense if the table is APL.
Clustered index sorts the data physically by the index, so if the tab...Switching a table from a clustered index to a non-clustered index.
I've been using CAST to set my table properties but now I must learn the
proper syntax in order to adjust the table settings from iSQL. Currently
table A's index is set to clustered but I want to convert it to a
non-clustered index. Does anyone know the command line that I should use?
Drop & recreate the index ( Primary key )
alter table table_name
drop constraint constraint_name
alter table table_name
add constraint constraint_name PRIMARY KEY NONCLUSTERED ( col )
...index server not indexing
Ok, so I want to add a catalog to the index server. I can add the new catalog, I then add a directory to this catalog. I restart the index server, and merge the index. If I click the index server, in mmc, it shows 0 documents for everything. Everything meaning documents to index, total docs, docs defered for indexing and so on.
I've tried this on vista as well as on win2003 and the same result is obtained, no documents are indexed. In win2003 I can index document, but ONLY the ones in the default catalog, if I paste some documents there they get indexed.
So, what have I don...Questions about Indexing and Using an Indexing POA
Although I have only about 50 users, at least 15 of them have in excess
of 100,000 messages in their accounts and the POA (version 7.0.2) is
regularly slowing to a crawl. (I just know that plans for revolution
are fomenting!) I have embarked on a campaign to reduce these accounts
by archiving everything off to get mail accounts down to 3000 or fewer
pieces. I have achieved user buy-in, but have worked on only a few
users so far.
In another closely related thread, it was suggested to me that the PO
speed issues relate to broken indexes. And I suspect that given so many
messages...Clustered Index Scan vs. Index Seek
I found something odd in the execution plan. I have a table
that looks like this (p1, p2, d1, d2, d3), where p1 and p2
are the primary key and d1, d2, and d3 are data. The primary
key is clustered.
When I check the execution plan of the below query:
where p1=111 and p2='2008-1-1'
It shows that it will do the query by performing an Index
Scan, and the performance, especially when the query is
executed the first time, is not great. For a table that has
4 million records, this sometimes can take 4-5 seconds.
In MS SQL server, the same query wil...Clustered Index on Indexed View from ADO.NET
I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.If anyone has experienced this please let me know.Cheers Niall
Views are...Would ASE implement BitMap index or BitWise index?
Dose ASE has any plan to implement BitMap index?
Any what's the advantage of BitWise index. I just know it's a patent
technology of SYBASE while BitMap index is a all-known technology based on
Thanks a lot.
This is a multi-part message in MIME format.
Content-Type: text/plain; charset=us-ascii; format=flowed
> Dose ASE has any plan to implement BitMap index?
> Any what's the advantage of ...SQL Server Clustered Index creation
I have a table in which I have defined a Clustered Index that is different
from the PK. SQL Server (7 and 2000) creates a clustered index for the
Primary Key by default.
So, in the script generated by PowerDesigner, the clustered key generation
fails with the error "Cannot create more than one clustered index on table
'AH_DESIGNDEFINITION'. Drop the existing clustered index
'PK_AH_DESIGNDEFINITION' before creating another."
It would be nice if PowerDesigner would specify that the Primary Key should
be created non clustered so that the user defined cluster...Crawler Index and FIle Index
I have setup the Apache web services as well as Quickfinder for my Netware
The Crawler works fine, finds all the hits I am looking for and opens the
file on my data volume.
However, when I setup a File System Index it does not open anything.
I have double checked my paths and syntex.
The thing that I have noticed is that when I search for a file with the
Crawler index and highlight the file, the path at the bottom of my browser
is like the following example.
http://myserver/comp/docs/myfile. showing the server name not an IP
address, which is fine and works!