How ro append characetrs to auto-incrementing number

Hi,
I am creating a new table. in the table, I want to have a field called
OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
value will be the primary key and thus unique for thr record. How do I appen
d
'TTK-' to an autoincrementing number? Do I have to use a trigger which
automatically appends 'TTk-' to the auto-incrementing number as soon as it i
s
inserted in the databse? If so, what will be the code for that trigger? Or i
s
there a better way to do it.
Please help.
Thanks
--
pmudIf TTK- prepends every unique value in the column, then it is no more unique
than the number itself. Think about it.
Anyway, the way to do this and to needlessly use up disk space so you can
fool yourself into believing you have a better key is to use a computed
field:
CREATE TABLE dbo.Orders
(
OrderNo INT IDENTITY(1,1),
..,
FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
);
Or a view:
CREATE VIEW dbo.View_Orders
AS
SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
FROM dbo.Orders;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
> Hi,
> I am creating a new table. in the table, I want to have a field called
> OrderNo, which will have values like TR-1, TR-2 , TR-3 ... and so on. This
> value will be the primary key and thus unique for thr record. How do I
> append
> 'TTK-' to an autoincrementing number? Do I have to use a trigger which
> automatically appends 'TTk-' to the auto-incrementing number as soon as it
> is
> inserted in the databse? If so, what will be the code for that trigger? Or
> is
> there a better way to do it.
> Please help.
> Thanks
> --
> pmud|||Hi Aaron,
We have orders for differnt companies in different tables. The primary
reason for doing this is that we want to allocate a separate chunk of order
nu,bers for each company rather than haveing Order no 10 for Comapny1 and 1
1
for Company2. So to aviod jumping numbers, we broke orders into different
tables based on teh company.
Now we want to prefix each Order no, in each table with the first 3
characters of the company name. For this, will the following trigger work on
each of the tables:
CREATE TRIGGER IDENTITY_TRIGGER
ON INSERT
AS
{
UPDATE TABLE1
SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
}
Please let me know if there is any mistake I have made. Also, please let me
know if you have any better quick ideas.
Thanks
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> If TTK- prepends every unique value in the column, then it is no more uniq
ue
> than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
> Or a view:
> CREATE VIEW dbo.View_Orders
> AS
> SELECT OrderNo, ..., 'TTk-'+RTRIM(OrderNo)
> FROM dbo.Orders;
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:6D76A707-E2A2-46C1-8B86-5F06EB01C989@.microsoft.com...
>
>|||Warning, warning! I've got alarm bells going off in my head.
You just said you're using 1 column to store 2 facts. This is a really
bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
them in 2 different columns. You table should look something like:
create table dbo.Orders
(
OrderNo int identity(1,1) primary key,
CompanyCode char(3) not null,
..,
CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
varchar(15)),
..,
);
Use the OrderNo as your unique primary key, store the company code in a
separate column and if you want to combine those 2 facts then do so in a
computed column. Don't mix the 2 values in a single materialised
column, it'll just cause you headaches in the long term.
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:

>Hi Aaron,
>We have orders for differnt companies in different tables. The primary
>reason for doing this is that we want to allocate a separate chunk of order
>nu,bers for each company rather than haveing Order no 10 for Comapny1 and
11
>for Company2. So to aviod jumping numbers, we broke orders into different
>tables based on teh company.
>Now we want to prefix each Order no, in each table with the first 3
>characters of the company name. For this, will the following trigger work o
n
>each of the tables:
>CREATE TRIGGER IDENTITY_TRIGGER
>ON INSERT
>AS
>{
>UPDATE TABLE1
>SET OrderNo=CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo)
>}
>Please let me know if there is any mistake I have made. Also, please let me
>know if you have any better quick ideas.
>Thanks
>|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> If TTK- prepends every unique value in the column, then it is no more
> unique than the number itself. Think about it.
> Anyway, the way to do this and to needlessly use up disk space so you can
> fool yourself into believing you have a better key is to use a computed
> field:
> CREATE TABLE dbo.Orders
> (
> OrderNo INT IDENTITY(1,1),
> ...,
> FakeOrderNo AS CONVERT(VARCHAR(15), 'TTk-'+RTRIM(OrderNo))
> );
To add to Aaron's post: if you need to, you can index the computed
column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||pmud (pmud@.discussions.microsoft.com) writes:
> We have orders for differnt companies in different tables. The primary
> reason for doing this is that we want to allocate a separate chunk of
> order nu,bers for each company rather than haveing Order no 10 for
> Comapny1 and 11 for Company2. So to aviod jumping numbers, we broke
> orders into different tables based on teh company.
You did what?
It's alright of wanting to have separate order numbers per company, but
you're criminal if you split the table into one per company only be able
to use IDENTITY for the order number. The normal key for the table would
be (company_id, order_id). "But then I can't use auto-increment?" So,
don't use auto-increment then. Rolling your own incrementing number is no
big deal:
BEGIN TRANSACTION
SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @.company
INSERT orders (company, orderid, ...
VALUES (@.company, @.orderid, ...
..
COMMIT TRANSACTION
And I suspect that if you want one series per company, you want it to
be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
and not 1, 5, 7 which is what you could get if you use IDENTITY.
I will have to add one caveat: the chief reason to use IDENTITY is
not simplicity of proramming, but scalability. In a high-transaction
environment where hundreds of rows are inserted by minute, IDENTITY
may be essential to avoid conentention. But somehow, I don't expect
the order rate for a single company be that high.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Mike,
You said that it was criminal to break out tables into different orders
table per company. What do you think are the flaws in this approach? I know
that it is not a good design but what are the exact resons?
Thanks
--
pmud
"Mike Hodgson" wrote:

> Warning, warning! I've got alarm bells going off in my head.
> You just said you're using 1 column to store 2 facts. This is a really
> bad design. If you've got 2 facts (OrderNo & CompanyCode) then store
> them in 2 different columns. You table should look something like:
> create table dbo.Orders
> (
> OrderNo int identity(1,1) primary key,
> CompanyCode char(3) not null,
> ...,
> CompanyOrderNo as cast(CompanyCode + '-' + rtrim(OrderNo) as
> varchar(15)),
> ...,
> );
> Use the OrderNo as your unique primary key, store the company code in a
> separate column and if you want to combine those 2 facts then do so in a
> computed column. Don't mix the 2 values in a single materialised
> column, it'll just cause you headaches in the long term.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> pmud wrote:
>
>|||Hi Earl,
What are the flaws in this structure?
Thanks
--
pmud
"Erland Sommarskog" wrote:

> pmud (pmud@.discussions.microsoft.com) writes:
> You did what?
> It's alright of wanting to have separate order numbers per company, but
> you're criminal if you split the table into one per company only be able
> to use IDENTITY for the order number. The normal key for the table would
> be (company_id, order_id). "But then I can't use auto-increment?" So,
> don't use auto-increment then. Rolling your own incrementing number is no
> big deal:
> BEGIN TRANSACTION
> SELECT @.orderid = coalesce(MAX(orderid), 0) + 1
> FROM orders WITH (UPDLOCK, HOLDLOCK)
> WHERE company = @.company
> INSERT orders (company, orderid, ...
> VALUES (@.company, @.orderid, ...
> ...
> COMMIT TRANSACTION
> And I suspect that if you want one series per company, you want it to
> be contiguous. For company ABC there should be orders, 1, 2, 3, 4, 5
> and not 1, 5, 7 which is what you could get if you use IDENTITY.
> I will have to add one caveat: the chief reason to use IDENTITY is
> not simplicity of proramming, but scalability. In a high-transaction
> environment where hundreds of rows are inserted by minute, IDENTITY
> may be essential to avoid conentention. But somehow, I don't expect
> the order rate for a single company be that high.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Earl,
I am not very sure about transactions. Do I have to write teh code for
transactions in trigger window? Also, with the approach you mentioned, will
it be possible to have contiguous order nos for each company?
If so, then that will be great.
Thanks for your help..
--
pmud
"Erland Sommarskog" wrote:

> Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> To add to Aaron's post: if you need to, you can index the computed
> column.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I didn't say it was criminal, Erland did.
But I agree that it's a bad idea to have 1 table per company - if you
add another company you'd have to change the schema of the DB rather
than simply inserting a row in a table. As a matter of course, you
shouldn't be changing the schema of production databases regularly
simply because you have new data (that has already been defined, eg. an
order). Also this design would make querying across multiple companies
much more complicated because you'd need to use joins or union operators
to combine the sets of data - yuk! To automate queries like this at
runtime you'd need to use dynamic SQL as the structure of your queries
would change each time you add a new company - imagine how ugly that
would quickly become. There are plenty of other things wrong with this
approach if you just think ahead a year or two and use you imagination a
bit.
When I initially posted in this thread I'd missed the bit where you said
you wanted to store orders specific to a company in company-specific
tables. I just thought you wanted to have 1 orders table and keep all
you orders in there. My main objection to your design was storing 2
facts in a single column. Why would you do that? If you store 2 facts,
why not 3, or 4 or 5 or 100? Imagine how hard it would be to write
queries against a table with a single varchar(8000) column that stored
an OrderNo, a CompanyCode, a OrderDate, a ShippingDate, a
DeliveryAddress, a ContactName, etc., etc.
The basic premise behind relational database theory (very much
simplified) is that a table represents a single entity or relationship
(this is in conflict with the idea of multiple orders tables to store
orders) and a column in a table represents a single fact or attribute of
that entity (this is in conflict with combining multiple attributes in a
single column). These are just the fundamental tenets of this area of
theory. (Oh no, I'm starting to sound a little like Celko - LOL!)
Chris Date, Fabian Pascal or E.F. Codd could give you a much better
explanation about relational theory than I could. If you're really
interested in the theory I recommend reading one or more of their books
(An Introduction to Database Systems
<http://www.amazon.com/gp/product/03...5Fencoding=UTF8>
by Chris Date is probably a good starting point or further to that his
latest book Database in Depth
<http://www.amazon.com/gp/product/05...5Fencoding=UTF8> ).
*mike hodgson*
http://sqlnerd.blogspot.com
pmud wrote:

>Hi Mike,
>You said that it was criminal to break out tables into different orders
>table per company. What do you think are the flaws in this approach? I know
>that it is not a good design but what are the exact resons?
>Thanks
>