INSERT-ing multiple rows

Question

I have the following sql code. Is it guaranteed that MyTable is going to be sorted by MyTable.data? Basically the question is - if I am inserting multiple rows with one INSERT statement, can other connection get in the middle of my insertion and insert something else in between my rows?

CREATE TABLE MyTable(
    id bigint IDENTITY(1,1) NOT NULL,
    data uniqueidentifier NOT NULL,
        CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED (id ASC)
)

DECLARE @data uniqueidentifier
SET @data = NEWID()

WITH Dummy AS
(
    SELECT @data as data, 1 as n
    UNION ALL
    SELECT @data, n + 1
    FROM Dummy
    WHERE n < 100
)
INSERT INTO MyTable(data)
    SELECT data FROM Dummy

Thanks.

Answer 1

Forgive me if I misunderstand your question. But you should never depend on primary keys to order data, if that is what you're after. That is not what primary keys are for. Ordering of data should be done with an ORDER BY clause, or with columns that are specifically introduced by you to keep track of order. Primary key columns are for identifying data, not ordering.

Answer 2

You have no guarantee that those rows will be inserted consecutively - unless you put your SQL Server database into single-user mode and make sure no one else is connected.

But the question is: why is this even important to you?? In SQL Server, you should never rely on a "system-given" order - if you need order, use an explicit ORDER BY - that's the only reliable way to get your rows in an ordered fashion anyway.

Answer 3

By definition, SQL tables have no defined order. But I don't think this is what you are asking. I think you are asking whether another process could insert a row in between one of your inserts. The answer is yes, unless you have the entire table locked, which you probably do not want to do for concurrency reasons.