INSERT not always going to bottom 2

[influent]

I have a stored procedure that uses the INSERT command to add a row to a table in SQL Server 7. Most of the time it adds the new row to the bottom of the table, but sometimes it doesn't. Does anybody know why?

CREATE PROCEDURE spTest
@Item varchar(15),
@Descrip varchar(30),
@Market varchar(20)
AS
INSERT tblTest (ItemNo,Description,MktNo)
VALUES (@Item,@Descrip,@Market)

 

[chiph]

How are you checking that the INSERT statement inserts at the end of the table? If you're using a SELECT, recall that there is no requirement that SELECT returns rows in key order....unless you put an ORDER BY clause on the end.

If, on the other hand, you expect your ItemNo to always be increasing, that won't happen unless you declare the column to be an Identity datatype (comes across as an int). The database will then track the highest number in the table, and assign the next-highest number for you during an INSERT.

Chip H.

 

[fluteplr]

In sql server there really is no concept of the bottom or top of a table.

There is just a table and the table has records.

SQL2000 will behave diferently than 7 and 6.5 was different than both of them.



If you need to know the order in which the records are inserted you either need an identity field or a datetime field with a default value of getdate().

 

[pheiner]

Also check for a clustered index on the table.

 

[mythili]

U cannot say that always the data you insert will be added to the bottom of the table.If u need the data to be added at the end of the table then u should always increment the index no or the primary key for the table.also the primary key should be numeric.If alpha numeric again u cannot be sure that the row will get inserted at the bottom

 

[influent]

Thanks for all the helpful responses. I was just looking at the table in EM since there are only about 20 records so far, and I was double checking the data. It doesn't matter to the application at all what order the rows are in, I was just curious for future reference why new rows would show up near the top sometimes. It sounds like it's random, or at least random to somebody like me who doesn't understand databases at the lowest level.