Default Sort Order Of The Data When Inserting From One Table To Another

May 19, 2008



I have a data load process that reads data from flat file into a Stage table in sql server. The order of the records in the stage table is exactly same as the order in the flat file. The identity column on the Stage table (which is also the clustered index) represents the exact line/row number of the data in the filat file. I perform some transformations on the data in the stage table and then insert it into a cumulative table which has a clustered index on an identity column again. When I do this, does the order of the data in the cumulative table be in the same order as the data in the stage table? Anyone, please let me know if I can rely on SQL server to maintain the same order or I will be forcing a sort order on the Identity column (clustered index) of the stage table when I insert the data into a cumulative table.


Thanks in advance!!

View 3 Replies


ADVERTISEMENT

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

How To Know What Is The Default Sort Order Of A Database

Feb 4, 2007

Hi all,

The server is sql server 2000, it has a database with collation SQL_Latin_General_CP1_CI_AS. How to know what is the default sort order of it? By the way, is it possible to use a query using query analyser to find the sort order of the db? Thanks in advance.

View 4 Replies View Related

Table Order When Inserting Data

Jul 23, 2005

I have to import data into a empty database, that has many tables.some tables have to be inserted first than others due to the foreignkeys.How do I find out the order of the tables that I have to insert datainto?Thanks in advance!Sam

View 6 Replies View Related

T-SQL (SS2K8) :: Sort Data By Using ORDER BY

Mar 7, 2013

When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?

1) Bubble Sort
2) Quick Sort
3) Merge Sort
4) Heap Sort
5) Insertion Sort

View 9 Replies View Related

SQL 2012 :: Temp Table Sort Order

Apr 3, 2013

Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.

View 5 Replies View Related

Dynamic Sort Column And Sort Order Not Working

Aug 7, 2007

I am trying to set sorting up on a DataGrid in ASP.NET 2.0.  I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure:   CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC  And I gotta tell you, that is ugly code, in my opinion.  What I am trying to do is something like this:  case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end

case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end  and it's not working at all, i get an error saying:  Incorrect syntax near the keyword 'case' when i put a comma after the end on line  5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak 

View 1 Replies View Related

How To Sort Table In Sql2000 With Ipaddress(format X.x.x.x) As Column With Nvarchar Datatype In Ascending Order

Apr 19, 2006

How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure

Ex:

Table: netComputers(3 rows)
Column Name: ipAddress (string data type)

ipAddress
0.0.18.1
0.1.1.2
0.0.0.1

Sql query : if I use the query

Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc

Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1

Where as expected result should be:

ipAddress
0.0.0.1
0.0.18.1
0.1.1.2

View 18 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Inserting Data Into Two Tables (Getting ID From Table 1 And Inserting Into Table 2)

Oct 10, 2007

I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
 Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
 This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!

View 3 Replies View Related

Sort Order Id. ?

Jul 20, 2000

Hi,
I am trying to restore .DAT file from dump. Its giving me error ..saying that the sort order id used for dumping was 42 not the default value 52.

How can i change the sort order to 42.
I am using sql server 6.5


Thanks
Srinivas

View 4 Replies View Related

Sort Order

Jan 13, 2000

We have a vendor who insists that sql server 7 be set to a binary sort order. Is there any real advantage to this as opposed to a dictionary sort?

View 2 Replies View Related

SORT ORDER

Sep 20, 2000

HOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?

HELP

View 1 Replies View Related

Sort Order ID's

Oct 18, 1999

I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful.

Thanks,
Rob.

View 2 Replies View Related

BCP And Sort Order

Jul 17, 1998

I need to copy the structure and data of an existing SQL 6.5 server to one with a different sort order. Normally, I would use the transfer tool to accomplish this, but the servers are on different networks. My question is, is BCP the answer? In other words, will the data copied via BCP from the sending server be able to be copied on the recieiving server. Also, is there a way to automatically generate the BCP statements for all tables? What I would really like is to be able to get at the scripts and data files created by the transfer tool.

View 1 Replies View Related

Sort Order

Jul 9, 2001

How can I set the sort order to 42, nocase when I install sql server 6.5
does Setup gives you some option to check to set sort order ?

View 1 Replies View Related

Sort Order

Mar 23, 2001

For SQL 2000,
Can I assign different sort order on the database level?

thanks!

View 2 Replies View Related

Sort Order Id 42 ?

Jul 24, 2000

Hi,
Can any one pls tell me what this sort order id 42 corresponds to and how its different from 52 ?
What options i need to check during installation for sort oreder id. ?

Thanks
Srinivas

View 1 Replies View Related

Sort Order

Apr 22, 2008

How do I create a sort order column in a view? I could use Row_Number() in 2005 but unfortunately I need to create a sort order column in a 2000 view and the View is not letting me to use an ORDER BY.

Any inputs?

Prakash.P
The secret to creativity is knowing how to hide your sources!

View 14 Replies View Related

Sort Data Table Smallest To Largest

Dec 30, 2014

i have table in sql and every month i am appending new data in this table, but i want to sort data Culumn Name "account_no' from smallest to largest, and whenever i append new data to this table it auto sort every time,

View 1 Replies View Related

Sort Order On Insert

Jun 22, 2001

Are there any techniques I can use to specify the sort order on an insert statement.

I want to insert data from tableA to tableB in a certain order .

I know I can do it with a cursor, but I'm sure theres a better way

Pargat

View 2 Replies View Related

Changing The Sort Order

Jul 18, 2001

Hi,

I want to change the "dictionary Sort Order" in SQL Server 7.0. What will be the impact? What is the safe way to do it?

Under MSSQL7INSTALL there is a file called instcat.sql. Should I run this file to change the catalog settings?

Any help is appriciated.

Thanks
Sri

View 2 Replies View Related

What Is The Best Sort Order Choice ?

Jul 5, 2000

In Sql6.5 we use binary sort order for a best performance (I think). I hear that it should be highlighted that the case for binary sort order being the fastest method of sorting and searching a database no longer applies at SQL 7.0
Is it right and why ????

View 1 Replies View Related

Can Not Attach -- Different Sort Order !!!

Sep 21, 2000

Help ...

Does this mean that I have to re-install SQL Server7 on the target server? For some reason, all except one (the source in this case) of our servers were set up taking the defaults (or at least they are all the same). I can not change the source.

Any advise, before I kill myself

Judith

View 2 Replies View Related

Upgrade SQL 6.5 To 7.0 With Different Sort Order

Jan 26, 2001

Hi,

I am trying to upgrade peoplesoft database which is in SQL server 6.5 with Dictionary sort order to SQL Server 7.0 Binary sort order. SQL Server Upgrade wizard fails because of different sort order. If anyone knows about upgrading SQL Server 6.5 with Dictionary sort order to SQL 7.0 with Binary sort order, would be appreciated.

Thanks,
Mohana

View 1 Replies View Related

Unicode Sort Order

Nov 17, 1998

Does anyone have any experience handling mixed-character data in Unicode, especially in how to handle sorting it?

For example, if I have a customer database with records from all over the world, with customer names in Chinese, Russian, Swedish, Arabic & Hebrew, and I want to generate a list of all customers who have not upgraded to the latst version yet, it will be sorted in the sort order of the locale I defined at DB installation. Say, in this case, US English. How do the Chinese, Arabic, Swedish, etc. characters get sorted?

I'm interested to know if this is something that people are grappling with out there, or whether it's a non-issue. I think what's probably needed is a special Unicode mixed-character sort order, encompasing all the Unicode codepoints and sorting them in an order that's as usable as possible for the general user. (e.g. Latin characters case-sensitive, including accented characters, then Asian characters in stroke order, etc.) Or maybe serveral different mixed-character sort orders would be the thing... Any feedback would be appreciated.

View 1 Replies View Related

Sp_attach_db And Sort Order

Dec 6, 2001

Hi,

I'm doing some work for a client who has not been backing up
his databases. After a server crash he has been left with
the .MDF and .LDF files. I have managed to successfully
use sp_attach_db on all but two databases. When attempting
to use sp_attach_db I get the following error message :

1> sp_attach_db @dbname = N'CSGDEV', @filename1 = N'e:SQL_DataDirDataCSGDEV_Data.MDF', @filename2 = N'e:SQL_DataDirDataCSGDEV_Log.LDF'
2> go
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.
Warning: sort order 51 in database differs from server sort order of 52.
Warning: Unicode comparison flags 196608 in database differs from server
Unicode comparison flags of 196609.
Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1
Could not attach the database because the character set, sort order, or Unicode
collation for the database differs from this server.

I'd appreciate any ideas. I'm not a SQL Server expert (heck, I'm not even
a Windows expert...) as you can probably tell.

Thanks and cheers,
Deeran

View 1 Replies View Related

Change Sort Order In 6.5

Aug 25, 1999

Hi,

Is there any way to change the sort order from case sensitive to case insensitive in SQL Server 6.5 without having to rebuild the master db and all users db's.

thanks in advance.

View 1 Replies View Related

Incorrect Sort Order.

Jan 25, 2007

I have select query that has text field in select list. When I use order by clause it gives me incorrect sort in result.

We are running SQL2K with service pack 4.

I find BUG #: 470536 on Microsoft site and it suggest applying service pack 4.

Is anyone has similar problem?

Thanks in advance.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved