SQL Server and CTE

I recently posted a function to perform string replacement for T-SQL which we were using with a cursor to run through a some look up table and replace any occurences of the lookup text in the destination with the corresponding replacement text. After initial testing we were finding that the cursor was really killing performance so we needed a way to replace the cursor.  All roads led to CTEs (Common Table Expression)  but apart from a small function to create a list of people from a tree structure (recursive CTE), that a colleague had written, I had not seen many CTEs in action.

I had a few examples of recursive CTEs from around the web to build from and so I could easily get a CTE to recusively look through a table of lookups and replace a destination string with the corresponding string. The added complication was that there was actually several lookup tables combined into one lookup table and  first I had to link the look up table to an id in the destination table.  An example of the two tables are shown below:

ItemID	Lookup	Replacement
1	MX	MN
1	MY	CY
1	SB	DG
2	HG	TY
2	HD	OK

Table 1: The lookup table

ItemID	String	Result
1	MX+MY+SB
2	HD/HG

  Table 2: The string table with column for resultant string with replacements
The desired result is to have MN+CY+DG in row 1 column 3 and OK/TY in row 2 column 3.  The first idea was to just let the lookup navigate all the way through table 2 but this may have replaced strings in the result that were not linked to the item (i.e. If row2 of table 2 contained SB) and would be really inefficent as in the real system these tables could potentially contain 1000s of rows.

I Ain't Afraid of No Ghost

I am often "copying" databases from different dev boxes and live boxes by backing up and restoring. However this causes problems with user accounts.

"Ghost" users are created when a database is backed up and restored to a different machine which uses local SQL user accounts. Even if the local SQL user account is recreated, because the underlying ID doesn't match, when the user tries to access the database the message will say the User does not exist. However when you try and add the user you will be told the user already existings. This causes access issues which at first glance seem illogical.

To fix this run the following query against the database:

use <database name>

go 

sp_change_users_login 'auto_fix', '<local login name>'