SQL Server, not inserting records in correct position after deleting records

[el_vala]

I know, is a very basic question, but I am in the process of re-learning sql stored procedures, and this is what happended:

I was practicing with some simple delete / insert routines, when encountered with this: if I ONLY insert records:

1. dog
2. cat
3. t-rex
4. llama

all is ok, then I call a delete procedure, passing the colum_ID to identify the record and delete it.

This is done correctly and all is OK

Then I call a insert procedure once again and the insert happens like this:

1. dog
2. cat
5. snake **now this is wrong, right?**
4. llama
.. should be here...

And of i delete lets say records 2 and 3, and call the insert procedure for 2 new records, ir will "fill out" those positions and the third record will be at the bottom, where it should be.

The stored procedures are very basic: Insert

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertData]
@name varchar(50),
@lastname varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    insert into dbo.names (name,last_name)
    values(@name,@lastname)
    select * from dbo.names
END

Delete

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeleteData]

    @id_record int
AS
BEGIN

    SET NOCOUNT ON;

    delete from dbo.names where id_record = @id_record
    SELECT * from dbo.names
END

So, what is causing this behavior, the table is very basic, but is compliant with Pkeys, not nulls, indexes, identity

I am using SQL server 2008.

[tesuji]

Hi

don't worry, rows in database tables never have a particular order. This is a general rule for relational databases are based on set theory (at least as regard that). So if you want to get a certain order, you must make explicitly use of order function by ORDER BY clause, for example:

SELECT * FROM dbo.names ORDER BY id_record ;
/* result (assumed that there exist a column id_record) 
1. dog
2. cat
4. llama
5. snake **now this is wrong, right?**
*/

.
If you don't have column id_record to order your rows, you can also use the lastname und name columns to get alphabetical order, for example:

SELECT * FROM dbo.names ORDER BY lastname asc, name desc;

Where ASC stands for ascending order and DESC for descending order.

Or you add a further column id_record to your table and you nominate id_record to be primary key of this table:

create table names(id_record integer not null, prename varchar(50), surname varchar(50),
primary key (id_record));

-- tesu

[el_vala]

Nice, thanks a lot for the info, that clears it for me.