How do I mimic autoNumber for non-identity columns?

Assume I have an inventory system used by several customers. Each customer
wants each item in their inventory to have a number, and they don't want any
gaps.
So, there's an Item table that has information about each item, as well as
the customer the item belongs to. When an item is inserted, I cannot use an
Identity column to autonumber the ItemId because if a customer inserts an
item, then a second customer inserts a hundred items, then the original
customer inserts another item, there is a gap of one hundred items from the
perspective of the original customer. This isn't desired behavior.
I need a way to do a per-customer autonumber, but I don't know how to do
this without running into concurrency problems.Greg, If I understood you properly you need
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE custid=.....
INSERT INTO AnothetTable VALUES (@.max_item)
COMMIT TRAN
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
> Assume I have an inventory system used by several customers. Each
> customer
> wants each item in their inventory to have a number, and they don't want
> any
> gaps.
> So, there's an Item table that has information about each item, as well as
> the customer the item belongs to. When an item is inserted, I cannot use
> an
> Identity column to autonumber the ItemId because if a customer inserts an
> item, then a second customer inserts a hundred items, then the original
> customer inserts another item, there is a gap of one hundred items from
> the
> perspective of the original customer. This isn't desired behavior.
> I need a way to do a per-customer autonumber, but I don't know how to do
> this without running into concurrency problems.|||If Table can be the same as AnotherTable, I think this could work. So,
assuming ItemNumber is the column I want to mimic autonumber on, we'd have:
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(ItenNumber),0) FROM Inventory WITH
(UPDLOCK,HOLDLOCK)
WHERE custid=4
INSERT INTO Inventory VALUES (@.max_item + 1)
COMMIT TRAN
Would that work? Are UPDLOCK and HOLDLOCK merely hints? What if the hints
get ignored?
Thanks.
"Uri Dimant" wrote:

> Greg, If I understood you properly you need
> DECLARE @.max_item
> BEGIN TRAN
> SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE custid=.....
> INSERT INTO AnothetTable VALUES (@.max_item)
> COMMIT TRAN
>
>
> "Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
> news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
>
>

How do I get just one set?

I am joining two tables in an SQL query.
Table 1: item has three fields, id, org_id, and name
Table 2: item_set has five fields item_id, org_id, leftIndex, depth, and
rightIndex
The item_set table is used to help sort structures hierarchically.
For example the following query would return a parent item and all its
children starting at the top of a tree:
Select
Itm1.id as parent_id,
Itm1.name as parent_name,
Is1.leftIndex as parentIndex,
Itm2.id as child_id,
Itm2.name as child_name,
Is2.leftIndex as childIndex,
Is2.depth as degree_of_separation
From
item as itm1
Inner join
Item_set as is1
On (itm1.id = is1.item_id and is1.depth = 0)
And (itm1.org_id = is1.org_id)
Inner join
Item_set as is2
On (is2.leftIndex between is1.leftIndex and is1.rightIndex)
And (is1.item_id <> is2.item_id)
And (is1.org_id = is2.org_id)
Inner join
Item as itm2
On (is2.item_id = itm2.id)
And (itm2.org_id = is2.org_id)
order by itm1.id , itm2.id
The trouble is that there may be different representations of trees for the
same items in the item_set table. One representation of four items might
have leftIndex values 1, 2, 3, 4 for the parent and its three children and
there could be another set of leftIndex values of 20, 21, 22, 24 for the
very same set of items.
My question for the SQL experts out there is how do I write a query to get
only one set of the items such as 1, 2, 3, 4 ? I can't assume that the
sequence will always be starting at the top of the tree, e.g. depth = 0.
27-1006Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, rows are not records and columns are not fields, there is not
such things as a magical "id", a vague "name", etc. Read ISO_11179 for
the proper way to name things
CREATE TABLE Items
(item_id INTEGER NOT NULL ,
org_id INTEGER NOT NULL,
PRIMARY KEY(item_id, org_id),
item_name CHAR(15) NOT NULL);
Is this a nested sets model!
CREATE TABLE ItemSets
(item_id INTEGER NOT NULL
org_id INTEGER NOT NULL,
FOREIGN KEY (item_id, org_id),
REFERENCES Items (item_id, org_id)
ON DELETE CASCADE -- guess at biz rule
ON UPDATE CASCADE,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0) ,
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
depth is computable, so do not store it. It will get out of synch and
screw up things.
Get a copy of TREES & HIERARCHIES IN SQL and look at chapter about
compare sub-tree structures. I am not going to give you a few
thoiusand words and illustrations in a newsgroup.
The basic idea is pick the root node. find the first subtree and
substract MIN(lft) from the lft and rgt values. Find the second
subtree and repeat the process. UNION ALL the two canonical subtrees
1) if you have a table with exactly duplicated rows. the subtress are
identical
2) if the nodes match, but not the (lft, rgt) pairs, they are different
arrangements of the same nodes
3) If the nodes do not match, but the (lft, rgt) pairs do, the have the
same structure with different nodes.
The pictures will help when you buy the book.
Here is query for depth. Assume an organizational chart in a Nested
Set model.
SELECT COUNT(O2.emp) AS depth, O1.emp
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
GROUP BY O1.lft, O1.emp;

How do I export my database from SQL Server Management Studio Express

Hi

I am using SQL Server Management Studio Express to make my databases but I noticed if I make my database in Visual studios and go new Item and make a new database I see it in app_data folder and server express tab and if I make it in SQL Server Management Studio Express I only see it in the server explorer.

So if I have to move my files to another computer how do I move my database easly with SQL Server Management Studio Express? Since when you make it with the visual studio the file gets stored with all the other files of your project so if you move it all to another computer you prob won't run into a problem.

So how do I make it that so I can do everything in SQL Server Management Studio Express(since I like working in it) then export it into a file that I can then go into my app_data folder and add it is an exist item?

Thanks

Hi chobo2,

Generally speaking, when you create a new database through Management Studio, the database file will be stored at: %yoursqlinstallationpath%MSSQL.1\MSSQL\Data. If you want to access it through your Visual Studio you must first connect to it(server exploer--> new connection).

And if you create a new database through Visual Studio, the database file will be stored in you application App_Data folder.You can access to it directly through both Visual Studio and Management Studio.

As to export a database file to new location(or migrate databae), as far as i know, you have 2 options:

1: Detach and Attach:

Detach the database from original database Server and copy the physical database files(*.mdf and *.ldf) to the new location.At the new server side, you put the physical database files to a certain place(generally speaking it's the default sql server database folder), and attach to the database again;

2:Use Backup and Restore:

In your original database server you can make a backup of your database file and store it to a certain place(*.bak).Take the *.bak file to the new server and in the new database server, restore that database.

You can implement the above 2 methods in sql2000, sql express, or sql 2005. However, detailed operations could be different if you use different verstions of sql server.

Hope my suggestion helps