Conditional Where Clauses

Excellect article by Erland Sommarskog about the various ways of doing SQL conditional where clauses. What sets this apart is that Erland has comparative performance figures, showing what way is best. Also kept up to date.

Change server collation

Changing SQL 2005 collation does not require a total re-install. Instead the following can be done :

1. Backup all user databases
2. Drop user databases
3. Make sure security is SQL Server
4. Rebuild the master database as follows :

cd "\Program Files\Microsoft SQL Server\90\Setup Bootstrap"

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AS

More details are here

Lock Down SQL 2005

Nice website detailing whats needed to lock down a SQL Server 2005 implementation

TFS Sharing workspace between multiple users

This requirement is for a build machine where several developers (who develop with TFS on their own PC) want to build and test on the final hardware. IT would not allow a single AD login, and TFS will not allow a workspace to be used by different users on the same PC.

However, this can be done by fooling TFS into thinking that the physical workspace is a different path per user. By using the DOS command SUBST to assign a different drive letter for each user to the same path, TFS will allow the files to be shared.

This is OK for this scenario where only get latest was every used - I'm not sure if it would be any good if files are checked out.

SQL Server Versions

A rather useful site with all the SQL Server versions and hotfixes. For a more detailed (though less readable) list look here.

Dynamic PIVOT table

SQL 2005 Pivot command is great if you have a fixed column result set. However, if the columns are data derived then it is unfortunately not possible to use a SELECT in the PIVOT IN clause. The following overcomes this shortfall :

--==============================
-- Create pivot "columns"
--==============================

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.Name
FROM ColumnNamesTable AS t2
ORDER BY '],[' + t2.Name
FOR XML PATH('')
), 1, 2, '') + ']'

--==============================
-- Create SQL for Pivot table
--==============================
SET @sqlStmt = N'SELECT MainColumn, ' + @Cols +
' FROM ResultsTable' +
' PIVOT'+
' (SUM(Value) FOR ColumnName IN (' + @cols + ')) AS PivotedResults' +
' ORDER BY MainColumn'

EXEC sp_executeSql @sqlStmt

Clustering SQL Server

Some useful sites on how to create W2K03 cluster servers with SQL 2005:

MSDN How To: Create a new SQL 2005 Failover Cluster(setup)

MSDN Before installing failover clustering

MS How to create Microsoft Distributed Transaction Coordinator on Windows 2003 cluster

MS How to enable network DTC access in Windows Server 2003

MS How to manually re-create Cluster service account