Robin Hames

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

SQL Server

Hints and tips relating to Microsoft SQL Server databases (mostly SQL server 2005)
Trim Leading Zeros from a string in SQL Server
This user defined function is based on a function posted at this very useful site: http://www.sql-server-helpe... I have modified it slightly to handle spaces within the input string; by first replacing any existing spaces with ‘¬’ (this seems to be a suitably obscure character that is never likely to occur in the input string). CREATE FUNCTION RemoveLeadingZerosFromVarChar ( -- Add the parameters for the function here @inputStr varchar(max) ) RETURNS varchar(max) ......

Posted On Friday, August 21, 2009 9:16 AM | Comments (4) | Filed Under [ SQL Server ]

Getting the Time part only from a SQL Server DateTime
Just some code to get the time part only from a DateTime field (is SQL Server 2000 or 2005), with the date set to a reference date declare @timetest datetime declare @refdate datetime set @timetest = getdate() set @refdate = '30 Dec 1899 00:00:00' select @timetest , @refdate , dateadd(day, datediff(day, @refdate , @timetest), @refdate ) -- date part only , dateadd(day, datediff(day, @refdate , @timetest) * -1, @timetest) -- time part only, with reference date Instead of @refdate, you could use 0 ......

Posted On Tuesday, August 11, 2009 12:24 PM | Comments (0) | Filed Under [ SQL Server ]

SQL server script to generate CREATE INDEX commands
This script generates the “CREATE INDEX” scripts for a SQL Server database: set nocount on declare @index table ( object_id int, objectName sysname, index_id int, indexName sysname, fill_factor tinyint, allow_row_locks bit, allow_page_locks bit, is_padded bit, indexText varchar(max), indexTextEnd varchar(max) ) declare @indexColumn table ( object_id int, index_id int, column_id int, index_column_id int, max_index_column_id int, is_descending_key bit, is_included_column bit, columnName varchar(255), ......

Posted On Friday, November 21, 2008 12:30 PM | Comments (0) | Filed Under [ SQL Server ]

SQL Server datetime - getting the date part only
I am always forgetting the format for using DATEADD and DATEPART functions to get just part of a SQL Server datetime (e.g. just the date), so I am posting it here so I’ll always know where to find it. declare @date datetime set @date = getdate() select @date, dateadd(day, datediff(day, 0, @date), 0) ......

Posted On Wednesday, November 19, 2008 3:17 PM | Comments (1) | Filed Under [ SQL Server ]

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 (2) | 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: