When working with SQL Server databases, determining whether a table contains any records is a common task. Whether you're validating data before performing operations or implementing business logic, knowing how to check for data existence efficiently is essential. This article demonstrates several approaches to verify if data exists in a table.

Using COUNT Function

The COUNT aggregate function returns the total number of rows matching specified criteria. By checking if the count is greater than zero, you can determine if data exists.

DECLARE @RowCount INT;
SELECT @RowCount = COUNT(1) FROM Products;

IF @RowCount > 0
    PRINT 'Table contains records.';
ELSE
    PRINT 'Table is empty.';

This approach is straightforward but requires scanning the entire table to get an exact count, which can be slow for large tables.

Using EXISTS Subquery

The EXISTS operator checks whether a subquery returns any rows. It stops processing as soon as it finds the first matching record, making it efficient for existence checks.

IF EXISTS (SELECT * FROM Products WHERE CategoryID = 5)
    PRINT 'Matching records found.';
ELSE
    PRINT 'No matching records.';

You can combine EXISTS with specific conditions to check for particular data patterns.

IF EXISTS (
    SELECT 1 
    FROM Products 
    WHERE UnitPrice > 100 
      AND UnitsInStock > 0
)
BEGIN
    SELECT ProductName, UnitPrice 
    FROM Products 
    WHERE UnitPrice > 100 
      AND UnitsInStock > 0;
END
ELSE
BEGIN
    PRINT 'No available products in this price range.';
END

Using TOP with EXISTS

Combining TOP with EXISTS provides another efficient method. The query terminates after finding the first row.

IF EXISTS (SELECT TOP 1 ProductID FROM Products)
    PRINT 'Data is present.';
ELSE
    PRINT 'No data available.';

This technique is useful when you need to verify existence before performing join operations.

DECLARE @CategoryId INT = 3;

IF EXISTS (SELECT TOP 1 p.ProductID 
           FROM Products p 
           INNER JOIN Categories c ON p.CategoryID = c.CategoryID
           WHERE c.CategoryID = @CategoryId)
BEGIN
    SELECT p.ProductID, p.ProductName, c.CategoryName
    FROM Products p
    INNER JOIN Categories c ON p.CategoryID = c.CategoryID
    WHERE c.CategoryID = @CategoryId;
END

Performance Considerations

For large datasets, EXISTS and TOP combinations typically outperform COUNT because they can short-circuit after finding the first row. Use COUNT when you actually need the exact number of rows for business logic.

-- Efficient existence check
IF EXISTS (SELECT 1 FROM Orders WHERE OrderDate = '2024-01-15')
    PRINT 'Orders exist for this date.';

-- When count is needed for calculations
DECLARE @PendingCount INT;
SELECT @PendingCount = COUNT(*) FROM Orders WHERE Status = 'Pending';

IF @PendingCount > 0
    PRINT 'There are ' + CAST(@PendingCount AS VARCHAR(10)) + ' pending orders.';

Practical Example

Here's a complete procedure demonstrating different check methods:

CREATE PROCEDURE sp_CheckInventory
    @ProductCategory NVARCHAR(50)
AS
BEGIN
    DECLARE @RecordCount INT;
    
    -- Method 1: COUNT with condition
    SELECT @RecordCount = COUNT(*) 
    FROM Products p
    INNER JOIN Categories cat ON p.CategoryID = cat.CategoryID
    WHERE cat.CategoryName = @ProductCategory;
    
    IF @RecordCount > 0
    BEGIN
        PRINT 'Found ' + CAST(@RecordCount AS VARCHAR) + ' products in ' + @ProductCategory;
        
        SELECT p.ProductName, p.UnitsInStock, p.UnitPrice
        FROM Products p
        INNER JOIN Categories cat ON p.CategoryID = cat.CategoryID
        WHERE cat.CategoryName = @ProductCategory
        ORDER BY p.UnitsInStock;
    END
    ELSE
    BEGIN
        PRINT 'No products found in this category.';
        RETURN;
    END
    
    -- Method 2: EXISTS for additional validation
    IF EXISTS (
        SELECT 1 FROM Products p
        INNER JOIN Categories cat ON p.CategoryID = cat.CategoryID
        WHERE cat.CategoryName = @ProductCategory 
          AND p.UnitsInStock < 10
    )
    BEGIN
        PRINT 'Warning: Some items have low stock.';
    END
END