September 13, 2009 at 7:25 am
Paul White (9/13/2009)
That is so lame!Hey look, we all make mistakes - some of us don't mind admitting it though.
*shrug*
It is not lame I reply based on the answer I get have a nice day.
Kind regards,
Gift Peddie
September 13, 2009 at 4:02 pm
The problem was simply that the full-text catalog needed re-populating.
Amodi specified CHANGE_TRACKING MANUAL and so must have added the data after the initial full crawl completed. A full crawl is initiated when the index is first created, with the options set as he described. Without change tracking, the index simply needed to be updated to see the new data.
The 'solution' of changing from VARCHAR(MAX) to VARCHAR(100) 'worked' since you cannot alter a column that is enabled for full-text search (error 7614).
Amodi must therefore have dropped the full-text index to modify the column, and re-created it afterward. The initial full crawl then picked up the existing data. (Another possibility is that it was a timing issue - but you get the idea).
The large value types out of row option never affects full-text :rolleyes:
Demonstration code below:
-- Create a tiny test database with default settings
USE [master];
CREATE DATABASE [9A0CB46F-7AE6-4409-B310-0CA604378833];
GO
USE [9A0CB46F-7AE6-4409-B310-0CA604378833];
GO
IF FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1
BEGIN
EXECUTE sp_fulltext_database @action = 'enable';
END;
GO
-- Create the demo table
CREATE TABLE dbo.Pages_Master
(
PageID INTEGER NOT NULL,
Page VARCHAR(MAX) NOT NULL,
FK_PageCode INTEGER NOT NULL,
);
-- Show that large_value_types_out_of_row is OFF by default
SELECT large_value_types_out_of_row
FROM sys.tables
WHERE [object_id] = OBJECT_ID(N'dbo.Pages_Master', 'U');
-- Unique index required for full-text
CREATE UNIQUE INDEX ui_PagesMaster ON dbo.Pages_Master(PageId);
-- Create a full-text catalogue
CREATE FULLTEXT CATALOG ISFullTextCatalogDetails;
-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.Pages_Master(Page)
KEY INDEX ui_PagesMaster ON ISFullTextCatalogDetails
WITH CHANGE_TRACKING MANUAL;
-- Wait for the initial full crawl to complete
WHILE NOT EXISTS(SELECT * FROM sys.fulltext_indexes WHERE [object_id] = OBJECT_ID(N'dbo.Pages_Master', 'U') AND has_crawl_completed = 1)
BEGIN
RAISERROR('Waiting for the crawl to complete - 1', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:01';
END;
GO
-- Full-text query (returns no rows, of course)
SELECT pageId, Page, fk_pagecode
FROM dbo.Pages_Master
WHERE CONTAINS(Page, '"Procedures"');
-- Insert data row 1
IF NOT EXISTS (SELECT * FROM dbo.Pages_Master WHERE PageID = 1)
BEGIN
INSERT dbo.Pages_Master (PageID, Page, FK_PageCode)
VALUES (1, CONVERT(VARCHAR(MAX), 'This column contains the word procedures but is quite short'), 1);
END;
-- Insert data row 2
IF NOT EXISTS (SELECT * FROM dbo.Pages_Master WHERE PageID = 2)
BEGIN
INSERT dbo.Pages_Master (PageID, Page, FK_PageCode)
VALUES (2, CONVERT(VARCHAR(MAX), 'This long column also contains the word procedures ' + REPLICATE('* Padding *', 1000)), 2);
END;
-- There is no crawl to wait for, this is just for completeness
WHILE NOT EXISTS(SELECT * FROM sys.fulltext_indexes WHERE [object_id] = OBJECT_ID(N'dbo.Pages_Master', 'U') AND has_crawl_completed = 1)
BEGIN
RAISERROR('Waiting for the crawl to complete - 2', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:01';
END;
-- Full-text query (still returns no rows, of course)
SELECT pageId, Page, fk_pagecode
FROM dbo.Pages_Master
WHERE CONTAINS(Page, '"Procedures"');
-- Because CHANGE_TRACKING is set to MANUAL, and the table does not
-- contain a rowversion column, we must do a full crawl to populate
-- the full-text index
ALTER FULLTEXT INDEX ON dbo.Pages_Master START FULL POPULATION;
-- Wait for that crawl to complete
WHILE NOT EXISTS(SELECT * FROM sys.fulltext_indexes WHERE [object_id] = OBJECT_ID(N'dbo.Pages_Master', 'U') AND has_crawl_completed = 1)
BEGIN
RAISERROR('Waiting for the crawl to complete - 3', 0, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:02';
END;
-- Full-text query (returns two rows, naturally)
SELECT pageId, Page, fk_pagecode
FROM dbo.Pages_Master
WHERE CONTAINS(Page, '"Procedures"');
-- Tidy up
DROP FULLTEXT INDEX ON dbo.Pages_Master;
DROP FULLTEXT CATALOG ISFullTextCatalogDetails;
DROP TABLE dbo.Pages_Master;
GO
-- Drop the test database
USE [master];
DROP DATABASE [9A0CB46F-7AE6-4409-B310-0CA604378833];
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply