SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


order of rows during bulk insert


order of rows during bulk insert

Author
Message
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706

I should have said "If there is a clustered index on the table it will return the data in the order of the clustered index"


That's not always true however. Just because a table has a clustered index it does not mean a query without ORDER BY will return rows in that clustered index order - even if there are no other indexes involved. If SQL Server chooses to do an Advanced Scan or Unordered scan then rows may not be returned in the clustered index order.
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3742 Visits: 2903
Not on a simple select *

Unless possibly there is a problem with the statistics on the clustered index.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 8586
There have been countless examples on these forums of cases where OPs expected data to be returned in clustered index order when no 'order by' was specified. It's not guaranteed to happen. Most of the time it does, but you can't EVER rely on it. The optimizer is free to return rows in any oder that miximizes query efficiency when no 'order by' is specified. A new version of SQL Server can break current behavior at any time. If you want something in a particular order, the only way to guarantee it is to use 'order by'. So in the OPs case here, he should add the data he wishes to order by to the rows before he loads them into the server. Updating them once they're in the server won't guarantee he'll get what he expects.



And then again, I might be wrong ...
David Webb
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706
sjimmo (6/25/2010)
Not on a simple select *


Are you saying that the IAM page chain will not be used to do an unordered page traversal if you specify a "simple select *" but it will be if you specify some subset of columns? I never heard that before and it sounds a bit unlikely. Perhaps it is true but I don't think that behaviour is documented. Or have you seen it documented somewhere?

What is for sure is that even for your SELECT * query the clustered index scan will be identified as unordered in the execution plan ("unordered = True"), therefore in principle I understand the "official" line to be that SQL Server is free to perform an unordered scan. Even if it does not do so today, it could do if there is an engine change in some hotfix or service pack. Therefore to say that the scan will be ordered without giving any warning or qualification is perhaps a bit reckless, especially if we are talking about undocumented behaviour. I just think it's worth qualifying your claim a bit more so that anyone reading this can make up his own mind on whether he wants to take that risk.
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3742 Visits: 2903
I may be a bit slow on this, and am not going to be as adament on it as Dave, who has been having this very same arguments for a few years now with many others but:

Doing a simple select * from <tablename> in which the table has no indexes of any kind will perform a table scan, and return the results in the order they are found on the various pages based upon the where clause unless there is a sorting clause to the sql command. Even then, it will perform a table scan, and do the sorting after it has recieved the result set.

When there is a clustered index, then the select will do a index scan on the clustered index and return the records in the order that it finds them based upon the clustered index.

I agree that the way that the engine works can be changed at any time by MS in a SP or a new release, but now you are really stretching into the realm of fantasy. The question had to do with a specific version, and action based upon the current systems and how they work today not in the distant future.

Dave, I guess we will have to agree to disagree on this, as has happened to you many times in the past. (BTW, you can see everything I have demonstrated and said by running showplans against each query.)

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706
[b]When there is a clustered index, then the select will do a index scan on the clustered index and return the records in the order that it finds them based upon the clustered index.


Agreed. But a clustered index scan can be unordered. If you look at the execution plan you will see that in the plan. If the server chooses to do an allocation order scan of the clustered index then the data may not be returned in the index key order.

Don't believe me. Here's Conor Cunningham's blog on the same topic. Conor is a lead developer on the SQL engine team.
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

I recommend you look up the chapters on indexing and execution plans in the Inside SQL Server series of books published by Microsoft which explain about unordered scans. Or just read about unordered scans at Microsoft.com. This topic has been written about extensively by Kalen Delaney, Itzik Ben Gan (who wrote the Inside SQL series) and others. Like me they have also spent a lot of time trying to correct myths about clustered indexes determining the order of a query.
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706
sjimmo (6/25/2010)
BTW, you can see everything I have demonstrated and said by running showplans against each query


See attached.
Attachments
orderedfalse.jpg (23 views, 97.00 KB)
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9753 Visits: 10569
I just recently had to load data in from a flat file, and had to ensure the order (well, I actually had to be able to get the first row with the column headers).

Solution:
BCP into a temp table with an identity column, using a format file to skip the identity column.

Example:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp 
CREATE TABLE #temp (RowData varchar(8000), RowID INT IDENTITY PRIMARY KEY CLUSTERED)

-- load the data file
PRINT 'BULK INSERT'
BULK INSERT #temp
FROM 'c:\<mypath>\<MyCSVFile>.csv'
WITH (FORMATFILE='C:\<mypath>\<MyXMLFormatFile.xml')



--Format file:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="8000"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RowData" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>



Hope this helps

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Luiz Fernando Braz
Luiz Fernando Braz
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 105
Hello Wayne,

Your solution is perfect !! Thank you very much !!
You saved my day !
awez1411
awez1411
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 2
Hi Everybody

I have some problem with the Bulk insert in Sql server 2008r2 i have 1 sample.txt file with 500000 records and all records are in Proper Sequence and insert into the database table like this.

Create Table sample (serialno varchar(max))

//----- Table Created--------//

Bulk insert sample From 'C:\Deno Range\sample.txt' WITH(KEEPIDENTITY, FIELDTERMINATOR = ',')

//----- Bulk Insert Complited ------//

view all data of table

Drop Table sample

//----- Table Deleted -----//

Do this process with same file more than 3 or 4 time Continuously

My Files data is like that

000001,56589587458585,20,03-18-2014

to

500000,56589587458585,20,03-18-2014
in proper sequence

its properly work 3 times but at 4 or 5th time when i use bulk insert command all data are successfully inserted into table but it lost the sequence.

after 1136th row 201545th row added and break all the sequence

Please Give me Proper Solution for that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search