I needed a list of all the database files on a SQL Server instance. Here’s how to get this easily. First create a temp table to hold the data like so.. ?View Code TSQLSELECT * INTO #db_files FROM sys.database_files; Truncate it so we don’t duplicate nay data. ?View Code TSQLTRUNCATE TABLE #db_files; Next we use [...]

Using functions improperly in your where clauses always prevents index usage right? I’ve been reviewing some queries generated by linq and I’ve found out this isn’t always the case. A quick demo… Create a temp tables and insert some data ?View Code TSQLCREATE TABLE #test ( id INTEGER NOT NULL PRIMARY KEY CLUSTERED, [name] VARCHAR(100) NOT NULL ); [...]

I realized I wasn’t! We run a couple of systems that I know stick a mass of records through on a daily basis. Better start doing this then or I might end up doing a whoopsie! Here’s a script I’ve quickly knocked up to make checking this simple. This script will check the specified tables TINYINT, SMALLINT, INT & BIGINT values [...]

I’m always the one to say RTFM but this one stumped me for a while. I had problems using a Table-Valued Parameter in a Stored Procedure today. The explanation is indeed in the documentation but it’s perhaps not as clear as it should be. Hopefully this post will be picked up better in Google for [...]

Several months ago at work we started having some terrible problems with some jobs that purge old data from our system. These jobs were put into place before my time, and while fine at the time, were now causing us some big problems. Purging data would take hours and cause horrendous blocking while they were [...]