Re: temp db indexing in SQL2k
From: Erland Sommarskog (sommar_at_algonet.se)
Date: 03/11/04
- Next message: Louis Davidson: "Re: highlight a row in MSFlexGrid"
- Previous message: Tom Moreau: "Re: how to use IF Exists in Sql to Drop a Table?"
- In reply to: MSNews: "temp db indexing in SQL2k"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 11 Mar 2004 20:37:33 +0000 (UTC)
[posted and mailed, please reply in news]
MSNews (davehart@telco.co.uk) writes:
> I have an SP on SQL7.0 which works correctly and does the following:
>
> CREATE TABLE #tmp (Phone_Number varchar(15), Post_Code_Area varchar(3))
> INSERT INTO #tmp (Phone_Number, Post_Code_Area) SELECT PhoneNo,
> Post_Code_Area FROM MyTable
> CREATE INDEX [IDX_PN] ON [#tmp]([PHONE_NUMBER]) ON [PRIMARY]
> UPDATE MyOtherTable SET phoneNo= phone_number FROM #tmp (INDEX=IDX_PN)
> DROP TABLE #tmp
>
> When I run this on SQL2K it complains that the index IDX_PN does not
> exist... if I move the create index line to before the insert it works.
> The problem is that inserting into the indexed table is slower than
> creating the index afterwards so this is not the preferred option...
>
> Does anyone know why this problem occurs and if I can get round it,
> preferably without using a permanent table?
This is why it is happening:
SQL Server has something called deferred name resolution. When SQL Server
parses a stored procedure to create it, or build a query plan and it
finds that a statement refers to a table that does not exist, SQL Server
abandons analysis of that statement completely. (But before it starts
looking for names, it has already validated syntax and use of variables.)
But if all tables in a query exists, then all other objects in the
query must exist. For instance, you cannot refer to a non-existent column.
As for index hints, there is an inconsistency as will see.
When you create a procedure like in Greg's repro:
create proc proc_test
as
create table #t1 (c1 varchar(15))
insert into #t1 (c1) values ('123')
create index idx_pn on #t1(c1)
select * from #t1 (index(idx_pn))
drop table #t1
go
exec proc_test
go
drop proc proc_test
go
SQL Server defers the resolution of #t1 when you create the procedure.
Likewise it does so when you invoke the procedure the first time.
When it hits the INSERT statement it finds that this statement does
not have a plan, because of the deferall, so SQL Server recompiles
the procedure, and it recompiles the entire procedure. But as the
index does not exist at this point, the compilation of SELECT statement
fails.
What is strange here, is that if you do:
CREATE TABLE nisse (a int NOT NULL)
go
CREATE PROCEDURE hint_sp AS
SELECT * FROM nisse WITH (INDEX not_here)
go
SQL Server accepts this, but execution fails of course. To be consistent,
SQL Server should barf about the missing index here as well.
When you say that it works in SQL7, I am a little puzzled, because when I
tried Greg's repro on SQL7 SP2, the result was the same as on SQL 2000.
Maybe your scenario with linked servers changes the scene here,
Possible workarounds:
1) Use dynamic SQL for the update.
2) Put the UPDATE in a second stored procedure, called from this procedure.
3) Create the index directly. If you make the index clustered, this might
improve the speed of the UPDATE statement, and in such case it's
probably better to create the index first.
4) Skip the table, and update from the linked server directly. It may not
be as scary as you believe.
By the way, your SQL 2000 server had version 8.00.194. This is the RTM
version. You should upgrade to SP3 to get several important fixes, not
the least for security. This particular issue will work the same though.
-- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Louis Davidson: "Re: highlight a row in MSFlexGrid"
- Previous message: Tom Moreau: "Re: how to use IF Exists in Sql to Drop a Table?"
- In reply to: MSNews: "temp db indexing in SQL2k"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
