Since When

I found a URLEncode function for SQL Server on the Internet and promptly ported it down to my code base, however like some code I find on the Internet it wasn't very good, here is a suggestion for all of us posting code: include the test cases also -- sort of like Blog Unit testing. Anyways here is the problem simplfied in this example code: DECLARE @c char SET @c = 'é' SELECT CASE WHEN (@c LIKE '[a-z]') THEN 'Huh?' ELSE 'Better' END Know I know that é is not between a and z, however the output says it is. There is only 26 letters from a to z, at least that is what I am teaching my three year old daughter. So what gives, must be a SQL server bug right? Whoops. Checking the documentation for LIKE it appears that I was being a little small minded -- since only my native alphabeta has 26 letters between a-z, there are some alphabetas that have a few extra letters and my SQL server configuration is taking them into consideration. So what do I have to do to make this work -- COLLATE. Which really means tell SQL Server what language I would like to match against. This works better: DECLARE @c char SET @c = 'é' SELECT CASE WHEN (@c LIKE '[a-z]' COLLATE SQL_Latin1_General_CP437_BIN ) THEN 'Huh?' ELSE 'Better' END Now, back to URL encode, I would like URL encode to translate: é to %e9, so that I can get the URL with the name Café in them to appear correctly in XML. So why does é need to be encoded for the browser? Maybe it too is small minded. {6230289B-5BEE-409e-932A-2F01FA407A92}

Disappointment Over Indexed Views

I upgraded to SQL Server 2005 Enterprise Edition today in order to use indexed views to implement better performance for my web site. Basically to short cut the multiple joins it requires to denormalize my web site data into something interesting. After upgrading, I was disappointed to learn that I couldn't use indexed views to help my performance. Indexed views are severly limited you can't have any outer joins, use other views, or do unions. So for my data, and almost any highly normalized data that you want join together to make it appear more denormalized you can't use indexed views. By indexed views I mean clustered unique indexed views that you can only get in enterprise edition SQL server. These views rewrite the data into a cluster and update when the underlying table updates. As long as the table doesn't bind to another with an outer join, or you want to stack views together you can achieve some performance gains. Just for a minute, after laboring 2-3 hours to get my index to create on my view and never seceeding, I thought about going to look at the Oracle documentation. It is always really frustrating working with SQL Server and discover that it has holes in the full feature set that you really want. {6230289B-5BEE-409e-932A-2F01FA407A92}

Positive Integer In Transact-SQL

While the IsNumeric Function in Transact-SQL is good for finding out if a varachar is a integer or a deciaml. Sometimes it is nice to find out if the varchar is a positive integer here is how: CREATE TABLE #Temp (Number varchar(25)) INSERT INTO #Temp (Number) VALUES ('2.4') INSERT INTO #Temp (Number) VALUES ('4') INSERT INTO #Temp (Number) VALUES ('-4') INSERT INTO #Temp (Number) VALUES ('-4a') INSERT INTO #Temp (Number) VALUES ('4-a') INSERT INTO #Temp (Number) VALUES ('4-') INSERT INTO #Temp (Number) VALUES ('4444') SELECT * FROM #Temp WHERE ISNUMERIC(Number) = 1 AND NOT Number LIKE ('%.%') AND NOT Number LIKE '%-%' DROP TABLE #Temp With test cases. Modify it to find integers both positive and negative: CREATE TABLE #Temp (Number varchar(25)) INSERT INTO #Temp (Number) VALUES ('2.4') INSERT INTO #Temp (Number) VALUES ('4') INSERT INTO #Temp (Number) VALUES ('-4') INSERT INTO #Temp (Number) VALUES ('-4a') INSERT INTO #Temp (Number) VALUES ('4-a') INSERT INTO #Temp (Number) VALUES ('4-') INSERT INTO #Temp (Number) VALUES ('4444') SELECT * FROM #Temp WHERE ISNUMERIC(Number) = 1 AND NOT Number LIKE '%.%' DROP TABLE #Temp {6230289B-5BEE-409e-932A-2F01FA407A92}

Adding To CheckBoxList

I always seem to be subclassing the standard .NET web controls and adding my own little features. Here is an enhancement that I made to CheckBoxList:

public String[] SelectedValues
{
    get
    {
        List<String> output = new List<String>();
        foreach (ListItem listItem in Items)
            if (listItem.Selected)
                output.Add(listItem.Value);
        return (output.ToArray());
    }
}

Now that I have this method I can convert the CheckBoxList to a CSV like this:

String csv = String.Join(",", checkBoxList1.SelectedValues);

{6230289B-5BEE-409e-932A-2F01FA407A92}