One last SQL syntax post for the evening...

We've all heard about SQL Server 2008 row constructors. They allow syntax like this to work:

CREATE TABLE name_table (name varchar(20), age int);
go
INSERT INTO name_table VALUES ('Bob', 54), ('Mary', 30), ('Sam', 15), ('Buddy', 9);
go

But how about using them as a table source:

SELECT n.name, n.age, tab.species
FROM name_table n
JOIN
(
  VALUES ('Bob', 'person'), ('Mary', 'person'), ('Sam', 'cat'), ('Buddy', 'cat')
) tab (name, species)
ON n.name = tab.name;

You specify a table alias and name the columns, and its just another (synthesized on the fly) table.

To round out the new plan guide-related features in SQL Server 2008, there is a new way to express a table hint that increases the plan guide's reach.

The sp_create_plan_guide procedure requires a hint as the last parameter. This can be in a form OPTION (hint), just an XML query plan (in SQL Server 2008 "OPTION (USE PLAN)" isn't required), or NULL. Specifying NULL can be used to "subtract" a hint from an existing query where the hint is hardcoded in source code you don't have the ability to change.

This requirement limited plan guides to hints that could be used in an OPTION clause, and table hints didn't qualify because they are inline. In SQL Server 2008 this limitation is removed. You can specify a table hint by using the syntax OPTION (TABLE HINT (table_alias, hint)). This makes table hints usable in plan guides. You can even specify multiple hints on a per-table basis.

OPTION (TABLE HINT (table_alias1, hint1), (table_alias2, hint2)...)

BOL indicates in a few places that this syntax exists specifically to make table hints accessible to plan guides. Cool.

Another use for an analogous syntax that's not in SQL Server 2008 would be to be able to specific per-table JOIN hints the same way. You can currently specify only one JOIN hint in the option clause. An analogous way to specify multiple, per-join JOIN hints in an OPTION clause would be nice. But, if you're controlling the plan that closely, perhaps its best to resort to plan forcing.

BTW, manditory disclaimer: Hints are (usually) evil and should be used only as a last resort. Using a USE PLAN hint seems, at least to me, similar to writing custom navigation code for each query in IMS/DB, as I did in the '80s. You're throwing the query algebrizer/optimizer away...or at least pinning its hands.

SQL Server 2005 introduced the OUTPUT clause in DML statements. With the OUTPUT clause, you can output column values to a table variable, a table, or return these values to the user. You can even use multiple OUTPUT clauses to push the values into both a table variable and a table from the same statement.

In SQL Server 2008 there is an additional option. You can use your OUTPUT column values directly in an INSERT-SELECT statement. Here's what it would look like, using MERGE with an OUTPUT clause (and an example from one of the early webcasts):

CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
CREATE TABLE AuditChanges (Action varchar(6), Stock VARCHAR(6), Qty INT);
GO

INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
GO

INSERT INTO AuditChanges
SELECT * FROM
(
MERGE Stock S
  USING Trades T
  ON S.Stock = T.Stock
  WHEN MATCHED AND (Qty + T.Delta = 0) THEN
    DELETE
  WHEN MATCHED THEN
    UPDATE SET Qty += T.Delta
  WHEN NOT MATCHED THEN
    INSERT VALUES(Stock, T.Delta)
  OUTPUT $action, T.Stock, inserted.Qty
) tab (action, stock, qty);
GO

Notice how the OUTPUT clause requires a table alias (in this case "tab") and needs to name the columns returned from OUTPUT.

One of the nifty new SQL Server 2005 features was called "plan forcing". You could acheive plan forcing by using the USE PLAN query hint but this made such a hideous query that the better way to do this is to use a plan_guide. A plan_guide is a named database object (like table or view) that associates a query hint with a particular query.

In SQL Server 2008 there have been some enhancements made for plan_guides and plan forcing. The biggest change is that you can create a plan from the plan cache directly, rather than using the rather long set of steps that you needed in SQL Server 2005. You only need a few lines of boilerplate code, setting up and invoking sp_create_plan_guide_from_cache, and the plan guide is automatically created. See SQL Server 2008 Nov 2007 for details and copious examples. The concept of creating a plan guide from a query plan already in cache is known as "plan freezing".

You can also validate that a plan guide will still be used (after dropping an index, for example) by using the new sys.fn_validate_plan_guide function. In addition, its easy to see if a Plan Guide is being used or not with the profiler trace events "Plan_guide_successful" and "Plan_guide_unsuccessful", located under the "Performance" category. These can also be monitored over time with two new perfmon counters "Guided Plan Executions/sec" and, what has to be the most humorous perfmon counter "Misguided Plan Executions/sec".

One other plan guide enhancement that doesn't seem to be mentioned in BOL is that you can use plan guides for INSERT, UPDATE, DELETE, or even MERGE statements in SQL Server 2008. This feature works in the SQL Server 2008 Nov CTP (CTP5).

As always the standard disclaimer is "query hints are (usually) evil" but if you're desperate enough to need plan forcing or plan freezing, its good to know that its there.

One reason why I've seen people use plan freezing is as a hedge against plan instability that can result from what's known as "parameter sniffing". Parameter sniffing is usually a good thing; the query processor sniffs the parameter values the first time you execute a parameterized stored procedure and uses the statistics based on those parameters accordingly. However, if your "first time execution" was based on uncommon parameter values, you're now stuck with a suboptimal plan. Plan freezing can be use to freeze a plan that's most optimal and "override" parameter sniffing. You just have to catch such a plan in the cache.

The drawback is, of course, that you are stuck with the same plan even if the statistics change. I'm sometimes accused of liking plan guides and plan forcing too much, but perhaps it's because I'd oftentimes get a call when parameter sniffing (or a service pack: yes, it does happen every now and again) changed to a query plan that is suboptimal. For those trouble calls (ie, "my query just starting running 4 times slower today and the plan is different from yesterday") plan_guides/forcing let you change the plan without changing the underlying code. When the fix arrives, you just disable the plan guide to let the optimizer go on its merry way.

After getting insert/update/delete stored procedures going with a simple standalone table (no relationships), I started thinking about how I'd do this in a many-many relationship. Let's say that I have students and classes, with a many-many relationship represented by a studentclass table with only a student_id and class_id. EDM maps this to a many-many relationship, leaving the "join table" out of the conceptual model (no studentclass entity). That's what I'd want. But...how to map insert/update/delete on the studentclass table, because there is no entity for it?

Turns out that this is OK, because mapping insert/update/delete to sprocs is not exposed on the entity (CSD layer), but on the mapping (MSD) layer. And, sure enough, there is an ModificationFunctionMapping element exposed under AssociationSetMapping, as well as under EntityTypeMapping. The designer doesn't support this yet, but the raw XML schema does.

In addition, the Entity Framework "Help Overview" file does mention "Mapping Association Sets to stored procedures". It doesn't help matters by showing an example of Categories and Products in the Northwind sample database, where the Categories and Products tables don't have a many-many relationship or a join table. Do I *need* stored procedures on a zero-or-one-many relationship as well as on the "base" tables that comprise the relationship? I can insert and delete a relationship between an existing product and an existing category, so I might need these in addition to the stored procs mapping the base tables, in case these aren't already enforced by key constraints in the database and model? Doesn't seem like I'd need them at all, unless I specifically left the appropriate key constraints out of the database, but wanted them in the mapping and conceptual model.

But the Help Overview specifically mentions "mapped to a join table in a relationship", so many-many sounds like to the relationship type (AssociationSet cardinality) I'd need stored procedure mappings for.

So there IS a way to do it with many-many.

I've been working with the new ADO.NET Entity Framework designer for a few weeks now, and I've got to like it. If you're used to the LINQ To SQL Object Relational designer, in which all the action takes place in the "diagram pane", it takes some getting used to. The ADO.NET Entity Framework designer actually uses three panes, the Entity Designer pane, the Model Browser pane (which docs itself in the Solution Explorer group at the right in my layout), and the Mapping Details pane (which docs itself in the Error List group at the bottom in my layout) pane.

In general, you can work with Entities (the CSDL) in the diagram pane or Model Browser. You can work with the store (database, the SSDL) in Model Browser, and the mapping (MSL) in Mapping Detalis. The Properties Window displays the property of the currently selected item, as with most things in Visual Studio.

Knowing which pane to work in makes it easier to figure out how to perform operations like mapping INSERT/UPDATE/DELETE to stored procedures. In the download announcement (which lists the features by pane) one the the new features was "View/Edit mappings using stored procedures (insert/update/delete)". The Entity Model Tools document seemed to indicate "not yet supported". I remember doing this "by hand" in earlier betas and it was fairly painful to hand-edit the XML file(s). I used the Model Browser to create a Function Import for my stored procedures and was ready to go...well...

Mapping the stored procedures to the insert/update/delete procedures in the Entity Designer (my LINQ to SQL experience got in the way here) and trying the Model Browser because the Mapping Details didn't seem to know about it, I finally located the little buttons on the extreme left side of the Mapping Details pane. The bottom button, "Map Entity to Functions" brought up an alternate Mapping Details pane, where I could map the stored procedures. Whew.

Maybe I'm "GUI impaired" but after I thought about it a while, where else would this function fit in the designer? Like I said, the EF/EDM is rich enough to make the designer quite complex. But after adding an ordinary "read" stored procedure (ie, not for insert/update/delete) to the model, and the XML hand-editing that this entails, I'm HAPPY to have to designer. Keep adding those new features...

Since its appearance in SQL Serve 2008 CTP5, folks have been wondering about the reason for SQL Server's new Change Tracking feature, and how it differs from Change Data Capture (CDC). The CTP5 BOL has a nice writeup about how it differs from CDC, so I'd encourage you to start there. But about "why it's there...".

The main reason for change tracking, as far as I can see, is to be used with ADO.NET Sync Services. ADO.NET Sync Services is part of the Microsoft Sync Framework and is a way to do (I'm trying to summarize here) "client directed programmable replication". For database folks ADO.NET Sync Services can be thought of as providing synchronization between SQL Server Compact Edition and any relational datastore, but the model is extensible. ADO.NET Sync Services 1.0 shipped with SQL Server Compact Edition 3.5 in Visual Studio 2008 (there may be other ship vehicles I'm unaware of) and provides 2-tier, 3-tier, or service-oriented synchronization. It provides hub-and-spoke synchronization. ADO.NET Sync Services version 2.0 will add (the CTP is out now) peer-to-peer synchronization. Microsoft Sync Framework (CTP 1 is out now) includes sync support for data stores that aren't necessarily databases. The best place to go to find some highly informative examples that illustrate the synchronization patterns supported by the model is "The Synchronizer's" (Rafik's) blog.

What's this all have to do with SQL Server 2008 change tracking? Although ADO.NET Sync Services 1.0 is a good start (and there's GUI-based designers in VS2008), it usually means adding timestamp or datetime columns and tombstone tables (tables that track the primary key of deleted rows) to existing database tables and triggers to populate the information sync services needs. That's fairly intrusive, especially with packed applications. SQL Server 2008 change tracking takes care of all of this for you.

You turn on change tracking on a database with ALTER DATABASE and on individual tables with ALTER TABLE. Change tracking does the rest. You can access the information you need for Sync Services applications using the CHANGETABLE table-valed function and a few related functions. You usually want to turn on ALLOW_SNAPSHOT_ISOLATION in the database as well, because change tracking works by tracking when a transaction is committed, rather than when its started.

Change tracking provides the information ADO.NET Sync Services needs to answer request such as "what rows have changed since my particular client (change originator is tracked by change tracking as well) last synchronized" and "have I synchronized with the main database so long ago (change tracking info has a DBA-specified retention) that I need to sync the entire table because the incremental info I need is no longer available".

Currently the VS2008 designers (they're accessed by Added a "Local Database Cache" item to a programming project) don't have an option to indicate "just use SQL Server 2008 and I'm using change tracking", so you have to code the synchronization procedures to use change tracking with Sync Services by hand. Perhaps such a feature is in the works for ADO.NET Sync Services 2.0.

And how about SQL Server Compact Edition (currently the only "ClientProvider" that ship with Sync Services 1.0). Well the SQL Server Compact Edition, Sync Services "just works". No special setup is required, although you may (I haven't determined this) need SQL Server Compact Edition 3.5. This version of SQLCE ships with VS2008. One last thing...ADO.NET Sync Services 1.0 isn't available for compact devices yet, so when you sync with SQL Server Compact Edition, it must be deployed on the desktop.

As a continuation of the previous discussion, here's a DELETE of a title row without fetching it from the database:

pubsEntities model = new pubsEntities();
titles deletetitle = new titles();
deletetitle.title_id = "BU9994";
deletetitle.EntityKey = new EntityKey("pubsEntities.titles", "title_id", "BU9994");
model.Attach(deletetitle);
model.DeleteObject(deletetitle);
model.SaveChanges();

In this case, you need to populate both the property (title_id) that corresponds to the primary key and also populate the EntityKey itself. Attach the object to the store (note that all the original values need not be specified), delete the object from the store, and save the changes. This bit of trickery convinces the ObjectStateManager that the object was in the store all the time, that you'd fetched it from the database. Then you deleted it from the store, so they'll delete it from the database.

Of course, if you have associated titleauthor rows, you'll get an error trying to delete that title. As you would in the relational database. Of course, you can put an ON DELETE CASCADE in the database or delete all of the associated rows first. SQL Server 2005 also allows ON DELETE SET NULL and ON DELETE SET DEFAULT, but you relational data model has to allow this; be careful about using these. You can synthesize and delete all the associated rows and the parent in a single round trip if know what the rows keys are in their entirety, but how do you delete a set of rows in a single statement without fetching or knowing all of the primary key pieces (like the associated au_id in titleauthor table)? Better yet...

UPDATE without fetch is even going to be even more interesting, because you need to know the original row values. Or how about updating/deleting a set of rows based on a non-key column, like "UPDATE titles SET royalty = NULL WHERE ytd_sales IS NULL"? Alex James, Metadata PM of the EF team has posted the first in a series of blog entries on doing just these types of things with EF...it will be interesting to see how far you can take this.

The ADO.NET Entity Framework Beta 3 was released this week. For details, see the ADO.NET team blog.

One of the things that bothered me originally about EDM was that Entity SQL in V1 doesn't contain INSERT, UPDATE, or DELETE statements. Only SELECT. So to update or delete a row, I'd have to fetch it first. Let's start with something simple, like an INSERT with a foreign key constaint?

All of the "add" examples I've seen always insert a new customer and new order and new order details at the same time. But what if your customer already exists?

As an example, the titles table in the pubs database requires a pub_id of an existing publisher. But EDM represents this constraint as a publishers class field in the titles class and an additional  publishersReference instance as a member also. My first attempt to create a new title with an existing publisher (pub_id 1389) went like this.

titles t = new titles();
t.title_id = "BU9994";
// rest of columns elided for clarity
// statement to set the pub_id to 1389
model.AddTotitles(t);
model.SaveChanges();

My first "statement to set the pub_id to 1389" was:

publishers pub = new publishers();
pub.pub_id = "1389";
t.publishers = pub;

This one tries to add the publisher row too and gets a duplicate key violation.

This worked
t.publishers = model.publishers.First(pub => pub.pub_id == "1389");

BUT it causes a SELECT to get the publisher from the database. I wondered at this point if I really needed to do a SELECT to INSERT a row with an existing foreign key row. With some help from Danny Simmons, a much nicer way is to use the publishersReference class and synthesize an EntityKey.

t.publishersReference = new System.Data.Objects.DataClasses.EntityReference<publishers>();
t.publishersReference.EntityKey = new EntityKey("PubsTestEntities.publishers", "pub_id", "1389");

This does the insert correctly with no gratuitous SELECT. Confirmed by SQL Profiler. Good.