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.