June 15, 2017 at 12:10 pm
I have a table that keeps track of current inventory, the location and qty:
INVENTORY
-----------------------------
InventoryID
ItemID
LocationID
Qty
Sample row:
1, 5, 7, 3
2, 5, 8, 2
3, 7, 7, 10
As new inventory gets assigned to a location that is already in the table, the qty changes, same for reducing items at that location. When new items are assigned to a location a new row is added; when all qty is depleted, the row gets deleted.
My question pertains to managing the table size over time. Since it is currently a Heap, it will continue to grow. I could convert it to a clustered index on the InventoryID (auto int), and that would solve the releasing space issue. The other idea I had was to use the TABLOCK hint when deleting rows. In this instance, the InventoryID is arbitrary.
Is there a better way to manage this table size over time?
June 15, 2017 at 12:54 pm
June 15, 2017 at 2:56 pm
From the very brief description you've given, it sounds as if:
LocationID
should be the lead clustering key.
If ItemID is unique within LocationID, then the full key should be:
( LocationID, ItemID )
and you don't really need InventoryID (honestly, there is no law that requires you to have an identity column in every table).
If ItemID is not unique within LocationID, then best is either:
( LocationID, InventoryID )
or possibly even:
( LocationID, ItemID, InventoryID )
Depending on the specific lookups and cardinalities.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply