Project 31-A

We have a varchar(50) in our database called item number. Most of our customers use this as a number, entering numbers. However, some of them for flexiablity (not all square items go into round holes) want to use it as the abstract number, entering in things like: "SC-1002S". The issue came up the other day, that a majority of our users want the numbers they add to sort like numbers, we were sorting this like strings: (1,11,2,22,23,3 ...). So Steve came up with this great idea of a double sort, first trying to CONVERT the string to a number. Here is my T-SQL for the idea: SELECT * FROM Items ORDER BY (CASE WHEN ((ISNUMERIC(ItemNumber) = 1) AND (NOT ItemNumber LIKE '%E%') AND (NOT ItemNumber LIKE '%.%')) THEN CONVERT(int, ItemNumber) ELSE NULL END), ItemNumber First thing to note is that you can use a CASE expression in an ORDER BY Clause. Second thing is that first we try to convert the string to a number, if we can't it is a NULL, secondarly we s...

The local Bellingham .NET User Group was blessed by having a speaker from the Microsoft C# compiler team visit and tell us about C# 4.0. One of the things that she kept repeating was that "fill in new feature" was done to save typing. Here is my suggestion for a language change: I do a lot of this: public class X { private int _id; public X(int id) { _id = id; } } Most of the time I have several overloads of the constructor, wouldn't it be nice if I could just write this: public class X { private int _id; public X(int _id) {} } If the compiler sees a variable in the constructor with the same names as a member variable of the class (an illegal syntax already) it would know that I want to assign the member variable automatically. The compiler already has to check for conflicts, so it is doing most of the work already. And it you didn't want this to happen, you could just change the variable name to be unique to the local an...

Lately I have been wondering why this will not compile in C# 2.0 Visual Studio 2005: int? x = ({ expression } ? null : 1); The error I get is: Type of conditional expression cannot be determined because there is no implicit conversion between ' ' and 'int' I would assume that the compiler "knows" that x can be null, or any int. However, it can't cast null to an int here. Probably becuase it is evaluating the ternary expression first. This works: int? x; if ({ expression }) x = null else x = 1 Probably because it evaluates the expression first then the assignments. Anyways a work around (suggested by Andy) is: int? x = ({ expression } ? (int?)null : 1); Which really shouldn't be nessecary. {6230289B-5BEE-409e-932A-2F01FA407A92}

Critical updates last night (Tuesday 2/10/2009) on my Windows 2008 Server with Hyper-V host operating system, caused my virtual machines not to boot. The error is: "An error occurred while attempting to change the state of the virtual machine [machinename]" '[machinename]' failed to intialize An attempt to read or update the virtual machine configuration failed. The solution to the problem is to: 1) Right click on the virtual machine in the server manager under Roles | Hyper-V | Hyper-V Manager 2) Choose Settings and write down all your settings you are going to need to re-enter them. 3) Choose Delete 4) You get a warning that the configuartion will be deleted, however not the hard drives. 5) Choose Yes. 6) Click On New From the Actions Window and Choose Virtual Machine 7) Walk through the wizard and reset up the virtual machine, when you get to the connect virtual hard disk window, choose: "Use an exisitng virtual hard disk" and select your old disk...

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

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

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 #Tem...