I have a SQL Server 2005 table with an Identity column called ID, a
Primary Key column called [Order], and an Account column like this:
CREATE TABLE [dbo].[HistTradesOrig](
[ID] [int] IDENTITY(1,1) NOT NULL,
[order] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Account] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
CONSTRAINT [DF_HistTradesOrig_Bill] DEFAULT ('0'),
CONSTRAINT [PK__HistTradesOrig__52593CB8] PRIMARY KEY CLUSTERED
(
[order] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I need to set [Order] to be a unique number containing account and
another 6 digits number.
Currently, most times I set [Order] = @ACCOUNT + '-' +
convert(varchar(50),@ID) where @ID is the SCOPE_IDENTITY().
Currently, this 6 digits number ranges from 100,000 to 999,999, and if
it's possible we would like to keep it that way.
The problem with that is since I have multiple thread doing it, every
now and then I get Transaction Lock error.

1. Is there any other way for me to set [Order] to be Account +
another unique 6 digits number besides getting it from the Identity
column of the same table ?

2. Is there a VB.NET subroutine/function that I can call to create a 6
digit unique number (so that I don't have to depend on the identity
column of that table) ?

Thank you.

Hello,

How far unique, a Globally Unique Identifier uses a 64Bit word and if that
is not guaranteed always unique

Cor

"fniles" wrote in message
news:acf1cd85-6223-4495-bac7-(E-Mail Removed)...

I have a SQL Server 2005 table with an Identity column called ID, a
Primary Key column called [Order], and an Account column like this:
CREATE TABLE [dbo].[HistTradesOrig](
[ID] [int] IDENTITY(1,1) NOT NULL,
[order] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[Account] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
CONSTRAINT [DF_HistTradesOrig_Bill] DEFAULT ('0'),
CONSTRAINT [PK__HistTradesOrig__52593CB8] PRIMARY KEY CLUSTERED
(
[order] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I need to set [Order] to be a unique number containing account and
another 6 digits number.
Currently, most times I set [Order] = @ACCOUNT + '-' +
convert(varchar(50),@ID) where @ID is the SCOPE_IDENTITY().
Currently, this 6 digits number ranges from 100,000 to 999,999, and if
it's possible we would like to keep it that way.
The problem with that is since I have multiple thread doing it, every
now and then I get Transaction Lock error.

1. Is there any other way for me to set [Order] to be Account +
another unique 6 digits number besides getting it from the Identity
column of the same table ?

2. Is there a VB.NET subroutine/function that I can call to create a 6
digit unique number (so that I don't have to depend on the identity
column of that table) ?

Thank you.

It looks like by creating a Unique NON Clustered ASC index on ID makes
everything runs a LOT faster and fixed the Transaction Log issue.
This even if I leave the following UPDATE statement in the stored
procedure:
select @ID = SCOPE_IDENTITY()
UPDATE HistTradesOrig
SET [Order] = @ACCOUNT + '-' + convert(varchar(50),@ID)
WHERE ID = @ID



On Dec 16, 11:48*am, fniles <fiefie.ni...@gmail.com> wrote:
> I have a SQL Server 2005 table with an Identity column called ID, a
> Primary Key column called [Order], and an Account column like this:
> CREATE TABLE [dbo].[HistTradesOrig](
> * * * * [ID] [int] IDENTITY(1,1) NOT NULL,
> * * * * [order] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> * * * * [Account] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> CONSTRAINT [DF_HistTradesOrig_Bill] *DEFAULT ('0'),
> *CONSTRAINT [PK__HistTradesOrig__52593CB8] PRIMARY KEY CLUSTERED
> (
> * * * * [order] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> I need to set [Order] to be a unique number containing account and
> another 6 digits number.
> Currently, most times I set [Order] = @ACCOUNT + '-' +
> convert(varchar(50),@ID) where @ID is the SCOPE_IDENTITY().
> Currently, this 6 digits number ranges from 100,000 to 999,999, and if
> it's possible we would like to keep it that way.
> The problem with that is since I have multiple thread doing it, every
> now and then I get Transaction Lock error.
>
> 1. Is there any other way for me to set [Order] to be Account +
> another unique 6 digits number besides getting it from the Identity
> column of the same table ?
>
> 2. Is there a VB.NET subroutine/function that I can call to create a 6
> digit unique number (so that I don't have to depend on the identity
> column of that table) ?
>
> Thank you.

On Thu, 16 Dec 2010 09:48:08 -0800 (PST), fniles
<(E-Mail Removed)> wrote:

>I have a SQL Server 2005 table with an Identity column called ID, a
>Primary Key column called [Order], and an Account column like this:
>CREATE TABLE [dbo].[HistTradesOrig](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [order] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>NULL,
> [Account] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL
>CONSTRAINT [DF_HistTradesOrig_Bill] DEFAULT ('0'),
> CONSTRAINT [PK__HistTradesOrig__52593CB8] PRIMARY KEY CLUSTERED
>(
> [order] ASC
>)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>) ON [PRIMARY]
>
>I need to set [Order] to be a unique number containing account and
>another 6 digits number.
>Currently, most times I set [Order] = @ACCOUNT + '-' +
>convert(varchar(50),@ID) where @ID is the SCOPE_IDENTITY().
>Currently, this 6 digits number ranges from 100,000 to 999,999, and if
>it's possible we would like to keep it that way.
>The problem with that is since I have multiple thread doing it, every
>now and then I get Transaction Lock error.
>
>1. Is there any other way for me to set [Order] to be Account +
>another unique 6 digits number besides getting it from the Identity
>column of the same table ?

Hi fniles,

In addition to the suggestions I made in your other topics, here is
one more thought. Redesign the table as follows:

CREATE TABLE dbo.HistTradesOrig
(ID int IDENTITY(1,1) NOT NULL,
Account varchar(10) NOT NULL,
OrderNo int NOT NULL,
order AS Account + CAST(OrderNo AS char(6)),
-- Other columns,
PRIMARY KEY (ID),
UNIQUE (Account, OrderNo)
);

PS: I changed the Account column to be NOT NULL, since your
description implies that it obviously should be.

When inserting a new row, use this code:

INSERT INTO dbo.HistTradesOrig
(Account,
OrderNo,
...)
VALUES (@AccountID,
(SELECT COALESCE(MAX(OrderNo), 100000)
FROM dbo.HistTradesOrig
WHERE Account = @Account),
...);

Make sure to do this in a TRY ... CATCH block that handles the
theoretic possibility of a duplicate key insertion (in case there is a
race condition between two connections trying to isnert a row for the
same Account at the exact same time).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

>> e a SQL Server 2005 table with an IDENTITY column sic: this is a physical table property, not a column called “id”, a PRIMARY KEY column called”order”and count column like this: <<

Well, this is about as wrong as you can get in so small a table.
Reserved words, vague data element names and a count of PHYSICAL
insertion attempts that have nothing to do with the data model. You
call it a History table, but have no temporal attributes. Did you
really need VARCHAR(50)? Surely something that long was the result of
careful research. Or a newbie without any data modeling knowledge
picking a magic number from the air (or from ACCESS defaults).

Have notices that most standardized identifier are fixed length with
heck digits, regular expression and other validation rules?

CREATE TABLE HistTradesOrig -– history about times, duh!
(account_nbr CHAR(n) NOT NULL, –- need specs
order_nbr CHAR (6) NOT NULL
CHECK (order_nbr LIKE '[01][0-9][0-9][0-9][0-9][0-9]') ,
PRIMARY KEY (account_nbr, order_nbr),
order_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
etc);

>> I need to set “order_something” to be a unique number [sic] containing account_<something> and another 6 digits number.[sic] <<

fundamental misconception; Numbers can do math; tag numbers are
identifiers that cannot do math. A random string of letters is not a
word. Got the idea about tag numbers?

>> currently, most times I SET order_something = @in_account_something + '-' + CONVERT (VARCHAR (50),@id) WHERE @in_vague_stupid_id is the SCOPE_IDENTITY(). <<

Using CONVERT instead of the ANSI/ISO Standard CAST() is a good way to
advertise that you are a Hillbilly who needlessly speaks in a local
dialect. The SCOPE_IDENTITY() is even worse; it says I have no real
keys, no relational model and I want to fake pointer chains.

>> currently, this 6 digits number ranges from 100,000 to 999,999, and if it's possible we would like to keep it that way. <<

CREATE TABLE ..
( ..
order_nbr CHAR(6) NOT NULL
CHECK (order_nbr LIKE '[01][0-9][0-9][0-9][0-9][0-9]'),
...
);

The REAL problem is that your approach to RDBMS is fundamentally
wrong. Read a book; learn what a key is; use it.

Can I use you as a bad example in my next book?

On Dec 19, 12:18*am, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> e a SQL Server 2005 table with an IDENTITY column sic: this is a physical table property, not a column called “id”, a *PRIMARY KEY column called”order”and count column like this: *<<
>
> Well, this is about as wrong as you can get in so small a table.
> Reserved words, vague data element names and a count of PHYSICAL
> insertion attempts that have nothing to do with the data model. You
> call it a History table, but have no temporal attributes. *Did you
> really need VARCHAR(50)? Surely something that long was the result of
> careful research. Or a newbie without any data modeling knowledge
> picking a magic number from the air (or from ACCESS defaults).
>
> *Have notices that most standardized identifier are fixed length with
> heck digits, regular expression and other validation rules?
>
> CREATE TABLE HistTradesOrig -– history about times, duh!
> (account_nbr CHAR(n) NOT NULL, –- need specs
> *order_nbr CHAR (6) NOT NULL
> * CHECK (order_nbr LIKE '[01][0-9][0-9][0-9][0-9][0-9]') ,
> *PRIMARY KEY (account_nbr, order_nbr),
> *order_date DATETIME DEFAULT CURRENT_TIMESTAMP *NOT NULL,
> *etc);
>
> >> I need to set “order_something” *to be a unique number [sic] containing account_<something> and another 6 digits number.[sic] <<
>
> fundamental misconception; Numbers can do math; tag numbers are
> identifiers that cannot do math. A random string of letters is not a
> word. Got the idea about tag numbers?
>
> >> currently, most times I SET order_something = @in_account_something + '-' + *CONVERT (VARCHAR (50),@id) WHERE @in_vague_stupid_id is the SCOPE_IDENTITY(). <<
>
> Using CONVERT instead of the ANSI/ISO Standard CAST() is a good way to
> advertise that you are a Hillbilly who needlessly speaks in a local
> dialect. The SCOPE_IDENTITY() is even worse; it says I have no real
> keys, no relational model and I want to fake pointer chains.
>
> >> currently, this 6 digits number ranges from 100,000 to 999,999, and ifit's possible we would like to keep it that way. <<
>
> CREATE TABLE ..
> ( ..
> *order_nbr CHAR(6) NOT NULL
> * *CHECK (order_nbr LIKE '[01][0-9][0-9][0-9][0-9][0-9]'),
> ..
> );
>
> The *REAL problem is that your approach to RDBMS is fundamentally
> wrong. Read a book; learn what a key is; use it.
>
> Can I use you as a bad example in my next book?

Hi There,

This CHECK (order_nbr LIKE '[01][0-9][0-9][0-9][0-9][0-9]'), wouldnot
cover the entire range OP asked for it should be Check(order_nbr LIKE
'[1-9][0-9][0-9][0-9][0-9][0-9]')

Other variation could have been
check(
(
case when isnumeric(order_nbr)=1 then
case when order_nbr between 100000 and 999999 then 1 else 0 end
else 0
end
)=1
)

Though it is little extra code.
I hope it helps
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

On Thu, 23 Dec 2010 02:53:33 -0800 (PST), jsfromynr
<(E-Mail Removed)> wrote:

>Other variation could have been
>check(
> (
> case when isnumeric(order_nbr)=1 then
> case when order_nbr between 100000 and 999999 then 1 else 0 end
> else 0
> end
> )=1
> )

Hi Jatinder,

Beware that ISNUMERIC() will test is a value converts to any numeric
data type; the implicit conversion to integer for the BETWEEN check
can still cause a runtime error. For instance if the data contains
'1e3', which is a valid convert to float, or '$1,234.56', which is a
valid convert to money.

There can also be false positives - this check constraint will not
reject the value 123456.7, because this will be rounded to 123457 on
the conversion to integer.

However, this is all completely irrelevant to the topic, which was
about GENERATING a six-digit number, not about validating it.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis