Microsoft has a very informative post on the pros and cons of 64 bit vs. 32 bit performance on SQL Server 2005. This post confirms and reinforces many of the results that I have seen running a VLDB on 64 … Continue reading →

As explained in this Microsoft KB article, there are two versions of the SP2 download for SQL Server 2005. Here is the "good" version of the SQL Server 2005 SP2 download. Notice the 9.0.3042.1 file version. The newer version was … Continue reading →

Microsoft’s Paul Randal has a good post with a list of common sense practices for maintaining very large databases. I have personally seen the problems that heavy index maintenance can cause when database mirroring is turned on (especially if you … Continue reading →

Database mirroring is a new high availability feature that was added to SQL Server 2005 (and enabled by default in SP1). It works at the database level rather than the instance level (like fail-over clustering). It requires SQL Server 2005 … Continue reading →

I had a question from someone about how to convert from UTCTime to local time in T-SQL code. Here is one way to do it: — Convert a UTC Time to a Local TimeDECLARE @UTCDate datetimeDECLARE @LocalDate datetimeDECLARE @TimeDiff int … Continue reading →

The most common way that I see people get a count of rows in a table in SQL Server 2005 is SELECT COUNT(*) FROM dbo.TableName WITH (NOLOCK). This works, but is expensive from a logical IO perspective. One alternative is … Continue reading →

One new scalability feature added in SQL Server 2005 is Query Notifications. This feature relies upon Service Broker, not on Notification Services. The idea behind it is to let SQL Server automatically notify applications when data changes, rather than relying … Continue reading →