Category Archives: Database Theory

Rolling back transactions with cursors and local variables.

I’ve been doing some work on a credit card payment system lately. Obviously this needs to be robust and consistent so I’ve been working on error handling and “transactionalising” certain parts of the process. I needed to answer a couple … Continue reading

Posted in Database Theory, SQL Server, T-SQL | Tagged | Leave a comment

In Memory OLTP with SQL Server 2014

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimised to be memory resident. So I wanted to have a play – and I … Continue reading

Posted in Database options, Database Theory, SQL Server, T-SQL | Tagged , | 1 Comment

Wildcards and Indexes

I was asked a good question the other day, one that I wasn’t sure of the answer. The question was “does SQL Server use an index if you have a wildcard in the search predicate?” My initial thought was it … Continue reading

Posted in Database Theory, SQL Server, T-SQL | 1 Comment

Don’t believe everything you read: Truncate table is not logged

Actually I’m not sure if anyone still believes this anymore. If you read the BOL description carefully it states that truncate de-allocates the pages rather than deleting the rows – meaning only the de-allocations are logged – resulting in far … Continue reading

Posted in Database Theory, SQL Server, T-SQL | 1 Comment

Quick tip: Count table rows in a database, and locate those rows in the data files.

Here are a couple of quick database and table level queries that I have found useful lately. How many rows in each table of a database? The first temptation with this one was to use something like sp_MSforeachtable with a … Continue reading

Posted in Database options, Database Theory, SQL Server, T-SQL | Leave a comment

Working with bitwise data in SQL Server

I was working with a group of students on a design brief. The brief was to partially implement a database design based on the LinkedIn web site. A discussion came up around the possibility of implementing the contact preferences (essentially … Continue reading

Posted in Database Theory, T-SQL | Leave a comment

Dependencies

One of the fundamentals of an RDBMS is the R. R for relational – meaning there are often multiple objects within a database schema that relate to, or are dependent on other objects. The first one that will spring to … Continue reading

Posted in Database Theory, T-SQL | 1 Comment

Book Review: The Information, by James Gleick

My intention is for this blog to be 90% technical database topics with the added depth, breadth and interest of 10% other interesting peripherally related topics. Today’s peripherally related topic is the book The Information, by James Gleick. Gleick’s name … Continue reading

Posted in Books, Database Theory | Leave a comment