Martina Ponca's Blog

DECLARE @statement NVARCHAR(MAX);
SET @statement = N'
CREATE FUNCTION [dbo].[SplitWordToLetters]
(
      @StringToSplit nvarchar(2000)
)
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Letter NCHAR(1)
)
AS
BEGIN
      Declare @substring nvarchar(2000)
      Declare @letter NCHAR(1)

      Set @substring = @StringToSplit 

      While (LEN(@substring) > 0)
      Begin
            SELECT @letter = LEFT(@substring, 1), @substring = SUBSTRING(@substring, 2, 255)

            Insert into @RtnValue (Letter) values (@letter)
      End

      Return
END '

IF OBJECT_ID(N'[dbo].[SplitWordToLetters]', N'TF') IS NULL
BEGIN
      EXECUTE (@statement);
END
ELSE
BEGIN
      SET @statement = REPLACE(@Statement, 'CREATE FUNCTION', 'ALTER FUNCTION');
      EXECUTE (@statement);
END

Usage:

SELECT *
FROM dbo.SplitWordToLetters ('abc')

/* Result:
Id          Letter
----------- ------
1           a
2           b
3           c

(3 row(s) affected)
*/

DECLARE @Temp TABLE (PrimaryKey INT, ParentKey INT)

INSERT INTO @Temp (PrimaryKey, ParentKey)
SELECT 1, 0
UNION SELECT 2, 1
UNION SELECT 3, 2
UNION SELECT 4, 3;  

WITH TopParent(PrimaryKey, ParentLevel, ParentKey) AS
(
       SELECT PrimaryKey, 0, ParentKey
       FROM @Temp
       UNION ALL
       SELECT Temp.PrimaryKey, TP.ParentLevel + 1, TP.ParentKey
       FROM @Temp Temp
       INNER JOIN TopParent TP ON TP.PrimaryKey = Temp.ParentKey
)
SELECT Tbl.PrimaryKey, Tbl.MaxLevel, TopParent.ParentKey
FROM TopParent
INNER JOIN (
	SELECT TopParent.PrimaryKey,
		   MAX(TopParent.ParentLevel) AS MaxLevel
	FROM TopParent
	GROUP BY TopParent.PrimaryKey
) Tbl ON Tbl.PrimaryKey = TopParent.PrimaryKey
	AND Tbl.MaxLevel = TopParent.ParentLevel;

/*
PrimaryKey  MaxLevel    ParentKey
----------- ----------- -----------
1           0           0
2           1           0
3           2           0
4           3           0

(4 row(s) affected)
*/

SELECT
       MyTable.myColumn.value('local-name(.)', 'VARCHAR(100)') AS ColumnHeader,
       MyTable.myColumn.value('.', 'VARCHAR(MAX)') AS Value
FROM (
       SELECT (
       SELECT *
       FROM Production.Product
       WHERE ProductID = 1
       FOR XML RAW, TYPE) AS XmlRow
)T
CROSS APPLY T.XmlRow.nodes('row/@*') AS MyTable(myColumn)

I used this idea when creating a Generic Drillthrough Report for SSRS, my query for dataset looked like this:

-- the SSRS report has the following parameters
DECLARE @SchemaDotTableOrViewName VARCHAR(200), @PrimaryKeyColumnName VARCHAR(200), @PrimaryKeyValue VARCHAR(20)
SELECT @SchemaDotTableOrViewName = 'Production.Product',
	@PrimaryKeyColumnName = 'ProductID',
	@PrimaryKeyValue = 1

-- dataset query
DECLARE @statement NVARCHAR(MAX)
SET @statement =
N'
SELECT
	MyTable.myColumn.value(''local-name(.)'', ''VARCHAR(100)'') AS ColumnHeader,
	MyTable.myColumn.value(''.'', ''VARCHAR(MAX)'') AS Value
FROM (
	SELECT (
		SELECT *
		FROM ' + @SchemaDotTableOrViewName + '
		WHERE ' + @PrimaryKeyColumnName + ' = ' + @PrimaryKeyValue + '
		FOR XML RAW, TYPE
	) AS XmlRow
) T
CROSS APPLY T.XmlRow.nodes(''row/@*'') AS MyTable(myColumn)
'

EXECUTE dbo.sp_executesql @statement

-- Create Date
declare @year int, @month int, @day int
select @year = 2009, @month= 3, @day = 3;
select dateadd(mm,(@year-1900)* 12 + @month - 1,0) + (@day -1) [Date]

-- Get Last Name out of Full Name string
DECLARE @FullName VARCHAR(30), @LastName as VARCHAR(30)
SET @FullName = 'Smith,John';
SELECT LastName = SUBSTRING(@FullName, 0, CHARINDEX(',', @FullName, 0))

-- @myTable is helpful in JOIN with another table when using GROUP BY in let's say Order
-- table when we need a SUM of $$$ by Date including the date with no orders for a given date
SET NOCOUNT ON
DECLARE @mytable TABLE (myDate DATE)
DECLARE @StartDate date, @EndDate date, @someDate datetime, @insertDate date
SET @someDate = GETDATE();
SET @StartDate = DateAdd(Month,DateDiff(Month,0,@someDate),0); -- first day of month
SET @EndDate = DAteAdd(Day, -1, DateAdd(Month, 1, @StartDate)) -- last day of month

SET @insertDate = @StartDate;
WHILE @insertDate <= @EndDate
BEGIN
       INSERT INTO @mytable (myDate)
       SELECT @insertDate;

       SET @insertDate = DATEADD(Day, 1, @insertDate);
END
SET NOCOUNT OFF
SELECT @StartDate As FirstDayOfMonth, @EndDate As LastDayOfMonth;
SELECT myDate from @mytable order by myDate;

/*
FirstDayOfMonth LastDayOfMonth
--------------- --------------
2010-09-01      2010-09-30

(1 row(s) affected)

myDate
----------
2010-09-01
2010-09-02
2010-09-03
2010-09-04
2010-09-05
...
...
2010-09-26
2010-09-27
2010-09-28
2010-09-29
2010-09-30

(30 row(s) affected)
*/

-- Rollup rows (lists) to a comma separated string
DECLARE @Temp TABLE (String VARCHAR(10))
DECLARE @Rollup VARCHAR(MAX)
DECLARE @Separator VARCHAR(3)
SET @Separator = ','; 

INSERT INTO @Temp (String)
SELECT 'flower'
UNION SELECT 'flour'
UNION SELECT 'cup'
UNION SELECT 'almond';
SELECT * FROM @Temp

SELECT @Rollup = ISNULL(@Rollup, '') + ISNULL(String, '') + @Separator
FROM @Temp
ORDER BY String ASC;

SELECT @Rollup As [Rollup];

/*
Rollup
--------------------------
almond,cup,flour,flower,

(1 row(s) affected)
*/

DECLARE @statement NVARCHAR(MAX);
SET @statement = N'
CREATE FUNCTION [MySchemaName].[Split]
(
      @StringToSplit nvarchar(2000),
      @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Data nvarchar(100)
)
AS
BEGIN
      Declare @Counter int
      Set @Counter = 1

      While (Charindex(@SplitOn,@StringToSplit)>0)
      Begin
            Insert Into @RtnValue (data)
            Select
                  Data = ltrim(rtrim(Substring(@StringToSplit,1,Charindex(@SplitOn,@StringToSplit)-1)))

            Set @StringToSplit = Substring(@StringToSplit,Charindex(@SplitOn,@StringToSplit)+1,len(@StringToSplit))
            Set @Counter = @Counter + 1
      End

      Insert Into @RtnValue (data)
      Select Data = ltrim(rtrim(@StringToSplit))

      Return
END '

IF OBJECT_ID(N'[MySchemaName].[Split]', N'TF') IS NULL
BEGIN
      EXECUTE (@statement);
END
ELSE
BEGIN
      SET @statement = REPLACE(@Statement, 'CREATE FUNCTION', 'ALTER FUNCTION');
      EXECUTE (@statement);
END

Problem:
Stored procedure was taking about 1-2 seconds when run in SQL Query Analyzer while with the same parameters timed out after 90 seconds if run via .NET application and using .Net SqlClient Data Provider.

Introducing the stored proc:
- About 10 parameters
- Many variables inside stored proc
- Some of the tables queried had millions of records
- Temporary tables were also created inside the stored procedure which reduces the chance to reuse the execution plan.

Two indexes were created for various scenarios but clearly SQL Server kept using the same cashed query plan. One of the indexes was not used. Why would SQL Server use a different query plan when connecting via different application?

When we drop and create stored procedure, SQL Server checks the syntax and then adds it as a new object but it will not compile it at creation time. When the stored proc get executed for the first time, SQL Server sniffs the parameters during compilation or recompilation to generate the fastest execution plan but for me, I needed different execution plans for different parameters passed. SQL Server stores only one execution plan per stored proc.

Solution:
1. WITH RECOMPILE - older solution used in SQL Server 2000 – we could force recompilation each time a stored proc is executed but this could consume too many server resources if the stored procedure is called often. Also, WITH RECOMPILE does not help if variables are used in queries.

2. OPTION (RECOMPILE) – new query hint in SQL Server 2005 compiles the SQL with all known parameters AND variables (yes, also variables – WITH RECOMPILE does not help if variables are used in query). This query hint allows recompiling only that query within stored proc not all queries in a given stored proc. This worked for me and is preferred solution.

Interesting comment from Technet:
http://technet.microsoft.com/en-us/library/cc966425.aspx
Query plan reuse – “In general, a query plan can be reused if the server, database, and connection settings of the connection that caused the query plan to be cached are the same as the corresponding settings of the current connection
Best Practice: “CREATE PROCEDURE … WITH RECOMPILE” can be used to mark stored procedures that are called with widely varying parameters, and for which best query plans are highly dependent on parameter values supplied during calls.

Compilations, Recompilations, and Parameter Sniffing
In SQL Server 2005, the behavior is extended for queries submitted using the OPTION(RECOMPILE) query hint. For such a query (could be SELECT, INSERT, UPDATE, or DELETE), both the parameter values and the current values of local variables are sniffed. The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the OPTION(RECOMPILE) hint. In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

3. Using Local variables disables parameter sniffing. One of my colleagues Alex Suprun found this solution where disabling parameter sniffing by using local variables solved the problem:
SQL Parameter Sniffing and what to do about it
How to Disable Parameter Sniffing in SQL Server 2005

Microsoft does not recommend using local variables: In the 2 articles below search for Avoid use of local variables in queries
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
“If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer typically can pick a better query plan. For example, consider this query, which uses a local variable.”

———————————————
Good Query Perfomance Articles:
Glenn Berry’s SQL Server Performance
I Smell a Parameter! (blogs.msdn.com)

Not all special characters cause an issue especially if most XML is generated using FOR XML PATH which takes care of encoding in 99% cases and encode any special characters not permitted in an XML document. The TYPE directive also helps as it creates the XML datatype.

CDATA or validation function? Neither scenario is pretty or quick.

CDATA can only be used with FOR XML EXPLICIT. CDATA is not preserved when used with FOR XML PATH. This would mean inserting EXPLICIT modes into existing FOR XML PATH code.

The only scenarios where FOR XML PATH failed (for me) is when using CHAR(25) and CHAR(147). We could use REPLACE or CDATA to resolve the issue.

Creating some REPLACE function seems easier to implement but we’d need to decide what special characters will be replaced.
CDATA is more work to implement, but we would not have to worry about what special character we’re dealing with.
I don’t really provide any solution here… it’s just some research I’ve done at the time.

CREATE TABLE #Temp (TempId INT, specialChar varchar(500), specialCharXml XML )
INSERT INTO #Temp (TempId, specialChar) VALUES (1, '€ Euro, à A tilde, † dagger, Š S caron (hacek), œ oe, ©, ¶ , » ÿ  ёйцбджюхъэ È')
INSERT INTO #Temp (TempId, specialChar) VALUES (2, '

- #x9 #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF] ')
INSERT INTO #Temp (TempId, specialChar) VALUES (3, '7308098ahdjkaikdmlhasl ')
INSERT INTO #Temp (TempId, specialChar) VALUES (4, N'
')
INSERT INTO #Temp (TempId, specialChar) VALUES (5, '"`1234567890-=¬!"£$%^&*()_+¦€qwertyuiop[]QWERTYUIOP{}asdfghjkl;'#ASDFGHJKL:@~\zxcvbnm,./|ZXCVBNM<>?/*-7894561230." />')
INSERT INTO #Temp (TempId, specialChar) VALUES (6, char(25))
INSERT INTO #Temp (TempId, specialChar) VALUES (7, char(147))
INSERT INTO #Temp (TempId, specialChar) VALUES (8, 'Some Value ' + CHAR(25) + 'Some OtherValue')
INSERT INTO #Temp (TempId, specialChar) VALUES (9, '♣ ◊ ♦')
INSERT INTO #Temp (TempId, specialChar) VALUES (10,'`1234567890-=¬!"£$%^&*()_+¦€qwertyuiop[]QWERTYUIOP{}asdfghjkl;'#ASDFGHJKL:@~\zxcvbnm,./|ZXCVBNM<>?/*-7894561230.')

SELECT REPLACE(REPLACE(specialChar, CHAR(25), ''), CHAR(147), '')
FROM #Temp
FOR XML PATH ('Row'), ROOT ('Data'), TYPE

DECLARE @TempId INT, @XML XML
SET @TempId = 1
WHILE (@TempId < 11)
BEGIN
	SET NOCOUNT ON
	SET @XML = NULL
	BEGIN TRY
		SET @XML = (
			SELECT *
			FROM #Temp
			WHERE TempId = @TempId
			FOR XML PATH ('Row'), ROOT ('Data'), TYPE -- FAILS char(25), char(147)
		)

		SELECT @TempId as TempID, @XML AS MyXML, @@ERROR AS Error
	END TRY
	BEGIN CATCH
		SELECT @TempId as TempID, @XML AS MyXML, @@ERROR AS Error
	END CATCH

	SET @TempId = @TempId + 1
END

SELECT 1 as Tag,
	NULL as Parent,
	specialChar as [temp!1!specialChar],
	specialChar as [temp!1!!CDATA], -- FAILS IF THIS COLUMN INCLUDED
	'some description' as [temp!1!!CDATA]
FROM #Temp FOR XML EXPLICIT, TYPE 

-- successful
SELECT 1 As Tag,
	NULL as Parent,
	(SELECT specialChar as 'data()'
	FROM #Temp Temp1
	WHERE Temp1.TempId = Temp2.TempId
	FOR XML PATH('')) AS 'temp!1!specialChar!cdata'
FROM #Temp Temp2
FOR XML Explicit

-- successful
SELECT 1 As Tag,
	NULL as Parent,
	(SELECT specialChar as 'data()'
	FROM #Temp Temp1
	WHERE Temp1.TempId = Temp2.TempId
	FOR XML PATH('')) AS 'temp!1!specialChar!cdata'
FROM #Temp Temp2
FOR XML Explicit, TYPE

-- fails
SELECT * FROM #Temp WHERE TempId = 8 FOR XML PATH ('Row'), ROOT ('Data') -- DOES NOT FAILS char(25)
SELECT * FROM #Temp WHERE TempId = 8 FOR XML PATH ('Row'), ROOT ('Data'), TYPE -- FAILS char(25)

-- able to reproduce if directly assigning varchar to an xml
DECLARE @xml xml;
SET @xml=N'';
select @xml 

DROP TABLE #Temp

-- EXPLICIT Mode: http://msdn.microsoft.com/en-us/library/ms189068.aspx
-- Encoding: http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references#Character_entities_in_XML
-- Invalid characters and escape rules: http://msdn.microsoft.com/en-us/library/bb500235.aspx
-- Remove Special Characters function: http://geekswithblogs.net/Gaurav/archive/2008/04/01/120947.aspx
-- Remove Special Characters: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24583739.html

Working with MDF files from Book CDs:
—————————————————–
Script:
—————————————————–
sqlcmd -E -o “%CD%\attach.log” -Q “exec master..sp_attach_db ‘ExamplesDB’, [%CD%\ExamplesDB.mdf]” -e

—————————————————–
Error:
—————————————————–
exec master..sp_attach_db ‘ExamplesDB’, [C:\Microsoft Press\SQL Server 2008 Examples\Setup\ExamplesDB.mdf]
File activation failure. The physical file name “C:\Microsoft Press\SQL Server 2008 Examples\Setup\ExamplesDB_log.LDF” may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Server SHARP, Line 1
Could not open new database ‘ExamplesDB’. CREATE DATABASE is aborted.

—————————————————–
Solution:
—————————————————–
CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort

/*	1. create database with the same name of mdf and ldf
	2. shut down slq server
	3. swap mdf files
*/
ALTER DATABASE ExamplesDB SET Emergency
GO
--DBCC REBUILD_LOG
--GO
ALTER DATABASE ExamplesDB SET SINGLE_USER;
GO
DBCC CHECKDB (ExamplesDB, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE ExamplesDB SET MULTI_USER;
GO

SELECT state_desc FROM sys.databases WHERE name='ExamplesDB';