<Nathan H. Omukwenyi>

The Damerau–Levenshtein distance or the “edit distance” between two strings can be used in SQL Server database applications to search for strings when you’ve reached the limit with the LIKE clause or the full-text indexing features. Used directly in T-SQL, the algorithm has some obvious limitations like:

• When used for fuzzy matches, the strings need to be relatively short  since the algorithm operates in the worst case at O(m*n) where m and n are the respective string lengths. This fact can be made worse if the algorithm is called as a scalar-valued function as part of a multi-column select over a very large table. The entire search then runs at O(k*m*n*) where k is the row count.
• There is little(no) chance of the query optimizer using any indexes on the searched column when you use the algorithm in your WHERE clause.

Those grim facts aside and the debatable instinct that you ought to consider implementing this either using LINQ or just plain .NET, here is my adapted T-SQL version of the Damerau–Levenshtein distance.

CREATE FUNCTION DamerauLevenshteinDistance(@str1 varchar(max), @str2 varchar(max))
RETURNS int
AS
BEGIN
    DECLARE @str1_len int=(SELECT DATALENGTH(@str1));
    DECLARE @str2_len int=(SELECT DATALENGTH(@str2));
    if (@str1 IS NULL AND @str2 IS NULL)
        return 0;

    if (@str1 IS null)
        return @str2_len
    if (@str2 IS null)
        return @str1_len;

    -- matrix that stores cumulative scores as strings are traversed
    DECLARE @d TABLE (i int NULL,j int NULL,value int NULL)
    declare @i int, @j int, @cost int;
    -- fill in scores for first row and column
    SET @i = 0;
    WHILE (@i <= @str1_len)
        BEGIN
            INSERT into @d (i,j,value) VALUES(@i,0,@i)
            SET @i +=1;
         END
    SET @j = 0;
    WHILE (@j <= @str2_len)
        BEGIN
            INSERT into @d (i,j,value) VALUES(0,@j,@j)           
            SET @j +=1;
         END
    declare @newvalue int;
    declare @x int;
    declare @y int;
    declare @z int;
    declare @tcost int;
    declare @a int;
    declare @b int;
    SET @i = 1;
    WHILE (@i <= @str1_len)
    BEGIN
        SET @j = 1;
        WHILE (@j <= @str2_len)
        BEGIN
            if (SUBSTRING(@str1,@i,1) = SUBSTRING(@str2,@j,1))           
                set @cost = 0;
            else
                set @cost = 1;

            -- addition, subtraction, substitution costs
            set @x=(SELECT value + 1 from @d WHERE i=@i - 1 AND j=@j);
            set @y=(SELECT value + 1 from @d WHERE i=@i AND j=@j-1);
            set @z=(SELECT DISTINCT value from @d WHERE i=@i-1 AND j=@j-1);
            set @z += @cost;
            SET @newvalue=dbo.Minimum(dbo.Minimum(@x, @y),@z);           
            INSERT into @d (i,j,value) VALUES(@i,@j,@newvalue)           
            if (@i > 1 AND @j > 1 AND SUBSTRING(@str1,@i,1) = SUBSTRING(@str2,@j - 1,1) AND SUBSTRING(@str1,@i - 1,1) = SUBSTRING(@str2,@j,1))
            BEGIN               
                -- transposition cost
                set @a=(SELECT value from @d WHERE i=@i AND j=@j);
                set @b=(SELECT DISTINCT value from @d WHERE i=@i - 2 AND j=@j-2);
                set @b +=@cost;
                set @tcost=dbo.Minimum(@a,@b);
                Update @d SET value=@tcost WHERE i=@i AND j=@j;         
            END
            SET @j +=1;
        END
        SET @i +=1;
    END
    declare @distance int=(select value from @d WHERE i=@str1_len AND j=@str2_len);
    return @distance;
END

The DL distance function above makes use of another scalar-valued function for getting the minimum of two numbers, Minimum, shown below:

CREATE FUNCTION Minimum(@val1 float, @val2 float)
RETURNS Float
AS
BEGIN
    IF (@val1 < @val2)
        RETURN @val1;
    RETURN @val2;
END

It is up to you to decide the DL distance that meets the threshold of relevance of your search.

SQL Server allows us to decide whether query objects we create referencing tables can be tightly or loosely bound to the table structure. When you create a view or stored procedure you can specify the WITH SCHEMABINDING option to indicate that the view or stored procedure is invalidated if the underlying table structure is modified. In such a situation, the developer will need to drop and recreate the view or stored procedure. It also, inadvertently , serves as a check against 'breaking' changes like dropping columns and changing column data types without regard for dependencies.

Most of the views I see in practice do not specify this option and a reasonable percentage use the "SELECT *" construct to retrieve data from the base tables. This means that if the underlying table(s) change, the developers will be scrambling to either fix bugs or if they are in the know, running sp_refreshview to synchronize the view metadata. One of the practices I preach is the use of WITH SCHEMABINDING at all times. This will consequently force you

1. Not to use the "SELECT *" construct in your views at all. This makes total sense to me even without considering some positive performance points you might gain by specifying exactly what you need to retrieve from the database.
2. To use two part names for every column and table you reference i.e. table.column or schema.table.
3. To carefully consider making 'breaking' changes to your table structure. In any case, good and thorough designing at all tiers of your application should reduce the need for frequent structural changes at the table level. If you must change stuff, script these changes and apply them as part of a bug fix or patch.
4. To seriously re-evaluate your usage of nested views. Yes, I think that they are bad.

I have not yet seen any single advantage (apart from excuses) of not tightly binding your references to tables in your database. If the guys at Redmond were listening, I would suggest that all views be implicitly schema bound instead of allowing the T-SQL developer to shoot themselves in the foot. In fact, it sounds better to me to have a WITH NOSCHEMABINDING option just in case some developer out there wanted to do the un-recommended.

A substantial part of my development career was spent on the assembly line churning out code based on someone else specifications. There was always somebody else to handle the financial, administrative, marketing and sales aspects of  the whole manufacturing process - what I used to call the 'social' face of software. Now that I am running my own small software outfit and having gone through a few projects, the lessons seem to be coming my way fast and hard on a daily basis. Most of them are the 'social' kind and I will proceed to randomly throw them at you, mainly because I don't want to forget them.

1. Document all proposed requirements (features) to the most atomic level of detail possible. Ambiguous or general statements can fool you into optimism as far as the project timeline and budget is concerned. A specification statement like "The new system should allow employees to view their own data currently stored in the HR management system oracle database" cannot be the basis of a design. Break it down with questions like this:
   • Allow - May suggest security concerns. Will this be integrated security (implicit) or forms-based (explicit)?. Is there some form of identity management in place? Will we have to log (keep track) access to the system somewhere?
   • Employees - How many? Where are they located? Will each computer need some form of installation or setup procedure before using the system?
   • View - Where is the viewpoint? i.e. can the employee view the data while outside the LAN. What will they use to view the data? i.e a browser, a Windows forms application through a VPN? Do we need to encrypt the data? What obstacles lie between the user and the data? Firewalls,
   • Own - Are there people who can view other peoples' data? Who manages all these permissions?
   • Data - Which data exactly? i.e. Salaries, leave (vacation), loans, employment history (vertical and horizontal movement within the firm). How should each be displayed?
   • Oracle - Which version? Oracle client software installation? Who is responsible for this other database? Will they help with scripts to get the data out? Will we be allowed access to some of the data during development? Silently:(Do we have skills to deal with PL/SQL and ADO.NET?). Any documentation available?
   • If most of the above questions can be answered satisfactorily by the client, you may be able to do a better estimate than just a '1 man-week' guess. In fact, you might realize that this could very easily end up at slightly more than '1 man-month'. Remember that I am talking about one requirements specification statement.  
   • You might, of course, want to wear the 'architects' hat and start thinking of performance, availability, scalability, extensibility and interoperability at the statement level. These thoughts will not reduce the value of your requirements and may uncover other concerns and wishes not previously stated.
2. Communicate anything involving an agreed or suggested flux in resources and system features in writing (e-mail) as much as possible. Copy (cc) the communication to all the people relevant to that part of the project. After meetings, e-mail the client highlighting the main points agreed or disagreed during that meeting and ask for confirmation. You may look dumb and slow but this will cover your bases and avoid any ambiguity or disputes later.
3. Perform user acceptance testing as early as sanely possible. Unit and functional tests can be greatly complemented by the almost natural ability of a user to always crash a system. The fact that the software is incomplete only serves to gauge your feature prioritization skills. This could be a better indicator of where the project status instead of just checking off a feature list in a garage or on an Ms Project gantt chart. In any case, a client nod is worth more than any test case scenarios you can cook up. 
4. Demarcate or at least try to understand the boundary between the user and the client. These two entities are important to the closure of the project. The client will pay for the software. Users will use your software as often as necessary. Users will certify your system. The client may not pay if the user is not happy. Your will be supporting users later - despite what the contract says. When the client is the user, you may have an easier job navigating through the acceptance and invoicing phases. If they are not , get a good project liaison to grease the path between them.
5. Listen to all levels of the user spectrum. Every data entry and retrieval scenario should have a 'persona' and a real blood-and-bones user early in the project. Different people have different views of design. You may be able to merge these different views with your own or even learn something new. Not listening creates room for assumptions. Assumptions can spawn undefined elasticity in resources and features. Listening later only serves to widen the delicate gap between project initiation and final invoicing.
6. Know the accounting practices and procedures of the client. What they may consider fast payment processing may be too slow for you. Your bills and obligations may not dance to the same tune as your clients' workflow and traditions. This will also allow you to plan for cash flow.  
7. If you can, start creating user documentation (help files) early. Consider this as a validation of the requirements and testing activities. This also brings the end game nearer.
8. If there is a training clause in the contract, make sure the scope is clear. You may find yourself teaching PC skills, database administration, active directory services or even VB.NET for free or at a lower price than you would have if it was stated clearly from the beginning.
9. If you can help it, get somebody who knows their way around the establishment and has a goodwill for the project as a liaison. Canceled or missed appointments with various people, internal events or staff deployment changes in the client firm can affect your ship date. You may also need help bullying the accounts people - they usually respond better to internal pressure and nagging. A good rapport with the internal IT team can be good for repeat or referential business. It might just help to reduce your medical bills as you struggle to deploy your new software.
10. It may be that you got the job through a competitive process (a.k.a tender). Make sure you get to know why you won/lost the bid and what your competition had to offer in their technical and financial proposals. There will always be a next time and it is very likely that you will meet your competitors again. Such knowledge can only serve to improve your chances. 
11. In case you were wondering, I have little to say about software maintenance contracts. You will not need to do much if the agreement (SLA) is clear and your software approximates the elusive zero-bug state. On the other hand, it is very easy to spend all your money 'maintaining' the software as you respond to every user SOS and hunt down all kinds of bugs.

Whether my experiences are out of place is subject to the test of time and the pressure of future projects. In the meantime, they will serve as a reminder that the world is still interesting outside the confines of Visual Studio and the SQL Server query editor.

T-SQL may not be a 'true' programming language but it sure has lots of language features that make manipulating data possible. I spend quite a reasonable amount of time writing T-SQL scripts; either in production or learning or sometimes while teaching. While doing this I have often found myself cursing at the amount of typing required to accomplish otherwise simple constructs. Here is a sampling what I am rambling about:

1. Separation of variable declaration and initialization. Put simply, I would love to be able to do the following, at least for scalar  types

DECLARE @var int=0

instead of having to do it over two lines, thus

DECLARE @var int
SET @var=0

2. Creating database objects using IF ..DROP..CREATE. It is just involves too many keystrokes. Oracle PL/SQL has the CREATE OR REPLACE syntax. Why wouldn't the SQL Server team indulge us on this one? For those who are wondering what this is all about, if you want to create a table,  you must check if it exists before issuing the CREATE statement. You could go about it like this

IF OBJECT_ID('myTable') IS NOT NULL
      DROP TABLE myTable
CREATE TABLE myTable(col1 int)

Please, just give us this instead

CREATE OR REPLACE TABLE myTable(col1 int)

If I am not mistaken, somebody has already asked for this on the Microsoft Connect site. You may want to vote for that feature.

3. Is there any hope for a more compact loop construct instead of this?

DECLARE @var int
SET @var=0
WHILE @var<1000
    BEGIN
    SET @var=@var+1
    --Some code here
   END

You notice that the six uncommented lines are required to do a simple loop. This can quickly increase if you have more than one loop (a.k.a an 'inner' loop). Since I don't expect to see curly brackets in T-SQL anytime soon, what about a FOR...NEXT simple VB-like loop?

I may come off as a truly lazy DBA, but after exposure to other 'programming' languages, I feel that once in a while my productivity is impaired with this chatful state of affairs. I am not sure if there is anything in the ANSI-SQL standard that prohibits these wishes from coming true, but I am definitely sure that the SQL Server programmability team should think about it. 

Note To Self: If wishes were horses, beggars would surely ride.

The delayed SQL Server 2005 Books Online (BOL) April 2006 refresh should now be up and ready for downloading.

Get it here