Mixing
What is the best way to insert invoice number (without any gap) into a table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am working on a sales project using SQL Server (versions 2008 and 2012). I need to insert sales data into a table and create an invoice number for that data. I have done this by setting my invoiceNumber column as an identity column which increments by one.
But now, we have a requirement that our invoice number should not have a gap between them (unless the invoice is deleted. The deleted invoice will be stored in separate tables).
Identity columns will lose invoice numbers when a transaction gets failed. From SQL Server 2012, it may increment by 1000 on server restarts. To get rid of this issue I am forced to use INSERT SELECT statements as shown below
INSERT INTO SalesTable(InvoiceNumber, col1, col2)
VALUES ((SELECT MAX(SalesId) FROM SalesTable + 1), value1, value2)
This may work fine if my software works on a single machine.
My question is if i use this code in my software where multiple users create sales invoice at the same time, is there a chance for two invoice to get same invoice number? I mean a kind of invoice number duplication?
Will using a lock on the table before inserting the data prevent the issue? if yes how to do it? What are the issues associated with using locks?
I have found similar questions, but I didn't find an answer to resolve this issue in a best way.
Any help on this will be greatly appreciated. Thank you.
sql
sql-server database sql-server-2008 edited Jan 3 at 10:42
marc_s
584k13011241271
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
add a comment |
I am working on a sales project using SQL Server (versions 2008 and 2012). I need to insert sales data into a table and create an invoice number for that data. I have done this by setting my invoiceNumber column as an identity column which increments by one.
But now, we have a requirement that our invoice number should not have a gap between them (unless the invoice is deleted. The deleted invoice will be stored in separate tables).
Identity columns will lose invoice numbers when a transaction gets failed. From SQL Server 2012, it may increment by 1000 on server restarts. To get rid of this issue I am forced to use INSERT SELECT statements as shown below
INSERT INTO SalesTable(InvoiceNumber, col1, col2)
VALUES ((SELECT MAX(SalesId) FROM SalesTable + 1), value1, value2)
This may work fine if my software works on a single machine.
My question is if i use this code in my software where multiple users create sales invoice at the same time, is there a chance for two invoice to get same invoice number? I mean a kind of invoice number duplication?
Will using a lock on the table before inserting the data prevent the issue? if yes how to do it? What are the issues associated with using locks?
I have found similar questions, but I didn't find an answer to resolve this issue in a best way.
Any help on this will be greatly appreciated. Thank you.
sql
sql-server database sql-server-2008 edited Jan 3 at 10:42
marc_s
584k13011241271
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
5
Do you have to not have gaps? What are you expecting to happen if you create 2 invoices, and then deleted the first? Would the second, and any latter invoices, have their number lowered? That could easily introduce inconsistencies in your data if you then don't update any relevant foreign keys,
– Larnu
Jan 3 at 8:56
1
It is my country rule that invoice number should not have any gaps between them. When deleting an invoice,we are supposed to create a record of that deleted invoice in my database. So that even though there is a gap, we will able to convince the authorities that it is entered previously and deleted because of certain issues. What i mean is I should not have an unarguable gap in the invoice number.
– Adeeb Arangodan
Jan 3 at 8:59
2
TheINSERTstatement you have would most definitely suffer from what's know as "race conditions", and could end up with 2 invoices with the same number. Note that something like anIDENTITYproperty won't allow 2 user's to get the same invoice number, however, what it does not guarantee is sequential numbers, or that every number will be used. Numbers would be "lost" in the event of a failedINSERT,DELETE,ROLLBACK, and even possibly an (unplanned) shutdown/restart.
– Larnu
Jan 3 at 8:59
Honestly, I feel like ensuring that every invoice's number is sequential proves nothing to any authority. Numbers like these can easily be amended retrospectively. If the number was "missing", someone inclined could easily add it to the "deleted" table. or move the numbers around if they were out of order.
– Larnu
Jan 3 at 9:02
2
Please do yourself a favour and never delete anything from the invoice database table. Add adeletedflag (and deletion date maybe), but don't delete the invoice row. Your auditors will also want to see which invoices were cancelled/retracted/etc.
– Corion
Jan 3 at 9:40
add a comment |
I am working on a sales project using SQL Server (versions 2008 and 2012). I need to insert sales data into a table and create an invoice number for that data. I have done this by setting my invoiceNumber column as an identity column which increments by one.
But now, we have a requirement that our invoice number should not have a gap between them (unless the invoice is deleted. The deleted invoice will be stored in separate tables).
Identity columns will lose invoice numbers when a transaction gets failed. From SQL Server 2012, it may increment by 1000 on server restarts. To get rid of this issue I am forced to use INSERT SELECT statements as shown below
INSERT INTO SalesTable(InvoiceNumber, col1, col2)
VALUES ((SELECT MAX(SalesId) FROM SalesTable + 1), value1, value2)
This may work fine if my software works on a single machine.
My question is if i use this code in my software where multiple users create sales invoice at the same time, is there a chance for two invoice to get same invoice number? I mean a kind of invoice number duplication?
Will using a lock on the table before inserting the data prevent the issue? if yes how to do it? What are the issues associated with using locks?
I have found similar questions, but I didn't find an answer to resolve this issue in a best way.
Any help on this will be greatly appreciated. Thank you.
sql
sql-server database sql-server-2008 edited Jan 3 at 10:42
marc_s
584k13011241271
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
I am working on a sales project using SQL Server (versions 2008 and 2012). I need to insert sales data into a table and create an invoice number for that data. I have done this by setting my invoiceNumber column as an identity column which increments by one.
But now, we have a requirement that our invoice number should not have a gap between them (unless the invoice is deleted. The deleted invoice will be stored in separate tables).
Identity columns will lose invoice numbers when a transaction gets failed. From SQL Server 2012, it may increment by 1000 on server restarts. To get rid of this issue I am forced to use INSERT SELECT statements as shown below
INSERT INTO SalesTable(InvoiceNumber, col1, col2)
VALUES ((SELECT MAX(SalesId) FROM SalesTable + 1), value1, value2)
This may work fine if my software works on a single machine.
My question is if i use this code in my software where multiple users create sales invoice at the same time, is there a chance for two invoice to get same invoice number? I mean a kind of invoice number duplication?
Will using a lock on the table before inserting the data prevent the issue? if yes how to do it? What are the issues associated with using locks?
I have found similar questions, but I didn't find an answer to resolve this issue in a best way.
Any help on this will be greatly appreciated. Thank you.
sql
sql-server database sql-server-2008 sql
sql-server database sql-server-2008 edited Jan 3 at 10:42
marc_s
584k13011241271
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
edited Jan 3 at 10:42
marc_s
584k13011241271
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
edited Jan 3 at 10:42
marc_s
584k13011241271
edited Jan 3 at 10:42
marc_s
584k13011241271
edited Jan 3 at 10:42
marc_s
584k13011241271
584k13011241271
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
asked Jan 3 at 8:54
Adeeb ArangodanAdeeb Arangodan
798
798
5
Do you have to not have gaps? What are you expecting to happen if you create 2 invoices, and then deleted the first? Would the second, and any latter invoices, have their number lowered? That could easily introduce inconsistencies in your data if you then don't update any relevant foreign keys,
– Larnu
Jan 3 at 8:56
1
It is my country rule that invoice number should not have any gaps between them. When deleting an invoice,we are supposed to create a record of that deleted invoice in my database. So that even though there is a gap, we will able to convince the authorities that it is entered previously and deleted because of certain issues. What i mean is I should not have an unarguable gap in the invoice number.
– Adeeb Arangodan
Jan 3 at 8:59
2
TheINSERTstatement you have would most definitely suffer from what's know as "race conditions", and could end up with 2 invoices with the same number. Note that something like anIDENTITYproperty won't allow 2 user's to get the same invoice number, however, what it does not guarantee is sequential numbers, or that every number will be used. Numbers would be "lost" in the event of a failedINSERT,DELETE,ROLLBACK, and even possibly an (unplanned) shutdown/restart.
– Larnu
Jan 3 at 8:59
Honestly, I feel like ensuring that every invoice's number is sequential proves nothing to any authority. Numbers like these can easily be amended retrospectively. If the number was "missing", someone inclined could easily add it to the "deleted" table. or move the numbers around if they were out of order.
– Larnu
Jan 3 at 9:02
2
Please do yourself a favour and never delete anything from the invoice database table. Add adeletedflag (and deletion date maybe), but don't delete the invoice row. Your auditors will also want to see which invoices were cancelled/retracted/etc.
– Corion
Jan 3 at 9:40
add a comment |
5
Do you have to not have gaps? What are you expecting to happen if you create 2 invoices, and then deleted the first? Would the second, and any latter invoices, have their number lowered? That could easily introduce inconsistencies in your data if you then don't update any relevant foreign keys,
– Larnu
Jan 3 at 8:56
1
It is my country rule that invoice number should not have any gaps between them. When deleting an invoice,we are supposed to create a record of that deleted invoice in my database. So that even though there is a gap, we will able to convince the authorities that it is entered previously and deleted because of certain issues. What i mean is I should not have an unarguable gap in the invoice number.
– Adeeb Arangodan
Jan 3 at 8:59
2
TheINSERTstatement you have would most definitely suffer from what's know as "race conditions", and could end up with 2 invoices with the same number. Note that something like anIDENTITYproperty won't allow 2 user's to get the same invoice number, however, what it does not guarantee is sequential numbers, or that every number will be used. Numbers would be "lost" in the event of a failedINSERT,DELETE,ROLLBACK, and even possibly an (unplanned) shutdown/restart.
– Larnu
Jan 3 at 8:59
Honestly, I feel like ensuring that every invoice's number is sequential proves nothing to any authority. Numbers like these can easily be amended retrospectively. If the number was "missing", someone inclined could easily add it to the "deleted" table. or move the numbers around if they were out of order.
– Larnu
Jan 3 at 9:02
2
Please do yourself a favour and never delete anything from the invoice database table. Add adeletedflag (and deletion date maybe), but don't delete the invoice row. Your auditors will also want to see which invoices were cancelled/retracted/etc.
– Corion
Jan 3 at 9:40
5
5
Do you have to not have gaps? What are you expecting to happen if you create 2 invoices, and then deleted the first? Would the second, and any latter invoices, have their number lowered? That could easily introduce inconsistencies in your data if you then don't update any relevant foreign keys,
– Larnu
Jan 3 at 8:56
Do you have to not have gaps? What are you expecting to happen if you create 2 invoices, and then deleted the first? Would the second, and any latter invoices, have their number lowered? That could easily introduce inconsistencies in your data if you then don't update any relevant foreign keys,
– Larnu
Jan 3 at 8:56
1
1
It is my country rule that invoice number should not have any gaps between them. When deleting an invoice,we are supposed to create a record of that deleted invoice in my database. So that even though there is a gap, we will able to convince the authorities that it is entered previously and deleted because of certain issues. What i mean is I should not have an unarguable gap in the invoice number.
– Adeeb Arangodan
Jan 3 at 8:59
It is my country rule that invoice number should not have any gaps between them. When deleting an invoice,we are supposed to create a record of that deleted invoice in my database. So that even though there is a gap, we will able to convince the authorities that it is entered previously and deleted because of certain issues. What i mean is I should not have an unarguable gap in the invoice number.
– Adeeb Arangodan
Jan 3 at 8:59
2
2
The
INSERT statement you have would most definitely suffer from what's know as "race conditions", and could end up with 2 invoices with the same number. Note that something like an IDENTITY property won't allow 2 user's to get the same invoice number, however, what it does not guarantee is sequential numbers, or that every number will be used. Numbers would be "lost" in the event of a failed INSERT, DELETE, ROLLBACK, and even possibly an (unplanned) shutdown/restart.– Larnu
Jan 3 at 8:59
The
INSERT statement you have would most definitely suffer from what's know as "race conditions", and could end up with 2 invoices with the same number. Note that something like an IDENTITY property won't allow 2 user's to get the same invoice number, however, what it does not guarantee is sequential numbers, or that every number will be used. Numbers would be "lost" in the event of a failed INSERT, DELETE, ROLLBACK, and even possibly an (unplanned) shutdown/restart.– Larnu
Jan 3 at 8:59
Honestly, I feel like ensuring that every invoice's number is sequential proves nothing to any authority. Numbers like these can easily be amended retrospectively. If the number was "missing", someone inclined could easily add it to the "deleted" table. or move the numbers around if they were out of order.
– Larnu
Jan 3 at 9:02
Honestly, I feel like ensuring that every invoice's number is sequential proves nothing to any authority. Numbers like these can easily be amended retrospectively. If the number was "missing", someone inclined could easily add it to the "deleted" table. or move the numbers around if they were out of order.
– Larnu
Jan 3 at 9:02
2
2
Please do yourself a favour and never delete anything from the invoice database table. Add a
deleted flag (and deletion date maybe), but don't delete the invoice row. Your auditors will also want to see which invoices were cancelled/retracted/etc.– Corion
Jan 3 at 9:40
Please do yourself a favour and never delete anything from the invoice database table. Add a
deleted flag (and deletion date maybe), but don't delete the invoice row. Your auditors will also want to see which invoices were cancelled/retracted/etc.– Corion
Jan 3 at 9:40
add a comment |
2 Answers
2
active
oldest
votes
You can use SEQUENCES created with NO_CACHE option, but this will not guarantee you lack of gaps due to errors in your application.
As a general advice, create your invoices in "draft" state, where everything is saved in the database and committed. Then "finalize" these invoices by generating their number.
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
3
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
add a comment |
You can try the identity property of a column in Sql server to get the auto increment number with sequence you provide such as 1. This number can be combined with some prefix like INV001, INV002 and so on.
You can learn here about identity in SQL Server - IDENITY
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
Have a look at my comments under the question.IDENTITYdoes not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."
– Larnu
Jan 3 at 9:16
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019013%2fwhat-is-the-best-way-to-insert-invoice-number-without-any-gap-into-a-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use SEQUENCES created with NO_CACHE option, but this will not guarantee you lack of gaps due to errors in your application.
As a general advice, create your invoices in "draft" state, where everything is saved in the database and committed. Then "finalize" these invoices by generating their number.
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
3
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
add a comment |
You can use SEQUENCES created with NO_CACHE option, but this will not guarantee you lack of gaps due to errors in your application.
As a general advice, create your invoices in "draft" state, where everything is saved in the database and committed. Then "finalize" these invoices by generating their number.
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
3
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
add a comment |
You can use SEQUENCES created with NO_CACHE option, but this will not guarantee you lack of gaps due to errors in your application.
As a general advice, create your invoices in "draft" state, where everything is saved in the database and committed. Then "finalize" these invoices by generating their number.
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
You can use SEQUENCES created with NO_CACHE option, but this will not guarantee you lack of gaps due to errors in your application.
As a general advice, create your invoices in "draft" state, where everything is saved in the database and committed. Then "finalize" these invoices by generating their number.
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
answered Jan 3 at 9:00
Andrey NikolovAndrey Nikolov
4,3983922
4,3983922
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
3
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
add a comment |
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
3
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
Thank you sir. But sequences are available from 2012 server onwards i guess. I need this to work in 2008 server also. Can I use lock and insert..select statements in such cases
– Adeeb Arangodan
Jan 3 at 9:09
3
3
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
Indeed. But you didn't mention which version of SQL Server is used. And 2008 is about to fall out of support in few months, so you should consider stop using it anyway. Of course, you can achieve this with proper selection of locking and transaction isolation levels too. Maybe using a separate "counters" table to store numbers per year.
– Andrey Nikolov
Jan 3 at 9:13
add a comment |
You can try the identity property of a column in Sql server to get the auto increment number with sequence you provide such as 1. This number can be combined with some prefix like INV001, INV002 and so on.
You can learn here about identity in SQL Server - IDENITY
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
Have a look at my comments under the question.IDENTITYdoes not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."
– Larnu
Jan 3 at 9:16
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
add a comment |
You can try the identity property of a column in Sql server to get the auto increment number with sequence you provide such as 1. This number can be combined with some prefix like INV001, INV002 and so on.
You can learn here about identity in SQL Server - IDENITY
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
Have a look at my comments under the question.IDENTITYdoes not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."
– Larnu
Jan 3 at 9:16
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
add a comment |
You can try the identity property of a column in Sql server to get the auto increment number with sequence you provide such as 1. This number can be combined with some prefix like INV001, INV002 and so on.
You can learn here about identity in SQL Server - IDENITY
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
You can try the identity property of a column in Sql server to get the auto increment number with sequence you provide such as 1. This number can be combined with some prefix like INV001, INV002 and so on.
You can learn here about identity in SQL Server - IDENITY
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
answered Jan 3 at 9:09
Suraj KumarSuraj Kumar
2,79841026
2,79841026
Have a look at my comments under the question.IDENTITYdoes not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."
– Larnu
Jan 3 at 9:16
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
add a comment |
Have a look at my comments under the question.IDENTITYdoes not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."
– Larnu
Jan 3 at 9:16
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
Have a look at my comments under the question.
IDENTITY does not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."– Larnu
Jan 3 at 9:16
Have a look at my comments under the question.
IDENTITY does not guarantee sequential numbers. The document you linked even states: "A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table." & "SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert."– Larnu
Jan 3 at 9:16
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
I am using this for creating id numbers for my items and all. But for invoice number, i can't use this as it may increment on failed transaction. as i mention from server 2012 onwards, for unplanned restarts it may increment by 1000. I need this number in sequential order with out gap (unless deleted) and to work both in 2008 and 2012 servers
– Adeeb Arangodan
Jan 3 at 9:17
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
You might be know you can get and reset the identity in case of failure, error or server restart. In that case you have to find only what is the maximum value.
– Suraj Kumar
Jan 3 at 9:19
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
@Larnu - At any point you have to select any one in available option with most success rate. Either by your customized number generation logic or available built in option with concatenation.
– Suraj Kumar
Jan 3 at 9:24
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019013%2fwhat-is-the-best-way-to-insert-invoice-number-without-any-gap-into-a-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
5
Do you have to not have gaps? What are you expecting to happen if you create 2 invoices, and then deleted the first? Would the second, and any latter invoices, have their number lowered? That could easily introduce inconsistencies in your data if you then don't update any relevant foreign keys,
– Larnu
Jan 3 at 8:56
1
It is my country rule that invoice number should not have any gaps between them. When deleting an invoice,we are supposed to create a record of that deleted invoice in my database. So that even though there is a gap, we will able to convince the authorities that it is entered previously and deleted because of certain issues. What i mean is I should not have an unarguable gap in the invoice number.
– Adeeb Arangodan
Jan 3 at 8:59
2
The
INSERTstatement you have would most definitely suffer from what's know as "race conditions", and could end up with 2 invoices with the same number. Note that something like anIDENTITYproperty won't allow 2 user's to get the same invoice number, however, what it does not guarantee is sequential numbers, or that every number will be used. Numbers would be "lost" in the event of a failedINSERT,DELETE,ROLLBACK, and even possibly an (unplanned) shutdown/restart.– Larnu
Jan 3 at 8:59
Honestly, I feel like ensuring that every invoice's number is sequential proves nothing to any authority. Numbers like these can easily be amended retrospectively. If the number was "missing", someone inclined could easily add it to the "deleted" table. or move the numbers around if they were out of order.
– Larnu
Jan 3 at 9:02
2
Please do yourself a favour and never delete anything from the invoice database table. Add a
deletedflag (and deletion date maybe), but don't delete the invoice row. Your auditors will also want to see which invoices were cancelled/retracted/etc.– Corion
Jan 3 at 9:40