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.
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.
Last Comment
Anthony Perkins
If your table has a unique index you can create a Full-Text index on it.
ASKER
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
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
>>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.
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.
ASKER
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.
>>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.
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
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.
"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.
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.
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
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
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
I am afraid I have no idea.
ASKER
I was thinking there was some miscommunication as the problem is due to having partition on the table.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
