SQL Server Reference Guide

Database professionals can be divided into a few roles, such as administration, design and development, Business Intelligence and so on. Even within these disciplines, such as administration, there are professionals that deal more with hardware than software, those that focus on replication and network, security and so on. And of course most of our day-to-day work doesn't fit neatly within a single area anyway — we jump from writing a Stored Procedure to setting up a SQL Agent Job for maintenance to adding a user to setting up a database design for a project. This diversity is one of the most challenging and most enjoyable aspects of our job.

But there can be conflict on a team when methodologies collide. Hardware specialists deal in electrical and mechanical terms, developers think in Classes and Objects (more on that in a moment), and Architects deal in design. Since the data professional deals with all of these, we often need to switch our thinking patterns to fit the audience we're talking with.

This article does not cover the process of creating an Object-Role Modeling (ORM) document, since as a data professional that's not always the best tool for you to use to create a database. Instead, I'll describe what an ORM model is, how other technical professionals use it, and what you need to be aware of as a data professional.

What It Is

Object-Role Modeling is the process of taking a programming Object (more on this in a moment) and describing its data and actions between each other. It's a bit like the Entity Relationship Diagram (ERD) process I've explained elsewhere, but with business logic and actions thrown in. That's a broad definition, but it works for now.

I'll explore ORM in more detail in other articles as well, but at a high level, the ORM model looks similar to an ERD, but in fact it describes more than just data, and in fact doesn't always include details that a Data Professional needs such as data types, cardinality and so on. But it is something that lends itself more to the Classes and Objects I'll describe in a moment.

Here's an example of an ORM Document — you can open this up to have as a discussion point throughout this article: http://upload.wikimedia.org/wikipedia/commons/b/b3/Object-Role_Model_Overview.jpg

The immediate distinction you'll see in that diagram from an ERD diagram is that not only is there less data detail, but that programming constructs are included within the relationships between entities and their data. For the Data Professional, it's easy to get a little confused with this diagram, since it seems to mix these two concepts together a great deal. And that's where I need to do a little more explanation between how a developer sees a system and how the Data Professional sees one.

Be aware that ORM is not only a documentation method — it's a development paradigm as well. An ORM tool does create a document, but it also can create the coding objects a developer needs.

In the .NET environment, two ORM tools are built right in to the Visual Studio development tool — Language Integrated Query (LINQ) and the newer Entity Framework (EF) modeling tools. Developers draw what they want in those tools, and Visual Studio writes the code the developer wants from the diagram. This is a completely normal task for the developer, hence the reason I'm covering this for the data professional.

A Brief and Most Assuredly Incomplete Description of a Class

Since an ORM tool normally creates "Classes" for the developer, I'll briefly explain what a programming Class is, although this isn't an article on development — but the idea of a Class is essential to why ORM is important. If you're already a developer, feel free to post comments on this article to add detail or refer readers to a good description on Object-Oriented Programming Books and articles.

Data Professionals mostly deal in "entities", which relate back to a physical or conceptual object somewhere in the real world. For instance, we think of a Purchase Order as an entity. But because of the relationship of parts of the entity — such as a particular line-item on a Purchase Order — we break the entity apart into multiple "parent" entities and "children" entities that belong to them. We then relate those back to each other using Key values and then join them together using the SQL language.

Developers also think this way, at least in some respects. Developers use the term "Class" to refer not to an actual Purchase Order, but its description — what we would consider the structure of a table. For each Row in our table, which might make up the data in a Purchase Order, the developer thinks of an "Object" or "Instantiation" of a Class.

At the risk of being a little technically inaccurate, you can think of a Class like a template used for a word processing document. It doesn't contain any data; it's just used to make the structure for data that will come later. When the developer writes code to "call" the Class to make a new Purchase Order, the Purchase Order is now called an "Object".

A class holds this data in something called a "Property", which you can think of a little like a column in a database table. But this is where the Class is separate in conceptual terms than a column — a class can contain actions performed on it, called "Methods".

So for instance you can create a Purchase Order Class and then make a Method called "Create" and perhaps another called "Destroy". Programmers can create a Purchase Order Object simply by using the appropriate Method, and the data structure is contained within the Class. So you can see that what we accomplish in the T-SQL Language combined with a SQL Server engine is done all in one place, in code.

There's an important distinction here that will help you understand LINQ or any program-oriented query language. Classes don't necessarily have built-in relationships to each other, except in one interesting way. A Class can "inherit" behavior from another one. For instance, perhaps you have a corporate standard for all Purchase Orders. Other departments might have similar requirements, but also have additional information or actions they want to perform on a Purchase Order.

In this case, the developer could create a "Purchase Order" Class and then another class called "Department PO Class". The second class can derive the data descriptions (Properties) and actions (Methods) from the first class.

There are far better descriptions (and more technically complete and therefore more accurate) available the book Sams Teach Yourself Object Oriented Programming in 21 Days

Why This Makes a Difference to the Data Professional

Working with an RDBMS often leads the developers to consider the data professional only as a caretaker of data, and not necessarily part of the design process for an application — which they often view as the center of the project. The data professional, on the other hand, often views the program as a way of manipulating data, which is the center of the project. This causes miscommunication, disconnected design problems, and disjointed systems of code and data.

This confusion is logical, since the developer may start out in an ORM tool such as those found in Visual Studio, which handles the Class design work. For data, the Classes are tied back to a data layer somewhere, and that's often where SQL Server comes in.

In Visual Studio, another development tool, or even just in code, the Classes are mapped back to some sort of a data layer. An RDBMS like SQL Server might be used, but in many cases other data engines might be used, or even multiple engines, even just memory. So for the developer, having to learn Transact-SQL (or any version of the SQL language) is only one of the things they need to care about.

Remember, the developer is thinking in terms of Classes and Objects, with their associated Properties and Methods. They don't often think in set-based logic, and that's where the rub comes in between the data teams and the programming teams. Developers may have to deal with multiple data sources, and many ORM constructs provide a single language for them to work with.

In the .NET environment, two such languages work with the ORM tools in Visual Studio quite well — one called Language Integrated Query (LINQ) and the other called the Entity Framework (EF). LINQ is smaller, fast, and simple to understand. EF is more complete, and is actively being added to. As of this writing, both are still supported by Microsoft.

There are some real advantages for the developer to use ORM languages:

• A single query language structure to learn and use
• Design-time development
• The languages and frameworks are extensible
• Can use key-less joins
• Can combine multiple data sets from multiple sources in a single query
• Data Layer is almost immaterial from their standpoint

If you want to learn more about Entity Framework, there's a good reference here. For LINQ, there's a good work here, and I also use a program called LINQpad. LINQpad has a free version, and right within the tool there are dozens of lessons and tutorials. You can learn to use LINQ with the Northwind sample database and LINQpad.

So your developers will use this methodology, almost assuredly. You need to understand that when a developer gets a specification, they may not consult you before designing the data structures, and then you'll live with the results.

So what is a data professional to do? Well, I've explained why it's attractive to developers — so you should be aware that they will be comfortable with that development paradigm. You should also be aware of the dangers in ORM as it applies to an RDBMS, and work with the developers to mitigate these issues.

Considerations

The primary issue with many ORM implementations is that they may rely too heavily on Dynamic SQL for their database access. Dynamic SQL means that T-SQL statements are sent to SQL Server rather than calling Server-side code like Stored Procedures and Functions. ORM programming like LINQ and EF can in fact use Stored Procedures, Functions and so on; it's just more difficult to do so.

It's not that the T-SQL sent from the tools is that bad. Many times it's well-formed, and you can use the LINQpad tool I mentioned to see what would be sent. The problem is that in addition to not using Stored Procedures and other server-side code, isolation levels and other engine-hinting technologies aren't used.

Another important issue is that in some cases I've seen ORM queries operate by opening a row, working on it and then closing the row — for each row, one at a time! This can completely destroy performance, and since everything is operating as designed, it's hard to track down. It just looks like a lot of activity on the server to the DBA.

There are other articles that detail these specific problems. I'll call your attention to an excellent article by Erland Sommerskog, a friend and SQL Server MVP on Dynamic SQL, and another (although older) article on LINQ in specific. Note that in both cases the ORM tools are updated to address at least part of these issues, but they will give you more detail to know what you are looking for.

There are other issues as well. Schemas within the database need to be mapped to Class Namespaces — something that takes time and effort on the developer's part.

Because of the row-by-row issue, some large Insert/Delete and Update operations can really suffer. Temporary tables, other special SQL Server features are not always recognized, understood or supported in ORM tools and languages.

Above all, try and work with the development teams to be brought into the design process as early as possible. This will ensure the issues are dealt with before they become a problem.