TempDB memory leak?

I found a bug where I’m seeing TempDB use more memory than it should on multiple versions of SQL Server, especially on servers set up with common best practices. There’s a workaround that has a profound affect on server performance, adding to my belief that this is a legitimate bug and leading me to open a Connect Item on the issue.

Querying the Buffer Pool

I have a query to show me what’s in the buffer pool for the entire server, showing the expected results as well as excessive space being used by TempDB.  The newest version showing these details is on my post Querying the Buffer Pool.

It shows number of pages in the buffer pool grouped by the database, table, and index.  The query makes use of left joins so it can see space in memory that’s not currently allocated to a specific object.

The results are surprising in many ways.

The good surprises are seeing what indexes are hogging up your buffer pool so you have an idea of where to start tuning.  I’m a huge fan of this and have blogged about it in Cleaning Up the Buffer Pool to Increase PLE, although the name of my older post is misleading because it does more than just help memory management in SQL Server.

The Bug

The bad surprise was a bug which has been harassing me for quite some time now.  As I mentioned, the query will return all the space in the buffer pool, specifically the contents of sys.dm_os_buffer_descriptors, and does a left join to the tables leading up to and including sys.indexes so space not currently allocated to a table will show up.  The problem is that the space that shows up as unallocated for TempDB is much larger than expected, in this case taking up 1/3 of my buffer pool.

QueryBufferPool_TempDB

On this post I’m talking about a single server, but the problem wasn’t limited to a single server.  It showed up at the same time, caused by the same change (implementing a common best practice), partially resolved by the same partial rollback (undoing the best practice) on SQL 2008 R2, SQL 2012, and SQL 2014.

Details About the Bug

So the query I have on yesterday’s post, Querying the Buffer Pool, showed I had unallocated space in TempDB in memory, and a lot of it.  However, it doesn’t show details.

To start looking at the details, what kind of pages are these that exist in sys.dm_os_buffer_descriptors, but not in sys.allocation_units?

SELECT bd.page_type
	, MB = count(1) / 128
FROM sys.dm_os_buffer_descriptors bd
	LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = 2 --TempDB
	AND bd.is_modified = 0 --Let's not play dirty, only clean pages
	AND au.allocation_unit_id IS NULL --It's not even allocated
GROUP BY bd.page_type 
ORDER BY 2 DESC

TempDB_BufferPool_Unallocated

Ok, so we’re dealing with typical data in TempDB.  Well, other than it not being allocated, of course.

So I run another query to get more details.  This time I want to look inside the pages to see if they tell a different story.

SELECT TOP 100 bd.*
FROM sys.dm_os_buffer_descriptors bd
	LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = 2 --TempDB
	AND bd.is_modified = 0 --Let's not play dirty, only clean pages
	AND au.allocation_unit_id IS NULL --It's not even allocated

TempDB_PageLevel_Unallocated

Then I follow that up with Paul Randal’s How to use DBCC PAGE, which comes with all the disclaimers about using an undocumented and unsupported trace flag and command.  This one isn’t horrible in my mind or Paul’s comments, but remember the undocumented and unsupported parts.

DBCC TRACEON (3604);

DBCC PAGE (2, 5, 502219	, 0)
DBCC PAGE (2, 5, 374929	, 0)
DBCC PAGE (2, 5, 69868	, 0)
DBCC PAGE (2, 5, 453687	, 0)
DBCC PAGE (2, 5, 214988	, 0)
DBCC PAGE (2, 5, 440966	, 0)

DBCC TRACEOFF (3604);

The results all looked about the same to me.

DBCC_Page_Results

There are several important parts to me.  The m_objId is a negative value I can’t find in TempDB.sys.objects, so it WAS a temporary object that no longer exists.  Across the board, these are “NOT ALLOCATED”, “NOT CHANGED”, “NOT MIN_LOGGED”, “0_PCT_FULL”, so there’s nothing there.

To me it looks like temp objects made it into memory and remained in memory after the temporary objects were dropped.  I have no idea what objects these were or how they were dropped, but I’m imagining these were temp tables automatically dropped when the session was either closed or reset.

A Recent Change (A CLUE)

I found this by noticing that PLE for several servers was lower now than it has been in the past, so I was peeking in the buffer pool to see who was playing nice.  Going off of “when did PLE start to be lower” I noticed that I implemented a change around that time to use a common best practice.

That change was presizing TempDB data files to take up a vast majority of the dedicated LUN instead of letting them grow as needed.  It avoids waiting for file growth, especially if you’re using TDE (I’m not) and can’t use IFI (I can), but for several other reasons as well, including file fragmentation and the slight pause even IFI causes.  So at the start of all these festivities, I took the 4 TempDB data files from 100 MB each to 12 GB each, using up 48 GB of the 50 GB available.

A Workaround

Seeing this, I wanted to partially roll back the change the next opportunity I had.  100 MB was too small and I was aware that it invoked file growths every month (we reboot monthly for OS updates).  48 GB wasn’t right though, we just have that much space on the drive due to server build standards and paranoia (I’m a DBA).  So I went through our Idera Diagnostic Manager monitoring software and found the most space TempDB used, which is captured once an hour.  I found that 4.8 GB was the peak usage with several incidents of usage going over 4.5 GB.

With that information available and still not wanting an autogrowth for all the reasons listed above, I decided that all 4 files should be 1.5 GB, so 6 GB total.  That means peak usage was about 75% full, leaving plenty of room for error, especially with my baseline only being captured once an hour.  Autogrowth is set to 256 MB, so it’d add 1 GB total each growth.  I can live with that.

I can’t say it eliminated the issue because I still have 2 GB of unallocated TempDB space in cache, but it’s better than 8 GB.  It can be considered more acceptable than other issues I need to tackle right now, but it still bugs me.

What’s the Best Practice?

It’s a best practice to have TempDB data files on their own LUN, drive, array, however you want to word it.  Then it just make sense to have the total size of your data files add up to 90% or more of the drive size.  I see this advice everywhere, with these two standing out:

  • Solar Winds – Configuration Best Practices for SQL Server Tempdb–Initial Sizing
    • “Next, if you can give tempdb its own disk, then configure it to almost fill the drive. If nothing else will ever be on the drive, then you’re better off setting it to be larger than you’ll ever need. There’s no performance penalty, and you’ll never have to worry about autogrow again.”
  • Brent Ozar – SQL Server 2005/2008/2012/2014 Setup Checklist
    • “Notice that I don’t have filegrowth enabled.  You want to proactively create the TempDB files at their full sizes to avoid drive fragmentation.”

Jonathan Kehayias does it a little bit differently in his post SQL Server Installation Checklist saying to add space to TempDB files in 4 GB increments.  Although he doesn’t fill the drive by default, this isn’t mentioned by him, either.

Now I need to be perfectly clear on this, I trust these three sources.  I trust Jonathan and Brent more than I trust myself with setting up SQL Server.  I also feel the same about the authors I know on the Solar Winds post.  This does not change that.

Sizing TempDB like that often means it’s much larger than you need.  The workaround I’m using is to right-size these files instead.  For me, for now, I’m going to stick with seeing how large TempDB gets and make it slightly larger than that until I have a solid answer to my problem.

What Was It?

I still don’t know.  The workaround managed to knock it off of my priority list enough where I’m not actively working on it.  However, my drive to understand SQL Server better won’t leave me alone.

This post is my solution.  I have some very intelligent people reading this who I hope will at least lead me further down the rabbit hole, even if they don’t have a conclusive answer.  There’s a good chance I’ll be asking for help on Twitter with #sqlhelp or opening a connect item on this, for which I have a very well documented description of the issue that I can link to.

Updates:

2016-01-06 – Caching of Temporary Objects

Due to a comment, I started looking into the caching of temporary objects to see if this was the root cause.  The comment specifically mentioned Paul White’s (b|t) post Temporary Object Caching Explained, and I also read over Itzik Ben-Gan’s (b|t) post Caching Temporary Objects.

Both of these left me with the impression that smaller amounts of data would be left in the cache linked to temporary objects linked to the proc cache.  What I’m seeing is large amounts of data in the buffer pool that did not drop when I ran DBCC FREEPROCCACHE (on a test server that wasn’t in active use) as I expected if this was the full explanation.

While it’s very likely this is related to the issue on hand, I’m not ready to accept it as a full explanation.  If the memory associated with TempDB dropped when clearing the proc cache (on a test server) then it would have been a great explanation with a poor side effect of going too far with the memory being used.

2016-01-07 – Opened a Connect Item

I mentioned this issue on the comments of Paul White’s blog post mentioned in the last update and comments below on this post.  His response concluded with this:

So, when memory pressure is detected, I would expect memory use like this to be freed up for reuse in pretty short order, by design. If it is not, and bad things happen because memory for unallocated tempdb is not released/reused, that would be a bug.

While I was already leaning that way, it pushed me over the edge to decided it was time to open up a connect item on this issue.  I feel it’s well worth the read going to Paul’s post and the connect item.  Also, if you’re seeing this as well, an upvote on connect is very appreciated.

https://connect.microsoft.com/SQLServer/feedback/details/2215297

Query the Buffer Pool

DBAs are known for asking for more memory, but often can’t say what’s in memory.  While I agree that many database servers can use more memory, I’m a firm believer in knowing how you’re using your resources before asking for more.  The script below allows me to do just that.

What It Returns

This will return every index that is using at least 1 MB of memory for every database on your server.  It also returns space in memory that is associated with unallocated space in the tables which shows up as NULL for everything except the size of the space and the table name.

I’ll warn you now that the unallocated space can be surprisingly high for TempDB, and I talk about that in TempDB Memory Leak?.  Hopefully we can get a good comment thread going on that post to talk through what we’re seeing and how common the issue really is.

The Script

IF OBJECT_ID('TempDB..#BufferSummary') IS NOT NULL BEGIN
	DROP TABLE #BufferSummary
END

IF OBJECT_ID('TempDB..#BufferPool') IS NOT NULL BEGIN
	DROP TABLE #BufferPool
END

CREATE TABLE #BufferPool
(
	Cached_MB Int
	, Database_Name SysName
	, Schema_Name SysName NULL
	, Object_Name SysName NULL
	, Index_ID Int NULL
	, Index_Name SysName NULL
	, Used_MB Int NULL
	, Used_InRow_MB Int NULL
	, Row_Count BigInt NULL
)

SELECT Pages = COUNT(1)
	, allocation_unit_id
	, database_id
INTO #BufferSummary
FROM sys.dm_os_buffer_descriptors 
GROUP BY allocation_unit_id, database_id 
	
DECLARE @DateAdded SmallDateTime  
SELECT @DateAdded = GETDATE()  
  
DECLARE @SQL NVarChar(4000)  
SELECT @SQL = ' USE [?]  
INSERT INTO #BufferPool (
	Cached_MB 
	, Database_Name 
	, Schema_Name 
	, Object_Name 
	, Index_ID 
	, Index_Name 
	, Used_MB 
	, Used_InRow_MB 
	, Row_Count 
	)  
SELECT sum(bd.Pages)/128 
	, DB_Name(bd.database_id)
	, Schema_Name(o.schema_id)
	, o.name
	, p.index_id 
	, ix.Name
	, i.Used_MB
	, i.Used_InRow_MB
	, i.Row_Count     
FROM #BufferSummary AS bd 
	LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
	LEFT JOIN sys.partitions p ON (au.container_id = p.hobt_id AND au.type in (1,3)) OR (au.container_id = p.partition_id and au.type = 2)
	LEFT JOIN (
		SELECT PS.object_id
			, PS.index_id 
			, Used_MB = SUM(PS.used_page_count) / 128 
			, Used_InRow_MB = SUM(PS.in_row_used_page_count) / 128
			, Used_LOB_MB = SUM(PS.lob_used_page_count) / 128
			, Reserved_MB = SUM(PS.reserved_page_count) / 128
			, Row_Count = SUM(row_count)
		FROM sys.dm_db_partition_stats PS
		GROUP BY PS.object_id
			, PS.index_id
	) i ON p.object_id = i.object_id AND p.index_id = i.index_id
	LEFT JOIN sys.indexes ix ON i.object_id = ix.object_id AND i.index_id = ix.index_id
	LEFT JOIN sys.objects o ON p.object_id = o.object_id
WHERE database_id = db_id()  
GROUP BY bd.database_id   
	, o.schema_id
	, o.name
	, p.index_id
	, ix.Name
	, i.Used_MB
	, i.Used_InRow_MB
	, i.Row_Count     
HAVING SUM(bd.pages) > 128  
ORDER BY 1 DESC;'  

EXEC sp_MSforeachdb @SQL

SELECT Cached_MB 
	, Pct_of_Cache = CAST(Cached_MB * 100.0 / SUM(Cached_MB) OVER () as Dec(20,3))
	, Pct_Index_in_Cache = CAST(Cached_MB * 100.0 / CASE Used_MB WHEN 0 THEN 0.001 ELSE Used_MB END as DEC(20,3))
	, Database_Name 
	, Schema_Name 
	, Object_Name 
	, Index_ID 
	, Index_Name 
	, Used_MB 
	, Used_InRow_MB 
	, Row_Count 
FROM #BufferPool 
ORDER BY Cached_MB DESC

Where’d the Script Come From

I’ve had a script similar to this one around for a while.  It’s originally based off of Jonathan Kehayias’s script on his post Finding What Queries in the Plan Cache Use a Specific Index, and I couldn’t have done this without having his script to start with.

Then I originally posted a version of this script on my post Cleaning Up the Buffer Pool to Increase PLE, which was great to see the index usage for a single database.  It runs slower than this, only returns a single database, and does not show unallocated space in memory.  Those changes warranted either an update to that post or a completely new post…I opted for the latter.

What It Means

Now you can see what’s in your memory. Hopefully you’ll see one or two things that stand out on here that don’t make sense; those are your easy tuning opportunities.

If an index is 100% in cache then you’re scanning on it, and that may be an issue.  Yes, you can find when you did scans on indexes using the scripts in my Indexes – Unused and Duplicates post, but it helps to have another view of what that means in your memory.

One thing the index monitoring scripts in the post I just mentioned can’t do is tell you when you’re doing large seeks as opposed to small seeks.  With the typical phone book example, you could ask for all the names in the phone book where the last names begins with anything from A to Y, giving you 98% of the phone book as results.  Index usage stats will show you did a seek, which sounds efficient.  The script on this post will show that you have 98% of your index in cache immediately after running the query, and that gives you the opportunity to find the issue.

When you see an index that looks out of place here, dive back into the scripts on Cleaning Up the Buffer Pool to Increase PLE to see what’s in cache using that index.  If the query isn’t in cache for any reason, you may be able to look at the last time the index had a scan or seek against it in sys.dm_db_index_usage_stats and compare that to results from an Extended Events session you had running to see what it could have been.

The main point is that you have something to get you started.  You have specific indexes that are in memory, and you can hunt down when and why those indexes are being used that way.  It’s not always going to be easy, but you have a start.

We’re All On a Budget

It’s not too uncommon for this process to end in asking for more memory, and I view memory like being on a budget.  The amount of memory you have right now is your current budget.  Asking for more memory should be viewed like asking for more money in a financial budget.  For a financial budget increase, here are the questions I’d be prepared to answer:

  1. What did you spend the money we already gave you on?
  2. Did you spend that money as efficiently as possible?
  3. What else do you want to spend money on?

Now you can answer these questions in database form:

  1. Here’s what I have in cache at multiple times, specifically right after PLE dropped.
  2. I went through the queries that pulled the data into cache and tuned what I could.
  3. When I checked what’s in cache multiple times, these indexes fluctuated a lot in how much was in there.  I believe adding more memory would allow them to stay in cache instead of kicking each other out to make room.

Be it Virtual or Physical environments, there’s only so much memory that can be made available to us.  We’re on budgets of how much memory the host has, how many memory slots a server has, and how large the memory chips are that those slots can handle.  Prove you’re doing it right and it’s a lot harder to say no to you.

I have an odd habit of getting the memory I ask for because I answer these questions up front in the initial request for memory.

Thank You for More Than You Realize

Blogging and presenting have changed me for the better, and in ways I didn’t see coming.  However, I know I wouldn’t have had them if it wasn’t for three things.  Getting it all started, having help getting established, and having an audience for my work were supported by several groups of people, with a couple people really standing out.

Getting it started

This all started because Tom Zimmerman noticed I had an answer for everything, even if I didn’t know it right away.  I learned which bloggers and forums to trust, and would look up any issue quickly and easily.  However, he pointed out that I was a “leech”, always taking and never contributing.

Tom was right, so I eventually started what I thought was an altruistic path to find ways to contribute.  I settled on blogging even though I thought it would be complex to set it up and maintain.  Although it turned out it was as easy as writing an email, I wouldn’t have gotten past the hurdle of starting if it wasn’t for Tom.

Thank you, Tom.

Establishing the blog

So I created my blog, decided that wording things towards more junior level DBAs was the best thing for me and an area that could use more content, and was on my way.  However, I didn’t know how to promote myself, get people to read what I wrote, or really much of anything.  If only a couple people were reading it then I would have given up, quickly.

To get established, as I blogged in the past, I was very fortunate to come across Brent Ozar’s blogs on bogging and presenting.  To me it’s even more significant than his work blogging SQL Server.  Some of the stuff he talked about:

  • Having your blog reach established audiences.
  • Improve search engine results with SEO.
  • Writing consistently to establish your own audience.
  • Picking an audience and talking to a person (2010 version of me).

People were reading my syndicated work from the start, and some of my posts were creeping their way up in the search engines to get readers there, too.  My words were being heard, and I needed that to keep going.

That part worked but he was also talking about presenting.  How to do it, how to get better at it, where to do it, and how it changes you.  Well, if one part worked for me, lets try the next part.  That, too, worked out rather well.

Thank you, Brent.

Having an audience

So I mentioned syndicating my blog and doing presentations, but those both required an established audience where unknown people were accepted and encouraged to participate.  For that I found a couple places.

SQL Server Central and Toad World (formerly SQLServerpedia.com) were the two places to syndicate that I found very early on, and to this day the only two I use.

SQL Server Central is the more important of the two for me for several reasons.  I used and abused this site in the past to the point I was called a leech, so it was an honor to be there.  Then they do daily and weekly emails highlighting blogs and articles with both of them selecting a couple blog posts syndicated to SQL Server Central.  To this day it makes my day seeing my work on there.  The vote of confidence and boost in readers is a real boost to my morale and dedication to write more, better content.

You can’t discount Toad World which is slow to get reads, but gets many of them over time.  It’s a lot like the SEO work for search engines where you need to do good work and be patient.  I didn’t start with patience (still not doing too well there, either), but I’m very glad I have this going as well.

Then there were presentations, which I jumped right into doing at SQL Saturdays without ever presenting to a user group first.  I didn’t do too well the first time or two, but I’m also an introvert to the point that my oldest brother told me he would have bet money against me publically speaking.  However, the audience was there, the organizers encouraged new people to participate, and I was able to refine myself to the point of standing toe-to-toe with names I’ve known for years.  Better yet, now they’re not names to me, they’re people I’ve met and continue to know better.

Yeah, you know that these things started somewhere by a couple people, but I never put much thought into that.  Not until I read a post thanking Steve Jones.  SQL Server Central and SQL Saturday, the two places I thank the most for hooking me up with an audience, were both started by the same person.  Sure, he had help, such as Andy Warren and Brian Knight for SQL Saturday, but he was key in starting two organizations that are still key to me.

Thank you, Steve.

Skipping people

Oh the people I would name if I thought I could name them all.  Coworkers, mentors, SQL Saturday volunteers, bloggers, presenters, readers, attendees, and more should all be mentioned.  Even if I was overconfident enough to think I could name them all, the readers would move along like people in a theater during the credits.  To keep it short and simple….

Thank you.

Where am I now?

I am SOOO glad that I had all of this established.  This past year has been the worst.  I wish that last sentence needed a filter or something saying it was the worst for certain areas, but it was pretty all-inclusive.  Work, home, life in general beat me down to nothing and I needed to be picked back up.  I didn’t blog for 17 months, skipped submitting to some SQL Saturdays I love, and in general completely fell apart.

When I started to get it back together, I had some things established that helped more professionally than I could have ever hoped for.  My blog was still pulling in views, especially with being in the top 5 search results for a common search term on Google.  I didn’t really have an established audience for my blog because I was inconsistent before I took 17 months off, but it was syndicated to an audience happy to see me return.  SQL Saturdays were coming up waiting for an abstract.

My confidence was just waiting for me to say that I’m back.  So, confidence, I’m back.

Now I’m ending the year with a blog that has 125,000 lifetime views not counting syndication that takes it closer to a total of 200,000.  I’m also pulling together a presentation for SQL Saturday Cleveland that I’ll blog about in January, and I’ll pin their speaker evaluation forms on my cube wall, too.

I have goals of blogging better to make a bigger difference for the readers, blogging more consistently to earn followers, and do more with presentations to reach more people with better content.  The details I have for these goals that I didn’t list here aren’t easy, but it’s not easy to describe what these accomplishments do to a person, either.

I understand what it really means to me, especially when I’m down.  Hopefully, I’ll never need it like that again, but I’m so glad all of this was there when I did.

Thank you.

Advent of Code – Day 4

Day 4 of the Advent of Code has us mining for data in MD5 hashes, which is out of the comfort zone for a SQL DBA to say the least.

Some would say it’s a poor use for SQL Server, and I’d have to agree.  However, sometimes things just need to get done, so I’m playing along with a single tool approach.

Now SQL Server isn’t supposed to do well with row-by-row processing, right?  Andy Warren was posing the same question in his post pondering possible solutions to this exact problem. Well…the problem is that row-by-row processing is typically referencing a table every row.  Earlier today I mentioned that I didn’t have a good example of set based being slower, but now I found a way.  Sure, some could say that it’s poor examples on poor ways to use SQL Server, but come on…we can all find production code that meets that criteria.

So, typical DBA fashion, I made it set-based evaluating values in batches of up to 50,000.  It worked.

Here’s my code for Part 2.  It’s so close to Part 1 that you’ll have trouble telling the difference.

IF OBJECT_ID('TempDB..#Numbers') IS NOT NULL BEGIN
	DROP TABLE #Numbers 
END

CREATE TABLE #Numbers
(
	ID INT IDENTITY(1,1) PRIMARY KEY
	, b BIT
)

DECLARE @StartString varchar(100) = 'iwrupvqb'
DECLARE @RowCount Int 

INSERT INTO #Numbers (b)
SELECT 0

SELECT @RowCount = @@RowCount 

WHILE 0 = (SELECT COUNT(1) 
			FROM (SELECT TOP (@RowCount) ID 
					FROM #Numbers 
					ORDER BY ID DESC
				) X 
			WHERE CONVERT(VarChar(10), HASHBYTES('MD5', @StartString + CAST(ID as VarChar(1000))), 2) LIKE '000000%') 
	BEGIN

	INSERT INTO #Numbers (b)  
	SELECT TOP (50000) b
	FROM #Numbers
	
	SELECT @RowCount = @@RowCount 
END

SELECT ID = MIN(ID)
	, MD5_Hash = CONVERT(VarChar(1000), HASHBYTES('MD5', @StartString + CAST(min(ID) as VarChar(1000))), 2) 
FROM (SELECT TOP (@RowCount) ID 
		FROM #Numbers 
		ORDER BY ID DESC
	) X 
WHERE CONVERT(VarChar(10), HASHBYTES('MD5', @StartString + CAST(ID as VarChar(1000))), 2) LIKE '000000%'

Then I realized I didn’t even need a table and just ran the code. As you can see, the code was much easier to write, and it also worked. More importantly, it worked in 36 seconds as opposed to 113 seconds in the set-based approach for part 2. Part 1, in case you’re wondering was 1 second compared to 3 seconds, so the non-set based was still about 3 times faster.

DECLARE @i Int = 0
	, @StartString varchar(100) = 'iwrupvqb'

WHILE CONVERT(VarChar(10), HASHBYTES('MD5', @StartString + CAST(@i as VarChar(1000))), 2) NOT LIKE '000000%' BEGIN
	SET @i = @i + 1
END

SELECT @i 

I’m saving the link to my own post to give to others as an example of when set-based is worse. Hint: it’s ONLY because I wasn’t referencing a table in the loop, and I could probably still find a way to make it more efficient in a set.  It’s still true that row-by-row would be slower, I’m cheating by not using rows.

Advent of Code – Day 3

Continuing with the language-independent code challenge on Advent of Code – Day 3, I’m tasked with figuring out how well a drunken elf can guide Santa to houses.  Since his instructions had Santa stumbling into the same house multiple times they then added in a robot to help Santa using the same instruction set, which went on to prove automation can be harmful if not done properly.

Wow, I bet you never thought you’d read that paragraph…

The elf gave Santa instructions on how to get around a grid of houses using ^, v, <, and > to point the way.  When that failed, they tried again with a robot assistant using the same instructions with Santa taking the odd numbered instructions and his automated assistant taking the even numbered instructions.

My code did not have to change much to partially automate Santa’s job, which is actually kinda disturbing.  However, since it didn’t work out so well I went back and added a parameter for them to easily roll back the change.  Now it’s easier to go back to happy and jolly instead of cold and…terminator.

SET NOCOUNT ON

IF OBJECT_ID('TempDB..#visited') IS NOT NULL BEGIN
	DROP TABLE #visited
END

DECLARE @input varchar(max) = '>^^v^<>v<<<v<v^>>v^^^<v<>^^><^<<^vv>>>^<<^>><vv<<v^<^^><>>><>v<><>^^<^^^<><>>vv>vv>v<<^>v<>^>v<v^<>v>><>^v<<<<v^vv^><v>v^>>>vv>v^^^<^^<>>v<^^v<>^<vv^^<^><<>^>><^<>>><><vv><>v<<<><><>v><<>^^^^v>>^>^<v<<vv^^<v<^<^>^^v^^^^^v<><^v><<><^v^>v<<>^<>^^v^<>v<v^>v>^^<vv^v><^<>^v<><^><v^><><><<<<>^vv^>^vvvvv><><^<vv^v^v>v<<^<^^v^<>^<vv><v<v^v<<v<<^^>>^^^v^>v<><^vv<<^<>v<v><><v^^><v<>^^>^^>v^>^<<<<v><v<<>v><^v>^>><v^^<^>v<vvvv<>>>>>^v^^>v<v<^<vv>^>^vv^>vv^^v<<^<^^<>v>vv^v>><>>>v^>^>^^v<>^<v<<>^vv>v^<<v>v<<><v>^vvv<v<vvv^v<vv<v^^^>v><<^<>><v^^>^v^>>^v<^<><v<>>v^<>>v<>>v^^^><^>>vvvv>^v<^><<>>^<>^>vv><v<<>>^^>v^^^><^<<^^v>v<^<<>v>^^vvv^v^>v^<>^^<>v^v>v>v<v^>vv>^^v<>v>>^<>><>v>v^<<vvvv<vvv><v^<^>^v<>>^><v>><>^<v>v<v>vv^>>vvv<>v>v<v^>>^>>v<<>^<>^<>>>^v<<<^<^v>vv^>><<><v^>^v^^^v<>^^vv><>><>>^>v^<v<>v<>>^<<^v>^^^<>^v^><>v<<v>vv^>vv<<>>><<^v^<>v<vv>>>^^<>^><<^>vv>>^<<v^^vv<>>><v>v><^<v<<>>>^^<>>^<^v><>vv^^^v>vvv>^><<>^^>^<<v^<v<^v<<>vvv<^<<>^>^v<vv<^>vvv>v>vv^<v^><>>^vv<^^^vv><^vv<v^<><v^vvv><<^>^^><v<<vv^>v<vv<v>^<>^v<<>v<v^v^>^>^>v<<^vvv<<<v>^^>^<<<<>vv>>^<>^>>>v<v>^^<v^<v<>>>vv>^^v<<>>>^^v><<<v<v<^v<>^^><v<^v<<v^><><^<><v<^^v>>><v^^v<<v^><^<><<v^>><^<>v>v^<><^<v>^v^>^>^vv^>^^<<vv^>vv<^vvv<>>^^<^>v^>^>^<v^><v<v>>>v<<<><^v<<><^<vv^v^^^>v<^^<v^vvv<v<><v<vv<^vv<>vv<v^<>>vvvvv<<>^v^v>vv>>>vvv^^<^<^<><>v<v>><^v><^<<<>><<<v>^>v<>^>^v>>^<>v^<^>><<>^<v>^>^^^>^^<v>>>><>^v^v><<<<vv^<vv<>vv>v<>v^<v^>v><>>>v^<><^vvv>vv^<^<<^<^^v>^>>>v<^<^v^^<^<^>>><v>vv>^<<><>^>>v>^<<>><^<>v<>vv^^>^>vvv^v<<^<^^<vv<>^vvv<^^v^vv^>>v<^>^^<v^<>v<^<^vv>v<<vv>vv>^>vvv>>>^^>v<>^v>v^<^>>v>^^v>>>>v^<v>v<^>v<v<<>>^v<^^<v><^<>>^<<vv^>>v<<v>^v<>><^>vv<v<^>>^^<vvvvvvvvv>>>v<v<>v^<>>^vv<v^^v<<^vvv^<<^><>vv<><<>>v>vv^><>>^^v^>>v^v^><<<>>^^<^v<<^<>>>>^<^>v^><<^>v<^v<^>>^^<<<<><^<^v^v<>>^v<^<<vv^<><^^vv><v^v^v>^>>^>^vv^>^v<v^v<<vvv^><>>^v^^><>v>vv><^>>vv<vvv<<<<^<>vvv^v<v>^<v<^>^<^<v<><>v^^^^<<vv<^^vv<v>><<v^><>>><v^>^v><^>^><vv^<><^<v>><<^vv<>>v^<<v<>v><v<><><vv>^>>v^<^<v>^><>>><^><v^v<>>>^^<^>v<v>vvv<>^<<><v^^>^>>v<^v>^>v>>>vv>v>>v^^^<^<vvv^<>^>^<v^<v^v>v>^>vv>vvv<>v<^>v>^^>>^<vv^^v>v^^^^^v^vv><^<><>^>vv<^>>^vvvv^^^>^<vv>^v<<^><^^>^<>^^>^<<v<^>>>^><<^^>v^v>>^>vvvv>^^v><v>>vv><<<vv<^>v>^^^<v>v^vvv<^><<^>^<>^><<<<<v^<<vv^v>^<>v<v>^>^>><>v^v<^vv^^>vv<<v^v>vv^vvv<<<<>^v<v^^v^v>v<<v>^^<>^vv^^>^>^v^vv^>>v^vv^^<vv><<v^v^^v><vv<^vvv<vv^^<<v>v^v^^^^v<^<^>v>^>v>^vv^v^^<v<^vvvv<<<>^<^^^<^^<>^<><vv<^^<<^>>><v^vvvv>^<>>^^>v^^v^<<v^^^<<<><^<v^v^^v<v^<>v><<v<>^v>v<^><^>vv^^<vvv<^v>>v>^<><v^><^^^<v^>>vv<<<<<^<>^v^v>^vv^<>v>v<^>vv<<^vv>vv<v<><>>v>><v<^<^^>><<v^v<<^><v<^<vv<v<<vv^>^<<><^^>^<^>>^<vv>><v<<vvv<^^v^>^^<^v>^v<v<>v><v^v^<<^<><<v<<^v>v<<>>^>v>>v>>v<^<<^<^>>>v>^^^v><^>^^>>v<<>^v><v>vvv^vv<<<>vvv<<>^>>>v<v<v^<^<^>^<^>v^^v<^^<v<>v<>>^^>^v^>v<<<<^<>v^><<<v>>>><<v^<^vv>v>><>>^<<<^<^^>v<>>v<>vv<<^<<><<^>v^^^vv^>vvvv>>v>v^><<v<>vv^<<><<vvv>^>>>^<<<^<^<<v>^>v<>>v>>vv^^><<<<^^^v>><<^><v><v^^><v<<v^^v^^v>>v<><><<>^><v><^<vv>><^v<>v<vvv<>^>><v>>v<^><<v>^<>^v><^><^^<v>^><^^v^<<><>>^>v^<^v^vv<><^>vv^>v^vvv^<>>^><^<^<>^<<v^v<^v><>^v<v>>^>>^v^vv>><vv><v^^<<^v^<>^v<<>^><^>><v>>v<<<v^^vv<>^^v>>><><><<v^<<<v^<^^><v^>v^^vv<v^<>>vv^<^v<>^v>>v^v>v<^^vv><>^v<<>v^<>v^>>v>vvv<^><><^^>^vv^>>v^>^<^^<><>><<>^^^><^v^v><<<><<^v^vv>v>><^>>><v^>v<v><><v^v<>v^^>>v<<>v>v<v<v<^^<><>v^^<>>v<^v<v>v<><v<v>^<<>v>vv^^<>>^^^<>^^>^v>v>>>^v^v><v^^<><v>^^v^v<^<^^><<v<^<^<>^<>><<>^>>^>^^><v><>v<><>><<<>>>>vv>>>^>>^v<^>v^^^v<<vv>><<<^<<<>>>>>^>vv<^v^<>^<v^>^v><v>vvv<>>>^v^^^v<<<<>>^^<vv<^<^^>^<>v<^<<<>><>>v<^<>^<vvv<^<>><><<v>^^^>^^<<v<v^>^^v^>><<^vv><v>^v>>^<v>v>^^>^v>^vvv<>v^v^^<><vv>vv^>>><>v<^><v<v^<><<<>^v>^v<<<^>^>^>v^v<<><vvv<<v^^<><v>^>>><vv>><v>>v^<vv>>vv<<^v^v<<><^v<vv>>>vv<>>>>^vv>v^<>vv>v^v<v^><v<^^^^^>vv<><<vvv^<v><^<vv><^^^vv^<>^^^^<^><^<>v^<v^v<<^v<<^^<>>^<v^^>>>vv<vvv<>v<<>><^vvv^<<^^<<>>>^<>>>v^^><>><<>><v^v>>>>>><>>><v^<<vvv^>v<>>v^<>vv<><^^^^v^<<^<v^vv><<^^>v<^vvv^v>>v>^>>v>^^><<v^<>v<>vv<^v^vv><v><<vv^v>>v^>>v<^^^>^><<v<>^><>v>>>vvv<v<vv<^>>^v<v>^<^^^^^v><>v><>v^v^v<v^vv^v>vvvv<>vv<<<vv<v<<>^<^>^^v^<<>^<v><^><v<v<><<>v^<<^<><vv>v<<^v>>^v<><v>^>>^^><>v^<^<vvv^>^>^<<<<>vv>^v^v<^^^<vv>><>^^<<v<^<^^>>>v^v<<^^^<v<v<^<>^v<v><v^vv^^v^^v^^<vv<>^<><vv^<^v^<<^><<vvv>^^<^^^<^v>^>^vv><<<^v<v>vv>v<>v^v<v^>v^>>>v^v<>^v<<>^vv>v>v>v^<^>v^^<^>^^^^vv>^^><^>vv^>>^^v>><<<<^><>v<>^<v<vv^>^^><<^><v>v^>^^<^>>><>><v^v<v^<v<vv^v^<<^<vvv>>><vv<^^>>^>^><<v^<>>v>v^v^^><<>vv^v>v^<v><^<>^^<^>v>^<><<<v>^<^<^>^>^>^^v^<<^^v^^<^<>><^>v>>^^<>^^^<<<<v^>^v<^vv>^<<<v<><<v<>vv>>>v><>>><>>v<<<vv><>^v>v<^>><^><><v<>^v^>^v>^v<<><<^<>>v>^><>^>><>><^<v^><v^^<><v><^^>^v^^<>v^<v^<^v<v^^^^^v^<<^>^^^<^v><>^^<<<><<<<<^^>v^vvvv>v<>>vv<^>^v^>v<^vv^v<<><<v>v^v>^^><><^<v^>v><vv><>>><<>^vv<>v>>v<^v>>>v<v>v>v>^vv<<>^^vv<v<^v^<v<v>vv<>^<^<vv<v^<^v^^><<>^>><^v>vv^^v<<^^><<>v^^<><><v^^<v^v>^>^>^>v<^<v>^v^^>v<>vvv<^v<v^v><<v^><<^^><^<<v^v^>v<>^>v><><v>^<v<v>^<^^^>^v<<><<><>vv>v^<>v^><v^v<v><><<v>v<vv><<v>>v>^<<<>vv>>vvv>^^vv^v^^<^^<>v^^<>v>>^^>^>^>v>><^>><>>^<<>><^>v<<<<<<<^v^v<v^<v^^>^<><<v<^>v^>v^vv<<^^vv^>>>>^<>v<^v<>v<vv<^>>v^vv>vv><vv<<^>v>><vv>>>vv^<<<<vv^>v<<<<^^>^^v^><<^<v^>v^>^^<v<>vvv^>^<>vvv<v<^^>v^<<v>><>v<v<>^^<vvv>^>vv><><<<^^vv<v^<v<>v<>><<v><^vv^>^<^>^^^<<<v>vv^<^<<>^>^<vv>v><v<<^><^>^^<vv^v^^>>>>vv^><^^vv><>^<v^v>v<vv>v><<<v>v<v>^><v^^><v>v<^v^>>^^<v^>^^>vv>>vv^><^vv^vv<<^>vv>^v<v><vv><v<vvvvv>^^v^v><v>>>^vv<>v>^^^^<^>><>^v^^^>v<^^<<^^v<vv<>vvv<^>><><^>>^><^<>v<v<<><<v><v^v<>><^>v><<v^<v>v<^<vv^v^v^>vvv^^>v>^<vv^>v^v^<>v>^>>vv>><^^<v<<>^vv<><><<^v<v>v<<vv><>><^v<v>>v^>vvv^v^<<^><v<>^vv^>v^<v<^>>v<v><v><v>>^<<<v^<><<>v>^>^^<v<>>^<>^>^><<<^<<^<<^>^v>>><vvv>><<<<v>>>>>>>^<^v<^>v<>vv<><>v>>^>>^>vv^^><<^<v<v>>^^<<^>v<^>>vv>^<>v><^>v<vv>>>>>>^v<^<<<v^><vv<<>>vv<<><v<><<<v<^<v<>>v<^^^^v^^<^^^<^<vv><<^>><>v<<>v<v<>>>><>v^vv>^>^>>vv^v<v<<><^v>vv^><v<<>v^v<^>vv<<^^v><^>>^^vv<^<>>v^^>><v>^v>>>^>>v>v<>v<^vv><>^<<^>vv>>><><>v^><>v^>v>v><^v<><v<v>^v<<^vv^><^^>><^^^<<<^>v>^v>>><^>><^>>>^^^<^>vv<><<<v^>^<^^>>^^^v^v^v>v<v>>>><^>>>v>^vv<<^^^<^^vv>v<<><v<<^^>v>><<v^^><^>^<^>^v^>v><^<^vv>v>><>^<<vv<<v>v<vv<v>^>^>><^^<v>^v^v<><<>vvv<^<v>^><>^>vvv>>>^><<>><v^^<^<<^v>>^v<v<vv>vv^v^>v<<vvv<^^v^v>^<^>>^>v<^>^v<<><<<^>^<^^^>vv<^^^^vv<v<^^v<<<<v<^v^<><v<<^><<>vv>>><^<^<>>>^>^>>^<<<<<^^v>^>^<>vvv^^<^><^>^^v>^vv^><v^<^<<v^<vvv<<^v<><^><^>>>v>^v>^>^v<vv^v>><v><^><v^^>v^>^<><<><>v<v^>vvv^>^>>v<>^><^>^><vvv>^^v^v>v<>^v^><^>>v>v^><<<^>>^<>^<>>v><>>v^>^>^^<>>v^>^<vvvv<^vvvv^>>vv^<v^v>^vv<>v<>^<v<v>v>^^><^>vv^<^v^<<^<^<><vv<^v<^v><>>>^v^<<^><^>vv<v>v<^>vv^>v<<<>^<><v<^^^>v><^^<>^<^<v^vv^<<^>><<v^v<^vvv<<<>>vvvv^v^^^>v<>>><<>vvv<<^^^>v>v>>v<<v<v^v^>^^v>^><^<><<v^<v<v^^^><>v^^^<v>vv<>^>^^vv>^<<^v<^v><v>>>^>>><^<<>^v>>^>vv<<<v<>^<v><v^<^<>v>v^^v^>><<^v<<<<>v>v>v^^<^><>^^<<<v>vv<>>>^>>v<><v^>^<><vv>v>v^v<v^<^>>^>><<^^<^^v<vv<>><<<v<^<<^^^>vvv^<vvv<^>vv><>><<<^<v^v^^<<^vvv^^<^<><<>^<^<>>vvv<>^<>v^v<><>>v^v><<>>>vvv>v<>^>>^><^>vv<<>>v<<^><>v>>^^<v>^>^<<>><^<<vv<^<vv^vv><>>>><^<v>^>vv<v><>^<>vvvvv^vv<<v<>>>^<<><>^^vvv>>>vv<<^^><^v^^v<>^^>^><^>v^^^^v<^<<vv<vv<>vv^^>v^vv>v><>>vv>^<^<v^v^>>v^v^^v>^>vv^>v<vvvv<^v<^v>^v>^^v<<^>^^<<>^><^v>>>vv^>^^>vvvv>>v<^<v>^>>>v^<><^<^^<v>vv^^><v>v^<>^^^>>><^^v>v>^<<>^<v^>vvv^>^^^><v<^>>v<v>>^v><<><<>v<^<<>^><>^>vv>^<v>^^v<<^v^vvv^^>^vv^<^>^>^^v>v^>^<<><<^>v>>vv^vv><v>>^<<^<v^^<^<v^^vv^><^^<^^><v^^>v^^^<^<>^<>>^v<^vvv^^v^<><^>>>>>v><><<<>vv<^v>><<>vvv<><<vv<<<^>v^^>>^>^v>><><^^v<>><>>v^>^<vv><<<>><><<v>^^<>>v<><^<vv>vv<^v>^<<<<v<^<<^^>>^<><^>><<>^>v>^^^v>>^<^^v><v^v>^><<><>>^>>^<<v<>^v<>^>^<v>>vv>^vvv<<v<<^>^>^<<^^<>^^^^vvv<>^vv<vvvvv^^>^^<^>>><>v^<><^<<^>v^^v<>>^vv<>v^^<>>v^vvvvv<<v^<v^^>>><vvvvv>><^>vv>v^v^<v<^>^^><^>^^^^v<><^v<<>v^>v>>vv<<>^<v^^>vvv>^^<v^<>vv^><>><v^^v<>^>>^>v><>>^^v>^>^>>>^>v<^v>v>^<^^^^^>>v<v<>>v<<^>^<v<<>^^>><<^><>v<>^^^vv<>^^>><<^^>v>vv>vv>v^>^v>v^^<>>><<v><v<<>>v><>vvv^^v>^^>^vvvv^>^<>^vvvv><v><v<>>><>^<^vv<>^v<^v<>^vvv<<>><vvv^>>^><<vv^<v^>^<v<<^^>^^<^^v^>v<>v^v><>><v^^>>^vvv><^vv>v^<^<^v>>v^^>^vvv^<v^^v^^>v<^<>>^<>>>^^<><^^vv<>^vv^<>>>>^^<<^^<>vv^^><>^^<v<<v>^<v^^>^v<><><>vvv>^v^>>vv<<^v<<>><v>^><^>>>^<^<^^>vv^<<^<>>^^><><<v>^^<v>>v<<vvvv>^v^vv>><^^<<^>>v>v<^^^<^><^^vv>^vv<^<vv<>v><^<><v><^^^>>^<><^<v>>>>v^<v>>>>>v<><^^>v<^<^>><v<>^>vv>^^v^v^<<v<><<<^v^><<^<><<<<v<^>><<<>v>>vv><vv<><<^<^<><vv>^^^^<>v<<<<v>vv<>vv^^^>><>vv^><>>^vv<<><^^vv<>v^>>^<<>^<v^<^>v<'

DECLARE @sx smallint = 0
	, @sy smallint = 0
	, @rx smallint = 0
	, @ry smallint = 0
	, @useRobot bit = 0
	, @x smallint = 0
	, @y smallint = 0
	, @i smallint = 0
	, @i_max smallint = 0
	, @char char(1)

SELECT @i_max = len(@input)

CREATE TABLE #visited
(
	x smallint
	, y smallint
	, PRIMARY KEY (x,y)
)

WHILE @i <= @i_max BEGIN
	SELECT @char = substring(@input, @i, 1)

	IF @i % 2 = 0 AND @useRobot = 1 BEGIN
		SELECT @x = @sx
			, @y = @sy
	END ELSE BEGIN
		SELECT @x = @rx 
			, @y = @ry
	END

	IF @char = '>' BEGIN
		SELECT @x = @x + 1
	END ELSE IF @char = '<' BEGIN
		SELECT @x = @x - 1
	END ELSE IF @char = '^' BEGIN
		SELECT @y = @y + 1
	END ELSE IF @char = 'V' BEGIN
		SELECT @y = @y - 1 
	END

	IF 0 = (SELECT COUNT(1) FROM #visited WHERE x = @x AND y = @y) BEGIN
		INSERT INTO #visited(x, y) SELECT @x, @y 
	END

	IF @i % 2 = 0 AND @useRobot = 1 BEGIN
		SELECT @sx = @x
			, @sy = @y
	END ELSE BEGIN
		SELECT @rx = @x 
			, @ry = @y
	END

	SELECT @i = @i + 1
END

SELECT COUNT(1)
FROM #visited 

I’m not the only one doing this with T-SQL.  Andy Warren also posted his very different, yet same results, version in separate posts for the two parts: Day 3 Part 1 and Day 3 Part 2.  His came out more complex, but more set-based.

My problem is that I develop T-SQL for the problem at hand with the expected size of the data.  Andy is looking ahead and developed a method that will probably scale much better than mine.  I’m hedging my bets and hoping the elf in charge of this either sobers up or gets fired.

Advent of Code

If you want your skills to be sharp, you practice.  If you want to get yourself to actually do practice, you call it a “challenge”.  This is what the Advent of Code is.

Now let me be perfectly clear about how I feel about practice.  I saw Jeremiah’s (b|tpost on this and ignored it.  Then I saw Steve Jones’s (b|tpost and ignored it.  Then I saw Andy Warren’s (b|tpost on it that also contained the answer, so I took a look.

It was intriguing and refreshing to have a question like that asked.  The refreshing part was all the requirements were all there in one place.

The intriguing part was me sitting there asking myself how I’d answer that question, realizing I would have done SOOO much more work than Andy.  However, I had to read Andy’s answer 3 times for me to realize I wasn’t able to read it, which increase my level of intrigue.  I rewrote it adding in some legibility for my sake and posted it on his comments.

Now there’s more to it.  I cheated.  I only rewrote someone else’s answer and didn’t really do the work.  Yeah, that’s what half of computer science is, copy/paste others work intelligently, add just enough to call it your own, then ask for a raise.  However, could I do one on my own?

Well, there’s 25 days on the Advent calendars I’m used to, and this one is no different.  So I tried it out on Day 2.

SPOILER ALERT: My answer to Day 2 is below.  Don’t cheat like I did.

The one odd thing you’ll see is that I copy/pasted the dimensions from the Advent of Code site, then I held in ALT and drug the mouse to write on more lines than one at a time.  (even I read that as I drugged the mouse…oh well)  However, that introduced an issue where there were trailing spaces and tabs I had to take care of as well, which I fixed in code as well.

IF OBJECT_ID('tempdb..#input') IS NOT NULL BEGIN
	DROP TABLE #input;
END

CREATE TABLE #input  
(
	dims varchar(100)
);

INSERT INTO #input 
SELECT '4x23x21'
UNION ALL SELECT '22x29x19 '
UNION ALL SELECT '11x4x11	 '
UNION ALL SELECT '8x10x5	 '
UNION ALL SELECT '24x18x16 '
UNION ALL SELECT '11x25x22 '
UNION ALL SELECT '2x13x20	 '
UNION ALL SELECT '24x15x14 '
UNION ALL SELECT '14x22x2	 '
UNION ALL SELECT '30x7x3	 '
UNION ALL SELECT '30x22x25 '
UNION ALL SELECT '29x9x9	 '
UNION ALL SELECT '29x29x26 '
UNION ALL SELECT '14x3x16	 '
UNION ALL SELECT '1x10x26	 '
UNION ALL SELECT '29x2x30	 '
UNION ALL SELECT '30x10x25 '
UNION ALL SELECT '10x26x20 '
UNION ALL SELECT '1x2x18	 '
UNION ALL SELECT '25x18x5	 '
UNION ALL SELECT '21x3x24	 '
UNION ALL SELECT '2x5x7	 '
UNION ALL SELECT '22x11x21 '
UNION ALL SELECT '11x8x8	 '
UNION ALL SELECT '16x18x2	 '
UNION ALL SELECT '13x3x8	 '
UNION ALL SELECT '1x16x19	 '
UNION ALL SELECT '19x16x12 '
UNION ALL SELECT '21x15x1	 '
UNION ALL SELECT '29x9x4	 '
UNION ALL SELECT '27x10x8	 '
UNION ALL SELECT '2x7x27	 '
UNION ALL SELECT '2x20x23	 '
UNION ALL SELECT '24x11x5	 '
UNION ALL SELECT '2x8x27	 '
UNION ALL SELECT '10x28x10 '
UNION ALL SELECT '24x11x10 '
UNION ALL SELECT '19x2x12	 '
UNION ALL SELECT '27x5x10	 '
UNION ALL SELECT '1x14x25	 '
UNION ALL SELECT '5x14x30	 '
UNION ALL SELECT '15x26x12 '
UNION ALL SELECT '23x20x22 '
UNION ALL SELECT '5x12x1	 '
UNION ALL SELECT '9x26x9	 '
UNION ALL SELECT '23x25x5	 '
UNION ALL SELECT '28x16x19 '
UNION ALL SELECT '17x23x17 '
UNION ALL SELECT '2x27x20	 '
UNION ALL SELECT '18x27x13 '
UNION ALL SELECT '16x7x18	 '
UNION ALL SELECT '22x7x29	 '
UNION ALL SELECT '17x28x6	 '
UNION ALL SELECT '9x22x17	 '
UNION ALL SELECT '10x5x6	 '
UNION ALL SELECT '14x2x12	 '
UNION ALL SELECT '25x5x6	 '
UNION ALL SELECT '26x9x10	 '
UNION ALL SELECT '19x21x6	 '
UNION ALL SELECT '19x4x27	 '
UNION ALL SELECT '23x16x14 '
UNION ALL SELECT '21x17x29 '
UNION ALL SELECT '24x18x10 '
UNION ALL SELECT '7x19x6	 '
UNION ALL SELECT '14x15x10 '
UNION ALL SELECT '9x10x19	 '
UNION ALL SELECT '20x18x4	 '
UNION ALL SELECT '11x14x8	 '
UNION ALL SELECT '30x15x9	 '
UNION ALL SELECT '25x12x24 '
UNION ALL SELECT '3x12x5	 '
UNION ALL SELECT '12x21x28 '
UNION ALL SELECT '8x23x10	 '
UNION ALL SELECT '18x26x8	 '
UNION ALL SELECT '17x1x8	 '
UNION ALL SELECT '2x29x15	 '
UNION ALL SELECT '3x13x28	 '
UNION ALL SELECT '23x20x11 '
UNION ALL SELECT '27x25x6	 '
UNION ALL SELECT '19x21x3	 '
UNION ALL SELECT '30x22x27 '
UNION ALL SELECT '28x24x4	 '
UNION ALL SELECT '26x18x21 '
UNION ALL SELECT '11x7x16	 '
UNION ALL SELECT '22x27x6	 '
UNION ALL SELECT '27x5x26	 '
UNION ALL SELECT '4x10x4	 '
UNION ALL SELECT '4x2x27	 '
UNION ALL SELECT '2x3x26	 '
UNION ALL SELECT '26x29x19 '
UNION ALL SELECT '30x26x24 '
UNION ALL SELECT '8x25x12	 '
UNION ALL SELECT '16x17x5	 '
UNION ALL SELECT '13x2x3	 '
UNION ALL SELECT '1x30x22	 '
UNION ALL SELECT '20x9x1	 '
UNION ALL SELECT '24x26x19 '
UNION ALL SELECT '26x18x1	 '
UNION ALL SELECT '18x29x24 '
UNION ALL SELECT '1x6x9	 '
UNION ALL SELECT '20x27x2	 '
UNION ALL SELECT '3x22x21	 '
UNION ALL SELECT '4x16x8	 '
UNION ALL SELECT '29x18x16 '
UNION ALL SELECT '7x16x23	 '
UNION ALL SELECT '13x8x14	 '
UNION ALL SELECT '19x25x10 '
UNION ALL SELECT '23x29x6	 '
UNION ALL SELECT '23x21x1	 '
UNION ALL SELECT '22x26x10 '
UNION ALL SELECT '14x4x2	 '
UNION ALL SELECT '18x29x17 '
UNION ALL SELECT '9x4x18	 '
UNION ALL SELECT '7x22x9	 '
UNION ALL SELECT '19x5x26	 '
UNION ALL SELECT '27x29x19 '
UNION ALL SELECT '7x13x14	 '
UNION ALL SELECT '19x10x1	 '
UNION ALL SELECT '6x22x3	 '
UNION ALL SELECT '12x21x5	 '
UNION ALL SELECT '24x20x12 '
UNION ALL SELECT '28x2x11	 '
UNION ALL SELECT '16x18x23 '
UNION ALL SELECT '2x13x25	 '
UNION ALL SELECT '11x7x17	 '
UNION ALL SELECT '27x21x4	 '
UNION ALL SELECT '2x10x25	 '
UNION ALL SELECT '22x16x17 '
UNION ALL SELECT '23x22x15 '
UNION ALL SELECT '17x13x13 '
UNION ALL SELECT '23x24x26 '
UNION ALL SELECT '27x18x24 '
UNION ALL SELECT '24x7x28	 '
UNION ALL SELECT '30x12x15 '
UNION ALL SELECT '14x28x19 '
UNION ALL SELECT '2x15x29	 '
UNION ALL SELECT '12x13x5	 '
UNION ALL SELECT '17x22x21 '
UNION ALL SELECT '27x10x27 '
UNION ALL SELECT '17x6x25	 '
UNION ALL SELECT '22x2x1	 '
UNION ALL SELECT '1x10x9	 '
UNION ALL SELECT '9x7x2	 '
UNION ALL SELECT '30x28x3	 '
UNION ALL SELECT '28x11x10 '
UNION ALL SELECT '8x23x15	 '
UNION ALL SELECT '23x4x20	 '
UNION ALL SELECT '12x5x4	 '
UNION ALL SELECT '13x17x14 '
UNION ALL SELECT '28x11x2	 '
UNION ALL SELECT '21x11x29 '
UNION ALL SELECT '10x23x22 '
UNION ALL SELECT '27x23x14 '
UNION ALL SELECT '7x15x23	 '
UNION ALL SELECT '20x2x13	 '
UNION ALL SELECT '8x21x4	 '
UNION ALL SELECT '10x20x11 '
UNION ALL SELECT '23x28x11 '
UNION ALL SELECT '21x22x25 '
UNION ALL SELECT '23x11x17 '
UNION ALL SELECT '2x29x10	 '
UNION ALL SELECT '28x16x5	 '
UNION ALL SELECT '30x26x10 '
UNION ALL SELECT '17x24x16 '
UNION ALL SELECT '26x27x25 '
UNION ALL SELECT '14x13x25 '
UNION ALL SELECT '22x27x5	 '
UNION ALL SELECT '24x15x12 '
UNION ALL SELECT '5x21x25	 '
UNION ALL SELECT '4x27x1	 '
UNION ALL SELECT '25x4x10	 '
UNION ALL SELECT '15x13x1	 '
UNION ALL SELECT '21x23x7	 '
UNION ALL SELECT '8x3x4	 '
UNION ALL SELECT '10x5x7	 '
UNION ALL SELECT '9x13x30	 '
UNION ALL SELECT '2x2x30	 '
UNION ALL SELECT '26x4x29	 '
UNION ALL SELECT '5x14x14	 '
UNION ALL SELECT '2x27x9	 '
UNION ALL SELECT '22x16x1	 '
UNION ALL SELECT '4x23x5	 '
UNION ALL SELECT '13x7x26	 '
UNION ALL SELECT '2x12x10	 '
UNION ALL SELECT '12x7x22	 '
UNION ALL SELECT '26x30x26 '
UNION ALL SELECT '28x16x28 '
UNION ALL SELECT '15x19x11 '
UNION ALL SELECT '4x18x1	 '
UNION ALL SELECT '20x14x24 '
UNION ALL SELECT '6x10x22	 '
UNION ALL SELECT '9x20x3	 '
UNION ALL SELECT '14x9x27	 '
UNION ALL SELECT '26x17x9	 '
UNION ALL SELECT '10x30x28 '
UNION ALL SELECT '6x3x29	 '
UNION ALL SELECT '4x16x28	 '
UNION ALL SELECT '8x24x11	 '
UNION ALL SELECT '23x10x1	 '
UNION ALL SELECT '11x7x7	 '
UNION ALL SELECT '29x6x15	 '
UNION ALL SELECT '13x25x12 '
UNION ALL SELECT '29x14x3	 '
UNION ALL SELECT '26x22x21 '
UNION ALL SELECT '8x3x11	 '
UNION ALL SELECT '27x13x25 '
UNION ALL SELECT '27x6x2	 '
UNION ALL SELECT '8x11x7	 '
UNION ALL SELECT '25x12x9	 '
UNION ALL SELECT '24x30x12 '
UNION ALL SELECT '13x1x30	 '
UNION ALL SELECT '25x23x16 '
UNION ALL SELECT '9x13x29	 '
UNION ALL SELECT '29x26x16 '
UNION ALL SELECT '11x15x9	 '
UNION ALL SELECT '11x23x6	 '
UNION ALL SELECT '15x27x28 '
UNION ALL SELECT '27x24x21 '
UNION ALL SELECT '6x24x1	 '
UNION ALL SELECT '25x25x5	 '
UNION ALL SELECT '11x1x26	 '
UNION ALL SELECT '21x4x24	 '
UNION ALL SELECT '10x5x12	 '
UNION ALL SELECT '4x30x13	 '
UNION ALL SELECT '24x22x5	 '
UNION ALL SELECT '26x7x21	 '
UNION ALL SELECT '23x3x17	 '
UNION ALL SELECT '22x18x2	 '
UNION ALL SELECT '25x1x14	 '
UNION ALL SELECT '23x25x30 '
UNION ALL SELECT '8x7x7	 '
UNION ALL SELECT '30x19x8	 '
UNION ALL SELECT '17x6x15	 '
UNION ALL SELECT '2x11x20	 '
UNION ALL SELECT '8x3x22	 '
UNION ALL SELECT '23x14x26 '
UNION ALL SELECT '8x22x25	 '
UNION ALL SELECT '27x1x2	 '
UNION ALL SELECT '10x26x2	 '
UNION ALL SELECT '28x30x7	 '
UNION ALL SELECT '5x30x7	 '
UNION ALL SELECT '27x16x30 '
UNION ALL SELECT '28x29x1	 '
UNION ALL SELECT '8x25x18	 '
UNION ALL SELECT '20x12x29 '
UNION ALL SELECT '9x19x9	 '
UNION ALL SELECT '7x25x15	 '
UNION ALL SELECT '25x18x18 '
UNION ALL SELECT '11x8x2	 '
UNION ALL SELECT '4x20x6	 '
UNION ALL SELECT '18x5x20	 '
UNION ALL SELECT '2x3x29	 '
UNION ALL SELECT '25x26x22 '
UNION ALL SELECT '18x25x26 '
UNION ALL SELECT '9x12x16	 '
UNION ALL SELECT '18x7x27	 '
UNION ALL SELECT '17x20x9	 '
UNION ALL SELECT '6x29x26	 '
UNION ALL SELECT '17x7x19	 '
UNION ALL SELECT '21x7x5	 '
UNION ALL SELECT '29x15x12 '
UNION ALL SELECT '22x4x1	 '
UNION ALL SELECT '11x12x11 '
UNION ALL SELECT '26x30x4	 '
UNION ALL SELECT '12x24x13 '
UNION ALL SELECT '13x8x3	 '
UNION ALL SELECT '26x25x3	 '
UNION ALL SELECT '21x26x10 '
UNION ALL SELECT '14x9x26	 '
UNION ALL SELECT '20x1x7	 '
UNION ALL SELECT '11x12x3	 '
UNION ALL SELECT '12x11x4	 '
UNION ALL SELECT '11x15x30 '
UNION ALL SELECT '17x6x25	 '
UNION ALL SELECT '20x22x3	 '
UNION ALL SELECT '1x16x17	 '
UNION ALL SELECT '11x5x20	 '
UNION ALL SELECT '12x12x7	 '
UNION ALL SELECT '2x14x10	 '
UNION ALL SELECT '14x27x3	 '
UNION ALL SELECT '14x16x18 '
UNION ALL SELECT '21x28x24 '
UNION ALL SELECT '14x20x1	 '
UNION ALL SELECT '29x14x1	 '
UNION ALL SELECT '10x10x9	 '
UNION ALL SELECT '25x23x4	 '
UNION ALL SELECT '17x15x14 '
UNION ALL SELECT '9x20x26	 '
UNION ALL SELECT '16x2x17	 '
UNION ALL SELECT '13x28x25 '
UNION ALL SELECT '16x1x11	 '
UNION ALL SELECT '19x16x8	 '
UNION ALL SELECT '20x21x2	 '
UNION ALL SELECT '27x9x22	 '
UNION ALL SELECT '24x18x3	 '
UNION ALL SELECT '23x30x6	 '
UNION ALL SELECT '4x18x3	 '
UNION ALL SELECT '30x15x8	 '
UNION ALL SELECT '27x20x19 '
UNION ALL SELECT '28x29x26 '
UNION ALL SELECT '2x21x18	 '
UNION ALL SELECT '1x23x30	 '
UNION ALL SELECT '1x9x12	 '
UNION ALL SELECT '4x11x30	 '
UNION ALL SELECT '1x28x4	 '
UNION ALL SELECT '17x10x10 '
UNION ALL SELECT '12x14x6	 '
UNION ALL SELECT '8x9x24	 '
UNION ALL SELECT '8x3x3	 '
UNION ALL SELECT '29x8x20	 '
UNION ALL SELECT '26x29x2	 '
UNION ALL SELECT '29x25x25 '
UNION ALL SELECT '11x17x23 '
UNION ALL SELECT '6x30x21	 '
UNION ALL SELECT '13x18x29 '
UNION ALL SELECT '2x10x8	 '
UNION ALL SELECT '29x29x27 '
UNION ALL SELECT '27x15x15 '
UNION ALL SELECT '16x17x30 '
UNION ALL SELECT '3x3x22	 '
UNION ALL SELECT '21x12x6	 '
UNION ALL SELECT '22x1x5	 '
UNION ALL SELECT '30x8x20	 '
UNION ALL SELECT '6x28x13	 '
UNION ALL SELECT '11x2x23	 '
UNION ALL SELECT '14x18x27 '
UNION ALL SELECT '6x26x13	 '
UNION ALL SELECT '10x24x24 '
UNION ALL SELECT '4x24x6	 '
UNION ALL SELECT '20x8x3	 '
UNION ALL SELECT '23x11x5	 '
UNION ALL SELECT '29x5x24	 '
UNION ALL SELECT '14x15x22 '
UNION ALL SELECT '21x17x13 '
UNION ALL SELECT '10x10x8	 '
UNION ALL SELECT '1x11x23	 '
UNION ALL SELECT '21x19x24 '
UNION ALL SELECT '19x9x13	 '
UNION ALL SELECT '21x26x28 '
UNION ALL SELECT '25x11x28 '
UNION ALL SELECT '2x17x1	 '
UNION ALL SELECT '18x9x8	 '
UNION ALL SELECT '5x21x6	 '
UNION ALL SELECT '12x5x2	 '
UNION ALL SELECT '23x8x15	 '
UNION ALL SELECT '30x16x24 '
UNION ALL SELECT '7x9x27	 '
UNION ALL SELECT '16x30x7	 '
UNION ALL SELECT '2x21x28	 '
UNION ALL SELECT '5x10x6	 '
UNION ALL SELECT '8x7x1	 '
UNION ALL SELECT '28x13x5	 '
UNION ALL SELECT '11x5x14	 '
UNION ALL SELECT '26x22x29 '
UNION ALL SELECT '23x15x13 '
UNION ALL SELECT '14x2x16	 '
UNION ALL SELECT '22x21x9	 '
UNION ALL SELECT '4x20x3	 '
UNION ALL SELECT '18x17x19 '
UNION ALL SELECT '12x7x9	 '
UNION ALL SELECT '6x12x25	 '
UNION ALL SELECT '3x30x27	 '
UNION ALL SELECT '8x19x22	 '
UNION ALL SELECT '1x9x27	 '
UNION ALL SELECT '23x20x12 '
UNION ALL SELECT '14x7x29	 '
UNION ALL SELECT '9x12x12	 '
UNION ALL SELECT '30x2x6	 '
UNION ALL SELECT '15x7x16	 '
UNION ALL SELECT '19x13x18 '
UNION ALL SELECT '11x8x13	 '
UNION ALL SELECT '16x5x3	 '
UNION ALL SELECT '19x26x24 '
UNION ALL SELECT '26x8x21	 '
UNION ALL SELECT '21x20x7	 '
UNION ALL SELECT '15x1x25	 '
UNION ALL SELECT '29x15x21 '
UNION ALL SELECT '22x17x7	 '
UNION ALL SELECT '16x17x10 '
UNION ALL SELECT '6x12x24	 '
UNION ALL SELECT '8x13x27	 '
UNION ALL SELECT '30x25x14 '
UNION ALL SELECT '25x7x10	 '
UNION ALL SELECT '15x2x2	 '
UNION ALL SELECT '18x15x19 '
UNION ALL SELECT '18x13x24 '
UNION ALL SELECT '19x30x1	 '
UNION ALL SELECT '17x1x3	 '
UNION ALL SELECT '26x21x15 '
UNION ALL SELECT '10x10x18 '
UNION ALL SELECT '9x16x6	 '
UNION ALL SELECT '29x7x30	 '
UNION ALL SELECT '11x10x30 '
UNION ALL SELECT '6x11x2	 '
UNION ALL SELECT '7x29x23	 '
UNION ALL SELECT '13x2x30	 '
UNION ALL SELECT '25x27x13 '
UNION ALL SELECT '5x15x21	 '
UNION ALL SELECT '4x8x30	 '
UNION ALL SELECT '15x27x11 '
UNION ALL SELECT '27x1x6	 '
UNION ALL SELECT '2x24x11	 '
UNION ALL SELECT '16x20x19 '
UNION ALL SELECT '25x28x20 '
UNION ALL SELECT '6x8x4	 '
UNION ALL SELECT '27x16x11 '
UNION ALL SELECT '1x5x27	 '
UNION ALL SELECT '12x19x26 '
UNION ALL SELECT '18x24x14 '
UNION ALL SELECT '4x25x17	 '
UNION ALL SELECT '24x24x26 '
UNION ALL SELECT '28x3x18	 '
UNION ALL SELECT '8x20x28	 '
UNION ALL SELECT '22x7x21	 '
UNION ALL SELECT '24x5x28	 '
UNION ALL SELECT '23x30x29 '
UNION ALL SELECT '25x16x27 '
UNION ALL SELECT '28x10x30 '
UNION ALL SELECT '9x2x4	 '
UNION ALL SELECT '30x2x23	 '
UNION ALL SELECT '21x9x23	 '
UNION ALL SELECT '27x4x26	 '
UNION ALL SELECT '2x23x16	 '
UNION ALL SELECT '24x26x30 '
UNION ALL SELECT '26x1x30	 '
UNION ALL SELECT '10x4x28	 '
UNION ALL SELECT '11x29x12 '
UNION ALL SELECT '28x13x30 '
UNION ALL SELECT '24x10x28 '
UNION ALL SELECT '8x12x12	 '
UNION ALL SELECT '19x27x11 '
UNION ALL SELECT '11x28x7	 '
UNION ALL SELECT '14x6x3	 '
UNION ALL SELECT '6x27x5	 '
UNION ALL SELECT '6x17x14	 '
UNION ALL SELECT '24x24x17 '
UNION ALL SELECT '18x23x14 '
UNION ALL SELECT '17x5x7	 '
UNION ALL SELECT '11x4x23	 '
UNION ALL SELECT '5x1x17	 '
UNION ALL SELECT '26x15x24 '
UNION ALL SELECT '3x9x24	 '
UNION ALL SELECT '5x3x15	 '
UNION ALL SELECT '5x20x19	 '
UNION ALL SELECT '5x21x2	 '
UNION ALL SELECT '13x5x30	 '
UNION ALL SELECT '19x6x24	 '
UNION ALL SELECT '19x17x6	 '
UNION ALL SELECT '23x7x13	 '
UNION ALL SELECT '28x23x13 '
UNION ALL SELECT '9x1x6	 '
UNION ALL SELECT '15x12x16 '
UNION ALL SELECT '21x19x9	 '
UNION ALL SELECT '25x5x5	 '
UNION ALL SELECT '9x7x9	 '
UNION ALL SELECT '6x5x8	 '
UNION ALL SELECT '3x11x18	 '
UNION ALL SELECT '23x25x11 '
UNION ALL SELECT '25x4x6	 '
UNION ALL SELECT '4x27x1	 '
UNION ALL SELECT '4x3x3	 '
UNION ALL SELECT '30x11x5	 '
UNION ALL SELECT '9x17x12	 '
UNION ALL SELECT '15x6x24	 '
UNION ALL SELECT '10x22x15 '
UNION ALL SELECT '29x27x9	 '
UNION ALL SELECT '20x21x11 '
UNION ALL SELECT '18x10x5	 '
UNION ALL SELECT '11x2x2	 '
UNION ALL SELECT '9x8x8	 '
UNION ALL SELECT '1x26x21	 '
UNION ALL SELECT '11x11x16 '
UNION ALL SELECT '2x18x30	 '
UNION ALL SELECT '29x27x24 '
UNION ALL SELECT '27x8x18	 '
UNION ALL SELECT '19x3x17	 '
UNION ALL SELECT '30x21x26 '
UNION ALL SELECT '25x13x25 '
UNION ALL SELECT '20x22x1	 '
UNION ALL SELECT '10x1x12	 '
UNION ALL SELECT '11x17x15 '
UNION ALL SELECT '29x11x30 '
UNION ALL SELECT '17x30x27 '
UNION ALL SELECT '21x22x17 '
UNION ALL SELECT '13x6x22	 '
UNION ALL SELECT '22x16x12 '
UNION ALL SELECT '27x18x19 '
UNION ALL SELECT '4x13x6	 '
UNION ALL SELECT '27x29x10 '
UNION ALL SELECT '3x23x10	 '
UNION ALL SELECT '26x16x24 '
UNION ALL SELECT '18x26x20 '
UNION ALL SELECT '11x28x16 '
UNION ALL SELECT '21x6x15	 '
UNION ALL SELECT '9x26x17	 '
UNION ALL SELECT '8x15x8	 '
UNION ALL SELECT '3x7x10	 '
UNION ALL SELECT '2x28x8	 '
UNION ALL SELECT '1x2x24	 '
UNION ALL SELECT '7x8x9	 '
UNION ALL SELECT '19x4x22	 '
UNION ALL SELECT '11x20x9	 '
UNION ALL SELECT '12x22x16 '
UNION ALL SELECT '26x8x19	 '
UNION ALL SELECT '13x28x24 '
UNION ALL SELECT '4x10x16	 '
UNION ALL SELECT '12x8x10	 '
UNION ALL SELECT '14x24x24 '
UNION ALL SELECT '19x19x28 '
UNION ALL SELECT '29x1x15	 '
UNION ALL SELECT '10x5x14	 '
UNION ALL SELECT '20x19x23 '
UNION ALL SELECT '10x7x12	 '
UNION ALL SELECT '1x7x13	 '
UNION ALL SELECT '5x12x13	 '
UNION ALL SELECT '25x21x8	 '
UNION ALL SELECT '22x28x8	 '
UNION ALL SELECT '7x9x4	 '
UNION ALL SELECT '3x20x15	 '
UNION ALL SELECT '15x27x19 '
UNION ALL SELECT '18x24x12 '
UNION ALL SELECT '16x10x16 '
UNION ALL SELECT '22x19x8	 '
UNION ALL SELECT '15x4x3	 '
UNION ALL SELECT '9x30x25	 '
UNION ALL SELECT '1x1x6	 '
UNION ALL SELECT '24x4x25	 '
UNION ALL SELECT '13x18x29 '
UNION ALL SELECT '10x2x8	 '
UNION ALL SELECT '21x1x17	 '
UNION ALL SELECT '29x14x22 '
UNION ALL SELECT '17x29x11 '
UNION ALL SELECT '10x27x16 '
UNION ALL SELECT '25x16x15 '
UNION ALL SELECT '14x2x17	 '
UNION ALL SELECT '12x27x3	 '
UNION ALL SELECT '14x17x25 '
UNION ALL SELECT '24x4x1	 '
UNION ALL SELECT '18x28x18 '
UNION ALL SELECT '9x14x26	 '
UNION ALL SELECT '28x24x17 '
UNION ALL SELECT '1x26x12	 '
UNION ALL SELECT '2x18x20	 '
UNION ALL SELECT '12x19x22 '
UNION ALL SELECT '19x25x20 '
UNION ALL SELECT '5x17x27	 '
UNION ALL SELECT '17x29x16 '
UNION ALL SELECT '29x19x11 '
UNION ALL SELECT '16x2x4	 '
UNION ALL SELECT '23x24x1	 '
UNION ALL SELECT '19x18x3	 '
UNION ALL SELECT '28x14x6	 '
UNION ALL SELECT '18x5x23	 '
UNION ALL SELECT '9x24x12	 '
UNION ALL SELECT '15x4x6	 '
UNION ALL SELECT '15x7x24	 '
UNION ALL SELECT '22x15x8	 '
UNION ALL SELECT '22x1x22	 '
UNION ALL SELECT '6x4x22	 '
UNION ALL SELECT '26x1x30	 '
UNION ALL SELECT '8x21x27	 '
UNION ALL SELECT '7x1x11	 '
UNION ALL SELECT '9x8x18	 '
UNION ALL SELECT '20x27x12 '
UNION ALL SELECT '26x23x20 '
UNION ALL SELECT '26x22x30 '
UNION ALL SELECT '24x3x16	 '
UNION ALL SELECT '8x24x28	 '
UNION ALL SELECT '13x28x5	 '
UNION ALL SELECT '4x29x23	 '
UNION ALL SELECT '22x5x8	 '
UNION ALL SELECT '20x22x3	 '
UNION ALL SELECT '9x9x17	 '
UNION ALL SELECT '28x3x30	 '
UNION ALL SELECT '10x13x10 '
UNION ALL SELECT '10x25x13 '
UNION ALL SELECT '9x20x3	 '
UNION ALL SELECT '1x21x25	 '
UNION ALL SELECT '24x21x15 '
UNION ALL SELECT '21x5x14	 '
UNION ALL SELECT '13x8x20	 '
UNION ALL SELECT '29x17x3	 '
UNION ALL SELECT '5x17x28	 '
UNION ALL SELECT '16x12x7	 '
UNION ALL SELECT '23x1x24	 '
UNION ALL SELECT '4x24x29	 '
UNION ALL SELECT '23x25x14 '
UNION ALL SELECT '8x27x2	 '
UNION ALL SELECT '23x11x13 '
UNION ALL SELECT '13x4x5	 '
UNION ALL SELECT '24x1x26	 '
UNION ALL SELECT '21x1x23	 '
UNION ALL SELECT '10x12x12 '
UNION ALL SELECT '21x29x25 '
UNION ALL SELECT '27x25x30 '
UNION ALL SELECT '24x23x4	 '
UNION ALL SELECT '1x30x23	 '
UNION ALL SELECT '29x28x14 '
UNION ALL SELECT '4x11x30	 '
UNION ALL SELECT '9x25x10	 '
UNION ALL SELECT '17x11x6	 '
UNION ALL SELECT '14x29x30 '
UNION ALL SELECT '23x5x5	 '
UNION ALL SELECT '25x18x21 '
UNION ALL SELECT '8x7x1	 '
UNION ALL SELECT '27x11x3	 '
UNION ALL SELECT '5x10x8	 '
UNION ALL SELECT '11x1x11	 '
UNION ALL SELECT '16x17x26 '
UNION ALL SELECT '15x22x19 '
UNION ALL SELECT '16x9x6	 '
UNION ALL SELECT '18x13x27 '
UNION ALL SELECT '26x4x22	 '
UNION ALL SELECT '1x20x21	 '
UNION ALL SELECT '6x14x29	 '
UNION ALL SELECT '11x7x6	 '
UNION ALL SELECT '1x23x7	 '
UNION ALL SELECT '12x19x13 '
UNION ALL SELECT '18x21x25 '
UNION ALL SELECT '15x17x20 '
UNION ALL SELECT '23x8x9	 '
UNION ALL SELECT '15x9x26	 '
UNION ALL SELECT '9x12x9	 '
UNION ALL SELECT '12x13x14 '
UNION ALL SELECT '27x26x7	 '
UNION ALL SELECT '11x19x22 '
UNION ALL SELECT '16x12x21 '
UNION ALL SELECT '10x30x28 '
UNION ALL SELECT '21x2x7	 '
UNION ALL SELECT '12x9x18	 '
UNION ALL SELECT '7x17x14	 '
UNION ALL SELECT '13x17x17 '
UNION ALL SELECT '3x21x10	 '
UNION ALL SELECT '30x9x15	 '
UNION ALL SELECT '2x8x15	 '
UNION ALL SELECT '15x12x10 '
UNION ALL SELECT '23x26x9	 '
UNION ALL SELECT '29x30x10 '
UNION ALL SELECT '30x22x17 '
UNION ALL SELECT '17x26x30 '
UNION ALL SELECT '27x26x20 '
UNION ALL SELECT '17x28x17 '
UNION ALL SELECT '30x12x16 '
UNION ALL SELECT '7x23x15	 '
UNION ALL SELECT '30x15x19 '
UNION ALL SELECT '13x19x10 '
UNION ALL SELECT '22x10x4	 '
UNION ALL SELECT '17x23x10 '
UNION ALL SELECT '2x28x18	 '
UNION ALL SELECT '27x21x28 '
UNION ALL SELECT '24x26x5	 '
UNION ALL SELECT '6x23x25	 '
UNION ALL SELECT '17x4x16	 '
UNION ALL SELECT '14x1x13	 '
UNION ALL SELECT '23x21x11 '
UNION ALL SELECT '14x15x30 '
UNION ALL SELECT '26x13x10 '
UNION ALL SELECT '30x19x25 '
UNION ALL SELECT '26x6x26	 '
UNION ALL SELECT '9x16x29	 '
UNION ALL SELECT '15x2x24	 '
UNION ALL SELECT '13x3x20	 '
UNION ALL SELECT '23x12x30 '
UNION ALL SELECT '22x23x23 '
UNION ALL SELECT '8x21x2	 '
UNION ALL SELECT '18x28x5	 '
UNION ALL SELECT '21x27x14 '
UNION ALL SELECT '29x28x23 '
UNION ALL SELECT '12x30x28 '
UNION ALL SELECT '17x16x3	 '
UNION ALL SELECT '5x19x11	 '
UNION ALL SELECT '28x22x22 '
UNION ALL SELECT '1x4x28	 '
UNION ALL SELECT '10x10x14 '
UNION ALL SELECT '18x15x7	 '
UNION ALL SELECT '18x11x1	 '
UNION ALL SELECT '12x7x16	 '
UNION ALL SELECT '10x22x24 '
UNION ALL SELECT '27x25x6	 '
UNION ALL SELECT '19x29x25 '
UNION ALL SELECT '10x1x26	 '
UNION ALL SELECT '26x27x30 '
UNION ALL SELECT '4x23x19	 '
UNION ALL SELECT '24x19x4	 '
UNION ALL SELECT '21x11x14 '
UNION ALL SELECT '4x13x27	 '
UNION ALL SELECT '9x1x11	 '
UNION ALL SELECT '16x20x8	 '
UNION ALL SELECT '4x3x11	 '
UNION ALL SELECT '1x16x12	 '
UNION ALL SELECT '14x6x30	 '
UNION ALL SELECT '8x1x10	 '
UNION ALL SELECT '11x18x7	 '
UNION ALL SELECT '29x28x30 '
UNION ALL SELECT '4x21x8	 '
UNION ALL SELECT '3x21x4	 '
UNION ALL SELECT '6x1x5	 '
UNION ALL SELECT '26x18x3	 '
UNION ALL SELECT '28x27x27 '
UNION ALL SELECT '17x3x12	 '
UNION ALL SELECT '6x1x22	 '
UNION ALL SELECT '23x12x28 '
UNION ALL SELECT '12x13x2	 '
UNION ALL SELECT '11x2x13	 '
UNION ALL SELECT '7x1x28	 '
UNION ALL SELECT '27x6x25	 '
UNION ALL SELECT '14x14x3	 '
UNION ALL SELECT '14x11x20 '
UNION ALL SELECT '2x27x7	 '
UNION ALL SELECT '22x24x23 '
UNION ALL SELECT '7x15x20	 '
UNION ALL SELECT '30x6x17	 '
UNION ALL SELECT '20x23x25 '
UNION ALL SELECT '18x16x27 '
UNION ALL SELECT '2x9x6	 '
UNION ALL SELECT '9x18x19	 '
UNION ALL SELECT '20x11x22 '
UNION ALL SELECT '11x16x19 '
UNION ALL SELECT '14x29x23 '
UNION ALL SELECT '14x9x20	 '
UNION ALL SELECT '8x10x12	 '
UNION ALL SELECT '18x17x6	 '
UNION ALL SELECT '28x7x16	 '
UNION ALL SELECT '12x19x28 '
UNION ALL SELECT '5x3x16	 '
UNION ALL SELECT '1x25x10	 '
UNION ALL SELECT '4x14x10	 '
UNION ALL SELECT '9x6x3	 '
UNION ALL SELECT '15x27x28 '
UNION ALL SELECT '13x26x14 '
UNION ALL SELECT '21x8x25	 '
UNION ALL SELECT '29x10x20 '
UNION ALL SELECT '14x26x30 '
UNION ALL SELECT '25x13x28 '
UNION ALL SELECT '1x15x23	 '
UNION ALL SELECT '6x20x21	 '
UNION ALL SELECT '18x2x1	 '
UNION ALL SELECT '22x25x16 '
UNION ALL SELECT '23x25x17 '
UNION ALL SELECT '2x14x21	 '
UNION ALL SELECT '14x25x16 '
UNION ALL SELECT '12x17x6	 '
UNION ALL SELECT '19x29x15 '
UNION ALL SELECT '25x9x6	 '
UNION ALL SELECT '19x17x13 '
UNION ALL SELECT '24x22x5	 '
UNION ALL SELECT '19x4x13	 '
UNION ALL SELECT '10x18x6	 '
UNION ALL SELECT '6x25x6	 '
UNION ALL SELECT '23x24x20 '
UNION ALL SELECT '8x22x13	 '
UNION ALL SELECT '25x10x29 '
UNION ALL SELECT '5x12x25	 '
UNION ALL SELECT '20x5x11	 '
UNION ALL SELECT '7x16x29	 '
UNION ALL SELECT '29x24x22 '
UNION ALL SELECT '28x20x1	 '
UNION ALL SELECT '10x27x10 '
UNION ALL SELECT '6x9x27	 '
UNION ALL SELECT '26x15x30 '
UNION ALL SELECT '26x3x19	 '
UNION ALL SELECT '20x11x3	 '
UNION ALL SELECT '26x1x29	 '
UNION ALL SELECT '6x23x4	 '
UNION ALL SELECT '6x13x21	 '
UNION ALL SELECT '9x23x25	 '
UNION ALL SELECT '15x1x10	 '
UNION ALL SELECT '29x12x13 '
UNION ALL SELECT '7x8x24	 '
UNION ALL SELECT '29x30x27 '
UNION ALL SELECT '3x29x19	 '
UNION ALL SELECT '14x16x17 '
UNION ALL SELECT '4x8x27	 '
UNION ALL SELECT '26x17x8	 '
UNION ALL SELECT '10x27x17 '
UNION ALL SELECT '11x28x17 '
UNION ALL SELECT '17x16x27 '
UNION ALL SELECT '1x8x22	 '
UNION ALL SELECT '6x30x16	 '
UNION ALL SELECT '7x30x22	 '
UNION ALL SELECT '20x12x3	 '
UNION ALL SELECT '18x10x2	 '
UNION ALL SELECT '20x21x26 '
UNION ALL SELECT '11x1x17	 '
UNION ALL SELECT '9x15x15	 '
UNION ALL SELECT '19x14x30 '
UNION ALL SELECT '24x22x20 '
UNION ALL SELECT '11x26x23 '
UNION ALL SELECT '14x3x23	 '
UNION ALL SELECT '1x28x29	 '
UNION ALL SELECT '29x20x4	 '
UNION ALL SELECT '1x4x20	 '
UNION ALL SELECT '12x26x8	 '
UNION ALL SELECT '14x11x14 '
UNION ALL SELECT '14x19x13 '
UNION ALL SELECT '15x13x24 '
UNION ALL SELECT '16x7x26	 '
UNION ALL SELECT '11x20x11 '
UNION ALL SELECT '5x24x26	 '
UNION ALL SELECT '24x25x7	 '
UNION ALL SELECT '21x3x14	 '
UNION ALL SELECT '24x29x20 '
UNION ALL SELECT '7x12x1	 '
UNION ALL SELECT '16x17x4	 '
UNION ALL SELECT '29x16x21 '
UNION ALL SELECT '28x8x17	 '
UNION ALL SELECT '11x30x25 '
UNION ALL SELECT '1x26x23	 '
UNION ALL SELECT '25x19x28 '
UNION ALL SELECT '30x24x5	 '
UNION ALL SELECT '26x29x15 '
UNION ALL SELECT '4x25x23	 '
UNION ALL SELECT '14x25x19 '
UNION ALL SELECT '29x10x7	 '
UNION ALL SELECT '29x29x28 '
UNION ALL SELECT '19x13x24 '
UNION ALL SELECT '21x28x5	 '
UNION ALL SELECT '8x15x24	 '
UNION ALL SELECT '1x10x12	 '
UNION ALL SELECT '2x26x6	 '
UNION ALL SELECT '14x14x4	 '
UNION ALL SELECT '10x16x27 '
UNION ALL SELECT '9x17x25	 '
UNION ALL SELECT '25x8x7	 '
UNION ALL SELECT '1x9x28	 '
UNION ALL SELECT '10x8x17	 '
UNION ALL SELECT '4x12x1	 '
UNION ALL SELECT '17x26x29 '
UNION ALL SELECT '23x12x26 '
UNION ALL SELECT '2x21x22	 '
UNION ALL SELECT '18x23x13 '
UNION ALL SELECT '1x14x5	 '
UNION ALL SELECT '25x27x26 '
UNION ALL SELECT '4x30x30	 '
UNION ALL SELECT '5x13x2	 '
UNION ALL SELECT '17x9x6	 '
UNION ALL SELECT '28x18x28 '
UNION ALL SELECT '7x30x2	 '
UNION ALL SELECT '28x22x17 '
UNION ALL SELECT '14x15x14 '
UNION ALL SELECT '10x14x19 '
UNION ALL SELECT '6x15x22	 '
UNION ALL SELECT '27x4x17	 '
UNION ALL SELECT '28x21x6	 '
UNION ALL SELECT '19x29x26 '
UNION ALL SELECT '6x17x17	 '
UNION ALL SELECT '20x13x16 '
UNION ALL SELECT '25x4x1	 '
UNION ALL SELECT '2x9x5	 '
UNION ALL SELECT '30x3x1	 '
UNION ALL SELECT '24x21x2	 '
UNION ALL SELECT '14x19x12 '
UNION ALL SELECT '22x5x23	 '
UNION ALL SELECT '14x4x21	 '
UNION ALL SELECT '10x2x17	 '
UNION ALL SELECT '3x14x10	 '
UNION ALL SELECT '17x5x3	 '
UNION ALL SELECT '22x17x13 '
UNION ALL SELECT '5x19x3	 '
UNION ALL SELECT '29x22x6	 '
UNION ALL SELECT '12x28x3	 '
UNION ALL SELECT '9x21x25	 '
UNION ALL SELECT '10x2x14	 '
UNION ALL SELECT '13x26x7	 '
UNION ALL SELECT '18x23x2	 '
UNION ALL SELECT '9x14x17	 '
UNION ALL SELECT '21x3x13	 '
UNION ALL SELECT '13x23x9	 '
UNION ALL SELECT '1x20x4	 '
UNION ALL SELECT '11x4x1	 '
UNION ALL SELECT '19x5x30	 '
UNION ALL SELECT '9x9x29	 '
UNION ALL SELECT '26x29x14 '
UNION ALL SELECT '1x4x10	 '
UNION ALL SELECT '7x27x30	 '
UNION ALL SELECT '8x3x23	 '
UNION ALL SELECT '1x27x27	 '
UNION ALL SELECT '7x27x27	 '
UNION ALL SELECT '1x26x16	 '
UNION ALL SELECT '29x16x14 '
UNION ALL SELECT '18x6x12	 '
UNION ALL SELECT '24x24x24 '
UNION ALL SELECT '26x2x19	 '
UNION ALL SELECT '15x17x4	 '
UNION ALL SELECT '11x7x14	 '
UNION ALL SELECT '14x19x10 '
UNION ALL SELECT '9x10x1	 '
UNION ALL SELECT '14x17x9	 '
UNION ALL SELECT '20x19x13 '
UNION ALL SELECT '25x20x8	 '
UNION ALL SELECT '24x20x21 '
UNION ALL SELECT '26x30x2	 '
UNION ALL SELECT '24x2x10	 '
UNION ALL SELECT '28x4x13	 '
UNION ALL SELECT '27x17x11 '
UNION ALL SELECT '15x3x8	 '
UNION ALL SELECT '11x29x10 '
UNION ALL SELECT '26x15x16 '
UNION ALL SELECT '4x28x22	 '
UNION ALL SELECT '7x5x22	 '
UNION ALL SELECT '10x28x9	 '
UNION ALL SELECT '6x28x13	 '
UNION ALL SELECT '10x5x6	 '
UNION ALL SELECT '20x12x6	 '
UNION ALL SELECT '25x30x30 '
UNION ALL SELECT '17x16x14 '
UNION ALL SELECT '14x20x3	 '
UNION ALL SELECT '16x10x8	 '
UNION ALL SELECT '9x28x14	 '
UNION ALL SELECT '16x12x12 '
UNION ALL SELECT '11x13x25 '
UNION ALL SELECT '21x16x28 '
UNION ALL SELECT '10x3x18	 '
UNION ALL SELECT '5x9x20	 '
UNION ALL SELECT '17x23x5	 '
UNION ALL SELECT '3x13x16	 '
UNION ALL SELECT '29x30x17 '
UNION ALL SELECT '2x2x8	 '
UNION ALL SELECT '15x8x30	 '
UNION ALL SELECT '20x1x16	 '
UNION ALL SELECT '23x10x29 '
UNION ALL SELECT '4x5x4	 '
UNION ALL SELECT '6x18x12	 '
UNION ALL SELECT '26x10x22 '
UNION ALL SELECT '21x10x17 '
UNION ALL SELECT '26x12x29 '
UNION ALL SELECT '7x20x21	 '
UNION ALL SELECT '18x9x15	 '
UNION ALL SELECT '10x23x20 '
UNION ALL SELECT '20x1x27	 '
UNION ALL SELECT '10x10x3	 '
UNION ALL SELECT '25x12x23 '
UNION ALL SELECT '30x11x15 '
UNION ALL SELECT '16x22x3	 '
UNION ALL SELECT '22x10x11 '
UNION ALL SELECT '15x10x20 '
UNION ALL SELECT '2x20x17	 '
UNION ALL SELECT '20x20x1	 '
UNION ALL SELECT '24x16x4	 '
UNION ALL SELECT '23x27x7	 '
UNION ALL SELECT '7x27x22	 '
UNION ALL SELECT '24x16x8	 '
UNION ALL SELECT '20x11x25 '
UNION ALL SELECT '30x28x11 '
UNION ALL SELECT '21x6x24	 '
UNION ALL SELECT '15x2x9	 '
UNION ALL SELECT '16x30x24 '
UNION ALL SELECT '21x27x9	 '
UNION ALL SELECT '7x19x8	 '
UNION ALL SELECT '24x13x28 '
UNION ALL SELECT '12x26x28 '
UNION ALL SELECT '16x21x11 '
UNION ALL SELECT '25x5x13	 '
UNION ALL SELECT '23x3x17	 '
UNION ALL SELECT '23x1x17	 '
UNION ALL SELECT '4x17x18	 '
UNION ALL SELECT '17x13x18 '
UNION ALL SELECT '25x12x19 '
UNION ALL SELECT '17x4x19	 '
UNION ALL SELECT '4x21x26	 '
UNION ALL SELECT '6x28x1	 '
UNION ALL SELECT '23x22x15 '
UNION ALL SELECT '6x23x12	 '
UNION ALL SELECT '21x17x9	 '
UNION ALL SELECT '30x4x23	 '
UNION ALL SELECT '2x19x21	 '
UNION ALL SELECT '28x24x7	 '
UNION ALL SELECT '19x24x14 '
UNION ALL SELECT '13x20x26 '
UNION ALL SELECT '19x24x29 '
UNION ALL SELECT '8x26x3	 '
UNION ALL SELECT '16x12x14 '
UNION ALL SELECT '17x4x21	 '
UNION ALL SELECT '8x4x20	 '
UNION ALL SELECT '13x27x17 '
UNION ALL SELECT '9x21x1	 '
UNION ALL SELECT '29x25x6	 '
UNION ALL SELECT '7x9x26	 '
UNION ALL SELECT '13x25x5	 '
UNION ALL SELECT '6x9x21	 '
UNION ALL SELECT '12x10x11 '
UNION ALL SELECT '30x28x21 '
UNION ALL SELECT '15x6x2	 '
UNION ALL SELECT '8x18x19	 '
UNION ALL SELECT '26x20x24 '
UNION ALL SELECT '26x17x14 '
UNION ALL SELECT '27x8x1	 '
UNION ALL SELECT '19x19x18 '
UNION ALL SELECT '25x24x27 '
UNION ALL SELECT '14x29x15 '
UNION ALL SELECT '22x26x1	 '
UNION ALL SELECT '14x17x9	 '
UNION ALL SELECT '2x6x23	 '
UNION ALL SELECT '29x7x5	 '
UNION ALL SELECT '14x16x19 '
UNION ALL SELECT '14x21x18 '
UNION ALL SELECT '10x15x23 '
UNION ALL SELECT '21x29x14 '
UNION ALL SELECT '20x29x30 '
UNION ALL SELECT '23x11x5	 ';

WITH 
TrimmedDims AS
(
	SELECT Dims = RTRIM(REPLACE(Dims, '	', ''))
	FROM #Input
)
, XLoc AS 
(
	SELECT Dims
		, FirstX = charindex('x', Dims)
		, SecondX = LEN(Dims) - charindex('x', REVERSE(Dims)) + 1
	FROM TrimmedDims
)
, Dims AS 
(
	SELECT BoxL = CAST(LEFT(Dims, FirstX - 1) as Int)
		, BoxW = CAST(SUBSTRING(Dims, FirstX + 1, SecondX - FirstX - 1) as Int)
		, BoxH = Right(Dims, LEN(Dims) - SecondX)
	FROM XLoc
)
, Measurements As 
(
	SELECT LW = BoxL * BoxW 
		, LH = BoxL * BoxH 
		, WH = BoxW * BoxH 
		, Slack = CASE WHEN BoxL >= BoxW AND BoxL >= BoxH THEN BoxH * BoxW 
				WHEN BoxW >= BoxH THEN BoxL * BoxH 
				ELSE BoxL * BoxW 
				END
		, RibbonWrap = CASE WHEN BoxL >= BoxW AND BoxL >= BoxH THEN BoxH * 2 + BoxW * 2
				WHEN BoxW >= BoxH THEN BoxL * 2 + BoxH * 2
				ELSE BoxL * 2 + BoxW * 2
				END
		, RibbonBow = BoxL * BoxW * BoxH 
	FROM Dims 
)
SELECT Wrapping =  SUM(LW * 2 
		+ LH * 2 
		+ WH * 2 
		+ Slack)
	, Ribbon = SUM(RibbonWrap + RibbonBow)
FROM Measurements;

*Update – I realized there was a part 2 to each day of code, so I updated the code above to figure out how much ribbon to order, too.  It was just adding the last two columns to the last CTE and the last column to the final SELECT statement.  BTW, I have never voluntarily written so many CTE’s in my life…this is about as normal as helping elves with math.

Let me know what you’d do differently. Also, if there’s interest, I also did the next couple days and could make posts for those as well.

Use Compression to Combine Data Quality and Performance

We’ve been using the wrong data types for all the wrong reasons.  DBAs, developers, data architects, etc. have all been told to keep data as narrow as possible, using the smallest data type to get the job done.  We sacrificed the integrity of our data for the performance of our data, and I’d like to thank Karen López (b|t) for pointing out that performance is NOT our #1 Job.

Disclaimer – Enterprise-ONLY

I’m talking about compression again, so I’m talking about my disclaimer again.  Compression is an Enterprise-ONLY feature that affects the structure of your data.  That means you can’t even restore a backup of your database to anything other than enterprise or developer editions unless you remove compression before the backup.

Also, this post is about maximizing the benefits of compression to reduce the costs of proper data architecture, not implementing compression blindly.  Test it on non-prod, test it again on non-prod, then only implement a documented change with a rollback plan.

Karen López Made Me Do It!

Wait a minute, it’s Lopez, not López, isn’t it?  Nope, she actually spells her name differently because there are so many programs out there that think we can get by with 26 letters in the alphabet.  To keep Karen happy (it’s in your best interest), we need to change our LastName column from VarChar(50) to NVarChar(50).  I know full well that I’m doubling the size of my data for every row, not just Karen’s, to make sure people don’t have to change their names to make me happy.

I’m wrong about half of that last sentence…. Yes, Unicode data is 2 bytes per character uncompressed while non-Unicode data is only 1 byte per character.  The key word being uncompressed, because that’s not your only option starting in SQL Server 2008 R2 (as opposed to 2008 when we got compression in general).  Look at what BOL has to say about Unicode Compression Implementation for more info on that, with the most important part being that you have to implement at least row-level compression to gain this advantage.

Now we can accommodate Karen without complaints.  After all, why should we complain?  She’s a great person to have around.

Running Out Of Numbers

Keeping up with my Karen López theme, she made a great presentation with Tom LaRock (b|t) called The Ticking Timebombs in Your Database that focused a lot on picking the right data type for identity columns.  Spoiler Alert…know your data well enough to choose either BigInt or Int for an identity column, then start with the maximum negative value for that type.

Look at your existing databases to see if you’re running out of numbers using Tom’s post SQL Server Identity Values Check.  You might find that you already have a case where there’s a 4-byte column about to hit the maximum number for an Int.  Maybe it wasn’t seeded with a big negative number, or maybe it’s because the database was just more active than it was designed for.  The important part is that this timebomb is quietly ticking down to the 3 AM phone call Tom just warned us about.

Now I’d like to stand in opposition to Karen and Tom a bit by suggesting starting a BigInt at the maximum negative value for Int.  Say you’re pretty sure an Int is big enough, but you’re not willing to bet your career on it.  However, you really like the idea of a 4-byte column as opposed to an 8-byte column.  Here’s what you do…  Make the column a BigInt (you’re not betting your career here), start it at -2,147,483,648 (maximum negative value for Int), increment by 1, and use compression.

Since the value is in the range of a 4-byte signed Int, the compressed stored value of this column will be 4 bytes.  If you happen to go over 2,147,483,647 then you keep your job (probably a good thing) and compression just loses a little of its magic when this column starts using up 8 bytes.

By the time you get to the point that the data can’t be compressed going forward, row-level compression for this single column only considering a single index only at the leaf level has saved you 16 GB.  Because it’s likely to be part of the clustered index key, it’s going to be part of all of your nonclustered indexes.  Say you have 4 nonclustered indexes on this table, each compressed giving you another 16 GB of savings.  So you saved 80 GB of disk space which is also less index space fighting for space in memory on your server.

You lose about half of your potential values when you use this method, but how much does that matter when I’d have to look up the name of my maximum value which is 9,223,372,036,854,775,807.  The important part is you avoided the extra cost of a BigInt while not failing miserably if you exceeded the maximum value of an Int.

I have kids and don’t get enough sleep as it is.  A compressed BigInt lets me sleep knowing I have both great performance and a large pool of numbers.

Why the Obsession with Karen?

Karen happens to be the perfect example.  She’s a great data architect who spells her name wrong to accommodate for not-so-great data architects.  Follow that up by her making presentations on using larger numeric data types to avoid running out of numbers.  Then I stumbled across her post about my #1 Job when I had compression on my mind, which cause me to make the correlation between compressing the data to get the performance along with the data quality.  Add to all this that she’s one of the most outspoken people (end of sentence).

It’s worth giving her (b|t) links again, with a little flare this time.  Her blog can be found at DataModel.com, and it’s a must-read for anyone creating or altering a column.  If you’re creating or altering lots of columns, then she’s a must-hire consultant.  She tweets like a bot with ADHD as @DataChick.  Unlike a bot, her tweets are pertinent and interesting…sometimes too interesting.

Just don’t ask her about her dad’s middle name, Canadian “zip codes”, the joys of air travel, or shoes.