SEO

Hi all,

I import MS Excel 2003 spread sheet in MS SQL Server 2000 through MS SQL
Server 2000 Enterprise Manager. In excel two sheets (sheet1 and sheet2) of
data is there. I imported first sheet data in first time in relevant table of
database and done second sheet of data in next time. My excel file have 2000
rows and 100 columns of data. All the data are imported in relevant
attributes cells in good manner. But the first 16 rows are sorted
automatically. I am trying to say that first row data is match with my excel
file. But second row data have gone to 7th row and 7th row have gone to 5th
row like that. Except that 16 rows all other data are matched with my MS
Excel 2003 file. I need the data sequence what I have in my excel file. What
is the problem occurred? How can I solve this?
Actually the problem occurred in first some rows so I found the mistake. If
it comes after 1000 rows how can I predict and rectify?
Please help me. I don't have more knowledge in MS SQL Server 2000.
Thanks,

With Regards,
bala.

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/

"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.

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" <bala@discussions.microsoft.com> wrote in message
news:986BE491-E58E-4AE8-A378-726439EC09E8@microsoft.com...
>
>
> "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.

I'm sorry, but what you're saying is a myth.
http://www.sqlmag.com/Articles/Artic...2886.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/

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/

"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.

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

http://www.sqlmag.com/Article/Articl...ver_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" <ML@discussions.microsoft.com> wrote in message
news:C39708B8-01AE-43D5-93A2-C8E3CC0B1B4F@microsoft.com...
> I'm sorry, but what you're saying is a myth.
> http://www.sqlmag.com/Articles/Artic...2886.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/

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" <phil.austin@clarity-integration.com> wrote in message
news:O8I9FWjaIHA.3400@TK2MSFTNGP03.phx.gbl...
> Interesting but only really makes sense when read with part II as well:
>
> http://www.sqlmag.com/Article/Articl...ver_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" <ML@discussions.microsoft.com> wrote in message
> news:C39708B8-01AE-43D5-93A2-C8E3CC0B1B4F@microsoft.com...
>> I'm sorry, but what you're saying is a myth.
>> http://www.sqlmag.com/Articles/Artic...2886.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/
>
>

Getting *close* is not getting *there*.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/