Avatar of jamesBond008
jamesBond008 asked on

SQL Server 2005 -- Full text index on partiotioned table

We have table with around 90 million rows and is partitioned on a column for example say A. There already exists a unique key. Now I would like to create a full text index on another column for example let us say B. Column B is not unique and hence unique key index can't be created here. I want to create full text index on Column B. My question is as follows
1) Is it possible to create full text index on column which is not unique.
2) If it is possible than what is my option to allow application to do advance search on this column.  

We have SQL Server 2005 64 bit enterprise edition.
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft Legacy OS

Avatar of undefined
Last Comment
jamesBond008

8/22/2022 - Mon
Anthony Perkins

If your table has a unique index you can create a Full-Text index on it.
ASKER
jamesBond008

Hi acperkins, I already have unique key on column A and I want to create full-text index on column B. It would have been very easy to create full text index on column A but I need full text on column B. In other words
1) Table has already unique key on a column and that column is not going to used as full text index.
2) Column where I want to use full text index is not unique.

Thanks
Anthony Perkins

>>I already have unique key on column A and I want to create full-text index on column B. <<
No problem that is doable.
>>It would have been very easy to create full text index on column A but I need full text on column B.<<
Than go ahead and create a Full-Text Search on B.

1)  Again, no problem.  That is pretty typical.
2) It does not have to be.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
jamesBond008

Thanks for response. As far as I know column needs to be unique inorder to be created full text index.  I am curious have you created full text index on non unique column? Thanks in advance.
Anthony Perkins

>>As far as I know column needs to be unique inorder to be created full text index.<<
No.
>>I am curious have you created full text index on non unique column?<<
Yes.  All the time.

As I mentioned before, you do need a primary key on the table, but it does not have to be the one that is included in the Full-Text search and in my experience is very seldom included in the Full-Text Search.
ASKER
jamesBond008

Here is example as mentioned in msdn http://msdn.microsoft.com/en-us/library/ms187317.aspx and if you read there is
"KEY INDEX index_name
Is the name of the unique key index on table_name. The KEY INDEX must be a unique, single-key, non-nullable column. Select the smallest unique key index for the full-text unique key. For the best performance, we recommend an integer data type for the full-text key"

I am surprised how were you able to create full text index on a column which did not had unique key.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

Again, and for the last time:

1. You must have a unique index on the table.  Got that?  Good.
2. You can create a Full-Text Search index on a column which does not have a unique key.  Further, 9 times out of 10 the column(s) you select will not have a unique key.  Is that clear now?  

I honestly do not know how else to describe it to you, other than to get remote access and do it for you.
ASKER
jamesBond008

I am sorry for confusion and being unclear with my question. Did I mentioned my table is partitioned on composite unique key? Is there any issue creating full text index on partitioned table? Can I get steps to create full text index on partitoned table. this is my table structure

column_key & column_id composite primary key and partition is based on range on this key
column search_term : this is non unique and I want to create full text index on this.

I will greatly appreciate if I can get the steps outlined how to create the full text index on this partitioned table? thanks
Anthony Perkins

I am afraid I have no idea.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
jamesBond008

I was thinking there was some miscommunication as the problem is due to having partition on the table.  
ASKER CERTIFIED SOLUTION
jamesBond008

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question