Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Roman Numerals to integers, and Vice Versa, in SQL:

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

9 comments, 3,704 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 20 September 2011

Brute-force searching for data.

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

7 comments, 1,469 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 8 July 2011

Belt and braces

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

0 comments, 1,072 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 30 June 2011

'Topping and Tailing' strings in database tables

/*

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

0 comments, 1,003 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 14 June 2011

A Quick TRIM, Sir?

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

0 comments, 943 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 28 January 2011

Silly Billiseconds

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

6 comments, 1,094 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 13 January 2011

Listing members of a group in SQL Server.

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

0 comments, 680 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 11 October 2010

Cleaning up excess contiguous spaces in strings. A mystery.

/* 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

4 comments, 1,417 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 30 September 2010

COMPUTE: now it is going, what else will do it?

/*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

6 comments, 739 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 23 September 2010

The Killer Backslash in TSQL

--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

7 comments, 1,981 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 29 July 2010

Ambiguities and the wild object.oriented brigades

--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

0 comments, 841 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 29 July 2010

Phil Factor SQL Speed Phreak Competition

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

4 comments, 1,142 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 25 October 2009

Evil Code

/*
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

14 comments, 1,314 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 14 August 2009

The Joy of Data Modelling.

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

9 comments, 1,079 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 11 August 2009

When the next object-persistence boat comes in.

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

6 comments, 671 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 22 June 2009

The secret skill revealed in the dusty booklet

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

24 comments, 688 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 18 June 2009

The Walking Billboard

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

4 comments, 538 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 10 May 2009

Teched Water Bottle

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

2 comments, 685 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 10 May 2009

Professor Hugh Bin-Haad.

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

3 comments, 686 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 3 April 2009

Accessing results in a random order: Towards the automated Thank-you letter

 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

11 comments, 1,361 reads

Posted in Phil Factor's Phrenetic Phoughts: The SQL on 16 March 2009

Older posts