Question: We have a table with three columns: id, fieldName, fieldValue. This table has many records. We want to quickly access a list of the distinct fieldNames.  

When we create a view with a clustered index, we get this, but we have another problem: we have many processes that delete from the table by the id column. These have deadlocks, when multiple deletes run concurrently, since they are trying to update the index.  

If we create a view without an index, there are no deadlocks, but the view becomes very slow to use.
Is there any way to create a view (or otherwise get the distinct fieldNames) that will work quickly, but also not lock on deletes?

Adding data to the question, to answer some of the suggestions provided:  

We very rarely add new fieldNames, and deleting existing fieldNames is even rarer. Almost all new records use the existing fieldNames.  

There are few distinct fieldNames (about 30), but hundreds of millions of records in the table.  

We do have an index on fieldName in the table, but getting a list of the distinct fieldNames is still very slow if the view is not indexed.

Answer: 
You don't need the view. Paul White blogged about how to find distinct values quickly in [Performance Tuning the Whole Query Plan](http://sqlperformance.com/2014/10/t-sql-queries/performance-tuning-whole-plan).

He uses a recursive CTE to seek the next distinct value. Basically doing one seek per iteration/value jumping through the index. This will be faster for few distinct values but somewhere as the number of distinct values, compared to the number of rows in the table, increases there is a tipping point where a scan will be faster.

In your case it would look something like this.

Setup:

```
create table dbo.YourTable
(
  id int identity primary key,
  fieldName varchar(20) not null,
  fieldValue varchar(20) null
);

go

create index IX_YourTable_fieldName on dbo.YourTable(fieldName);

go

insert into dbo.YourTable(fieldName) values
('F1'),
('F1'),
('F1'),
('F2'),
('F2'),
('F2'),
('F3');

```

Query:

```
with C as
(
  select top (1) T.fieldName
  from dbo.YourTable as T
  order by T.fieldName
  union all
  select R.fieldName
  from (
       select T.fieldName,
              row_number() over (order by T.fieldName) as rn
       from dbo.YourTable as T
         inner join C
           on C.fieldName < T.fieldName
       ) as R
  where R.rn = 1
)
select C.fieldName
from C
option (maxrecursion 0);

```

Queryplan:

[![enter image description here](https://i.stack.imgur.com/CSKL7.png)](https://i.stack.imgur.com/CSKL7.png)
Why not creating an extra index on the fieldNames column - I see no need for a view.