Query Notification / SQLDependency fails on table w/ computed column

I have a table with a computed column defined on it. I can't get SQLDependency to work with that table unless I remove the computed column definition. The select statement I am using does not include the computed column, it just exists on the table.

When I execute the SQLCommand, the SQLDependency immediately fires the OnChange event with a SQLNotificationEventArgs.Info of 8 {Query}. I can't find any documentation in SQL Books Online that the base table must not have a computed column, even if the select does not include that column. Is this a bug or by design? Thanks.

Here is a sample create table statement:

create table dbo.test

(id int,datechanged datetime

,mycomputedcolumn as (1+1)

)

go

insert into dbo.test (id,datechanged)

values (46,getdate())

And here is a sample VB program that duplicates my problem (the sub cb will be called immediately with a e.Info=8)

Module Module1

Sub Main()

Dim connString As String = "server=paulg\yukon;database=bbinfinity;integrated security=sspi"

SqlClient.SqlDependency.Start(connString)

Dim conn As New SqlClient.SqlConnection(connString)

conn.Open()

Dim cmd As SqlClient.SqlCommand = conn.CreateCommand

cmd.CommandText = "select id,datechanged from dbo.test;"

cmd.Connection = conn

Dim dep As New SqlClient.SqlDependency(cmd, Nothing, 500000)

AddHandler dep.OnChange, AddressOf cb

Dim o As Object = cmd.ExecuteScalar

Console.ReadLine()

End Sub

Sub cb(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)

Debug.WriteLine(e.ToString)

End Sub

End Module

This is a behavior of the underlying notification engine in the server. I'm moving the thread to a server forum.|||

Paul,

Using a query notification on a table with a computed field is NOT supported by Microsoft. I was told it would be added to the BOL in the list of scenarios not supported, but only after I reported it as a bug.

Chris

|||

Thanks for the reply. It helps to know that it is officially not supported vs. something I was doing wrong.

Seems like a bizarre limitation to me. I always thought computed columns were just some extra metadata with very little cost that were convenient when you had common expressions you knew you might need frequently. Under that impression, I've used them all over the place where I know I'll often need an expression (such as a CASE..WHEN statement) to avoid having to repeat the expression in every TSQL statement that needs that logic. I know I can use UDFs but my experience shows that in-line expressions can be much faster than UDFs, so in simple cases where inline expressions work I try to use them. Computed columns seems like a great way to have the re-usability of UDFs with the performance of in-line expressions. But now I'm scared that computed columns are more "special" than one would think.

Obviously there are many workarounds so I'll get by. But this still smells to me like something that must have just been overlooked rather than a well reasoned design choice. Thanks again for the reply.

|||

No problem. It was pretty frustrating for me as well. It seemed that some people I talked to still using the Beta versions of Visual Studio could get this to work, but I never could. It took over a month for MS to tell me that it was not supported after I reported it as a bug. Luckily we didn't have too many computed fields in the tables we wanted to use for query notifications, but it was still a major annoyance.