Category Archives: SQL 2008

Executing the same SQL statements against multiple servers and databases using SQL Server Management Studio, and Merging the Results

Validated against: SQL Server: 2008 R2. If you need to apply the same SQL statements against multiple servers and databases, this can be accomplished using SQL Server Management Studio (version 2008 onwards). Simply with the Registered Servers window, select the … Continue reading

Posted in SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Executing the same SQL statements against multiple servers and databases using SQL Server Management Studio, and Merging the Results

Blocked SQL Sessions and Blockers

Validated against: SQL Server 2005, 2008, 2008 R2. Below is a SQL script that returns SQL sessions waiting (blocked) for one second or more, and also the associated SQL sessions that are doing the actual blocking. The script returns pretty … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Blocked SQL Sessions and Blockers

Very Quick Accurate Row Counts

Validated against: SQL Server 2005, 2008, 2008 R2.

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Very Quick Accurate Row Counts

Current Database Update All Table Statistics with Fullscan

Validated against: SQL Server 2005. Updating statistics against each table in the current database by using the undocumented stored procedure [sp_MSforeachtable].

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Current Database Update All Table Statistics with Fullscan

Efficient Numbers Table

Validated against: SQL Server 2005. Just performed a quick Google of the various methods of acquiring a list of sequential numbers and recommend the approach by Itzik Ben-Gan. The approach uses a small footprint of code and allows numbers up … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Efficient Numbers Table

Disk Subsystem Benchmarking

When benchmarking disk subsystems, I generally benchmark against the most challenging task; which is random writes, with no buffering! The tool I use is the Microsoft SQLIO tool that measures IO performance. The SQLIO tool can be acquired from: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163 … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Disk Subsystem Benchmarking

Using INSERT to Remove Duplicate Rows (IGNORE_DUP_KEY)

Create a duplicate structure of the table concerned and build an IGNORE_DUP_KEY index over the second table that includes all the original table candidate keys and insert rows into it. forename surname age ————— ————— ———– David Selwood 40 Paul … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Using INSERT to Remove Duplicate Rows (IGNORE_DUP_KEY)

Rounding a Date (removing time)

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Rounding a Date (removing time)

Cast or Convert

Syntax for CAST: CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) CONVERT is specific to SQL Server, and allows for a … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Cast or Convert

Forcing the Screen Buffer to be Displayed

In order to force the contents of the screen buffer to be outputted staight away please use the following SQL:

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Forcing the Screen Buffer to be Displayed