Population Schedule Form (Click to Enlarge)

As interesting as the Census is for all its historical and social reasons, there’s an equally awesome tale to be told of all its data and the technologies behind it. Setting up a table in SQL Server to store 310 million rows and aggregate results from them would be pretty easy today – many DBAs deal with tables that are orders of magnitude larger than that, but in 1940 it was a major undertaking involving thousands of workers. Today the census is still a non-trivial task, however I’d imagine most of that work goes into getting data from the population into a database, while calculating the results from that is relatively simple.

ETL: Enumerate, Tabulate, Lock Up
Prior to 1960, censuses weren’t mailed to your house like they are now. Instead every household was visited in person by an enumerator, a single person responsible for an “Enumeration District”, or “ED”. EDs varied wildly in size, and could consist of a single block in a large city, or an entire township in a more rural area. The enumerator would stop by and ask questions about each member of your household while writing the answers onto a population schedule form that measured 23.75″ wide by 18.5″ high. Yep, the entire database was on paper. Torn page detection must have been a very serious issue! When the enumerator had information about every last person in their district, they would send their data to the Census Office via log shipping Registered Mail.

Once in Washington, an army of operators transferred each record from the population schedule forms to a punch card. Punch cards had been used to tabulate the census since 1890 and were still the best technology available fifty years later. The 1880 census was tabulated by hand, which took 7 years to complete. Knowing there had to be a better way to calculate results, former census employee Herman Hollerith set out to create a machine that could count results from data stored on punched cards. He won a contract to tabulate the 1890 census, which was completed in only 1 year. By 1900, he had formed the Tabulating Machine Company and greatly increased his fees, knowing he had a monopoly and the Census Office would have no other option than to pay them. By 1910, census employees had developed and patented their own counting machine to avoid using Hollerith’s. The Tabulating Machine Company, which by then had merged and changed names to the Computer Tabulating Recording Company, was nearly bankrupted by the loss of business. They eventually got their act together and were able to turn a profit. In 1924, Computer Tabulating Recording Company changed its name to International Business Machines Corporation.

Women in (1940) Technology: Tabulating By Machine

After the records were copied to punch cards and tabulated by machine, the aggregated results were released immediately for uses like determining congressional seats and allocation of public funds. Since the population schedules contain information on individuals, they are held for 72 years before being released for research purposes. Rather than keep all 3.9 million pages of records on paper, the Census Office used the most compressed format available at the time, microfilm. Apparently they had not yet discovered the rather obscure and undocumented BACKUP CENSUS TO TAPE=’MICROFILM’ WITH COMPRESSION; command. The records released this year are images scanned from that microfilm.

Indexing
Since all the data consists of images, there’s no easy way to index them. Optical character recognition software is pretty good these days, but probably not good enough to pick out the handwriting in these images – most of which is in cursive. Instead everything was indexed by enumeration district, meaning you need to know where someone was living during April of 1940 before you can search for them. Many genealogy websites are now working on indexing this data by name, but it is not expected to be completed for a few months.

Finding Your Family
If you had relatives in the US in 1940 and know where they lived at that time, I highly recommend looking for them. Everything can be found for free at http://1940census.archives.gov. The first thing you’ll need to do is find which enumeration district they lived in. If you have an address, you are very much in luck. If you only have a general idea of where they were, then you’ll probably have to do a bit more work to find them. The census site lets you drill down by state, county, and city, and provides a list of EDs that apply. If you’re searching in an urban area you might need to use maps and/or descriptions returned by the search to narrow down exactly which ED they were in. If the official site isn’t finding anything for you, I’ve also had luck using Steve Morse’s 1940 ED Finder. Once armed with a list of relevant enumeration districts, you can view or download the population schedules from each district and look for people you recognize. You’ll probably end up looking through all the sheets because the entries on the forms aren’t always in order. My guess is that enumerators would start going down a street, skip houses where nobody was at home and then come back to them later.

I was fortunate enough to find all of my family, and it’s really neat to be able to see a snapshot of their lives at a time when my grandparents were close to my age. It also gave me great appreciation for what a chore recordkeeping was in that era! Even if you have no relatives in this census I think it’s still worth taking a look at – it was very interesting to see what kinds of jobs people had, their education level, and how much they were paid. My family was in the suburbs of Chicago at that time, and probably 7 out of 10 people in their area worked in “telephone manufacturing” which would have been at Western Electric’s Hawthorne Works. My wife’s family was in a rural area downstate, and practically everyone worked on a farm and the few who didn’t were employed by the WPA. The best job title I saw when searching for her family was “chicken picker”.

Best of luck if you end up searching for your ancestors. If you find any who were employed as a chicken picker, let me know!

Fun Videos

• Short Video on how the National Archives digitized the microfilm
• Newsreel informing people about the upcoming 1940 Census
• Training film for enumerators

The sp_estimate_data_compression_savings procedure is an excellent tool for estimating how compression will affect an index or partition. It works by sampling 5% of the appropriate pages and applies the desired level of compression on them in TempDB. The results are then extrapolated to give an estimated size for the entire index/partition with that level of compression applied. It’s not perfect, but again it’s an estimate. I recently found however that depending on the datatypes present in the table, that estimate can get very bad. I noticed this in SQL Server 2008R2, but it also shows up in 2012.

For this demo, you’ll need the AdventureWorks database or one of its variants. I used AdventureWorks2008R2 and AdventureWorks2012 in my tests. You’ll also need tables a bit larger than AdventureWorks provides, so I use Adam Machanic’s bigAdventure script to create the dbo.bigTransactionHistory table we’ll be working with.

Once dbo.bigTransactionHistory is set up, we’re going to focus on it’s nonclustered index which is created in the script along with the table. Let’s rebuild this index with a fill factor of 100 so there’s as little fragmentation as possible.

ALTER INDEX IX_ProductID_TransactionDate ON dbo.bigTransactionHistory
REBUILD WITH (FILLFACTOR=100);

Now check the estimated size of it using all 3 compression settings: NONE, ROW, and PAGE.

EXEC sp_estimate_data_compression_savings 'dbo','bigTransactionHistory', 2, NULL, NONE;

EXEC sp_estimate_data_compression_savings 'dbo','bigTransactionHistory', 2, NULL, ROW;

EXEC sp_estimate_data_compression_savings 'dbo','bigTransactionHistory', 2, NULL, PAGE;

(Click to enlarge)

The index is currently not compressed, and you can see that the first result set’s estimated size (with no compression) is close to the current size. It won’t be exactly the same and your values will vary, but the main idea here is that using no compression gives an estimate closest to the current value and row and page compression yield smaller values yet.

Now let’s change things up by adding another column to bigTransactionHistory and giving it a value:

ALTER TABLE dbo.bigTransactionHistory ADD vbm VARBINARY(MAX) NULL;

UPDATE dbo.bigTransactionHistory SET vbm = CONVERT(VARBINARY(MAX), '0xAB');

Then check the estimated sizes again just like before:

EXEC sp_estimate_data_compression_savings 'dbo','bigTransactionHistory', 2, NULL, NONE;

EXEC sp_estimate_data_compression_savings 'dbo','bigTransactionHistory', 2, NULL, ROW;

EXEC sp_estimate_data_compression_savings 'dbo','bigTransactionHistory', 2, NULL, PAGE;

Look at that first result (with no compression) – it’s now way smaller than the current size. It’s actually even smaller than the estimated sizes for row and page compression, and it all seems to be due to the VARBINARY(MAX) column. If you remove that column and rebuild the table, the estimated size with no compression will be back to where it should be.

Strange, huh? I can only assume this has something to do with VARBINARY being a type that doesn’t get compressed, but I don’t see how that should affect the estimation.

BOL states that “If the requested compression setting is same as the current compression setting, the stored procedure will return the estimated size with no data fragmentation and using the existing fill factor.” I can’t see the assumption of no fragmentation being responsible for the estimated size shrinking so much and being smaller than the estimates using compression. Compression can increase the size of an object in certain cases, but the differences here are just too great.

I have submitted this on MS Connect – feel free to vote up if you can reproduce it!

Let’s start off by creating a very simple table about soda pop soft drinks in a test database (mine is named “Sandbox”) and fill it with some data:

USE [Sandbox]
GO

CREATE TABLE [dbo].[SoftDrinks](
	[Name] [varchar](50) NOT NULL PRIMARY KEY,
	[Style] [varchar](50) NOT NULL,
	[CaffeineLevel] [varchar](10) NULL
);
GO

INSERT INTO [dbo].[SoftDrinks] VALUES (N'50/50', N'Citrus', NULL);
INSERT INTO [dbo].[SoftDrinks] VALUES (N'7 Up', N'Citrus', NULL);
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Coca-Cola Classic', N'Cola', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Diet Pepsi', N'Diet', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Diet Rite', N'Diet', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Jolt', N'Cola', N'High');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Mello Yello', N'Citrus', N'High');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Mountain Dew', N'Citrus', N'High');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'Pepsi', N'Cola', N'Medium');
INSERT INTO [dbo].[SoftDrinks] VALUES (N'RC Cola', N'Cola', N'Medium');

Selecting from the table should now get you this:

I should add that the values in the CaffeineLevel column are completely made up. I just wanted a few different values with some NULLs mixed in.

Now that the table is set up, we need a report. To briefly describe the report I created, I’m using the List object with text boxes for Name and CaffeineLevel in the detail section. The report is grouped by Style and the groups are expandable and closed by default. The name of each group also displays a count of the number of records it contains. (Click to enlarge).

To make things a bit quicker, you can download the report project here. It’s setup to use Windows authentication to connect to the Sandbox database on localhost. You may need to make adjustments to the connection information depending on your test environment. I wasn’t able to observe this behavior when previewing in Business Intelligence Development Studio, so you will need to deploy it to a report server.

Once the report is deployed, run it and you should see the 3 Style values with counts, however the details aren’t visible:

Open the Cola and Diet groups and you’ll see the values for Name and CaffeineLevel. Everything looks fine, right?

Now open the Citrus group. Notice how the size of the text in the CaffeineLevel column shrinks?

Closing the Citrus group will return the text sizes for the other groups to normal. For fun, let’s try this again with a non-null value for the first row of the CaffeineLevel column. Add another row to the SoftDrinks table:

INSERT [dbo].[SoftDrinks] ([Name], [Style], [CaffeineLevel])
VALUES (N'123_NotNull', N'Citrus', N'Low');

Refresh the report and open all groups. You’ll see the font size doesn’t change, which should be expected.

It appears that the cause of this is related to the top value in the column. If the top value is null, the font size gets smaller.

Solutions

I found a couple of ways to resolve this. The first is to remove the NULL values from the CaffeineLevel column by adjusting the query. I used the ISNULL() function and tried replacing the NULL values with an empty string, but that had no effect. Replacing null values with a single space did fix the problem, however.

SELECT
   Name,
   Style,
   --CaffeineLevel -- (The original query)
   --ISNULL(CaffeineLevel,'') AS CaffeineLevel -- (This doesn't work)
   ISNULL(CaffeineLevel,' ') AS CaffeineLevel -- (This works)
FROM dbo.SoftDrinks;

Another solution is to filter out the NULL values by using an expression in SSRS. For this I used the IsNothing() function to replace NULL values with a string containing a single space, which resolved the issue. Much like above, replacing NULL values with an empty string had no effect. The expression I used was:
=IIF(IsNothing(Fields!CaffeineLevel.Value)," ",Fields!CaffeineLevel.Value)

Whichever of the above you choose, you’ll arive at the correct (and visually pleasing) result of having the font sizes be the same:

Personally I prefer to alter the query rather than add an SSRS expression, as I am not a fan of seeing <<expr>> in report definitions when it’s not truly necessary.

If anyone else has encountered this situation, I hope you find this solution helpful!

This past Friday I had the pleasure of attending the first-ever SQLFriends event!

SQLFriends is the brainchild of Aaron Lowe (blog | @Vendoran) who, as he described it, wanted to come up with a SQL Server community event based around networking instead of training. Events such as SQL Saturdays and conferences are a great source of training, but many people look forward to the social and networking aspects as well. So why not just have an event focused around networking? SQLFriends was created to fill that void.

The first SQLFriends event was a sell-out lunch at Buca di Beppo in Chicago with 30 attendees and special guest Brent Ozar (blog | @BrentO). Brent answered all kinds of questions about training, certification, consulting, and anything else we could come up with. There was also the opportunity to network amongst ourselves and meet some new faces. Several attendees mentioned how happy they were to meet at lunchtime because they can’t fit evening user group meetings into their schedule.

All in all I’d say the inaugural SQLFriends lunch was a great success. Thank you very much to Aaron for putting it together. If you’d like to find out about future events, head on over to their website at http://sqlfriends.org/ or follow them on Twitter at @SQLFriends.

Super Bowl pools are usually pretty easy – pick a square (or two or three), hand over the cash, and the numbers get filled in later. Building an NCAA bracket on the other hand is a lot of work. Even when picking winners completely at random there are over 60 choices to make. For someone like myself who doesn’t watch nearly enough basketball to make educated guesses about most of the teams it’s a lot of wasted effort. I’d rather spend my time doing something more constructive, such as developing a formula in T-SQL to make my bracket picks for me. Much like last year I’ve done it again.

This year’s bracket is based on 2 things. The first is the idea that a #16 seed team will never beat a #1 seed team. (Since it’s never happened yet it seems like a safe bet). After that, game winners are determined by a formula.

All teams in the tournament are searched in Bing twice according to the following template:
<School Name> <Team Name> Basketball rocks
<School Name> <Team Name> Basketball sucks

For example:
“Illinois Fighting Illini Basketball rocks”
and
“Michigan Wolverines Basketball sucks”
would both be valid queries.

The total number of results returned by Bing for each query are recorded in a table, and to represent a game the following values will be compared for teams A and B:
Team A: A.rocks / B.sucks
Team B: B.rocks / A.sucks

The team with the higher value wins the game.

One flaw in last year’s formula was that it generated a value for each school which never changed. This meant the school with the highest generated value would be the one to win the championship. This time around the outcome differs based on the teams that are compared, which makes things much more interesting. Here’s the bracket that resulted (click to enlarge).

If you’d like to try this yourself you can download the code here. Since search results are constantly changing, you’ll likely have a different outcome than I did. Also be forewarned that while this code does work, it was put together very quickly and is neither elegant nor efficient.

Why Bing?
In case you’re curious, I decided to use Bing search instead of Google because I’m a tightwad and it turns out Google’s Search API isn’t free. If you want to make more than 100 queries per day on Google, you need to pay. Since I needed to make over 100 queries to gather my data plus a few extra during development I went with Bing, which has a much friendlier policy of restricting your query rate instead of limiting the total number of queries per day.

Enjoy the tournament! Should you use my method and come up with a winning bracket, please contact me to discuss my share of the winnings.

I left Chicago around noon figuring I would give myself plenty of time to drive there, find my way if I got lost, and/or sit in traffic. I didn’t end up getting lost or hitting any traffic, so I ended up with a few hours to kill. Fortunately the building MADPASS meets in has this awesome room called “The Pub” which very much looks the part. I think my building needs one of these! I spent a few hours in there, got some work done via wi-fi and made sure my slide deck was in order.

The Pub

After the welcome and introductions, Jes Borland started off with a 30 minute version of her “Making Your Voice Heard” presentation on the benefits of raising your profile in the SQL Server community through twitter, forums, and blogs, and some tips and tricks to help accomplish that goal.

After Jes came food and networking. Dinner consisted of pizza, soft drinks, beer (we need this at the Chicago meetings!!) and birthday cake. February marks MADPASS’ first birthday, and of course no birthday would be fun without some cake. Jes has a photo of it in her blog post about the meeting.

I left dinner a little early to make sure all my equipment was setup to present, and it’s a good thing I did that because I ended up needing every second of that time and then some. Apparently I’m the first person they’ve had that’s given their presentation from a Mac and LiveMeeting didn’t want to play very nicely. I ended up getting started about 20 minutes later than planned and with screenshots recorded via a join.me session. I ended up running a bit long and rushing through the last few slides because I started late, but managed to get everything in. I hope everyone enjoyed it and learned a trick or two. I also got some really great questions afterwards. If you’re looking for the demo scripts I used, you can find the latest version of them along with other resource material here.

I’d like to thank the MADPASS board: Tim Benninghoff (blog), Jes Borland (blog | @grrl_geek), Matt Cherwin (@control_group), Steve Novoselac (blog | @scaleovenstove), and Tony Sebion (@tonysebion) for the opportunity to speak, and also Confio for sponsoring the meeting and providing swag and raffle prizes.