Implementing LOB Storage in Memory Optimized Tables

Introduction Memory optimized tables do not have off-row or large object (LOB) storage, and the row size is limited to 8060 bytes. Thus, storing large binary or character string values can be done in one of two ways: Split the LOB values into multiple rows Store the LOB values in a regular non-memory optimized (file…


Adventures in TSQL: SQL Server Query Performance Analysis using DMVs

From the development perspective I often have to perform an analysis of a database application. More often than not this entails looking at a running system and ensuring that the application queries are behaving as expected. As such, I thought it would be worthwhile sharing some TSQL scripts that I have been using over the…


Creating a Partitioned View in the BAM Archiving Database

When you run the BAM data maintenance package (BAM_DM_<activity name>) BAM copies each partition in the BAM Primary Import database to a separate table in the BAM Archive database. You can create partitioned views in the BAM Archive database to facilitate locating the data. However one is left to create these partitioned views oneself. A…


Adventures in TSQL: Comma separated string from column values

It seems that several times now I have had the requirement to create a comma separated string from column values. The latest reason for doing this was to build up a list of table column names such that a view could be generated for the table. This is surprisingly easy to do in TSQL. One…


Adventures in TSQL: Adding date and time values

With the addition of the SQL Server date and time types, I have often found myself needing to create a datetime (or datetime2) value based on the addition of a date and a time value. However, there is no built-in function for such an operation. There are a few solutions to this problem that encompass…


Adventures in TSQL: Using CTEs when performing UPDATE operations

In a recent post I discussed using CTEs for managing ordered DELETE operations. Adventures in TSQL: Using CTEs when performing DELETE operations However CTEs are also useful for ordered UPDATE operations, as I will hopefully show in this post. One of the limitations of UPDATE TOP() statement is that an ORDER BY cannot be specified….


Adventures in TSQL: Using CTEs when performing DELETE operations

In most applications these days there will always be some form of backend database. So hopefully over the coming months I thought it would be a good idea to share some TSQL bits, starting with using Common Table Expressions (CTEs) for managing DELETE operations. It seems in quite a few applications of late there has…