To Index or Not To Index or How To IndexHello,
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...
SQL Server Clustered Index creationI 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...
SQL 2000 Indexing and .NET issue
I got in a conversation with another person about MS SQL 2000 server and .NET. According to this guy for some reason they didn't work well together (database indexing issues) and there were better 3rd party indexing tools. Anyone heard anything about this?
Thanks,
Trey
Huh? That's nonsense. MS designed the data features of .NET to be tightly integrated with SQL Server, and Whidbey is only going to make things better.
Besides, what does db indexing have to do with client-side code? Does the person have any details, or are there just generalities?
DonDon Kiely, MCP, MCSDIn the ...
Does .NET map calls for ADO to ADO.NET?
Hello,We are trying to troubleshoot some DB problems from COM objects that call ADO. I heard that when ADO is loaded inside of a COM object by a .NET app, that .NET intercepts the call and re-maps those calls over to ADO.NET. This seems untrue to me, given the nature of ADO.NET being a totally separate technology.Does anyone know for sure whether this does or does not occur?The problem we are seeing is actually almost identical in symptoms to this:http://support.microsoft.com/default.aspx/kb/230101"FIX: Data Queries Sporadically Return Empty Recordsets" Literally, this is what...
Clustered index and identical non-clustered indexGurus,
I have come across a table with the following indexes on it
CREATE TABLE banana
(
b_id int,
...
)
It has the following indexes
unique clustered index banana_idx_1 ON banana ( b_id )
and
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 ?
Hi Zia,
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...
indexers in .net
Hi Is Indexer a .net 1.1 feature or .net 2.0 feature RegardsKaran RegardsKaran GuptaFor .net,Javascript and Sql server articles and interview question visithttp://guptakaran21.googlepages.com/main
Neither. It is a C# language feature available from v1.0 (.NET 1.0) onwards.Mark replies as answers if they helped you solve the problem.
Indexer is feature of .Net.
An indexer enables you to use an index on an object to obtain values stored within the object. In essence this enables you to treat an object like an array.
An indexer is also similar to a pro...
Clustered Index against non clustered indexes in OLTPI 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
system.
Thanks in advance,
Evelyn
This is a multi-part message in MIME format.
--------------057F1209A1406F494E323FFD
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Evelyn wrote:
> ...
Basic Index question :- nonclusted indexes which are a subset of a clustered index
Can anyone suggest why I would have the following set of indices upon a
table:
index1 clustered, unique field1,field,field3,fiel4
index2 nonclustered field2,field3, field4
index3 nonclustered field,field2,field4
The table is one into which a lot of inserts are done, so I would have
thought that index2 & 3 would be unncessary since they're
(1) already in the clustered index and
(2) any inserts would triple the amount of index updates that are
required...
Are these assumptions correct ?
Thanks in advance,
Steve
Hi,
if you have a...
updateable view ado.net sql server
Have any body use updateable view ado.net sql server .
That is for example you have bind a grid with update able view[which comprises of multiple tables]
Then you update data via updateable view.
Kamran ShahidSr. Software Engineer(MCP,MCAD.net,MCSD.net,MCTS,MCPD.net[web])Netprosys Inc.www.netprosys.comRemember to click "Mark as Answer" on the post that helps U
Any Idea Guys ?? Kamran ShahidSr. Software Engineer(MCP,MCAD.net,MCSD.net,MCTS,MCPD.net[web])Netprosys Inc.www.netprosys.comRemember to click "Mark as Answer" on the post that helps U...
Indexs on Views (Do the call to a view uses index on a table)
Hi I have created a view on my server A which is caling a table from Server B. The table that its calling has a bunch of index on it. Now I want to know does a call to this view, considers the indexes on my table on server B. Its taking a lot of time to respond so I am thinking its not considering the indexes but pls shed some light on it. I am not very good with Views and Indexes. Have something like this on server A Create view history as Select * from ServerB.siq.dbo.History Please help Thanks -
SarahSarah Cameron Benjamin
Have you checked the Execution Plan for your qu...
Switching a table from a clustered index to a non-clustered index.Hi all,
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?
Thanks,
Al
Drop & recreate the index ( Primary key )
alter table table_name
drop constraint constraint_name
go
alter table table_name
add constraint constraint_name PRIMARY KEY NONCLUSTERED ( col )
go
...
ADO in VB6 vs ADO.NET in VB.NET
I am writing a database interface and I would like to know whether a
VB6 interface using ADO or a VB.NET interface using ADO.NET would be
faster in terms of just performing database operations. Is there an
explanation for this, and what is the magnitude of the difference?
Thanks in advance!...
ado.net or/and datawindow.netHi,
Can datawindow.net work with Sybase's ado.net or are they not compatible
with each other.
We have two separate groups of developers, one group develops web apps using
ASE as the back end for the data, so they use a combination of Visual Studio
..NET and Sybase's ado.net to do their work. We have another group that
develops client/server apps using Powerbuilder, but since 80% of their work
is in writting reports using Powerbuilder, I was wondering if they could use
a combination of datawindow.net and ado.net to put make these reports
available on the web through ...
When forced index in not there, ado.net does not exceute and rolls backI am using v 1.1.411.0
When I try to execute a store proc that is forcing an index
ie (index nb) and the index is not there, the ado.net
returns and error msg and rolls back.
However, executing the same store proc in SQL advantage, the
transaction finishes and generates a warning.
How can I get the same behaviour as SQL advantage?
Thanks
Is your code catching AseException and rolling back a transaction ?
You could try setting connection string property
"IgnoreErrorsIfRSPending=true"
Please let us know if this works.
However, I do feel that a missing index is a seri...