I have a series of records, that are displayed as contents in a .NET application,i know i can order the items to be dynamic, i.e I set the top item in the contents,
However I know this will get quite messy at the application level.
I would like to set up the default if I insert an item, into the table to make it the last in the list, select max(ordernum) + 1, i guess?
How can I set an on insert constraint, or what its called for this in SQL Server?
The rest of the data alreay has a PK, and some FKs, but can I use the same process but make it not identity?
See MSDN on Identity columns here. Good to read since there can be gotchas depending on needs and environment (eg: is it a replicated DB, etc).
It doesn't have to be the PK, but it's probably advisable to index it.
Note on using a time stamp rather than an ID: On mass Inserts (and depending on the version of SQL) getdate() may be executed once (all rows identical) or once for each row - so unique values is not a guarantee. Does that matter for your application? If not; maybe a simple TimeStamp column that has default of "getdate()" is best (at the expense of 8 bytes per). Timestamps can come in handy for other reporting needs. For that matter; use smalldatetime (4 bytes). Having an unique ID can be great for linking or if MS Access is in the mix, since it chokes without a unique key.