Finally we get a T-SQL debugger back into SQL Server Management tools. Evidently it was announced at the Launch Event in LA that Microsoft has decided to put the T-SQL Debugger into SQL Server Management Studio.  This is something that was in Query Analyzer for SQL 2000 but we haven’t had it for 2005 up until now.  See here for more information.  However here’s the pertinent excerpt:

<excerpt>

So what are the surprise features?

#1: we’ve rewritten the activity monitor to be far more useful and performant (funny word) than ever before. This was work Ken Henderson started before he passed away. One of Ken’s good friends and a dev on the team, Bart Duncan, took over the work to complete it in Ken’s honor. I showed this at launch.

#2: object search: yes, we’re bringing back object search. In Object Explorer Details we’ve added a search bar that will search for database objects. It’s a simple yet powerful search. Unfortunately I wasn’t able to demo it at launch.

Drumroll please…

#3: the debugger: we’ve brought back the T-SQL debugger in Management Studio. This is huge and incredibly valuable. And yes, I demoed it at the launch event.

BTW: #3 had the highest number of votes in Connect and #2 was close behind. We did these because customers told us they are important feature to them. While Connect does have its flaws, it also has its good things. Keep the feedback coming!

</excerpt>

One of the things I’ve been doing lately for an upgrade from SQL 2000 to SQL 2005 is restructuring databases, adding filegroups and files to split out the data and non clustered indexes, leaving only the system tables in the primary filegroup. 

This is actually fairly simple as we’re just dropping the clustered indexes and recreating them in the new filegroup which moves the data.  However we have some tables that don’t have clustered indexes (i.e., HEAP tables) so I wrote a script that moves the HEAP tables into another filegroup.  First it checks for an identity column and if one exists, I create a clustered index on the identity column for the new filegroup, then remove the clustered index.  If an identity column doesn’t exist, I create one with a clustered index on the new filegroup then drop the index and the column.  Fairly simple.

On another note if anyone knows an easy way on how to move text, ntext data between to a different filegroup, let me know.

-- Aaron Lowe
-- 8/24/2007

SET NOCOUNT ON
Print 'Starting'
DECLARE @SchemaName nvarchar(128),
        @TableName nvarchar(128),
        @SQLcmd nvarchar (1024),
        @ColumnName nvarchar(128),
        @FullQualTable nvarchar(261),
        @FileGroup nvarchar(128)

SELECT @FileGroup = DB_NAME() + '_DATA'

DECLARE HeapTables_CRSR Cursor FOR
    select ss.name, so.name
    from
        sys.indexes si
        join sys.objects so on si.object_id = so.object_id
        join sys.schemas ss on so.schema_id = ss.schema_id
        join sys.database_files sdf on sdf.data_space_id = si.data_space_id
        join sys.filegroups sfg on sfg.data_space_id = sdf.data_space_id
    where
        si.type = 0
        and so.type = 'U'
        and sfg.name = 'PRIMARY'
    order by
        so.name

OPEN HeapTables_CRSR

FETCH NEXT FROM HeapTables_CRSR INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @FullQualTable = '[' + @SchemaName + '].[' + @TableName + ']'
        Print 'Currently working on: ' + @FullQualTable
        IF EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID(@FullQualTable)
             and is_identity = 1)
            BEGIN
                SELECT @ColumnName = name from sys.columns WHERE OBJECT_ID =
                    OBJECT_ID(@FullQualTable) and is_identity = 1
                SET @SQLCmd = N'CREATE CLUSTERED INDEX [IX_TempIDent] ON
                [' + @SchemaName + '].[' + @TableName + '] ([' +
                @ColumnName + '] ASC) ON [' + @FileGroup + '];'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'DROP INDEX [IX_TempIDent] ON [' + @SchemaName + '].[' +
                @TableName + '];'
                exec sp_executesql @SQLCmd
            END
        ELSE
            BEGIN
                SET @SQLCmd = N'ALTER TABLE [' + @SchemaName + '].[' + @TableName
                + '] ADD [TempIdent] int IDENTITY (1,1);'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'CREATE CLUSTERED INDEX [IX_TempIDent] ON [' +
                @SchemaName + '].[' + @TableName +
                '] ([TempIdent] ASC) ON [' + @FileGroup + '];'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'DROP INDEX [IX_TempIDent] ON [' + @SchemaName + '].[' +
                @TableName + '];'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'ALTER TABLE [' + @SchemaName + '].[' + @TableName +
                '] DROP COLUMN [TempIdent];'
                exec sp_executesql @SQLCmd
            END
        FETCH NEXT FROM HeapTables_CRSR INTO @SchemaName, @TableName
    END
CLOSE HeapTables_CRSR
DEALLOCATE HeapTables_CRSR
Print 'Done'