MS SQL Server - Identity vs. Identity(1,1)

Author

Identity vs. Identity(1,1)

cbrichards via SQLMonster.com

2006-11-30, 12:12 am

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:

CREATE TABLE #Temp (TempID int identity, Description(100) )

CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )

In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?

--
Message posted via http://www.webservertalk.com

KenJ

2006-11-30, 12:12 am

Hi cbrichards

BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)

When you insert a few records into each table and selected them back
out, what do you get?

CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp

CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp

KenJ

cbrichards via webservertalk.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
>
> CREATE TABLE #Temp (TempID int identity, Description(100) )
>
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
>
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
>
> --
> Message posted via http://www.webservertalk.com

Hari Prasad

2006-11-30, 7:14 pm

Yes, if you are not specifying the value then it will be defaulted to (1,1)

Thanks
Hari

"cbrichards via webservertalk.com" <u3288@uwe> wrote in message
news:6a0b564bac5a5@u
we...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
>
> CREATE TABLE #Temp (TempID int identity, Description(100) )
>
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
>
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
>
> --
> Message posted via http://www.webservertalk.com
>

John Bell

2006-11-30, 7:14 pm

Hi

When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more

John

"cbrichards via webservertalk.com" wrote:

> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
>
> CREATE TABLE #Temp (TempID int identity, Description(100) )
>
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
>
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
>
> --
> Message posted via http://www.webservertalk.com
>
>

Sponsored Links