SQL Server – Creating and Using Fulltext Index in Stored Procedures

full-text-searchindexsql serversql server 2014stored-procedures

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?

Best Answer

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.

Related Solutions

Sql-server – Stored Procedure as Create Table proxy

Yes, it should be working how you think. Below is an example proving this:

-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go

use TestDB;
go

-- create the test user
create user TestUser1
for login TestLogin1;
go

-- create the proc to create a table
create procedure dbo.CreateTableProc
as

    create table dbo.SomeTestTable(id int);

go

-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go


-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- unsuccessful, permission denied

Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
CREATE TABLE permission denied in database 'TestDB'.

-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
    with execute as owner
as

    create table dbo.SomeTestTable(id int);

go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- successful, table created

Command(s) completed successfully.

In this example, TestUser1 does not have the permissions to create the table. We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1. But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).

Sql-server – Troubleshooting vanishing fulltext index in SQL Server 2012

Catalog and index are separate. What do you see in sys.fulltext_indexes? Did you confirm data there after the index was created?

You could check for recent Object:Deleted events in the default trace:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
-- AND StartTime > 'yyyymmdd' -- rough date/time you created the index
ORDER BY StartTime DESC;

Now, your default trace may be very busy with a lot of relevant events, in which case the drop could have rolled off the current set of trace files, but this is a rare scenario.

Another case where it might not show up in the default trace is if the database was restored to an earlier point in time - you may find some evidence of this in SQL Server's error log (though this only keeps so much history too - if you've restarted SQL Server six times, for example, any information from before that is gone).

Best Answer

Related Solutions

Sql-server – Stored Procedure as Create Table proxy

Sql-server – Troubleshooting vanishing fulltext index in SQL Server 2012

Related Question