Why is SQL flagging this as an error/ Incorrect Syntax near 'Order'?

Question

Sometimes SQL Server gives me random errors like this. I'm no pro with SQL but I'm pretty sure there is nothing wrong with this code...

Incorrect syntax near 'Order'

CREATE TABLE Order
(
    OrderNumber INTEGER PRIMARY KEY,
    OrderDate DATE,
    CustomerNumber INTEGER,
    CustomerPONumber INTEGER,
    OrderTotal FLOAT,
    OrderStatus VARCHAR(10),
    OrderSpecialCharges FLOAT,
    OrderSpecialChargeDescription VARCHAR(50),
    ShipName VARCHAR (20),
    ShipAddress1 VARCHAR (10),
    ShipAddress2 VARCHAR (10),
    ShipCity VARCHAR (10),
    ShipState CHAR (2),
    ShipZip INTEGER,
    FOREIGN KEY (CustomerNumber) REFERENCES Customer
);

Because ORDER is a reserved word in sql. Use a different name or you will be fighting that decision forever. Something like OrderHead would be a better choice. — Sean Lange, May 09 '18 at 18:36
And don't use the float datatype for storing things like OrderTotal. Use an exact datatype like numeric. — Sean Lange, May 09 '18 at 18:37
No kidding. That would be nice if the intellisense told me that instead! Thanks guys. — Michael M, May 09 '18 at 18:38
Intellisense can't figure out what you are trying to do because it sees a totally nonsensical statement. Humans can see that and understand it easily enough but the words in that sequence are not valid so it chokes. — Sean Lange, May 09 '18 at 18:42
IMHO a table name should describe the set, not an instance from the set. `Orders` makes more sense than `Order`. — Aaron Bertrand, May 09 '18 at 19:00
Bigger issue here as @SeanLange mentioned is using float for money columns though, imo. — Jacob H, May 09 '18 at 19:00
@JacobH Sure, but the question is about the syntax error, not a full code/design review. — Aaron Bertrand, May 14 '18 at 16:27

Lukasz Szozda · Answer 1 · 2018-05-10T13:59:56.893

6

You need to use []:

CREATE TABLE [Order]
(
OrderNumber INTEGER PRIMARY KEY,
OrderDate DATE,
CustomerNumber INTEGER,
CustomerPONumber INTEGER,
OrderTotal FLOAT,
OrderStatus VARCHAR(10),
OrderSpecialCharges FLOAT,
OrderSpecialChargeDescription VARCHAR(50),
ShipName VARCHAR (20),
ShipAddress1 VARCHAR (10),
ShipAddress2 VARCHAR (10),
ShipCity VARCHAR (10),
ShipState CHAR (2),
ShipZip INTEGER,
FOREIGN KEY (CustomerNumber) REFERENCES Customer
);

You should avoid reserved keywords as identifiers. I suggest to rename to for example Orders.

EDIT:

As others mentioned in comments you should avoid approximate number data types (FLOAT/REAL/DOUBLE) and use accurate data types(MONEY/DECIMAL) instead. This will save your time in the future. For example:

Avg of float inconsistency

edited May 10 '18 at 13:59

answered May 09 '18 at 18:36

Lukasz Szozda

162,964
23
234
275

2

Nice catch, while the table will hold more than 1 order it's better to name it `Orders` rather than `Order` :). – Ilyes May 09 '18 at 19:11
I disagree, I would not name a table with a plural; I would also not name a table with a reserved keyword either. I would browse a dictionary and re-think until I found something that fits both rules, and illustrates the table usage as well. – Random_User May 09 '18 at 20:51
1

@Random_User I suggest that name as an example. OP should change the name according to own naming standards.Anyway I won't start another academic debate like [Table Naming Dilemma: Singular vs. Plural Names](https://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names) – Lukasz Szozda May 10 '18 at 13:54

Why is SQL flagging this as an error/ Incorrect Syntax near 'Order'?

1 Answers1