Sql Server 2005 Auto changing primery key

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hi
i am using sql server 2005 and C#.net, i have a table name patient the primary key of this table is (LAB_Id int) which auto increment is true and seed in one the value of this column is like 11000001,11000002,11000003.........and so on now i want that when the year change from 2011 to 2012 my LAB_Id automatically be start from 0 but like this 12000001, 12000002, 12000003.............so on and in 2013 it should be start like 13000001, 13000002,13000003............. this means that first two digit represent the year and with every year changing it should be automatically change.
is it possible if so help me with the help of code

Posted 10-May-11 8:14am

sher ali

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

strogg · Answer 1 · 2011-05-10T10:35:00

Remove the identity spec for the column & calculate & insert the value yourself

You can get the highest Id using

Select MAX(Lab_Id) from patient

You can increment & insert the new value

If the year has changed, calculate the new id like

int Id = ((DateTime.Today.Year % 100) * 1000000) + 1

============================================

Alternatively, you can still use auto increment & choose to temporarily turn off auto increment and insert value when the year has changed.

You can use

SET IDENTITY_INSERT patient ON

After this, you can insert custom id value and turn off using

SET IDENTITY_INSERT patient OFF

Remember that IDENTITY_INSERT can be turned on for only one table at a time.

NuttingCDEF · Answer 2 · 2011-05-10T11:33:00

I generally avoid putting anything that looks like meaningful data in a primary key / ID field. It's also generally preferable to have separate fields for each item of data (rather than any form of composite) and then add an ID field whose sole purpose is to uniquely identify each record (primary key and field used to link records between tables in 1-many relationships - and can be any unique gobbledegook you like as it can be automatically generated and, apart from debugging etc., is never seen by a human).

In your case I'd have:
Field 1 - ID - autoincrement integer or (my preference) a Guid
Field 2 - Year
Field 3 - Patient no.

Just a thought . . .

Sql Server 2005 Auto changing primery key

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0