question

$$ANON_USER$$ avatar image
0 Votes"
$$ANON_USER$$ asked Criszhan-msft edited

SQL Auto Number Generate

the application should generate a unique 16-digit order number (includes the type of delivery (1 digit) that the user has opted for, Product id (7 digits), and the order number (8 digits)) as soon as the order is placed by the customer.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And with which part of this requirement do you have issues? Should it be a numeric or a varchar "number"? A order can have only one product, not more? I ask because the product id should be part of the unique ID.
Can you post some samples?

0 Votes 0 ·

Can you post some samples?

I would not think so. I mean this smells like an assignment/interview question, with no real-world data to help with the task.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 edited

The way to do what you describe is to create 3 fields, delivery, product id, and order number. Then create a computed field which concatenates the values into a single string.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

Hi,

Because this 16-digit order number is completely generated by the combination of the contents of the other three columns, whether it is unique depends on the definition of other columns, and at least one column needs to be defined as unique.

You can consider the way to set up a computed column, whether to add a computed column when creating a table or add a computed column to an existing table. But the specific implementation needs to be based on your table definition, you did not provide it.
The order number is usually unique and unchangeable., it is recommended to make the computed column persisted without having to recalculate each time it is accessed.

Or consider creating a view based on this table and generating the new order number column.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.