There are a few ways you could grant a user execution permission on stored procedures, through assigning permissions on different object hierarchies (objects/schema/database) you can control the level of permissions to ensure optimum security and flexibility.

This post will go through how to grant SQL Server execution permissions on individual stored procedure objects within a database, how to grant execution permission on all object within a schema (including functions), and finally how to grant execution permission across the whole database.

Read more →

SQL Server queries can be returned as a valid XML as well as a rowset (by default), there are multiple ways you could control the format the end result XML is returned in, giving SQL developers flexibility that can meet most of the demand of an application consuming this XML data. XML support has been natively built into SQL Server since the 2005 release.

In this post I will be briefly exploring the different type of XML output modes supported by SQL Server through the FOR XML statement.

Read more →

There are many ways to obtain the execution plan of a SQL Server query, each of which serves a particular purpose, for example if you can run a query easily, then the best way to get an execution plan is to simply enable the execution plan (either through SSMS or SETting the correct session option), but if the query takes a long time to run, or you simply cannot run the query any other reason, then you might decide to profile the query when it is naturally running (perhaps on the live environment) through SQL Profiler.

In this post I will go through a simple SQL query to extract the execution plan from SQL’s own plan cache. There are many reasons why this might not be an accurate way to extract a query, but it is certainly helpful in situation were you have good understanding of your DB setup.

Read more →

In this post I will show you a quick example of how to write an on-the-fly table in SQL Server.

Read more →

Today I ran into the following SQL Server error while trying to run some Filtered Indexes:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

In this post I go through the possible reasons for this Query Processor error, and how to solve each of those.

Read more →

A simple example on SQL Server’s RECOMPILE query option.

Read more →

A quick example that shows how to force SQL Server to use a specified index when generating the Query Plan

Read more →

There is nothing more annoying than having to go through SQL Server code line by line trying to get the formatting of the each statement correct, in order to be able to reach the code. SQL Server (or any SQL Engine) has a multitude of plugins and online SQL code formatting tools. This short post goes through one online tool to format SQL Server code.

Read more →