How to Implement Full-Text Search in SQL Server

Full-text search in SQL Server allows for efficient searching of text data stored in tables. Unlike the traditional LIKE operator, full-text search enables powerful linguistic-based searches, ranking results by relevance and supporting advanced features like inflectional search and proximity queries. In this guide, we will walk through the steps to implement full-text search in SQL Server.

Before using full-text search, ensure that your SQL Server instance supports and has full-text search enabled. You can check this by running:

SELECT SERVERPROPERTY('IsFullTextInstalled') AS FullTextInstalled;

If the result is 1, full-text search is installed; otherwise, you may need to install it.

Step 2: Create a Full-Text Catalog

A full-text catalog is a container for full-text indexes. To create one, use:

CREATE FULLTEXT CATALOG MyFullTextCatalog AS DEFAULT;

Step 3: Create a Full-Text Index

A full-text index is required on the columns you want to search. First, make sure your table has a unique index:

CREATE UNIQUE INDEX UI_MyTable ON MyTable(Id);

Then, create a full-text index:

CREATE FULLTEXT INDEX ON MyTable(
    MyTextColumn LANGUAGE 1033
)
KEY INDEX UI_MyTable
ON MyFullTextCatalog;

The LANGUAGE 1033 specifies English. You can change this according to the language used in your data.

Step 4: Perform Full-Text Searches

Once the index is created, you can perform full-text searches using CONTAINS and FREETEXT.

Using CONTAINS

CONTAINS allows you to search for exact words or phrases:

SELECT * FROM MyTable
WHERE CONTAINS(MyTextColumn, '"search term"');

You can also use logical operators like AND, OR, and NEAR:

SELECT * FROM MyTable
WHERE CONTAINS(MyTextColumn, '"SQL Server" NEAR "Index"');

Using FREETEXT

FREETEXT allows for a broader, natural language search:

SELECT * FROM MyTable
WHERE FREETEXT(MyTextColumn, 'search term');
  • Populate the Full-Text Index: Full-text indexes are updated automatically, but you can manually trigger an update:

    ALTER FULLTEXT INDEX ON MyTable START FULL POPULATION;
    
  • Monitor Full-Text Indexing: Check the status of your full-text population with:

    SELECT * FROM sys.fulltext_indexes;
    
  • Remove a Full-Text Index: If needed, drop the index using:

    DROP FULLTEXT INDEX ON MyTable;
    

Conclusion

Full-text search in SQL Server is a powerful tool for handling complex text-based queries. By enabling full-text search, creating an index, and using CONTAINS or FREETEXT queries, you can significantly improve search performance and relevance in your applications. With proper indexing and management, full-text search can be a game-changer for handling large text-based datasets.

2
141

Related

Slow initial load times can drive users away from your React application. One powerful technique to improve performance is lazy loading - loading components only when they're needed.

Let's explore how to implement this in React.

The Problem with Eager Loading

By default, React bundles all your components together, forcing users to download everything upfront. This makes navigation much quicker and more streamlined once this initial download is complete.

However, depending on the size of your application, it could also create a long initial load time.

import HeavyComponent from './HeavyComponent';
import AnotherHeavyComponent from './AnotherHeavyComponent';

function App() {
  return (
    <div>
      {/* These components load even if user never sees them */}
      <HeavyComponent />
      <AnotherHeavyComponent />
    </div>
  );
}

React.lazy() to the Rescue

React.lazy() lets you defer loading components until they're actually needed:

import React, { lazy, Suspense } from 'react';

// Components are now loaded only when rendered
const HeavyComponent = lazy(() => import('./HeavyComponent'));
const AnotherHeavyComponent = lazy(() => import('./AnotherHeavyComponent'));

function App() {
  return (
    <div>
      <Suspense fallback={<div>Loading...</div>}>
        <HeavyComponent />
        <AnotherHeavyComponent />
      </Suspense>
    </div>
  );
}

Route-Based Lazy Loading

Combine with React Router for even better performance:

import React, { lazy, Suspense } from 'react';
import { BrowserRouter, Routes, Route } from 'react-router-dom';

const Home = lazy(() => import('./pages/Home'));
const Dashboard = lazy(() => import('./pages/Dashboard'));
const Settings = lazy(() => import('./pages/Settings'));

function App() {
  return (
    <BrowserRouter>
      <Suspense fallback={<div>Loading...</div>}>
        <Routes>
          <Route path="/" element={<Home />} />
          <Route path="/dashboard" element={<Dashboard />} />
          <Route path="/settings" element={<Settings />} />
        </Routes>
      </Suspense>
    </BrowserRouter>
  );
}

Implement these techniques in your React application today and watch your load times improve dramatically!

1
104

Reading a file line by line is useful when handling large files without loading everything into memory at once.

✅ Best Practice: Use File.ReadLines() which is more memory efficient.

Example

foreach (string line in File.ReadLines("file.txt"))
{
    Console.WriteLine(line);
}

Why use ReadLines()?

Reads one line at a time, reducing overall memory usage. Ideal for large files (e.g., logs, CSVs).

Alternative: Use StreamReader (More Control)

For scenarios where you need custom processing while reading the contents of the file:

using (StreamReader reader = new StreamReader("file.txt"))
{
    string? line;
    while ((line = reader.ReadLine()) != null)
    {
        Console.WriteLine(line);
    }
}

Why use StreamReader?

Lets you handle exceptions, encoding, and buffering. Supports custom processing (e.g., search for a keyword while reading).

When to Use ReadAllLines()? If you need all lines at once, use:

string[] lines = File.ReadAllLines("file.txt");

Caution: Loads the entire file into memory—avoid for large files!

4
296

XML (Extensible Markup Language) is a widely used format for storing and transporting data.

In C#, you can create XML files efficiently using the XmlWriter and XDocument classes. This guide covers both methods with practical examples.

Writing XML Using XmlWriter

XmlWriter provides a fast and memory-efficient way to generate XML files by writing elements sequentially.

Example:

using System;
using System.Xml;

class Program
{
    static void Main()
    {
        using (XmlWriter writer = XmlWriter.Create("person.xml"))
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("Person");

            writer.WriteElementString("FirstName", "John");
            writer.WriteElementString("LastName", "Doe");
            writer.WriteElementString("Age", "30");

            writer.WriteEndElement();
            writer.WriteEndDocument();
        }
        Console.WriteLine("XML file created successfully.");
    }
}

Output (person.xml):

<?xml version="1.0" encoding="utf-8"?>
<Person>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <Age>30</Age>
</Person>

Writing XML Using XDocument

The XDocument class from LINQ to XML provides a more readable and flexible way to create XML files.

Example:

using System;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = new XDocument(
            new XElement("Person",
                new XElement("FirstName", "John"),
                new XElement("LastName", "Doe"),
                new XElement("Age", "30")
            )
        );
        doc.Save("person.xml");
        Console.WriteLine("XML file created successfully.");
    }
}

This approach is ideal for working with complex XML structures and integrating LINQ queries.

When to Use Each Method

  • Use XmlWriter when performance is critical and you need to write XML sequentially.
  • Use XDocument when you need a more readable, maintainable, and flexible way to manipulate XML.

Conclusion

Writing XML files in C# is straightforward with XmlWriter and XDocument. Choose the method that best suits your needs for performance, readability, and maintainability.

2
279