Understanding SQL Server Collations

SQL Server Club have published an article I wrote on Understanding SQL Server Collations

Bookmark : Deriving a list of tables in dependency order

An excellent post from last year on Jamie Thomson's blog , entitled Deriving a list of tables in dependency order.

A must try script which is an excellent start for automating deployment scripts.

An Indexed View story....

An Indexed View story.

Playing with INDEXED VIEWS on Sql 2008 Enterprise today, these are some facts I found. Fussy creatures, these Indexed views...

Attempt 1)

create index ix_testindex on [Report].[myView] (id)

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'myView' because the view is not schema bound.

(I went back to the view definition and added WITH SCHEMABINDING )

Attempt 2)

create index ix_testindex on [Report].[myView] (id)

Msg 10140, Level 16, State 1, Line 1

Cannot create index on view 'dbtest.Report.myView' because the view contains a table hint. Consider removing the hint.

OK, you caught me out, I had a dirty (NOLOCK) hint in there. I recreated the view without this, and ...

Attempt 3)

create index ix_testindex on [Report].[myView] (id)

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view 'Report.myView'. It does not have a unique clustered index.

Fair enough, I added 'UNIQUE CLUSTERED' to index creation script...

Attempt 4)

create unique clustered index ix_testindex on  [Report].[myView] (id) with (data_compression=page,maxdop=1,online=on) ON [indexes]

Msg 1967, Level 16, State 1, Line 1

Cannot create a new clustered index on a view online.

Argh! I cannot create the index online (an Enterprise feature if you didn't know).

Attempt 5)

create unique clustered index ix_testindex on [Report].[myView] (id) with (data_compression=page,maxdop=1,online=on) ON [indexes]

Phew! , this worked.

Note : I'm using named filegroups for indexes.

Remote Desktops SnapIn in Windows 7

Want the Remote Desktops SnapIn in Windows 7 ?

1) Download and Install the Remote Server Administration Tools for Windows 7

2) Control Panel, and then click Programs.

3) Programs and Features area> 'Turn Windows features on or off'

4) Windows Features  expand Remote Server Administration Tools, tick 'Remote Desktop Services Tools' >

5) Following a reboot, you'll find it under the start menu :) >

6) Right Click 'Remote Desktops' and select 'Add New Connection' to add each server.

SQL Server & Network Packet Size

I've seen a number of references to packet size on my research into optimising SQL & SSIS.
The default packet size is 512 Bytes and the maximum SQL server supports is 32767.

So, the question is, " Why can't i just change it ? "

Hardware support is the answer
Connecting clients and the network switches need to support the packet size (e.g. 9000 for Jumbo frames)
Network Cards need to support the speed (and for that support to be enabled via the drivers in Windows)

Your OS also needs to support it. In the case of Hyper-V (pre Windows 2008 R2), the virtual NIC cannot support Jumbo frames.

If your environment is suitable however, here's the configuration code to do it >

EXEC SP_CONFIGURE 'show advanced option', '1'; 
RECONFIGURE;
EXEC SP_CONFIGURE;

EXEC SP_CONFIGURE 'network packet size (B)', '9000';
RECONFIGURE WITH OVERRIDE;
RECONFIGURE;

EXEC SP_CONFIGURE

Link : Gigabit Ethernet Jumbo Frames

Automatically Script Non Clustered Indexes

As part of a larger backup project I wanted to automatically script my non clustered indexes to a file and have set about writing a procedure to do this. Here are the steps to follow if you want to achieve the same.

1) Enable OLE automation

2) Create utils.usp_OLEwritefile (sp that Writes to a text file from TSQL)

3) Create utils.usp_GenerateIndexesScript as follows -

/* 
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

 1) Changed Schema of routine to Utils
 2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
 3) Added Schemas to script
 4) Reformatted for clarity
 5) Compression Option added with compression type
 6) Existing Index detection script changed to use sys.indexes and sys.objects
 7) Fix for Included Column Ordering
 
--  Usage: 

EXEC utils.usp_GenerateIndexesScript 
  @IncludeFileGroup   = 1
    ,@IncludeDrop    = 1
    ,@IncludeFillFactor   = 1
    ,@destinationparameter  = '\\SERVER-01\sqlbackup$\Indexes\myindexes.sql'
    ,@IncludeCompression  = 1    
    ,@compressionType   = 'PAGE'
*/ 
CREATE PROCEDURE [utils].[usp_GenerateIndexesScript]
(
     @IncludeFileGroup  bit = 1
    ,@IncludeDrop   bit = 1
    ,@IncludeFillFactor  bit = 1
    ,@destinationparameter  NVARCHAR(1000) = NULL
    ,@IncludeCompression bit = 1
    ,@compressionType  VARCHAR(4) = 'PAGE'
 
)
AS

BEGIN
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor CURSOR
        FOR SELECT 
     SC.Name   AS SchemaName
     , SO.Name  AS TableName
                    , SI.Object_Id  AS TableId
     , SI.[Name]     AS IndexName
     , SI.Index_ID   AS IndexId
     , FG.[Name]     AS FileGroupName
     , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
              INNER JOIN sys.objects SO
     ON SI.object_id = SO.object_id
     INNER JOIN sys.schemas SC
     ON SC.schema_id = SO.schema_id
             WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
               AND SI.[Name] IS NOT NULL
               AND SI.is_primary_key = 0
               AND SI.is_unique_constraint = 0
               AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0
               AND FG.[Name]  IS NOT NULL
             ORDER BY SC.Name , SO.Name ,SI.[Name]    

    DECLARE @SchemaName  sysname
    DECLARE @TableName  sysname
    DECLARE @TableId  int
    DECLARE @IndexName  sysname
    DECLARE @FileGroupName sysname
    DECLARE @IndexId  int
    DECLARE @FillFactor  int

    DECLARE @NewLine nvarchar(4000)     
    SET @NewLine = CHAR(13) + CHAR(10)
    
    DECLARE @Tab nvarchar(4000)     
    SET @Tab = Space(4)

 DECLARE @SQLOutput nvarchar(max) 
 SET @SQLOutput = ''
 
    -- Loop through all indexes
    OPEN Indexes_cursor

    FETCH NEXT
     FROM Indexes_cursor
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

    WHILE (@@Fetch_Status = 0)
        BEGIN

            DECLARE @sIndexDesc nvarchar(4000)
            DECLARE @sCreateSql nvarchar(4000)
            DECLARE @sDropSql  nvarchar(4000)

            SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @SchemaName + '.' + @TableName
            SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                          + '            FROM sys.indexes si' + @NewLine
                          + '            INNER JOIN sys.objects so' + @NewLine
                          + '                   ON so.object_id = si.object_id' + @NewLine
                          + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                          + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                          + 'BEGIN' + @NewLine
                          + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                          + 'END' + @NewLine

            SET @sCreateSql = 'CREATE '

            -- Check if the index is unique
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'UNIQUE '
                END
            --END IF
            -- Check if the index is clustered
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                END
            --END IF

            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

            -- Get all columns of the index
            DECLARE IndexColumns_cursor CURSOR
                FOR SELECT SC.[Name],
                           IC.[is_included_column],
                           IC.is_descending_key
                      FROM sys.index_columns IC
                     INNER JOIN sys.columns SC
                             ON IC.Object_Id = SC.Object_Id
                            AND IC.Column_ID = SC.Column_ID
                     WHERE IC.Object_Id = @TableId
                       AND Index_ID = @IndexId
                     ORDER BY IC.[is_included_column],
         IC.key_ordinal

            DECLARE @IxColumn   sysname
            DECLARE @IxIncl     bit
            DECLARE @Desc     bit
            DECLARE @IxIsIncl     bit     SET @IxIsIncl = 0
            DECLARE @IxFirstColumn  bit     SET @IxFirstColumn = 1

            -- Loop through all columns of the index and append them to the CREATE statement
            OPEN IndexColumns_cursor
            FETCH NEXT
             FROM IndexColumns_cursor
             INTO @IxColumn, @IxIncl, @Desc

            WHILE (@@Fetch_Status = 0)
                BEGIN
                    IF (@IxFirstColumn = 1)
                        BEGIN
                            SET @IxFirstColumn = 0
                        END
                    ELSE
                        BEGIN
                            --check to see if it's an included column
                            IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                BEGIN
                                    SET @IxIsIncl = 1
                                    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                END
                            --END IF
                        END
                    --END IF

                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                    -- check if ASC or DESC
                    IF @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' 
                  END

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql  + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) 
                END
     
                IF @IncludeCompression = 1
                BEGIN
        IF @IncludeFillFactor = 1
       BEGIN
        SET @sCreateSql = @sCreateSql + ','
       END
                    SET @sCreateSql = @sCreateSql  + 'DATA_COMPRESSION = ' + @compressionType
                END
                
                 IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql  + ')' + @NewLine
                END
                            
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'


  SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine
        SET @SQLOutput = @SQLOutput +  @sIndexDesc + @NewLine
        SET @SQLOutput = @SQLOutput +  '-- **********************************************************************' + @NewLine
  SET @SQLOutput = @SQLOutput + @NewLine


            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                    
                   SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine
                   SET @SQLOutput = @SQLOutput + 'GO' + @NewLine 
                    
                END
            --END IF

            PRINT @sCreateSql
            PRINT 'GO' + @NewLine  + @NewLine
           
            SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine
            SET @SQLOutput = @SQLOutput + 'GO' + @NewLine  


            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END

    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor
    
    -- @SQLOutput contains the output to place in a file
    

IF LEN(@destinationparameter) > 0
 BEGIN

 EXEC utils.usp_OLEwritefile  @FileName = @destinationparameter
        , @TextData = @SQLOutput
        , @FileAction = 'CREATENEW'
 END    
    
    --PRINT @SQLOutput
END

Run the procedure to script the indexes as follows -
The compression and destination file parameters are optional!

EXEC utils.usp_GenerateIndexesScript 
     @IncludeFileGroup   = 1
    ,@IncludeDrop   = 1
    ,@IncludeFillFactor   = 1
    ,@destinationparameter  = 'c:\MyNonClusteredIndexes.sql'
    ,@IncludeCompression  = 1    
    ,@compressionType   = 'PAGE'