Michael J. Swart

Well, the title is a bit misleading. It’s not possible really. At least SQL Server doesn’t support any syntax that will allow a constraint to be created directly on a view. But there is a little bit of a workaround and it’s far from perfect. It involves making a UNIQUE constraint do the work of a CHECK constraint.

Using the AdventureWorks database for illustration. Lets say you wanted to enforce the ridiculous business rule that no-one with the middle initial ‘Q’ is allowed to be a Production Technician. We’ll call this business rule “No Q Technicians”.

Here’s a query that will return any offending violations of the rule:

SELECT * 
FROM HumanResources.Employee e
JOIN Person.Person p
    ON e.BusinessEntityID = p.BusinessEntityID
WHERE MiddleName = 'Q'
    AND JobTitle LIKE 'Production Technician'

What we can do is turn that into a indexed view and use the unique constraint to prevent rows in the table:

CREATE VIEW Person.v_NoQTechnicians WITH schemabinding
AS
SELECT 1 one
FROM HumanResources.Employee e
JOIN Person.Person p
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.BusinessEntityID = 1 -- we want exactly one placeholder row in this view always
   OR (MiddleName = 'Q'
   AND JobTitle LIKE 'Production Technician%')
GO

CREATE UNIQUE CLUSTERED INDEX ix_NoQTechnicians ON Person.v_NoQTechnicians(one)
GO

UPDATE Person.Person
SET MiddleName = 'Q'
WHERE BusinessEntityID = 42
GO

The last update statement will fail with an error message that looks like:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'Person.v_NoQTechnicians' with unique index 'ix_NoQTechnicians'.
The statement has been terminated.

There’s a lot that is wrong with the solution. For one thing, I think it’s a big hack. It felt like it while I was writing it. I also don’t like having to accommodate the one placeholder row in the view. Another thing is that indexed views are very restrictive with respect to the variety of select commands that are allowed.

Any improvements are welcome. Leave a note in the comments!

I’ve noticed that there are several subjects where the act of studying a thing affects the thing being studied. I can think of an example in Physics: Measuring the speed of a particle affects what you can know about the location of that particle and vice versa. Or as another example, psychologists have to take care when setting up experiments so as not to influence subjects and inadvertently skew results.

Well, the same thing applies when monitoring the performance or health of SQL Server. The new Performance Data Collector in SQL 2008 helps (not surprisingly) collect performance data. But the data collector can consume about 5% of the machine’s CPU and it has to be taken into account when analyzing the results.

Another case is when executing scripts on SQL Server that view or analyze cached plans. Here’s such a script:

SELECT TOP 10 st.TEXT, qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_worker_time DESC

Queries like these can sometimes show up in the results themselves! To filter out these rows from the results, you can add a where clause that looks like this:

SELECT TOP 10 st.TEXT, qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.TEXT NOT LIKE '%sys.dm_%'
ORDER BY total_worker_time DESC

If you want to be more selective about what queries to filter out, and you have control over the queries, then include a GUID in every query that you want to eliminate. It can be any arbitrary GUID, but it should be the same GUID. This GUID can be used as a tag for SQL Server to use in filtering criteria:

SELECT TOP 10 st.TEXT, qs.*
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.TEXT NOT LIKE '%078D048A-DDD1-4092-9259-3CC175D644F9%'
ORDER BY total_worker_time DESC

The above query will never return itself. It’s a bit like searching Google for:

“database whisperer” -“smudgy didjeridoo”

You’ll find a number of web pages, (including many from this blog), but you’ll never find this particular page that you’re reading. That’s because there’s a recursive thing going on based on the use-mention distinction of a phrase.