My Adventures in Coding

April 13, 2016

SQL Server – Get Sizes of All Tables and Indexes in a Database

Filed under: SQL,SQL Server — Brian @ 9:51 pm
Tags: , , , ,

Even though there are great tools in SQL Server Management Studio that give you a wealth of information about all of your tables and indexes, I still find it handy to have a script I can use to quickly check the current state of all tables and indexes in a database.

Size of each Table (Including Indexes)

This query gives you a total size for each table in KB including all of the indexes on that table. The query shows the table name, row count, total space, and total space used by the table and its indexes.

    t.[Name] AS TableName,
    p.[rows] AS [RowCount],
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.[Name], p.[Rows]
ORDER BY t.[Name]

Size of each Index

This query shows the total size in KB of each index in the database. The query shows the name of each index, which table the index is on, and the total size of the index.

    i.[name] AS IndexName,
    t.[name] AS TableName,
    SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] 
	AND s.[index_id] = i.[index_id]
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
GROUP BY i.[name], t.[name]
ORDER BY i.[name], t.[name]

I hope you find these queries useful!

January 5, 2016

SQL Server – How to write an Upsert using MERGE

Filed under: SQL,SQL Server — Brian @ 8:35 pm
Tags: , , , ,

Normally, when you want an application to INSERT a row to a table if it does not exist or UPDATE it if it does exist, your application must first do a SELECT to check if the row exists, which is the standard SELECT-UPDATE-INSERT pattern. The down side to this pattern is it requires two database round trips instead of one.

Now, over the years I have worked with document stores such as MongoDB and really enjoyed the freedom to be able to make a single call to the database and be able to ADD/REPLACE a document, without having to check if it exists.

Fortunately in SQL Server 2008, the MERGE function was introduced.

MERGE allows you to make only a single database round trip when you want to INSERT a row if it does not exist, or UPDATE it if it does. The following is a simple example showing how to use the MERGE statement.

Quick Start

So if you just want a quick example to get you started then here you go. In this example the table “ClientData” is the one being updated.

  • MERGE – specifies the table we will be inserting a row into or updating
  • USING – defines the condition we will be using to check if the row exists or not
  • WHEN MATCHED THEN – SQL statement to run when the row exists
  • WHEN NOT MATCHED – SQL statement to run when the row does not exist
MERGE dbo.ClientData AS [Target] 
USING (SELECT 12345 AS clientId, 'Some' AS data) AS [Source] ON [Target].clientId = [Source].clientId 
WHEN MATCHED THEN UPDATE SET [Target].data = [Source].data, [Target].updatedDateUtc = GetUtcDate() 
WHEN NOT MATCHED THEN INSERT (clientId, data) VALUES ([Source].clientId, [Source].data);

How it Works

First lets create a table to use for our test of the Merge statement:

CREATE TABLE dbo.ClientData(
	ClientId [bigint] NOT NULL,
	Data [varchar](20) NOT NULL,
	UpdatedDateUtc [datetime] NOT NULL DEFAULT (getutcdate()),
	ClientId ASC

You can verify the table has been created and see that it is empty:

SELECT * FROM dbo.ClientData

Now, run the following Merge statement for the first time, where no matching row in the table:

MERGE dbo.ClientData AS [Target]
USING (SELECT 12345 AS clientId) AS [Source] 
ON [Target].clientId = [Source].clientId
WHEN MATCHED THEN  UPDATE SET [Target].data = 'Update', [Target].updatedDateUtc = GetUtcDate()
WHEN NOT MATCHED THEN  INSERT (clientId, data) VALUES ([Source].clientId, 'Insert');

As you can see, the INSERT statement was executed:

SELECT * FROM dbo.ClientData

Now let’s run the exact same merge statement a second time and see what happens:

MERGE dbo.ClientData AS [Target]
USING (SELECT 12345 AS clientId) AS [Source] 
ON [Target].clientId = [Source].clientId
WHEN MATCHED THEN  UPDATE SET [Target].data = 'Update', [Target].updatedDateUtc = GetUtcDate()
WHEN NOT MATCHED THEN  INSERT (clientId, data) VALUES ([Source].clientId, 'Insert');

Now, you can see the the UPDATE statement was executed since the “data” field has been updated to the text “Update”:

select * from dbo.ClientData

If you are curious about the performance difference between MERGE and SELECT-INSERT-UPDATE here is a performance comparison.

August 14, 2015

Java – Automate database schema updates with Flyway

Filed under: Java — Brian @ 1:44 pm
Tags: , , , , , ,

I am currently working on a Java 8 project which is a REST API deployed as a WAR file to Jetty. Our deploy process is very simple, our deploy pipeline just copies the WAR file into the Jetty directory in each environment then verifies the app is up and running with the correct version and runs some integration tests.

We wanted to be able to apply database migration scripts automatically in each environment (Dev, Test, QA, Staging, Prod) as we did our deploy, so we would no longer have to worry about manually applying scripts. In the past for Java, Scala, and .NET projects I have used several different tools, but for this project we decided to use Flyway which is very flexible and simple to setup.

The documentation for Flyway is excellent, however I decided to just post what we did in our app in case it might help someone else out. Here is our “Quick Start” setup.

1. Add the Flyway dependency

Flyway can be setup using Maven, Gradle, SBT, Ant, etc. In our project we used Maven, so all we did was add the following to our pom.xml file:


2. Code

Now, to get Flyway to check for any database changes to apply, whenever the application is started, put the following code somewhere in your applications startup sequence.

Flyway flyway = new Flyway();
//Create the dbo.schema_version table if it does not already exist
//Where the method "getDataSource()" provides your DataSource 
//object that has the jdbc url, username, and password.

3. SQL Script Location

All migrations scripts by default must go in the following folder in your app:


4. SQL Script Naming convention

Scripts are run in version number order based on their names, the default naming convention is:

For example: “V1_0_1__create_tables.sql”

All scripts must start with the letter “V”, followed by major/minor version numbers, with two underscores “__” separating the version from the description.

5. Schema Version Table

Flywaydb will automatically create a table in each database called “dbo.schema_version” which stores a log of all migration scripts that have been applied.

The table looks like:

version_rank installed_rank version description type script checksum installed_by installed_on execution_time success
1 1 1 Flyway Baseline BASELINE Flyway Baseline NULL JohnSmith 2015-07-30 15:55:49.337 0 1
1 2 1.0.1 create tables SQL V1_0_1__create_tables.sql -440358290 JohnSmith 2015-07-30 15:55:49.337 109 1

6. Handling Failure

If a script fails, the app will fail to start and the failure information is written to our log files as well as a monitor is tripped. Flywaydb applies each script as a transaction so all changes in the script will be rolled back if any part of the script fails. This is very handy because if you commit a script with some invalid SQL syntax, all you have to do is update the script with the corrected syntax, commit it again, and let the build pipeline apply the changes from the fixed script. No messy cleanup or reset to worry about.

So that is it, you should have all you need to get your database schema changes easily synced up with the deploy of your app!

May 2, 2014

SQL Server – Simple Recursive Query Example

Filed under: SQL,SQL Server — Brian @ 2:50 pm
Tags: , , , ,

Every once in a while I need to write a recursive query in SQL Server and I always forget the syntax so I have to track down a simple example to help me remember. However, if you are trying to write a recursive query for the first time, I find some of the examples online to be a little bit too complicated. So I wanted to post an example, but also give you the script to create the table and populate it with data so you can see how it works and try it yourself. In this post I use the common example of a table with countries, states, and cities and where we want to get a list of all cities in a single country. Enjoy!

Create a table called “Area”:

   AreaID int NOT NULL,
   AreaName varchar(100) NOT NULL,
   ParentAreaID int NULL,
   AreaType varchar(20) NOT NULL
( AreaID ASC

Add some “Area” data:

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(1, 'Canada', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(2, 'United States', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(3, 'Saskatchewan', 1, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(4, 'Saskatoon', 3, 'City')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(5, 'Florida', 2, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(6, 'Miami', 5, 'City')

If I do a select by AreaType “City”:

select * from dbo.Area
where AreaType = 'City'

I get both Saskatoon and Miami:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3            City
6       Miami           5            City

However, what if I wanted to return all cities in Canada?

You can accomplish this by doing a recursive select which uses a common table expression (CTE).

--anchor select, start with the country of Canada, which will be the root element for our search
SELECT AreaID, AreaName, ParentAreaID, AreaType
FROM dbo.Area 
WHERE AreaName = 'Canada'
--recursive select, recursive until you reach a leaf (an Area which is not a parent of any other area)
SELECT a.AreaID, a.AreaName, a.ParentAreaID, a.AreaType 
FROM dbo.Area a 
INNER JOIN AreasCTE s ON a.ParentAreaID = s.AreaID 
--Now, you will have all Areas in Canada, so now let's filter by the AreaType "City"
where AreaType = 'City' 

Now we get back the following results for cities in Canada:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3               City

That’s it! Now we have written a simple recursive query!

March 6, 2014

SQL Server – Alter database in Single User mode to Multi User mode

Filed under: SQL,SQL Server 2005 — Brian @ 8:43 am
Tags: , , , ,

We have some test environment databases that get rebuilt nightly. The job that does the rebuild always switches the database into single user mode when it is being rebuilt. When the rebuild is finished it will switch it back to multi user mode. However, if there was a problem with a script and the job failed, it will leave the database in single user mode.

First, open a SQL Server Management Studio query window connected to database “master”.

The command to change the database back to multi user mode is:


However, if there is an existing process blocking this alter, you may get the error message:

“Transaction (Process ID 864) was deadlocked on lock resources with another process and has been chosen as the deadlock victim”

Since both the existing running process and the current request have the same deadlock priority, preference is given to the longer running process which is why your alter database command is chosen as the deadlock victim. So to deal with this when you run the command again, set the deadlock priority to HIGH. However, if that does not work, you will need to kill the existing connection.

To find the “spid” of the existing connection, you can use the following query:

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.[name] = 'DatabaseNameGoesHere'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

exec sp_who

So, here is our revised command:

KILL SpidToKillGoesHere

That should do it!

May 27, 2013

SQL Server – Check Index Fragmentation on ALL Indexes in a Database

Filed under: SQL,SQL Server — Brian @ 11:46 am
Tags: , , , ,

Often, when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”. Here is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2K5 or newer.

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

That should do the trick!

January 21, 2013

LINQ to SQL – Unit Testing a Repository

Filed under: .NET,c#,LINQ,Testing — Brian @ 10:47 pm
Tags: , ,

Over the last few months I have been using LINQ to SQL for accessing a SQL Server database from an MVC 4 WEB API application. Overall it works very well, and when looking at the SQL it generates, it does generate very efficient SQL. Also, I really like the LINQ to SQL syntax as well, very simple and clean, and it is easy to translate a SQL query into the LINQ to SQL syntax.

So normally in the past when I was testing a repository in Java/Scala projects that used Hibernate, we just used SQLite for our unit tests. But since the syntax used in LINQ to SQL is just LINQ expressions, I was wondering if it would be possible to unit test the LINQ expressions in my repository by just passing in a list of objects.

I looked at several existing solutions for unit testing repositories using LINQ to SQL, which looked good, but they each had the same problem. They were creating a repository with a DataContext being provided in the constructor, which allowed it to be mocked in a test. But, the problem with this approach is that the DataContext is designed to be used for a single database transaction, so you should always create and dispose of a new DataContext for reach request to the database. For Example I would expect each method in the repository to look like:

using (var db = new EmployeeDataContext(new DataContext(_connectionString)))
    return (from employee in db.Employees() where employee.Id == id select employee).SingleOrDefault();

Instead of:

return (from employee in existingContext.Employees() where employee.Id == id select employee).SingleOrDefault();

The only way this other approach would work reliably would be if the application were creating a new instance of the repository class on every request, but I did not want to do that. So the solution I came up with to get the tests working the way I wanted AND while still creating a new DataContext for each transaction, was to create a wrapper class for the DataContext. This allowed me create a stub for the DataContextWrapper that could be used to replace the real one in a test. This may not be the most eloquent solution, but it worked for what I needed. If anyone has a cleaner solution for accomplishing this, please let me know!

Let’s use the standard database “Employee” example, just to be consistent.

Note: I used Moqand NUnit in this example.

Employee Entity
Just a standard LINQ to SQL entity object

[Table(Name = "dbo.Employee")]
public class EmployeeEntity
    [Column(IsPrimaryKey = true, IsDbGenerated = true)] public int Id { get; set; }
    [Column] public string FirstName { get; set; }
    [Column] public string LastName { get; set; }
    [Column] public DateTime StartDate { get; set; }
    public override string ToString()
        return string.Format("Id={0}, FirstName={1}, LastName={2}, StartDate={3}", Id, FirstName, LastName, StartDate);

Employee Data Context
DataContext for accessing the “Employees” table in our database.

public interface IEmployeeDataContext : IDisposable
    ITable<EmployeeEntity> Employees();
    void ExecuteCommand(string command, params object[] parameters);
    void SubmitChanges();

public class EmployeeDataContext : IEmployeeDataContext
    private readonly DataContext _dataContext;

    public EmployeeDataContext(DataContext dataContext)
        _dataContext = dataContext;

    public ITable<EmployeeEntity> Employees()
        return _dataContext.GetTable<EmployeeEntity>();

    public void ExecuteCommand(string command, params object[] parameters)
        _dataContext.ExecuteCommand(command, parameters);

    public void SubmitChanges()

    public void Dispose()

Data Context Wrapper
Now, let’s wrap the EmployeeDataContext in another class that has a method called CreateDataContext() which allows us to create a new EmployeeDataContext.

public interface IDataContextWrapper
    IEmployeeDataContext CreateDataContext();

public class DataContextWrapper: IDataContextWrapper
    private readonly string _connectionString;
    public DataContextWrapper(string connectionString)
        _connectionString = connectionString;

    public IEmployeeDataContext CreateDataContext()
        return new EmployeeDataContext(new DataContext(_connectionString));

Employee Repository
The constructor of our EmployeeRepository now takes a parameter of type IDataContextWrapper. So in each method that calls the database, it can now call _dataContextWrapper.CreateDataContext() in a using block to create a new DataContext for the request and also ensure the DataContext will be disposed.

public interface IEmployeeRepository
    EmployeeEntity AddEmployee(EmployeeEntity employee);
    EmployeeEntity GetEmployee(int id);
    List<EmployeeEntity> GetAllEmployees();
    List<EmployeeEntity> GetEmployeesByLastName(string lastName);
    List<EmployeeEntity> GetEmployeesByStartDate(DateTime minDate, DateTime maxDate);

public class EmployeeRepository : IEmployeeRepository
    private readonly IDataContextWrapper _dataContextWrapper;

    public EmployeeRepository(IDataContextWrapper dataContextWrapper)
        _dataContextWrapper = dataContextWrapper;

    public EmployeeEntity AddEmployee(EmployeeEntity employee)
        using (var db = _dataContextWrapper.CreateDataContext())
            return employee;

    public EmployeeEntity GetEmployee(int id)
        using (var db = _dataContextWrapper.CreateDataContext())
            return (from employee in db.Employees() where employee.Id == id select employee).SingleOrDefault();

    public List<EmployeeEntity> GetAllEmployees()
        using (var db = _dataContextWrapper.CreateDataContext())
            return (from employee in db.Employees() select employee).ToList();

    public List<EmployeeEntity> GetEmployeesByLastName(string lastName)
        using (var db = _dataContextWrapper.CreateDataContext())
            return (from employee in db.Employees() where employee.LastName == lastName select employee).ToList();

    public List<EmployeeEntity> GetEmployeesByStartDate(DateTime minDate, DateTime maxDate)
        using (var db = _dataContextWrapper.CreateDataContext())
            return (from employee in db.Employees() where employee.StartDate >= minDate && employee.StartDate <= maxDate select employee).ToList();

Stub Data Context Wrapper
Now let’s create a stub class for our DataContextWrapper which will be used to replace the real one in our unit tests.

public class StubDataContextWrapper : IDataContextWrapper
    private readonly IEmployeeDataContext _dataContext;
    public StubDataContextWrapper(IEmployeeDataContext dataContext)
        _dataContext = dataContext;

    public IEmployeeDataContext CreateDataContext()
        return _dataContext;

Stub Employee Table
Also, we will need to create a stub of the employee table, which will be used in our tests to wrap the list of EmployeeEntity objects we will return as the result from our EmployeeDataContext when it is mocked in our tests.

public class StubEmployeeTable : ITable<EmployeeEntity>
    protected List<EmployeeEntity> internalList;

    public StubEmployeeTable(List<EmployeeEntity> list)
        internalList = list;

    public void Attach(EmployeeEntity entity)

    public void DeleteOnSubmit(EmployeeEntity entity)

    public void InsertOnSubmit(EmployeeEntity entity)

    public IEnumerator<EmployeeEntity> GetEnumerator()
        return this.internalList.GetEnumerator();

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        return this.internalList.GetEnumerator();

    public Type ElementType
        get { return this.internalList.AsQueryable().ElementType; }

    public System.Linq.Expressions.Expression Expression
        get { return this.internalList.AsQueryable().Expression; }

    public IQueryProvider Provider
        get { return this.internalList.AsQueryable().Provider; }

Employee Repository Unit Tests
Now we can write our unit tests around the EmployeeRepository. The first step is to mock the EmployeeDataContext. Next we create a StubDataContextWrapper passing in the mock object. Then to create the EmployeeRepository we pass in the StubDataContextWrapper. We setup some EmployeeEntity objects with data we will use for testing, then finally we set the test data as the return value for the Employees() method on the mock of our EmployeeDataContext, using the StubEmployeeTable to wrap the result, to make it look like a response from a LINQ to SQL query.

public class EmployeeRepositoryTest
    private Mock<IEmployeeDataContext> _mockEmployeeDataContext;
    private IDataContextWrapper _stubDataContextWrapper;
    private IEmployeeRepository _employeeRepository;

    public void Setup()
        _mockEmployeeDataContext = new Mock<IEmployeeDataContext>();
        _stubDataContextWrapper = new StubDataContextWrapper(_mockEmployeeDataContext.Object);
        _employeeRepository = new EmployeeRepository(_stubDataContextWrapper);

        var employee1 = new EmployeeEntity { Id  = 1, FirstName = "John", LastName = "Smith", StartDate = new DateTime(2013,01,10)};
        var employee2 = new EmployeeEntity { Id  = 2, FirstName = "Frank", LastName = "Smith", StartDate = new DateTime(2013,01,15)};
        var employee3 = new EmployeeEntity { Id  = 3, FirstName = "Stan", LastName = "Johnson", StartDate = new DateTime(2013,01,20)};

        var employees = new List<EmployeeEntity> {employee1, employee2, employee3};
        _mockEmployeeDataContext.Setup(x => x.Employees()).Returns(new StubEmployeeTable(employees));

    public void AddEmployeeShouldAddNewEmployeeWhenDoesNotExist()
        _employeeRepository.AddEmployee(new EmployeeEntity { FirstName = "New", LastName = "Employee", StartDate = new DateTime(2013, 01, 25) });
        Assert.AreEqual(4, _employeeRepository.GetAllEmployees().Count);

    public void GetAllEmployeesByIdShouldReturnEmployeeWhenIdExists()
        var results = _employeeRepository.GetAllEmployees();
        Assert.AreEqual(3, results.Count);

    public void GetEmployeeByIdShouldReturnEmployeeWhenIdExists()
        var result = _employeeRepository.GetEmployee(1);
        Assert.AreEqual(1, result.Id);

    public void GetEmployeesByLastNameShouldReturnEmployeeWhenLastNameMatches()
        var results = _employeeRepository.GetEmployeesByLastName("Smith");
        Assert.AreEqual(2, results.Count);

    public void GetEmployeesByStartDateShouldReturnEmployeeWhenStartDateIsWithinRange()
        var results = _employeeRepository.GetEmployeesByStartDate(new DateTime(2013, 01, 14), new DateTime(2013, 01, 16));
        Assert.AreEqual(1, results.Count);
        Assert.AreEqual(new DateTime(2013, 01, 15), results[0].StartDate);

That is all. Of course this example can be made more generic, but it is just a simple example. If you have an example that works better for LINQ to SQL, please let me know, I would be happy to hear about it!

January 25, 2009

SQL Tips – Update From, Delete Into, and more

Filed under: SQL,SQL Server 2005 — Brian @ 10:43 pm

Over the last few years I have spent a lot of time working with Microsoft SQL Server. I frequently get asked SQL questions by my co-workers so I thought I would compile a list of some of those most commonly asked SQL questions.

Checking Index Fragmentation

As an index becomes more fragmented, performance of the index will degrade. You can check the fragmentation of an index with the following query:

declare @table sysname;
set @table = 'dbo.MYTABLE';
declare @indexid int;
select      @indexid = index_id
from        sys.indexes
where       object_id = object_id(@table) and [name] = 'IX_MYTABLE_MYINDEX';

select      *
from        sys.dm_db_index_physical_stats(db_id(), object_id(@table), @indexid, NULL, NULL);

Update From (With Joins)

When migrating data it is common to update a value in one table, filtering by a value stored in another table. Usually the simple answer is to do the following:

UPDATE dbo.Friends
SET status='Declined'
	Select LastName
	From dbo.Enemies
	Where status='Active'

However, sometimes you may need to filter an update based on the values in two columns such as LastName and FirstName. This is where the SQL statement “UPDATE FROM” becomes very useful:

SET status='Declined'
FROM dbo.Friends fr
JOIN dbo.Enemies en
	ON fr.LastName = en.LastName
	AND fr.FirstName = en.FirstName
WHERE en.status='Active'

Delete Into (Output)

Let’s say that I want to delete old friends, but at the same time I also need to capture the list of friends deleted so that I can cleanup data in other tables. Now this can be accomplished with two statements, a SELECT followed by a DELETE, however it is much cleaner to just do this in one statement with a DELETE INTO statement. Here is a simple example:

Declare a table to store the deleted friendIDs:

declare @deletedFriendIDs table (FriendID int);

Delete old Friends, and output the list into the table variable:

	from	dbo.Friends
	output	deleted.Friends INTO @deletedFriendIDs
	where	LastAccessDate &amp;lt; (getdate() - 90)

Now let’s display the list of the deleted FriendIDs to make sure that it worked

Select * from @deletedFriendIDs

Case Sensitive Query

SQL is case insensitive. However, sometimes you may need to do a query where case is important. The following example will return all Friends with the first name “chris” but exclude “Chris”.

select * from dbo.Friends
where FirstName = 'chris'
COLLATE SQL_Latin1_General_CP1_CS_AS

How to Capitialize the first letter and make all other letters lower case

It is common that you might have some data, such as a list of names where the first letter is capitalized in some names and not in others, so you will want to clean up this data. Here is a simple query to capitalize the first letter in each name and make the rest of the name lower case:

update dbo.Friends
Set firstname = upper(left(firstname, 1)) + lower(right(firstname, len(firstname) - 1)),
lastname = upper(left(lastname, 1)) + lower(right(lastname, len(lastname) - 1))

Removing spaces from a varchar

So it is possible that some data slipped into your database without spaces being trimmed off. So if this happens and after the code bug is fixed you need to cleanup existing data, here is a simple query to do it:

UPDATE dbo.Friends
set LastName = replace (LastName,' ','')
where LastName IS NOT NULL

How to get the total row count of a very large table without using a “Select count(*)…” statement

For some production tables that are very large doing the typical “Select count(*) from dbo.Friends” statement can be very expensive and slow. SQL Server has a property on each table which stores the current row count for the table. So rather than using an expensive select you can just access that property:

select      ISNULL(sum(spart.rows), 0)
from        sys.partitions spart
where       spart.object_id = object_id('dbo.Friends') and spart.index_id &amp;lt; 2

December 7, 2007

How to use Recursion with FOR XML PATH

Filed under: SQL,SQL Server 2005 — Brian @ 10:17 pm
Tags: , ,

If you have tried writing a recursive function using FOR XML EXPLICIT you will know how tedious it can be. However, with FOR XML PATH recursion is simple and incredibly fast. I had to recently write a recurisve function to return all province and city data in Canada in our system as a formatted XML document. The following is just a simple example I wrote to demonstrate how to accomplish this task with FOR XML PATH (NOTE: this function is just a basic example, it is not the full version I wrote for the task I was working on). If you need to write a recursive function, I hope this helps you get started!

This example recursive function takes in a LocationID and produces an XML document with every Location descendant of the given LocationID:

CREATE FUNCTION dbo.GetAllLocationsFromParent(@Parent int)
  (SELECT loc.LocationID as '@id',
	loc.[name] as '@name',
	loct.[Name] as '@type',
      CASE WHEN loc.Parent=@Parent
      THEN dbo. GetAllLocationsFromParent(loc.LocationID) --Recursive call
	FROM dbo.Location loc
		JOIN dbo.LocationType loct ON loct.LocationTypeID = loc.LocationTypeID
	WHERE loc.Parent=@Parent
		and loc.[Status] = 'Approved'
   FOR XML PATH('location'), TYPE)

The following is a sample of the format of the XML document if the Parent LocationID passed in was 0 for World, where all countries and their corresponding decendants would be returned:

<location id="1" name="Canada" type="Country">
    <location id="123" name="Saskatchewan" type="State">
      <location id="12345" name="Saskatoon" type="City">

That’s all! Writing a recursive method with FOR XML PATH is very straight forward.

October 15, 2007

Service Broker Tutorial – SQL Server 2005

Filed under: SQL Server 2005 — Brian @ 5:46 pm
Tags: ,

The following is meant as a “Quick Start” tutorial for anyone needing to create a service broker queue under extreme time pressure! This is a generalized example of a db script for a new use of service broker queue called fooQueue. Replace attributes foo1, foo2 with whatever attributes (and types) you need in your message. Replace foo with a relevant name, e.g. CustomerOrders.  Replace schemaName with dbo or with whatever schema name you are using.  (I have experienced tricky permissions issues in the past with schema names other than dbo.)

NOTE: During our use of service broker, we ran into a number of “Gotchas”. The bonus of these examples is all of those fixes are built into the example. So if you need a high performance service broker queue where all of the problems have been ironed out, then feel free to cut and past this example.

STEP 1: Create the message schema

This schema defines the XML messages that can be placed onto the service broker queue.

CREATE XML SCHEMA COLLECTION [schemaName].[fooMessageXmlSchema] 
AS N'<xsd:schema xmlns:xsd="">
      <xsd:complexType name="foo">
            <xsd:attribute name="foo1" type="xsd:integer" />
            <xsd:attribute name="foo2" type="xsd:integer" />
      <xsd:complexType name="Root">
                 <xsd:element name="foo" type="foo" minOccurs="0" maxOccurs="unbounded"/>
      <xsd:element name="Root" type="Root" />

STEP 2: Create the Send Request Stored Procedure

This procedure is responsible for taking a properly formatted message and sending it to the service broker queue.

CREATE PROCEDURE [schemaName].[fooSend]
      @body XML (DOCUMENT fooMessageXmlSchema)
      DECLARE @handle uniqueidentifier;

      IF (convert(varchar, @body.query('count(/Root/*)')) != 0)
            BEGIN DIALOG CONVERSATION     @handle
            FROM SERVICE      fooService
            TO SERVICE  'fooService'
            ON CONTRACT fooMessageContract
            WITH ENCRYPTION = OFF;        SEND
            ON CONVERSATION   @handle
            MESSAGE TYPE      fooMessage (@body);
            END CONVERSATION @handle;

STEP 3: Send Stored Procedure Caller

This piece of code creates a new message that can then be placed on the queue. Place this code inside some calling stored procedure and/or trigger.

DECLARE @body XML (DOCUMENT fooMessageXmlSchema);
      SELECT @body =  (
           SELECT (
                     i.foo1 as <span>"@foo1"</span>,
                     i.foo2 as <span>"@foo2"</span>
                FROM inserted i
                FOR XML PATH ('foo'), TYPE
           ) FOR XML PATH ('Root'), TYPE
     );    EXEC schemaName.fooSend @body;

STEP 4: Create the Receive Stored Procedure

The receive stored procedure is where the bulk of your work will be done. This is the sproc that will be called by the service broker queue every time a new message is inserted into the queue.

CREATE PROCEDURE [schemaName].[fooReceive]
DECLARE @MessageBody XML (DOCUMENT fooMessageXmlSchema);
DECLARE @foo1 int;
DECLARE @foo2 int;
DECLARE @fooCount int;
DECLARE @fooCurrent int;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
      @mt = message_type_name,
      @MessageBody = message_body
      FROM [schemaName].[fooQueue]), TIMEOUT 1000;
            IF @mt = N''
            ELSE IF (@mt = N'fooMessage')
                  SET @fooCount = @MessageBody.value('count(/Root/foo)', 'int');
                  SET @fooCurrent = 1;

                  WHILE (@fooCurrent &lt;= @fooCount)
                        --set variable values from xml message body
                        SELECT @foo1 ='@foo1','int'),
                               @foo2 ='@foo2','int')
                        FROM @MessageBody.nodes('(/Root/foo[sql:variable(&quot;@fooCurrent&quot;)])') AS Root(foo)

                        -- DO YOUR WORK HERE

                        -- increment the position

                        SET @fooCurrent = @fooCurrent + 1;
                  END CONVERSATION @dh;
      END TRY
                  -- If there are any exceptions then end conversation,
                  -- rollback the transaction and stop processing the queue
                  END CONVERSATION @dh;
                  ROLLBACK TRANSACTION;
                  INSERT INTO ServiceBrokerErrors (queuename, errornumber, errormessage, errordate)
                  VALUES ('fooQueue', ERROR_NUMBER(), ERROR_MESSAGE(), getdate())
                  ALTER QUEUE [schemaName].[fooQueue] WITH ACTIVATION ( STATUS = OFF );
                  BEGIN TRANSACTION
      END CATCH   -- Try to loop once more if there are more messages
      SELECT @dh = NULL;
      WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
            @mt = message_type_name,
            @MessageBody = message_body
            FROM [schemaName].[fooQueue]), TIMEOUT 1000;

Note: The RECEIVE top (1) used in the above store procedure is good for a single reader. If you need to use multiple readers, you can use the following to receive all messages for a conversation group:

DECLARE  @Messages TABLE (MessageBody XML, MessageTypeName SYSNAME, ddh UNIQUEIDENTIFIER)
WAITFOR (RECEIVE  message_body, message_type_name, conversation_handle
	FROM [dbo].[EmailServiceColpaEventQueue]
	INTO @Messages), TIMEOUT 1000

You can then loop through the messages in the temporary table and end the conversation appropriately.

STEP 5: Setting up the Service Broker Queue

Create the Message Type for the queue. This states what the queue will accept as a valid message. In this Message Type we are saying it must be an XML document and must conform to the schema fooMessageXmlSchema.

WITH SCHEMA COLLECTION [schemaName].[fooMessageXmlSchema]

The Contract just states who is authorized to add messages to the queue and references the Message Type.

CREATE CONTRACT [fooMessageContract]

Create the new service broker queue.
Note: When creating the service broker queue, the Activation section allows you to attach the receive stored procedure (e.g., fooReceive) that you have already created. This stored procedure will be called any time a new message is inserted into the queue.

CREATE QUEUE [schemaName].[fooQueue]
      STATUS = ON ,
      PROCEDURE_NAME = [schemaName].[fooReceive] ,

Finally, create a new Service to run the new queue.

AUTHORIZATION [dbo]  ON QUEUE [schemaName].[fooQueue] ([fooMessageContract])

Well that is all! I hope this helps!

Create a free website or blog at