Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!

I have a table below:
CREATE TABLE rsccategory
(
categoryid NUMERIC(2) IDENTITY(1,1),
categoryname VARCHAR(20) NOT NULL,
PRIMARY KEY(categoryid)
)
Then I do:
INSERT rsccategory(categoryname) VALUES('url')
INSERT rsccategory(categoryname) VALUES('document')
INSERT rsccategory(categoryname) VALUES('book')
INSERT rsccategory(categoryname) VALUES('software')
INSERT rsccategory(categoryname) VALUES('casus')
INSERT rsccategory(categoryname) VALUES('project')
INSERT rsccategory(categoryname) VALUES('disert')
Then SELECT * FROM rsccategory in ,I can get a recordeset with the
'categoryid' in order(1,2,3,4,5,6,7)
But If I change the table definition this way:
categoryname VARCHAR(20) NOT NULL UNIQUE,
The select result is in this order (3,5,7,2,6,4,1),and 'categoryname '
in alphabetic.
Q:why the recordset's order is not the same as the first time since
'categoryid' is clustered indexed.

If I change the table definition again:
categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTERED
the result is the same as the first time.
Q:'categoryname' is clustered indexed this time,why isn't in alphabetic
order?

I am a newbie in ms-sqlserver,or actually in database,and I do have
sought for the answer for some time,but more confused,Thanks for your
kind help in advance!

View 2 Replies


ADVERTISEMENT

Return Set Ordered By Physical Order Of Rows?

Nov 5, 2012

I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.

Specifically, something like:

Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"

I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.

(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).

View 6 Replies View Related

How To Load A Unicode File Into The Database In The Same Order As The File Order

Jul 23, 2005

The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---

View 15 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 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

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

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

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

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 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

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth



If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 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

SQL To Order Results In Predefined Order

Mar 27, 2008

I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?

View 4 Replies View Related

Force The ORDER BY To Be In Ascending Order??

Jul 5, 2007

I noticed the StockDate is not sorted in proper order, like ascending order...


Code:

select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc



I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...

The sample data is MM/DD/YYYY...

So, how do I get it ordered propery by Year, Month then Day??

View 2 Replies View Related

Specify Order For Select Results, Order By: Help!

Nov 17, 2006

Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View 1 Replies View Related

In-Order/Level Order Etc. Traversal Using CTE

Apr 16, 2008

Hi,

I have some hierarchical data in a table. Say for example:

Parent Child
------------------------
NULL 1

1 2

1 3

2 4

2 5

3 6

3 7

5 8

5 9

7 10

7 11

11 12

11 13


Now I want to be able to use CTE's to be able to traverse this tree in
1) level by level order 1,2,3,4,5,6,7,8,9,10....
2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13...

What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well.


with Tree (id)

as

(

select id from WithTest

where parent is null

union all

select a.id

from Tree b join WithTest a

on b.id = a.parent



)

select * from Tree


Any ideas? Thanks.

View 3 Replies View Related

Order ID For Latest Order For Every Customer

Jan 8, 2008

Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.

Any ideas?

Thanks,
Assaf

View 1 Replies View Related

The Order Of Insertion Of Rows Into Destination Is Not Same As The Order Of Incoming Rows

Dec 21, 2006

Hi ,

i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,

However it is same for smaller number of rows.

Please help ...i want the order to be same.

View 4 Replies View Related

Order Which Mirroring Database Get Up

Feb 7, 2007

Hi

I have 3 server configured with mirroring. When the automatic failover occur, I use alter event generate by Sql Agent to rebuild user account (problem witch
orphan user). My application use multiple database and I set up mirroring to multiple database. A question is the order which database get up after automatic failover. When event occur all database are ready to use, or maybe one database get up and sql agent send me event , secound database get up and sql agent send me event. What is the order?

View 3 Replies View Related

Select At Random Order From Database

Dec 23, 2005

Hi,
I need to select few items from sql database.I know for ORDER BY, but I need those items to be mixed in random order every time when they are returned from database. Those are the same items every time, just randomly mixed.
Can i do it with SQL, or I have to find another way (e.g. to mix them after sql returns them)?

View 1 Replies View Related

Restoring Database With Different Sort Order

Jun 13, 2002

Is this possible?

I try to restore SQL server database but program says i have different sort order.

My database files and backUps are made with 1033 (Unicode collation) and 185 (Sort order). The problem is that i can't install SQL -server 7 anymore with these options(Sort order is allways changing to 184).

If i try the change sort order with sp_configure stored procedure the server won't start anymore. (I tried single user mode, did not help.)

How could i restore my database? Please i could use some good tips.

Best regards,

Juise

View 1 Replies View Related

Can I Change The Sort Order Of A Database On Sql

Feb 5, 2007

Im new to sql server 2000 can i change the order in the database is it possible. If anybody help me how to do it that would be a great help.

Articles on various categories

View 2 Replies View Related

Restore Database Via Automated Order

Jul 20, 2005

Hi folks,I got a script which restores a database. It works fineif it is running in my Query Analyzer.It fails when I put this script in an automated schedule using theSQL agent.This is my scriptRESTORE DATABASE [RestoreTest]FROM DISK = N'E:sqlbakRestoreTest.BAK'WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERYand this is the error message from the scheduler (Sorry its in German)Executing as User dbo. Exclusiv access to database not possiblebecauseit is in use (which is not).. Rest may be clear ;-))Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank istnicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.[SQLSTATE 42000] (Fehler 3013). Fehler bei SchrittDo you have any suggestion to me ?

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

Database Sort Order / Collation

Jan 8, 2006

I have an O2 Xda IIs Pocket PC running PPC 2003 SE, WWE (Worldwide English) edition.

I want to create a database which contains all Chinese characters that have been mapped under the Unicode Standard. I already have a similar database on my desktop PC. On my desktop PC, I have used the collation Chinese_Hong_Kong_Stroke_90_CI_AS. However, on my Pocket PC, I do not see any Chinese or even Unicode sort orders. The best I get is "General".

I want to use the Chinese sort order to govern the way the characters are sorted (ie., characters with fewer strokes appear first).

How am I able to do this? / Is there anything extra that I may install to make additional collations available?

View 14 Replies View Related

Display Database Column In Alphabetical Order

Mar 12, 2007

Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From  Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
 SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn

View 1 Replies View Related

Updating Database Column After Order Has Been Made

Jun 7, 2004

i am building a shopping cart. I want to update the UNIT_IN_STOCK column in database after order have been submitted. i want to subtract the quanity value from the order made from the UNIT_IN_STOCK column in database. how would the sql statement be like?? i tried this but it didnt work. any suggestions??

CREATE PROCEDURE update_Products_By_name
(

@ProductName varchar,
@UnitInStock int

)
AS

UPDATE Products
SET UnitInStock=(UnitInStock-@UnitInStock)
WHERE ProductName = @ProductName
GO

View 1 Replies View Related

ID ORDER PROBLEM IN TABLES IN SQL7 DATABASE

Oct 25, 2001

I have a problem with the order of the numeric ID in several of my tables in one database.
Basically every quarter, using link table i update the figures in my table using basic cut and paste. However this will not work now as the table has become out of order due to the ID'S not beeing in numeric order.example
ID
1
7
23
24
15
16
2
3
8
34

I am desperate to get these collums back into order so i can paste my data in how do i do this. i want it to be like this

ID
1
2
3
4
5
ECT

PLEASE HELP

THANKS

View 2 Replies View Related

Order Of Database Maintenance Plans (SQL 7/2000)

Jul 20, 2005

I want to know if there is a "best-practice" for setting up DatabaseMaintenance Plans in SQL Server 7 or 2000. To be more specific, I wantto know the order in which I complete the tasks. Do I completeoptimization first, then integrity checks, then translog backup, thenfull backup??? OR is there a better order which should be used?Should I ALWAYS backup the transaction Log before I complete a fulldatabase backup, and if so, why??If someone can help, it would be great.....

View 2 Replies View Related

Server Must Shut Down In Order To Recover A Database

Oct 7, 2015

I know that there was huge tempdb usage and may be the disk was full but SQL server is not suppose to restart in this case.What could be the reason behind this? SQL server restarted and is currently up and running.We are using SQL server 2008 SP3

View 10 Replies View Related

DB Design :: Change Order Of Column In Database Tables

Jul 22, 2015

How to Change Order of Column In Database Tables

View 10 Replies View Related







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