>I need to add a field to a SQL Server table to store free-text notes. The note can be a brief one, just a few characters, or could be as long as two pages of text. I would like to be able to search the Notes field by a string/content using SQL Select. Will this be possible with TEXT type field?

Yes, you can run queries with LIKE operator agains text fields but it is not going to be optimized.

>The reason I am asking is that in a White Paper on conversion to SQL Server it says that TEXT type is searchable only if SQL full-text search ability is enabled. Is this capability difficult to enable? Do most companies using SQL Server have this capability enabled (that is, will I have problems to convince a customer in have to set this capability?).

With full-text search enabled and Notes fields included in full-text indexes your query may be optimized.

>And if I have to use VARCHAR, what length would you recommend to specify? This is fairly small table, I don't think it will be more than 10000 records so I am not concerned about the storage. But I want to learn to do it right, so that when I deal with a large table, I will apply the same rules.

The size of varchar column is limited to 8000 characters. The lenghth of a record in SQL cannot exceed 8060 bytes. So you can set the length of the varchar column to (8060 - sum of length of the rest of the fields in the record) but <= 8000. The size of varchar column doesn't affect the space used so you can set it to the max possible value. You've to warry about varchar column size only when you want to have index on it.

I prefer to use varchar fields whenever I can because they are easier to deal with. In SQL Server 2005 MS introduced varchar fields of unlimited size to avoid the problems with text fields.