Oct 09

Storing Variable-Length Decimal Data

If you have lots (and I mean LOTS — we have 175 GB of school-related data, and our biggest per-table savings would have been 1.5 MB) of nulls in decimal/numeric columns with large precision, you may be able to save noticeable space with vardecimal, which requires only 2-4 bytes per column when the column is 0 or null.

Oct 08

Spinlocks

A spinlock is a very lightweight lock used for accessing things which must be synchronized, like caches. They don’t show up in the queryable wait indicators. When there is contention for them, you’ll see lots of runnable (waiting to run) processes. This is more likely to happen on x64 systems because the caches can get bigger.

Jul 19

SSMS: Keyboard shortcut for "change connection"

When deploying database code, I’m running each SQL script on several servers, and having to click the “change connection” button dozens of times in SSMS is very frustrating. I submitted this as a bug to Microsoft, and Bill Ramos offered the following method for creating a keyboard shortcut:

  1. Open a Query Editor window connected to your favorite server.
  2. Right click on the Change Connection toolbar button on the Query Toolbar button and select the Customize command at the bottom of the menu.
  3. Right click on Change Connection button again and then change the command name “C&hange Connection…” to “Chan&ge Connection…”. This will change the hot key from Alt-H (which would conflict with the Help menu hot key) to Alt-G.
  4. Finally, in the Right Click menu for the Change Connection button, you need to select the command option to display both “Image and Text”. This exposes the Alt-G command to the Query Editor.
  5. You can now close the Customize dialog and use Alt-G as your hot key!

I love this shortcut. I use it dozens of times daily. It’s still too much overhead for deploying, though, so I either use Query Analyzer (open a window, drag all the files into it, and they all open with a connection to that same server without prompting) or an application I wrote to accept a deploy list, get the files from our source control provider, and put them all into one script with appropriate USE [database] statements.

Apr 20

Orphaned Logins

When you transfer SQL Server logins to a new server with DTS or create them on the new server manually, the SIDs don’t match up with the server that you are importing your database backups from. To avoid this, Microsoft has created handy scripts (for transferring from 2000 to 2005, for transferring between 2005 instances). If you’ve already botched it up, you can find the orphaned logins like so:

EXEC sp_change_users_login ‘Report’

And you can fix them one at a time like so:

EXEC sp_change_users_login ‘Update_One’, ‘username’, ‘login’

which links users in databases with the username “username” to SQL Server logins with the name “login”.