Don’t use wildcard in UDF and Views

In SQL Server the * wildcard is useful feature when quickly putting together a query.
SELECT *
FROM MyTable

However there are certain situations where you should avoid using it. Namely you should not use it when you’re returning data from user defined function or a view (and possibly procedures as well). The reason behind it, is that SQL Server stores what a view or function returns (even if you don’t explicitly define it). When you ALTER or CREATE a function, the format of the returned data is defined. Let’s say your UDF executes this query:

SELECT *
FROM Orders

And your Orders table contains following columns:

  • Id
  • CustomerId
  • OrderTotal
  • IsPaid

This function will obviously return all the rows in the Orders table and everything is fine. However a problem will arise when you decide to alter the structure of the Orders table. Let’s say we will insert an IsCnancelled column right before IsPaid. So the table will look like this:

  • Id
  • CustomerId
  • OrderTotal
  • IsCnancelled
  • IsPaid

The data returned by the UDF will be missing the IsCnancelled column, but that’s not all. The contents of the IsCnancelled column, will be presented as the IsPaid column. Such an error could wreck havoc. If you ALTERR the UDF (no changes are actually required), the structure of the returned data will be updated, but it’s better to avoid using the * wildcard altogether, than trying to manage this mess.

On the other hand, using wildcard in subqueries or CTE inside an UDF should be safe.

All this is probably obvious stuff to most people working with SQL Server and my example is very basic, but I had to fix problems caused by such use of the wildcard in the past and they can get ugly.

Advertisements

Disappearing objects

While working on Rikaikyun I came across this strange problem, after pushing one of my revisions I tried running my app on my target device (Onyx Boox T68 Lynx) and found out it no longer works (even though I had no problems running on Chrome and another Android device). Debugging Phonegap apps on a Lynx is a pain, because Phonegap Development App cannot be used there, so you have to probe blindly in hopes of finding what could be causing the problem.

My first finding was that one of my classes suddenly became undefined. I spare you the details of what I tried or not, but the source of the problem was mismatched letter case in the name of one of the JS files. Now, I’m smart enough not to reference JS files using wrong case, but as it happen, at one point in the project, the file had a different name (only the case was different). Given that I use Windows and store my project on GIT, I’m sure you can guess what went wrong… Anyway GIT didn’t notice the fact that I change the case of the file and when I finally did a merge it renamed my file to what I named it originally.

I guess when you use the file:/// protocol in Android browser you have to pay attention to the case in file names, because requests to those files does appear to be case sensitive.