0

I want to create and use a fulltext index within a single procedure. For example:

CREATE PROC [dbo].[dbp_PCL_to_Demo_Matching_Mason] AS

--IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID('Medicaid_State_Claims'))
    CREATE FULLTEXT INDEX ON dbo.Medicaid_State_Claims(Scrubbed_Account_Number)
      KEY INDEX nci_Seq_No;

select * from dbo.Medicaid_State_Claims
  where CONTAINS(Scrubbed_Account_Number, 'blahblah');

However, this fails with the error message:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.Medicaid_State_Claims' because it is not full-text indexed.

Uncommenting the "IF NOT EXISTS" condition makes no difference. However, if the index already exists and I uncomment that line and create the stored procedure, it works. But I'd rather not create it ahead of time, I want to create it within the stored procedure. How can I create the fulltext index and use it in the same stored procedure?

1
  • Full-text indexes take time to populate, so go ahead, create it ahead of time. Otherwise you would have to put a loop in to say "wait until full-text index has finished populating" like here, but that's genuinely a bad idea for a production system.
    – wBob
    Mar 15, 2017 at 23:06

1 Answer 1

3

I don't believe you can, at least not within the same proc.

Stored procs are compiled before running, so that command is going to be checked for validity before it executes.

Full text is an entirely separate executable, and while you can certainly issue SQL commands that create a new full text index, or initiate a rebuild of one, or whatever, those commands aren't going to work like normal SQL commands (they can't be within an ATOMic transaction, they can't possibly roll back, etc.).

This means that just because the CREATE FULLTEXT INDEX statement succeeds, that doesn't guarantee the index is populated and/or useful.

It's like issuing a msdb.dbo.sp_start_job to run a SQL agent job. The statement finishes quickly and returns control, but the agent could very well still be running (for a very long time).

Maybe you can trick it by calling separate stored procs, one that creates the full text index and kicks off a full build, and then another that checks the status of that build and loops till it is complete, before calling a proc that runs the query?

It seems likely to me that you will return a value faster by doing a normal WHERE Scrubbed_Account_Number LIKE '%blahblah%', even though that will do a full table scan.

In fact, I guarantee that will be faster, since it will have to do a full table scan to build the full text index.

13
  • One word. Dynamic SQL. Ok, two-words. But you get the picture.
    – Hannah Vernon
    Mar 15, 2017 at 21:07
  • Perhaps that would work, but see my edits, I can't imagine what he's trying is a good idea.
    – BradC
    Mar 15, 2017 at 21:15
  • Agreed 100%, sounds like an A|B problem to me.
    – Hannah Vernon
    Mar 15, 2017 at 21:33
  • No, a LIKE %text% statement is far slower. I have tried and measured it. The problem is that I have two tables of data, each contain data from different sources. In the first table there is an account number, in the second table there is a string that may or may not contain an account number from the first table. I need to join them where the account number is in that larger string. Simple describe, slow to run when we're talking about millions of records. I wasn't aware that the fulltext index building was an asynchronous process, so this is helpful.
    – mason
    Mar 15, 2017 at 23:45
  • @MaxVernon It's not exactly an A|B problem. I've got an issue to solve, and I'm trying several different ways to find the best approach. I'm dealing with literally 5,000 databases, rewriting a complex matching process to join data from two separate sources that isn't guaranteed to be formatted perfectly since it's provided by customers and it would take them too much time to clean up millions of records before giving them to us. It's a complex thing requiring complex solutions, and I'm trying to keep it as simple as I can to combat that.
    – mason
    Mar 15, 2017 at 23:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.