SQL
Jun 09 2010
Text Search in SQL Server Stored Procedures: Overcoming sys.comments Insufficiencies
Searching a SQL Server database’s sys.comments table has long been a quick way to find stored procedure usage of another database, table, or column. Likewise, clicking “view dependencies” on a table is a quick way to find the opposite: which stored procedures (on the local db) refer to that table. I recently came across a scenario where neither met my needs, so I had to expand the capabilities of the former. The limitation of the first approach is parsing the text field to find multiple items. CHARINDEX and SUBSTRING functions only get you to the first, and looping can be incorrect as line breaks may occur haphazardly mid-text.
Feb 15 2010
Problem:
Our team needed to create a data set listing retail stores belonging to different territories for performance tests that involved store selection. The data set was to be used by virtual users signing-in as different territory owners. The challenge was that the territory and store numbers in the data set should be as random as possible. For example, if there are 20 territories and each territory has 10 stores, the data set should contain the 20 territories with their first stores, followed by the same 20 territories with their second stores and so on.
Nov 11 2009
Hampton Roads .NET User Group November 2009 Presentation
I presented a talk called "Enterprise Data Validation" at the Hampton Road .NET User Group this evening. The premise was simple. Data validation needs to happen in all the tiers of a modern application but the validation rules should be defined only once to avoid synchronization errors. In this talk, I showed how to extend SQL Server using extended properties to store regular expressions for data validation as column metadata. I also showed how to add a regular expression matcher to SQL Server using the SQL CLR and how to add check constraints to invoke the regular expression parser. Then I built a WCF service to query the validation metadata to make it available in other application tiers. I quickly assembled WCF service host and client showed how you could bring all of the elements together to create a working Enterprise Validation solution.
Oct 24 2009
SQL UNIQUEIDENTIFIERs are Really Big Integers
I wrote a blog post called How SQL Server Sorts the UNIQUEIDENTIFIER Type and another one called Ordering the SQL UNIQUEIDENTIFIER Type Numerically Correct for Reporting a while back. As a result, I get a lot of e-mails from people struggling with UNIQUEIDENTIFIER values in Microsoft SQL Server. That's cool because I like helping other developers. The mistake that most people make when working with this data type is treating them like strings. However, UNIQUEIDENTIFIERS are absurd looking integers, really big ones. We show them in hexadecimal format to make them more compact which adds to their absurdness, I suppose.