How to preview large string

Often you need to debug dynamic SQL statement. Simplest way to do this is to print statement. Because of 8000 char limit (400 for unicode), it is hard to do in "standard way".

here is an example:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

PRINT @Very_big_string 

Last result will be 0800 - 4 chars  for digits and one for end line - char(13) (4+1)*5=4000

There are some methods to print string by UDF etc. I propose simpler method: print XML which will contain your string (xml is not limited by char, by size in SSMS settings only - default is 2MB
So here is an example for this string:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

SELECT CAST(''+@Very_big_string+'' as XML)

Here you can preview your string in XML viewer.

Disadvantage of this method is that all standard XML symbols like <,>,! etc are changed to ampersand sequences (eg > is changed to &dt;)

How to extract substrings limited with delimiters.

If you need extract from really big string small strings which are limited by delimiters, so for you can be useful this example:

DECLARE @text AS NVARCHAR (MAX) = '***[ddd]***
dfdf
fdfdfdfdfdf
***[fff]***
4545445
45454
***[ahaASSDAD]***

DFDFDF
***[SOME   TEXT
';

DECLARE @PatBegin AS VARCHAR (1000) = '***[';

DECLARE @PatEnd AS VARCHAR (1000) = ']***';

WITH   C1
AS     (SELECT 0 AS Num
        UNION ALL
        SELECT 0),
       C2
AS     (SELECT C1.Num
        FROM   C1 CROSS JOIN C1 AS C2),
       C3
AS     (SELECT C2.Num
        FROM   C2 CROSS JOIN C2 AS C3),
       C4
AS     (SELECT C3.Num
        FROM   C3 CROSS JOIN C3 AS C4),
       C5
AS     (SELECT C4.Num
        FROM   C4 CROSS JOIN C4 AS C5),
       numbers
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY o.Num) AS Number
        FROM   C5 AS o),
       counts
AS     (SELECT COUNT(*) AS CNT
        FROM   numbers
        WHERE  PATINDEX(@PatEnd, @TExt) = 1),
       c
AS     (SELECT N.Number AS CHARBegin
        FROM   numbers AS N
        --CROSS JOIN numbers N2
        WHERE  SUBSTRING(@text, N.Number, LEN(@PatBegin)) = @PatBegin),
       d
AS     (SELECT c.CHARBegin,
               T.CHARBegin AS CharEnd,
               ISNULL(c.CHARBegin + LEN(@PatBegin) + NULLIF (CHARINDEX(@PatEnd, SUBSTRING(@text, c.CHARBegin + 1, ISNULL(T.CHARBegin, 2E7) - C.CHARBegin)), 0), 2e7) AS FactEnd
        FROM   c OUTER APPLY (SELECT TOP 1 C2.CHARBegin
                              FROM   C AS C2
                              WHERE  C2.CHARBegin > C.CharBegin) AS T)
SELECT SUBSTRING(@text, d.CHARBegin + LEN(@PatBegin), d.FactEnd - [d].[CHARBegin] - LEN(@PatBegin) - LEN(@PatEnd)) AS String_OUT,
       [d].CHARBegin
FROM   d;

here you have 2 delimiter - @PatBegin and @PatEnd - begin and end delimiters.
Result here will be:

UPDATE (09-01-2012)
Fixed logic to handle lack of last closing delimiter

Dynamic repeatable character replacement

Sometime you need to eliminate duplicated char in string. Static replacement like

SET @Output_String=REPLACE(@teststring,'__','++')

is good for not big and predictable values. But what if you need to change replaced chars dynamically or its quantity is big enough?
In that case you can use dynamic replacement. Here is an example:

SET @Output_String=REPLACE(@teststring,'__','++')

DECLARE @Duplicate NVARCHAR(100)= '#$'
DECLARE @TestString NVARCHAR(MAX)= 'test_test__f##f2$$g'
DECLARE @Replacement NVARCHAR(MAX)= ''
DECLARE @OutputString NVARCHAR(MAX)= @teststring ;
WITH    numbers
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o.object_id, o2.object_id ) Number
               FROM     sys.objects o
                        CROSS JOIN sys.objects o2
             ),
        chars
          AS ( SELECT   SUBSTRING(@Duplicate, 1, 1) CHAR ,
                        CAST(1 AS INT) [LEVEL]
               UNION ALL
               SELECT   SUBSTRING(@Duplicate, numbers.Number, 1) CHAR ,
                        CAST(numbers.Number AS INT) [LEVEL]
               FROM     numbers
                        JOIN chars ON chars.Level + 1 = numbers.Number
               WHERE    LEN(SUBSTRING(@Duplicate, numbers.Number, 1)) > 0
             ),
        Replicated
          AS ( SELECT   REPLICATE(CHAR, numbers.number) Repl ,
                        numbers.Number
               FROM     chars
                        CROSS JOIN numbers
             )
    SELECT  @OutputString = REPLACE(@OutputString, Repl, @Replacement)
    FROM    replicated
    WHERE   number <= LEN(@TestString)

SELECT  @OutputString

You can modify @Duplicate string which is storage for replaced characters. Also @Replacements is a string which will be inserted instead of duplicated chars

Microsoft releases cumulative update #7 for SQL Server 2008 R2

Microsoft releases cumulative update #7 for SQL Server 2008 R2. After this update, your SQL will have version 10.50.1777.0
You can check this by running query:

SELECT  SERVERPROPERTY('ProductVersion') as ServerVersion

You can find article related with this Update on Microsoft page: http://support.microsoft.com/kb/2507770.
Most critical fixes:

Database Engine :

• Error message when you try to bulk insert data into a table that contains a DATE type column that uses a US date format in SQL Server 2008 or in SQL Server 2008 R2: "Bulk load data conversion error
• "CPerIndexMetaQS::ErrorAbort - Index corruption" error when you try to update a partitioned view in SQL Server 2008 R2
• A common table expression-based query that uses the CHANGETABLE function runs very slowly in SQL Server 2008 Service Pack 1 or SQL Server 2008 R2
• A query that uses the DATEDIFF function may run slowly in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2
• SQL Server Agent job runs incorrectly if it is scheduled to run every hour on the last day of every week or month in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2
• Error 7359 when you run a query against a user-defined function or a view that uses a synonym on a linked server in SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2
• Assertion error when you try to recover a database in SQL Server 2008 R2
• "There is no function '{urn:schemas-microsoft-com:xml-sql}:variable()" error for an XQuery query that updates the value of an XML column in SQL Server 2005 or in SQL Server 2008 R2
• PowerPivot window does not open when you open an .xla or .xlam file in Excel 2010 if PowerPivot for Excel 2010 is installed
• SQL Server Browser service periodically does not respond to incoming requests
• Application performance issue when a query references temporary tables that are created in a session in SQL Server 2008 R2
• The Intellisense feature in SQL Server Management Studio for SQL Server 2008 R2 may stop working after you install Visual Studio 2010 Service Pack 1

Reporting services:

• Performance decreases after you move a large report to SQL Server 2008 R2 Reporting Services
• Report builder 3.0 stops responding after you run a query more than two times against a report model in SSRS 2008 R2
• Performance decreases after you move a report that contains a large multi-select drop-down parameter list to SQL Server 2008 R2 Reporting Services

SSIS:

• You can now use Package Installation Wizard (Dtsinstall.exe) to run the deployment manifest file of an SSIS package when SSIS 2008 or SSIS 2008 R2 is not installed
• Column of a Flat File source is not imported if the next column data is one byte longer than the output column width in SSIS 2005, SSIS 2008 or in SSIS 2008 R2

How to transpose table(change columns to rows).

One of most discussed question about TSQL algorithms is „How to transpose (change columns into rows) a table?” Most solutions based on static code which is adapted to your table. For every table, you need to write different code. Some authors propose using PIVOT statement, but it is not very handy to use this because PIVOT uses rows aggregation so avoiding this is always so easy.

Another question is "What if i need not only data change but gets columns/rows names too?” I you read my previous posts so you know which method we will use: XML-based.
This is how it looks:

DECLARE @xml XML ,
    @RowCount BIGINT
CREATE TABLE #Table
    (
      Column#1 INT ,
      Column2 NVARCHAR(MAX) ,
      Column3 DECIMAL(15, 2)
    )
CREATE TABLE #TempTable
    (
      RowID BIGINT ,
      CellId BIGINT ,
      Value NVARCHAR(MAX) ,
      ColumnName NVARCHAR(MAX)
    )
DECLARE @sSQl NVARCHAR(MAX)= 'SELECT (SELECT DISTINCT ColumnName FROM #TempTable WHERE CellId=Cell.CellId) as ColumnName,'
INSERT  INTO #Table
        SELECT  5 ,
                'Column_1_Test_String' ,
                99.99
INSERT  INTO #Table
        SELECT  9 ,
                'Column_2_Test_String' ,
                NULL


SET @xml = ( SELECT * ,
                    Row_Number() OVER ( ORDER BY ( SELECT   1
                                                 ) ) Rn
             FROM   #Table Row
           FOR
             XML AUTO,
                 ROOT('Root') ,
                 ELEMENTS XSINIL
           ) ;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi),RC AS
   (SELECT COUNT(Row.value('.', 'nvarchar(MAX)')) [RowCount]
   FROM @xml.nodes('Root/Row') AS WTable(Row))
,c AS(
SELECT b.value('local-name(.)','nvarchar(max)') ColumnName,
  b.value('.[not(@xsi:nil = "true")]','nvarchar(max)') Value,
  b.value('../Rn[1]','nvarchar(max)') Rn,
  ROW_NUMBER() OVER (PARTITION BY b.value('../Rn[1]','nvarchar(max)') ORDER BY (SELECT 1)) Cell
FROM
 @xml.nodes('//Root/Row/*[local-name(.)!="Rn"]') a(b)

 ),Cols AS (
 SELECT  DISTINCT c.ColumnName,
  c.Cell
 FROM c
 )
 INSERT INTO #TempTable (CellId,RowID,Value,ColumnName)
 SELECT Cell,Rn,Value,REPLACE(c.ColumnName,'_x0023_','#')
 FROM c
 
SELECT  @sSQL = @sSQl
        + '(SELECT T2.Value FROM #Temptable T2 WHERE T2.CellId=Cell.CellID AND T2.Rowid='
        + CAST(T.RowId AS NVARCHAR) + ') AS Row_' + CAST(T.RowID AS NVARCHAR)
        + ','
FROM    ( SELECT DISTINCT
                    RowId
          FROM      #TempTable
        ) T
SET @sSQl = LEFT(@sSQL, LEN(@sSQL) - 1)
    + ' FROM (SELECT DISTINCT CellId FROM #TempTable) Cell'
EXECUTE sp_Executesql @sSQl
--here you will have your output
DROP TABLE #Table
DROP TABLE #TempTable

Description:
Lines 1-24: declaring variables and filling up source table:

Lines 25-33: writing source table as XML into XML variable









Lines 34-52: filling up ##Temptable (temporary table). It is used for debug purpose only. If you test this solution, you can avoid this and implement it as next CTE in chain of sub-queries.


Lines 54-63: Create dynamic SQL statement to prepare transposed output

Line 64: Executing dynamic SQL. Here we have next output:

Lines 66-67 Dropping temporary tables.

Here I described how you can easily transpose table without knowing nothing about source table structure. Based on code above,you can prepare stored procedure which will get XML generated from table and it will return transposed XML. In such way you can easily transpose any of your tables from any place in your code.