If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN the tag table against the CTE.
Example -
WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing')
SELECT *
FROM item_tag i
INNER JOIN tags t
ON i.tag_id = t.tag_id
RIGHT JOIN ListOfTags l
ON l.Tag like t.tag
WHERE i.item_id is null
This might not be the most elegant way of doing it, but it might work for you
Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows:
CREATE VIEW dbo.MyView
WITH SCHEMABINDING
AS
SELECT
COUNT_BIG(*) AS NumberOfRows
FROM [dbo].[Products];
GO
CREATE UNIQUE CLUSTERED INDEX IX_MyView
ON dbo.MyView(NumberOfRows);
(Note: an ID
column can't be added to this view and then the clustered index created on it.)
Yes, this will add a small amount of overhead to writes to the base table; it doesn't sound like that's going to be a problem in this case.
Then it's really easy and efficient to read a transactionally-consistent row count:
SELECT NumberOfRows FROM dbo.MyView WITH(NOEXPAND);
which can either be included in the same query, or separately. Personally, I'd try to keep it separate and return the value as an OUTPUT
parameter. In either case, for an exact count, I'd strongly recommend using snapshot isolation to avoid out-of-sync issues.
This type of structure could also be created with a user table and a trigger, or a "business logic" approach... but because of the number of moving parts in either of those solutions, I really wouldn't recommend them unless there's no other choice.
If you do go this route, be sure to patch SQL Server to at least:
- Cumulative Update 7 for SQL Server 2012 SP2
- Cumulative Update 1 for SQL Server 2014 Service Pack 1
- Cumulative Update 8 for SQL Server 2014 RTM
See the Knowledge Base article:
Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014
For more details and background, see An Indexed View Bug with Scalar Aggregates by Paul White.
Best Answer
I was able to do this using multiple CTEs, similar to the following: