Tag: T-SQL

 

One of the things that is needed in quite a few functions is the object_id of a particular table/view/procedure/function in SQL Server. For example, I was looking at STATS_DATE recently, and it has this definition.

STATS_DATE (object_id, stats_id)

In the past, I’d run something like this:

DECLARE @i INT
SELECT @i = object_id
 FROM sys.objects 
 WHERE name = 'SalesOrderHeader'
SELECT STATS_DATE ( @i , 2)

Actually, I’d really do this as two batches.

SELECT * FROM sys.objects WHERE name = 'SalesOrderHeader'
SELECT STATS_DATE ( 1266103551 , 2)  

I’d run the first, get the ID, and paste it into the second. However I’ve learned that isn’t the best way to do this. In fact, when I started doing  a lot of encryption testing and research, I started to take advantage of functions like OBJECT_ID.

Now, here’s what I’d do:

SELECT STATS_DATE ( OBJECT_ID(‘Sales.SalesOrderHeader’) , 2) 

Simple, easy, and I can do this inline. With SQL Prompt, I’m also pretty quick getting this out. Of course, I do need to remember to include the schema, because this won’t work:

SELECT STATS_DATE ( OBJECT_ID(‘SalesOrderHeader’) , 2) 

Three warnings. First, qualify your objects. In this case, I should have used Sales.SalesOrderHeader to be sure I get the correct object. There are people that use schemas with the same object in multiple schemas (etl.SalesOrderHeader, audit.SalesOrderHeaders, etc.).

Second, the object_id() isn’t guaranteed to be unique across databases. I should have pointed that out.

Last, isolation can be an issue here. There’s a Connect item you can vote on to ask this be fixed: Metadata functions should follow same isolation semantics as metadata queries

SQLNewBlogger

When I find quick tricks or techniques I use often, I try to make a note and then write about them later. It helps me remember, but it also lets me share things with others.

Perhaps most important, it shows I’m doing and learning things in my career.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I showed how to write a simple recursive query recently that calculated an amount of money paid out each day. Now I want to extend this a bit to include a few math formulas.

Note: These aren’t terribly useful, but they are good practice for just writing recursive queries.

Fibonacci Series

I’m sure many of you have dealt with a Fibonacci series at some point in your life. This is a series where the current value is the sum of the two previous values. In other words,

term 3 = term 1 + term 2

term 4 = term 2 + term 3

etc.

The series starts with 0, 1 and goes from there. Can we do this in SQL? Sure.

Let’s start by looking at what we need. We need some counter, a current term, and a previous term. That’s 3 columns in our query. We start by building an anchor, which has the first two terms. The counter is n and the two terms are i and j.

— Anchor

select n = 1

, i = 0

, j = 1

Now we add the recursive part. In this case, the counter increases by 1. I only include the counter so I know when to stop. SQL Server has a finite size of various values, and we can exceed that without a way to stop.

The first value will be calculated from the current value + the next call. The current value will become the second one on the next last call, so we move that over. This gives us.

select counter + 1

  , first = first + second

, second = first

With this, we can then add a WHERE clause to stop. I’ll stop at the first ten terms. Here’s the CTE.

WITH myFib (n, i, j)
AS
(
  — anchor
  SELECT ‘n’ = 1
       , ‘i’ = 0
       , ‘j’ = 1
   UNION ALL
   — recursive section
   SELECT n + 1
        , i + j
        , i
       FROM myFib
WHERE myFib.n < 10
)
SELECT
‘Level’ = myFib.n
, ‘Fibonacci’ = i FROM myFib

If we run this, we see:

We can extend this by altering the WHERE clause.

A Little Calculus

What about math functions? Have any of you worked with a series in calculus? If so, you might remember something like this:

This is a repetitive calculation, and should either converge or diverge. Can we implement this as a recursive CTE? Sure.

This one is really simple. I use the POWER() function in my recursive member to raise –1 to whatever counter I’m using for n. I then use a SUM() across all previous values in the outer query to get the sum.

WITH myPartialSum (n, s)
AS
(
SELECT ‘n’ = 1
     , ‘s’ = POWER( -1, 0)
     UNION ALL
     SELECT n + 1
       , POWER(-1, n)
       FROM myPartialSum
       WHERE n < 100
)
SELECT myPartialSum.n
      ,myPartialSum.s
      , ‘partialsum’ = SUM(s) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM myPartialSum

Note: This series does not converge, as it alternates to infinity.

Neither of these is terribly useful, but they do allow some practice in writing CTEs that will recurse.

SQLNewBlogger

Implement some other series or sequence yourself and explain how it works.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

When I was a six or seven year old, my Mom asked me a question. She asked if I’d rather have $1,000,000 at the end of the month, or a penny on day 1, with the note that each day of the month, she’d double what I’d gotten the first day. Doing quick math in my head, $0.01, $0.02, $0.04, etc, I said a million.

Was I right? Let’s build a recursive CTE.

Recursion is an interesting computer science technique that stumps lots of people. When I was learning programming, it seemed that recursion (in Pascal) and pointers (in C), were the weed out topics.

However, they aren’t that bad, and with CTEs, we can write recursion in T-SQL. I won’t cover where this might be used in this post, though I will give you a simple CTE to view.

There are two parts you need: the anchor and the recursive member. These are connected with a UNION ALL. There can be multiple items, but we’ll keep things simple.

I want to first build an anchor, which is the base for my query. In my case, I want to start with the day of the month, which I’ll represent with a [d]. I also need the amount to be paid that day, which is represented with [v]. I’ll include the $1,000,000 as a scalar at the end. My anchor looks like this:

WITH myWealth ( d, v)
AS (
— anchor, day 1
SELECT
‘d’ = 1
, ‘v’ = CAST( 0.01 AS numeric(38,2))
UNION ALL
Now I need to add in the recursive part. In this part, I’ll query the CTE itself, calling myWealth as part of the code. For my query, I want to increment the day by 1 with each call, so I’ll add one to that value.

SELECT
myWealth.d + 1

For the payment that day, it’s a simple doubling of the previous day. So I can do this a few days: addition or multiplication. I’ll use multiplication since it’s easier to read.

SELECT
myWealth.d + 1
, myWealth.v * 2

My FROM clause is the CTE itself. However I need a way to stop the recursion. In my case, I want to stop after 31 days. So I’ll add that.

UPDATE: The original code (<= 31) went to 32 days. This has been corrected to stop at 31 days.

FROM
myWealth
WHERE
myWealth.d <= 30

Now let’s see it all together, with a little fun at the end for the outer query.

WITH  myWealth ( d, v )
AS (
— anchor, day 1)
SELECT
‘d’ = 1
, ‘v’ = CAST(0.01 AS NUMERIC(38, 2))
UNION ALL
— recursive part, get double the next value, end at one month
SELECT
myWealth.d + 1
, myWealth.v * 2
FROM
myWealth
WHERE
myWealth.d <= 31
)
SELECT
‘day’ = myWealth.d
, ‘payment’ = myWealth.v
, ‘lump sum’ = 1000000
, ‘decision’ = CASE WHEN myWealth.v < 1000000 THEN ‘Good Decision’
ELSE ‘Bad decision’
END
FROM
myWealth;

When I run this, I get some results:

Did I make a good choice? Let’s look for the last few days of the month.

That $1,000,000 isn’t looking too good. If I added a running total, it would be worse.

SQLNewBlogger

If you want to try this yourself, add the running total and explain how it works.