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 […]
Bob Beauchemin
SQL Server Blog
Another use for SQL Server 2008 row constructors
Posted on: December 28, 2007 7:07 am
More hints available to plan guides in SQL Server 2008
Posted on: December 28, 2007 6:55 am
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 […]
Using the OUTPUT clause results and INSERT-SELECT
Posted on: December 28, 2007 6:23 am
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. […]
Plan freezing and other plan guide enhancements in SQL Server 2008
Posted on: December 27, 2007 10:26 pm
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) […]
Mapping Insert/Update/Delete sprocs with Many-to-Many
Posted on: December 26, 2007 10:13 pm
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 […]
Mapping Insert/Update/Delete sprocs with Entity Designer
Posted on: December 26, 2007 7:55 pm
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 […]
SQL Server 2008 and ADO.NET Sync Services
Posted on: December 20, 2007 8:08 pm
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 […]
Entity Framework Beta3 – Deleting without fetching
Posted on: December 9, 2007 4:36 am
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 […]
Entity Framework Beta3 – In search of round-trip optimization
Posted on: December 9, 2007 3:39 am
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. […]
