Identity Type Column...

[JaedenRuiner]

Okay,
This will be an odd question.

In The Table
Item_ID PK
ItemOwn_ID PK

Then in a View I would have:
ItemOwnID + '-' + Format('00000', Item_ID) as RecordID

(Okay i know that isn't the right syntax, but I know somewhere in there there is a way to Format at Integer number so that it is exactly "x" digits wide, and can be padded with 0's or spaces, etc, so please ignore the pseudo code and understand the idea of what the field is "supposed" to look like)

Now what I'd like is for Item_ID to be an Auto-Incremented field (starting at 1 and going up 1 each new record Much like an Identity Field)
HOWEVER (and this is the important part):
I want that Auto Increment Field to Reset (or perhaps be partitioned) "around" the ItemOwn_ID field.

So Example:

AddNewRow -
ItemOwn_ID = "100" //user Entered
ItemID = 1

AddNewRow -
ItemOwn_ID = "100" //user entered
ItemID = 2

... etc ...

AddNewRow -
ItemOwn_ID = "106" //User entered
ItemID = 1

Like that.

(Yes, I can do this with back end programming in my VB.net application, but I was wondering if there is a way I can set up a table to do this automatically)

Thanks

 

[cjard]

You'll probably need an insert trigger, and an index on the ItemOwn_ID column so that the trigger can count the number of ItemOwn_ID already in the table quickly and use that to increment the ID. You'll also probably be looking to use a high isolation level for the transaction, to prevent other users from entering data that collides. You can implement your own sequencing system using a table to store sequence counters..

I gotta say though, i probably wouldnt bother. Instead I'd use a standard incrementing ID to ensure uniqueness and then when/if I need to show the records to the user:

SELECT own + '-' + Format('000000', ROW_NUMBER() OVER(PARTITION BY own ORDER BY id) FROM items

Thus the row number, ordered by ID, gives us the incrementing number.. Humans can use this as something meaningful but the database just calculates it

 

[JaedenRuiner]

You'll probably need an insert trigger, and an index on the ItemOwn_ID column so that the trigger can count the number of ItemOwn_ID already in the table quickly and use that to increment the ID.

Have not yet looked into Triggers, they sound interesting but basically went with what I knew and figured I'd learn more on the way.

SELECT own + '-' + Format('000000', ROW_NUMBER() OVER(PARTITION BY own ORDER BY id) FROM items

I tried the Over/Partition syntax, because I found it so helpful with Oracle, but SQL Server Express compalined and said it didn't support the Over syntax keyword or something. (I did a lot of running sums)

Thus the row number, ordered by ID, gives us the incrementing number.. Humans can use this as something meaningful but the database just calculates it

Yea. Basically, the database is never directly ppl manipulated it is solely for programmatic access, So I handled it as a two part primary key, CustomerID and Num, the customerID is a 6 digit string number, the last 3 chars are formatted into the 10 character EmailID code with the number. So I just created a Stored Proc that Interpreted the current Num for CustID and creates the new one, handling my INSERT statement for me. It is a bit of a hack, i admit, but since the db is only for the program to interface with, i feel pretty assured no unsuspecting moron is going to toy with it. (though I may end up eating those words, :O)

 

[cjard]

Have not yet looked into Triggers, they sound interesting but basically went with what I knew and figured I'd learn more on the way.

Stored procedure that is run every time a chosen table event occurs; nothing special

I tried the Over/Partition syntax, because I found it so helpful with Oracle, but SQL Server Express compalined and said it didn't support the Over syntax keyword or something. (I did a lot of running sums)

Bummer. Analytics may not be available in the baby versions of SQLS. Try OracleXE instead; just as free

i feel pretty assured no unsuspecting moron is going to toy with it. (though I may end up eating those words, :O)

You'd be horrified to see what we do here with a banking system bought in years ago..

 

[JaedenRuiner]

Bummer. Analytics may not be available in the baby versions of SQLS. Try OracleXE instead; just as free

Hrm. didn't know Oracle came out with a Freebie version. I did like using oracle, and their SQL syntax was much cleaner. this T-SQL often ticks me off with the Microsoftisms (most of which completely disobey ansi-sql)

I may be screwed though, because we already have sqlsvr2005 on one of the servers here, so i might be forced to use that any way. *shrug*

 

[cjard]

we already have sqlsvr2005 on one of the servers here

It should be able to do analytical queries, at least..

 

[JaedenRuiner]

It should be able to do analytical queries, at least..

I looked it up, and it says it does. I guess I was using Oracle syntax or something, because last time i tried on SQL express I got an error "around the keyword over" and the error did say something about non-support. Of course, that could also be a SQL Express thing, or a Server Configuration thing that I have not yet looked into.

I've used analytical queries in the past, but honestly, for this i think it is overkill.