As the concept of search continues to grow in scope, we find ourselves relying more on search functionality rather than categorization. Whether it’s searching through our inbox or navigating a data-driven website, the ability to quickly find what we’re looking for is becoming increasingly important.

In this article, we will explore a simple way to implement basic keyword search functionality using SQL Server. By leveraging SQL Server’s capabilities, we can create a system that allows us to associate keywords with entries and easily retrieve relevant information.

Setting up the Tables

To begin, we need to set up the necessary tables. We’ll start with a table called LogEntries, which will store our entries along with their associated keywords. The table will have columns for the entry itself, the associated tags, and the date it was entered.

CREATE TABLE LogEntries
(
  EntryId int IDENTITY(1,1) NOT NULL,
  Entry varchar(8000) NOT NULL,
  Tags varchar(200) NOT NULL,
  DateEntered datetime NOT NULL DEFAULT (GETDATE()),
  CONSTRAINT PK_LogEntries PRIMARY KEY CLUSTERED (EntryId ASC)
)

Next, we’ll create a table called Keywords, which will store the individual keywords. This table will have a single column for the keyword itself.

CREATE TABLE Keywords
(
  KeywordId int IDENTITY(1,1) NOT NULL,
  Keyword varchar(50) NOT NULL,
  CONSTRAINT PK_Keywords PRIMARY KEY CLUSTERED (KeywordId ASC)
)
CREATE UNIQUE NONCLUSTERED INDEX IX_Keywords ON Keywords (Keyword ASC)

Finally, we’ll create a table called LogEntry_Keyword, which will be used to calculate the number of occurrences of a keyword in a given entry.

CREATE TABLE LogEntry_Keyword
(
  EntryId int NOT NULL,
  KeywordId int NOT NULL,
  Hits int NOT NULL,
  CONSTRAINT PK_LogEntry_Keyword PRIMARY KEY CLUSTERED (EntryId ASC, KeywordId ASC)
)

Implementing the Trigger

Now that we have our tables set up, we can implement a trigger that will automatically update the LogEntry_Keyword table whenever a new entry is inserted into the LogEntries table.

CREATE TRIGGER trgInsertLogEntry
ON LogEntries
FOR INSERT
AS
BEGIN
  DECLARE @tags AS varchar(200)
  DECLARE @keyword AS varchar(50)
  DECLARE @keywordId AS int
  DECLARE @found AS int
  DECLARE @entryId AS int

  -- Temp table for current keyword data for the newly inserted LogEntry
  CREATE TABLE #kw
  (
    kwid int PRIMARY KEY,
    hits int
  )

  -- Data from the newly inserted LogEntry
  SET @entryId = @@identity
  SET @tags = (SELECT tags FROM INSERTED)
  SET @tags = LTRIM(RTRIM(@tags)) + ' xyz'

  -- While there are still keywords in the newly inserted LogEntry's tag
  WHILE (CHARINDEX(' ', @tags) > 0)
  BEGIN
    -- Get the next keyword from the tags string
    SET @keyword = SUBSTRING(@tags, 0, CHARINDEX(' ', @tags))
    -- Get the KeywordId from the Keywords table
    SELECT @keywordid = (SELECT KeywordId FROM Keywords WHERE Keyword = @keyword)
    -- Insert the keyword if necessary
    IF (@keywordId IS NULL)
    BEGIN
      INSERT INTO Keywords VALUES (@keyword)
      SET @keywordId = @@identity
    END
    -- See if the keyword id is in the temp table yet
    SELECT @found = (SELECT COUNT(*) FROM #kw WHERE kwid = @keywordId)
    -- If not found insert it
    IF (@found = 0)
      INSERT INTO #kw VALUES (@keywordId, 1)
    -- If found update the hit count
    IF (@found != 0)
      UPDATE #kw SET hits = hits + 1 WHERE kwid = @keywordId
    -- Update the tags by lopping off the keyword just processed
    SET @tags = substring(@tags, charindex(' ', @tags) + 1, len(@tags) - charindex(' ', @tags))
  END

  -- Insert the keywords and their occurrences into the LogEntry_Keyword table
  INSERT INTO logentry_keyword
  SELECT @entryid, kwid, hits
  FROM #kw
END

Adding Data and Retrieving Information

Now that our system is set up, we can easily add data and retrieve information using stored procedures.

To add an entry, we can use the sp_logentry stored procedure. This procedure takes two arguments: the entry itself and the associated tags.

CREATE PROC sp_logentry(@entry AS varchar(8000), @tags AS varchar(200)) AS
INSERT INTO LogEntries (Entry, Tags)
VALUES (@entry, @tags)

For example, we can add an entry like this:

EXEC sp_logentry 'Remember that you increased the budget for the Razzmatazz account by $10K.', 'increased budget account'

To retrieve information based on keywords, we can use the sp_search stored procedure. This procedure takes a single argument: the keywords to search for.

CREATE PROC sp_search(@keywords AS varchar(50)) AS
DECLARE @kws as varchar(50)
DECLARE @kw as varchar(50)
DECLARE @kwid as int

-- Temp table for current keyword data
CREATE TABLE #kw
(
  kwid int PRIMARY KEY
)

-- Add a fake keyword that won't get inserted
SET @kws = LTRIM(RTRIM(@keywords)) + ' xyz'

-- While there are still keywords
WHILE (CHARINDEX(' ', @kws) > 0)
BEGIN
  SET @kw = SUBSTRING(@kws, 0, CHARINDEX(' ', @kws))
  SELECT @kwid = (SELECT keywordid FROM keywords WHERE keyword = @kw)
  IF (@kwid IS NOT NULL)
    INSERT INTO #kw VALUES (@kwid)
  SET @kws = SUBSTRING(@kw, CHARINDEX(' ', @kws) + 1, LEN(@kws) - CHARINDEX(' ', @kws) - 1)
END

SELECT le.EntryId, Entry, Tags, DateEntered, SUM(Hits) h
FROM LogEntries le
JOIN LogEntry_Keyword lek ON lek.EntryId = le.EntryId
JOIN #kw tkw ON tkw.kwid = lek.keywordid
GROUP BY le.EntryId, Entry, Tags, DateEntered
ORDER BY h DESC

For example, we can search for entries related to the keywords “budget” and “account” like this:

EXEC sp_search 'budget account'

Conclusion

Implementing basic keyword search functionality in SQL Server can be a useful tool for organizing and retrieving information. By associating keywords with entries and leveraging SQL Server’s capabilities, we can easily search for relevant information based on specific keywords.

While this implementation may be more of an exercise than something you’ll put into practice, the basic idea can be expanded upon and customized to fit your specific needs. You can hook it up to a basic UI for a popup notepad or add additional validation to ensure the tags string is properly formatted.

With a little creativity and customization, you can create a powerful search functionality that allows you to quickly find the information you need.