A pen and handwriting

Make OSX and Linux command line behave more like Windows

Lately, I have been doing more and more work in my native OSX image. And I am really loving it.

However, being an old Windows user, I find a lot of habits hard to break and I constantly find myself wanting the standard Bash command line to behave just a little more like good old DOS. To avoid the same typing error over and over again, I found these modifications to be useful. I am sharing them here in hope they may help other people who are transitioning from Windows. Continue reading…

SQL Server Logo

On using Query Hints

The revealed wisdom in the SQL Server community has generally been that query hints should only be used as a last resort. For a complete novice of SQL Server, I would agree. But to call avoiding query hints a “best practise” is taking it too far.

For anyone with a little experience who knows what they are doing, I find that query hints are not only a good reactive solution, they are a proactive, design time, tool.

Continue reading…

Moebius Strip

Table Pattern: Rotating Log / Ring Buffer

Most database systems need some form of log table to keep track of events, for example for auditing purposes. To avoid the log growing forever, it is often a good idea to regularly rotate old log entries out of this table. For small log tables, running a DELETE statement works well for this purpose. However, as the log throughput grows, it is often preferable to use partition switching instead. In this blog, I will show you an implementation of a rotating log table.

Continue reading…

Shooting yourself in the foot

SELECT INTO – Moving Data From A to B

When building a data warehouse, you often find yourself needing to move data that is the result of a query into a new table. In SQL Server, there are two way to do this efficiently:

  1. SELECT INTO
  2. INSERT INTO WITH (TABLOCK)

While both techniques allow to you achieve bulk logged (i.e. fast) inserts, I am going to argue that method 2 is preferable in most situations.

Continue reading…

Domino pieces about to fall

High Availability, at High Speed

In the quest for 100% uptime, a great many hours must be invested in careful design. Even when you think you have eliminated every Single Point Of Failure (SPOF) – something new always shows up. It get’s worse: All this the effort is multiplied if you want to BOTH achieve high availability AND run a system at high speed.

In this blog, I will share some lessons we learned the hard way while tuning our high speed SQL Server mirror.

Continue reading…

IBM M13 Keyboard

Turn off CAPS LOCK and INS

I am a ten finger typist doing around 5 strokes/second – though I do make a lot of mistakes. I learned the craft on a good old fashioned typewriter. The world has moved; unfortunately, keyboards have not. There are keys left in a modern keyboard that are simply of no use anymore – grim reminders of the age of typewriters or just leftovers from ancient (before 2000) times in computers.

Two such keys are CAPS LOCK and INS. As fast typist, you simply don’t need CAPS LOCK. And I have no idea what anyone needs INS for anymore. In other words, hitting either of those keys is always an error. Fortunately, there is a solution that does not require you to take a screwdriver to your keyboard: you can turn those keys off.

The attached registry file will do this nicely. Just unzip and apply.

Registry-IconDisableUselessKeys.Reg

Featured image: The legendary IBM M13 in Stealth Black.

Shooting yourself in the foot

BULK INSERT with Linux Line Endings

After all these years, Mac, Windows and Linux still cannot agree on what special characters are used to represent a new line in a text file.

Windows generated text files will typically end a line with CR+LF (0x0D0A). Unix/Linux prefers LF alone (0x0A) and old versions of Macs will use CR alone (0x0D). See this Wikipedia entry for details.

When receiving and loading text files into databases, this causes a mess unless you are careful. Unfortunately, this is particularly true for the BULK INSERT command in SQL Server because the documentation is misleading.

Continue reading…

Two clueless consultants

How I hire a DBA or Database Developer

Livedrive is currently looking to hire a rock star DBA and developers with a strong understanding of databases. We have 100TB of mySQL data online and a SQL server mirror running at 15k tx/sec non stop (peaking at 200K) with a nice little 3TB OLTP system.

And my goodness – rock star DBAs are hard to find. For those of you looking for one – or thinking you are one – I wanted to write up my advice.

Here is how I hire a DBA:

Continue reading…