3 added 950 characters in body
source | link

ThisA second, perhaps more instructive example, using the OrderDetails table from above, where we're not joining any other tables, but have a simple requirement to find rows matching both the OrderID and the UserID, we see the problem.

We'll create an index to support the query, as you would likely do in real life if performance is in any way important (when isn't it?).

CREATE INDEX OrderDetailsOrderIDUserID ON dbo.OrderDetails(OrderID, UserID);

Here's the query:

SELECT od.OrderDetailsID
FROM dbo.OrderDetails od
WHERE od.OrderID = 15
    AND (od.UserID = 21 OR od.UserID = 22)

And the results:


╔════════════════╗
║ OrderDetailsID ║
╠════════════════╣
║          21421 ║
║        5061421 ║
║        7091421 ║
║         691422 ║
║        3471422 ║
║        7241422 ║
╚════════════════╝

Adding an ORDER BY clause will most definitely ensure we get the correct sort here, too.

These mock-up isups are just a single examplesimple examples where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.

This mock-up is just a single example where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.

A second, perhaps more instructive example, using the OrderDetails table from above, where we're not joining any other tables, but have a simple requirement to find rows matching both the OrderID and the UserID, we see the problem.

We'll create an index to support the query, as you would likely do in real life if performance is in any way important (when isn't it?).

CREATE INDEX OrderDetailsOrderIDUserID ON dbo.OrderDetails(OrderID, UserID);

Here's the query:

SELECT od.OrderDetailsID
FROM dbo.OrderDetails od
WHERE od.OrderID = 15
    AND (od.UserID = 21 OR od.UserID = 22)

And the results:


╔════════════════╗
║ OrderDetailsID ║
╠════════════════╣
║          21421 ║
║        5061421 ║
║        7091421 ║
║         691422 ║
║        3471422 ║
║        7241422 ║
╚════════════════╝

Adding an ORDER BY clause will most definitely ensure we get the correct sort here, too.

These mock-ups are just simple examples where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.

2 added 1230 characters in body
source | link

Adding an explicit ORDER BY ensures rows will be returned to the client in the desired order:

SELECT od.OrderDetailsID
    , o.OrderID
    , u.UserID
FROM dbo.OrderDetails od
    INNER JOIN dbo.Users u ON u.UserID = od.UserID
    INNER JOIN dbo.Orders o ON od.OrderID = o.OrderID
WHERE u.UserID = 15
ORDER BY od.OrderDetailsID;
╔════════════════╦═════════╦════════╗
║ OrderDetailsID ║ OrderID ║ UserID ║
╠════════════════╬═════════╬════════╣
║           3915 ║      40 ║     15 ║
║         100715 ║       8 ║     15 ║
║         221815 ║      19 ║     15 ║
║         299915 ║     100 ║     15 ║
║         368215 ║      83 ║     15 ║
║         603815 ║      39 ║     15 ║
║         630215 ║       3 ║     15 ║
║         728515 ║      86 ║     15 ║
║         972215 ║      23 ║     15 ║
║         992015 ║      21 ║     15 ║
║        1017115 ║      72 ║     15 ║
║        1113815 ║      39 ║     15 ║
╚════════════════╩═════════╩════════╝

If order of rows is imperative, and your engineers know that order is imperative, they should only ever want to use an ORDER BY statement, since it might cost them their designation if there was a failure related to incorrect order.

This mock-up is just a single example where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.

This mock-up is just a single example where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.

Adding an explicit ORDER BY ensures rows will be returned to the client in the desired order:

SELECT od.OrderDetailsID
    , o.OrderID
    , u.UserID
FROM dbo.OrderDetails od
    INNER JOIN dbo.Users u ON u.UserID = od.UserID
    INNER JOIN dbo.Orders o ON od.OrderID = o.OrderID
WHERE u.UserID = 15
ORDER BY od.OrderDetailsID;
╔════════════════╦═════════╦════════╗
║ OrderDetailsID ║ OrderID ║ UserID ║
╠════════════════╬═════════╬════════╣
║           3915 ║      40 ║     15 ║
║         100715 ║       8 ║     15 ║
║         221815 ║      19 ║     15 ║
║         299915 ║     100 ║     15 ║
║         368215 ║      83 ║     15 ║
║         603815 ║      39 ║     15 ║
║         630215 ║       3 ║     15 ║
║         728515 ║      86 ║     15 ║
║         972215 ║      23 ║     15 ║
║         992015 ║      21 ║     15 ║
║        1017115 ║      72 ║     15 ║
║        1113815 ║      39 ║     15 ║
╚════════════════╩═════════╩════════╝

If order of rows is imperative, and your engineers know that order is imperative, they should only ever want to use an ORDER BY statement, since it might cost them their designation if there was a failure related to incorrect order.

This mock-up is just a single example where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.

1
source | link

Consider the following example, where we have three related tables. Orders, Users, and OrderDetails. OrderDetails is linked with foreign keys to the Orders table and the Users Table. This is essentially a very typical setup for relational databases; arguably the entire purpose of a relational DBMS.

USE tempdb;

IF OBJECT_ID(N'dbo.OrderDetails', N'U') IS NOT NULL
DROP TABLE dbo.OrderDetails;

IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL
DROP TABLE dbo.Orders;

IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;

CREATE TABLE dbo.Orders
(
    OrderID int NOT NULL
        CONSTRAINT OrderTestPK
        PRIMARY KEY
        CLUSTERED
    , SomeOrderData varchar(1000)
        CONSTRAINT Orders_somedata_df
        DEFAULT (CRYPT_GEN_RANDOM(1000))
);

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT UsersPK
        PRIMARY KEY
        CLUSTERED
    , SomeUserData varchar(1000)
        CONSTRAINT Users_somedata_df
        DEFAULT (CRYPT_GEN_RANDOM(1000))
);

CREATE TABLE dbo.OrderDetails
(
    OrderDetailsID int NOT NULL
        CONSTRAINT OrderDetailsTestPK
        PRIMARY KEY
        CLUSTERED
    , OrderID int NOT NULL
        CONSTRAINT OrderDetailsOrderID
        FOREIGN KEY
        REFERENCES dbo.Orders(OrderID)
    , UserID int NOT NULL
        CONSTRAINT OrderDetailsUserID
        FOREIGN KEY
        REFERENCES dbo.Users(UserID)
    , SomeOrderDetailsData varchar(1000)
        CONSTRAINT OrderDetails_somedata_df
        DEFAULT (CRYPT_GEN_RANDOM(1000))
);

INSERT INTO dbo.Orders (OrderID)
SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc;

INSERT INTO dbo.Users (UserID)
SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.syscolumns sc;

INSERT INTO dbo.OrderDetails (OrderDetailsID, OrderID, UserID)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    , o.OrderID
    , u.UserID
FROM sys.syscolumns sc
    CROSS JOIN dbo.Orders o
    CROSS JOIN dbo.Users u
ORDER BY NEWID();

CREATE INDEX OrderDetailsOrderID ON dbo.OrderDetails(OrderID);
CREATE INDEX OrderDetailsUserID ON dbo.OrderDetails(UserID);

Here, we're querying the OrderDetails table where the UserID is 15:

SELECT od.OrderDetailsID
    , o.OrderID
    , u.UserID
FROM dbo.OrderDetails od
    INNER JOIN dbo.Users u ON u.UserID = od.UserID
    INNER JOIN dbo.Orders o ON od.OrderID = o.OrderID
WHERE u.UserID = 15

The output from the query looks like:

╔════════════════╦═════════╦════════╗
║ OrderDetailsID ║ OrderID ║ UserID ║
╠════════════════╬═════════╬════════╣
║        2200115 ║       2 ║     15 ║
║         630215 ║       3 ║     15 ║
║        1990215 ║       3 ║     15 ║
║        4960215 ║       3 ║     15 ║
║         100715 ║       8 ║     15 ║
║        3930815 ║       9 ║     15 ║
║        6310815 ║       9 ║     15 ║
║        4441015 ║      11 ║     15 ║
║        2171315 ║      14 ║     15 ║
║        3431415 ║      15 ║     15 ║
║        4571415 ║      15 ║     15 ║
║        6421515 ║      16 ║     15 ║
║        2271715 ║      18 ║     15 ║
║        2601715 ║      18 ║     15 ║
║        3521715 ║      18 ║     15 ║
║         221815 ║      19 ║     15 ║
║        3381915 ║      20 ║     15 ║
║        4471915 ║      20 ║     15 ║
╚════════════════╩═════════╩════════╝

As you can see, the order of rows output does not match the order of rows in the OrderDetails table.

This mock-up is just a single example where rows are not guaranteed to be "in order" without an explicit ORDER BY statement. There are many more example like this, and since DBMS engine code changes quite frequently, the specific behavior may change over time.