May 22, 2013 at 7:26 am
Nice question thanks. I've learnt something new, that SELECT INTO does not transfer the properties of a computed column to the target table.
Dave Morris :alien:
"Measure twice, saw once"
May 23, 2013 at 1:37 am
Earlier I'm aware of Index and constraints will not be transferred to destination table..but about Computed columns i came to know only from this QotD. Thanks kapil.
--
Dineshbabu
Desire to learn new things..
May 23, 2013 at 1:43 am
Hugo Kornelis (5/18/2013)
sea4stars (5/18/2013)
Interesting: After I decided on my answer of the first insert running ok, and the second insert failing, I fired up my trusty copy of SQL Server Express 2012 and ran your question in my test area to verify it. This resulted in BOTH inserts failing. So, trusting software over wetware, I selected the answer of having both fail. Oh well!You probably executed thee two statements as a single batch. Since the error you get is raised at compile time and SQL Server always compiles the entire batch before starting execution, the ffect would be that none of the statements run. However, you get only a single error message, refering to a single statement. So the answer option "both statements will throw an error" is definitely not correct.
When I answered this question (yesterday), I knew that neither statement would execute when sent as a single batch, but since there was no answer option that described this behaviour, I knew the author intended the statements to be executed independently. (If the answer option had been phrased as "neither statement will execute successfully", I would indeed have been confused, and forced to make a 50/50 guess as to the author's intentions).
Hi Hugo,
I'm little bit confused with your explaination,
I knew that neither statement would execute when sent as a single batch.
I didn't understand this part. can you give some more explaination if possible with some sample code.
--
Dineshbabu
Desire to learn new things..
May 23, 2013 at 2:03 am
I'm little bit confused with your explaination,
I knew that neither statement would execute when sent as a single batch.
I didn't understand this part. can you give some more explaination if possible with some sample code.
[/quote]
If you submit a single batch with the two statements, the batch fails at compile time; you get an error message and no results.
If you select the two statements as two batches, then the first batch (with the first statement) executes successfully, and the second batch (with the second statement) fails at compile time.
May 23, 2013 at 3:10 am
Hugo,
In this scenario, eventhough if we run both insert statements as single batch, first statement will be executed successfully and we will get error only on second one rite? Correct me if i'm wrong or understanding in wrong direction.
--
Dineshbabu
Desire to learn new things..
May 23, 2013 at 3:24 am
Dineshbabu (5/23/2013)
Hugo,In this scenario, eventhough if we run both insert statements as single batch, first statement will be executed successfully and we will get error only on second one rite? Correct me if i'm wrong or understanding in wrong direction.
Did you try it?
As I already said in my previous reply, if you execute both in a single batch, the first statement will NOT execute. You'll get an error on the second, but because that is generated when parsing and compiling the batch, the first statement is not executed.
May 23, 2013 at 5:31 am
Hugo Kornelis (5/23/2013)
Dineshbabu (5/23/2013)
Hugo,In this scenario, eventhough if we run both insert statements as single batch, first statement will be executed successfully and we will get error only on second one rite? Correct me if i'm wrong or understanding in wrong direction.
Did you try it?
As I already said in my previous reply, if you execute both in a single batch, the first statement will NOT execute. You'll get an error on the second, but because that is generated when parsing and compiling the batch, the first statement is not executed.
I'm really very sorry.. Still i'm not clear.. can you give me the sample code ,one as single batch and another separate batch.
Then as per BOL
http://msdn.microsoft.com/en-IN/library/ms175502(v=sql.105).aspx
CREATE TABLE dbo.t3(a int) ;
INSERT INTO dbo.t3 VALUES (1) ;
INSERT INTO dbo.t3 VALUES (1,1) ;
INSERT INTO dbo.t3 VALUES (3) ;
GO
SELECT * FROM dbo.t3 ;
First, the batch is compiled. The CREATE TABLE statement is compiled, but because the table dbo.t3 does not yet exist, the INSERT statements are not compiled.
Second, the batch starts to execute. The table is created. The first INSERT is compiled and then immediately executed. The table now has one row. Then, the second INSERT statement is compiled. The compilation fails, and the batch is terminated. The SELECT statement returns one row.
In SQL Server 2000, the batch starts to execute and the table is created. The three INSERT statements are compiled one by one but are not executed. Because the second INSERT causes a compilation error, the whole batch is terminated. The SELECT statement returns no rows.
This says like first statement will be executed immediately after compilation.
--
Dineshbabu
Desire to learn new things..
May 23, 2013 at 6:53 am
Dineshbabu (5/23/2013)
I'm really very sorry.. Still i'm not clear.. can you give me the sample code ,one as single batch and another separate batch.
Sure. It's here: http://www.sqlservercentral.com/questions/T-SQL/98942/[/url].
First run the part where the two tables are created (the CREATE TABLE and the SELECT INTO). You may have to change upper- and lower-case, depending on case sensitivity of your instance.
Then, run the two INSERT statements as a single batch. You'll get an error. Use SELECT against both tables to verify that no row was inserted in either one.
Then, run the two INSERT statements individually (either by selecting and execution one at a time, or by typing "go" in between them). You'll, again, get an error. Use SELECT against both tables to verify that this time, a row was inserted in the first table, but in the second.
And for bonus points - drop both tables, and now execute the entire script (table creation + insert statements) at once. Now, the first insert IS executed. To explain this, google for "deferred name resolution".
May 23, 2013 at 8:43 am
Hugo Kornelis (5/23/2013)
Sure. It's here: http://www.sqlservercentral.com/questions/T-SQL/98942/[/url].First run the part where the two tables are created (the CREATE TABLE and the SELECT INTO). You may have to change upper- and lower-case, depending on case sensitivity of your instance.
Then, run the two INSERT statements as a single batch. You'll get an error. Use SELECT against both tables to verify that no row was inserted in either one.
Then, run the two INSERT statements individually (either by selecting and execution one at a time, or by typing "go" in between them). You'll, again, get an error. Use SELECT against both tables to verify that this time, a row was inserted in the first table, but in the second.
And for bonus points - drop both tables, and now execute the entire script (table creation + insert statements) at once. Now, the first insert IS executed. To explain this, google for "deferred name resolution".
Now I understood the entire story.
Still now I was trying as you mentioned in the bonus point (table creation + insert statements) at once. So while parsing time both tables was not exist but all the statements are syntax wise correct and parsing is finished sucessfully. so while execution, first statement will be executed successfully but second insert will fail at compilation.
If I run table creation as one batch and two insert statements as one batch, then while compiling the second (Insert) batch, error will be raised by second insert statement so entire batch will be failed at compilation time itself...
I hope now i'm correct track...
--
Dineshbabu
Desire to learn new things..
May 23, 2013 at 9:41 am
Dineshbabu (5/23/2013)
Still now I was trying as you mentioned in the bonus point (table creation + insert statements) at once. So while parsing time both tables was not exist but all the statements are syntax wise correct and parsing is finished sucessfully. so while execution, first statement will be executed successfully but second insert will fail at compilation.If I run table creation as one batch and two insert statements as one batch, then while compiling the second (Insert) batch, error will be raised by second insert statement so entire batch will be failed at compilation time itself...
I hope now i'm correct track...
Yes, you are on the right track!
When compiling a batch, SQL Server will check for existence of tables and columns. If a table exists but a column doesn't, you'll get an error. But if the table itself does not exist, "deferred name resolution" kicks in - SQL Server will assume (hope) that the table will be created in time and continue optimizing. Then, when that specific statement is executed, a new attempt is made to bind the table and column names, and now it must succeed.
Deferred name resolution for tables has advantages (like being able to create a stored procedure that uses a table that will be created later) and disadvantages (like limited column name checks, and no error message until run-time if you mistype a table name in a stored proc). There could also be situations where deferred name resolution for columns could be nice (e.g. a batch or stored proc that adds a column to a table and then uses it), as well as situations where it would cause problems. But that is a moot point, for the SQL Server development team decided, for whatever reason, to implement deferred name resolution for tables (and views), but not for columns.
May 23, 2013 at 10:25 pm
Thank you very much Hugo for spending your valuable time to make me understand.
--
Dineshbabu
Desire to learn new things..
May 24, 2013 at 9:55 am
EZ PZ
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
May 27, 2013 at 12:55 am
Nice question. 🙂
May 27, 2013 at 5:13 am
Hugo Kornelis (5/23/2013)
I'm little bit confused with your explaination,I knew that neither statement would execute when sent as a single batch.
I didn't understand this part. can you give some more explaination if possible with some sample code.
If you submit a single batch with the two statements, the batch fails at compile time; you get an error message and no results.
If you select the two statements as two batches, then the first batch (with the first statement) executes successfully, and the second batch (with the second statement) fails at compile time.[/quote]
how it is possible insert into second table even without # or @ (table variable or temp table) Symbol? what type of table is this?
Manik
You cannot get to the top by sitting on your bottom.
May 27, 2013 at 5:31 am
manik123 (5/27/2013)
how it is possible insert into second table even without # or @ (table variable or temp table) Symbol? what type of table is this?
A normal (permanent) table. It has been created by the SELECT ... INTO statement.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply