Can I use an indexed view in SQL Server to fetch only non duplicate records

Question

We have a huge table with millions of records. We want to fetch only first record based on some key in that table. When I tried using self-join it's resulting in temp db space issue. From the performance point of view, I was thinking of using an indexed view. But I can't use left join or CTE in an indexed view.

Is there any way available to fetch only non duplicate records using indexed views?

Answer 1

The scenario is that an indexed view contains distinct values of the "key" that are joined to the first row on the source table.

Prepare example data

SET NOCOUNT ON;
CREATE TABLE dbo.t (
  product_name nvarchar(20), 
  vendor_name nvarchar(20)
)
GO
INSERT INTO dbo.t (product_name, vendor_name) VALUES 
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('Oracle', 'Oracle'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('DB2', 'IBM'),
('DB2', 'IBM');

Create indexed view

IF OBJECT_ID ('dbo.v_t', 'view') IS NOT NULL  
    DROP VIEW dbo.v_t
GO
CREATE VIEW dbo.v_t
WITH SCHEMABINDING
AS
    SELECT COUNT_BIG(*) prod_count, vendor_name
    FROM dbo.t
    GROUP BY vendor_name
GO
CREATE UNIQUE CLUSTERED INDEX IX1_v_t
    ON dbo.v_t (vendor_name);  
GO  

The query

SELECT t.* 
FROM dbo.v_t v
CROSS APPLY(SELECT TOP 1 * 
            FROM dbo.t t 
            WHERE v.vendor_name = t.vendor_name 
            ORDER BY t.product_name) t

Result

product_name         vendor_name
-------------------- --------------------
DB2                  IBM
SQL Server           Microsoft
Oracle               Oracle