May 21, 2020 at 12:00 am
Comments posted to this topic are about the item The IDENTITY Column Property
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
May 21, 2020 at 12:28 pm
Good job!
There is one things that could probably be added to this. An identity is incremented when an attempt to insert is performed. Even if the insert fails, the identity is incremented.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 21, 2020 at 12:58 pm
Good handy reference. I NEVER use identities in my designs or code, but they exist and you have to deal with them.
May 21, 2020 at 1:23 pm
Good handy reference. I NEVER use identities in my designs or code, but they exist and you have to deal with them.
Curiously, why would you NEVER use an identity?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 21, 2020 at 1:36 pm
It's great to see this article! I'm going to sit this pie fight out though. Two items I would toss in fwiw:
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 21, 2020 at 2:10 pm
Good job!
There is one things that could probably be added to this. An identity is incremented when an attempt to insert is performed. Even if the insert fails, the identity is incremented.
Thanks so much. Absolutely correct Michael.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
May 21, 2020 at 2:13 pm
It's great to see this article! I'm going to sit this pie fight out though. Two items I would toss in fwiw:
<li style="list-style-type: none;">
- The return type of the SCOPE_IDENTITY function is NUMERIC(38, 0) which is colossal sized. Because (or since?) that's the largest possible primary key you can create in SQL Server. Bigger than bigint
<li style="list-style-type: none;">
- I have never and I will never use @@IDENTITY. Cattle prods could not get me to use that. Doing anything based on "session" is fraught with issues imo. No way
Good points, scdecade. Thanks. You know, this feedback was definitely expected given the amount of material out there about the limitations of IDENTITY. Definitely doing follow up articles on NEWID() and SEQUENCES.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
May 21, 2020 at 2:21 pm
It's great to see this article! I'm going to sit this pie fight out though. Two items I would toss in fwiw:
<li style="list-style-type: none;">
- The return type of the SCOPE_IDENTITY function is NUMERIC(38, 0) which is colossal sized. Because (or since?) that's the largest possible primary key you can create in SQL Server. Bigger than bigint
<li style="list-style-type: none;">
- I have never and I will never use @@IDENTITY. Cattle prods could not get me to use that. Doing anything based on "session" is fraught with issues imo. No way
At a previous position, @@IDENTITY was in hundreds of procs. I did a demo of why this is not a good idea and proved to the devs why and where this was causing bugs.
Spend forever removing it.
A big giant new module was developed. QA failed miserably. Well, the dev's paid no attention, @@IDENTITY was all through this "new" code.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 1, 2020 at 7:32 am
The increment also is out of transactions. So, rollback doesn't restore the original value.
Also, you can not remove the property IDENTITY from a column.
This adds the IDENTITY to a column:
ALTER TABLE TB ALTER COLUMN my_id INT IDENTITY
but this doesn't remove it, also though no error is given:
ALTER TABLE TB ALTER COLUMN my_id INT
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply