What will be inserted?

Question

What will be inserted?

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
July 31, 2023 at 12:00 am

#4223640

Comments posted to this topic are about the item What will be inserted?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ron Hinds SSC-Addicted Points: 413 More actions · Answer 1

I'm going to call my answer of "We can't guess" correct. The reason being, without knowing the structure of your table, we cannot possibly know the output of the INSERT statement. What if the "mytime" column was declared NOT NULL? Then the result would be an error.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

When in doubt, assume the columns exist and defaults are used. NULL is the default.

Chris Wooding SSCarpal Tunnel Points: 4555 More actions · Answer 3

I also think "We can't guess" is correct, but for a different reason. Rows could have been inserted by other users in the time between the insert statement and the query being executed. There could also have been failed attempts to insert during that period.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 4

No, not a valid guess. There are always potential issues with concurrency, but for understanding the point, which is what is reflected in system tables and what happens, you ought to be able to determine this. For some systems and some data, the next value is stored, for some the last value. Knowing which is which is helpful.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

What about if the server or sql service was restarted since the last insert and the cached identity values were tossed out? Wouldn't the value in sys.identity_columns still be the last value that was inserted (or attempted) but the next value be much higher?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 6

There are always holes. It is very difficult or not impossible to write questions to cover every certainty. Assume this ran and the value was something you checked in the nanosecond before an insert.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Steve Jones - SSC Editor wrote: