|
|
Posted by Hugo Kornelis on 10/02/14 11:56
On Wed, 23 Aug 2006 21:51:18 +0000 (UTC), Erland Sommarskog wrote:
>Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
>> On 23 Aug 2006 12:14:02 -0700, mutemode@gmail.com wrote:
>>
>> (snip)
>>>AND
>>>
>>>I need to get the numbers 1-1000 into a table called #thousand using
>>>the identity function. Help?
>>
>> Hi mutemode,
>>
>> SELECT TOP 1000 IDENTITY(int, 1,1) AS id
>> INTO #ten
>> FROM sysobjects AS a, sysobjects AS b
>
>IF (SELECT COUNT(*) FROM #ten) < 1000 OR
> (SELECT MIN(id) FROM #ten) <> 1 OR
> (SELECT MAX(id) FROM #ten) <> 1000
>BEGIN
> RAISERROR ('Fill of #ten failed!', 16, 1)
> RETURN 1
>END
>
>That is, I don't think one should trust the code above to always return
>what you looking for. Adding some paranoia can avoid incorrect results.
Hi Erland,
Some paranoia is good, but too much is, well, too much <g>
I agree with the test for a COUNT(*) of less than 1000 (though even in
an empty database, sysobjects has 47 rows so the cross join should be
good 2209 rows).
The tests for MIN and MAX remind me of the examples of "defensive
programming" I have seen when I still programmed PL/I on a mainframe. In
T-SQL equivalent, the code read something like this:
SET @SomeVariable = 15;
IF @SomeVariable <> 15
BEGIN;
RAISERROR ('The DBMS has a bug!', 16, 1);
END;
Assuming that the IDENTITY function works as advertised, you'll never be
able to get a situation with MIN(id) other than 1 and MAX(id) other than
1000 (assuming the COUNT(*) check is passed).
Finally, the COUNT(*) check can be replaced by a much more efficient
check for @@ROWCOUNT. The end result would be (correcting my error in
the requested table name while I'm at it:
SELECT TOP 1000 IDENTITY(int, 1,1) AS id
INTO #thousand
FROM sysobjects AS a, sysobjects AS b;
IF @@ROWCOUNT < 1000
BEGIN;
RAISERROR ('Fill of #thousand failed - not enough rows in sysobjects!
Please add an extra occurence of sysobejcts to the FROM clause.', 16,
1);
ROLLBACK TRANSACTION;
END;
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|