Find Gaps in a Range

Common Table Expressions (CTE’s) are a new structure in SQL Server 2005 that can be used to assist with performing recursive operations. The following example demonstrates how a CTE can be used to find the gaps in a range.

Consider the following table:

CREATE TABLE nums

(

col1 INT NOT NULL

)

INSERT nums SELECT 1

INSERT nums SELECT 2

INSERT nums SELECT 3

INSERT nums SELECT 5

INSERT nums SELECT 6

INSERT nums SELECT 9

As can be seen 4, 7 and 8 are missing from the range sequence.

The following query can be used to identify the first gap in the range ie. 4 and 7:

SELECT    col1 + 1

FROM    nums T1

WHERE    NOT EXISTS

(SELECT    1

FROM    nums T2

WHERE    T2.col1 = T1.col1 + 1)

AND    col1 < (SELECT MAX(col1) FROM nums)

However to determine all the missing numbers in the range is significantly more complex. However this process can be simplified using a CTE as illustrated below:

DECLARE @i INT

SELECT @i = MAX(col1) FROM nums;

WITH tmp (GapCol1) AS

(

SELECT DISTINCT a.col1 + 1

FROM nums a

WHERE NOT EXISTS

(SELECT 1 FROM nums b

WHERE b.col1 = a.col1 + 1)

AND a.col1 < @i

UNION ALL

SELECT a.GapCol1 + 1

FROM tmp a

WHERE NOT EXISTS

(SELECT 1

FROM nums b

WHERE b.col1 = a.GapCol1 + 1)

AND a.GapCol1 < @i

)

SELECT GapCol1

FROM tmp

ORDER BY GapCol1;

April WARDY IT Community Dinner

The WARDY IT Community Dinner is a great opportunity to network with other IT professionals in order to share knowledge, information and experiences. The April WARDY IT Community Dinner is being held on Monday the 16th of April. To RSVP to be at this event and find out what other people are doing and how they are doing it send an email to peter @ wardyit.com.

Note: The WARDY IT Community Dinner is typically held on the second Monday of the Month, however due to Easter the April Community Dinner is one week later.

WHERE Campari | The Fox | Cnr Melbourne & Hope Street | South Brisbane | http://www.thefox.com.au
WHEN Monday 16th April | 6:30PM
RSVP Friday 13th April
Please note that you are responsible for the cost of your own dining.

Add this event to your Outlook Calendar

Critical Update for SQL Server 2005 SP 2

If you have already installed SQL Server 2005 Service Pack 2 it is recommended that you apply the Critical Update referenced in KB933508 http://support.microsoft.com/kb/933508.

There is an issue in SP2 that causes maintenance plan cleanup tasks to remove data before the specified cleanup interval.  The Critical Update to resolve this issue can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en.

GRANT EXECUTE to all Stored Procs

Often when a new login has been created and the user mapped to a login the next step is to grant EXECUTE to all Stored Procedures in a database.  The following script illustrates how to generate a script to that will grant EXECUTE on all Stored Procedures in a database:

DECLARE @user SYSNAME
SET @user = ‘guest’

SELECT ‘GRANT EXEC ON [‘ + ROUTINE_SCHEMA + ‘].[‘ + ROUTINE_NAME + ‘] TO [‘ + @user + ‘]’
FROM  INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_TYPE = ‘PROCEDURE’

SQL Server Virtualization Licensing

More and more organisations are looking at Virtualization as a solution to managing the growth in the number of SQL Server instances that today’s IS departments need to support. However there is still a lot of confusion surrounding the licensing for virtualized instances of SQL Server.  The following article describes the licensing requirements for Virtualization and Multi-Instancing http://www.microsoft.com/sql/howtobuy/virtualization.mspx.

One thing to note that makes SQL server Enterprise Edition a great option for Virtualization is that if all processors in a machine have been licensed, then unlimited instances of SQL server 2005 can be run on unlimited virtual operating environments on that same machine.

Talking about SQL Server Virtualization at the March QLD SQL Server User Group on Thursday the 22nd of March Peter Fitzsimon from the Microsoft Global Solutions Technical Team will be presenting a session on running SQL Server on Virtual Server.