Solved

MSSQL Create Ordered Table from Sorted View

Posted on 2014-01-09
8
473 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Numeric sequence in SQL 14 38
SQL Help - 12 42
GRANT, REVOKE, DENY 4 20
SQL Date Retrival 7 31
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now