Loading ShapeFiles into SQL Server 2008 and 2012

Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything […]

I’m at DevWeek London Mar14-18

I'm getting my plane tickets and plans together for DevWeek 2011 in London, coming up in March. I'll be doing a preconference workshop on "Writing SQL Server database applications and stored procedures for best performance". In addition, I'll be presenting on:    Performance Enhancements in SQL Server 2008    LINQ vs Stored Procedures    Spatial Indexing […]

SQL Server Security Best Practices whitepaper update is live

I've recently completed an update to the SQL Server 2005 Security Best Practices whitepaper. It's available on the security and compliance website under whitepapers or the direct link is here. Unsurprisingly, its called "SQL Server 2008 R2 Security Best Practices – Operational and Administrative Tasks". Thanks to all the folks who reviewed it. Enjoy. @bobbeauch

The interesting case of TVPs and plan compilation

A couple of people reported problems with SQL Server 2008's table-valued parameters and it eventually got back to me. Had to try it out. The reported problem is that there's a plan compile each time TVPs are used from ADO.NET. Interestingly, when the same statements are executed from SSMS (which is an ADO.NET client program), […]

SQLCLR debugging and VS 2010 revisited

In my blog entry from about a month and a half ago, I summarized my finding on using Visual Studio 2010 with SQLCLR, and the partial support of SQL Server 2008 new features. One feature (with the disclaimer that's it's not preferred/supported) that I thought I'd lost was "attach to process" style debugging with SQLCLR. […]

Visual Studio 2010 and SQLCLR 2008 new features

There's a special kind of project for SQLCLR development in Visual Studio. It's been around since Visual Studio 2005, when SQLCLR was introduced in SQL Server 2005. When SQL Server 2008 was released, there were a bunch of new features introduced in the SQLCLR arena. Unfortunately, Visual Studio 2008 supported none of them. We were […]

Guide to the Data Development Platform for .NET Developers whitepaper available

I've been busy for the last few weeks putting together a "manifesto" whitepaper about the .NET-based data access stacks and also the possibilities for .NET programmers in the SQL Server product itself. The whitepaper's direct link is here, although its also available via both the MSDN Data Developer Center and the SQL Server 2008 Application Development website. […]

How’s about a map on your SSRS report?

When I downloaded SQL Server 2008 R2 CTP2 last week, I went right by (for now) the cool Gemini features and even Multi Server Management and went straight for the new SSRS Map Control. After explaining the SQL Server 2008 spatial data types, the spatial methods, and SRIDs to folks until they glazed  over, and […]

Microsoft Complex Event Processing platform is called StreamInsight

And a CTP is available now. I first heard of the .NET Complex Event Processing engine at this year's TechEd. Until lately, all we had to go on was Torsten Grabs' TechEd presentation and a whitepaper that gave a fairly high-level overview of its purpose and architecture. And, oh, it was to be released as […]

At the ESRI user conference next week

Just got a mail message from an old friend who asked why I'd dropped out of sight in the past month of so. I've been in "extended partial vacation mode" for the last month or so and haven't blogged in a while. But I'm "still alive and well" (Johnny Winter was at the Portland blues […]

Book review – Beginning Spatial with SQL Server 2008

About a month ago, I received a copy of "Beginning Spatial with SQL Server 2008" by Alastair Aitchison. I've become acquainted with Alastair through his frequent postings on the SQL Server Spatial forum (under the login Tanoshimi), where he's always been very helpful and patient with folks starting out with spatial data concepts and SQL […]

Talking about spatial data on RunAs Radio

My interview with the folks at RunAs Radio is out. I'm discussing one of my favorite topics, spatial data and spatial in SQL Server 2008, with Richard and Greg. This wandered off into some interesting possible usages for this data. Catch it here.

Article about programming filestreams on MSDN

My latest article on out. It's in the May issue of MSDN magazine, and can be found here. It this article I explore the internals of programming with the filestream feature of SQL Server 2008 and some best practices around when and how to use it. Hope you like it.

How to ensure your spatial index is being used

I've answered quite a few questions lately about how to make sure that your spatial index is being used. Use of a spatial index with a large or complex spatial dataset can mean the difference between a query that takes minutes to execute and subsecond execution. You also want to use an index hint as […]

Spatial Indexes in SP1, almost no hints required

On experimenting with the CTP of SQL Server 2008 SP1, I found that there were some changes made to the costing algorithm, vis-a-vis spatial indexes. In the RTM version, spatial indexes weren't uses as often as they could be, because the query costing was too high compared to the spatial filters. This made programmers resort […]

My First MSDN column is live today

I've become a columnist for MSDN magazine and my first column is available in the online edition, Feb 2009 issue today. I'm doing a column every few months called "Under The Table", about database-centric development. The first column is about one of the most exciting (IMHO) new features in SQL Server 2008, spatial data type […]

SQL Server Compliance Portal is now live

Today I came across the new SQL Server 2008 Compliance Portal. This portal has information and links to the new Compliance whitepaper and compliance scripts (the "sample files" at the bottom of the main page on the compliance portal). New features for ensuring compliance in SQL Server 2008 include Policy-Based Management, Auditing, and TDE, to […]

Spatial Methods: What’s with the ‘ST’?

When SQL Server's spatial data types were introduced, people asked "Why does every standard method begin with ST" (e.g. STIsEmpty). SQL Server docs always cite the OGC (Open Geospatial Consortium) specifications, but OGC's methods do not begin with ST. Where did it come from? As far as I can figure the "ST" before every standard […]

SQL Server Spatial: EMPTY vs. NULL

Yesterday a friend asked me if it was better to always use a value of GEOMETRYCOLLECTION EMPTY as an alternative to making a geometry/geography column nullable. OGC has its own concept of database NULL, that is [GEOMETRY] EMPTY, where [GEOMETRY] can be any valid geometry subtype (that is 'POINT EMPTY', 'LINESTRING EMPTY', etc). When I asked why […]

Spatial Index Diagnostic Procs – The Rest of the Story

I've devoted the last few blog entries to describing what's in that spatial index analysis proc output and how to use it and interpret the results. Just wanted to describe the rest of the information and how the information relates to what you'd find in a query plan that uses a spatial index. The procs […]

Spatial Index Diagnostic Procs – How to specify query sample

A little more about the query sample that gets fed into the spatial index procs. Query sample is a singleton geometry or geography. It's not a query. So it's not as straightforward as "here's a spatial query that could use an index, run this query and show me the figures". So this procedure is not […]

Spatial Index Diagnostic Procs – Filter Output

Last post discussed the filters. The procs report some raw numbers and some derived numbers. Here's a cheat sheet, although the info is all in the BOL. N = Number of rows in the table O = Number of rows output P = Number of rows selected by primary filter (by the index) S = […]

Spatial Index Diagnostic Procs – Filters

The spatial index diagnostic procs' most enlightening pieces of information have to do with the filter counts and efficiencies reported at the end of the set of columns or XML document output. But what are these filters exactly and what do the results mean? The proc output refers to three filters: 1. Primary Filter 2. […]

Spatial Index Diagnostic Procs – Intro

I've been looking at the spatial index stored procedures (sp_help_spatial_geography_index_xml and friends) a little harder recently, in an attempt to help answer two questions. 1. If I have a spatial query, why does/doesn't it use my spatial index? 2. If I have a specific spatial query in mind, what are the best values to choose […]

Secrets, hints, and tips for SQL Server Extended Events

I've been doing talks and demos on SQL Server 2008 Extended Events for a while now, it's one of my favorite parts of the product. Per session waitstats, SQL stack, built-in system health session, what's not to like? Before my talk at TechEd EMEA for IT Professionals last week (which went really well), I discovered something that […]

Using the spatial index diagnostic stored procedures

I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune […]

Broker External Activator (and more) in latest SQL Server 2008 feature pack

I'm not usually one to post product announcements, but I had to point out that the Service Broker External Activator shipped with the latest (Oct 2008) version of SQL Server 2008 feature pack. It's available here. Doing a quick "visual diff" with the August 2008 feature pack, I also notice some new PowerShell extensions, SQLXML […]

November Road Trip

It's almost November and I'm in the final phase of getting ready for a road trip. In the next three weeks I'll be speaking on SQL Server 2008 at: TechEd EMEA ITPro – talks on Extended Events and on T-SQL performance improvements TechEd EMEA Developers – talks on T-SQL perf, SQLCLR, Filestream programming, Sparse Columns, […]

SQL Server 2008 and TokenAndPermUserStore

In the past year or so, a few installations had begun to experience problems with the SQL Server 2005 security cache (aka TokenAndPermUserStore) growing too large over time. Some manifestations are connection and query timeouts and queries that take a long time. The folks at PSS published the canonical blog entry about this problem, including […]

SQLCLR interop between SQL Server versions and Visual Studio multi-targeting

A friend of mine was asking about the affect of multi-targeting in Visual Studio 2008 on SQLCLR. For an explanation of how multi-targeting works, reference David Kean's blog entry on Visual Studio 2008 multi-targeting and FXCop. I happened to have an instance of SQL Server 2005 RTM (which uses .NET framework version 2.0.50727.42) on hand […]

SQLCLR default procedure parameters scripts correctly by SSMS and SMO 2008

Although being able to use a GUI tool like SQL Server Management Studio is a nice feature by itself, it's unusual that fixing something in a GUI makes a feature (that hasn't changed) more useable. The "feature" is defaults on SQLCLR procedure parameters. You can't have defaults on parameters in .NET, but you can specify […]

Speaking on Extended Events at SQL Server User Group

Extended Events is one of my favorite SQL Server 2008 features and I’ll be speaking about how to use them for problem diagnosis, at the Portland (Oregon) SQL Server User’s Group’s September meeting. Check out http://www.pdxvbug.com/pdxuser.asp for details. Meeting is 6:30 on Thursday Sept 25. See you there.

On hinting spatial indexes and query complexity

After reading Isaac's recent blog posting about spatial queries and index hinting, I thought I might add some information based on a query I was working with this week. Sometimes if you have a query that's too syntactically complex, hinting won't work. In these cases you can cause the index to be used (or at least […]

Visual Studio 2008 SP1 and SQL Server 2008 SQLCLR features

When SQL Server 2008 shipped, Visual Studio 2008 SP1 and .NET 3.5 SP1 shipped a few days later. Visual Studio SP1 contains some neat enhancements that allow you to use SQL Server 2008 databases in Server Explorer and the related designers. There's support for SQL Server change tracking in the Sync Services designer. The EDM and LINQ […]

A GUI visualizer/editor for SQL Server 2008 Extended Events

One of my favorite new features of SQL Server 2008 is extended events. I've written a bunch of blog entries on 'em (use the search, type in Extended Events). So a few days ago, I recieved an email from Jonathan Kehayias directing me to his new program on Codeplex, the SQL 2008 Extended Events Manager, […]

A helper function for ring-orientation in the SQL Server 2008 geography data type

Folks have always had trouble with the fact that ring orientation is required with spatial instances if you're using SQL Server 2008's geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen's blog entry here and Ed Katibah's blog entry (link in next paragraph). In Ed's […]

Semantic (possibly breaking) change in SQLCLR TVFs

I came across the following interesting behavior while testing a SQLCLR table-valued function that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server 2008. It appears to be by design, because the error message (in 2008) clearly indicates what's wrong. But the code worked in SQL Server 2005 and […]

Demos for Portland SQL Server user group talk on SQL Server spatial

For attendees of last Thursday’s talk on SQL Server Spatial for the masses, the demo code is located here. Thanks for coming and for your participation!

DDL appdomains appear in SQL Server log in SQL Server 2008

.NET (and therefore SQLCLR) divides up running its code (even within the same process like the sqlserver.exe process) into appdomains. The appdomain is like a lightweight process used to enforce isolation between running .NET code within the same Windows process. SQLCLR (.NET code running in SQL Server) uses appdomains to isolate execution of .NET code […]

I’m speaking at Portland SQL Server Users Group next Thursday

I’ve been able to coordinate being home and the Portland (Oregon) SQL Server user group’s meeting schedule, so next week I’ll be speaking “at home” for a change. I’m giving a talk on one of my favorite features for developers in SQL Server 2008, Spatial Data support. Because it’s relatively common these days to see […]

Programming SQLRegistration in the SQL Server PowerShell Provider

I've noticed that some folks have written PowerShell scripts that execute against a list of servers. In the scripts, they read the names of the servers from XML files. But the SQL Server PowerShell provider in RC0 has a useful "component" called SQLRegistration; its "path" is SQLSERVER:\SQLRegistration that they can use instead. This path permits enumeration […]

Using PowerShell to program SQL Server’s Policies and Data Collection

I received a question today about whether I'd converted my Policy-Based Management examples using SMO (see the multi-part "Programming Policy-Based Management with SMO" series, starting here) from C# to PowerShell yet. I did do this a while ago; they're available as a script download on the SQLskills website (look on the "Past Conferences" page under TechEd […]

Changes to the SQL Server PowerShell provider in RC0

Last week at TechEd Developers, I gave a talk on PowerShell and SQL Server. I mentioned some upcoming changes in RC0, and have just had a chance to check them out. The PowerShell provider for SQL Server has been expanded to handle not only a "SQL" subdirectory (which enumerates database objects) and "SQLPolicy" (which enumerates […]

Don’t just read the Readme, read the Release Notes too

SQL Server 2008 RC0 comes with a Readme file and a Release Notes file. It’s always good to read both. In this RC, the readme file contains information on installation and upgrade, and the Release Notes file contains information about things that have changed from previous releases. In RC0, they’ve changed the syntax/format of a […]

Performance features in SQL Server 2008 RC0 – Hashes for queries and query plans

There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet. Another performance-related feature (actually its additional information) allows you to have better visibility into performance-affecting queries. This information is available as columns query_hash and query_plan_hash in the sys.dm_exec_query_stats and sys.dm_exec_requests DMV. There's a really nice illustrative example in Books […]

Performance features in SQL Server 2008 RC0 – Optimize for Adhoc Workloads

There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet. There's a couple of features that show up in RC0 that are performance related. One is a server setting 'optimize for ad hoc workloads'. This one tells SQL Server to save a "compiled plan stub" in the query cache […]

What’s the DataSet doing in a trace of an Entity Framework program?

In a blog posting about a few weeks ago, I'd written about noticing a DataSet being created in an Entity Framework program by using the ADO.NET client trace facility. Entity Framework programs do (indirectly) cause a DataSet to be created, but its only for resolving the ProviderInvariantName of the underlying data provider. NOT for anything […]

Heading Out To TechEd

I’m getting ready to head out to TechEd in Orlando. I’ll be doing a full day of SQL Server 2008 for Developers on Monday, a talk on SQL Server client and server-side tracing (including SQL Server 2008 Extended Events), one on PowerShell and SQL Server, and finishing up with a talk on SQL Server Security […]

Saving a roundtrip when inserting rows with filstream columns

It's a good idea when talking to a database to save on network roundtrips. The table-valued parameter in SQL Server 2008 is an example of a feature that can reduce them in the "1 order, 1-n detail items" use case. So its always been mildly irritating that in order to insert a row with a […]

SQL Server 2008 Change Tracking and Sync Services ARE made for each other… in VS2008 SP1

This item was mentioned in other blogs too, but just to mention it again (so my SQLCE and Sync Services friends won't think I overlooked it)… Visual Studio 2008 SP1 Beta contains direct support for using SQL Server 2008 Change Tracking. When you use ADO.NET 1.0 Sync Services with a SQL Server 2008 database, the […]

SQL Server 2008 data types in LINQ to SQL and EF, they (mostly) did it

Last fall I'd asked the folks on the data access team about the possibility of including the new SQL Server 2008 data types in the new data access object mapping layers. At that time they said "no" but they also asked "why do you want it?" and "are people expecting it?". Someone must have had […]

First blog entry in a month…what’s up

After posting the last blog entry, I realized that I hadn’t blogged in about a month. Hmmmm… Well, since last month I posted the demos for my SQLConnections talks on the SQLskills website (under PastConferences). I really had a good time at SQLConnections, its been a while since I’d been invited to speak there. Thanks […]

Trace Events in ADO.NET Entity Framework

Every once in a while I’ll get inquires on a paper I wrote in 2004 about the ETW trace providers for ADO.NET (named “Tracing Data Access”). I got an inquiry today, and since I’d recently installed Visual Studio 2008 SP1 Beta, I thought I’d try it out on a LINQ to SQL program and an […]

Heading off to SQLConnections

I’m heading off to SQLConnections in Orlando early tomorrow morning. I’ll see starting with a preconference “Day of SQL Server 2008 for Developers” and covering, well, every new feature that could interest a developer. I’ll also being doing a series of talks about everything from Spatial data support to Extended Events to PowerShell in SQL Server […]

Converting an EAV design to sparse columns and populating

One of the uses for sparse columns will likely be replacing entity-attribute-value designs. In an EAV design, the "sparse attribute" values are represented by a separate table. The attribute value can either be declared as (n)varchar or sql_variant. An example I've always used is the hardware store that wants to keep track of its products […]

Getting an activity ID with Extended Events

It's easier than you'd think… When you use the SQL Server 2008 CREATE EVENT SESSION DDL statement with extended events, you specify: Events to be captured (e.g. sqlserver.error_reported) Actions to be fired to add more information (e.g. sqlserver.sql_text) Predicates for conditional capture (e.g. where sqlserver.error_reported.error = 547) Event target to collect the events (e.g. package0.ring_buffer) […]

SqlClient, System.Transactions, SQL Server 2008, and MARS

It appears that there are some changes in .NET 3.5 System.Transactions (or System.Transactions.dll and System.Data.dll version 2.0.50727.1433 if you're looking at assembly versions). Florin Lazar blogs about a change to the syntax here. And Alazel Acheson blogs about changes to SqlClient changes to accomodate using lightweight transactions with less promotion to distributed transactions in the ADO.NET Team blog. […]

Accessing multiple servers with the SQL Server 2008 PowerShell provider

Short post this evening… Just in case anyone else flails around looking for this feature, its right under your nose. When you use the PowerShell SQL Server 2008 provider, you have visibility to a single, local machine (and all its SQL Server instances you can access with integrated security) by default. To get access to […]

More about sparse columns and column_sets

I'm still getting used to the new sparse column feature in SQL Server 2008. I'd just read in the BOL definition of ALTER TABLE that you can add a column_set to an existing table. I was converting a sample app from an EAV (entity attribute value) design to sparse columns. I used the existing "attribute-value" […]

Programming Policy-Based Management with SMO – Part 6 – Categories, Subscriptions, Wrapup

This is the last part of a series on programming policy-based management. The series starts here. In the previous installment, I created a policy that was constrained to a single database. To accomplish this, I used a Condition that called out the database by name, and tied it to the TargetSet using TargetSet's SetLevelCondition method. […]

Programming Policy-Based Management with SMO – Part 5 – TargetSets and TargetSetLevels

This post is part of a series on programming policy-based management. The series begins here. So, we were working with a policy that required an ObjectSet. ObjectSets contain TargetSets. For example, the ObjectSet for the naming policy (IMultipartName) we were working on needs a TargetSet for Procedure, Synonym, Table, Function, Type, View, and XmlSchemaCollection. Note […]

Programming Policy-Based Management with SMO – Part 4 – Introducing ObjectSets

This is part of a series on programming policy-based management. The series starts here. Now, we'll tackle programming a little bit more complicated policy. The table-naming standard that applies to a set of database objects. For this, we need three items: 1. Condition for defining the policy itself. 2. Policy that uses the condition and […]

Programming Policy-Based Management with SMO – Part 3 – ExpressionNode and Policy

This is part three of a series on programming policy-based management. The series starts here. So, to initialize my Condition's ExpressionNode I need more than just a string. It doesn't look like, at this time, every ExpressionNode CAN initialized with a string. But we can use the subclasses. The ones I need here are Operator, […]

Programming Policy-Based Management with SMO – Part 2 – Conditions

This is the second part in a series about programming policy-based management. The series starts here. To build our MailOffByDefault policy we need:    Condition that specifies properties and settings    Policy that uses the condition Condition first. This looks pretty straighforward. Condition con1 = new Condition(ps, "MailOffSMO"); con1.Facet = "ISurfaceAreaFacet"; con1.ExpressionNode = ExpressionNode.Parse(                         […]

Programming Policy-Based Management with SMO – Part 1 – Intro

I've been looking at the new Policy-Based Management (was: Declarative Management Framework) in SQL Server BOL. All of the BOL examples use the SSMS user-interface to define and maintain the policy store. While there will likely be 3 ways to configure PBM as there is with, say, replication (SSMS, SMO, and system stored procs), I […]

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 6

This is the last post in the series, at least for now. I'll update it (or post more on the topics discussed here) as the products involved evolve and mature. This one's about: LINQ to SQL and EF queries will be "untunable" because, even if you discover a performance problem, you can't change the underlying […]

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 5

This post is part of a series about worries when implementing LINQ to SQL or ADO.NET Entity Framework from a SQL database-centric programmer's perspective. The last two worries are related. It's mostly about either level of abstraction being one level away from the "real SQL code" that's being executed. First off… LINQ to SQL and […]

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 4

This post covers LINQ to SQL and EF worry #4. That is: LINQ to SQL and EF will write code that gets too much or too little data at a time. Too much data in one query is a waste. Too little data is also bad because it means extra database round-trips. I really put […]

Feature synergy in SQL Server 2008

SQL Server 2005 introduced some new features that left “traditional” SQL folks puzzled. What’s the hidden use for SQLCLR? Or the XML data type? Or Service Broker? Besides being available to you as a programmer, these are all used internally.  I once had a conversation with a database person (not SQL Server) who, when asked […]

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 3

This post is part of a series, see parts 1 and 2. Sorry to be so long getting back to this series. This post covers LINQ to SQL and EF worry #3. That is: LINQ to SQL and EF will encourage "SELECT to middle-tier, then update/delete" rather than issuing SQL UPDATE/DELETE statements that are set-based. […]

Installing .NET 3.5 on a SQL Server 2005 machine…be careful

Recently I had the occasion to load .NET 3.5 on to a machine that had an existing instance of SQL Server 2005. .NET 3.5 does not work by versioning the “main .NET assemblies” (e.g. there is no “version 3.5” of mscorlib.dll, System.dll) but by replacing the 2.0 versions of them. You can observe this by […]

Ed’s Blogging about SQL Server Spatial

It’s good to see Ed Katibah open up a blog on SQL Server spatial data. Ed is sort of the “dad” of spatial data in SQL Server; he’s got a ton of experience and history in this space. Be sure to catch his posting on what’s new in spatial for CTP6.  I’ve been trying out the new functions […]

Configuring Filestream in CTP6 (its different)

I've been working with Filestream storage in SQL Server 2008 since it appeared in CTP5. The way I've always set it up is to use sp_filestream_configure. During the CTP6 setup process, I noticed you could now configure Filestream as part of setup. Because I knew how to use sp_filestream_configure I skipped that part of setup. […]

Lots of ORM and VS activity lately

Quite a bit of activity last week in the Visual Studio and ORM spaces. Visual Studio released a CTP of Visual Studio 2008 support for SQL Server 2008 CTP6. It allows you to connect to CTP6 with Server Explorer, which enables quite a few other features to work. check with Overview document on the download […]

LINQ and LINQ to XML to appear in SQL Server 2008 SQLCLR approved list

Sometime before SQL Server 2008 RTM, the libraries for LINQ and LINQ to XML (that is System.Core.dll and System.Xml.Linq.dll) but NOT LINQ to SQL will be added to the SQLCLR "approved" assembly list. The approved assembly list is the list of .NET Framework libraries that have been tested in a SQL Server-hosted environment, annotated their […]

Learning LINQ, LINQ to SQL and eSQL

I often hear those who identify themselves as "database programmers" (sometimes I think these may be folks who program only inside-database code), say: "I'd give learning LINQ and/or LINQ to SQL/Entities or Entity SQL (eSQL) a go in my sparse time, but I don't have a good book or a tool. I haven't read a book on […]

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 2

This post covers LINQ to SQL and EF worry #2. That is: LINQ to SQL and EF will encourage "SELECT * FROM…" style coding because you get back a nice, clean object this way instead of the less-useful anonymous type you receive by doing projection. This will also make most/all covering indexes useless. LINQ to […]

MHO: LINQ to SQL and Entity Framework: Panacea or evil incarnate? Part 1

I'd been meaning to write this entry for a while, about my opinion on LINQ to SQL and the ADO.NET Entity Framework and performance. I've just finished reading the 2-part blog series "Exploring the Performance of the ADO.NET Entity Framework", and was surprised (I guess) that the database performance aspect was barely mentioned. One way to […]

Using SQL Server 2008 spatial and the Virtual Earth map control – 2

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 […]

Using SQL Server 2008 spatial and the Virtual Earth map control – 1

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 […]

HTTP Endpoints to be deprecated in SQL Server 2008

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 Morning After…SQL Server 2008 Jumpstart

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 […]

Composable DML and Composable Queries

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 […]

Service Broker in SQL Server 2008 – When sent message and receive message priority are different

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 […]

Service Broker in SQL Server 2008 – Priority By Contract

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 […]

Another use for SQL Server 2008 row constructors

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 […]

More hints available to plan guides in SQL Server 2008

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

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

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) […]

SQL Server 2008 and ADO.NET Sync Services

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 […]

Large user-defined types and aggregates in SQL Server 2008

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 […]

SQL Server 2008: Ordered SQLCLR table-valued functions

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 […]

SQLCLR in SQL Server 2008: Multi-input user-defined aggregates

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 […]

What’s new in SQL Server 2008 CTP5? Start by RTFR and RTFBOL

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.  […]

Service Broker Priorities in SQL Server 2008 CTP5 – An overview

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 […]

SQL Server 2008 XML: Let there be ‘let’

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 […]

ITForum – Day 5 – Powershell and SQL Server 2008 – the reprise

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 […]

Astoria… I learned something today…

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 […]

Mondial database for SQL Server 2008

I like free, standard, sample databases. One that I’ve been looking at for quite a while is the Mondial database currently available at Institute for Informatics Georg-August-Universität Göttingen. This set of data (from an old CIA World factbook and other sources), is available not only as a relational database, but as XML, RDF, and even […]

SQL Server 2008: Inheriting From a System Data Type?

Ever since the hierarchyid data type was introduced in SQL Server 2008 CTP4, there’s been a fair amount of discussion about renaming some of the methods of the data type to make them a bit more intuitive. In addition, there’s been discussion about “missing” methods that don’t exist in the hierarchyid data type. Well… Because […]

Wither CTP5?

There’s been some rumors going ’round about the immenent release of SQL Server 2008 CTP5. OK, maybe I’ve been asking around too. The main reason for the rumors is that there was a published CTP timetable at one point and its getting to be about that time. The other reason is the plethora of sessions, […]

Powershell and SQL Server’s 2008 SMO – the reprise

In the last few years, I've done a few talks at various conferences on the integration of SMO (SQL Server Management Objects) and Powershell. My friend and co-author of the SQL Server 2005 books, Dan Sullivan, got me into using Powershell and SMO and has written quite a number of excellent blog entries and articles about it. […]

Sparse tables, sparse columns, and XML

I've been talking with folks for (it seems like) a long time about modeling sparse attributes in a relational database. Seems like I run into a new design where there's a need for sparse attributes every few weeks. If you remember the "hardware store" example (where each new sales item may have unqiue properties), that's just […]

Spatial Data, a niche or a tool for the masses?

I'll have to admit it, when I first saw that SQL Server 2008 was adding spatial data support, I thought of it as a niche. The province of geographers, cartographers, and maybe a few others. Complex, involving a lot of higher mathematics, each province having their own geographic encoding, and so on… And that level […]

Using the SQL Server 2008 UDTs on clients

A couple of people have been asking and, in preparing for my upcoming talk on spatial data on SQL Server 2008 at ITForum in Barcelona in a few weeks I got to ask about using the new system UDTs types on the client. In an upcoming CTP release there will be an MSI installer file specifically to install […]

Sync Services and SQL Server 2008 Change Tracking…a perfect match?

Just saw Aaron Bertrand's post from PASS on SQL Server 2008 Change Tracking vs Change Data Capture (change data capture (or CDC) is in the current CTP; change tracking is not). I'm not at PASS this week myself, but home while the house is being re-roofed. His post seems to confirm something I'd suspected all along. Change tracking […]

An example of using ODBC and SQL Server 2008 table-valued parameters

I'll be at TechEd and ITForum in Barcelona in November, doing sessions on SQL Server 2005 and SQL Server 2008. One of the TechEd sessions is called "To ODBC or to OLE DB?" and is a discussion of using ODBC and OLE DB is applications these days, whether one or the other is "better", if […]

More info on SQLCE 3.5 beta and LINQ and EDM support

I received mail today from the SQL Server Compact Edition folks at Microsoft on my blog post on using SQL Server CE 3.5 beta, Visual Studio Beta 2, LINQ, and EDM. The current plan is: 1. Visual Studio 2008 will not ship with LINQ to SQLCE designer support. SQLMetal works just fine with SQLCE, though, […]

SQL Server 2008 and .NET framework versions

So, people always ask… now that .NET Framework 3.0 installed on my system and there's going to be a new version of .NET that includes LINQ, what version of the framework will SQL Server load now, in SQL Server 2008? Does 2.0 still load? Or does it load 3.0 or 3.5? Theoretically, and I've written […]

SQL Server 2008 and Clients – the long story

Let's talk about clients and SQL Server 2008. First, a little history… Every new version of SQL Server seems to include a new feature that requires a change to the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS stands for tabular data stream, and is a propriatary protocol used by […]

SQLCLR and system functionality in SQL Server 2008 – part 3

So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup. I have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE/TIME-related data […]

SQLCLR and system functionality in SQL Server 2008 – part 2

I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly […]

SQLCLR and system functionality in SQL Server 2008 – part 1

I was listening to a replay of the webcast recording on the HierarchyID by Michael Wang (thanks, Michael) and as he mentioned the considerations for the CLR-based type with respect to DDL, I thought it would be interesting to go back and see how this type showed up in the various facilities that we have […]

Using LINQ to SQL and EDM with SQL Server Compact Edition 3.5

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "different" data sources. This post covers using SQLCE 3.5. The latest version of SQLCE 3.5 comes with Visual Studio 2008 Beta 2. Using SQLCE 3.5 data sources doesn't work with the built-in LINQ for SQL designer in Visual […]

Using LINQ to SQL and EDM with SQL Server 2008

I've been trying out LINQ for SQL and the Entity Data Model (EDM) latest betas with some "non-traditional" data sources. Namely SQL Server 2008 (CTP4) and SQL Server Compact Edition 3.5. I'll cover using SQL Server 2008 CTP in this blog entry, and using SQLCE 3.5 in the next one. Using SQL Server 2008 data […]

.NET Nullable Types as SQLCLR parameters in SQL Server 2008

Just found out about this one today. This SQLCLR function works right now in CTP4 of SQL Server 2008. public static Nullable<int> AddTwo(Nullable<int> x, Nullable<int> y) { return x+y; } create function dbo.addtwo (@x int, @y int) returns int as external name asmname.[Mynamespace.Class1].AddTwo; select dbo.addtwo(2, null); -> null Great! Turns out, I suggested this back in […]

XEvent: Some final followup questions (slight return…)

Last blog entry on SQL Server Extended Events for a while. But…a couple of questions came up since I wrote my first blog entry on SQL Server Extended Events.   What are the major advantages to SQL Server Extended Events?   Is this really using the Crimson event system? There's a couple of reasons that come […]

Finishing XEvent package items, syntax, and semantics (moon turn the tides…)

This won't be as long of an entry because I'm trying to finish describing the items that you can use in an XEvent session, that is, the items that exist in a package.   Events   Targets   Actions   Predicates   Maps   Types   Let's do types and maps. A type is simply […]

More about XEvent: actions and targets (still raining, still dreaming)

It's another rainy day in Portland in summer, so I thought I'd stay inside and write more about SQL Server 2008 Extended Events. I wanted to finish things off by talking about actions and predicates. Need to make a detour at targets, too. I noticed the BOL examples (my point is to try not to repeat things […]

More about SQL Server 2008 extended events: packages, events, and event sessions

It's summertime in Oregon and its been nice and warm out (75-85F) lately. But today its raining (or specifically, the skies are quite ominous right now), so it's time to write more about my latest favorite subject: SQL Server 2008 Extended Events. The SQL Server 2008 extended events introduce quite a bit of new terminology, but […]

Making the XEvent ETW target run

When starting out with XEvent support, I thought it would be good to start with the ETW target, although you can capture and catagorize events in buckets with the async bucketizer target, and pair related events (like obtain lock/release lock) with the pair matching target. Both VERY cool. But I just wanted a raw, vanilla trace, […]

XEvent in SQL Server 2008

When I'm doing problem solving, its always good to have too much information rather than too little. With this in mind, I was quite interested in looking at SQL Server Extended Events (XEvent support) in SQL Server 2008. You could always get diagnostic information in SQL Server through DBCC and SQL Trace/SQL Profiler. In SQL […]

New SQL Server 2008 Date/Time-related types and ADO.NET

I've been looking at the mapping of the new Katmai date/time data types to .NET types, both from the point of view of SQL Server ADO.NET clients and of SQLCLR procedures/functions/triggers. There are a couple of things that "interesting", if I'm not misunderstanding something obvious. There are no new System.Data.SqlTypes to correspond to the new […]

Katmai: Using Table-Valued Parameters with ADO.NET

I've been trying out table-valued parameters along with ADO.NET support in Orcas and came across an interesting dilemma. It centers around INSERTs using TVP against a table with an identity column. ADO.NET can use DataTable, DbDataReader or IList<SqlDataRecord> to represent a TVP parameter. Let's say that I want a TVP and a procedure for insert that […]

SQL Server 2008 July CTP – what’s new (and what’s gone)

I saw Dan Jones’ posting that the SQL Server 2008 July CTP (aka CTP4) was available on the Connect website. This one has some good things in it (like the new date/time datatypes as well as the hierarchyid data type) that should keep me busy for a while. There’s much more new stuff than that, but […]

MERGE, JOINS, and determinism

SQL Server 2008 will contain an ANSI SQL standard MERGE statement with extensions. Listening to the webcast last Friday, there's a fairly straightforward way to describe how this works. Let's go back to first principals. Relational databases support two ways to do UPDATE and DELETE; positioned updates and searched updates. In a positioned update, you […]

And the EAV winner is …. sparse columns

Many of you have already heard the "hardware store" story. What's the best way to model products in a hardware store, where new items arrive at the hardware store each day. Each item has a "short list" of similar properties (e.g. UPC, price) and a long list of dissimilar properties (e.g. paint has color, type, […]

ORDPATH, ORDPATH, everywhere

ORDPATH is a hierarchical labeling scheme used in the internal implementation of the XML data type in SQL Server 2005. It's meant to provide optimized representation of hierarchies, simplify insertion of nodes at arbitrary locations in a tree, and also provide document order. It's described in the academic whitepaper "ORDPATHs: Insert-Friendly XML Node Labels". In […]

SQL Server system data types implemented in .NET

SQL Server 2008 will contain, if my count is correct, 7 new data types. Note: none of these are in the current CTP. DATE – ANSI-compliant date data type TIME – ANSI-compliant time data type with variable precision DATETIMEOFFSET – timezone aware/preserved datetime DATETIME2 – like DATETIME, but with variable precision and large date range GEOMETRY […]

A supported Service Broker external activator?

Had to write about another thing that "caught my ear" at TechEd during a chalktalk by Rick Negrin about Service Broker usage patterns. Service Broker supports "internal activation", that is, associate a stored procedure that gets invoked when a queue has messages to process as well as "external activiation". When using external activation, an event […]

Table valued parameters in SQL Server 2008

The June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while. Imagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire […]

Grouping sets and TABLIX – made for each other?

SQL Server 2008 will include GROUPING SETS; a (very) short explanation is that these allow the equivalent of multiple GROUP BY clauses in a single SQL statement. The result is a UNION ALL of the resultsets. SQL Server 2008 also contains/allows standard syntax for ROLLUP and CUBE, which have been in SQL Server for a while. […]

SQL Server 2008 new features – the list

There was a fairly well-known Powerpoint slide that attempted to summarize the new SQL Server 2005 features in bullet points of a single slide. By the release, there were so many new features, the feature list had to be rendered in a 5-point font to fit. At TechEd 2007, Microsoft presented the new features of SQL […]

Katmai Sessions at TechEd

SQL Server “Katmai”, which some folks are already calling “SQL Server 2008” because of its scheduled release date, was officially annonuced yesterday. I’ve been following things and noticed, a while back, that there are a number of Katmai sessions scheduled at TechEd in Orlando, in June. Now that there is an official announcement, I guess I […]