Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I saw the above 'ANSI warning' message today when running a colleague's script (and I don't know which of the many statements caused the warning to be shown).

In the past I've ignored it: I avoid nulls myself and so anything that would eliminate them is a good thing in my book! However, today the word 'SET' literally shouted out at me and I realised I don't know what the meaning of the word is supposed to be in this context.

My first thought, based on the fact it is upper case, is that it is referring to the SET keyword and means 'assignment', as in

UPDATE <table> SET ...

...ON DELETE SET NULL...

SET IDENTITY_INSERT <table> ON

According to the SQL Server Help, the 'ANSI warnings' feature is based on ISO/ANSI SQL-92, the spec for which makes just one use of the term 'Set operation' in a subsection title, hence in title case, in the data assignment section. However, after a quick Googling of the error message I see examples that are SELECT queries with seemingly no assignment involved.

My second thought, based on the wording of the SQL Server warning, was that the mathematical meaning of set is implied. However, I don't think that aggregation in SQL is strictly speaking a set operation. Even if the SQL Server team consider it to be a set operation, what is the purpose of putting the word 'set' in capitals?

While Googling I noticed a SQL Server error message:

Table 'T' does not have the identity property. Cannot perform SET operation.

The same words 'SET operation' in the same case here can only refer to the assignment of the IDENTITY_INSERT property, which brings me back to my first thought.

Can anyone shed any light on the matter?

share|improve this question
    
I always assumed it meant operation on a set of rows. –  Martin Smith Jan 19 '12 at 14:35
    
@MartinSmith I don't think so since SET is always in full caps like a keyword –  JNK Jan 19 '12 at 14:38
    
@JNK - Yes on thinking about it I assume there must be some other non aggregate operation that raises that warning so I guess if we find out what that is it might explain it! –  Martin Smith Jan 19 '12 at 14:40
    
SELECT * FROM sys.messages WHERE text LIKE '%SET operation%' gives 3 other results that all seem to indicate SET keyword as well. –  Martin Smith Jan 19 '12 at 14:47
    
I did some more searching and it may actually be referring to set operations as in mathematical sets. I found a lot of references in SS 2k docs to set operations referring to UNION and INTERSECT and EXCEPT, but I can't get the error to come up with NULL in any of those circumstances. I think it may be a legacy error message. –  JNK Jan 19 '12 at 14:59

3 Answers 3

up vote 9 down vote accepted

I was just looking through the SQL-92 Specification and saw a passage that reminded me of this question.

There is in fact a prescribed warning for this situation as indicated below

b) Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function.

I assume that the SET in the SQL Server Error Message is a reference to the set function of that error message although I'm not sure why it would make a distinction between aggregates and other set functions, as far as I can see they are synonymous. The relevant bit of the grammar is below.

6.5  <set function specification>

         Function

         Specify a value derived by the application of a function to an
         argument.

         Format

         <set function specification> ::=
                COUNT <left paren> <asterisk> <right paren>
              | <general set function>

         <general set function> ::=
                <set function type>
                    <left paren> [ <set quantifier> ] <value expression> <right paren>


         <set function type> ::=
              AVG | MAX | MIN | SUM | COUNT

         <set quantifier> ::= DISTINCT | ALL
share|improve this answer

Quick answer

The "other SET* is probably related to older SQL Server versions.

I used to see it more back when I worked with SQL Server 6.5 and 7 I'm sure, but it's been some time. Many quirks have been ironed out + SQL Server follows standards more

Longer:

Nowadays, the message is controlled by SET ANSI_WARNINGS which defaults to ON.
This relates purely to whether

  • a warning is generated by a NULL value in an aggregate.
  • silent truncation occurs on insert/update for varchar type fields

One example:

DECLARE @foo TABLE (bar int NULL);
INSERT @foo VALUES (1), (2), (NULL);

SET ANSI_WARNINGS ON;
SELECT SUM(bar) FROM @foo;
SET ANSI_WARNINGS OFF;

SELECT SUM(bar) FROM @foo;

gives

(3 row(s) affected)
---- -----------
ON   3
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
---- -----------
OFF  3
(1 row(s) affected)

Another example:

DECLARE @foo TABLE (bar varchar(5) NULL);
SET ANSI_WARNINGS ON;
INSERT @foo VALUES ('123456'); -- error
GO
DECLARE @foo TABLE (bar varchar(5) NULL);
SET ANSI_WARNINGS OFF;
INSERT @foo VALUES ('123456'); -- OK
GO

Personally, I ignore the warning and leave SET ANSI_WARNINGS ON because of the other consequences to computed columns and indexed views of setting it OFF.

Finally, there could be a trigger or computed column or indexed view generating this warning somewhere

share|improve this answer
    
The phrasing of the error message definitely implies that there is (or was) some non aggregate operation that could also eliminate NULLs to me. –  Martin Smith Jan 19 '12 at 15:29
    
@Martin Smith: agree. It could be indirect though as per my (new) last statement. Or some optimiser strategy that would be seen in the plan –  gbn Jan 19 '12 at 15:32
    
Shame that the docs for anything older than SQL Server 7.0 don't seem to be online anywhere. –  Martin Smith Jan 19 '12 at 15:34
1  
example (hard to find stuff this old): kbalertz.com/Feedback.aspx?kbNumber=149921/EN-US –  gbn Jan 19 '12 at 15:51
1  
Although actually it looks as though the SQL Server 7.0 version of the message was just Warning: Null value eliminated from aggregate.. The SET operation bit wasn't added until 2000. –  Martin Smith Jan 20 '12 at 10:12

The other side of the warning refers to 'set' operations not 'SET' operations - that looks like a message bug to me - for example it is also produced with windowing functions:

select max(foo) over() as max_foo from (values (1), (2), (null)) as t(foo);
/*
max_foo
-------
2
2
2

Warning: Null value is eliminated by an aggregate or other SET operation.
*/
share|improve this answer
1  
Seems like it's still referring to the aggregation part though MAX() –  JNK Jan 19 '12 at 15:44
    
Is that called the 'aggregation part'? I know what you mean of course, but they are different operations - eg compare select max(foo) from (values (1), (2), (null)) as t(foo) where 1=2; versus select max(foo) over() from (values (1), (2), (null)) as t(foo) where 1=2; –  Jack Douglas Jan 19 '12 at 15:50
1  
It's the aggregate function :) I believe the error is referring to MAX(). I think you second example has to do more with order of operations using window functions. –  JNK Jan 19 '12 at 15:54
    
ok - see what you mean from the SS docs –  Jack Douglas Jan 19 '12 at 16:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.