Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry.

Q. Can you use a plan guide on an encrypted procedure? I want to put a hint on encrypted vendor-supplied code.
A. No, per books online you cannot create an OBJECT plan guide on any object that is itself encrypted or references an encrypted object. You get error message 10512 if you try.

Q. Can you create a plan guide on the SQL statement (rather than an OBJECT plan guide) on a SQL statement that I know exists as part of an encrypted procedure and have the guide be used?
A. You can create a SQL-type plan guide on a statement in a procedure, but it won't be used in any case; you need to create an OBJECT-type plan guide for it to be usable inside a procedure. You can confirm its "non-use" by looking at the showplan output from an un-encrypted procedure with a SQL-type plan guide; you can't see an XML showplan for statements in an encrypted procedure.

Q. Can I have more than one plan guide? For example to ENABLE/DISABLE different guides at different times of the day? Can I have two guides enabled at the same time?
A. You can't create more than one plan guide on a single statement/module-or-object combination, whether they are DISABLEd or not. You get error message 10502 if you try. You'd have to DROP the first plan guide before you can create the second one.

Here's another blog posting to answer a question from over a month ago.

With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"

Short answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so...

create login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
go

use adventureworks
go

sp_grantdbaccess 'bob'
go

select * from sys.schemas  -- bob schema is there
go

sp_revokedbaccess 'bob'
go

select * from sys.schemas  -- bob schema is gone
go

The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:

-- creates schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))

-- drops schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))

Sure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:

EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)

Hmmm....

It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago.

What are the limitations/requirements for using a SQLCLR function in an indexed view (aka materialized view)?

Some of the requirements came from the books online, but can be verified with a simple sample, but there were a few surprises.

1. The VIEW must be created WITH SCHEMABINDING. This is a "normal" requirement of an indexed view, but usually when creating VIEWs WITH SCHEMABINDING that reference UDFs, the UDF must be defined WITH SCHEMABINDING as well. SQLCLR UDFs can't be explicitly be defined WITH SCHEMABINDING, but they can be used in views defined as WITH SCHEMABINDING.
2. The VIEW can't use a SQLCLR derived column as part of the index key unless the derived column is declared as PERSISTED in the base table.
3. The SQLCLR function must be declared Deterministic and Precise, and do no data access using SqlCommand, etc. DataAccess = None, SystemDataAccess = None is the default in SQLCLR functions anyway. And no external access is allowed.
4. An indexed view cannot contain a SQLCLR user-defined aggregate (UDA) function.

To demonstrate, I wrote two functions AddOne in SQLCLR, TSQLAddOne in TSQL. You can use AddOne in an indexed view. You can use AddOne as a key column in an indexed view if its defined as persisted in the base table. Note that the TSQLAddOne can be used as an index only if its defined WITH SCHEMABINDING.

This is fairly similar to the restrictions for TSQL functions and indexed views, except that you can access data in TSQL function and use it as the key, only if the TSQL function is defined with schemabinding. SQLCLR functions can't be defined with schemabinding, because there is no way to figure out which tables, etc, it's accessing. So because you can declare a TSQL function with schemabinding, you can probably make it part of the key if you access data. But, as when using a SQLCLR function in a VIEW WITH SCHEMABINDING, this shouldn't be an issue when your SQLCLR function does no data access. Hmmm...

Check out "table3 and view3". It uses SQLCLR function in a persisted computed column, and then as the KEY in an indexed view derived from the table.