November 7, 2012 at 5:24 am
insert into table_that_needs_number (number) select a.Client1Number from (update controlfile set Client1Number = Client1Number +1 output inserted.Client1Number ) a
gets it all into one statement which will even work with implicit transactions but if what is desired is for the transactions to be numbered from 1 to n per customer then
select * from
(select id,CustomerNumber,ROW_NUMBER() over (order by id) invoice_number from orders) a where CustomerNumber=@customerNumber and
invoice_number between @beginRow and @endRow
will let you display for the customer a sequential number they can use as a transaction ID which is guaranteed to uniquely identify each transaction without the bother of having sequences or control tables or what all. All it requires is logical deletes instead of physical deletes. Each customer only cares about their own transactions. You only care about your ID - not the one they'll use since you can easily look theirs up given your or vice versa.
November 8, 2012 at 2:38 pm
Eugene Elutin (11/6/2012)
I don't think SEQUENCE feature will help you, as it's going to be a nightmare if you will create a sequence per customer... Also, you will be bound to use Dynamic SQL to get next ID's. No, SEQUENCEs are not designed for what you want.
I would not give up so easily. The implementation of the sequence is exactly what we need - a self-managed guarantee that we don't need to worry about supporting the incrementing mechanism or deadlocking. The only drawback is that Microsoft did not implement it in such a way as to accept variables in the related functions. I think they missed an obvious boat on that one, but there are some ways we can accomplish that ourselves. You mentioned dynamic sql, which is one way, but there is another as well: CLR.
Here is an example of a quick and easy CLR function that will return the incremented number based on variable input:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlInt64 NextSeq(string sequenceName)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select next value for " + sequenceName, conn);
return (System.Int64)cmd.ExecuteScalar();
}
}
};
It's that simple.
November 8, 2012 at 3:34 pm
bteraberry (11/8/2012)
Eugene Elutin (11/6/2012)
I don't think SEQUENCE feature will help you, as it's going to be a nightmare if you will create a sequence per customer... Also, you will be bound to use Dynamic SQL to get next ID's. No, SEQUENCEs are not designed for what you want.
I would not give up so easily. The implementation of the sequence is exactly what we need - a self-managed guarantee that we don't need to worry about supporting the incrementing mechanism or deadlocking. The only drawback is that Microsoft did not implement it in such a way as to accept variables in the related functions. I think they missed an obvious boat on that one, but there are some ways we can accomplish that ourselves. You mentioned dynamic sql, which is one way, but there is another as well: CLR.
Here is an example of a quick and easy CLR function that will return the incremented number based on variable input:
using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlInt64 NextSeq(string sequenceName)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select next value for " + sequenceName, conn);
return (System.Int64)cmd.ExecuteScalar();
}
}
};
It's that simple.
Why CLR? you can do it using dynamic SQL... but it's still absolutely irrelevant to the problem here.
Have you read the last OP explanation of the requirements? He need incremental ID per customer!
Would you still suggest using SEQUENCEs? 100,000 cutomers = 100,000 SEQUENCE's ...
November 8, 2012 at 4:11 pm
Yes I did read it. Why CLR? Because it's awesome compiled code that will run very fast ... much faster than the equivalent in dynamic sql.
If he has 100,000 customers then great. Use the function and pass in 'dbo.customer' + convert(int, cust.CustomerNo) to the CLR function and you will get exactly what he asked for: a reliably incremented number specific to that customer.
The other part would be the initial CREATE SEQUENCE statement for 'dbo.customer' + convert(int, cust.CustomerNo) which would be done in one of three ways: manually (if there are relatively small numbers of customers), with dynamic sql, or with a different CLR function (and I'm not positive that that part can be done with CLR but I would guess it can). Regardless, the create would be a one time thing for each customer. The bigger issue is the fetching of the incremented value because that's where the volume will be.
I'm not sure what you think the problem is. Did you not realize that you are not limited to one sequence entity?
November 9, 2012 at 4:27 am
bteraberry (11/8/2012)
Yes I did read it. Why CLR? Because it's awesome compiled code that will run very fast ... much faster than the equivalent in dynamic sql.
It's really funny! To get next value from SEQUENCE, your function does execute Dynamic SQL, why do you think wrapping it into CLR makes it faster? What do you mean by compiled code? C# code will be compiled, that is true, but SQL it does call has nothing to do with this compilation.
In this case CLR is absolutely irrelevant, it will make process of getting next value much slower!
If he has 100,000 customers then great. Use the function and pass in 'dbo.customer' + convert(int, cust.CustomerNo) to the CLR function and you will get exactly what he asked for: a reliably incremented number specific to that customer.
The other part would be the initial CREATE SEQUENCE statement for 'dbo.customer' + convert(int, cust.CustomerNo) which would be done in one of three ways: manually (if there are relatively small numbers of customers), with dynamic sql, or with a different CLR function (and I'm not positive that that part can be done with CLR but I would guess it can). Regardless, the create would be a one time thing for each customer. The bigger issue is the fetching of the incremented value because that's where the volume will be.
Basically you are advocating of having as many database objects (SEQUENCE's) created as many customers. That is almost the same as having a table per customer...
I don't think any database designer would really like it.
I'm not sure what you think the problem is. Did you not realize that you are not limited to one sequence entity?
Problem is that SEQUENCE's are not designed to support the required functionality.
Please believe me, with having around 20 years experience in a field (eg. Oracle), I have pretty much very good idea of what SEUQENCEs are and how to use them.
OP wants the invoice numbers to be strongly sequential and without any gap. You may be surprised, but using SEQUENCE's doesn't guarantee no gaps in generated numbers... SEQUENCE in SQL (same as in Oracle where it was copied from :-)) generates NEXT number outside of the transaction. So, if you generated sequence in transaction which will be rolled back, generated sequence number will be lost!
Single table maintaining incremental number per CustomerId would be the best way until MS will extend SEQUENCE functionality to support per ROW sequence generated and connectivity to the running transaction. Actually, I think it could be the great idea...
November 9, 2012 at 10:47 am
November 9, 2012 at 11:59 am
Easy guys. I believe that some to-the-point with mistaken first-party terms instead of 3rd party terms is being mistaken as a rude attack. I could be wrong but I don't believe the intention was to be rude here.
--Jeff Moden
November 9, 2012 at 3:55 pm
bteraberry (11/9/2012)
Eugene,I've never had any issue with you whatsoever, but you seem condescendingly rude about some things I don't think you understand. That's not the kind of conversation I want to be a part of, so have fun.
Rude? Where exactly I was rude? Honestly, I didn't have such intention at all.
Would be really great to know what I've failed to understand, but whatever... Thanks, as advised, I'll have fun.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply