Issues with INT instead of int for identity function

Question

One time when I play with identity(int, seed, increment) and found something as shown below:

create table test(A varchar(50))
insert into test(A)
select 'A'
union all
select 'B'
union all
select 'C'
union all
select 'D'

--When "INT" is used with Identity function, this script only works on SQL server 2008, not on 2005. Both servers have collation "Latin1_General_CS_AS"

SELECT IDENTITY(INT, 1, 1) AS GID,
A AS ITEM
INTO #T1
FROM test 

--This will work on both 2005 and 2008 

SELECT IDENTITY(int, 1, 1) AS GID,
A AS ITEM
INTO #T2
FROM test

Could anyone please provide some clues on this since INT and int are no different for other code on these two servers.

 

Thanks.

 

Answer 1

It's working perfect.

 

What's the issue? Are you getting error? Different result?

 

 

Answer 2

in 2005, I get the below errors when I use with INT in identity func:

Msg 243, Level 16, State 3, Line 1
Type INT is not a defined system type.
Msg 2749, Level 16, State 1, Line 1
Identity column 'INT' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

Answer 3

http://msdn.microsoft.com/en-us/library/ms187745%28v=SQL.90%29.aspx

http://msdn.microsoft.com/en-us/library/ms187745%28v=SQL.105%29.aspx

 

 

It seems that it should work...

Answer 4

Ok. I tested this on both 2005 and 2008

and it's working on both instance.

now is there any other data type you're working on? (TIME, DATE etc)

 

 

Answer 5

Yes. it should work. But it does not as shown from the error message....

Thanks.

Answer 6

The test table only has varchar data type here. what is the collation oof your server for 2005?

Thanks.

Answer 7

> --When "INT" is used with Identity function, this script only works on SQL server 2008, not on 2005. Both servers have collation "Latin1_General_CS_AS"

Seems like there was a bug in SQL 2005, that was fixed in SQL 2008.

When you have a case-sensitive collation, this applies to metadata as well. So if you define you own type:

CREATE TYPE mytype FOR int

You cannot use this type as MYTYPE in a case-sensitive database.

Now, when it comes to the built-in types, they belong to a so-called keyword collation, which is always case-sensitive. But there are glitches. I don't think I've seen of this one before, but here is another one:

select hierarchyid::GetRoot ( )
go
SELECT HIERARCHYID::GetRoot ( ) 

The first query returns a result, but the second fails, but it shouldn't.

Personally, I recommend that you always use lowercase for the built-in types, because that is how they actually appear in sys.types.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 8

That makes sense. Thanks. Erland.

Shigui,