When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users is query plan reuse with 1-part object names. Create two users:

-- logins already created
CREATE USER bob FOR LOGIN bob
CREATE USER mary for LOGIN mary
GRANT SELECT ON authors TO bob, mary

The query plan for the following query (executed by bob or mary)

USE pubs
GO
SELECT * FROM authors -- note the 1-part object name

would not normally be reused for both bob and mary. In SQL Server 2005, it can be reused if bob and mary have the same default schema. BTW, this is adhoc SQL, rules differ in procedural objects like stored procs. To see the reason for the query reuse, you can obtain the plan handle (reference this blog entry), but leave out querying the plan itself because you don't need it here) and pass it in to the sys.dm_exec_plan_attributes dynamic management function. This function shows the attributes of the plan and includes a second column, is_cache_key. If the cache_keys match, the plan is reused.

One of the rows produced by sys.dm_exec_plan_attributes is named user_id. Interestingly, for the query plan for bob or mary against SELECT * FROM authors, the user_id is 1. DBO. Hmmm... User_id is a misleading name for this column. It's not the user_id of the user who executed the query (and caused the plan to be created) but that user's DEFAULT_SCHEMA's owner's user_id. Looking at the CREATE USER DDL statements above, because I didn't specify a DEFAULT_SCHEMA, both users' DEFAULT_SCHEMA is DBO. So, for adhoc SQL, all other things being equal, the plan will be resued for two users as long as they have the same DEFAULT_SCHEMA. The rules for query plan reuse follow the rules for object resolution, which makes sense, once you think about it.

Of course, now that you know this, ALWAYS use 2-part names. User_id with 2-part names' plans has a special value of -2 which ensures the plan is resued regardless of DEFAULT_SCHEMA of the user who executes it.

Quite a while ago, someone (I forget who) was soliciting opinions on whether or not LINQ and ADO.NET vNext EDM should be able to run "in process", in other words should be useable in SQLCLR procedures. Here's one perspective, and a question.

LINQ and Entity SQL are QUERY languages. Although they might be able to encapsulate resultset output from stored procedures, much of their value is in the query language itself. NOT having them be useable in SQLCLR would encourage folks that want to use these languages to move AWAY from stored procedures and put their data access query code in client or middle-tier programs, rather than in the database. I don't think DBAs and database developers really want to encourage that.

On the other hand, some of the value of stored procedures is being able to restrict table permissions and find errors at compile time. SQLCLR procedures don't have either of those attributes built-in, although you can restrict table permissions if you use EXECUTE AS OWNER. And coding with LINQ/Entity SQL can allow compile-time type metadata checking. Other benefits of stored procedures, such as allowing the code to be shared among database apps, and consolidation of SQL code in database objects, do apply as well to SQLCLR.

And its a given that, with either of these languages (and with SQLCLR in procedures in general) you'll be giving away procedure execution speed for (*arguably* better, meaning you'all can argue about it) code maintainability. Unless you only use LINQ/Entity SQL to process T-SQL stored procedure resultsets.

What do YOU think?

Haven't written in a while. I've been back from Hong Kong for a few weeks (had a fantastic time, thanks folks), and, a few days later, contracted the (American) flu that's been going around here. Just getting over it. Also, I've been busy working on some whitepapers that will come out (relatively) soon. I'll announce it here when they are available.

I'm headed off to Barcelona in a few weeks to TechEd and ITForum. I'll be speaking about SQL Server 2005, on some topics that folks ask about in class often (now that they've heard of the features). But there usually isn't time to go over these in as much detail as I'd like. These talks are an effort to make the time. At TechEd, I'll be speaking on:

Plan Guides and Plan Forcing
Query Notifications / Cache Sync
Error handling on server and client
Implementing a Service Oriented Database Architecture application
MARS, transactions, and SQLCLR (for a preview of who uses MARS, read back a few blog entires)

and a chalk talk on maintaining your system with SMO. Ought to be fun.

For the IT Professional folks at ITForum, they'll be:

XML for the DBA (because the list of SQL Server DBA items in XML format is about a mile long now)
Troubleshooting Service Broker (even DBMail uses broker)
Security Account and Key Management

Hope to see you there. Stop by and say hi.