Caffeine-Powered Life

Loading tweet...

Filtered Indices in SQL Server 2008
Aug 10
31

I’ve got a business requirement that values must be unqiue, but are not required, like a drivers license number. I would like to enforce this rule in the database server. How do we solve this?

CREATE NONCLUSTERED UNIQUE INDEX IX_Unique_DriversLicenseNumber
ON [User] (DriversLicenseNumber)
WHERE [User].[DriversLicenseNumber] IS NOT NULL

Check out the filtered index design guidelines from Microsoft.

However, there is a problem with this solution. If you attempt to add a column to the table, you need to remove the index, add the column, and replace the index.

Prior to SQL Server 2008, the solution would have been to create a schemabound view, then put a unique index on the view. To create a schemabound view…

CREATE VIEW VW_DriversLicenseNumbers
WITH SCHEMABINDING
AS
SELECT DriversLicenseNumber
FROM [dbo].[User]
WHERE DriversLicenseNumber IS NOT NULL

This doesn’t have the same problems when adding a column, but it does run into various issues with replication environments. Microsoft recommends using a filtered index, as opposed to an indexed view, in almost all situations. Check out the design guidelines for more details.

Special thanks to Remus Rusanu at stackoverflow.com for this one.

Comments In Code: Evil! Or Are They?
Aug 10
29

The developer blogosphere is in a fuss about in-code comments. In my opinion, most code comments that I’ve seen are just not necessary. It’s more than possible to refactor, extract method, and rename your way to cleaner code where comments just aren’t needed. But that only describes what the code does.

Where I find code comments far more helpful is knowing the why. Why did a developer solve a particular problem in a particular way? Are there limitations or constraints that may not be clear? If you check the ASP.NET MVC source code, you’ll find that there are a lot of comments like this. They don’t describe what is going on, but they do describe why the developer chose to solve a particular problem in some fashion.

Aug 10
28
Bicyclists Injured in Mulholland Drive Collision

Three cyclists had to be airlifted to a hospital after being hit by an 81-year-old driver.

The comment rage is both amusing and sad. Motorists exceed the speed. Cyclists run red lights and roll stop signs. Both groups are at fault here. I rolled a stop sign last night right in front of a cop. No, I didn’t see the cop until after I went through the sign. I did pedal on a few yards wondering what would happen. Can I get a ticket with just a name and SSN? Anyway, when I see a car, I always yield to the big metal box. If I hit him, I might piss him off. If he hits me, I could be dead. Let’s see who wins that contest. Not me.

Ruby Koans #3 (Strings)
Aug 10
27

There’s a question in the string file.

# THINK ABOUT IT:
#
# Ruby programmers tend to favor the shovel operator (<<) over the
# plus equals operator (+=) when building up strings.  Why?

I was able to find the answer here. The += operator will create a new instance of a string. The << operator will append in place, as long as there’s enough room for the currently allocated string object.

Ruby Koans #2 (Arrays)
Aug 10
26

I’ll admit that it took me a little while to figure this one out, but I eventually got it.

def test_slicing_arrays
  array = [:peanut, :butter, :and, :jelly]

  assert_equal [:peanut], array[0,1]
  assert_equal [:peanut, :butter], array[0,2]
  assert_equal [:and, :jelly], array[2,2]
  assert_equal [:and, :jelly], array[2,20]
  assert_equal [], array[4,0]
  assert_equal [], array[4,100]
  assert_equal nil, array[5,0]
end

The first 6 assertions were no big deal, but that last one confused me. Then I figured out that you can always append to an array by accessing the next element. If your array has 4 elements, then you can access indices 0 through 4, where 0 through 3 are already assigned, and the 4th index is the unassigned 5th element. So, array[4,1] is an empty array slice, but array[5, 1] is nil.

Running Through The Ruby Koans
Aug 10
25

If you’re new to Ruby, check out the koans. They’re a great way to learn some of the finer details of the Ruby language.

Check out this little gem I picked up from the second koan on learning about nil.

  • In Ruby, everything is an object.
  • In Ruby, even nil is an object. It is not a pointer exception that you get in other languages.
  • The nil object has methods on it. You can use nil.to_s, nil.inspect, and nil.nil?, because all Ruby objects have those methods defined and inherited from the base object class.

Finally, check out this little artifact from the comments.

# THINK ABOUT IT:
#
# Is it better to use
#    obj.nil?
# or
#    obj == nil
# Why?

Of course, after doing the koans you’ll realize that the Rubyist will use the first version as a form of inspection, where other developers from other languages would do the second version.