Monthly Archives: November 2013

The MVC Architecture

As the name implies, there are three primary components of an MVC framework: one or more models, views, and controllers. These components define the response cycle of a user interaction with an application.

  •  Model: A domain-specific set of classes that provide an interface into the data, implement domain-specific business rules, and reflect application state. The model may or may not wrap a persistent data store such as SQL Server or other database server. Usually the model will consist of a data access layer or some kind of object-relational tool such as LINQ to SQL, Entity Framework, or NHibernate.
  • View: The application’s user interface, which serves to render the model in a form that the user can interact with. An application will typically have multiple views associated with a single model, each used for different purposes. In a web application, the view is generally defined by HTML and contains no code or logic, other than what is necessary to display the view to the user and allow the user to interact with the view.
  • Controller: Receives requests from the user and initiates a response, interacting with the model as necessary. The controller also handles the overall application flow. In a web application, the controller usually responds to HTTP GET or POST inputs, hands the request over to the model that implements the business rules, and then selects the view subsequently displayed to the user.

The user’s interaction with a web-based MVC application generally follows this pattern:

  1. The user interacts with the web page in some way, such as making selections in a form and clicking a submit button.
  2. The controller handles the input and routes the request to the appropriate action, which is usually a public method of the controller class.
  3. The controller notifies the model of the user’s action, usually resulting in a change to the model’s state.
  4. The controller then selects a view, which queries the model in order to generate a new web page. In essence, the controller tells the view to generate itself, based on the current model state.
  5. The user interface (the web page) awaits a new interaction from the user, and the cycle begins again.

Figure below shows how the various MVC application components interact with each other to handle a user request and generate a response.
mvcarchitectimg1The anatomy of an MVC request and response.

If you’re familiar with ASP.NET Web Forms development, notice that there are no page events involved in responding to a user request. This makes for a much cleaner, simpler, and more maintainable architecture for applications.

ldn-pledgerwoodThis post is an excerpt from the online courseware for our MVC 4.0: Views and Models course written by expert Phil Ledgerwood.

Phil Ledgerwood has been a software developer for fifteen years. He currently works primarily in .NET technologies producing custom software for organizations of all sizes. He has also done extensive training for those same organizations in both technical and business process topics.

Core Foundation Assemblies

Before you can write any managed code for SharePoint you must reference the assembly that contains the classes you need. Although there are many assemblies in a SharePoint installation, there are a few that you will use often. If you are using one of the SharePoint project templates in Visual Studio 2010, the appropriate references are usually preconfigured when you create the Visual Studio solution.

The most commonly used assemblies are in the SharePoint root within the ISAPI folder although there are many located in other places, especially in the global assembly cache. Some of the most commonly used assemblies are:

  • Microsoft.SharePoint: The core classes for SharePoint Foundation.
  • Microsoft.SharePoint.Client: The core classes for client applications using the managed client object model.
  • Microsoft.SharePoint.Linq: LINQ to SharePoint.
  • Microsoft.SharePoint.WorkflowActions: SharePoint specific workflow actions.

Core Classes

The classes that represent the elements common to all SharePoint sites are in the Microsoft.SharePoint assembly. Figure 15 shows a few of these classes. If you have taken the time to become familiar with SharePoint as a user, the purpose of most of these should be obvious to you at this point—SPSite is a site collection, SPWeb is a web, and so on.

corefoundationimg1 Some of the commonly used classes in Microsoft.SharePoint.dll.


SPContext is the only class shown in figure above that doesn’t represent an item in a SharePoint site. You use this extremely handy class when writing code that runs when SharePoint renders a page, for example in a Web Part or user control, to gain access to the current request. You can use SPContext . Current to get access to the current:

  •  Site
  •  Web
  •  List
  • ListItem
  •  More…

It is important to note that SPContext is always governed by the security context of the user that is requesting the page. You can get the current user programmatically via the CurrentUser property of the SPWeb class. For example:

SPUser currentUser = SPContext.Current.Web.CurrentUser;

Because SPContext.Current uses the security context of the current user your code will throw a security exception if it attempts to perform any operations that the user does not have permission to perform.

Common Conventions

As you saw in Figure above, it is easy to guess the names of most of the core classes—just add an SP to the front of the name. For example, it should come as no surprise that the class you use to work with an alert is SPAlert! Other common conventions include:

  • An item’s name is usually the Title property such as SPWeb.Title and SPList.Title.
  • Most collection indexers provide the following overloads for accessing collection items:
    • Ordinal index
    • Guid
    • Title (if applicable)
  • Most changes to property changes only persist when you call the object instance’s Update() method.

doug (frame 367 of smile clip)This post is an excerpt from the online courseware for our Microsoft SharePoint 2010 for Developers course written by expert Doug Ware.

Doug Ware is a SharePoint expert and an instructor for many of our SharePoint 2007 and SharePoint 2010 courses. A Microsoft MVP several times over, Doug is the leader of the Atlanta .NET User Group, one of the largest user groups in the Southeast U.S., and is a frequent speaker at code camps and other events. In addition to teaching and writing about SharePoint, Doug stays active as a consultant and has helped numerous organizations implement and customize SharePoint.

Types of Spatial Data

SQL Server includes two spatial data types—Geometry and Geography—that encapsulate the database’s support for spatial data. Each data type includes pretty much the same point, line, shape, and collection objects, as well as rich sets of methods and properties for working with these shapes. Figure below shows the hierarchy of available objects. The way this works is that you define a field or variable as either the Geometry or Geography data type, then assign to that field or variable data that describes the point, line, shape, or collection you want to store. The yellow/lighter objects shown in the figure are abstract objects, while the blue/darker objects are concrete objects that you can store in the spatial data type. The only difference between the Geometry and Geography object hierarchies is that in Geography there is a FullGlobe object that represents the complete surface of the earth. This is shown in Figure below in a lighter color and dashed line because it is not part of the Geometry object hierarchy.

The shapes defined by the various spatial objects are vector objects, which are collections of points, lines, curves, polygons, and compound curves. You’re likely to use the following spatial objects most often to store and manipulate within SQL Server:

  • Point: Identified by a pair of coordinates as an exact location as X and Y coordinates, but can also include Z (elevation) and M (measure). SQL Server doesn’t use the Z and M coordinates, but lets you store the data so that client applications can use them.
  • LineString: A path along a sequence of points. It is a one-dimensional shape: it has length but no area, even if multiple connected LineStrings look like a closed shape. The LineString is simple if it doesn’t cross itself, or a ring if the starting point is the same as the ending point.
  • CircularString: Similar to a LineString, but defines a curve as a portion of a circle instead of a straight line.
  • Polygon: A closed 2-dimensional shape; a ring. A polygon has both a length property and an area. It can have holes in its interior, the shapes of which are defined by other, smaller polygons. The area of a hole is excluded from the area of the outer polygon.

You can also create collections of these basic shapes, including a MultiPoint, MultiLineString, and MultiPolygon, each of which can contain only the specified shapes. If you need a collection of any type of shape, including the multi-shapes, you can use a GeomCollection. This makes it easy to work with groups of shapes as a single unit.

The Geography and Geometry data types are .NET classes implemented as system SQLCLR objects with several dozens of methods. Many of the methods, about two-thirds, have names that start with the prefix ST, such as STArea, STLength, STDistance, and STGeomFromText. These ST methods are implementations of Open Geophysical Consortium standard methods (the ST stands for spatiotemporal). These methods provide the most commonly used, basic features for working with spatial data.

Microsoft also added some custom extension methods to the objects to support features not part of the OGC standards. These method names don’t have the ST prefix, such as Parse, ToString, and Reduce.

The nice thing about the Geography and Geometry data types is that they share a lot of methods that work the same across the two types. The main difference is that with the Geography type you’ll use latitude and longitude coordinates but with the Geometry type you’ll generally use x and y coordinates.
ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

The HierarchyID Data Type

As the name suggests, relational databases are best at storing and retrieving relational data. That’s the kind of data that fits neatly into multiple tables, each consisting of rows and columns of relatively homogeneous data, all related through mechanisms such as foreign keys. Most of the material in this course covers relational data, since SQL Server is a relational database system.

But the reality is that sometimes you have chunks of data that don’t fit neatly into the relational model. One example of commonly used non-relational data is hierarchical data. This is data that defines some kind of hierarchy, such as an organizational chart or part subassemblies to build a complex object like an automobile. If the structure is rigidly set with a limited number of levels, you can do a decent job of representing the hierarchy using a table for each level, or other techniques. But it can be very difficult to represent the hierarchy if the structure needs to be flexible.

You can always store hierarchical data outside of SQL Server, but over the years, people have come up with various ways of adapting hierarchical data to fit the relational model. Some of these include:

  •  Table self-joins: In a table self-join, you use a single table to contain the hierarchical data. The table has a foreign key to itself—thus the name “self-join”—to reference another row in the table. A good example of this is the Northwind Employees table, which has a ReportsTo foreign key field to identify the person to whom an employee reports. You can use that field to construct the Northwind sales organizational chart.
  • XML: XML data is inherently hierarchical, consisting of elements that entirely contain one or more child elements in a structured hierarchy. SQL Server 2005 introduced XML as a native data type, and Microsoft integrated it well with relational data. XML is handy because you have a great deal of flexibility in how you define the hierarchy. But you have to learn specialized methods to work with the data, and searching and sorting on the data can be problematic.
  • Roll your own: SQL Server 2005 also introduced the capability to create your own custom data types using the .NET Common Language Runtime (CLR). Many people took advantage of this feature to create their own hierarchical data type, with all the features to support their needs. SQLCLR types support properties and methods, so the type could implement behaviors as well as custom data structures to support hierarchical data. Such custom types were far from trivial to implement, and it took a lot of work to get it right.

Because developers and administrators frequently need to store and work with hierarchical data in SQL Server, Microsoft introduced the HierarchyID data type in SQL Server 2008. This is a compact object you can use to manage hierarchical data using materialized paths. It is compact in that it uses very little storage, and it materializes paths and manages the hierarchy for you. It is a System CLR type, which means that Microsoft implements it via .NET code.

Because it is a system type, you don’t have to enable custom SQLCLR code in a database before using the HierarchyID data type, as you must for your own custom SQLCLR code.

You can use the HierarchyID type to represent organization charts, map a file directory system on your hard drive, create an assembly part chart with subassemblies, represent project tasks, and for many other uses. The type has rich support with methods that provide many ways to maintain the hierarchy, such as making arbitrary insertions and deletions, as well as relocating sections of the hierarchy to other places.

There is no magic to the HierarchyID. Although SQL Server gives you everything you need to maintain the integrity of the hierarchy, it doesn’t automatically enforce hierarchical relationships. You have to do that in your code, using the tools that are part of the data type. The HierarchyID type also does not guarantee uniqueness, but you can enforce uniqueness in other ways, such as with a unique constraint on a HierarchyID field in a table.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Project Templates

The first thing you do when building a new solution in Visual Studio 2010 is to select a project template. The template you select determines the tools that are available and how Visual Studio behaves when you build the project. Visual Studio comes with project templates for a variety of Windows and Web application types. Among these are a number of templates for building SharePoint applications.

Several of the project templates concern development of specific types of features. Each of these is a starting point that allows you to add any type of feature, but that starts with an element manifest that corresponds with the project template’s name. The feature oriented project templates are:

  •  List Definition
  •  Content Type
  •  Module
  •  Event Receiver

Workflows are an important component of most SharePoint environments. There are five workflow specific project templates for SharePoint, two of these support workflow development for SharePoint 2007. The SharePoint workflow templates are:

  •  Sequential Workflow
  •  State Machine Workflow
  • Import Reusable Workflow
  •  SharePoint 2007 Sequential Workflow
  •  SharePoint 2007 State Machine Workflow

The SharePoint 2007 workflow templates are the only direct support Visual Studio offers developers writing code for SharePoint 2007. All of the other project templates support only SharePoint 2010.

The remaining SharePoint project templates are:

  • Empty SharePoint Project: An empty project with SharePoint tools.
  • Visual Web Part: A user control and a Web Part wrapper with a feature to add the Web Part to the deployment target.
  • Business Data Connectivity Model: A project with tools for building Business Connectivity Services applications.
  • Site Definition: A project pre-populated with files for a custom site definition.
  • Import SharePoint Solution Package: A project with imported contents from a WSP usually generated via the Save Site as Template functionality of a SharePoint site.

Most large solutions start with the Empty SharePoint Project template.

doug (frame 367 of smile clip)This post is an excerpt from the online courseware for our Microsoft SharePoint 2010 for Developers course written by expert Doug Ware.

Doug Ware is a SharePoint expert and an instructor for many of our SharePoint 2007 and SharePoint 2010 courses. A Microsoft MVP several times over, Doug is the leader of the Atlanta .NET User Group, one of the largest user groups in the Southeast U.S., and is a frequent speaker at code camps and other events. In addition to teaching and writing about SharePoint, Doug stays active as a consultant and has helped numerous organizations implement and customize SharePoint.

Overview of the Entity Framework

Suppose you are designing a database application from scratch. The first thing you do is design the data. What are the things or entities you need to keep track of and what are their relationships? If you are building an order entry system, your list of entities might include customers, orders, products, suppliers, etc.

You might then determine that customers place orders and customers can place many orders. Suppliers supply products and suppliers can supply many products. Orders consist of one or more products and each product can be included in one or many orders. You have now created a conceptual model of your data using an entity-relationship model.

The next step in the process is to define how you want to store this information in your data source. What tables will you create and what are their relationships? You might decide to have tables for Customers, Orders, Products, Suppliers, etc. Since orders contain one or more products, you might decide to have an Order Details table. Each of these tables, of course, has a primary key.

You then define the foreign key constraints that relate the tables. The Customers table is related to the Orders table based on the CustomerID column. The Orders table is related to the Order Details table based on the OrderID column. You now have a logical model of your data.

The final step is to create the database, the tables, and the constraints that relate the tables. You also create stored procedures in this step. You now have a physical model of your data.

The database administrator (DBA) owns and manages the physical model. The
DBA creates and maintains the database and defines the stored procedures.

The developer owns and manages the application code and writes SQL statements to retrieve and update data. The developer typically works from the logical model because he or she needs to write SQL statements that reference particular columns in particular tables. To display products and their suppliers, the developer needs to construct a SQL statement that joins the Suppliers and Products tables. Unless the DBA creates stored procedures for every data operation, the developer needs to know how the data is stored.

What about the conceptual model? You essentially create it so that you can create the logical and physical models. The DBA works from the physical model and the developer codes to the logical model—no one uses the conceptual model.

However, users think in terms of the conceptual model. When users open the Customers form, they want to see customer information. Whether that resides in one, two, or three database tables is irrelevant.

Ideally, the developer would write code based on the conceptual model. If you want to work with customer information, you would create an instance of the Customer class and that class contains customer information. Whether that information resides in one, two, or three database tables should be irrelevant.

If you are using ADO.NET the number of tables is not irrelevant because you write the SQL statements to retrieve customer information. If that information resides in three tables, you need to either write a three-table join or execute three SQL statements.

If you are using LINQ to SQL, the number of tables is not irrelevant either. To use LINQ to SQL, you map the structure of a SQL Server database to an object model. Each table in the database maps to a class. So if customer information is in three tables, you need to create three Customer classes and your queries need to account for that.

The ADO.NET Entity Framework enables you to write code against the conceptual model rather than the logical model. To work with customers, you work with the Customer class. That class contains all of the customer information, regardless of how that information is stored in the database.

NOTE As of Visual Studio 2008 Service Pack 1 the ADO.NET Entity
Framework is included in Visual Studio.

The Entity Data Model is the key to this capability. The Entity Data Model is your object model. It consists of entities, associations, and functions. Entities are your domain objects. These are the things you need to keep track of—for example customers, orders, products, etc. Associations are the relationships between entities. Functions are stored procedures or user-defined functions that you can use to retrieve data.

The Entity Data Model is an abstract conceptual model and is not tightly bound to the data source or to the application. In LINQ to SQL, each entity in your object model maps to a table in the database. In the Entity Data Model, you have a variety of possible mappings, such as:

  • An entity can map to one table in a database.
  • An entity can map to more than one table in a database.
  • An entity can map to a table in a SQL Server database and another table in an Oracle database.
  • An entity can map to the results of calling a Web service or a Windows Communication Foundation service.
  • An entity can map to a table in a database and to the results of calling a Web service.

Customers have information and that information can come from a variety of sources. The Entity Data Model abstracts the physical storage of the information so that all you have to do is create an instance of the Customer class and know that you have access to all the information you need.

The Entity Data Model consists of three components:

  • Conceptual layer: Defines the conceptual model. What are the entities in the model and how do they relate? This information is stored as XML via the Conceptual Schema Definition Language (CSDL).
  • Storage layer: Defines the storage model. How is the data stored in the data source(s)? This information is stored as XML via the Storage Schema Definition Language (SSDL).
  • Mapping layer: Defines the mapping between the conceptual and storage models. For each entity, where is the information stored? This information is stored as XML via the Mapping Specification Language (MSL).

In addition to the Entity Data Model, the Entity Framework includes two other key components: the EntityClient Data Provider and Object Services.

  • EntityClient Data Provider is responsible for communicating with the underlying data sources. It does this by calling the ADO.NET data providers for each data source. The EntityClient Data Provider translates your conceptual model queries into data source-specific queries and returns a data reader that contains the results of these queries.
  • Object Services is responsible for populating entity instances with the results of queries and for tracking changes to data. It enables queries against the conceptual model via either Entity SQL or LINQ to Entities. Entity SQL is a querying language that resembles Transact SQL, but it is outside the scope of this course. LINQ to Entities enables queries that use the familiar LINQ syntax.

Types of Features

Most of the elements in a SharePoint installation are either defined using features or leverage features in some way. Accordingly, there are feature types that define most of the site elements with which you are already familiar.

Feature/Site Template Association

Feature/site template associations allow you to associate new features and functionality with existing site definitions or site templates so that, when you provision a site, SharePoint automatically activates the features. This technique is known as feature stapling.

Feature stapling makes versioning much easier because it allows you to associate new features with preexisting site definitions. Stapled features also provide an excellent way to handle situations that require activation code, because they are applied after their associated site is fully provisioned.

Two features are involved in feature stapling: the feature that contains the functionality you want to add to an existing site template, and the feature/site template association feature. Feature/site template associations have Farm or WebApplication scope.

You can globally associate a feature with every site definition on a farm. Associate a feature with the global site definition by specifying GLOBAL for the TemplateName attribute. Whenever you create a site, the global site definition is applied, so your feature is also applied.


Use a workflow feature to attach a custom workflow to SharePoint. The feature includes the code for the workflow and the necessary forms for each step.

Document Converter

Document converters automatically convert a document library item from one format to another. WSS does not offer document converters, but MOSS includes several (for example, to convert documents to HTML) and third-party converters to support conversion to PDF and other formats.

Site Columns and Content Types

In features, you define site columns with a Field element. All of the core site columns are defined in a feature named fields that is activated by the global site definition. You can use the fields feature as a reference when creating your own site columns or content types.

Just as the global site definition activates the fields feature for site columns, the ctypes feature is also activated globally for content types. You can create a new content type with a feature using the ContentType feature element.

Sometimes, you will need to associate a content type with an existing list or alist that another feature created as part of a larger solution. Use the ContentTypeBinding feature type for that task.


You can define a new type of list—including content type associations, views, and custom forms—with a ListTemplate feature. Each of the standard lists and libraries is defined by a ListTemplate feature element. For example, the DocumentLibrary feature defines the Document Library template.

You can create new list instances by using ListInstance to specify the target URL and the list template that defines the list.

SharePoint exposes a rich event model for lists that allows custom code to run whenever something happens to a list or an item. Use the Receivers feature type to associate the assembly and a class that contains a handler for a specific event type to all lists defined by a particular list template.


The Module feature allows you to deploy files to a site. You can use it to add files to a library or simply place the files onto the site in a specified folder. In the latter case, the module creates the folder if necessary.


You might expect the Control feature to install controls, but it doesn’t. Many of the core site pages use a Web control named DelegateControl. DelegateControl is a placeholder; when the page renders, it loads a concrete control instance that is based on an ID and a sequence number. DelegateControl looks for the lowest sequence number of a given ID and loads
the associated control. Individual Control features define the IDs and sequence numbers.

This feature allows site owners to override existing controls with their own specialized instances on existing sites, without altering the sites or their definitions. A common use of this technique is to replace the basic search control, which is defined by a DelegateControl on the master page with an ID of SmallSearchInputBox. You can replace this control when you brand a site by deploying your own control or Web Part and then creating a Control feature with the SmallSearchInputBox identifier and a low sequence number.

Custom Actions

Just as Control allows you to insert controls in defined placeholders without changing the target site or pages, the Custom Actions feature allows you to insert or hide links on pages and menus. CustomAction and HideCustomAction both require that you specify a location, and they affect the resulting page without directly altering its definition.

Use Custom Actions to show or hide different elements of the site, including the contents of many drop-down menus such as site actions, personal settings, list settings, and item edit menus. You can also use Custom Actions to show and hide links on the Site Settings page and on the Create Libraries, Lists, And Sites page. Custom actions enable greater control of additional navigation within the site than you can accomplish with the top-level menus.
You can use custom actions to display an option on the edit item menu of a list or document library’s items. For example, you could display a link to a function that merges a contact item’s data with a document template to create a form letter.

New Feature Elements in 2010

SharePoint 2010 adds a number of feature elements to the schema.

Property Bag

The PropertyBag element allows you to add properties to items in a site. The Save Site as Template functionality uses PropertyBag to create solutions that can duplicate the source site with high fidelity.

When using a PropertyBag to target a file, the use is identical to the Properties child element of the File element within a module. The difference is that the PropertyBag is independent of the need to deploy the file. In other words, you can apply properties to items that already exist.

Web Template

The WebTemplate element provides an alternative to traditional site templates. In most cases this element is now the preferred method for creating new templates for custom sites.

Workflow Association

The WorkflowAssociation allows declarative association of a workflow template to a list, content type, or site. Previously you had to use managed code to create a workflow association. Managed code is still a fully supported option.


The feature schema definition, wss.xsd, includes four other workflow elements that are currently undocumented and unused by any of the built-in features that come with SharePoint 2010. They are:

  • Workflow Actions
  • Web Part Adder Extension
  •  User Migrator
  • Group Migrator

Doug (SPC  towards the end of the smile clip)This post is an excerpt from the online courseware for our Microsoft SharePoint 2010 for Developers course written by expert Doug Ware.

Doug Ware is a SharePoint expert and an instructor for many of our SharePoint 2007 and SharePoint 2010 courses. A Microsoft MVP several times over, Doug is the leader of the Atlanta .NET User Group, one of the largest user groups in the Southeast U.S., and is a frequent speaker at code camps and other events. In addition to teaching and writing about SharePoint, Doug stays active as a consultant and has helped numerous organizations implement and customize SharePoint.

The Stages of Query Compilation

Compiling a query involves three stages: parsing, algebrization, and optimization. The process transforms the logical T-SQL code you write into the physical operators that define the steps that SQL Server will perform to execute the query and return results. Below shows a very high-level view of the entire process.

thestagesofQuery1 The overall query compilation process in SQL Server.

When SQL Server receives a batch of T-SQL statements from a client application (which includes Management Studio), it first checks to see if a plan exists in the plan cache for the query. If the query was recently executed by this or another session, then the query doesn’t need compilation. The plan is passed to the execution engine, and the process is done.

If no match is found in the plan cache, SQL Server parses the query to check the correct T-SQL syntax, including the use of valid SQL identifiers for object names—although not yet to verify that all referenced objects exist—as well as the spelling and use of keywords.

Then for each statement in the batch, the query processor sees if there is an existing plan guide and if it is in the cache. A plan guide is an object that you can define to specify a query plan for a query. If there is a match that is in the plan cache, the processor uses the plan guide for that statement and sends it off for execution. If there is no match, the query is parameterized, which essentially creates a version of the query with separate parameters, and it once again checks to see if the parameterized query is in the plan cache. If there is a
version in the plan cache, it is sent to the execution engine.

Otherwise, if there is no matching plan guide or parameterized query in the plan cache, the query processor has to go through the process of creating an execution plan for the query. The first step is algebrization, also called normalization. This step creates a logical tree, sometimes called a parse tree, that represents the logical steps needed to execute the query. A primary task of algebrization is binding, which checks whether referenced tables and columns exist, loads metadata about those objects, and adds information about implicit
conversions needed. The next step is optimization, which, as its name implies, performs any refinements that can make the execution plan more efficient. Then the plan is added to the plan cache and sent to the execution engine.

TIP: Not all execution plans are cached. For example, you can define a stored
procedure with the WITH RECOMPILE option, which causes SQL Server to
generate a new execution plan every time that code executes. In that case,
SQL Server doesn’t bother caching the plan since there is no point.

Caching execution plans is one of the major ways that SQL Server dramatically increases the efficiency of query execution. Creating an execution plan can be a time-consuming operation, so if most queries can use a cached plan the server will be able to process far more queries.

Even if an execution plan for a query is in the plan cache, SQL Server may decide to recompile the query anyway and throw out the old plan. This process is called recompilation, and it occurs for two broad reasons, correctness and optimality.

  • Correctness: The query processor might determine that an existing cached plan could return incorrect results. In that case, it recompiles the statement or batch. There are numerous reasons why a plan might return incorrect results, including schema changes; added, changed, or dropped indexes; statistics updates; use of WITH RECOMPILE; changed environment settings (usually SET statements); or an explicit call to the sp_recompile system stored procedure.
  • Optimality: SQL Server tracks data distributions with statistics, including information about the number of rows in a table as well as changes to the data in individual columns. The query processor uses a threshold value to decide whether it should recompile a query. Essentially, if the data has changed too much, it could affect the choice of the optimal execution plan and so SQL Server recompiles it.

The benefit of recompilation, even though it is a hit against performance, is that a query with an execution plan that is incorrect or suboptimal is likely to perform far worse than with a correct, optimal plan. It could even return incorrect results in extreme cases. So the performance hit of recompilation is usually well worth the performance benefits.

NOTE: In SQL Server 2005 and later, individual statements can be
recompiled instead of the entire batch, an enormous performance
boost for long batches or complicated stored procedures and other
code modules that can take some time to compile.

It is important to understand that the query processor is not looking for the absolute best execution plan possible. A complex query might have many thousands of execution plans that return correct results, and it might take hours to find the best plan that saves mere milliseconds off query execution time. One of the tasks that the optimizer does is to look for a trivial plan, which has a known and constant cost, involves no parameters, and only uses system functions. An example of a query with a trivial plan is:

Because this query returns all rows and all columns from a relatively small table, no amount of analysis is likely to find one method of scanning the table that is substantially better, in terms of performance, than another. There just aren’t that many ways to perform this query. If the optimizer finds a trivial plan, it returns that execution plan and does no further work.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer: LocalDB Databases course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.