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.

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!

October 24, 2009

Migrating Data to JSON In SQL Server

Filed under: SQL — Brian @ 10:44 pm
Tags: , ,

Recently I was working on a task that required migrating some existing data (Phone Numbers) from being stored as separate columns in a separate table (e.g., dbo.FriendPhoneNumbers), to being stored as a single JSON document in one column of the table containing all related data (e.g., dbo.Friend).

My task was only to complete a one-time migration, so I did not want to spend a lot of time writing a custom application to complete this work. Just for fun I decided to see if I could first accomplish this migration in SQL. I was also curious if such a SQL statement would be efficient enough to be run in a live production environment.

Using the SQL UPDATE statement below I was able to create 100,000+ JSON documents in just a few seconds.

SET PhoneNumber = phoneJSON.JSON 
from dbo.Friend f 
	select FriendID, REPLACE( 
		CASE WHEN Home IS NOT NULL THEN '"Home":"'+Home+'",' ELSE '' END +
		CASE WHEN Work IS NOT NULL THEN '"Work":"'+Work+'",' ELSE '' END +
		CASE WHEN Fax IS NOT NULL THEN '"Fax":"'+Fax+'",' ELSE '' END +
		CASE WHEN Cell IS NOT NULL THEN '"Cell":"'+Cell+'",' ELSE '' END
		+'}', ',}', '}') as 'JSON'
	from dbo.FriendPhoneNumbers
) as phoneJSON ON phoneJSON.FriendID = f.FriendID

While writing the script I ran into two issues:

  1. Nullable Columns: Wrapped each column in a SQL CASE statement so when a phone number was null, nothing was added to the JSON
  2. Trailing Comma: Added a SQL REPLACE to replace “,}” with “}” to handle the case where the last phone number in the list was null

That was all! It took only a few minutes to write this script.

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 < (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 < 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.

Create a free website or blog at