How to de-dupe a record set using a temporary table (MSSQL)

Following on from the SQL Fire we witnessed a while ago I've just wrote this rather snazzy little query that does something similar.

The Problem:
The original problem is we have a little game with a scoreboard and one very dedicated player. Well, not only one player, many players but one is more determined to win than the rest. So to keep the small scoreboard looking inviting we decided to only show each player once, and to only show that players highest score.

I really wanted to do this in SQL as I figured it’s sorting and ordering abilities would be far easier than writing something in C#.

The Solutions:
Select the data into a temporary table which has a unique index on the user ID. The order of the select will ensure that only the highest score for each user will make it into the temporary table. Then select everything out of said temptable.

The Practical:

-- CREATE THE TEMPORARY TABLE
CREATE TABLE #tmpTable (
    strFirstName NVARCHAR(255),
    strLastName NVARCHAR(255),
    intScore INT,
    intUserID_PK INT
)

-- ADD THE UNIQUE INDEX
CREATE UNIQUE INDEX ix0 ON #tmpTable (intUserID_PK) WITH (ignore_dup_key = ON)

-- INSERT THE DATA
INSERT INTO #tmpTable SELECT strFirstName,strLastName,strValue, intUserID_FK
    FROM tblScores

-- THE ORDER IS THE WRONG WAY TO WHAT YOU THINK
-- IT SHOULD BE, ASC NOT DESC...
-- TRY IT THE OTHER WAY AND YOU SHOULD GET THE LOWEST SCORES
    ORDER BY intScore ASC
-- SELECT THE DATA
SELECT * FROM #tmpTable
-- WITH THE CORRECT ORDER BY
ORDER BY intScore DESC
GO -- CALL GO SO THIS BATCH GETS EXECUTED AND THE TABLE IS NOLONGER USED
DROP TABLE #tmpTable
GO -- DROP THE TABLE SO IT IS NOLONGER IN MEMORY

Update: If you use this within a stored procedure you will need to remove the GO statements or the SP won't compile

Saturday Morning: Website Security Time...

Recently a couple of our sites have been subject to SQL Injection Attacks but non have suffered so hard as the one I'm currently securing.

I spent all day yesterday on it and I've been up since 9AM when the head IT guy from our client called (and woke) me.

So after I get this thing done, and then enjoy what is left of my weekend I plan on writing up my learnings and educating my co-workers. Keep an eye out for a blog post or two.

Shrinking Log files in SQL Server 2005

So you, like I, have a database in SQL Server 2005 who's log file (.ldf) has grown massive.

Here is a quick but of SQL that should help you shrink the file to reclaim some disk space. be warned, this might impeed your ability to restore the database later if it all goes wibble.

First run the following

USE nameOfTheDatabaseHere
SELECT * FROM sysfiles 

Now, take note of the fileid of the log file (usualy 2 for most databases) then do

DBCC SHRINKFILE
    ( fileIDGoesHere, TRUNCATEONLY )

This will truncate the log file to as small as the DB Server will allow.

See http://msdn2.microsoft.com/en-us/library/aa258824.aspx for more info

SQL Noise

Someone's going to be annoyed when they end up here looking for how to add "noise" to an SQL query.. oh well, sorry...

However, it is an appropriate title because after adapting Matt's SQL on FIRE, I truly feel I'm bringing the noise (to SQL [in my own little way])!

Today really is SQL Day 2008 (another dead end search result there! :)

SQL : Multiple Order By's

Just trying to sort a query with an ORDER BY clause where I needed to order by two columns in different directions so I tried it and it works! Didn't know you could do that...

SELECT * FROM tblTableName
WHERE column1='x'
AND column2='y'
ORDER BY column3 ASC, column4 DESC

Nice!

(tested in MySQL and MS SQL)