Here are a couple of functions to convert between roman numerals and integers. They are handy for all sorts of little jobs, I've had them in some form or another for some time but dusted them out when someone was trying to tell me how compact some damn new OO… Read more

Today, I needed a way of searching through a database, any database, looking for a certain string. The procedure I had was an old one that used the information_schema and I couldn't figure out how to update it to take in user-types, so I wrote one using system views. It… Read more

A moment ago, I was deep in some coding. What it was is not entirely relevant, With a page-full of carefully crafted code, I hit 'preview'.  Bang. The IDE died. Normally, the programmer will say at that point something like "Aiee! I wish I'd hit 'save' first!", Probably, the temporary… Read more

/*

When you are having to extract just part of a string in TSQL, there are a number of techniques you can use. For a small table, you can encapsulate the process in a scalar function. This is neater, easily tested, and simple to understand. With large tables, you can't… Read more

The other day, I was answering a question on the ASK.SQLSERVERCENTRAL.COM forum from a programmer who had hit a problem where he thought that LTRIM and RTRIM had failed to work. Although, I wasn’t entirely certain, I felt sure it was because of the 'Nul' characters that he admitted had… Read more

You have to admire Microsoft for sticking to their guns, even when being palpably stupid. I refer, of course to the SqlDateTime structure constructor in .NET 2 through to 4. See here

public SqlDateTime(
   int year,
   int month,
   int day,
   int hour Read more

In SQL we are used to the GROUP BY and the type of result it produces. In real life, we are often asked for other types of grouping. One of these is to produce a list of the members of each set  This is easier to show by example.

Adams… Read more

/* Have you ever come across the problem where you have to remove spaces from a string that has too many? You'll know that if you do the obvious ...*/
SELECT REPLACE ('this         has            too                          many                               spaces' ,'  ', ' ')
-- ... you end up with your problem… Read more

/*I'd be very surprised if you've ever used the old COMPUTE clause in SQL Server. It is a bit mad, and it is flagged for deprecation. It is an old Sybase legacy that was used when reports were printed directly from the output of SQL. You can still do it,… Read more

--it is quite startling to see that this, in TSQL, executes.
Select    \
/*
---------------------
0.00
 
(1 row(s) affected)
 
so does this....
*/
Select    null  /\/\/\/\/\/\/\/\/\/\/\/ Read more

--Whilst trying to design a more effective way of detecting dependencies in a SQL Server database, I was mulling over the cruelty of SQL Server's parser in allowing this to compile and run..
create schema int
create table int.int (int int)
select int.int. Read more

If you have a database application that is running slowly, or is straining the server, what do you do? After you’ve eliminated the obvious malaises such as locking problems, do you buy more hardware, tweak the indexes, or do you rewrite the most hard-working queries until they run faster?

My… Read more

/*
A couple of years ago, I wrote the Simple-Talk Prettifier. This is really no more than a stored procedure that renders SQL code  as HTML. I then suggested that the code should be allowed on Simple-Talk, and had the humiliation of seeing the contemptuous ease with which Neil and… Read more

You may think that the task of teasing out the exact nature of the data and processes within a company is a boring job. Not a bit of it! It is half way between archaeology and forensic science, and I have spent some of the happiest years of my life… Read more

I was reading a MCP training manual for SQL Server the other day. It was good. The whole of TSQL was covered in four hundred pages. It didn’t go into anything esoteric, mind you, but it explained such things as remote transactions and locking hints with precision and clarity. There… Read more

Going through one’s father’s belongings after his death is always a poignant, bitter-sweet experience. I was going through his most precious books a while back; there were books that were written by him, books by friends and relatives, books about friends or relatives, books of precious poems. There was a… Read more

I spent a fascinating hour at the Teched Shop this afternoon. I was trying to get a feel for where the publishers were seeing the size of the market in the SQL Server space. I bumped into Brian Desmond whose epic Active Directory book is now in its 4th edition,… Read more

One of the charming people who helped me register at Teched told me where we, who had registered, could get a free Tee-shirt, Microsoft bag and a water-bottle. Phil K Dick introduced the term 'Kipple' for all such stuff, but sad old gits like me are suckers for it. I… Read more

Steve Jones isn't usually the wild man of publishing, but on April 1st, a strangely anarchic spirit breaks loose. Having plotted a number of April Fool jokes in SQL Server Central, he egged us on to join in the fun. The result was The Concept of Cardinal Reciprocity-A Primer by… Read more

 Most of the time, you want to have a SQL result in a particular order. Occasionally you just don't care. Just now and then,you actually want to shake the dice and get your data back in a random order. I quite often like to get a random order so as… Read more