473,585 Members | 2,529 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Alter table weird bug?

I have created the following test SQL code to illustrate a real
problem I have with some SQL code.

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Jon Combe'
, 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo ) VALUES ( 'Bill
Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

When I run this SQL via the query analyser I get the errors:-

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'CustomerNo'.

It appears the SQL Server is trying to "pre-parse" the query and
hasn't picked up on the ALTER TABLE line that adds this column and so
complains that it doesn't exist. However it doesn't end there.

If I then run this query a line at a time by highlighting each line in
the query analyser and running it all works (not unexpected). However
then dropping the table and then running the full SQL code once more
(I.E. not highlighting each line), it then works as expected. I assume
it was somehow remembering "state" in my session so closed and
re-started the Query Analyser with the same result that the code does
now work.

However changing the table name to something new brings back the
errors once more. Can anyone explain what is going on here? I am
dropping the table before re-running the code each time.

I'm using SQL Server 2000 if that makes a difference.

Thanks.
Jon.
Jul 20 '05 #1
7 5386
Not a bug. As you rightly suspected, SQL Server tries to validate your
code first by attempting to resolve any column or object references to
existing objects and columns. If the table doesn't exist at compile time
then the resolution of that table's columns is deferred until the
statement executes. However, if the table exists then you will receive
an error if the referenced columns don't also exist. A workaround is to
put a GO batch separator after the ALTER TABLE statement so that the
second batch will be compiled independently.

For this reason among others it is good practice always to separate DDL
(Data Definition Language, such as CREATE and ALTER table statements)
and DML (Data Manipulation Language, such as SELECT, UPDATE, INSERT,
DELETE statements). I would create separate scripts for your DDL and DML
statements.

--
David Portas
SQL Server MVP
--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #2
David Portas wrote:
Not a bug. As you rightly suspected, SQL Server tries to validate your
code first by attempting to resolve any column or object references to
existing objects and columns. If the table doesn't exist at compile time
then the resolution of that table's columns is deferred until the
statement executes.


David,

Thanks, this does make sense however the table in my statement did not exist
at compile time (otherwise the create table line would fail), so that is
why I cannot understand why I get that message.

Thanks.
Jon.
Jul 20 '05 #3
Jon Combe (jc****@acxiom. co.uk) writes:
Thanks, this does make sense however the table in my statement did not
exist at compile time (otherwise the create table line would fail), so
that is why I cannot understand why I get that message.


Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland Sommarskog wrote:

Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.


Thanks Erland,

Why doesn't it spot that I've added a column and then either defer the last
three statements again, or recognise that the column I'm using does now
exist? I'd expect that sort of behaviour from it although I take the point
made earlier that it's best to split the statements with a GO between them.

Also given the way it is compiling the code it doesn't explain why if I run
these statements one line at a time, then drop the table, reload Query
Analyser and re-run this full batch of code it works yet running the full
batch before the table has ever existed generates an error. That just seems
weird, so if anyone can explain why I'd be interested to hear it! The
database should be in the same state in both cases, but as the behaviour is
different it can't be.

Jon.
Jul 20 '05 #5
Jon Combe wrote:
Erland Sommarskog wrote:

Your batch gets compiled several times. First you have:

CREATE TABLE JCTable ( CustomerName varchar(50) )
ALTER TABLE JCTable ADD CustomerNo int
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Jon Combe' , 1 )
INSERT INTO JCTable ( CustomerName , CustomerNo )
VALUES ( 'Bill Gates' , 1 )
UPDATE JCTable SET CustomerNo = 2 WHERE CustomerName = 'Jon Combe'
SELECT * FROM JCTable

On the first compile, all but the first statement is deferred. Once the
table has been created, SQL Server hits the ALTER TABLE, finds that the
statement is deferred, and recompiles the batch. This time, all statements
are scrutinized, since once all tables in a query exist, SQL Server per-
form full checks on the query.

Thanks Erland,

Why doesn't it spot that I've added a column and then either defer the last
three statements again, or recognise that the column I'm using does now
exist? I'd expect that sort of behaviour from it although I take the point
made earlier that it's best to split the statements with a GO between them.

Also given the way it is compiling the code it doesn't explain why if I run
these statements one line at a time, then drop the table, reload Query
Analyser and re-run this full batch of code it works yet running the full
batch before the table has ever existed generates an error. That just seems
weird, so if anyone can explain why I'd be interested to hear it! The
database should be in the same state in both cases, but as the behaviour is
different it can't be.

Jon.


I have to confess that this behaviour, if it is "normal", is surprising
to me. Is this the way the product is expected to behave?

Thanks.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)
Jul 20 '05 #6
Jon Combe (jc****@acxiom. co.uk) writes:
Why doesn't it spot that I've added a column
No, you haven't added a column. You get the error the table has been
created, but the ALTER TABLE statement has not been executed. Since the
ALTER TABLE statement was deferred, SQL Server recompiles the batch,
and it recompiles the batch, because that the lowest granularity for
compilation in SQL 2000. And since at this point the columns does not
exist, the compilation fails.
and then either defer the last three statements again,
SQL Server could defer compilation because of unknown columns too, but it
has quite some ramifications, and I am very happy that unknown columns is
reason for deferral. It is bad as it is. To wit, when you create a stored
procedure, you want to be alerted if you have misspelled a table name of a
column name. Due to deferred name resolution, you don't get alerts for
misspelled table names, but since SQL Server checks the query once all
tables are there, you do at least sometimes get alerts about misspelling
column names. (And in our in-house load tool, I scan the code for table
references to find the missing tables, and also perform some tricks to
get SQL Server check queries with temp tables too.

But, there is light at the end of the tunnel. Your script runs as you
expected in SQL 2005. This is because SQL 2005 is able to recompile a
single statement in a batch, so the entire batch is not recompiled at
once.
Also given the way it is compiling the code it doesn't explain why if I
run these statements one line at a time, then drop the table, reload
Query Analyser and re-run this full batch of code it works yet running
the full batch before the table has ever existed generates an error.
That just seems weird, so if anyone can explain why I'd be interested to
hear it! The database should be in the same state in both cases, but as
the behaviour is different it can't be.


This has to do with cached plans. The plans are in the cache, even if
the table is dropped. (This makes sense with temp tables.) Yes, it's
certainly a bit confusing, but for the situations for which the behaviour
is designed, it gives the best result.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
Erland Sommarskog wrote:
Also given the way it is compiling the code it doesn't explain why if I
run these statements one line at a time, then drop the table, reload
Query Analyser and re-run this full batch of code it works yet running
the full batch before the table has ever existed generates an error.
That just seems weird, so if anyone can explain why I'd be interested to
hear it! The database should be in the same state in both cases, but as
the behaviour is different it can't be.


This has to do with cached plans. The plans are in the cache, even if
the table is dropped. (This makes sense with temp tables.) Yes, it's
certainly a bit confusing, but for the situations for which the behaviour
is designed, it gives the best result.


So basically with the query I have, whether I get an error or not (when the
database is in the same state) depends entirely on what has been run
previously? That doesn't seem very satisfactory!

Jon.

Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
9817
by: Dylan Nicholson | last post by:
Seems that Oracle 9.2 (using MS ODBC driver) requires extra parentheses when adding multiple columns to a table: ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)) vs ALTER TABLE MyTable ADD MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)
1
11495
by: Lannsjo | last post by:
I need to change my primary key column type from smallint to int. I have tried: ALTER TABLE livegroup MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT; But get an error message certainly since my id-column is primary key and references other tables as well. How can I come around this problem? Need help /Martin
0
1492
by: anzenews | last post by:
Hello! I have a weird problem... The application I use issues this SQL from time to time: alter table t modify id int(6) unique not null auto_increment; The problem is that this SQL adds a key on id every time it is run! mysql> show keys;
4
5867
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
10
26083
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not null references another, FK_LASTLYOID bigint not null references lastly, unique (FK_OTHEROID,FK_ANOTHEROID))
2
31469
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1, column2), );
7
6971
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get into the GUI because it is hard to describe in text. First of all what is the purpose of ALTOBJ()? This procedure was created mostly for ISVs who...
1
3487
by: vasilip | last post by:
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table containing both xml and spatial data fields I can't seem to find a way to alter the table I have created a table containing an id, xmldata field and a...
11
4073
by: raylopez99 | last post by:
Keep in mind this is my first compiled SQL program Stored Procedure (SP), copied from a book by Frasier Visual C++.NET in Visual Studio 2005 (Chap12). So far, so theory, except for one bug (feature?) below. At some point I'm sure I'll be able to laugh about this, akin to forgeting a semi-colon in C/C++, but right now it's frustrating (time...
0
7908
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7836
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7950
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8212
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6606
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5389
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3835
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2343
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1447
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.