|
Home > Archive > MS SQL Server > March 2006 > Index vs. Full-Text Index
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Index vs. Full-Text Index
|
|
| Tejas Parikh 2006-03-05, 8:23 pm |
| Hey guys. I've a few columns which require some kind of indexing. Please
suggest which and why should i chose that method. Thank you
The columns are all varchar(600)
IPAddress
Serial Number
Mac Address
Organizational Unit as in Active Directory( 2 or 3 words)
Country
Zip
State
I think Regular indexes should suffice me in this but please let me know.
Thank you.
| |
| Tibor Karaszi 2006-03-05, 8:23 pm |
| First of all you need to clean up the datatypes. Why are you using varchar(600) for all columns? Is
a serial number really variable length? And if so, can it be 600 characters?
After that you need how you will query that data. In general, regular indexes will be the best for
you. But if you have several words in the string and will search for all occurrences of what can be
the second word in that string, consider a full text index. And realize that you use different
predicates when you have full text indexes (instead of LIKE or =, you use FREETEXT or CONTAINS - or
even FREETEXTTABLE or CONTAINSTABLE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Tejas Parikh" < TejasParikh@discussi
ons.microsoft.com> wrote in message
news:1A0FFCBD-AA9C-4223-9C30- 4B2656A5EC96@microso
ft.com...
> Hey guys. I've a few columns which require some kind of indexing. Please
> suggest which and why should i chose that method. Thank you
>
> The columns are all varchar(600)
> IPAddress
> Serial Number
> Mac Address
> Organizational Unit as in Active Directory( 2 or 3 words)
> Country
> Zip
> State
>
>
> I think Regular indexes should suffice me in this but please let me know.
> Thank you.
| |
| Tejas Parikh 2006-03-05, 8:23 pm |
| Hey. Thank you for the prompt answer. I was just giving an example. None are
actually varchar(600). they are all variable varchar lengths. The query is
done from an application where people will be searching on keywords but all
the columns I use will have only ONE word. Only one column has 3 words or so.
And people can do searches on any of those words. So, will it be safe to
create regular indexes on all columns and a full text index on the column
that has 3 or more words to be searched on?
| |
| Tibor Karaszi 2006-03-05, 8:24 pm |
| > So, will it be safe to
> create regular indexes on all columns and a full text index on the column
> that has 3 or more words to be searched on?
As usual, it depends. As I mentioned earlier, searching through a full text index is done
differently compared to regular indexes. So you have to consider this slightly increased complexity
compared to the performance gain you might see.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Tejas Parikh" < TejasParikh@discussi
ons.microsoft.com> wrote in message
news:4E6FBEC2-C0C7-472A-BF6C- 9920138E060F@microso
ft.com...
> Hey. Thank you for the prompt answer. I was just giving an example. None are
> actually varchar(600). they are all variable varchar lengths. The query is
> done from an application where people will be searching on keywords but all
> the columns I use will have only ONE word. Only one column has 3 words or so.
> And people can do searches on any of those words. So, will it be safe to
> create regular indexes on all columns and a full text index on the column
> that has 3 or more words to be searched on?
|
|
|
|
|