Identity field on each table

Question

Identity field on each table

miss.delinda

SSCommitted

Points: 1616
More actions
February 26, 2010 at 12:22 am

#230317

I understood each table must have a Identity Field called trnxid (transaction id)
Consider as follow,
CREATE TABLE [dbo].[tCounter](
[trnxid] [int] IDENTITY(1,1) NOT NULL primary key clustered,
[cd] [varchar](20) NOT NULL,
[desn] [varchar](50) NOT NULL
)
int data type is a 4 byte. It is between -2,147,483,648 to 2,147,483,647
My question is
1. Is that right define the Identity Field using int datatype?
2. What happen when this Identity Field reach maximum value?
Need explanation

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

miss.delinda (2/26/2010)
I understood each table must have a Identity Field called trnxid (transaction id)

There are no such requirements in SQL. There's no requirement that a table MUST have an identity column, there's no requirement that, if a table does have an identity, that it must have a specific name.

This may be a coding standard in your team, but that's all

My question is
1. Is that right define the Identity Field using int datatype?

It's common, but it doesn't have to be int. As far as I know, any numeric data type will work

2. What happen when this Identity Field reach maximum value?

Inserts into the table throw errors. Not sure offhand which error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

miss.delinda (2/26/2010)
I understood each table must have a Identity Field called trnxid (transaction id)
Consider as follow,
CREATE TABLE [dbo].[tCounter](
[trnxid] [int] IDENTITY(1,1) NOT NULL primary key clustered,
[cd] [varchar](20) NOT NULL,
[desn] [varchar](50) NOT NULL
)
int data type is a 4 byte. It is between -2,147,483,648 to 2,147,483,647
My question is
1. Is that right define the Identity Field using int datatype?
2. What happen when this Identity Field reach maximum value?
Need explanation

To add to what Gail has already identified, are you worried that a table will have over 2 billion rows in it? If so, you could always use BIGINT but I believe that you'll have other problems if you have a single table with over 2 billion rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

1. The use of Int is a common practice.

2. You will receive errors. If using an Int, you may want to change that to BigInt.

Otherwise, the comments that Gail made are accurate. Identity columns and naming standards are an internal decision and standard. You should check with your team.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff Moden (2/26/2010)
To add to what Gail has already identified, are you worried that a table will have over 2 billion rows in it? If so, you could always use BIGINT but I believe that you'll have other problems if you have a single table with over 2 billion rows.

If you start from 1. Another practice is to start from the negative value end and thus double the int to over 4 billion rows.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

miss.delinda SSCommitted Points: 1616 More actions · Answer 5

miss.delinda

SSCommitted

Points: 1616

February 26, 2010 at 12:52 am

#1125405

Ok guys. Me understood now. cheers ..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

CirquedeSQLeil (2/26/2010)
Jeff Moden (2/26/2010)
To add to what Gail has already identified, are you worried that a table will have over 2 billion rows in it? If so, you could always use BIGINT but I believe that you'll have other problems if you have a single table with over 2 billion rows.
If you start from 1. Another practice is to start from the negative value end and thus double the int to over 4 billion rows.

Heh... I've never actually met or heard of anyone who has actually done that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I've seen it a few times. And now you can say that you have heard of that. 😀

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

CirquedeSQLeil (2/26/2010)
I've seen it a few times. And now you can say that you have heard of that. 😀

Heh... but never in the first person. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 10

GilaMonster (2/26/2010)
It's common, but it doesn't have to be int. As far as I know, any numeric data type will work

You can use decimal, int, numeric, smallint, bigint, or tinyint . The imprecise (FLOAT, REAL) numeric types can't be used.

The maximum for DECIMAL(38,0) is 10^38 - 1, which seems adequate for most requirements 😀

If not, you can always use the negative numbers too.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 11

Jeff Moden (2/26/2010)
Heh... I've never actually met or heard of anyone who has actually done that.

Hello! Pleased to meet you.

Actually, I worked at a place which started at one and ran out of numbers. We reseeded to the largest (smallest?) negative number and went for coffee.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Paul White (2/27/2010)
GilaMonster (2/26/2010)
It's common, but it doesn't have to be int. As far as I know, any numeric data type will work
You can use decimal, int, numeric, smallint, bigint, or tinyint . The imprecise (FLOAT, REAL) numeric types can't be used.
The maximum for DECIMAL(38,0) is 10^38 - 1, which seems adequate for most requirements 😀
If not, you can always use the negative numbers too.

Thanks Paul. That is good information.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 13

Paul White (2/27/2010)
Jeff Moden (2/26/2010)
Heh... I've never actually met or heard of anyone who has actually done that.
Hello! Pleased to meet you.
Actually, I worked at a place which started at one and ran out of numbers. We reseeded to the largest (smallest?) negative number and went for coffee.
Paul

Cool... what kind of information did the table hold where it exceeded the positive values of INT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 14

Jeff Moden (2/27/2010)
Cool... what kind of information did the table hold where it exceeded the positive values of INT?

Well this was 13 years ago...but to the best of my recollection, the table contained Line Loss Factors (LLFs). LLFs are used to calculate line losses (really!) when transmitting electricity over a national electricity grid. They were issued for every stretch of line, for every 15-minute period of every day. The numbers were used in calculating aggregate line losses for the individual power companies. The numbers were retrospectively changed quite often, so the sliding window of data that was 'live' was extremely large. And this was on SQL Server 6.5 and 7.0!

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi