Re: composite primary keys to get different sequences with autoincrement

Tech-Archive recommends: Fix windows errors by optimizing your registry


"adanny08" <u51973@uwe> wrote in message news:9638e5654a04f@xxxxxx
I am working on a software where each entry must get a unique identity
comprising of region+birthdate+id.The id should be implemented with auto
increment and the id will reflect his no. on the people with the same birth
date in that region.Example,A20092285 will mean the person was born in region
A 0n the date 28-2-2009 and is the 5th person born on that day to be
registered.I need help seriously.

Hi

If these are all columns in the table, then SQL Server does not have the equivalent of doing an incremental number. The identity column will increment over the whole table, but and identity column could be used as a differentiator ie. to give the order of the given rows when you use a select statement using ROW_NUMBER in SQL 2005 and 2008 or a subquery in SQL 2000

This has SQL 2008 row constructors you would need to change this to work on older versions

CREATE TABLE t1
(
id int not null identity,
religion char(1) not null,
dateofbirth date not null,
name varchar(20) not null,
CONSTRAINT [PK_t1]
PRIMARY KEY CLUSTERED (dateofbirth, id, religion )
)

INSERT INTO t1 ( religion, dateofbirth, name )
VALUES ( 'A', '20090101', 'AA1'),
( 'A', '20090101', 'AA2'),
( 'A', '20090101', 'AA3'),
( 'A', '20090101', 'AA4'),
( 'A', '20090101', 'AA5'),
( 'B', '20090101', 'BA1'),
( 'B', '20090101', 'BA2'),
( 'A', '20090102', 'AB1'),
( 'A', '20090102', 'AB2'),
( 'B', '20090102', 'BB1'),
( 'A', '20090103', 'AC1'),
( 'A', '20090103', 'AC2')


SELECT * FROM t1
ORDER BY religion, dateofbirth

SELECT religion, dateofbirth,
ROW_NUMBER() OVER ( PARTITION BY religion, dateofbirth ORDER BY id ) AS [ROW_NUMBER]
FROM t1

Or in SQL 2000 if the dateofbirth (which would be a datetime) always has the same time portion,

SELECT religion, dateofbirth,
(SELECT COUNT(*)+1 FROM t1 d WHERE d.religion = t.religion AND d.dateofbirth = t.dateofbirth AND d.id < t.id ) AS [ROW_NUMBER]
FROM t1 t

But there isn't a need for the identity column if you can order by the dateofbirth which could have differentiating time portions e.g.

CREATE TABLE t2
(
religion char(1) not null,
dateofbirth datetime not null,
name varchar(20) not null,
CONSTRAINT [PK_t2]
PRIMARY KEY CLUSTERED (dateofbirth, religion )
)

INSERT INTO t2 ( religion, dateofbirth, name )
VALUES ( 'A', '20090101 00:00', 'AA1'),
( 'A', '20090101 00:01', 'AA2'),
( 'A', '20090101 00:02', 'AA3'),
( 'A', '20090101 00:03', 'AA4'),
( 'A', '20090101 00:04', 'AA5'),
( 'B', '20090101 00:00', 'BA1'),
( 'B', '20090101 00:01', 'BA2'),
( 'A', '20090102 00:00', 'AB1'),
( 'A', '20090102 00:01', 'AB2'),
( 'B', '20090102 00:00', 'BB1'),
( 'A', '20090103 00:00', 'AC1'),
( 'A', '20090103 00:01', 'AC2')


SELECT * FROM t2
ORDER BY religion, dateofbirth

SELECT religion, CAST(dateofbirth AS Date) AS [DateOfBirth],
ROW_NUMBER() OVER ( PARTITION BY religion, CAST(dateofbirth AS Date) ORDER BY dateofbirth ) AS [ROW_NUMBER]
FROM t2

-- SQL 2000
SELECT t.religion, CAST(FLOOR(CAST(t.dateofbirth AS float)) AS datetime) AS [DateOfBirth],
(SELECT COUNT(*)+1 FROM t2 d
WHERE d.religion = t.religion
AND CAST(FLOOR(CAST(t.dateofbirth AS float)) AS datetime) = CAST(FLOOR(CAST(d.dateofbirth AS float)) AS datetime)
AND d.dateofbirth < t.dateofbirth
) AS [ROW_NUMBER]
FROM t2 t
ORDER BY t.religion, [DateOfBirth], [ROW_NUMBER]


If you actually want to store this value, then within your transaction you can select the current maximum for a given religion, date, the use this value in your insert statement. You would need an UPDLOCK hint on the select statement to make sure no-one else will update it. This method may cause significant contention problems if there are many inserts.

John

.