InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 16:11:59 GMT20http://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxI was just curious about the columns that you might have in there! Could you provide us some detail about that (It would be great for me since I am new to this field and exploring things :cool:)Tue, 09 Feb 2010 11:21:29 GMTrvphxhttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxI would stick with the Design Guidelines for using clustered/non clustered indexes that appears on MSDN. It should give a pretty good idea as to where and what kind of index you might want to use. :-)Tue, 09 Feb 2010 11:14:51 GMTrvphxhttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspx[quote][b]Jaya Chitra (9/23/2008)[/b][hr]Dear All,Thanks for you replies.I have the table which contains some properties of the document in my knowledge repository.The user can search by specifying any values to the properties.Based on the values in the properties the where criteria is framed and then I am sending this to the stored procedure.In the stored procedure I am framing the dynamic query with the where clause with the input where clause.The table has minimum more than 200000 documents.So I don't know how to make the index.I had a discussion and some were saying that I can have index for all the fields.Can any one tell me how to resolve this?Thanks a lot.[/quote]Study the clustered,nonclustered and covering indexes in BOL....Making a clustered index on the columns in where clause will improve performanceWed, 24 Sep 2008 04:50:28 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspx[quote][b]Jaya Chitra (9/23/2008)[/b][hr]Dear All,Thanks for you replies.I have the table which contains some properties of the document in my knowledge repository.The user can search by specifying any values to the properties.Based on the values in the properties the where criteria is framed and then I am sending this to the stored procedure.In the stored procedure I am framing the dynamic query with the where clause with the input where clause.The table has minimum more than 200000 documents.So I don't know how to make the index.I had a discussion and some were saying that I can have index for all the fields.Can any one tell me how to resolve this?Thanks a lot.[/quote]Study the clustered,nonclustered and covering indexes in BOL....Making a clustered index on the columns in where clause will improve performanceWed, 24 Sep 2008 04:50:20 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxCan you post the proc, the table and the index defs? Are there certain columns that will be more frequently searched than others?Wed, 24 Sep 2008 01:08:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxDear All,Thanks for you replies.I have the table which contains some properties of the document in my knowledge repository.The user can search by specifying any values to the properties.Based on the values in the properties the where criteria is framed and then I am sending this to the stored procedure.In the stored procedure I am framing the dynamic query with the where clause with the input where clause.The table has minimum more than 200000 documents.So I don't know how to make the index.I had a discussion and some were saying that I can have index for all the fields.Can any one tell me how to resolve this?Thanks a lot.Tue, 23 Sep 2008 21:46:24 GMTJaya Chitrahttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxIt depends. What other columns are being returned by the query? What is the selectivity of the column(s) in the index?As a rule you should not place a separate single column index on every column in a table. It creates more overhead than it is worth. You need to analyze the queries of the database and then index appropriately. A single composite index will likely be better (more selective) than 2 single column indexes and will reduce IO's. FOr example a person search on last name and first name will be faster if the index is a composite index including the last name and first name columns than 2 indexes on last name and first name.Tue, 23 Sep 2008 08:54:36 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxYou should not index ALL the fields. That will slow down your updates/inserts/deletes.You want to include indexes on those fields which are mostly used for queries. If you have two items in the WHERE clause, it depends. You can include both fields in the index, you can have separate indexes that might be used.Best bet is to get the execution plans for those queries often run and look to see if you are getting index seeks.Tue, 23 Sep 2008 08:50:43 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspxIf your join condition matches with the index fields the query will also performs faster.It is not the index that imporve the performance. You should look out at defragmentation aslo. If the fragmentation is at the highest level the query also improves.Tue, 23 Sep 2008 07:43:32 GMTvyashttp://www.sqlservercentral.com/Forums/Topic574303-360-1.aspx[b]Dear All,[/b]Can any one tell me the relation between the index fields and the query criteria sequence.I have created the index for all the fields in the database individually.If I am querying the database with by specifying two fields in the criteria field.What will happen, will the performance is increased or not?Kindly give me your suggestion.[b]Thanks a lot in advance.[/b] :)Tue, 23 Sep 2008 07:18:08 GMTJaya Chitra