CodeStock 2016

My Background with CodeStock

It was probably about three years ago that I was attending a SQL Saturday when one of the speakers (I wish I could remember who, so as to give proper credit) asked if anyone attended various community technical conferences. I jotted down the names that he threw out there, and then forgot about it for a while.

A few months passed, and then I ran across my note. I started to look up the conferences, and the one that really stuck out was CodeStock. It was an annual event held in Knoxville, Tennessee and the mix of topics from the most recent gathering looked to be just about right. The conference had already passed for the year, so once again I put the notion on the back burner but continued to watch for announcement of the next time around.

Then in late January of 2014, the notice came: the event would be held on July 11-12 of that year. I continued to watch as the call for speakers opened and close, a keynote was announced, and in due course the session voting began. The list of available sessions sealed the deal for me, and I attended the conference in July. Then again in 2015.

And now I’ve just returned from the 2016 event.

CodeStock 2016

So a quick overview of my CodeStock experience this year is in order.

It began with the keynote address by the excellent Cory House (b | t), who also presented the keynote during my first year. His basic theme was that it is OK to be anywhere on the technology adoption curve (bleeding-edge, mainstream, legacy), so long as it is a deliberate and informed decision. My favorite take-away was a comment regarding providing praise to others. Cory presented it, as I recall, in the context of complimenting a child’s accomplishment. Saying that “you’re so smart” is easy but downplays the actual achievement. Better to say, “You must have worked really hard.” I like that.

I won’t summarize every session I attended, but I’ll just highlight a couple. That’s not to suggest that any of the others weren’t worthwhile, but there were a few that really stood out.

I loved what I came to think of as the “encryption two-for”: back-to-back sessions in the same room starting with Steven Swenson (b | t), who gave a nuts-and-bolts presentation on how to use cryptography in applications, including several concrete implementations.

Then Adam Caudill (b | t) discussed the state of modern cryptography, include what is broken today, what is likely to break soon, and what remains solid. Then he moved into a fascinating explanation of where crypto appears to be headed in the next few years.

These two sessions provided a great “level-up” for me on a topic where I have a fair bit of interest but haven’t keep up on things lately.

I also attended the Women in Technology panel discussion. It was a good conversation and there some quality questions that arose, but the standout to me was the “adventure” that the moderator proposed with the leftover time during the session. She wanted everyone to meet someone new and to have a discussion with them. Honestly, this is the kind of thing that frightens me, being the big-time introvert that I am. Fortunately, the two people sitting on the same row with me had already left, so I figured I was off the hook.

Not so. Just as I getting ready to head on out, the moderator herself came to me and said, “You’re not talking to anyone!” She then began the conversation, and it was actually pretty fun and welcoming. A couple of other women also joined in over the next few minutes, and it ended up being a highlight of the conference for me. So thanks to them for engaging with me.

On the second day of the conference I mostly attended some “soft-skills” sessions, something I have tried of late to incorporate into conferences that I go to. They were all quite valuable, but the one session of the day that really resonated with me was from Cameron Presley (t) on the SOLID principles. I’ve tried to wrap my head around these ideas in the past, but Cameron finally managed to show it in a way that really made sense to me, so thanks to him for that. Now I’ve got a couple of personal projects that need a whole lot of refactoring.

Conclusion

CodeStock is an awesome and growing conference that has become a summer ritual for me over the past few years. It is interesting for me, as someone who mostly lives in the SQL Server world, but who dabbles in software development, to get to attend a conference like this and at least stay somewhat current on where the dev world is at and where it is going.

One last note. I think I was asked about three or four times if I planned to submit to present next year. I think it’s great that there is so much emphasis on growing a speaker base. (I actually did submit a session for CodeStock this year, but in the end was actually somewhat relieved when it wasn’t selected. May and June ended being pretty busy for me and it would have been challenging to create a presentation on top of all that.)

But yes, I have found myself noodling over what I might submit for 2017.

The Imminent Release of SQL Server 2016

When I first started working with SQL Server, version 7.0 was the cutting edge. That version was released in late 1998, and the project I was working on at the time was still in the planning stages. The powers that be decided to move to 7.0 rather than 6.5, and it was only some time later when I had to support some 6.5 instances that I came to appreciate the paradigm shift that the new version represented. I think we can all agree that we all benefit from that brave new world to this day.

Since then, we have seen six new releases of SQL Server (2000, 2005, 2008, 2008R2, 2012 and 2014), and we now find ourselves on the cusp of the release of SQL Server 2016. We have seen over the past couple of releases an increase in the transparency of the development process with the release of beta version, and 2016 took it a whole new level. There have been, if my count is correct, eleven public preview versions released as of this writing, up to release candidate 1. That is absolutely incredible, and I think Microsoft deserves a ton of credit for opening up the process to this degree.

The new and updated feature set in SQL Server 2016 is equally impressive. I have downloaded, installed, and (to varying degrees) used every single one of those preview versions, and yet I feel that I’ve barely skimmed the surface of what I need to eventually learn. I have studied pretty much every new feature in the product, but I just haven’t been able to dig in nearly as deep as I would like. There’s just too much.

So, I guess that all of this just my way of saying that I’m really quite excited for the imminent release of SQL Server 2016. It is packed with new features, and I am sure that it will be some time until I get the opportunity to use some of these new bits of functionality in real systems (and until they mature enough to be ready for such use). Still, this is an exciting time for me — more so than for the past few releases.

Professional Development Notes

I have been doing a brain dump on Hekaton for the past few weeks, but I need a bit of break before diving into (and possibly drowning in) the internals.

I have attended a few technology-related professional development courses over the past few years, and I wanted to place some of the better pieces of advice here, primarily for my reference.  Because I very much need this as much as anyone, if not more.

Because I’m such an expert on such matters (not!), I have decided to leave these suggestions in bullet form. It may also have something to with the fact I really don’t have anything to add.

  • Interview question: What is one of the worst things about working here?
  • Act like a consultant (even if you aren’t)
  • Work on advanced projects – just enough to be uncomfortable
  • Get some exercise / mediate
  • Non-computer-related hobby
  • Make your accomplishments visible (far beyond the team that already knows)
  • Have strong opinions, weakly held (be willing to change)
  • Be a jack-of-all-trades
  • Be willing to step up and work extra hours when the need arises
  • Avoid negativity – in yourself and others
  • Learn new, related technologies
  • Know where you want to go
  • Use objective information (not emotion) to project the lifetime of a technology
  • If your current job doesn’t include what you love to do, expand your job and take on extra responsibilities
  • Once per year take time to assess where you were a year ago and where you want to be in one year
  • Iterative, continual learning is the best approach
  • Recognize that short-term thinking is usually driven by artificially dictated project deadlines
  • On the other hand, too much long-term thinking can cause the project to become mired and never ship
  • Learn when to ignore company politics and just do your job
  • Choose your battles; is this the right hill to die on?
  • Listen before responding; let them give their story
  • Most people need criticism; most people who deliver criticism do it poorly (get over it)
  • Don’t try to do too much; do fewer things and do them well
  • Low performers realize they can do 20% and survive; high performers do 20% more and stay ahead
  • Continually interview, even if you don’t intend to leave (helps evaluate the industry and self-skills)

Hekaton Part 7: Error Handling

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

In the previous post in this series, I discussed three validation errors that are unique to Hekaton and their common causes:

  • Error 41302 – Generated when two concurrent sessions update the same record.
  • Error 41303 – Generated when a concurrent update causes a repeatable read failure.
  • Error 41325 – Generated when a concurrent insert causes a serializable failure.

There is also error 41301 that I will address later in this series.

Applications that access databases containing In-Memory tables need to be aware of these errors and be able to respond gracefully when they occur.

There is one error condition that has been around for a long time in SQL Server that closely parallels these three errors, and that is the deadlock (error 1205). Deadlocks, however, are largely not accounted for in error handling because they tend to be rare, and can largely be avoided by good coding practices (specifically, consistent order of access to tables and other resources, when at all possible). On the other hand, the Hekaton errors will, in practice, be more commonplace, and I consider them to be “normal” events in practice, especially as the throughput on the system becomes heavy.

The appropriate response, in most situations, is to retry the operation when this error is raised. Microsoft has an example of handling errors in stored procedures, but applications also need to address these errors.

Here is a stub of a data access class that demonstrates how this can be done.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HekatonErrorTrapping
{
    class Dal : IDisposable
    {
        private SqlConnection _connection;

        public Dal()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @".";
            sb.InitialCatalog = "Hekaton";
            sb.IntegratedSecurity = true;

            _connection = new SqlConnection(sb.ToString());
            _connection.Open();
        }

        public SqlTransaction StartTransaction()
        {
            return _connection.BeginTransaction();
        }

        public void UpdateProductUnitPrice(int productId, decimal newPrice, SqlTransaction transaction)
        {
            string sql = @"update dbo.Product with (snapshot) set UnitPrice = @NewPrice where ProductID = @ProductId;";
            SqlParameter idParameter = new SqlParameter("@ProductId", productId);
            SqlParameter priceParameter = new SqlParameter("@NewPrice", newPrice);

            using (SqlCommand command = new SqlCommand(sql, _connection, transaction))
            {
                command.Parameters.Add(idParameter);
                command.Parameters.Add(priceParameter);

                int retryCount = 5;
                bool isComplete = false;
                while (isComplete == false)
                {
                    try
                    {
                        command.ExecuteNonQuery();
                        isComplete = true;
                    }
                    catch (SqlException ex)
                    {
                        switch (ex.Number)
                        {
                            // 41302: Updated a record that has been updated since the txn started.
                            case 41302:

                            // 41305: Repeatable read validation failure.
                            case 41305:

                            // 41325: Serializable validation failure.
                            case 41325:

                            // 41301: Commit dependency on a transaction that aborted.
                            case 41301:

                            // 1205: Deadlock (not applicable to in-memory tables, but same principles apply).
                            case 1205:
                                retryCount--;
                                if (retryCount <= 0)
                                {
                                    throw;
                                }

                                // Take a very brief pause -- mostly needed in cases of very high write contention.
                                System.Threading.Thread.Sleep(1);
                                break;

                            default:
                                throw;
                        }
                    }
                }
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }
        }
    }
}

And here is an example of how to call this code.

using (Dal dal = new Dal())
{
    using (SqlTransaction transaction = dal.StartTransaction())
    {
        dal.UpdateProductUnitPrice(productId, newPrice, transaction);
        transaction.Commit();
    }
}

Now if one of these errors is generated during the update, the system will pause for one millisecond (just in case there is a lot of write contention going on; this pause may need to be longer on some systems), and retry the operation up to five times. (Of course, these values should be configurable and not hard-coded.)

There is one HUGE caveat to this, however. When one of these errors is thrown, the entire transaction is rolled back. In this simple example, the transaction is just a single update statement, but if the transaction spans a dozen statements, they all get rolled back.

This means that at the very least the application needs to be aware that the full transaction will go away, and it should wrap transaction appropriately. Better yet, the application will know how to replay the full transaction up to the point of failure and will gracefully retry the operation a given number of times before giving up.

Hekaton Part 6: MVCC Assumptions and Validation

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

I originally intended to make part 6 in this series about error handling, but quickly realized that I needed to lay a bit of a foundation on multi-version concurrency control (MVCC), and particularly some of the assumptions made by MVCC and how those assumptions are subsequently confirmed.

Historically, SQL Server has used a pessimistic form of concurrency. My session sees every other connection as the enemy. Every other connection on the system is presumed to want access to my resources, and so SQL employs a locking mechanism to prevent anyone else from stomping over my work.

Now, locking and isolation levels is a complex topic in itself, but a simple example will suffice for the the purposes of this discussion. Suppose I update the Product table for ID 23, and before I commit my transaction, you come along and try to update the same row. Your connection will be blocked from proceeding until I commit or rollback, at which point you will be allowed to make the update. Under most isolation levels you won’t even be allowed to read the row I’ve updated so long as it is uncommitted.

Blocking is a good thing in that it preserved isolation, but a bad thing when everybody is always waiting on someone else.

In SQL Server 2005, Microsoft introduced a quasi-optimistic concurrency model called snapshot that addressed some of these problems. With snapshot enabled, you would be able to read the row that I’ve updated (you will see the values in the row prior to any of my changes). However, you still won’t be able to update it. You will be blocked just as always.

Snapshot isolation is frequently explained as reader don’t block writers, and writers don’t block readers.

Hekaton takes us a huge step forward toward optimistic concurrency. It makes a couple of assumptions that remove that “every other session is the enemy” paradigm. One assumption is that write conflicts are rare, so SQL always proceeds with data modifications under the assumption that no one else is going to change the same row.

A second assumption is that competing transactions are going be committed and not rolled back.

Now, sometimes these assumptions simply aren’t valid, so SQL Server will includes validation checks to make sure that things don’t go awry.

We’ll get into internals in a later part of this series to better explain how SQL does all this magic, but for now suffice it to say that SQL maintains multiple versions of the same row along with a pair of time stamps indicating when the row is valid. So when we update ProductId 23, SQL will actually create a new row in the table, and mark the original row as valid through, say, 1:17 pm, and the new row only becomes valid at 1:17 pm. When a user starts a new transaction, SQL notes the time that the transaction starts and during the course the transaction will always return the row that was valid and committed at that time.

Let’s demonstrate. We’ll open two tabs in Management Studio, each connected to our Hekaton database. In the first tab, we’ll make sure that the price on product 23 is a known value.

update dbo.Product
set UnitPrice = 23.00
where ProductID = 23;

Now in tab 1, we’ll start a transaction and modify the price.

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 30.00
where ProductID = 23;

In tab 2, let’s examine the value.

select ProductID, UnitPrice
from dbo.Product
where ProductID = 23;

This confirms that connection 2 sees a UnitPrice of 23.00, which is the committed value of the row at the time the transaction started. Continuing in tab 2, start a new transaction:

begin transaction;

Switch back to tab 1 and commit.

commit;

Re-execute the select in connection 2, and you will still get 23.00. Again, this was the committed value of the row at the time the transaction began. But if we close the transaction and do it again:

commit;

begin transaction;

select ProductID, UnitPrice
from dbo.Product with (snapshot)
where ProductID = 23;

commit;

This time we get a unit price of 30.00, reflecting the committed price when the new transaction begins.

So that is MVCC in a nutshell.

What about the validations? If one of the MVCC assumptions doesn’t hold up, SQL Server will detect this circumstance by one of several validation methods. In some cases, these validations happen as the transaction progresses, and in cases the validation happens when the transaction commits.

In connection 1, make sure that there are no open transactions, then run the following:

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 30.00
where ProductID = 23;

And in connection 2 we will try a simultaneous update.

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 35.00
where ProductID = 23;

This immediately fails with an error:

Msg 41302, Level 16, State 110, Line 18
The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
Msg 3998, Level 16, State 1, Line 16
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
The statement has been terminated.

I find the wording on the 41302 error a bit odd because it’s not completely correct, but the upshot is that there are two simultaneous update operations in progress. The “write-write conflict” assumption has failed, so the error is generated. Note that SQL Server does not block the progress of the transaction, as with previous versions, but it does generate an error.

In addition to the 41302 error, we might also get 41303 and 41325 errors. These relate to violations of the repeatable read and serializable isolation level requirements. Make sure that both connections don’t have open transactions, then run this in connection 1:

begin transaction;

select ProductID, UnitPrice
from dbo.Product with (repeatableread)
where ProductID = 23;

Then back in connection 2 change the price:

begin transaction;

update dbo.Product with (snapshot)
set UnitPrice = 38.00
where ProductID = 23;

commit;

Now try to commit transaction 1:

commit;

At commit time, SQL Server will validate the repeatable read requirement and the following error will be generated.


Msg 41305, Level 16, State 0, Line 25
The current transaction failed to commit due to a repeatable read validation failure.

Finally, we can create the conditions for a 41325 error. Commit any open transactions, then in connection 1 run:

delete dbo.Product where ProductID = 1000;

begin transaction;

select ProductID, UnitPrice
from dbo.Product with (serializable)
where ProductID = 1000;

And in connection 2 run:

insert dbo.Product (ProductID, Description, UnitPrice)
values (1000, 'Product 1000', 1000.00);

Back in connection 1:

commit;


Msg 41325, Level 16, State 0, Line 35
The current transaction failed to commit due to a serializable validation failure.

Whew, that was a lot just to talk about the validations that take place (and there is actually one more validation that happens at commit time, but we’ll address that separately). With that background, we can now talk about error handling in the next post.

Hekaton Part 5: Native Procedures

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

Now we come to one of the Hekaton components that is really quite cool but that I haven’t found a practical use for. Not even once.

Natively compiled stored procedures.

The cool part is that they can be really, really fast. They can be orders of magnitude faster than traditional interpreted procedures. The not so cool part is that they support only a very, very limited subset of T-SQL.

Let’s demonstrate. First, we’ll create a new table and add a couple of rows.

if exists (select * from sys.tables where name = 'Product')
	drop table dbo.Product;
create table dbo.Product
(
	ProductID int not null,
	Description nvarchar(500) not null,
	UnitPrice money not null,

	primary key nonclustered hash (ProductID) with (bucket_count = 1048576),
	index idx_Product__UnitPrice nonclustered (UnitPrice)
) with (memory_optimized = on, durability = schema_and_data);

-- Populate a few rows into the table

insert dbo.Product (ProductID, Description, UnitPrice)
values (1, 'Gizmo', 34.00),
	(2, 'Whatzit', 16.00);

Now can create a natively compiled stored procedure to update the table. This isn’t going to demonstrate speed, but it’s just to show the general idea (and the general awkwardness of the syntax).

if exists (select * from sys.objects where type = 'P' and name = 'usp_UpdateProduct')
	drop procedure usp_UpdateProduct
go
create procedure dbo.usp_UpdateProduct (@ProductID int, @NewUnitPrice money)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	update dbo.Product
	set UnitPrice = @NewUnitPrice
	where ProductID = @ProductID;
end
go

We can demonstrate that the procedure really works by running something like.

select ProductID, Description, UnitPrice from dbo.Product where ProductID = 1;
exec dbo.usp_UpdateProduct @ProductID = 1, @NewUnitPrice = 42.00;
select ProductID, Description, UnitPrice from dbo.Product where ProductID = 1;

Now let’s write a procedure to add a bunch of new rows. First, we will create and populate a tally table.

create table Tally
(
    Number int not null,
    constraint PK_Tally primary key nonclustered
        (Number)
) with (memory_optimized = on, durability = schema_and_data);

insert Tally (Number)
select top 1000000 row_number() over (order by v1.number)
from master.dbo.spt_values v1 cross join master.dbo.spt_values v2;

And then try this for a stored procedure:

if exists (select * from sys.objects where type = 'P' and name = 'usp_CreateNewRecords')
	drop procedure usp_CreateNewRecords
go
create procedure dbo.usp_CreateNewRecords (@RecordCount int)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	declare @firstId int;
	select @firstId = isnull(max(ProductID), 0) from dbo.Product;

	insert	dbo.Product (ProductID, Description, UnitPrice)
	select	t.Number + @firstId ProductID,
			'Product ' + cast(t.Number + @firstId as nvarchar(500)) Description,
			cast(t.Number + @firstId as money) UnitPrice
	from	dbo.Tally t
	where	t.Number <= @RecordCount;
end
go

Then we’ll run the procedure to add 1,000,000 records:

exec dbo.usp_CreateNewRecords @RecordCount = 1000000;

This goes pretty fast on my machine, consistently adding the 1M records in 5 to 6 seconds.

I wrote the procedure the way I did because I’ve always been taught (any experience has validated) that set-based logic is almost always superior to row-by-row processing. Hekaton definitely changes that paradigm. Let’s rewrite that procedure to do one-off inserts.

if exists (select * from sys.objects where type = 'P' and name = 'usp_CreateNewRecords')
	drop procedure usp_CreateNewRecords
go
create procedure dbo.usp_CreateNewRecords (@RecordCount int)
with native_compilation, schemabinding, execute as owner
as
begin atomic with (transaction isolation level = snapshot, language = 'us_english')
	declare @firstId int;
	select @firstId = isnull(max(ProductID), 0) from dbo.Product;

	declare @counter int = 1;
	while @counter <= @RecordCount
	begin
		insert dbo.Product (ProductID, Description, UnitPrice)
		values (@firstId + @counter,
			'Product ' + cast(@firstId + @counter as nvarchar(500)),
			cast(@firstId + @counter as money));
		select @counter = @counter + 1;
	end
end
go

Now that insert runs in 3 to 4 seconds. Not a dramatic difference, but intriguing to me because it seems to be a game-changer.

Finally, the limitations. This is why I’ve not found natively compiled procedures to be terribly compelling, because I am constantly running up against some limitation. The Microsoft master In-Memory Limitations page lists them. Scroll down to the Natively Compiled Stored Procedures section, which occupies the majority of the page, and you’ll see the long list. I won’t repeat them all here, but the big ones for me are lack of support for CTEs and subqueries, CASE statements, temp tables, non-BIN2 limits, lots of unsupported join types, no IN or OR or LIKE, no DISTINCT and limitations on aggregations, no ROW_NUMBER(), and of course no references to disk-based tables.

Whew! Even my abbreviated list is pretty long!

Hekaton Part 4: Isolation Levels

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One thing to keep in mind about querying Hekaton tables is that the isolation level you use is quite important. Queries must be run at the snapshot, repeatable read or serializable isolation level. In the previous post, we executed a select query. Let’s add an explicit isolation level.

set transaction isolation level read committed;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

And this query runs just fine. We execute at the read committed level using snapshot isolation.

But change that “read committed” to just about anything else (read uncommitted, repeatable read) and the statement will produce an error.

Msg 10794, Level 16, State 80, Line 3
The transaction isolation level 'READ UNCOMMITTED' is not supported with memory optimized tables.

Msg 41333, Level 16, State 1, Line 3
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Msg 41333, Level 16, State 1, Line 3
Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

Things also start to break down when you use an explicit transaction (instead of the autocommit transaction above).

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Msg 41368, Level 16, State 0, Line 5
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

The error message give the solution: use the with (snapshot) table hint.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee with (snapshot)
order by LastName, FirstName;

commit;

There is a database-level setting to allow omitting the table hint.

alter database Hekaton
set memory_optimized_elevate_to_snapshot = on;

And now the table hint can be left out of the query.

set transaction isolation level read committed;

begin transaction;

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

commit;

Things get a bit more interesting when queries access both Hekaton tables and disk-based tables. Suppose we have an on-disk Customer table and we want to note which Employees have certain relationships to a given Customer.

create table CustomerEmployeeRelationship
(
	RelationshipId int not null identity(1,1) primary key clustered,
	CustomerId int not null,
	EmployeeId int not null,
	RelationshipTypeId int not null
);

Side note: Trying create a foreign key back to the Employee table won’t work, another limitation of Hekaton tables.

alter table dbo.CustomerEmployeeRelationship
add constraint fk_CustomerEmployeeRelationship_EmployeeId
foreign key (EmployeeId) references dbo.Employee (EmployeeID);


Msg 10794, Level 16, State 10, Line 57
The feature 'FOREIGN KEY' is not supported with memory optimized tables.
Msg 1750, Level 16, State 0, Line 57
Could not create constraint or index. See previous errors.

Let’s find customer-employee relationships of a certain type:

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;

That works fine and well, but some combinations of isolation levels don’t mix and match.

set transaction isolation level repeatable read;

select rel.CustomerId, rel.EmployeeId
from dbo.Employee emp with (repeatableread)
inner join dbo.CustomerEmployeeRelationship rel
on emp.EmployeeID = rel.EmployeeId
where rel.RelationshipTypeId = 7;


Msg 41333, Level 16, State 1, Line 74
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

Hekaton Part 3: Querying In-Memory Tables

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So now that we’ve created our Hekaton table, we should be able to query and modify it just like any other table, right?

Actually, yes. OK, so there are a few limitations, but by and large this is the part of Hekaton that is the most fully supported part of the product.

insert dbo.Employee (EmployeeID, FirstName, LastName, Salary)
values
(1, 'Marie', 'Salazar', 106779.90),
(2, 'Jason', 'Brown', 85553.93),
(3, 'Felicia', 'Normandin', 52278.50),
(4, 'Peter', 'Sabatino', 55018.27),
(5, 'Gregory', 'Mannan', 66715.94);

Selecting from the data works well.

select EmployeeID, FirstName, LastName, Salary
from dbo.Employee
order by LastName, FirstName;

The data can be modified just like normal.

update dbo.Employee
set Salary = 57219.00
where EmployeeID = 4;

The table can be joined to other tables, whether another Hekaton table or a good old-fashioned disk table, so long as the other table in in the same database. If you try to cross over to a different database, you get this error:

Msg 41317, Level 16, State 1, Line 20
A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user database or databases model and msdb, and it cannot write to master.

Some constructs aren’t supported. This MERGE statement is valid for a disk-based table.

merge dbo.Employee emp
using (values (5, 'Gregory', 'Mannan', 69384.58), (6, 'Michelle', 'Irvin', 80221.66) )
as src (EmployeeID, FirstName, LastName, Salary)
on emp.EmployeeID = src.EmployeeID
when matched then
	update set FirstName = src.FirstName,
		LastName = src.LastName,
		Salary = src.Salary
when not matched by target then
	insert (EmployeeID, FirstName, LastName, Salary)
	values (src.EmployeeID, src.FirstName, src.LastName, src.Salary);

But for a Hekaton table we get an error:

Msg 10794, Level 16, State 100, Line 25
The operation 'MERGE' is not supported with memory optimized tables.

(However, using a Hekaton table in a MERGE statement but not as the target is OK.)

truncate table dbo.Employee;

Msg 10794, Level 16, State 92, Line 37
The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

So yeah, a few limitations on querying, but most things work just fine.

Hekaton Part 2: Table Limitations

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

One the big bummers about the first version of Hekaton is the limitations, which are legion. Microsoft has the complete list, but here are the big ones as far as I’m concerned.

Once you’ve created a table structure, it can’t be modified. No new columns, no new indexes. If you need to change the schema, you need to copy out the data somewhere else, drop and recreate the table with the modified structure, then copy the data back. This is tedious at best for small data sets, but a possible killer for larger tables.

A table cannot have more than 8 indexes. It need to have at least one index, a primary key, but otherwise none of the indexes can be unique. Also, you cannot update the primary key columns. Oh, and if a index is sorted ascending, SQL can only use that index in ascending mode. If queries need descending order, you’ll have to create a second index on the same keys, but in the reverse order.

There is a hard limit of 8,060 bytes per row, enforced at table create time. Disk-based tables had a similar limitation at one point, but I sure have liked things since that went away.

As a natural extension of the 8,060 byte limit, tables cannot have LOB columns.

No foreign keys. No check constraints.

Any string columns in an index must be in a BIN2 collation. This is a big one, in my experience. We’ve come to expect certain behavior from certain collations, and not having this behavior has been a deal-breaker in a number of circumstances when I’ve considered using an in-memory table.

A note about indexes that I didn’t cover before is that are two types of indexes. One is the hash index, and you would create one by simply declaring a nonclustered hash index, and include a bucket count based on how many items you expect the index will ultimately hold. Hash indexes are more useful if the query specifies an equality.

index idx_Product__UnitPrice nonclustered hash (UnitPrice)
with (bucket_count = 1048576)

Range indexes are useful for finding ranges of data, such as when the query uses an inequality or a range of values. To create a range index, omit the HASH keyword.

index idx_Product__UnitPrice nonclustered (UnitPrice)

Hekaton Part 1: Getting started

One in a series of posts about Microsoft’s In-Memory OLTP Functionality

So, back in SQL Server 2014, Microsoft introduced a new technology by the name of In-Memory OLTP.  It was code named Hekaton, which is a much cooler name.  And shorter.  And easier to say.  So I’m just going to keep calling it Hekaton.

This post is part one of what I expect will be a long series of brain dumps on how to use Hekaton as well as some of the internal that I have picked up over the past couple of years.  With the run-up to SQL Server 2016, we’ll also see some pretty good enhancements, so I’ll address these as well in due course.

Back when Hekaton was first announced (and once I started to wrap my head around it), I thought it was the coolest thing ever, and that it was going to revolutionize SQL Server.  Once it came out, it was quickly appparent that it was a “version 1” product and that there were just too many limitations around it to be truly useful from the get-go.

So it didn’t exactly set the world on fire, and even with many improvements coming in “version 2,” it still has a ways to go.  I am still firmly of the belief that the day will come when in-memory technology will become the de facto standard for new tables.

So, with that background and that hope for the future, let’s start with how to enable Hekaton at the database and how to create a new in-memory table.

create database Hekaton
on primary (name = 'Hekaton',
	filename = 'D:\sql\data\Hekaton.mdf',
	size = 25600KB, filegrowth = 5120KB),
filegroup InMemoryFileGroup contains memory_optimized_data
	(name = 'InMemoryFiles',
	filename = 'D:\sql\HekatonInMemoryFileGroup')
log on (name = 'Hekaton_log',
	filename = 'D:\sql\log\Hekaton_log.ldf',
	size = 10240KB, filegrowth = 10240KB);
use Hekaton;

create table Employee
(
	EmployeeID int not null,
	FirstName varchar(50) not null,
	LastName varchar(50) not null,
	Salary money not null,
	constraint PK_Employee primary key nonclustered
		(EmployeeID)
) with (memory_optimized = on, durability = schema_and_data);