SQL Loop and Recursive CTE

Though, the concept of Common Table Expressions (CTE) is not new in SQL Server, I found something interesting when resolving a small logic in SQL Server. I wanted to extract the words in a comma separated string. As, this is a small task anybody would go extracting the words in the comma delimited text with the ordinary way of using WHILE loop by getting the delimited sub strings.

Search a particular text in SQL SERVER

If you want to search a particular text (table name, command name, part of any written code)  in any of the stored procedure, VIEW, UDF or inline written code in any stored procedure or a function you can easily use the following small query.

Since, this is just a basic need of a day to day operations of a software/database developer, I’ve thought of sharing this in my blog that any interested individual can have a look.

In the following example I’ve attempted to search the word ‘itemmaster’ in all my written SPs and functions. Actually, itemmaster is just a table name in one of my databases.

Removing the duplicate entries from a SQL TABLE

If you need to remove duplicate entries created in your sql data table, the following approach may be useful.

Usually, duplicate records can be added to your data table when those data are imported from outside sources. So, the following small script may really helpful to eliminate the duplicated entries from your data table.

Even though, the following table contains 2 records, I want to keep only 1 record for nparentid = 6618, nchildid = 6318 and nevenid = 0.

What and How to use SQL SERVER FULL TEXT Search

What is Full Text Search?

This is the search functionality which enables a fast and easy way of searching text based data in SQL Server. Even though, full text search is not new to SQL Server 2005, there are some enhancements in performance and manageability of SQL Server 2005.

Why Full Text Search?

This is used as a replacement to LIKE queries to search a pattern in a large data set since, LIKE doesn’t yield the exact results in a huge data sets.

· In a huge data set, LIKE query behaves very slowly and it may take several minutes to return the results.

· LIKE query doesn’t work on formatted binary data.

Full text search is fast, efficient, can also be run on unstructured text data and supports for different data types like formatted binary.

Components of Full Text Search

MSFTESQL – Microsoft Full Text Engine for SQL Server

MSFTEFD – Microsoft Full Text Engine Filter Daemon which contains Filter, Protocol handler and Word breaker

Architecture

MSFTESQL is a windows service that is tightly integrated with SQL Server 2005 which used as a search engine and built on top of MSSearch technology. The service provides,

· Implementing full text catalogs and indexes for database

· Querying the DB with words, phrases and words in close proximity

· Managing the full text catalogs that are stored in SQL Servers