October 1, 2009 at 7:14 am
I have a table with two varchar data type fields, It will be very frequently the searchs of these two fileds. Is recommendable to have an index for these fields?
Thanks
Viky.
October 1, 2009 at 7:34 am
Viky (10/1/2009)
I have a table with two varchar data type fields, It will be very frequently the searchs of these two fileds. Is recommendable to have an index for these fields?Thanks
Viky.
Could be, depends on a lot of things. but generally you should index on columns that are used to filter data within queries. but it does depend a lot on the data and how queries are written that access this table.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 1, 2009 at 7:59 am
I have a doubt because the fields are varchar type,
Does not generate problems if I have index of fields that are varchar type?
Viky
October 1, 2009 at 8:22 am
There are no big problems when creating an index on varchar fields. I hope it is not varchar(MAX) though.
-Roy
October 1, 2009 at 8:58 am
Viky (10/1/2009)
I have a doubt because the fields are varchar type, Does not generate problems if I have index of fields that are varchar type?
No. No problem at all.
How big is the target table?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 1, 2009 at 9:08 am
The table will have got 15000 rows intially and it can grow
October 1, 2009 at 9:35 am
Good.
Take the ten top queries you expect to hit that table, look at the predicate a.k.a. where clause and build indexes based on what you see.
As a rule of thumbs you should try to have indexes serving all major conditions specified in query predicates.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 1, 2009 at 9:45 am
Just one small question, Does that table have a clustered Index at all?
-Roy
October 1, 2009 at 9:59 am
It has got a primary key that is clustered, the primary key is int type.
October 1, 2009 at 10:02 am
Please keep in mind that just because it is primary key, it has to be clustered. You can always alter the table and change the cluster index to the column that you think is the best.
-Roy
October 1, 2009 at 1:09 pm
Roy Ernest (10/1/2009)
Please keep in mind that just because it is primary key, it has to be clustered.
mmhh... not entirely true.
Statement holds true only if 1) No clustered index already exists at the time PK is created OR, 2) PK is created with default options.
Either way, not sure how clustered/non-clustered issue applies to poster's question.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 1, 2009 at 1:36 pm
PaulB-TheOneAndOnly (10/1/2009)
Roy Ernest (10/1/2009)
Please keep in mind that just because it is primary key, it has to be clustered.mmhh... not entirely true.
Statement holds true only if 1) No clustered index already exists at the time PK is created OR, 2) PK is created with default options.
Either way, not sure how clustered/non-clustered issue applies to poster's question.
I really do not understand your point here. The only statement I made was, Just because you have primary key, it does not need to be clustered. You can have a primary key that is non clustered.
Also the OP is asking if it is OK to create an Index on Varchar. According to the OPs scenario, the index that needs to be created can be clustered. So just giving another option. Is that wrong?
-Roy
October 1, 2009 at 1:48 pm
Roy Ernest (10/1/2009)
PaulB-TheOneAndOnly (10/1/2009)
Roy Ernest (10/1/2009)
Please keep in mind that just because it is primary key, it has to be clustered.mmhh... not entirely true.
Statement holds true only if 1) No clustered index already exists at the time PK is created OR, 2) PK is created with default options.
Either way, not sure how clustered/non-clustered issue applies to poster's question.
I really do not understand your point here. The only statement I made was, Just because you have primary key, it does not need to be clustered. You can have a primary key that is non clustered.
Also the OP is asking if it is OK to create an Index on Varchar. According to the OPs scenario, the index that needs to be created can be clustered. So just giving another option. Is that wrong?
Don't you remember posting this? "because it is primary key, it has to be clustered" 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 1, 2009 at 1:52 pm
hahahaha.... I stand corrected.... That is what happens when you dont read it again before you post...:-D I meant that it does not have to be clustered.... 😛
Please note Paul is right here..... Thanks Paul
-Roy
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply