I don't know if this will add clarity or confusion:
CREATE TABLE #foo (Sequence int)
SELECT * FROM #foo;select @@Rowcount
--the results you are seeing
SELECT MAX(ISNULL([Sequence],0))+1 FROM #foo
SELECT ISNULL(MAX([Sequence]),0)+1 FROM #foo
--lets look at the functions:
SELECT ISNULL([Sequence],0) FROM #foo
SELECT MAX([Sequence]) FROM #foo
the first returns nothing because there is no data to return
the second returns NULL
so, when you wrap those functions with MAX and ISNULL (as you have done
within your question) the first returns NULL (because it seems that MAX
returns a NULL even when there is no data) and the second performs the
ISNULL check on the [NULL] data that was returned from the MAX.
Did that statement make any sense?
Lets look at it again. This statement
SELECT ISNULL([Sequence],0) FROM #foo
returns nothing (as it probably should) because there is not any data in the
table
The statement:
SELECT MAX([Sequence]) FROM #foo
returns NULL
So, when you wrap the first statement with MAX() and the second statement
with ISNULL() you end up with NULL and whatever the result of the ISNULL
function does
now, lets try inserting one null record
INSERT INTO #foo VALUES (NULL)
SELECT * FROM #foo
now lets look at the results
SELECT MAX(ISNULL([Sequence],0))+1 FROM #foo
SELECT ISNULL(MAX([Sequence]),0)+1 FROM #foo
of course if we throw a row in the table they should both work the same:
INSERT INTO #foo VALUES (200)
SELECT MAX(ISNULL([Sequence],0))+1 FROM #foo
SELECT ISNULL(MAX([Sequence]),0)+1 FROM #foo
--
Keith
Post by balkiihi guys
just a small doubt
why does MAX(ISNULL([Sequence],0))+1 returns no row whereas
ISNULL(MAX([Sequence]),0)+1 returns 1.
would appreciate if someone can clarify the above eventhough it is a
silly question
thanx in advance
bala