My blog has always been the documentation of my evolutionary journey through the world of databases, especially, Microsoft SQL Server. Today, on the birthday of our daughters, I am pleased to announce the start of my blog, SQLTwins at http://nakulvachhrajani.com. I dedicate my journey with Microsoft SQL Server to our daughters, Devika & Devina.

The content on my pages in BeyondRelational.com will be moved (gradually) over to the new blog.

Today’s post is a brief summary of the various ways in which you can stay in touch with me.

My Blog

This blog will now move to http://nakulvachhrajani.com effective today.

You can leave a comment (every one of which will be read by me). I would like to hear from you, so please leave your feedback – whether you liked the post, did not like it (if not, why), and what else would you like to know. I eagerly look forward to your comments.

You can follow my blog or even subscribe via E-mail!

My Linked-In Profile:

You can take a look at my Linked In Profile here:

Social Media Presence

You can follow me on the following:

Twitter: Tweet to @sqltwins

Google+: +Nakul

Facebook: The SQLTwins Page on FB

Do note that it’s generally a busy day at the office for me, so I don’t post updates on my stream often.

That’s pretty much it. There aren’t too many channels, which helps me to read each reply, comment or query that you might send out to me.

Until we meet next time,

Be courteous. Drive responsibly.

I was explaining recursive CTEs recently, and had to come up with a practical example for the CTE implementation. One of the first use cases that came to mind was the creation of a simple multiplication table. Now that the script is created, I thought that you may also find it useful sometime in the future.

USE tempdb;

GO

–Generate basic mathematical tables using Recursive CTE

DECLARE @inputValue INT = 2;

DECLARE @targetMultiplicationFactor INT = 20;
–Define the CTE to generate a mathematical table

;WITH GenerateTablesCTE (Input, MultiplicationFactor, Result)

AS

(

    –Anchor member

    SELECT @inputValue AS Input,

            1 AS MultiplicationFactor,

            (@inputValue * 1) AS Result

    UNION ALL

    –Recursive member

    SELECT @inputValue AS Input,

            (gtCTE.MultiplicationFactor + 1) AS MultiplicationFactor,

            (@inputValue * (gtCTE.MultiplicationFactor + 1)) AS Result

    FROM GenerateTablesCTE AS gtCTE

    WHERE gtCTE.MultiplicationFactor < @targetMultiplicationFactor

)

SELECT ogtCTE.Input,

        ogtCTE.MultiplicationFactor,

        ogtCTE.Result

FROM GenerateTablesCTE AS ogtCTE;

GO

Here’s the result:

This script can be used to populate a tally/look-up table.

Further Reading

• Multiplication Table [Wikipedia Link]

Until we meet next time,

Be courteous. Drive responsibly.

The topic of today’s post might be obvious, but is backed by a question that came to me while making a change to one of our in-line table-valued functions (TVF).

We had an in-line TVF to which we had to add another SELECT statement in order to achieve a business requirement. Normally, the first thing that most developers would do is to convert it into a multi-line TVF. As a DBA, this would raise a red flag due to the much-talked about performance issues associated with multi-line TVFs.

Per Books-on-line (refer “Further Reading” section below):

The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

Now, a view can use multiple SELECT statements separated by UNION or UNION ALL. Hence, if a UNION can be used be used such that the SELECTs in a TVF can be combined into a single statement, then SQL Server will continue to treat the function as an in-line TVF. Here’s the simple example I used to cross-check that a function with only multiple SELECTs combined via the use of UNION will make it an in-line TVF:

USE tempdb;
GO</pre>
--Safety Check
IF OBJECT_ID('dbo.tmpfunc_MultiLineTest','IF') IS NOT NULL
DROP FUNCTION dbo.tmpfunc_MultiLineTest;
GO

--Create the function
--Disclaimer: This function has been created for demo purposes only!
CREATE FUNCTION dbo.tmpfunc_MultiLineTest
(@id1 INT,
@id2 INT
)
RETURNS TABLE
AS
RETURN
SELECT 1 AS Col1, 2 AS Col2
UNION
SELECT 2 AS Col1, 3 AS Col2
UNION
SELECT @id1 AS Col1, @id2 AS Col2
GO

--Is the function an in-line table valued function?
SELECT so.name,
so.type,
so.type_desc
FROM sys.objects AS so
WHERE so.name = 'tmpfunc_MultiLineTest';
GO
-------------------------
-- RESULTS
-------------------------
--name type type_desc
-------------------------- ---- ---------------------------------
--tmpfunc_MultiLineTest IF SQL_INLINE_TABLE_VALUED_FUNCTION

As you can see, I have a function that uses the syntax for in-line TVFs and has 3 SELECT statements combined via the use of a UNION operator. I then query the sys.objects catalog view which clearly tells me that this created an in-line TVF.

The reason? It’s quite simple – it’s still a single query!

Further Reading

• Inline User Defined Table Valued Functions [TechNet Link]
• Table valued parameters to a table valued function – Underappreciated features of Microsoft SQL Server [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.