SELECT * FROM [Musings]

Database matters.

May 2006

I presume you have seen the story about the Veterans Administration employee who managed to bring home and have stolen a database with 25 million veteran's social security numbers. The scale of the databases we have today, and how portable they can be, has become a real issue. Years ago, it would just not be reasonable for us to carry such a database in such an easily transported/stealable database. The amount of damage that can be done by one boneheaded move is really amazing. Are you careful with the data you bring home to work on?

Even more freightening is the thought of government workers acting as the stewards of a database that might contain information on something like two b>trillion phone calls. While the phone database was initially targeted at finding terrorists, how many of us use a database for only the one thing that caused it's creation? It will be interesting to see what happens with the phone call database.

Posted by Douglas Reilly at 09:11 AM  Permalink |

Frans Bouma blogs giving links that cover a number of the discussions that have gone on related to using or not using SP's over the years. Frans is the maker of a .NET OR Mapper.

Posted by Douglas Reilly at 10:39 AM  Permalink |

Here is a new MSDN article on why to use stored procedures. This is a topic that seems to be endlessly interesting. I come down personally on the side of using stored procedures wherever possible, mostly to make certain changes without recompiling code. Here are my thoughts on the matter.

Posted by Douglas Reilly at 11:21 AM  Permalink |

Red Gate purchased the Prompt SQL product, renamed it SQL Prompt, and are for now releasing it as a free product (apparently until September). The new version (available here) seems a little faster than the previous version, and the free part is of course nice (I actually purchased 2 licenses to the previous version). The program works with most common SQL Server tools, like Enterprise Manager and Query Analyzer, popping up a menu of possible tables, columns, etc. as appropriate.

Here is an interview I did a while ago with the original author of PromptSQL.

Posted by Douglas Reilly at 09:35 AM  Permalink |

Simon blogs about the difference in performance between using T-SQL functions directly, placing the code in T-SQL functions, and using CLR functions. He discovers that the overhead of functions is non-trivial.

Now, if moving functionality out to functions (T-SQL or CLR) makes your system easier to understand, then for all but the most intense systems, I would still recommend using functions, as breaking things into functions generally makes your code much more understandable. Note that the CLR version of the function worked significantly quicker than the T-SQL function, but slower than moving the code in-line. That said, if a function is used in a dozen queries, having the code in the function will save a whole lot of developer time if there needs to be a change in the code.

Posted by Douglas Reilly at 09:24 AM  Permalink |

One of the things about me that many on-line folks may not know is that I am a long time and current cancer survivor, having first had liver cancer in 98, considered cured, and now fighting a cancer that, strictly speaking, is not curable. The article is here.

It is important to note that there are currently 10 million cancer survivors in the US, perhaps some of them in cubicles nearby.

Posted by Douglas Reilly at 12:33 PM  Permalink |

Wally McClure is getting set up for Code Camps he will be speaking at this season. One of his talks covers SQL CLR Objects. This is an interesting, and potentially complex issue. The files are on this page.

Posted by Douglas Reilly at 11:12 AM  Permalink |

Mike Gunderloy wrote a very nice article on using the new Database Engine Tuning Advisor. It is here. The process is a little different from SQL 2000, where the index tuning wizard was inside Query Analyzer.

Posted by Douglas Reilly at 10:13 AM  Permalink |

Julia Lerman blogs about the history of LINQ. Interesting reading...

Posted by Douglas Reilly at 10:50 PM  Permalink |

A new article on Simple-Talk.com (run by Red Gate, a company I have done some work for now and again - in the interests of complete disclosure) discusses SQL Server performance testing, as well as detailing how indexes can be constructed and built, depending upon the requirements of the database.

Posted by Douglas Reilly at 08:52 AM  Permalink |

Don Kiely blogs about the issues trying to get SQL Server 2005 SP1 installed. Don has had enough. His problem appears to be addressed in this KB article, though Don did not have any success. I think I will pause for a bit before installing SP1.

Posted by Douglas Reilly at 08:41 AM  Permalink |

This is a problem I believe I have run into, but never was able to get any more information on it. It appears, from this blog entry, that small tables (under 500 rows) will not have their statistics automatically updated. In some scenarios, the lack of statistics on these smaller tables can make a difference. I have seen a couple of situations where updating statistics on small tables have resulted in big gains. So, no matter the size of tables involved in a problem query, one step towards solution might be updating manually statistics on all of the tables, even tiny ones.

Posted by Douglas Reilly at 08:33 AM  Permalink |

Scott Guthrie (ScottGu) blogs about using LINQ with ASP.NET. LINQ is Language Integreted Query. Lest you think this is some sort of embedded SQL hack, let me assure you, LINQ is an entirely new way to access your data.

Using LINQ, your database code is integrated into your traditional code in a very cool way. For example, from ScottGu's blog, a Page_Load for a simple "Hello World" page:

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Query;

public partial class Step1 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)
{
string[] cities = { "London", "Amsterdam",
"San Francisco", "Las Vegas",
"Boston", "Raleigh", "Chicago", "Charlestown",
"Helsinki", "Nice", "Dublin" };

GridView1.DataSource = from city in cities
where city.Length > 4
orderby city
select city.ToUpper();

GridView1.DataBind();
}
}

The cool thing is this line:

GridView1.DataSource = from city in cities
where city.Length > 4
orderby city
select city.ToUpper();

It looks like you are setting the DataSource to a variant of SQL inside your C# code. Cooler still is that you can use CLR function like ToUpper() on the variables that are part of the statement.

Deep inside, all of this "magic" is actually backed up by methods on the objects that are called in an entirely reasonable way. There are changes to C# and VB.NET that allows this magic to work. More details on those sorts of things when I more fully understnad them ;).

The LINQ May CTP is here.

Posted by Douglas Reilly at 06:13 PM  Permalink |

If you have ever written a query optimizer, you know how involved it is to do a really good one. Thus, it is cool that the Query Optimization Team is blogging. I encourage you to subscribe. There is already a lot of great content, as well as a pointer to the SQL Programmibility blog, which is good compliment to this blog.

Posted by Douglas Reilly at 10:33 AM  Permalink |

DonXML blogs about Kent Tegels' upcoming talk at the NJ SQL Server User's Group. If you are in NJ and can get to the meeting, I would do so, especially if you want to get the most out of XML in SQL Server 2005. I have been reading a lot about XML in SQL Server, and I honestly think that the details of XML in SQL Server 2005 do require some elaboration. It is a somewhat complicated, but ultimately worthwhile new feature.

Posted by Douglas Reilly at 10:40 AM  Permalink |

For the C/C++ programmers amongst us, the thought of a printf like function might cause a twitch or two. It certainly does for me, especially in the SQL Server world. AS it happens, every now and again, being able to use a function like printf in SQL Server, might be woth the possible ugliness. Printf, in the C/C++ world, accepts a variable number of arguments, and likely has caused a large number of programs to roll over and die. Here is an alternate implementation of a printf like SQL Server function.

Posted by Douglas Reilly at 10:40 AM  Permalink |

Don Kiely blogs about a problem that the University of Alaska had with some personal data being lifted from their network. The disturbing part of this (and something that I expect is not uncommon) is that the system was sitting on a server as part of a kludge to support authentication. It likely did not get the attention it deserves. Even more important, the file uses student's SSN as the identifier, even when students had explicitly requested use of a pseudo SSN rather than their real SSN.

How many files do you have out there with data you wish was not there? Given the constant torrent of new work in most IT groups today, reviewing old vulnerabilities likely do not rise to the level that they should. What can you do about changing that where you work?

Posted by Douglas Reilly at 01:23 PM  Permalink |

One of the cool things added to .NET 2.0 (especially ASP.NET 2.0) is the concept of a well supported provider model. Johan Danforth points to a couple of blog posts on the topic, specifically referring to an SQL Image Provider. The provider model helps you isolate yourself from the implementation details, something that is critical these days, as you never know what is coming next. This is especially true for database developers.

Posted by Douglas Reilly at 10:31 AM  Permalink |

I have written about Damian Mehers, the man behind what was previously known as PromptSQL. PromptSQL gives you intellisense inside Visual Studio, Query Analyzer and Enterprise Manager while editing SQL code. This is a huge help, since it can help prevent lots of minor but annoying typos.

Now Red Gate has announced that they have taken over the product from Damian, and Damian indicates that Red Gate is putting more resources than he could into the product. I am looking forward to getting the latest, as there were a couple of things that made PromptSQL not exactly perfect (notably, being a bit on the slow side initially). The Red Gate version of hte product is currently in beta, but expect the released product to be available in some reasonable time frame.

Posted by Douglas Reilly at 01:10 PM  Permalink |

I will be going to and participating at Tech Ed 2006 in Boston this June. I am very psyched to be going, and will be doing a Birds of a Feather Session, entitle "Geeks With Cancer and Other Serious Diseases." For those of you who do not know me, I am a cancer survivor since 1998, and am still trying to LIVESTRONG. I published an article on coming out as a cancer survivor and software developer, which can be read here.

Posted by Douglas Reilly at 10:37 AM  Permalink |

Per Simon's SQL Server Stuff, the SQL Server April Books Online update is available. Click here to get to that link.

Posted by Douglas Reilly at 10:36 AM  Permalink |