IDENTITY on 2 PRIMARY KEY's

I have a MS SQL problem. I used to do this (AUTO_INCREMENT on 2 primary key) in MY SQL and it works,

Example:

CREATE TABLE blah (
col1 INT AUTO_INCREMENT,
col2 INT,
PRIMARY KEY (col2, col1)
)

So, when i do the following queries:
INSERT INTO blah (col2) VALUES (1)
INSERT INTO blah (col2) VALUES (1)
INSERT INTO blah (col2) VALUES (3)
INSERT INTO blah (col2) VALUES (3)

I would get the following table with SELECT * from blah

col2 | col1
------------
1 | 1
1 | 2
3 | 1
3 | 2

This does not seem to work in MS SQL with IDENTITY. Can anyone help?

primary keys must, by definition, be null, so that was my first thought

however, i just tested your code, and it worked fine!what do you mean, "does not seem to work"?

It works, but if i do that in MS SQL (replacing AUTO_INCREMENT with IDENTITY), the result from select * from blah is not the same.

The result will be:

col2 | col 1
-------------
1 | 1
1 | 2
3 | 3
3 | 4

It works but not the the way MySQL would produce the table.

MySQL would produce this table
col1 col2
1 23
1 34
1 45

and if you do this query again (in mysql)
insert into compositepk (col2) values ( 23 );
then the result will be
1 23
2 23
1 34
1 45

oh wow, that is so cool!!

i never knew it could do that

thanks for the info

no, i don't think sql server works that way