Solved

MSSQL Create Ordered Table from Sorted View

Posted on 2014-01-09
8
482 Views
Last Modified: 2014-01-09
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

0
Comment
Question by:XGIS
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39768823
>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
 
LVL 7

Author Comment

by:XGIS
ID: 39768862
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39768866
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 7

Author Comment

by:XGIS
ID: 39768909
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
 
LVL 7

Author Closing Comment

by:XGIS
ID: 39768938
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768946
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39769013
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
 
LVL 7

Author Comment

by:XGIS
ID: 39770012
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question