Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gussabina
Starting Member
1 Post |
 Posted - 2010-01-30 : 13:16:17
|
| Hi All:I have a table where one of the columns (other than the Primary key with identity) should allow to insert either a 0 value or a unique incremental value (like an identity).I noticed only one identity could be active in a table, and also I'm not sure if the column is identity, the insert can force a 0 in somes cases, as requested.What other approach can I take? (I could handle programatically, but I would like to have the SQL solving this automatically). Would Uniqueidentifiers solve this problem?Thanks;Gus |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 13:26:42
|
If you want a unique incremental value, or zero (duplicates allowed) then you will have to handle it yourself.You can doINSERT INTO MyTable(MyID, ...)SELECT (Select COALESCE(MAX(MyID)+1, 1) FROMMyTable), ... In SQL 2008 you could also use a Filtered Index ignoring zeros, but requiring other values to be unique, just as a safeguard. In SQL 2005 you could use a trigger to enforce, or SQL 2005 Enterprise an Indexed View. |
 |
|
|
|
|
|
Define a column which allow either 0 or identi