MSSQL Create Ordered Table from Sorted View

Is it possible?  The code below does not sort.  IF it is not possible what is the fastest way to achieve this without manual manipulation of insert statements?
use db
go 
SELECT  *
INTO    MS0Countries6
FROM    WAC.VWCCountries
ORDER BY CountryName asc

Open in new window

LVL 7
XGISAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
If you want views to sort, you need to add TOP 100 PERCENT to the SELECT line.
No, I am afraid that will not do it.  SSMS is smart enough to know that TOP 100 PERCENT means all there no need to sort.

Yes, you can use kludges like SELECT TOP 2147483647 ... ORDER BY, but please don't.

Answer:  Don't use an ORDER BY clause in a VIEW.  It does not make any sense.  Instead use ORDER BY on a view
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The code below does not sort.
Yeah this is one of those 'undocumented features' of SQL Server, which probably exists only to make passing certification exams more difficult.

If you want views to sort, you need to add TOP 100 PERCENT to the SELECT line.
SELECT  TOP 100 PERCENT *
INTO    MS0Countries6
FROM    WAC.VWCCountries
ORDER BY CountryName asc

Open in new window


>MSSQL Create Ordered Table from Sorted View
Not sure if you mean 'ordered return set' or 'ordered table'.  
Tables are not intended to contain a specific sort order, so most experienced developers would question why you want this.
0
 
XGISAuthor Commented:
Hello Jim Yes it is a base master table which i would like sorted as per the view. There are no FK dependencies and it is a fresh db tbl that has been edited. The only other solution i see is export to excel, and re-import the ordered table. Else generate the sql inserts in excel as ordered.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
XGISAuthor Commented:
The base view actually works fine.. only 259 records.. I usually use it without issue, despite the warning/error that always displays.

SELECT        TOP (100) PERCENT CountryName, REPLACE(CountryName, ' ', '_') + N'.png' AS CountryFlagImage, CountryTaxName, CountryTaxRate, CountryActive
FROM            WAM.MS0Countries
WHERE        (CountryActive = 1)
ORDER BY CountryName

Open in new window


If you mean perform order by ON then I thought that is what i used in the code create table from view code;

SELECT  TOP 100 PERCENT *
INTO    MS0Countries7
FROM    WAC.VWCCountries
ORDER BY CountryName asc

Open in new window


with the diverse things sql can do there are some simple things it cant..
0
 
XGISAuthor Commented:
Hello Anthony.. pls note the "kludges" actually worked... I only went for 260......awesome.  Cheers and thankyou to you and Jim for your time.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree on the above: you cannot really create a "sorted" table. sql server will spread the rows in the "heap" as it needs, and unless you have a ORDER BY in the query that fetches the data, you will not get the records in the "order" you want them.

if your concern is that exactly this order by is taking resources to actually sort the records, you should create a clustered index on the columns your want to order by, the fields being in exactly the same order as the order by specifies them.
note 1: you can only have 1 clustered index per table, so choose wisely
note 2: with the clustered index, you STILL have to use the ORDER BY in the final select to guarantee the sorted output
note 3: if your select does not return all the fields, but just some, you may want to create a normal index, with again just those tables you return, and the ones in the order by first. this would be a "covering" index (covering the relevant query), and the query will then not touch or read the table data blocks, but only the (sorted) index blocks.

hope this helps
0
 
Anthony PerkinsCommented:
pls note the "kludges" actually worked... I only went for 260......awesome
I did not say they did not work.  Unfortunately, they do (for now), they are just a very bad idea.
0
 
XGISAuthor Commented:
I wasn't actually sure what "kludges" were but it gave me an idea of something to try to achieve a goal.  The end resource was the list of countries in the world that align with a folder of images.  It is a common practice in this office to generate lookup/domain and some data tables in excel in alphabetic order since many lists are finite.  The purpose is to avoid (in some cases) having to order every view or implementation within the database or codes or code generators where domain values are ordered alphabetically for controls.

The 2nd stage of this cleansing process is done using "Generate Scripts" at the DB level which allows you to extract the "schema and data" from the newly generated ordered table.  Then to re-implement the index and primary key just place the following code at the start of the create table process;  "[ID] [int] IDENTITY(1,1) NOT NULL,"

Once new table is generated you delete the old and rename the new. Problem solved.  Also thankyou Guy for the fundamental notes that are also important to be understand in relation to this issue.


"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.