If you use t-sql(smaller development language you can use in a mssql database) in mssql 2005 and 2008 editions, you can use like, in a kind of contains method. It may be "less painful" to insert the fulltext index if you have not installed SQLServer with the advanced settings, wich is required to run fulltext search.

By that I mean that the installation time for mssql server 2008 is longer than the 2005 version.

T-SQL is a good thing if you know what you're looking for, for example, you can use like %__% or just a select statement, finding what you need.

But t-sql and use of like does not give equally good results every time, for example:

 
    select * from table Where column like '%main task%' 
    

The above select sentence will not return a row if it says 'very long main task' in the row.

You will need to use a sentence such as:

    select * from table Where column like 
    '%task%main%' or column like '%maint%task%' 
    or ... etc.. ... etc. 
    

which you usally don't implement in real life otherwise I would like to see it!

You could perhaps use RegEx(Regular Expression), but since there is not implemented anything in mssql, there are other solutions. People have tried to solve the problem, such here where regex is implemented in MySQL, which you can just use.

If you think of MySql way it is implemented with the MATCH() AGAINST(), which you can just use.

The sentence is not complex, as you can see, but there is a problem. This problem is indexing. If you want to add non-clustered index in the sentence must look like this:

 
    select * from Table Where Column like 'main task'
    

I.e. here you will get all the rows with the name 'main task'.

The next example will give a full table scan until you find the number you are looking for, and it is a "big" table, let's say several gigabytes, it will take longer time than the example below.

 
    declare @søgTekst nvarchar(100) set @søgTekst ='tobias' 
    SELECT*from Kunde k WHERE k.Firmanavn Like'%'+ @søgTekst +'%'
    OR k.Adresse Like'%'+ @søgTekst +'%'OR k.bynavn Like'%'+ @søgTekst +'%'
    

SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 419 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

As you can see the execution plan, performed clustered index scan, which is the primary key for the customer and is automatically clustered if the key is primary. In this case, it takes less than 1 sec. to get 2 rows out where the company name is 'tobias'. Since the clustered index is sorted by index, and it was among the first rows were inserted, it takes therefore the short time. It may be slower to write to a clustered index. Compared to a clustered index, where you can only have one, you can have many nonclustered index, although each new nonclustered index will increase the time it takes to insert new values. You can then say that it is faster to insert data and slower to get them.

To get back to the search, if you want to search a number of different sentences, and you want it to happen as quick as possible, you might not get the best results, using like. In short, t-sql does not give developers the best tools / methods to search for.

If you wish to use the fulltext search, create a directory to store the fulltext index.

 
 CREATEFULLTEXTCATALOG WorksFTCatalog
 

After that, we create fulltext index. This can be done as follows:

 CREATEFULLTEXTINDEXON Kunde
(
CVR,
[Firmanavn],
[Adresse]
)
KEYINDEX PK_Kunde ON WorksFTCatalog
WITHCHANGE_TRACKINGAUTO

Then you can use Contains for example:

 SELECT*FROM Kunde WHERECONTAINS(Text,N't')
eller FREETEXT
SELECT*FROM Kunde WHEREFREETEXT(Text,N'main task');

In some cases it will give better performance if you delete the index in a column. Example if you often insert rows, it is best to completely avoid putting the index on.