This entry is a continuation of the previous posting on how to use SQL Server 2008 spatial data and Virtual Earth. This entry discusses your web service and SQL Server code choices.

The point of the web service is to translate one of the output formats of SQL Server 2008 spatial to a format this Virtual Earth can use. The spatial data types can be output in:
1. Well-known text format
2. "Native" format - that is, return a Geometry/Geography type to a .NET client as the SqlGeometry/SqlGeography native .NET types
3. GML - Geography markup language

Remember that Virtual Earth needs:
1. GEORSS or
2. KML
3. Custom XML-based format (and custom Javascript code to process it)
4. Javascript strings (to pass to eval())

There is not a one-to-one correspondence between any of these methods, so there is going to be a need for a transformation somewhere. In addition, a SQL Server Geometry or Geography type can contain a collection of points, linestrings, or polygons. There may also need to be a one-to-many translation. This is facilitated by methods STNumGeometries/STGeometryN and STNumPoints/STPointN.

Finally, there could be a pushpin (point) associated with the center of a polygon and/or text associated with the pushpin. Where the text comes from will be application-dependent.

Because there are many ways to process XML in SQL Server and in web services, that's lots of choices. Some that come to mind are:
1. Transforming a SqlGeometry/SqlGeography from a SqlDataReader to a custom XML format
   Javascript code in the web page processes the custom format.
2. Using SQL Server's XQuery to transform GML into GEORSS or KML
   This is returned to the web service as a SqlXml data type.
   Consumed directly as a layer in Virtual Earth.
3. Using an XSLT transform (or LINQ to XML) to transform GML to GEORSS or KML. This can occur in the web service or even a SQLCLR procedure, although this type of processing should really be accomplished in the web service.
4. Using SELECT ... FOR XML PATH to construct GEORSS or KML directly.
   Similar processing to choice #2 above.
5. Transforming a SqlGeometry/SqlGeography from a SqlDataReader to Javascript strings.
   Web page simply calls eval()

Hopefully this posting will elucidate the choices you have for this rendering. Depending on which method you choose, there could be quite a few "moving parts" (a la Rube Goldberg) in the solution.
- Web pages with custom Javascript code
- Web service to supply the data
- HTTP Handler to allow Virtual Earth to use remote GEORSS/KML
- Data access code in SQL Server
- Transformation code in web service or SQL Server

So.....what's your favorite method for using SQL Server 2008 spatial data with Virtual Earth?

Last week at the SQL Server 2008 Jumpstart I showed an example of using SQL Server 2008 spatial data types with Virtual Earth. I showed a single coding style. There really is a plethora of coding styles to using these together, and I'd like to describe some of the most common ones. I'd like to do this without much code for now, because an end-to-end description sometimes results in the graphic aspects (which other controls to use, how to build Ajax web pages, etc) seem to get in the way.

I'm not even going to mention using Virtual Earth to serve tiles directly, but only the Virtual Earth web control. Start with the Virtual Earth Interactive SDK for coding information.

In general, what we're after is:
   Page contains Virtual Earth control
   Page makes a request to a Web Service for data (usually Ajax)
   Web Service get and processes data from database, returns data
   Page uses Virtual Earth calls to present the data

The first two are straightforward, although the Virtual Earth control is available only online via a URL. This makes testing when you don't have internet access virtually impossible. The Ajax request (that is, call a URL with an XML payload using a browser-specific mechanism, wait asynchronously for the response) normally doesn't use SOAP-based Web Services, because you'd have to add and remove the SOAP headers. It's usually XML-in, XML-out, sans SOAP headers.

The Virtual Earth calls depend on what version of Virtual Earth control you're using. I'll show this using version 6.
1. You can render using shapes directly
2. You can import GEORSS into a shape layer
3. You can import KML into a shape layer

Virtual Earth supports visualizing shapes directly (#1 above) by using:
1. AddPushpin - to visualize a point or the centroid of a polygon
2. AddPolyline - that's Linestring in SQL Server 2008
3. AddPolygon - for polygons

For shape layers (#2 and #3 above), the code looks something like this:

var l = new VEShapeLayer();           
var veLayerSpec = new VEShapeSourceSpecification(type, local.xml, l);

map.ImportShapeLayerData(veLayerSpec, onFeedLoad);

The "type" in the VEShapeSourceSpecification constructor can either be GEORSS or ImportXML (that's KML). You can also have a Live Search Maps Collection but likely that one won't come from SQL Server.  Bear in mind that the VEShapeSourceSpecification constructor needs a *local* XML file URL. If you don't want to use a local XML file, you can build an ASP.NET HTTPHandler that uses redirection to pretend a remote XML file is local (see Mike McDougall's article and code to accomplish this).

One last twist that I've seen is that you can build your Javascript code directly in each page OR make the Web Service program reformat your data into Javascript calls directly. Once you're back at the page (the Web Service passes back a string of Javascript) you simply call Javascript's "eval" function. This makes the web page client-side code easy, but the Web Service code lots more complex, as you're generating code, not points, lines, and polygons.

Web Service choices follow next....

SQL Server 2005 introduced the concept of endpoints. Every connection entry point into SQL Server is abstracted as a endpoint. You can see all the endpoints on your SQL Server instance by using the sys.endpoints metadata view. In addition to those you'd expect (for TCP/IP, Named Pipes, Shared Memory) there are also endpoints defined for the dedicated admin connection, Service Broker, Database Mirroring...and HTTP.

The endpoint concept is still with us, but HTTP endpoints, those endpoints that allow you to expose SQL Server procs and functions directly as HTTP Web Services will be deprecated in SQL Server 2008. Upon creating an HTTP endpoint (aka SOAP endpoint) in SQL Server 2008, you'll be warned that "this feature will be removed in a future version. You should not use these in new development work." This was announced at the SQL Server 2008 Jumpstart last week.

So what replaces this functionality (and how many folks actually used it in SQL Server 2005). The likely successor seems to be ADO.NET Data Services, a mechanism to produce RESTful services over any type of data, using Entity Data Model or LINQ to SQL. The model you expose through data services doesn't really have to be a database at all.

And for those of you that liked the idea of exposing your stored procedures as services, ADO.NET Data Services supports "Service Operations", which can be parameterized and contain any type of logic you wish. Service Operations aren't limited to stored procedures, but could be used to encapsulate them.

Wow. It's already Tuesday and I'm still recovering from last week. I've been laying low for the last six months or so, writing a few (OK, quite a few) blog entries on SQL Server 2008 topics. But I'd really been working on my next SQL Server course (on 2008, of course), that premiered last week as part of a program known as SQL Server 2008 Jumpstart.

Simon Sabin (the newest SQLskills associate) and I presented to a good-sized crowd of Microsofties and trainers from everywhere. We did the developer track; there were also DBA and BI tracks by my well-known SQLskills associates. You'll see this course in a lot of different venues in the next few months, but it won't always be us that's doing the presenting. Just so many places we can be at once...

We covered everything from new T-SQL features to Spatial Data and Indexes and everything in-between. In addition to the SQL Server features, there was an entire day devoted to client APIs and dev tools; not only ODBC (yes, I did an ODBC demo in C++) and ADO.NET, but also the Entity Framework, ADO.NET Data Services, Occasionally Connected Apps.

So, for the next few weeks, I'm going to be writing down my thoughts on questions (some of them rather pointed) that arose about the topics we presented. Some of the post will be facts, answers to questions...but they'll be some opinion pieces as well (marked with MHO, that's my humble opinion).

In a previous blog entry a while ago, I wrote about writing a single SQL statement that did a SQL MERGE operation, used the OUTPUT clause to put out a rowset and directed the rowset into an INSERT statement. This happens in one statement without the need of explicitly defining a temporary table and using multiple SQL statements.

Turns out that this feature has a name: Composable DML. I've also heard it called (in SQL Server Books Online) "DML table source".

I've also heard the term composable queries, both in references to both Entity Framework functions and LINQ to SQL. They even have an "IsComposable" attribute in the Function definition) In this context, it means that the output of a function that produces a rowset (in this case a SQL Server table-valued function) or code that produces a rowset can be combined with further queries that do more filtering or projection, or even retrieve related rows. When the queries are submitted to the database, they are composed into a single SQL statement.

The point is pretty similar, reduce the resources required to accomplish a set of operations by reducing the number of statements or database round-trips needed.

More about Service Broker priority in SQL Server 2008.

The books online states, when setting a initiator/target priority as local service name/remote service name that the priority affects:

1. Sends from the initiator queue
2. Receives from the initiator queue
3. Getting the next conversation group from the initiator queue.

And specifies the mirror image priority for target/initiator as local service name/remote service name affects:

1. Receives from the target queue
2. Sends from the target service
3. Getting the next conversation group from the target queue

So, in my previous example, where

The initiator/target as local service name/remote service name is priority 7
The target/initiator as local service name/remote service name is priority 6

Let's send a message from the initiator that "sticks" in sys.transmission_queue. Suppose that either the network is slow or we don't have the appropriate setup. But the message is floundering in the transmission queue. Already SENT, waiting to be delivered.

"select * from sys.transmission_queue;" shows the message BEING SENT (and transmitted) at priority 7. And, when the message is finally DELIVERED, the priority in the receiveq "select * from receiveq" is priority 6.

Normally, you'd likely make both priorities in the initiator/target pair the same to avoid confusion, but you can make them different for greater granularity. And it DOES help to illustrate where and when priority is used.

Conversation priority is a new feature with SQL Server 2008. In a previous blog post, I talked about how to simply set one up. But you'd usually not want to set up a priority for all messages. So, lets set up a simple service and then define a "Premier Customer" priority. Before trying this you need to make sure the database is set to honor broker priority.

ALTER DATABASE pubs SET HONOR_BROKER_PRIORITY ON;

Suppose I had a simple (one database) service pair. A service called "sender" (note lower case) using senderq is the initiator and a service called "receiver" using receiverq is the target. They use the [default] contract. So we can set up a "Premier Customer" by contract.

CREATE CONTRACT premier ([DEFAULT] SENT BY ANY);
GO

Now, let's ensure that the target can also use the premier contract in addition to the DEFAULT contract.

ALTER SERVICE receiver ON QUEUE receiveq (ADD CONTRACT premier);

We need to set up the priority.

CREATE BROKER PRIORITY PremierCustomer
    FOR CONVERSATION
    SET (CONTRACT_NAME = premier,
         LOCAL_SERVICE_NAME = sender,
         REMOTE_SERVICE_NAME = 'receiver',
         PRIORITY_LEVEL = 7);
GO

The interesting thing is, this priority ONLY covers messages sent and received (and get conversation group) by the sender/senderq, IT DOESN'T cover the receives in the receiver/receiveq queue. So we'll set up a mirror image priority, and make the priority level still higher than the default (5) but gratuitously different than the other priority

CREATE BROKER PRIORITY PremierCustomer2
    FOR CONVERSATION
    SET (CONTRACT_NAME = premier,
         LOCAL_SERVICE_NAME = receiver,   -- local service is receiver
         REMOTE_SERVICE_NAME = 'sender', -- remote service is sender
         PRIORITY_LEVEL = 6);                   -- note level 6, not 7
GO

Now, begin a conversation using sender and receiver and the premier contract and send a message. Note that when you issue "SELECT * FROM sys.conversation_endpoints;" the sender (initiator's) endpoint is priority 7, the receiver's endpoint is priority 6. And, looking at the message in the receiveq its set to priority 6.

If you'd send a return message from the receiver (target) back to the sender (initiator), the return message in the senderq would be priority 7.

So when setting up priorities, if you want all messages on both sides to be affected, don't forget to set up a priority for EACH side. Here's the entire demo.

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.

One last SQLCLR feature I'd forgotton about but was quite highly publicized. This is extension of SQLCLR UDT and UDAgg maximum size from 8000 bytes to 2gb. You just the MaxByteSize of -1 in the appropriate attribute, like this:

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)]

The serialization format is going to have to be UserDefined, the limt for Format.Native is still 8000 bytes.

Just catalog the UDT or UDAgg and use it as you would any other TYPE/AGGREGATE. I crufted up a proof-of-concept one pretty easily, and the new spatial data types (GEOGRAPHY and GEOMETRY) are also large (system) UDTs, so you know this works as of CTP5.

Now you can have your "infinite" UDT that holds an array or "infinite" string concatenation UDAgg. Enjoy.

Another cool SQLCLR feature in SQL Server 2008 is the ability to define a table-valued function with an ORDER clause. If you have intimate knowledge that the function always returns rows in sorted order, you declare your SQLCLR UDF with an ORDER clause in the CREATE FUNCTION DDL statement. Let's experiment with this using the cheap and easy Fibonacci sequence function from Dan Sullivan and my SQL Server 2005 Developer's Guide.

create assembly orderedtvf from 'C:\temp\OrderedTVF.dll'
go

-- no order clause
create function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
as external name orderedtvf.FData.Fibonacci
go

The Fibonacci sequences generated are always in ascending order because that's how the function is implemented. In fact, they are in order by both the "next" and "prev" column. Because there is no limit to the number of method signatures we can have over the same SQLCLR UDF, we use the same SQLCLR method, just changing the DDL statement and function name.

create function FibonacciByNext (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (next asc) -- this is new
as external name orderedtvf.FData.Fibonacci
go

create function FibonacciByPrev (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev asc) -- same concept, different ordering column
as external name orderedtvf.FData.Fibonacci
go

Now let's do some testing. The query plan iterators and plan cost are shown as comments.

-- cost: 0.0279081
-- TVF -> Sort -> Select
select * from dbo.FibonacciUnordered(3,4,5)
order by next

With an ordered TVF, there is no SORT iterator, but there are extra query plan steps to operate on the ordered set. AND... the query cost is over 10x lower.

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by next

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByPrev(3,4,5)
order by prev

Note that it IS best to have a different TVF name for each sort order, and "no order", if you plan to use different ORDER BY clauses. This one has a Sort AND its cost is greater than the function that's declared UnOrdered

-- Different order
-- cost: 0.0292881 (more than Unordered = 0.0279081)
-- TVF -> Segment -> Sequence Project -> Assert -> Sort -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by prev

In addition, the query plan guarentees that you don't lie in your order clause. Here's proof.

create function FibonacciWrong (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev desc) -- THEY ARE IN ASCENDING ORDER, NOT DESCENDING
as external name orderedtvf.FData.Fibonacci
go

-- Error:
-- The order of the data in the stream does not conform to the ORDER hint
-- specified for the CLR TVF 'dbo.FibonacciWrong'.
-- The order of the  data must match the order specified in the ORDER hint for a CLR TVF.
-- Update the ORDER hint to reflect the order in which the input data is ordered,
-- or update the CLR TVF to match the order specified by the ORDER hint.
select * from dbo.FibonacciWrong(3,4,5)
order by prev

Remember, you're not only saving a SORT iterator in the query plan, you're saving memory too. The SORT iterator requires a memory grant. And because there are no stats for SQL Server to use in these "opaque to SQL" functions, the memory grant for the SORT iterator in FibonacciUnOrdered is 1024K. And, we hope that the rather generous memory grant is enough, else memory is being allocated during query execution.

So, declaring ordered TVFs is worth it.

One thing that I didn't find in the BOL What's New page is some of the new SQLCLR functionality in SQL Server 2008. The first one that intrigued me is support of multi-input user-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate, an analytic function that returns the population covariance between two expressions. The signature is COVAR_POP(expr1, expr2) and I want the signature to stay the same in SQL Server.

All that I need to do this is to use the "template" for a .NET UDAgg struct/class, replacing the Accumulate method that take one parameter with a 2-parameter method, like this:

public void Accumulate([SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value1, 
                               [SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value2)
{
  // code here
}

My CREATE AGGREGATE DDL statement would change a bit:

CREATE ASSEMBLY multiparmagg FROM 'C:\temp\multiparmagg.dll'
go

CREATE AGGREGATE dbo.covar_pop(@expr1 decimal(20,10), @expr2 decimal(20,10))
RETURNS decimal(20,10)
EXTERNAL NAME multiparmagg.CovarPop;
go

To invoke:

create table dbo.test_covar (
  i1 decimal(20,10),
  i2 decimal(20,10)
);
go
-- fill with data, then...
select dbo.covar_pop(i1, i2) from dbo.test_covar;

Happy aggregating.

With any new product or CTP, I've always gotten fast results by starting with the readme.txt file that comes with the product/CTP. Although its now called ReadmeSQL2008.htm, its still worth reading. This usually gives you answers about install scenarios and last-minute changes.

For example, this readme file answers two of the (so-far) FAQs about CTP5.
  What happened to Surface Area Configuration Utility?
  Can I install SQL Server 2008 CTP on Windows Server 2008? (ie, is it officially supported)

You'll need to RTFR for answers to these.

Right after the readme file, the first thing to do (after you've installed it) is to take a gander at the SQL Server Books Online, in the "What's New" section. Here's a bookmark for those without the facility to look in the TOC. mshelp://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10sq_GetStart/html/6a428023-e3cc-4626-a88a-4c13ccbd7db0.htm.

There's WAY TOO MANY new features in this CTP to describe in a few sentences. And the BOL writers have really had their hands full with this CTP, by the looks of the sheer volume of new material. Enough to keep anyone reading and experiementing for weeks. However...

Every once in a while something slips through the readme and What's new, or isn't finished yet, like the "What's new in SQL Server Installation" section. Or folks think of new ways to use features the BOL writers hadn't thought of. Or want to expound on the repercussions of feature XYZ. Stay tuned for these.

SQL Server 2008 adds the concept of priority for conversations. It's setup using special DDL statements, priority cannot be specified on the CREATE DIALOG CONVERSATION or SEND/RECEIVE DML statements. The DDL statements are CREATE/ALTER/DROP BROKER PRIORITY.

To specify a priority, you associate a BROKER PRIORITY object with combinations of the qualifiers LOCAL_SERIVCE_NAME/REMOTE_SERVICE_NAME/CONTRACT and the priority is associated with all messages and conversation endpoints that match that combination. The wildcard 'ALL' (or leaving the qualifier out entirely) is permitted for any or all of the qualifiers, and matching precidence is specifying in BOL under the CREATE BROKER PRIORITY syntax. The set of defined priorities is stored in sys.conversation_priorities metadata view.

In addition to this setup, the database has to be set to use priorities, with the "ALTER DATABASE...SET HONOR_BROKER_PRIORITY ON" DDL statement. The default behavior is not to honor priorities.

Once you define BROKER PRIORITY(s) and set the database to honor them, priority (default is 5) will be set on:
 sys.conversation_endpoints
 each message in the queue 
 sys.transmission_queue messages

Service Broker priorities are a much asked-for feature and its good to see it implemented. Because this is a new feature in CTP5, there are still some rough edges that need fixing. You can't set honor_broker_priority on CREATE or ATTACH database. And using the SMO scripter (Script As/CREATE on the database in Object Explorer) doesn't set honor_broker_priority either.

There are some really nice examples of the syntax and the concept in BOL. I've enclosed a really simple starter script that changes the default priority and illustrates the metadata. Enjoy.

SQL Server 2008 adds support for the 'let' clause in FLWOR expressions. The for and let clauses have a similar purpose, to bind content (tuples) to variables.  Either one can begin a FLWOR expression:

declare @x xml = '';
select @x.query('
for $i in (1,2,3)
return $i
');
> returns 1 2 3

declare @x xml = '';
select @x.query('
let $i := (1,2,3)
return $i
');
> returns 1 2 3

The distinction is that let is an assignment clause, in the simple statement using 'let' above, $i refers to the entire sequence (1,2,3). The for clause sets up an iterator. The simple statement above using 'for' loops 3 times and each time through the loop $i refers to a single member of the sequence. So, if I add an 'order by' clause, the results are quite different.

declare @x xml = '';
select @x.query('
for $i in (1,2,3)
order by $i descending
return $i
');
> returns 3 2 1

declare @x xml = '';
select @x.query('
let $i := (1,2,3)
order by $i descending
return $i
');
> error:
> XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'

One limitation on the XQuery let clause is that it does not support constructed elements. So this statement works fine:

declare @x xml = '';
select @x.query('
let $x := 1
return $x
');
> returns 1

but this statement does not:

declare @x xml = '';
select @x.query('
let $x := ( <foo>2</foo>, <bar>2</bar> )
return $x
');
> error:
XQuery [query()]: 'let' is not supported with constructed XML

So Let the use of the let clause begin...no longer do I have to explain what a "FWOR" expression is, hooray. However, nota bene. When 'let' is used inside a loop, it's evaluated each time around the loop:

declare @x xml = '';
select @x.query('
for $i in (1,2,3)
let $j := 42
return ($i, $j)
');
> returns 1 42 2 42 3 42
> $j is evaluated three times

The upcoming SQL Server 2008 provider for Powershell was a big hit and sparked quite a bit of discussion when I talked about and demonstrated it during TechEd/Developers last week. At the time I thought that this might be a good discussion topic for database administators, and, after asking around, a room was found, and I'll be re-presenting this session, entitled "Using Windows Powershell with the SQL Server 2008 Provider and SMO" here at TechEd/ITForum tomorrow (Friday) at 13:30-14:45 in room 131.

If you do administrative tasks with Powershell or are interested in SQL Server, drop by and bring your opinions. See you there.

I'm up early this morning for a repeat of my T-SQL in SQL Server 2008 talk. Things really went well for the last two days, folks really seemed to like to see lots of actual working code, albeit my examples are always "minimalist". There was a lot of interest around the Spatial data talk and some discussion of upcoming and ongoing projects that will take advantage of this new functionality. The increase in interest could be been caused by announcements about spatial data at the conference and also the opening of the new Spatial Data section on the SQL Server website. My SQL query tuning segment generated a lot of interest too.

If you're around the conference this morning and interested in SQL (and who isn't?) stop by and say hi.

Today is day 1 of TechEd/ITForum in Barcelona. I have a bit less hectic of a workload at this one, a total of 4 sessions, 3 of them being "interactive sessions" (this year's word for chalktalk). Today's session is going to be on T-SQL enhancements in SQL Server 2008, at the late-in-the-day time of 5:45-7:00pm. C'mon out and I'll see you there, or, if you can't make the late session, I'm repeating it on Wednesday.

Tuesday, I'm doing my only breakout, the "day's worth of material in 75 minutes" on SQL query performance tips and tricks. And, in the afternoon, a session on spatial data (and indexes) in SQL Server 2008. See you there...

Actually yesterday, but today was the first time I'd had a chance to write about it.

Having done quite a bit with what's now being called "traditional web services", my first impression of REST were, I'll have to admit, the thought that it was web services without schema. I kinda like my metadata; it's always been irritating that stored procedures do not store ANY metadata on the number or shapes of the rowsets returned, only metadata on the parameters are stored. The closest that ANSI comes is allowing specification of the number of rowsets, a piece of the standard the SQL Server doesn't implement. So web services without schema seemed less useful than web services with.

But one of the up-and-coming data access technologies at Microsoft is Astoria, which is described as "a REST-ful set of interfaces to relational database (and other) data". It's causing a lot of excitement.

After the conference I mentioned this to an old friend, Jon Flanders, as he was wearing his "real programmers care about URIs" (or something close) teeshirt. Asked him to explain the zen of REST. To summarize, I got the impression it was all about the location specification and using HTTP verbs like GET/POST/DELETE to effect "state transfer" operations. And, although its not very common, metadata can be specified using WADL (Web Application Description Language). So specifying the location is perhaps like a "connection string" to the service? And to the data the application interacts with?

I'm still a bit skeptical of the (seeming) typelessness and contract-lessness of it all, the "HTTP is the only protocol"-ishness, and IIRC, I can find out the location of a traditional web service using WSDL's soap:address element's "location" attribute in the service portion. But at least I have an somewhat of an understanding of what all the buzz is about.

More on Astoria in future posts.

Whew! I had a full week at TechEd Barcelona, just getting around to finishing up writing about it. It was great to get out and see everyone, there were a lot of familiar faces. Thanks for showing up and saying hello. I fully enjoyed each and every presentation and interactive discussion.

A special thank you to Gunther Beersaerts for allowing me to attend and present. Got to meet a lot of "the old gang" as well as some people who I'd only known through their blogs, like Ward Pond and Johannes Kebeck. And I got to attend the Belgium/Luxembourg country drinks party, where I had some great technical discussions over beers.

I don't have exact figures, but because I had the same room for each breakout, I "counted" interest by counting the number of people who showed up. Transactions and Optimizing T-SQL still rule, although there was almost a full room for SQLCLR. And there was more than I expected for Service Broker and quite a few folks for SQLXML. Packed a double "interactive" room for the Powershell in SQL Server 2008 presentation/discussion, although the liveliest discussions were in the "OLE DB and ODBC" and "tell us where it hurts in SQL Server" sessions. The ODBC folks were happy they aren't being forgotten, it was enlightening to see the numbers using OLE DB and ODBC. Thanks to John de Longa of DataDirect for his input on ODBC/OLE DB and Elisa Flasko for helping me moderate the discussion. A number of people seem quite worried about the advent of LINQ to SQL/EDM and what they see as the upcoming deluge of dynamic SQL and its effect on the plan cache. I'll have more to write on that subject soon.

Next week is ITForum. I won't be quite as busy, but I will be doing a few cool talks on Spatial Data in SQL Server, T-SQL improvements in SQL Server 2008 and T-SQL query tuning. See you there.

Today, I'm finishing up my (many) talks at TechEd/Developers' Barcelona with a breakout on best practices for transactions and isolation and leading an interactive discussion on OLE DB and ODBC. I love talking about transactions and, in addition to demonstrating all of the isolation levels SQL Server supports their behavior and repercussions, I'll even show the fairly esoteric "MARS batch-scoped transaction".

The "OLE DB and ODBC" discussion should be good. After seemingly moving away from ODBC since the late 1990s (actually they never moved away) there has been a movement toward embracing the ODBC lifestyle and reiterating support in the pre-SQL Server 2008 timeframe. I'll be co-hosting this one with Elisa Flasko of the Microsoft data access team.

See you there!