Category Archives: Blog

Articles related to this Blog

#0338 – SQL Server – Recursive CTE – Script to generate a simple multiplication table


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:


image


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.

#0337 – SQL Server – Inline TVFs – Using multiple SELECTs with UNION


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.

#0336 – SQL Server – Underappreciated Features – Using the UPDATE clause to update the value of a variable


We typically use the UPDATE clause to update or change the existing data in a given table or view. Most business logic requires that the value of one or more variables is set based on this update. Generally, we find that these requirements are realized with two statements – an UPDATE, followed by a SELECT statement (or vice versa, as the case may be). However, a little known and therefore underappreciated feature of the UPDATE clause is that it can also be used to update variables. Here’s an example.

Assume that we have a requirement to update the order detail quantities in the [Sales].[SalesOrderDetail] table of the [AdventureWorks2012] database with a given value supplied in the variable @orderDetailQuantityIncrementBy. At the same time, we also need to know the amount by which the order value increased or decreased.

A typical implementation of this would be:

USE AdventureWorks2012;
GO

--Variable indicating the change in Order Quantity
DECLARE @orderDetailQuantityIncrementBy INT = 1;

--Temporary table variable to hold the before/after values
DECLARE @orderUpdate TABLE (BeforeOrderQuantity INT,
                            BeforeUnitPrice DECIMAL(20, 8),
                            AfterOrderQuantity INT,
                            AfterUnitPrice DECIMAL(20, 8)
                           );

--Using a transaction so that we can return to the table's base state
BEGIN TRANSACTION UpdateVariableTest;

  --Perform the update
  UPDATE so
  SET so.OrderQty += @orderDetailQuantityIncrementBy
  OUTPUT deleted.OrderQty, 
         deleted.UnitPrice, 
         inserted.OrderQty, 
         inserted.UnitPrice
  INTO @orderUpdate (BeforeOrderQuantity, 
                     BeforeUnitPrice, 
                     AfterOrderQuantity, 
                     AfterUnitPrice)
  FROM Sales.SalesOrderDetail AS so
  WHERE so.SalesOrderID = 43659
    AND so.SalesOrderDetailID IN (8, 10);

  --Calculate the change in order total
  SELECT SUM((ou.AfterOrderQuantity * ou.AfterUnitPrice)
              - (ou.BeforeOrderQuantity * ou.BeforeUnitPrice)
            ) AS orderAmountChanges
  FROM @orderUpdate AS ou;

ROLLBACK TRANSACTION UpdateVariableTest;
GO

/***********
  RESULTS
***********/
--orderAmountChanges
--------------------
--34.54040000

As you can see, we used an UPDATE clause and collected the before/after update information in a temporary table variable. Later, we performed arithmetic operations on the value stored in the table variable.

Using a single UPDATE clause to update a variable

This requirement can be realized within a single UPDATE clause also. Here’s the modified solution:

USE AdventureWorks2012;
GO

--Variable indicating the change in Order Quantity
DECLARE @orderDetailQuantityIncrementBy INT = 1;

--Variable indicating the change in Order Amount
DECLARE @orderAmountChanges DECIMAL(20,8) = 0;

--Using a transaction so that we can return to the table's base state
BEGIN TRANSACTION UpdateVariableTest;
    --Perform the update
    UPDATE so
    SET so.OrderQty += @orderDetailQuantityIncrementBy,
        @orderAmountChanges += (@orderDetailQuantityIncrementBy * so.UnitPrice) 
    FROM Sales.SalesOrderDetail AS so
    WHERE so.SalesOrderID = 43659
      AND so.SalesOrderDetailID IN (8, 10);

    --Fetch the value from the variable
    SELECT @orderAmountChanges AS OrderAmountChanges;

ROLLBACK TRANSACTION UpdateVariableTest;
GO
/***********
  RESULTS
***********/
--OrderAmountChanges
--------------------
--34.54040000

As can be seen from the example above, the UPDATE clause makes realization of the said requirement very simple & readable.

Further Reading

  • Compound Operators – the most common mistake [Link]
  • The UPDATE clause [Books ON Line Link]

Until we meet next time,

Be courteous. Drive responsibly.

#0335 – SQL Server – Using the WITH TIES option in the TOP clause of SELECT queries


When working with the AdventureWorks database, assume that we have the following requirement:

Please provide the list of customers with TOP 100 orders from Australia with the highest order totals.

The following query is the first one that comes to mind:

USE AdventureWorks2012;
GO
SELECT TOP 100 soh.CustomerID, 
               soh.TotalDue, 
               soh.TerritoryID
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesTerritory AS sst ON soh.TerritoryID = sst.TerritoryID
WHERE sst.CountryRegionCode = 'AU'
ORDER BY soh.TotalDue DESC;
GO

image

This query would work in most cases, but analyzing the requirement, we realize that we don’t want the TOP 100 orders, we want all customers with the TOP 100 orders. These two requirements are different, and here’s why:

  • TOP 100 orders by total value = this means that we should have only 100 orders being returned, in the order defined by the ORDER BY clause
  • Customers with TOP 100 orders by total value = this means that we need to first fetch the TOP 100 order totals and then look for all customers which have orders with these totals. In this case, the number of orders being returned may be more than 100

In this case, we need to use the WITH TIES clause when defining the TOP statement. Here’s the query:

USE AdventureWorks2012;
GO
SELECT TOP 100 WITH TIES 
        soh.CustomerID, 
        soh.TotalDue, 
        soh.TerritoryID
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesTerritory AS sst ON soh.TerritoryID = sst.TerritoryID
WHERE sst.CountryRegionCode = 'AU'
ORDER BY soh.TotalDue DESC;
GO

image

NOTES:

  • The TOP…WITH TIES can only be used in a SELECT statement
  • It cannot be used without an accompanying ORDER BY clause (a normal TOP can)
  • The TOP…WITH TIES also works with the TOP (n) PERCENT option

Until we meet next time,

Be courteous. Drive responsibly.

#0334 – SQL Server – SSMS – Troubleshooting Intellisense issues


Intellisense is one of the most frequently used features of SQL Server in the development community – so much so that if Intellisense stops working, work comes to a grinding halt and frantic calls to the IT team are made.


This post documents the steps, in order of most common occurrence (in my experience) that can be taken when faced with an issue that relates to Intellisense.


Assumption: The user is attempting to type in code in the code block, i.e. not in the comments block or string literals.



  1. Confirm that Intellisense is enabled (it may have been accidentally turned off due to “happy clicking”)

  2. Refresh the local cache

    • Often, when workstations (especially laptops) are hibernated and awaken again, newer objects are created or when connections in the Query editor are changed, Intellisense may stop working or take a while to refresh
    • To manually refresh the cache, press (Ctrl + Shift + R) or go to Edit –> Intellisense –> Refresh Local Cache

  3. Is the SSMS connecting to a SQL Server running a lower version? (e.g. SSMS for SQL 2012 connecting to SQL 2008 R2/2005 or lower) If yes, SSMS would not work
  4. Review the code – does an error exist such that the queries in the particular query editor cannot be parsed?
  5. After Visual Studio 2010 SP1 is installed on a workstation with SQL Server 2008 R2 (any release from RTM to CU6), the Intelli-sense feature in the SQL server Management Studio (SSMS) may stop working

    • This is a  known, documented issue with Microsoft (KB2531482)and the fix is quite simple – deploy the latest service pack/cumulative update of SQL Server 2008 R2

While Intellisense kicks back to life in most cases after the above steps are performed, it is still possible that it may cease to work in rare cases. The last step in this case is to reinstall the SQL Server client tools.


Further Reading:


Here are some other posts about Intelli-sense that you may be interested in:



Until we meet next time,



Be courteous. Drive responsibly.