Row of data interchaged automatically when import from MS ..

"ML" wrote:

> Sets are unordered which means there is no inherent order in SQL objects
> (tables, views). For instance: the order of inserted rows is not preserved,
> and when retrieving rows from the table (or view) the order is not guaranteed
> unless the ORDER BY clause is used in the referencing (SELECT) query.
>
> If you need to preserve the order of your data then you must design your own
> method of doing so. The usual practice is either to use an existing column to
> sort the rowset or add a column (i.e. directly to the source or through
> staging) to store the original row order.
>
> In Excel you could just add a column and fill it with numbers designating
> the original row order.
>
> Do not rely on the IDENTITY column in a SQL table to preserve the order as
> the optimizer is free to choose the way rows are inserted, which means (as
> mentioned) before that the order may not be preserved.
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/

thank you very much. Your answer is very help full to me. I add one column
in both xl and in that sql table and give row no to it. Then import the data
from xl. Now the sequence is very correct with my xl file that is the row
hasn't changed it's order. But I have no permission to add one column in my
sql table. So after imported the data I deleted that column what I have added
extra in sql table then again all rows are changed it's order.
Actually one customized tool has given for entering data to that sql
database. If I entered the data through that tool then the row order may be
correct (what order I has entered) or we can't preserve the row order? please
help me.
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

Look at your data and your data model again. Perhaps there already is a
column (one or more) that you can use to determine the correct order of rows.

Remember: order is guaranteed only if you use the ORDER BY clause.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

Maybe I'm missing something here but the use of a clustered index can
enforce ordering on a table as the following demonstrates:

CREATE TABLE OrderTest
(

OrderKey int not null,

SomeOtherJunk varchar(50) not null

)

CREATE CLUSTERED INDEX IX_OrderTest_01 ON OrderTest(OrderKey)

INSERT OrderTest

VALUES(100, 'William')

INSERT OrderTest

VALUES(1, 'Phil')

INSERT OrderTest

VALUES(10, 'Rich')

INSERT OrderTest

VALUES(1000, 'Sam')

SELECT * FROM OrderTest

By using the DESC keyword in the CREATE INDEX statement you can reverse the
ordering.

If you are trying to ensure the sort order of a text field (char, varchar
etc) it gets a little trickier - ordering in the clustered index is also
dependant on things like the collation settings. On my install of SQL for
example, if you change the clustered index above so it applies to the
SomeOtherJunk field, it comes out in the general latin case-insensitive,
accent-sensitive ordering; Phil, Rich, Sam, William
(SQL_Latin1_General_CP1_CI_AS). A different collation may give you different
ordering (although in this case probably not).

--
Phil
http://www.clarity-integration.com
http://www.phil-austin.blogspot.com

"bala" wrote in message

>
>
> "ML" wrote:
>
>> Sets are unordered which means there is no inherent order in SQL objects
>> (tables, views). For instance: the order of inserted rows is not
>> preserved,
>> and when retrieving rows from the table (or view) the order is not
>> guaranteed
>> unless the ORDER BY clause is used in the referencing (SELECT) query.
>>
>> If you need to preserve the order of your data then you must design your
>> own
>> method of doing so. The usual practice is either to use an existing
>> column to
>> sort the rowset or add a column (i.e. directly to the source or through
>> staging) to store the original row order.
>>
>> In Excel you could just add a column and fill it with numbers designating
>> the original row order.
>>
>> Do not rely on the IDENTITY column in a SQL table to preserve the order
>> as
>> the optimizer is free to choose the way rows are inserted, which means
>> (as
>> mentioned) before that the order may not be preserved.
>>
>>
>> ML
>>
>> ---
>> Matija Lah, SQL Server MVP
>> http://milambda.blogspot.com/
>
> thank you very much. Your answer is very help full to me. I add one column
> in both xl and in that sql table and give row no to it. Then import the
> data
> from xl. Now the sequence is very correct with my xl file that is the row
> hasn't changed it's order. But I have no permission to add one column in
> my
> sql table. So after imported the data I deleted that column what I have
> added
> extra in sql table then again all rows are changed it's order.
> Actually one customized tool has given for entering data to that sql
> database. If I entered the data through that tool then the row order may
> be
> correct (what order I has entered) or we can't preserve the row order?
> please
> help me.
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

I'm sorry, but what you're saying is a myth.
http://www.sqlmag.com/Articles/ArticleID/92886/92886.html?Ad=1

The *only way* to retrieve a sorted set from a SQL object is to use the
ORDER BY clause. There are no alternatives, although some methods give that
false appearance. In fact, without using an ORDER BY clause the order in
which the rows are returned is completely unpredictable.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

"ML" wrote:

> Look at your data and your data model again. Perhaps there already is a
> column (one or more) that you can use to determine the correct order of rows.
>
> Remember: order is guaranteed only if you use the ORDER BY clause.
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/

Hi,

Thank you so much. Your answer is very very helpful to me. The article which
one you recommend is really superb. Thanks a lot.
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

Interesting but only really makes sense when read with part II as well:

http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html

"to guarantee consistency, in all cases besides when
NOLOCK or TABLOCK are specified, SQL Server scans the data
in index order by following the linked list."

If this wasn't the case the ASC and DESC keywords would be meaningless. So I
think you're still OK to use a clustered index as long as you're careful to
avoid query hints, even though there are more subtleties under the covers.

Phil.

"ML" wrote in message

> I'm sorry, but what you're saying is a myth.
> http://www.sqlmag.com/Articles/ArticleID/92886/92886.html?Ad=1
>
> The *only way* to retrieve a sorted set from a SQL object is to use the
> ORDER BY clause. There are no alternatives, although some methods give
> that
> false appearance. In fact, without using an ORDER BY clause the order in
> which the rows are returned is completely unpredictable.
>
>
> ML
>
> ---
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

Don't get me wrong btw - if you want to ensure ordering in a_query_use ORDER
BY. The closest you can get to logically ordering a table is by using a
clustered index.

Phil.

"Phil" wrote in message

> Interesting but only really makes sense when read with part II as well:
>
> http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html
>
> "to guarantee consistency, in all cases besides when
> NOLOCK or TABLOCK are specified, SQL Server scans the data
> in index order by following the linked list."
>
> If this wasn't the case the ASC and DESC keywords would be meaningless. So
> I think you're still OK to use a clustered index as long as you're careful
> to avoid query hints, even though there are more subtleties under the
> covers.
>
> Phil.
>
> "ML" wrote in message
>
>> I'm sorry, but what you're saying is a myth.
>> http://www.sqlmag.com/Articles/ArticleID/92886/92886.html?Ad=1
>>
>> The *only way* to retrieve a sorted set from a SQL object is to use the
>> ORDER BY clause. There are no alternatives, although some methods give
>> that
>> false appearance. In fact, without using an ORDER BY clause the order in
>> which the rows are returned is completely unpredictable.
>>
>>
>> ML
>>
>> ---
>> Matija Lah, SQL Server MVP
>> http://milambda.blogspot.com/
>
>
 >> Stay informed about: Row of data interchaged automatically when import from MS .. 

Getting *close* is not getting *there*.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
 >> Stay informed about: Row of data interchaged automatically when import from MS ..