|
|
Posted by Erland Sommarskog on 10/02/68 11:45
(drawnai@hotmail.com) writes:
> Creating an index on the chosen columns and then forcing the sliding
> update statement
> Update M Set @Fred = Col = @Fred + 1 From tabl M with (index=idxname)
> is the only way I've ever seen it never fail for multi columns.
This is one more "works most of the time, but don't cry foul if it
doesn't". I see no point of using methods of which the result is
not defined.
>> Note that this applies to INSERT only - it does *not* apply to SELECT
>> INTO.
>
> That's interesting, I've found the exact opposite, so long as a table
> scan is forced on the created table, I can't create temporary indexes
> on a temporary table in 2000 if it's created with insert into select,
> only with select into.
Here is a quick example:
select IDENTITY(int, 1, 1) AS ident, OrderID = OrderID + 0,
CustomerID, OrderDate, ShipVia
into Orders
from Northwind..Orders Order by CustomerID, OrderID
go
SELECT * FROM Orders ORDER BY CustomerID, OrderID
go
TRUNCATE TABLE Orders
go
INSERT Orders (OrderID, CustomerID, OrderDate, ShipVia)
select OrderID = OrderID + 0, CustomerID, OrderDate, ShipVia
from Northwind..Orders
Order by CustomerID, OrderID
go
SELECT * FROM Orders ORDER BY CustomerID, OrderID
go
DROP TABLE Orders
It may not be the indexes you were talking about, but the result of a
query should never be dependent of the indexes on the table.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|