Visual Studio 2008, VB, SQL: How to create 'identity increment' for clustered key field? Syntax?

I have following stored procedure to create several database tables (see attached code).

In line 103 (and also for other fields that are part of clustered keys) I want the identifying field value to increment by 1 however not further so for al records in the database (as by IDENTITY (100000,1)' but per [CompetitionId], [MatchId], [PlayerId], [ScoreItemId] value combination (in this example).

How is that properly done? I thought of creating a function (stored procedure?) that can be used in the DEFAULT attribute like DEFAULT (newid()), but than different. Is that a good approach? What should the code look like (syntax)?

I work with Visual Web Developer 2008, VB.

1    ALTER PROCEDURE dbo.ScoreCompleteAdd
2    	/*
3    	*/
4    AS
5    	SET NOCOUNT OFF;
6    
7    	IF OBJECT_ID ('dbo.Competition', 'U') IS NULL
8    	BEGIN
9    	CREATE TABLE [dbo].[Competition]
10   	(
11   	[CompetitionId] [int] NOT NULL IDENTITY (1,1),
12   	[Description] [nvarchar] NULL,
13   	[StartDateTime] [datetime] NULL DEFAULT (getdate()),
14   	[EndDateTime] [datetime] NULL,
15   	[ScoreCurrent] [int] NULL,
16   	[ScoreIntermediate] [int] NULL,
17   	[ScoreEnd] [int] NULL
18       PRIMARY KEY ([CompetitionId])
19       WITH (IGNORE_DUP_KEY = OFF)
20   	) 
21   	ON [PRIMARY]
22   	END;
23   
24   	IF OBJECT_ID ('dbo.Match','U') IS NULL
25   	BEGIN
26   	CREATE TABLE [dbo].[Match]
27   	(
28   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
29   	[MatchId] [int] NOT NULL IDENTITY (10,1),
30   	[Description] [nvarchar] NULL,
31   	[StartDateTime] [datetime] NULL,
32   	[EndDateTime] [datetime] NULL,
33   	[ScoreCurrent] [int] NULL,
34   	[ScoreIntermediate] [int] NULL,
35   	[ScoreEnd] [int] NULL
36       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId])
37       WITH (IGNORE_DUP_KEY = OFF)
38   	) 
39   	ON [PRIMARY]
40   	END;
41   
42   	IF OBJECT_ID ('dbo.PlayingClub','U') IS NULL
43   	BEGIN
44   	CREATE TABLE [dbo].[PlayingClub]
45   	(
46   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
47   	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
48   	[PlayingClubId] [int] NOT NULL IDENTITY (100,1),
49   	[Description] [nvarchar] NULL,
50   	[ScoreCurrent] [int] NULL,
51   	[ScoreIntermediate] [int] NULL,
52   	[ScoreEnd] [int] NULL
53       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayingClubId])
54       WITH (IGNORE_DUP_KEY = OFF)
55   	) 
56   	ON [PRIMARY]
57   	END;
58   
59   
60   	IF OBJECT_ID ('dbo.Player','U') IS NULL
61   	BEGIN
62   	CREATE TABLE [dbo].[Player]
63   	(
64   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
65   	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
66   	[PersonId] [int] NOT NULL REFERENCES Person(PersonId),
67   	[PlayerId] [int] NOT NULL IDENTITY (1000,1),
68   	[ScoreCurrent] [int] NULL,
69   	[ScoreIntermediate] [int] NULL,
70   	[ScoreEnd] [int] NULL
71       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayerId])
72       WITH (IGNORE_DUP_KEY = OFF)
73   	) 
74   	ON [PRIMARY]
75   	END;
76   
77   
78   	IF OBJECT_ID ('dbo.ScoreItem','U') IS NULL
79   	BEGIN
80   	CREATE TABLE [dbo].[ScoreItem]
81   	(
82   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
83   	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
84   	[PlayerId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId, PlayerId) REFERENCES Player,
85   	[ScoreItemId] [int] NOT NULL IDENTITY (10000,1),
86   	[ScoreCurrent] [int] NULL,
87   	[ScoreIntermediate] [int] NULL,
88   	[ScoreEnd] [int] NULL
89       PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayerId], [ScoreItemId])
90       WITH (IGNORE_DUP_KEY = OFF)
91   	) 
92   	ON [PRIMARY]
93   	END;
94   	
95   	IF OBJECT_ID ('dbo.ScoreDetail','U') IS NULL
96   	BEGIN
97   	CREATE TABLE [dbo].[ScoreDetail]
98   	(
99   	[CompetitionId] [int] NOT NULL REFERENCES Competition(CompetitionId),
100  	[MatchId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId) REFERENCES Match,
101  	[PlayerId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId, PlayerId) REFERENCES Player,
102  	[ScoreItemId] [int] NOT NULL FOREIGN KEY (CompetitionId, MatchId, PlayerId, ScoreItemId) REFERENCES ScoreItem,
103  	[ScoreDetailId] [int] NOT NULL IDENTITY (100000,1),
104  	[Score] [int] NULL
105      PRIMARY KEY CLUSTERED ([CompetitionId], [MatchId], [PlayerId], [ScoreItemId], [ScoreDetailId])
106      WITH (IGNORE_DUP_KEY = OFF)
107  	) 
108  	ON [PRIMARY]
109  	END;
110  	
111  	/* SET NOCOUNT ON */
112  	RETURN

Select allOpen in new window