Is there any benefit to my rather quirky character sizing convention?

Question

I love things that are a power of 2. I celebrated my 32nd birthday knowing it was the last time in 32 years I'd be able to claim that my age was a power of 2. I'm obsessed. It's like being some Z-list Batman villain, except without the colourful adventures and a face full of batarangs.

I ensure that all my enum values are powers of 2, if only for future bitwise operations, and I'm reasonably assured that there is some purpose (even if latent) for doing it.

Where I'm less sure, is in how I define the lengths of database fields. Again, I can't help it. Everything ends up being a power of 2.

CREATE TABLE Person
(
    PersonID int IDENTITY PRIMARY KEY
   ,Firstname varchar(64)
   ,Surname varchar(128)
)

Can any SQL super-boffins who know about the internals of how stuff is stored and retrieved tell me whether there is any benefit to my inexplicable obsession? Is it more efficient to size character fields this way? Can anyone pop in with an "actually, what you're doing works because ....."?

I suspect I'm just getting crazier in my older age, but it'd be nice to know that there is some method to my madness.

Answer 1

Well, if I'm your coworker and I'm reading your code, I don't have to use SVN blame to find out who wrote it. That's kind of cool. :)

Answer 2

The only relevant powers of two are 512 and 4096, which is the default disk block size and memory page size respectively. If your total row-length crosses these boundaries, you might notice un-proportional jumps and dumps in performance if you look very closely. For example, if your row is 513 bytes long, you need to read twice as many blocks for a single row than for a row that is 512 bytes long.

The problem is calculating the proper row size, as the internal storage format is not very well documented.

Also, I do not know whether the SQL Server actually keeps the rows block aligned, so you might be out of luck there anyways.

Answer 3

iPill: There's a drug for that.

Answer 4

With varchar, you only stored the number of characters + 2 for length.

Generally, the maximum row size is 8060

CREATE TABLE dbo.bob (c1 char(3000), c2 char(3000), c31 char(3000))

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'bob' failed because the minimum row size would be 9007, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The power of 2 stuff is frankly irrational and that isn't good in a programmer...