MSSQL Create Ordered Table from Sorted View

>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

Select allOpen 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.

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.

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

Select allOpen 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

Select allOpen in new window

with the diverse things sql can do there are some simple things it cant..

Hello Anthony.. pls note the "kludges" actually worked... I only went for 260......awesome.  Cheers and thankyou to you and Jim for your time.

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

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.

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.


"