Tagging With a Many-to-Many Table: Storing and Querying Hashtags

Searching a comma-separated tag column starts simply and becomes a daily string puzzle. Store hashtags as rows in a tag table and a bridge table instead. The keys can prevent duplicates, and ordinary joins can answer searches without guessing where one tag ends.

Separate Posts, Hashtags and Assignments

One post can have several tags, and one tag can describe several posts. The bridge holds that relationship. Its composite primary key prevents a tag from being assigned twice to the same post. Foreign keys prevent orphan assignments. A reverse index supports finding posts by tag without scanning the post-first key.

CREATE TABLE dbo.Post
(
    PostID int NOT NULL PRIMARY KEY,
    Title nvarchar(200) NOT NULL
);
CREATE TABLE dbo.Tag
(
    TagID int NOT NULL PRIMARY KEY,
    TagName nvarchar(100) COLLATE Latin1_General_100_CI_AS NOT NULL UNIQUE
);
CREATE TABLE dbo.PostTag
(
    PostID int NOT NULL REFERENCES dbo.Post(PostID),
    TagID int NOT NULL REFERENCES dbo.Tag(TagID),
    TaggedAt datetime2(0) NOT NULL,
    CONSTRAINT PK_PostTag PRIMARY KEY(PostID,TagID)
);
CREATE INDEX IX_PostTag_Tag ON dbo.PostTag(TagID,PostID) INCLUDE(TaggedAt);
INSERT dbo.Post VALUES(1,N'Indexes'),(2,N'Plans'),(3,N'Backups');
INSERT dbo.Tag VALUES(10,N'sql'),(20,N'performance'),(30,N'backup');
INSERT dbo.PostTag VALUES
(1,10,'20260921'),(1,20,'20260921'),(2,10,'20260922'),
(2,20,'20260922'),(3,10,'20260923'),(3,30,'20260923');

Use a disposable database for these sample objects. I inspect both key directions before adding application queries. Which path dominates: tags for one post, or posts for one tag? That determines the index review, not the number of commas in the old column.

Normalize at the Input Boundary

Choose one canonical spelling. This example stores hashtags in lowercase without the leading hash. Trim whitespace, reject empty values, and define allowed length and characters. A case-insensitive unique constraint prevents SQL and sql from becoming separate tags here. Accent behavior is another deliberate collation choice. Decide it from the product's search rules.

I normalize when data enters the system, then store the canonical name. Do not run LOWER over the indexed stored name in every search. The bridge stores integer IDs, so renaming a tag does not require editing every post. Keep a separate display label if a product needs decorative casing.

Accept a List Without Storing One

STRING_SPLIT is useful for an input parameter. Split once, trim, lowercase, and deduplicate the requested set. It does not promise input order unless you use a supported ordinal option and explicitly order by it. Tag searches need set membership, so order is irrelevant. Validate length before inserting into the typed request table.

DECLARE @input nvarchar(max)=N'SQL, performance,sql';
DECLARE @wanted TABLE
(TagName nvarchar(100) COLLATE Latin1_General_100_CI_AS PRIMARY KEY);
IF EXISTS
(SELECT 1 FROM STRING_SPLIT(@input,N',') WHERE LEN(TRIM(value))>100)
    THROW 50000,'A tag is too long.',1;
INSERT @wanted(TagName)
SELECT DISTINCT LOWER(TRIM(value))
FROM STRING_SPLIT(@input,N',') WHERE NULLIF(TRIM(value),N'') IS NOT NULL;
SELECT * FROM @wanted;

A comma separator cannot represent a tag containing a comma without another encoding rule. Restrict the tag domain or accept a structured input instead. Never reinterpret the list as SQL text. The parser is an input adapter, and the database still stores one tag per row.

Find Posts With Any Requested Hashtag

Use EXISTS so a post matching two requested tags still appears once. The requested names resolve to tag IDs through the unique tag key. A missing requested name simply contributes no match. This is a natural join problem, not a LIKE pattern against a comma list.

DECLARE @wanted TABLE
(TagName nvarchar(100) COLLATE Latin1_General_100_CI_AS PRIMARY KEY);
INSERT @wanted VALUES(N'sql'),(N'backup');
SELECT p.PostID,p.Title FROM dbo.Post AS p
WHERE EXISTS
(
    SELECT 1 FROM dbo.PostTag AS pt
    JOIN dbo.Tag AS t ON t.TagID=pt.TagID
    JOIN @wanted AS w ON w.TagName=t.TagName
    WHERE pt.PostID=p.PostID
);

Require All Requested Hashtags

For an all-tags search, count each post's matches and compare with the number requested. The bridge and request keys prevent duplicate rows from inflating the count. Keep the requested count before resolving names, so an unknown requested tag makes the all-tags condition impossible. Decide what an empty request means rather than returning everything accidentally.

DECLARE @wanted TABLE
(TagName nvarchar(100) COLLATE Latin1_General_100_CI_AS PRIMARY KEY);
INSERT @wanted VALUES(N'sql'),(N'performance');
SELECT p.PostID,p.Title
FROM dbo.Post AS p
JOIN dbo.PostTag AS pt ON pt.PostID=p.PostID
JOIN dbo.Tag AS t ON t.TagID=pt.TagID
JOIN @wanted AS w ON w.TagName=t.TagName
GROUP BY p.PostID,p.Title
HAVING COUNT_BIG(*)=(SELECT COUNT_BIG(*) FROM @wanted);

I test any and all separately with one tag, two tags, a repeated input, and an unknown tag. Those are different search contracts. A query that silently ignores an unknown tag can make an all-tags filter broader than the reader intended.

Count Usage Without Losing Empty Tags

Count bridge rows per tag. A LEFT JOIN can include unused tags for cleanup, but count the non-null bridge key rather than COUNT(*). Apply visibility rules if private or deleted posts must not contribute. Counts should mean the same thing as the posts the user can actually open.

SELECT t.TagID,t.TagName,COUNT(pt.PostID) AS post_count
FROM dbo.Tag AS t
LEFT JOIN dbo.PostTag AS pt ON pt.TagID=t.TagID
GROUP BY t.TagID,t.TagName;

Define Trending Hashtags With a Time Window

Trending needs a definition. The bridge timestamp here measures new tag assignments, not views or popularity. Compare a fixed business week with the preceding week using half-open ranges. If you mean newly published posts, filter the post publication timestamp instead. Store timestamps consistently and convert the week boundary once.

DECLARE @week_start datetime2(0)='20260921';
SELECT t.TagName,
       SUM(CASE WHEN pt.TaggedAt>=@week_start THEN CONVERT(bigint,1) ELSE 0 END) AS this_week,
       SUM(CASE WHEN pt.TaggedAt<@week_start THEN CONVERT(bigint,1) ELSE 0 END) AS prior_week
FROM dbo.PostTag AS pt
JOIN dbo.Tag AS t ON t.TagID=pt.TagID
WHERE pt.TaggedAt>=DATEADD(day,-7,@week_start)
  AND pt.TaggedAt<DATEADD(day,7,@week_start)
GROUP BY t.TagName
ORDER BY this_week DESC,t.TagName;

Every sample assignment falls in the week of September 21, so prior_week is zero for each tag. An additional index beginning with TaggedAt can help large time-window scans. Measure it against the reverse lookup index and write cost. The word trending should describe a defined comparison. A large lifetime count is a different report wearing a fashionable hat.

Rename and Merge With Keys Intact

Two application calls can attempt to create the same canonical tag at once. Keep the unique constraint as the final protection. Use a transaction and a tested insert-or-find pattern. Handle a duplicate-key race by looking up the existing tag rather than returning a mysterious failure. Do not use an unchecked pre-insert EXISTS test as your only concurrency control.

The bridge needs a similar retry rule. Repeated assignment of the same pair should have a defined outcome. It can be an idempotent success or a clear duplicate response. The composite key protects storage either way. Preserve TaggedAt when a retry finds an existing assignment, unless the product explicitly means to reset the assignment time.

Check the all-tags query with a requested name absent from Tag. It must return no posts. Count the requested set, not only recognized names. A recognized-count shortcut can accidentally turn two requested tags into a one-tag filter. Test an empty set as well. Decide whether it means no filter or invalid input, then implement that policy at the API boundary.

A simple rename changes one Tag row. Renaming to an existing name requires a merge plan: move nonduplicate assignments to the surviving TagID, reconcile timestamps, remove old assignments, then remove the old tag. Use a transaction and retain an audit when the product needs it. Do not disable uniqueness to make the merge easier.

I keep the relationship tests beside the ingest code. They cover duplicate assignment, missing post, missing tag, and canonical spelling. The schema makes correct storage easier, while query-specific tests preserve the meaning of any, all, and weekly usage. Add a rename test that keeps the same TagID and verifies both lookup directions through the unchanged bridge rows.

Related reading on this blog: Split Comma Separated Value String in a Column Using STRING_SPLIT and Normalisation in Plain Words, and When to Stop.

A tag list is not one string to search, it is a set of relationships that keys can protect.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.