Forgot your password?

Home > About Us >Modern Signal Blog

Modern Signal Blog


Viewing posts for topic: "SQL". View all posts.

Reporting with SQL PIVOT


Displaying the Results


        COUNT(*) AS Count,
        AVG(IsNull(red,255)) as Red ,
        AVG(IsNull(green,255)) as Green ,
        AVG(IsNull(blue,255)) as Blue
FROM    WCIResponse r
        INNER JOIN WCIThing t ON r.ThingID = t.ThingID







    FROM    WCIPerson
    ORDER BY AgeRange


            <cfloop query="GetAges">
    FROM    ( SELECT    t.ThingID ,
                        #preserveSingleQuotes(AgeRange)# AS AgeRange,
              FROM      WCIResponse r
                        INNER JOIN WCIThing t ON r.ThingID = t.ThingID
                        INNER JOIN WCIPerson p ON r.PersonID = p.PersonID
              WHERE     p.Age IS NOT NULL
              GROUP BY  t.ThingID ,



SQL Server Query Performance

This page houses tips and tricks for improving query performance in SQL Server.

POSTED BY: Adam Polon

After running into some very quirky results from seemingly simple queries, I have found the following suggestions to make an enormous difference in query performance. 

Of course, you should always start by making sure that your query is optimized using standard practices, such as the following:
  • Make sure fields that are searched over frequently have indexes assigned.  This only makes sense if the field will return less than 10% of the table, so don't index status fields if there are only 5 of them, etc.
  • Make sure the data type for a field is appropriate.  For instance, if your field contains numeric data, avoid using a varchar field and CASTING AS INTEGER.
In some cases, switching around the order of fields in WHERE clauses makes a difference, but I usually do not find this to solve my issues.

Here are some things to try next.


Debug sluggish queries in SQL Server Management Studio by toggling the “Include Actual Execution Plan” button.  This will allow you to see the execution plan – including any table scans, index seeks, etc, to identify slow points in the query.


Review the nightly database maintenance plan to ensure that indexes are rebuilt on a nightly basis. 


I believe that the rebuilding of indexes on a nightly basis makes the following item unnecessary, but it is listed here just in case in helps where other avenues do not. 

Defragment indexes on large tables. To do this, use the following command:

DBCC INDEXDEFRAG (database_Name, 'table_name', index_name)

Calling this command on key data tables made a huge difference in performance for a few queries that were performing slower than expected.  Queries literally went from 45 seconds to 2 seconds.

It would be a good idea to schedule these commands to run on a nightly or weekly basis.


Run Update Statistics on key data tables.  In conjunction with DBCC INDEXDEFRAG, I have seen huge performance enhancements as a result of running this.  To do this, use the following command:

update statistics table_name;



Please let me know if you wish to add any additional tips and tricks to this page.

Search and Replace in a Text Column

SQL Server has a replace function, but it doesn't work on "text" columns.  SQL Server seems to address this with a new varchar(max) datatype, but those of us still using SQL Server 2000 still need a solution.  The following stored procedure should do the trick.  The seed of the idea for this came from the following page:

My version puts the code in a stored procedure, replaces all instances of the string, escapes any wildcard characters that might be in the string, and also prints out the number of replacements that are done.  It is limited in that it won't work if the new text contains the old text, because that would cause an infinite loop, but maybe we'll be using varchar(max) before we need to do that!

- David (

EXEC usp_utility_replacetext 'Content','htmlContent','oldtext','newtext'

CREATE PROCEDURE dbo.usp_utility_ReplaceText
    @TableName VARCHAR(32),
    @ColumnName VARCHAR(32),
    @otxt VARCHAR(100),
    @ntxt VARCHAR(100)
DECLARE @invalidChars VARCHAR(20)

SET @invalidChars = '%[^a-z1-9_@#$]%'

IF PATINDEX(@invalidChars, @TableName) > 0
    PRINT 'Invalid character in table name.'

IF PATINDEX(@invalidChars, @ColumnName) > 0
    PRINT 'Invalid character in column name.'

IF CHARINDEX(@ntxt, @otxt) > 0
    PRINT 'Sorry, the new text cannot contain the old text.'

SET @sql = N'
    DECLARE @txtlen INT
    DECLARE @ptr BINARY(16)
    DECLARE @pos INT
    DECLARE @otxtPat VARCHAR(100)
    DECLARE @more bit

    SET @txtlen = LEN(@otxtIN)
    SET @otxtPat = @otxtIN
    SET @otxtPat = REPLACE(@otxtPat,''%'',''[%]'')
    SET @otxtPat = REPLACE(@otxtPat,''_'',''[_]'')
    SET @otxtPat = ''%'' + @otxtPat + ''%''
    SET @numOUT = 0

        SELECT * FROM ' + @TableName + '
        WHERE ' + @ColumnName + ' LIKE @otxtPat
        DECLARE curs CURSOR local fast_forward
        FOR SELECT
            TEXTPTR(' + @ColumnName + '),
            ' + @ColumnName + ') - 1
        FROM ' + @TableName + '
        WHERE ' + @ColumnName + ' LIKE @otxtPat
    OPEN curs
    FETCH NEXT FROM curs INTO @ptr, @pos
    WHILE @@fetch_status = 0
        @ptr @pos @txtlen @ntxtIN
        SET @numOUT = @numOUT + 1
        FETCH NEXT FROM curs INTO @ptr, @pos
    CLOSE curs

SET @parms = N'@otxtIN VARCHAR(100),@ntxtIN VARCHAR(100),@numOUT INT OUTPUT'
EXEC sp_executesql @sql, @parms,
    @otxtIN = @otxt,
    @ntxtIN = @ntxt,
    @numOUT = @num OUTPUT
PRINT CONVERT(VARCHAR(32), @num) + ' occurrences replaced.'

RSS Feed



    - Ecosystem Investment Partners


    - National Association of Student Financial Aid Administrators


    - The National Center for Safe Routes to School


    - National Association of Home Builders


    - Center for Medicare Education


    - Charm City Run


    - Kirk Gillis, Managing Director at Zoom Tanzania


    - NALP


    - Teaching Strategies, Inc.