Thinking outside the box

How to calculate the covariance in T-SQL

Peter Larsson — Wed, 18 Jan 2012 12:01:46 GMT

DECLARE @Sample TABLE
        (
            x INT NOT NULL,
            y INT NOT NULL

        )

INSERT  @Sample
VALUES  (3, 9),
        (2, 7),
        (4, 12),
        (5, 15),
        (6, 17)

;WITH cteSource(x, xAvg, y, yAvg, n)
AS (
        SELECT  1E * x,
                AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),
                1E * y,
                AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),
                COUNT(*) OVER (PARTITION BY (SELECT NULL))
        FROM    @Sample
)
SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
FROM    cteSource

Avoid stupid mistakes

Peter Larsson — Thu, 22 Sep 2011 06:38:31 GMT

Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is

SELECT COUNT(*) OfflineData

Do you see the bug?

Yes, there should be a FROM clause before the table name. Without the from clause, SQL Server treats the name as an alias for the count column. And what do the COUNT always return in this case?

It returns 1.

So the bug had a severe implication. Now I now it's easy to forget to write a FROM in your query. How can we avoid these stupid mistakes?
An way is very easy; always prefix your table names with schema. Besides this bug there are a lot of other benefits from prefixing your tables names with schema.

In my client's case, if OfflineData had been prefixed with dbo, the query wouldn't parse and you get a compile error.
Next thing to do to avoid stupid mistakes is to put AS before alias names, and have alias names after the expression.

SELECT COUNT(*) AS MyCount FROM dbo.OfflineData

Convert UTF-8 string to ANSI

Peter Larsson — Wed, 14 Sep 2011 04:30:46 GMT

CREATE FUNCTION dbo.fnConvertUtf8Ansi
(
    @Source VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Value SMALLINT = 160,
            @Utf8 CHAR(2),
            @Ansi CHAR(1)

    IF @Source NOT LIKE '%[ÂÃ]%'
        RETURN  @Source

    WHILE @Value <= 255
        BEGIN
            SELECT  @Utf8 = CASE
                                WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value)
                                WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value - 64)
                                ELSE NULL
                            END,
                    @Ansi = CHAR(@Value)

            WHILE CHARINDEX(@Source, @Utf8) > 0
                SET    @Source = REPLACE(@Source, @Utf8, @Ansi)

            SET    @Value += 1
        END

    RETURN  @Source
END

Do people want help? I mean, real help?

Peter Larsson — Sun, 24 Jul 2011 06:08:26 GMT

Or do they just want to continue with their old habits?

The reason for this blog post is that I the last week have tried to help people on several forums. Most of them just want to know how to solve their current problem and there is no harm in that. But when I recognize the same poster the very next day with a similar problem I ask myself; Did I really help him or her at all?

All I did was probably to help the poster keep his or her job. It sound harsh, but is probably true. Why would the poster else continue in the old habit? The most convincing post was about someone wanted to use SP_DBOPTIONS. He had an ugly procedure which used dynamic sql and other things done wrong.

I wrote to him he should stop using SP_DBOPTION because that procedure have been marked for deprecation and will not work on a SQL Server version after 2008R2, and that he should start using DATABASEPROPERTYEX() function instead.
His response was basically “Thanks, but no thanks”. Then some other MVP jumped in and gave him a solution using SP_DBOPTIONS and the original poster once again was a happy camper.

Another problem was posted by someone who wanted a unique sequence number like “T000001” to “T999999”. I suggested him to use a normal IDENTITY column and add a computed column and concatenate the “T” with the value from the IDENTITY column. Even if other people several times proposed my suggestion as an answer, the original poster (OP) unproposed my suggestion! Why?

The only reason I can think of, is that OP is not used to (or even heard of) computed columns. Some other guy posted and insinuated that computed columns don’t work on SQL Server 2000 and earlier. To that I just posted that computed columns did in fact work already back in SQL Server 7.

Are people so stuck in their old habit and inept to change for whatever reason that might be? Could it be they are not qualified, or lack enough experience, for their current position? Do they lack basic education about relational databases?

My question to you is, how do you really help people with these mindsets?

Code Audit - The Beginning

Peter Larsson — Thu, 21 Jul 2011 06:44:57 GMT

For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
My part will be the technical details of the forecasting application now when our former DBA has left our company.

Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these.

One function I stumled across is very simple. All it does is to add a timepart from current execution time to the datepart from a variable.

CREATE FUNCTION dbo.GetDateTimeFromDate
(
    @p_date DATE
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ActualWorkDateTime DATETIME

    SET @ActualWorkDateTime = CONVERT(VARCHAR, @p_date, 101) + ' '+ CONVERT(VARCHAR, GETDATE(), 114)

    RETURN @ActualWorkDateTime
END

This doesn't look to bad compared to what I have seen on the online forums. But there is a hidden performance issue here, besides being not an inline function, and that is the conversion to varchar and back to datetime. Also, this functions crashed in my tests when I changed dateformat to dmy. This is because the developer used style 101 in the convert function. If he had used style 112 the function would not have crashed no matter which dateformat value I use.

So to our next meeting I will explain to the consultants the issues I have with this function and the others that I've found.
A better choice for this function would be

CREATE FUNCTION dbo.GetDateTimeFromDate
(
    @p_date DATE
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT DATEADD(DAY, DATEDIFF(DAY, GETDATE(), @p_date), GETDATE())
            )
END

See, now there is no conversion, it's inline and dateformat-safe! A generic function for adding the date part from one variable to the time part from another variable looks like this

CREATE FUNCTION dbo.fnGetDateTimeFromDatePartAndTimePart
(
    @DatePart DATETIME,
    @TimePart DATETIME
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT DATEADD(DAY, DATEDIFF(DAY, @TimePart, @DatePart), @TimePart)
            )
END

A glance at SQL Server Denali CTP3 - DATEFROMPARTS

Peter Larsson — Wed, 13 Jul 2011 07:18:51 GMT

There is a new function in SQL Server Denali named DATEFROMPART. What is does, is to calculate a date from a number of user supplied parameters such as Year, Month and Date.

Previously you had to use a formula like this

DATEADD(MONTH, 12 * @Year + @Month - 22801, @Day)

to calculate the correct datevalue from the parameters. With the new DATEFROMPARTS, you simple write

DATEFROMPARTS(@Year, @Month, @Day)

and you get the same result, only slower by 22 percent. So why should you use the new function, if it's slower?
There are two good arguments for this

1) It is easier to remember
2) It has a built-in validator so that you cannot "spill" over the current month.

For the old way of doing this, using @Year = 2009, @Month = 2 and @Day = 29 you would end up with a date of 2009-02-28 and the DATEFROMPARTS will give you an error message.

MVP renewed

Peter Larsson — Sun, 03 Jul 2011 13:20:48 GMT

I got an email last friday telling me I was to keep my MVP status!

What do one say about that? Except "Thank you". To all that reads my articles and posts. To all who attends my presentations.

//Peter

Feedback from SQLBits 8

Peter Larsson — Wed, 18 May 2011 11:25:52 GMT

This years SQLBits occurred in Brighton. Although I didn’t have the opportunity to attend the full conference, I did a presentation at Saturday.

Getting to Brighton was easy. Drove to Copenhagen airport at 0415, flew 0605 and arrived at Gatwick 0735. Then I took the direct train to Brighton and showed up at 0830, just one hour before presenting. This was the easy part.

Getting home was much worse. Presentation ended at 1030 and I had to rush to the train station to get back to London, change to tube for Heathrow. Made it at the gate just 15 seconds before closing. That included a half mile run in the airport…

Anyway, yesterday I got the feedback for my presentation. It does look good, especially since English is not my first language.

This is the first graph

Seems to be just halfway between conference average and best session. I can live with that.

Second graph shows more detail about attendees voting.

It also look acceptable. A wider spread for the 9’s, but it is an inevitable effect from how attendees percept the session. I did get a lot of 8’s and the lower grades in an descending order. The two people voting 4 and 5 didn’t say why they voted this so I don’t know how to remedy this.

Third graph is about each category of votes.

Again, I find this acceptable. The Session abstract and Speaker’s knowledge seems to follow attendees expectations compared to conference average.
I seem to have met the attendees expectations (and some more) for the other four categories, also compared to conference average.

Since this did encourage me, I believe I will present some more at future meetings. I do have a new presentation about something all developers are doing every day but they may not know it.

I will also cover this new topic in the next Deep Dives II book.

Stay tuned!

//Peter

A tale from a Stalker

Peter Larsson — Wed, 27 Apr 2011 14:16:11 GMT

Today I thought I should write something about a stalker I've got. Don't get me wrong, I have way more fans than stalkers, but this stalker is particular persistent towards me.

It all started when I wrote about Relational Division with Sets late last year(http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx) and no matter what he tried, he didn't get a better performing query than me. But this I didn't click until later into this conversation. He must have saved himself for 9 months before posting to me again. Well...

Some days ago I get an email from someone I thought i didn't know. Here is his first email

Hi,

I want a proper solution for achievement the result. The solution must be standard query, means no using as any native code like TOP clause, also the query should run in SQL Server 2000 (no CTE use).

We have a table with consecutive keys (nbr) that is not exact sequence. We need bringing all values related with nearest key in the current key row.

See the DDL:

CREATE TABLE Nums(nbr INTEGER NOT NULL PRIMARY KEY, val INTEGER NOT NULL);
INSERT INTO Nums(nbr, val) VALUES (1, 0),(5, 7),(9, 4);

See the Result:

pre_nbr     pre_val     nbr         val         nxt_nbr     nxt_val
----------- ----------- ----------- ----------- ----------- -----------
NULL        NULL        1           0           5           7
1           0           5          7           9           4
5           7           9           4           NULL        NULL

The goal is suggesting most elegant solution. I would like see your best solution first, after that I will send my best (if not same with yours)

Notice there is no name, no please or nothing polite asking for my help.
So, on the top of my head I sent him two solutions, following the rule "Work on SQL Server 2000 and only standard non-native code".

-- Peso 1
SELECT               pre_nbr,
                             (
                                                          SELECT               x.val
                                                          FROM                dbo.Nums AS x
                                                          WHERE              x.nbr = d.pre_nbr
                             ) AS pre_val,
                             d.nbr,
                             d.val,
                             d.nxt_nbr,
                             (
                                                          SELECT               x.val
                                                          FROM                dbo.Nums AS x
                                                          WHERE              x.nbr = d.nxt_nbr
                             ) AS nxt_val
FROM                (
                                                          SELECT               (
                                                                                                                    SELECT               MAX(x.nbr) AS nbr
                                                                                                                    FROM                dbo.Nums AS x
                                                                                                                    WHERE              x.nbr < n.nbr
                                                                                       ) AS pre_nbr,
                                                                                       n.nbr,
                                                                                       n.val,
                                                                                       (
                                                                                                                    SELECT               MIN(x.nbr) AS nbr
                                                                                                                    FROM                dbo.Nums AS x
                                                                                                                    WHERE              x.nbr > n.nbr
                                                                                       ) AS nxt_nbr
                                                          FROM                dbo.Nums AS n
                             ) AS d

-- Peso 2
CREATE TABLE #Temp
                                                        (

ID INT IDENTITY(1, 1) PRIMARY KEY,

nbr INT,

val INT

)

INSERT #Temp

(

nbr,

val

)

SELECT nbr,

val

FROM dbo.Nums

ORDER BY nbr

SELECT pre.nbr AS pre_nbr,

pre.val AS pre_val,

t.nbr,

t.val,

nxt.nbr AS nxt_nbr,

nxt.val AS nxt_val

FROM #Temp AS pre

RIGHT JOIN #Temp AS t ON t.ID = pre.ID + 1

LEFT JOIN #Temp AS nxt ON nxt.ID = t.ID + 1

DROP TABLE #Temp

Notice there are no indexes on #Temp table yet.
And here is where the conversation derailed. First I got this response back

Now my solutions:

--My 1st Slt
SELECT T2.*, T1.*, T3.*
FROM Nums AS T1
       LEFT JOIN Nums AS T2
         ON T2.nbr = (SELECT MAX(nbr)
                        FROM Nums
                       WHERE nbr < T1.nbr)
       LEFT JOIN Nums AS T3
         ON T3.nbr = (SELECT MIN(nbr)
                        FROM Nums
                       WHERE nbr > T1.nbr);

--My 2nd Slt
SELECT MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END) AS pre_nbr,
       (SELECT val FROM Nums WHERE nbr = MAX(CASE WHEN N1.nbr > N2.nbr THEN N2.nbr ELSE NULL END)) AS pre_val,
       N1.nbr AS cur_nbr, N1.val AS cur_val,
       MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END) AS nxt_nbr,
       (SELECT val FROM Nums WHERE nbr = MIN(CASE WHEN N1.nbr < N2.nbr THEN N2.nbr ELSE NULL END)) AS nxt_val
FROM Nums AS N1,
       Nums AS N2
GROUP BY N1.nbr, N1.val;

/*
My 1st Slt
Table 'Nums'. Scan count 7, logical reads 14

My 2nd Slt
Table 'Nums'. Scan count 4, logical reads 23

Peso 1
Table 'Nums'. Scan count 9, logical reads 28

Peso 2
Table '#Temp'. Scan count 0, logical reads 7
Table 'Nums'. Scan count 1, logical reads 2
Table '#Temp'. Scan count 3, logical reads 16
*/

To this, I emailed him back asking for a scalability test

What if you try with a Nums table with 100,000 rows?

His response to that started to get nasty.

I have to say Peso 2 is not acceptable.

As I said before the solution must be standard, ORDER BY is not part of standard SELECT.

Try this without ORDER BY:

Truncate Table Nums

INSERT INTO Nums (nbr, val) VALUES (1, 0),(9,4), (5, 7)

So now we have new rules. No ORDER BY because it's not standard SQL! Of course I asked him

Why do you have that idea? ORDER BY is not standard?

To this, his replies went stranger and stranger

Standard Select = Set-based (no any cursor)

It’s free to know, just refer to Advanced SQL Programming by Celko or mail to him if you accept comments from him.

What the stalker probably doesn't know, is that I and Mr Celko occasionally are involved in some conversation and thus we exchange emails. I don't know if this reference to Mr Celko was made to intimidate me either. So I answered him, still polite, this

What do you mean?
The SELECT itself has a ”cursor under the hood”.

Now the stalker gets rude

But however I mean the solution must no containing any order by, top...

No problem, I do not like Peso 2, it’s very non-intelligent and elementary.

Yes, Peso 2 is elementary but most performing queries are... And now is the time where I started to feel the stalker really wanted to achieve something else, so I wrote to him

So what is your goal?
Have a query that performs well, or a query that is super-portable?

My Peso 2 outperforms any of your code with a factor of 100 when using more than 100,000 rows.

While I awaited his answer, I posted him this query

Ok, here is another one

-- Peso 3

SELECT MAX(CASE WHEN d = 1 THEN nbr ELSE NULL END) AS pre_nbr,

MAX(CASE WHEN d = 1 THEN val ELSE NULL END) AS pre_val,

MAX(CASE WHEN d = 0 THEN nbr ELSE NULL END) AS nbr,

MAX(CASE WHEN d = 0 THEN val ELSE NULL END) AS val,

MAX(CASE WHEN d = -1 THEN nbr ELSE NULL END) AS nxt_nbr,

MAX(CASE WHEN d = -1 THEN val ELSE NULL END) AS nxt_val

FROM (

SELECT nbr,

val,

ROW_NUMBER() OVER (ORDER BY nbr) AS SeqID

FROM dbo.Nums

) AS s

CROSS JOIN (

VALUES (-1),

(0),

(1)

) AS x(d)

GROUP BY SeqID + x.d

HAVING COUNT(*) > 1

And here is the stats

Table 'Nums'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

It beats the hell out of your queries….

Now I finally got a response from my stalker and now I also clicked who he was. This is his reponse

Why you post my original method with a bit change under you name? I do not like it.
See: http://www.sqlservercentral.com/Forums/Topic468501-362-14.aspx

;WITH C AS
(
SELECT seq_nbr, k,
       DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct
FROM [Sample]
        CROSS JOIN
        (VALUES (-1), (0), (1)
        ) AS D(k)
)
SELECT MIN(seq_nbr) AS pre_value,
       MAX(CASE WHEN k = 0 THEN seq_nbr END) AS current_value,
       MAX(seq_nbr) AS next_value
FROM C
GROUP BY grp_fct
HAVING min(seq_nbr) < max(seq_nbr);

These posts:

Posted Tuesday, April 12, 2011 10:04 AM

Posted Tuesday, April 12, 2011 1:22 PM

Why post a solution where will not work in SQL Server 2000?

Wait a minute! His own solution is using both a CTE and a ranking function so his query will not work on SQL Server 2000! Bummer... The reference to "Me not like" are my exact words in a previous topic on SQLTeam.com and when I remembered the phrasing, I also knew who he was. See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159262 where he writes a query and posts it under my name, as if I wrote it.
So I answered him this (less polite).

Like I keep track of all topics in the whole world… J

So you think you are the only one coming up with this idea?

Besides, “M S solution” doesn’t work.

This is the result I get

pre_value current_value next_value

1 1 5

1 5 9

5 9 9

And I did nothing like you did here, where you posted a solution which you “thought” I should write

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159262

So why are you yourself using ranking function when this was not allowed per your original email, and no cte? You use CTE in your link above, which do not work in SQL Server 2000.

All this makes no sense to me, other than you are trying your best to once in a lifetime create a better performing query than me?

After a few hours I get this email back. I don't fully understand it, but it's probably a language barrier.

>>Like I keep track of all topics in the whole world… J
So you think you are the only one coming up with this idea?<<

You right, but do not think you are the first creator of this.

>>Besides, “M S Solution” doesn’t work.
This is the result I get <<

Why you get so unimportant mistake?
See this post to correct it:

Posted 4/12/2011 8:22:23 PM

>> So why are you yourself using ranking function when this was not allowed per your original email, and no cte? You use CTE in your link above, which do not work in SQL Server 2000. <<

Again, why you get some unimportant incompatibility?
You offer that solution for current goals not me

>> All this makes no sense to me, other than you are trying your best to once in a lifetime create a better performing query than me? <<

No, I only wanted to know who you will solve it.
Now I know you do not have a special solution. No problem.

No problem for me either. So I just answered him

I am not the first, and you are not the first to come up with this idea. So what is your problem? I am pretty sure other people have come up with the same idea before us.

I used this technique all the way back to 2007, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

Let's see if he returns... He did!

>> So what is your problem? <<

Nothing

Thanks for all replies; maybe we have some competitions in future, maybe.

Also I like you but you do not attend it. Your behavior with me is not friendly. Not any meeting…

Regards

//Peso

Simple function to get beginning or end of month

Peter Larsson — Thu, 14 Apr 2011 11:40:28 GMT

CREATE FUNCTION dbo.fnIsOnMonthEdge
(
    @theDate DATETIME
)
RETURNS SMALLINT
AS
BEGIN
    RETURN CASE @theDate
                WHEN '99991231' THEN 1
                ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate))
            END +
            CASE @theDate
                WHEN '17530101' THEN -1
                ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate))
            END
END