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

Open in new window

ruud00000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
Which version of SQL Server are you using?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ruud00000Author Commented:
Sorry, i didn't continue with this. Assigned points anyway.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.