Robin Hames

Hints, tricks and tips relating to MS SQL Server and .NET
posts - 14 , comments - 43 , trackbacks - 0

October 2008 Entries

SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values.
SQL Server CHECKSUM() with Single Quotes (‘) gives duplicate values. A database I have been working on uses a checksum across address data to try to establish whether an entered address already exists in the database. A problem occurred because two addresses had the same checksum value, but the actual address was subtlety different. The difference was that one had the street address as “St James’ Street”, whereas the other had “St James Street”. The following test script shows that the single quote ......

Posted On Thursday, October 30, 2008 10:58 AM | Comments (4) | Filed Under [ SQL Server ]

Why you should always specify the SqlDbType for an ADO.NET SqlParameter object.
How not specifying the ADO.NET SqlParameter DbType can lead to a horrible query execution plan! I was looking into a query that was performing much worse than expected. The query was a simple select of the form: select e.EmployeeID, count(r.EmployeeRoleID) from Employee e left join EmployeeRole r on e.EmployeeID = r.EmployeeID where e.EmployeeRef = @EmployeeRef group by e.EmployeeID I expected the query plan for this to be fairly straightforward, a couple of Index Seeks and a Left Outer Join, but ......

Posted On Wednesday, October 29, 2008 3:50 PM | Comments (0) | Filed Under [ SQL Server ]

Creating SQL Wildcards with Recursion and a Common Table Expression in SQL Server 2005
Creating Test Wildcards with a Common Table Expression in SQL Server 2005 I needed to create a SQL Server table of test wildcard strings ranging from ‘AA%’ through to ‘ZZ%’. This seemed to be a prime candidate for using recursion with a Common Table Expression (CTE). After a little playing around I came up with the following: with wildcards as (select char(65) + char(65) + '%' as wildcard, 65 as num1, 65 as num2 union all select char( case when num2 = 90 then num1 + 1 else num1 end) + char( case ......

Posted On Tuesday, October 28, 2008 3:21 PM | Comments (1) | Filed Under [ SQL Server ]

Calculating Running Totals in SQL Server 2005, The optimal solution?
Using “Update to a local variable” to calculate running totals in SQL. Recently I was looking at an existing view on a client's SQL server 2005 database. This view calculated the running total for a transaction amount from a table, but was performing very poorly. I had always believed there were three different methods for calculating a running total using TSQL: 1. Use a nested sub-query 2. Use a self join 3. Use Cursors My own personal preference was to use the cursors option. If the cursor guidelines ......

Posted On Tuesday, October 28, 2008 10:22 AM | Comments (18) | Filed Under [ SQL Server ]

Powered by: