SQL Server, not inserting records in correct position after deleting one or more records

Question

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

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, etc.

Answer 1

The order that rows are returned from a select statement is arbitrary, unless you use an 'order by' clause.

In practice, the order will be determined by the particular implementation that your DBMS uses, but you should not assume that the order is stable.