Subject: Re: How ro append characetrs to auto-incrementing number
From: Erland Sommarskog <esq...@sommarskog.se>
References: <6D76A707-E2A2-46C1-8B86-5F06EB01C989@microsoft.com> <OQiHjkWQGHA.5400@TK2MSFTNGP09.phx.gbl> <Xns977F54C51BDBYazorman@127.0.0.1> <EAFF0DFF-F19A-42E0-B33A-3AF31FC9B041@microsoft.com>
Organization: Erland Sommarskog
Message-ID: <Xns977F8054FF7A3Yazorman@127.0.0.1>
User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32)
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.sqlserver.programming
Date: Tue, 07 Mar 2006 03:36:55 -0800
NNTP-Posting-Host: 212.247.8.61
Path: g2news1.google.com!news1.google.com!news4.google.com!news.glorb.com!newshub.sdsu.edu!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
Lines: 1         

pmud (p...@discussions.microsoft.com) writes:
> I am not very sure about transactions. Do I have to write teh code for 
> transactions in trigger window? 

If you are not sure about transactions, you have a lot to learn! 
Understanding transactions is essential for implmenting database solutions
and maintaining integrity of the data.

The code that I showed 

   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

is something you would write in a stored procedure.

Note that the transaction is not only needed to get the order number
right. Presumably there is also an OrderDetails table that you need to
fill. And you don't want the order to be store incompletely in the 
database, do you?

Speaking of OrderDetails, that table should have a foreign-key constraint
to the Orders table. How is that going to work out if you have one order 
table per company? One OrderDetails per company?

Overall, having one table per company is going to lead an increase of
complexity in development seriously.

> Also, with the approach you mentioned,
> will it be possible to have contiguous order nos for each company? 

The above is a necessrity to have contiguos order numbers. If you 
requirement is contiguous numbers, you *cannot* use autonumber. The
IDENTITY feature is *designed* to not give contiguous numbers.

-- 
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx