quote:

---

Originally posted by sakets_2000

Doesn't putting an exclusive lock on the resources help your cause ?

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by sakets_2000

Doesn't putting an exclusive lock on the resources help your cause ?

---

No, that probably won't work for this case. That only prevents reading values that have changed but not yet committed.

---

quote:

---

Originally posted by sakets_2000

Not sure on this, But doesn't TABLOCKX lock all the rows and not just the uncommited ones ?

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by sakets_2000

Doesn't putting an exclusive lock on the resources help your cause ?

---

No, that probably won't work for this case. That only prevents reading values that have changed but not yet committed.

I assume you cannot use or repurpse the RecID? Havig that Indentity would solve your problem (I think). But, fudimentally, you are faced with a design flaw. I'm not thinking too clearly cuz it's Friday and I want to get out of here, but one hack that comes to mind is to create another "dummy" table with an Indentity and do an insert into that "dummy" table to get your next ID. <shrug> EDIT: Oh, I thought of another hack. You could replace the sequence with a ROWVERSION(TIMESTAMP) column. That'd get you an ever increasing number.

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by sakets_2000

Not sure on this, But doesn't TABLOCKX lock all the rows and not just the uncommited ones ?

---

Ahh, yes that should work, I had my head stuck thinking about TRANSACTION ISOLATION LEVELs. Also if you include the HOLDLOCK hint or use the SERIALIZABLE isolation level, that should keep the table locked until the end of the transaction.

---

quote:

---

Originally posted by LoztInSpace

I think you are getting a bit obsessed with this whole "Unique within ..." thing.

---

print 'Create table [dbo].[PackageTypeInventoryYearSeq]'
if OBJECT_ID('[dbo].[PackageTypeInventoryYearSeq]','U') is not null
	drop table [dbo].[PackageTypeInventoryYearSeq]

CREATE TABLE [dbo].[PackageTypeInventoryYearSeq](
	[PackageType] [smallint] NOT NULL,
	[InventoryYear] [smallint] NOT NULL,
	[InventorySeq] [int] NOT NULL,
 CONSTRAINT [PK_PackageTypeInventoryYearSeq] PRIMARY KEY CLUSTERED 
([PackageType] ASC,[InventoryYear] ASC))
go
print	'***	Initial load of [PackageTypeInventoryYearSeq]'
insert into  [dbo].[PackageTypeInventoryYearSeq]
select
	a.PackageType,
	b.InventoryYear,
	0
from
	(
	select 1 PackageType union all
	select 2 union all
	select 3 union all
	select 4 union all
	select 5
	) a
	cross join
	(
	select 2007 InventoryYear union all
	select 2008 union all
	select 2009 union all
	select 2010 union all
	select 2011
	) b	
order by
	a.PackageType,
	b.InventoryYear
go

--
declare @out table (NewInventorySeq int not null)

print '***	Update table [PackageTypeInventoryYearSeq] to get next value'
update [dbo].[PackageTypeInventoryYearSeq]
set
	[InventorySeq] = [InventorySeq] +1
output
	inserted.[InventorySeq]
into
	@out
where
	[PackageType]	= 2 and
	[InventoryYear]	= 2008

print '***	Show updated row in table [PackageTypeInventoryYearSeq]'
select *
from
	[dbo].[PackageTypeInventoryYearSeq]
where
	[PackageType]	= 2 and
	[InventoryYear]	= 2008
	
	
print '***	Show updated value to return to client'
select * from @out

Create table [dbo].[PackageTypeInventoryYearSeq]
***	Initial load of [PackageTypeInventoryYearSeq]

(25 row(s) affected)
***	Update table [PackageTypeInventoryYearSeq] to get next value

(1 row(s) affected)
***	Show updated row in table [PackageTypeInventoryYearSeq]
PackageType InventoryYear InventorySeq
----------- ------------- ------------
2           2008          1

(1 row(s) affected)

***	Show updated value to return to client
NewInventorySeq
---------------
1

(1 row(s) affected)

quote:

---

Originally posted by PaulieD

For example:

PackageType : InventoryYear : InventorySeq
1 : 2007 : 172
1 : 2008 : 11
2 : 2007 : 69
2 : 2008 : 28
3 : 2008 : 5

...where an insert for PackageType=1, InventoryYear=2008 would result in 12 for the new row's InventorySeq value.

---

quote:

---

Originally posted by PaulieD

Thanks for the constructive feedback. With thanks and respect to MVJ for his time and contribution, I am also confused as to where he was going - especially since his suggestion contained hard-coded values rather than adaptive logic for continued use into the future...

---

quote:

---

Originally posted by Michael Valentine Jones: I hard-coded the values in the UPDATE statement for simplicity with the assumption that you would be able to understand and adapt this for your needs.

---

quote:

---

Originally posted by PaulieD
Anticipating your inquiry, the format "2007-1-10" serves multiple purposes: "2007" identifies the year it was inventoried; more so for purging aged inventory, "1" dictates where the package should be stored and "10" is nothing more than the unique identifier that completes a unique key.

---

quote:

---

Originally posted by Michael Valentine Jones: (They) are really just attributes of the transaction, not part of the PK

---

quote:

---

Originally posted by Michael Valentine Jones: What information does the InventorySeq hold? Really, it is nothing but a number to make the row unique, so it is just very inconvenient to use IDENTITY value.

---

quote:

---

Originally posted by Peso: And with an identity, you also can make sure no other packagetype nor year can have exact same inventoryseq.

---