To Index or Not To Index or How To Index
Hello, 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 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...

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 index
Gurus, 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 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 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.net
Hi, 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 back
I 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...