SQL with Manoj

Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

Recursive CTE error – The maximum recursion 100 has been exhausted before statement completion

December 23, 2011 12 comments

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them. In my previous posts I’ve discussed this topic with some good examples, link.

An incorrectly composed recursive CTE may cause an infinite loop. So recursive CTEs should be designed very carefully and the recursion level should be checked. To prevent it to run infinitely SQL Server’s default recursion level is set to 100. But you can change the level by using the MAXRECURSION option/hint. The recursion level ranges from 0 and 32,767.

If your CTEs recursion level crosses the limit then following error is thrown by SQL Server engine:
Msg 530, Level 16, State 1, Line 11
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Let’s check this with an example discussed in MSDN TSQL forum, link:

DECLARE
	@startDate DATETIME,
	@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
	SELECT
		YEAR(@startDate) AS 'yr',
		MONTH(@startDate) AS 'mm',
		DATENAME(mm, @startDate) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		@startDate 'new_date'
	UNION ALL
	SELECT
		YEAR(new_date) AS 'yr',
		MONTH(new_date) AS 'mm',
		DATENAME(mm, new_date) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		DATEADD(d,1,new_date) 'new_date'
	FROM CTE
	WHERE new_date < @endDate
	)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)

Output:-

Year	Month		Days
2011	November	22
2011	December	31
2012	January		31
2012	February	29
2012	March		24

Here, by applying “OPTION (MAXRECURSION 1000)”, we can set the recursion level, so that it does not go infinite.

Note: Restriction of recursive CTE is – “A view that contains a recursive CTE cannot be used to update data”.

More info on: http://msdn.microsoft.com/en-us/library/ms175972.aspx

MSDN & Online Technical Articles on using XML with SQL

December 22, 2011 Leave a comment

On this post I’m not going to discuss anything about XML. Here I’m listing out some links that I found very informative so that I can refer to them easily in future, and so can you.

My all previous posts on XML-SQL can be found at: https://sqlwithmanoj.wordpress.com/category/xml/

There are also lot of Technical Articles in MS BOL that discuss about using XML in SQL Server.

Here is a list of those:

What’s New in FOR XML: http://msdn.microsoft.com/en-US/library/ms345137(v=SQL.90).aspx

XML Best Practices: http://msdn.microsoft.com/en-US/library/ms345115(v=SQL.90).aspx

XML Indexes: http://msdn.microsoft.com/en-US/library/ms345121(v=SQL.90).aspx

XML Options: http://msdn.microsoft.com/en-US/library/ms345110(v=SQL.90).aspx

XML Support: http://msdn.microsoft.com/en-US/library/ms345117(v=SQL.90).aspx

Performance Optimizations for the XML Data Type: http://msdn.microsoft.com/en-US/library/ms345118(v=SQL.90).aspx

… enjoy reading.

Please post your comments and links if you found any informative online article about XML-SQL.

Categories: Datatypes, XML Tags: XML, XML Best Practices, XML index, XML SQL

Using IDENTITY function with SELECT statement in SQL Server

December 15, 2011 6 comments

In MS BOL for IDENTITY columns it is mentioned that [link]: “It creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.”

But experimenting with IDENTITY function I learnt this new thing with is not mentioned (or I could not find) anywhere in MS BOL that: “IDENTITY function can also be used with a SELECT statement but with some condition.”

Let’s check what’s that condition is:

USE [AdventureWorks]
GO

-- Let's use Person.Contact table in our SELECT statement and add a new column as IDENTITY function to it:
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	ContactID, Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
FROM Person.Contact

Error Msg thrown:
Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

As per the error msg we cannot add an IDENTITY column to a SELECT query. The SELECT should be followed by an INTO clause. This way a new table will be created and records will be entered with the new IDENTITY column.

-- Let's add INTO clause in SELECT statement:
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	ContactID, 
	Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
INTO #tempTable
FROM Person.Contact

Again an Error Msg:
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘#tempTable’, which already has column ‘ContactID’ that inherits the identity property.

Completely agree with this!!! You cannot have 2 IDENTITY columns in a single table.

-- So we will remove the ContactID column as we want to have new ID column.
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
INTO #tempTable
FROM Person.Contact
-- This worked perfectly.

-- Now let's check the newly created temp table and inserted records:
select * from #tempTable

-- Output below shows selected records from Person.Contact table with a new column having values starting from 100 with a gap of 5:

-- Final Cleanup
DROP TABLE #tempTable

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check the same demo here in YouTube:

Categories: DB Concepts, SQL Tips Tags: IDENTITY, SELECT IDENTITY INTO

Query XML with different languages or different UNICODE encodings [UTF-8 & UTF-16]

December 9, 2011 1 comment

While creating XML documents we should take care of the UTF encoding if we are dealing with NON-US or international data. Non-English foreign languages fall in different ranges and takes more memory size compared to English language and thus require different encoding schemes, as follows:

>> UTF-8: represents an octet (8 bit) loosless encoding of Unicode characters. UTF-8 encodes each Unicode character as a variable number of 1 to 4 octets, where the number of octets depends on the integer value assigned to the Unicode character. It is an efficient encoding of Unicode documents that use mostly US-ASCII characters because it represents each character in the range U+0000 through U+007F as a single octet. UTF-8 is the default encoding for XML.

In an XML prolog the encoding is represented as:
‘<?xml version=”1.0″ encoding=”UTF-8″ ?>’

>> UTF-16: encoding is the 16-bit encoding of Unicode. It use 2 bytes per character (and sometimes combines two pairs), it makes implementation easier, but looks a bit overkill for Western languages encoding.

In an XML prolog the encoding is represented as:
‘<?xml version=”1.0″ encoding=”UTF-16″ ?>’

Let’s check both the encodings and issues if they are not used correctly:

-- ===================================================
-- XML containing NON Unicode English characters only:
-- ===================================================
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-8"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Happy Birthday</subject>
	<body>Wish you a very very Happy Birth Day!!!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

 Correct expected output:-

TO	FROM	SUBJECT		BODY
Gaurav	Garvit	Happy Birthday	Wish you a very very Happy Birth Day!!!

Let’s see how this works with international languages. Let’s change the language to French with same UTF-8 encoding:

-- ============================================
-- When XML contains Unicode French characters:
-- ============================================
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-8"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

We get an Error Message:
Msg 9420, Level 16, State 1, Line 2
XML parsing: line 6, character 31, illegal xml character

As a NON-English language (French) is used to so we have to use the UTF-16 encoding:

-- Let's change the encoding to UTF-16, i.e. for UNICODE characters:
DECLARE @xml as XML
SET @xml =
'<?xml version="1.0" encoding="utf-16"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

Still getting the Error Message:
Msg 9402, Level 16, State 1, Line 2
XML parsing: line 1, character 39, unable to switch the encoding

Now, as we are dealing with UNICODE data so the XML string should also be of UNICODE type, so I’ll prefix ‘N’ before the string starts in SET statement:

-- Add N for to make the xml string Non-Unicode:
DECLARE @xml as XML
SET @xml =
N'<?xml version="1.0" encoding="utf-16"?>
<root>
	<to>Gaurav</to>
	<from>Garvit</from>
	<subject>Joyeux anniversaire</subject>
	<body>Je vous souhaite une très très heureux anniversaire!</body>
</root>'

SELECT
	T.C.value('to[1]', 'VARCHAR(10)') AS 'TO',
	T.C.value('from[1]', 'VARCHAR(10)') AS 'FROM',
	T.C.value('subject[1]', 'VARCHAR(50)') AS 'SUBJECT',
	T.C.value('body[1]', 'VARCHAR(200)') AS 'BODY'
FROM @xml.nodes('//root') T(C)
GO

Correct expected output:-
TO	FROM	SUBJECT			BODY
Gaurav	Garvit	Joyeux anniversaire	Je vous souhaite une très très heureux anniversaire!

As per MS BOL: “SQL Server stores XML data using the UTF-16 encoding scheme. Because UTF-16 data is variable-width, it is processed according to a byte-oriented protocol. This means that UTF-16 data can be treated in a way that is independent of the byte ordering on different computers (little endian versus big endian). Therefore, UTF-16 is well-suited for traversing different computers that use different encodings and byte-ordering systems. Because XML data is typically shared widely across networks, it makes sense to maintain the default UTF-16 storage of XML data in your database, and when you export XML data to clients.”

More info on: http://msdn.microsoft.com/en-US/library/ms145866%28v=SQL.90%29.aspx

Categories: XML Tags: big endian, encoding, little endian, UTF-16, UTF-8, XML, xml version

TSQL Interview Questions – Part 5

December 6, 2011 6 comments

Next part of TSQL Interview Questions, contd. from my previous post.

101. What are the required/mandatory parameters that have to be passed with RAISEERROR statement?

– message _id or message_str
– severity, and
– state

102. Difference between RAISERROR and THROW
RAISERROR vs THROW: https://sqlwithmanoj.com/2012/12/20/new-throw-statement-in-sql-server-2012-vs-raiserror/

103. What severity level errors are managed in TRY-CATCH block?
A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. Check about TRY-CATCH here: https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/

104. What are row constructors?
Also known as Table Value Constructor: https://sqlwithmanoj.com/2015/02/16/sql-basics-what-are-row-constructors/.
Also check Method #5 of this post: https://sqlwithmanoj.com/2010/10/15/multiple-ways-to-insert-records-in-a-table/

105. What is SCD (Slowly Changing Dimensions)? What are its types?
Check this YouTube tutorial on SCD: v=aTnr00NnlXE

106. How many types of internal joins are there in SQL Server?
– Nested loop join
– Merge join
– Hash join
More about these: https://sqlwithmanoj.com/2010/10/06/physical-join-vs-logical-join/

107. What are pages in SQL Server? How many types of pages are there?
A page in SQL Server is an 8 KB data storage area. There are 8 types of pages, check details here: https://sqlwithmanoj.com/2011/10/12/what-are-records-pages-extents-heap/

108. How many data sources are available in SSIS?
– DataReader Source in 2005 & ADO NET Source in 2008 & above.
– Excel Source
– Flat File Source
– OLE DB Source
– Raw File Source
– Script Component
– XML Source

109. How will you deploy an SSIS package in testing, staging & production environments?
The dtutil is the command prompt utility which is used to manage SSIS packages.
This utility can copy, move, delete or verify the existence of a package.
More on: http://msdn.microsoft.com/en-us/library/ms162820.aspx

110. Where will you use Conditional Split transformation?
More info: http://msdn.microsoft.com/en-us/library/ms137886.aspx

111. What is live lock, deadlock and what is Lock escalation?
Check this YouTube tutorial on Deadlock: v=V_KLPWHfYFU

112. If you are working on a SQL database and if suddenly a developer changes the code and your queries results start giving errors, how will you check using a T-SQL query (on system tables) that what has changed in the database.
select o.name, o.object_id, o.modify_date, c.text
from sys.objects o
join sys.syscomments c
on o.object_id = c.id

113. Difference between Nested loops join, Merge join & Hash Join.
Check all differences here: https://sqlwithmanoj.com/2010/10/06/physical-join-vs-logical-join/

114. What are Surrogate Keys in a Data Warehouse?
Surrogate Keys are UNIQUE, SEQUENTIAL and MEANINGLESS generated integer numbers to identify every record in a Dimension table uniquely. The are used to JOIN Dimension tables with Fact tables.

… I’ll be adding more questions on upcoming post.

Comments welcome!!!

Categories: Interview Q Tags: SQL Server Interview Questions

Newer Entries Older Entries