I have create this little sample Table Person.

The Id is a primary key and is identity = True

Now when I try to create a FullText Catalog, I'm unable to search for the Id.

If I follow this wizard, I'll end up with a Catalog, where its only possible to search for a persons name. I would really like to know, how I can make it possible to do a fulltext search for bo开发者_StackOverflow社区th the Id and Name.

edit

SELECT *
FROM [TestDB].[dbo].[Person]
WHERE FREETEXT (*, 'anders' );

SELECT *
FROM [TestDB].[dbo].[Person]
WHERE FREETEXT (*, '1' );

I would like them to return the same result, the first returns id = 1 name = Anders, while the second query don't return anything.

edit 2

Looks like the problem is in using int, but is it not possible to trick FullText to support it?

edit 3

Created a view where I convert the int to a nvarchar. CONVERT(nvarchar(50), Id) AS PersonId this did make it possible for me to select that column, when creating the Full Text Catalog, but It still won't let me find it searching for the Id.

---

From reading your question, I am not sure that you understand the purpose of a full-text index. A full-text index is intended to search TEXT (one or more columns) on a table. And not just as a replacement for:

SELECT *
FROM table
WHERE col1 LIKE 'Bob''s Pizzaria%'
OR col2 LIKE 'Bob''s Pizzaria%'
OR col3 LIKE 'Bob''s Pizzaria%'

It also allows you to search for variations of "Bob's Pizzaria" like "Bobs Pizzeria" (in case someone misspells Pizzeria or forgot to put in the ' or over-zealous anti-SQL-injection code stripped the ') or "Robert's Pizza" or "Bob's Pizzeria" or "Bob's Pizza", etc. It also allows you to search "in the middle" of a text column (char, varchar, nchar, nvarchar, etc.) without the dreaded "%Bob%Pizza%" that eliminates any chance of using a traditional index.

Enough with the lecture, however. To answer your specific question, I would create a separate column (not a "computed column") "IdText varchar(10)" and then an AFTER INSERT trigger something like this:

UPDATE t
SET IdText = CAST(Id AS varchar(10))
FROM table AS t
INNER JOIN inserted i ON i.Id = t.Id

If you don't know what "inserted" is, see this MSDN article or search Stack Overflow for "trigger inserted". Then you can include the IdText column in your full-text index.

Again, from your example I am not sure that a full-text index is what you should use here but then again your actual situation might be something different and you just created this example for the question. Also, full-text indexes are relatively "expensive" so make sure you do a cost-benefit analysis. Here is a Stack Overflow question about full-text index usage.

---

Why not just do a query like this.

SELECT *
FROM [TestDB].[dbo].[Person]
WHERE FREETEXT (*, '1' )
OR ID = 1

You can leave off the "OR ID = 1" part if by first checking if the search term is a number.