Database Discussion Boards

First Prev Next

Re: What happens with auto-incremented value on rollback?

Eddy Vluggen30-Jan-13 22:50

Eddy Vluggen

30-Jan-13 22:50

piticcotoc wrote:
Why do some people think that a question must always be answered with with an assumption that everything you do is wrong

..because we never use the value of an identity field; it does not (EVER) hold information. Asking what the number is indicates that you might indeed have a design-problem, and that might lead to problems in the long run.

It's called free advice, and sometimes comes unsollicited.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: What happens with auto-incremented value on rollback?

piticcotoc30-Jan-13 23:17

piticcotoc

30-Jan-13 23:17

Quote:
It's called free advice, and sometimes comes unsollicited.

So do STDs but that doesn't mean I need them

Re: What happens with auto-incremented value on rollback?

Eddy Vluggen31-Jan-13 3:16

Eddy Vluggen

31-Jan-13 3:16

Same goes for taxes, but the fact they have a single thing in common doesn't make them comparable.

This is "normal behaviour" when asking an expert for advice. Ask a builder how to build on quicksand. You can expect an answer, and a warning. You can ask the doctor to remove your lungs - and get an answer and a warning. Same goes here.

Even when being paid for the job, I will not shut up whn someone is making a mistake that could cost them dearly. I´m not that much of an ass yet Thumbs Up | :thumbsup:

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: What happens with auto-incremented value on rollback?

R. Giskard Reventlov31-Jan-13 5:18

R. Giskard Reventlov

31-Jan-13 5:18

piticcotoc wrote:
I did move on.

No you didn't: you were rude which is uncalled for. You should have ignored it. Now you have just shown that you are an arrogant, ungrateful smart-ass. Good luck with getting any more help.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Re: What happens with auto-incremented value on rollback?

PIEBALDconsult30-Jan-13 10:29

PIEBALDconsult

30-Jan-13 10:29

Contrarywise; you don't need to know and yet you asked anyway.

Re: What happens with auto-incremented value on rollback?

piticcotoc30-Jan-13 22:09

piticcotoc

30-Jan-13 22:09

Mind-reading is a great feature to have. This way you know what I want better than me... oh wait; you don't and yet you assumed otherwise anyway

Re: What happens with auto-incremented value on rollback?

pmpdesign30-Jan-13 19:46

pmpdesign

30-Jan-13 19:46

Why don't you just try it and find out for sure instead of being rude to people trying to help. Hmmm | :|

BEGIN TRANSACTION

INSERT something INTO table

ROLLBACK TRANSACTION

Re: What happens with auto-incremented value on rollback?

Jörgen Andersson30-Jan-13 7:37

Jörgen Andersson

30-Jan-13 7:37

I do know, and I did tell you to rethink your design.

If you had used Oracle it would have been much worse, Oracle cache the sequences, so if you restart the server you can get a gap of twenty numbers. and if you have a cluster server they won't even be in order.
(<for the nitpicks> I know it's a setting, but if you want higher performance you don't change it</for the nitpicks>)

People say nothing is impossible, but I do nothing every day.

Re: What happens with auto-incremented value on rollback?

Shameel30-Jan-13 3:15

Shameel

30-Jan-13 3:15

The new IDENTITY value is generated when a row insert is initiated and will update the table's current identity value even when the transaction is rolled back. In simple terms, it would behave as if no rollback happened.

Re: What happens with auto-incremented value on rollback?

piticcotoc30-Jan-13 5:45

piticcotoc

30-Jan-13 5:45

Thanks guys. Exactly what I needed to find out.

Re: What happens with auto-incremented value on rollback?

i.j.russell30-Jan-13 4:37

i.j.russell

30-Jan-13 4:37

Identity (auto-increment) is not part of the transaction, so isn't rolled back. In your example, the ID for the next call would be 102.

Re: What happens with auto-incremented value on rollback?

piticcotoc30-Jan-13 5:41

piticcotoc

30-Jan-13 5:41

Thanks guys. Exactly what I needed to find out.

Re: What happens with auto-incremented value on rollback?

Mycroft Holmes30-Jan-13 11:44

Mycroft Holmes

30-Jan-13 11:44

While Shameel and Russel gave you answers it is not the solution, if you are relying on the identity filed to give you sequential information (or any information) your design is WRONG. An ID field has only 1 function, to identify the record.

Never underestimate the power of human stupidity
RAH

Re: What happens with auto-incremented value on rollback?

PIEBALDconsult31-Jan-13 3:40

PIEBALDconsult

31-Jan-13 3:40

10!

Re: What happens with auto-incremented value on rollback?

gvprabu20-Feb-13 23:02

gvprabu

20-Feb-13 23:02

Hi,

I think You are asking about "IDENTITY" Property in SQL Server right. If you will use IDENTITY while creating table, It will Increment Automatically based on your Starting value and Increment.

For Example,

SQL

CREATE TABLE #T1 (ID INT NOT NULL IDENTITY(1,1), Name VARCHAR(12))
INSERT INTO #T1(Name) VALUES('A')
SELECT * FROM #T1

BEGIN TRAN T1
    INSERT INTO #T1(Name) VALUES('B')
    ROLLBACK  TRAN T1

INSERT INTO #T1(Name) VALUES('C')
SELECT * FROM #T1

So Once Identity values is Incremented, In case If Transaction Roll backed also you will get next value only.

Regards,
GVPrabu.

Book recommendation for learning SQL

LloydA11129-Jan-13 18:33

LloydA111

29-Jan-13 18:33

I am planning on learning about SQL. However, there seems to be no good quality tutorials that I can find on the internet.

Are there any recommended books for SQL beginners?

Thanks

=====
\ | /
 \|/
  |
  |-----|
  |     |
  |_    |
   _)   | /
   _)  __/_
   _)  ____
  |    /|
  |   / |
  |     |
  |-----|
  |
=====
 ===
  =

Re: Book recommendation for learning SQL

Jörgen Andersson30-Jan-13 0:52

Jörgen Andersson

30-Jan-13 0:52

SQL for smarties by Joe Celko.
It's deceptively simple but lasts a long way.

People say nothing is impossible, but I do nothing every day.

Re: Book recommendation for learning SQL

i.j.russell30-Jan-13 4:34

i.j.russell

30-Jan-13 4:34

If you are using SQL Server 2008 or later, I suggest you try T-SQL Fundamentals for Microsoft SQL Server by Itzik Ben-Gan.

Re: Book recommendation for learning SQL

Simon_Whale30-Jan-13 5:46

Simon_Whale

30-Jan-13 5:46

I also read articles from http://www.sqlservercentral.com/[^] and they have a good Question of the Day feature that gets emailed to you daily so that you can test your knowledge etc.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: Book recommendation for learning SQL

Corporal Agarn30-Jan-13 6:33

Corporal Agarn

30-Jan-13 6:33

You need to decide what start with. Do you want a general overview? To learn to code (T-SQL)? To learn how to be a DBA? Do you want a certificate?

I would go to a bookstore with a good computer section and look at the SQL books. As an example, have a question in mind, say for T-SQL, lookup data types. If you can understand what they say, that book would be high on the possible list.

Re: Book recommendation for learning SQL

gvprabu20-Feb-13 23:09

gvprabu

20-Feb-13 23:09

Hi,

Check the following Books.

-- Book 1
Book Name : Beginning SQL Server 2008 for Developers From Novice to Professional Jul 2008
Authors : Louis Davidson, With Kevin Kline, Scott Klein, and Kurt Windisch
Publisher : APress

-- Book 2
Book Name : Pro-T-SQL-2008-Programmers-Guide-Experts-Voice-in-SQL-Server
Author : Michael Coles
Publisher : APress

Regards,
GVPrabu

Can we delete old records from aspstatetempsessions table?

manikantaer29-Jan-13 17:43

manikantaer

29-Jan-13 17:43

Hi..
Recently i created Session State in my project, code is here..
<sessionState mode="SQLServer"
allowCustomSqlDatabase="true"
sqlConnectionString="Data Source=ADMIN-9F8C57749\SQLEXPRESS;Initial Catalog=kecbliss;Integrated Security=True"
timeout="60"
stateNetworkTimeout="60">
</sessionState>
the problem is day by day aspstatetempsessions table is becoming big
so my question is
1.can i delete previous days records from aspstatetempsessions?
2.How to achieve this without affecting to project ?
project details
front end ASP.NET
Back end MS SQL Server

Re: Can we delete old records from aspstatetempsessions table?

thatraja4-Oct-13 20:46

thatraja

4-Oct-13 20:46

You got answer there[^].

FYI
Potential enhancements to ASPState[^]
Where Are ASPStateTempApplications and ASPStateTempSessions Tables?[^]

thatraja

FREE Code Conversion VB6 ASP VB.NET C# ASP.NET C++ JAVA PHP DELPHI ColdFusion
HTML Marquee & its alternatives

Nobody remains a virgin, Life screws everyone Sigh | :sigh:

control AVG cost and close stock

kornkimhour29-Jan-13 15:13

kornkimhour

29-Jan-13 15:13

hi all,
I am new database designer, and now i want to design database that can control avg cost and close stock.could you give me an idea? and please tell me the way to calculate avg cost every transaction(ex: sale,purchase,....) include Item avg cost and report. It is really difficult for new database designer like me. Thank in advanced.

Re: control AVG cost and close stock

thatraja4-Oct-13 20:50

thatraja

4-Oct-13 20:50

For db designing check this site http://www.databaseanswers.org/data_models/[^]
For calculations, it's based on logic. You could discuss with your lead & team.

thatraja

FREE Code Conversion VB6 ASP VB.NET C# ASP.NET C++ JAVA PHP DELPHI ColdFusion
HTML Marquee & its alternatives

Nobody remains a virgin, Life screws everyone Sigh | :sigh:

Last Visit: 31-Dec-99 18:00 Last Update: 15-Jun-24 0:13

Refresh

ᐊ Prev 1...298 299 300 301302303 304 305 306 307 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.