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>'

Conditional Joins

I recently came across a problem where I needed to join a table based on whether the a row in another column contained a flag.  I needed to retrive a date field from either a sale table or order table depending on status of the order.  I needed to join the tables as there was certain other data I need to take into account. 

Unfortunately SQL doesn;t support a case join statement so I went off to serch for a solution. Jeffs blog revealed a good solution involving left joining both tables then using COALESCE to get the relevant date.  I use a where statement on the status table so that I only get the data from the order table when the status is not sold and from the sale table when the item has been sold.

select
  C.CustomerName, coalesce(s.date,o.date) as ActivityDate
from
  Customers C
left outer join 
  Sales S on ...
left outer join
  Orders O on ...
where
  O.Dateis not null OR S.Date is not null

The solution worked well and, as in Jeffs blog, I could have use CASE instead of the COALESCE but I went with COALESCE for neatness.

Arrrgh, I've Deleted my Default Database

I have a local instance of SQL server 2005 on my desktop which hosts a number of databases that I have worked on over the years.  I decided I would have a bit of a clean up and deleted a number of the databases.  Unfortunately, I hadn't updated my main login and deleted the default database.  In SQL server this means that when a login has no default database the login will not be given access to the server object so I could no longer login. 

I couldn't remember my sa password so it was off to google to see what I could do.  Fortunately I found a command to open SSMS using a different DB (master). 

sqlwb -S <login name> -d master -E

Once into SSMS I could then alter the default database of the login to master.

EXEC sp_defaultdb <login name>, 'master'

This altered the login default database and I checked by running

SELECT * FROM master.dbo.syslogins

There it was, default db changed.  Whilst I was there I updated a number of logins that had deleted databases as their master and everything was hunky dory.  I also changed the sa password to something I would remember so that I didn't caught out again.

500 Mile Challenge

At the start of the year I was looking to give myself a small running challenge to motivate myself into increasing my running both in number of runs and length of run. After chatting to some friends I decided to go for 365 miles in a year which was achievable but also allowed for some time off due to holidays and any possible injuries.

I decided to mention it to the guys I regularly go running with (both better runners than myself) and they decided it was a good idea but 365 was a bit too easy. The 365 mile challenge was replaced with the 400 mile challenge. We decided to log this on Endomondo so we could track each others progress but found that someone had already posted a 500 mile challenge. This led to the 365 mile challenge becoming the 500 mile challenge.

It worked out at roughly 9 miles a week which is a mile more than what I usually do after completing two 4 mile runs at lunchtimes. It was good motivation so we upped our normal run to 5 miles so that we would be completing 10 miles a week on average. So far I am just about on track but I had to clock ~70 miles this month to make up for the short fall I had in January due to the birth of my son.

I also bought some new trainers for some extra motivation and finally ditched my 10 year old asics that had about 1000 miles on the clock. I went for the asics fuji es trail shoes as most of the running I do is on tracks in the forest. The shoes are really comfortable and have the usual asics excellent fit and are a lot lighter than my old shoes. 

For trail shoes they are great with good grip but the only gripe I have is that they should really be a lot more waterproof to be true trail shoes. 

T-SQL Replace String

On a recent project I was tasked with writing a simple string replace function for T-SQL.  On the surface it seemed a fairly straightforward task using either PatIndex or charindex.  However, some of the specific requirements proved problematic.  There could be multiple replaces of the same text throughout the string, the replacement text may be the same as the text to find and the word may contain numbers and underscores. 

Using patindex in a loop seemed the best way forward to deal with the multiple replacements and the numbers and underscore characters.  The other problem was that I had to step through the original text to do the replacements so that if a text was replaced with the same text then the function wouldn't get stuck.  I achieved this by using sub string to get the remaining text after each replacement.

@OrigString varchar(8000), 
@LookFor varchar(1000), 
@ReplaceWith varchar(1000))

returns varchar(8000)
BEGIN
DECLARE @findIndex int
DECLARE @lengthLookFor int
DECLARE @lengthReplace int
DECLARE @totalLength int
DECLARE @tempString as varchar(8000)
DECLARE @counter int

SET @OrigString = '(' + @OrigString + ')'

SET @lengthLookFor = LEN(@LookFor)
SET @lengthReplace = LEN(@ReplaceWith)
SET @totalLength = LEN(@OrigString)
SET @tempString = @OrigString
SET @counter = 0

DECLARE @stuffindex int
SET @stuffindex = 0

DECLARE @substring int
SET @substring = 0

WHILE PATINDEX('%' + @LookFor +'[^a-z0-9]%', @tempString) != 0
BEGIN
 SET @findIndex = PATINDEX('%[^a-z0-9]' + @LookFor +'[^a-z0-9]%', @tempString) + 1
--SELECT @findIndex as 'findIndex'

 set @stuffindex =  @findIndex + @substring 
 if(@counter >0)
  set @stuffindex = @stuffindex - 1

 --SELECT @stuffindex as stuffindex

 SET @OrigString = STUFF(@OrigString, @stuffindex , @lengthLookFor, @ReplaceWith)
--SELECT @OrigString as 'outputstring'

SET @substring = @stuffindex + @lengthReplace
--SELECT @substring as substring

SET @tempString = SUBSTRING(@OrigString,@substring,@totalLength - @findIndex)
--SELECT @tempString as 'tempstring'

SET @counter = @counter + 1


END

SET @OrigString = SUBSTRING( @OrigString, 2, LEN(@OrigString)- 2)

Creating ASP.NET tables in SQL server

To create all of the user and membership tables in SQL Server 2010 there is a command line tool within the tools directory (use the link to the command prompt) that can be used.  The command to get the tool to create the tables is:

ASPNET_RegSQL.exe -S (Server) -d (Database) -A m -E

-A m creates the membership tables and -E uses a trusted connection.

The command creates the Applications, Membership, SchemaVersions and Users tables for ASP.NET.