February 11, 2006 at 7:24 am
I have two tables in SQL SERVER 2000
STANDARD and CUSTOM
Now i require the primary keys of both the tables to be Identity field. But instead of purely an integer, I require it to be starting with a character or two.
ie;
STANDARD CUSTOM
-------- --------
S1000 C1000
S1002 C1002
S1003 C1003
.... ......
I tried to input s1000 or c1000 as seed, but unfortunately it din't work.
Is there any other way possible?
Can anyone please help me with this, I'd be really grateful
Thanks,
Ron.
February 11, 2006 at 7:00 pm
Ron,
This is what I posted on the "other" forum you posted the same question on...
> First, I have to do due diligence... what you're suggesting is, for so many reasons, "death by SQL". One of those reasons is that the x9999 format you want to use is limited to only 9999 rows. Other problems include the fact that character based keys take more hard disk space and take longer to resolve in joins and filters. As you well know, SQL has a wonderful tool known as the IDENTITY property to make auto-numbering columns but they don't include lettering.
>
> That, not-with-standing, there's a couple of fairly easy ways to do what you ask... the best way, so far as SELECTS go, is to make an IDENTITY column next to another column that will contain your x9999 format. Because a default cannot contain a column name, you'll need to make a trigger for INSERTs that will use the following formula to create the x9999 format (in this case, ID is the IDENTITY column and PK is the x9999 format)...
>
> UPDATE yourtable
> SET PK = 'x'+REPLACE(STR(ID,4),' ','0')
> FROM yourtable tgt,
> INSERTED i
> WHERE tgt.ID = i.ID
>
> Obviously, you would substitute 'C' or 'S' for the 'x' in the algorithm above.
>
> The reason why this is the "Best" way is that it will allow you to add an index to the PK column although you won't be able to add a primary key or unique index to it because the initial state of the column will be NULL for all newly inserted rows.
>
> If you make the trigger containing code similar to the above to the fire for both INSERTs and UPDATEs, it's a faily safe method for ensuring the column is fairly tamper proof.
>
> Another way to do it is to created a calculated column in your table like the code below... the disadvantage here is that you cannot create any kind of index on a calculated column. Fortunately, the x9999 format limits you to a mere 9999 records so table scans won't be all that big a hit on performance.
>
> CREATE TABLE dbo.YourTable
> (
> ID int IDENTITY (1, 1) NOT NULL,
> PK AS 'x' + REPLACE(STR(ID,4),' ','0'),
> CONSTRAINT PK_YourTable
> PRIMARY KEY CLUSTERED(ID)
> )
>
> Here, again, you would sub a 'C' or and 'S' for the 'x' in the code. Obviously, you would also rename the columns and the table to meet your requirements, as well.
>
> There are other methods that include a sequence table (NOT a good idea in MS-SQL Server... if done incorrectly, will produce many deadlocks) and you should never use any form of MAX()+1 to do this type of thing.
>
> Again, my instinct is to protect you and tell you that such keys as you have requested will cause you much pain in the future. But, now you have a couple of methods to explore if you or your boss insist on doing it this way.
>
> --Jeff Moden
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply