When is CRUD enough?

Let’s lay some groundwork before we start the rant.

CRUD is an acronym for the Create, Read, Update and Delete operations done to tables in a database.  I believe I can safely say “All database applications do at least the CRU of the CRUD”.

Now for the argument

I have never dealt with an enterprise level application that does just plain old CRUD.  In every case, there was some sort of logical requirement that precluded the CRUD requirement.  In the academic world there are examples of applications where CRUD operations can be done, but I have not been involved with one for a very long time.

Here’s an example:

Tables: UserInfo, ProductInfo, EventHistory

Assume EventHistory is a generic event history table with the key from the foreign table (UserInfo, ProductInfo, etc.)

Now, let’s add a new UserInfo record with the appropriate EventHistory record, this would require an Insert of the  UserInfo record with the retrieval of the key column then a write to the EventHistory table to record the user and such of the action.  Two trips even in a transaction, bleh.

How about writing a stored procedure to do the same.  One trip with little overhead to the calling application.  Simple, nice.

I’m sure there are a ton of people saying it could be done in a trigger and yet others think it should be done in the business application layer but in my opinion, the database is a good place to database oriented things.

SQL Server: Like with Underscores

There are times when you need to do a LIKE based search and need to find an underscore.  I was surprised to find the underscore is reserved as a single character wild card.

There are two ways to get this done.  First is the explicit ESCAPE definition as follows where the backslash is the escape character.

-- looking for anything with an underscore
SELECT  *
FROM    dbo.SomeTable
WHERE   ColumnName like '%\_%' ESCAPE '\'

To me, a better approach is surrounding the character is square brackets.

-- looking for anything with an underscore
SELECT  *
FROM    dbo.SomeTable
WHERE   ColumnName like '%[_]%'

The second approach may get a little confusing when looking for brackets though.

-- Looking for '[' + sometext + ']'
SELECT  *
FROM    dbo.SomeTable
WHERE   ColumnName like '%[[]%[]]%'

Hope this helps.