Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSSQL Create Ordered Table from Sorted View

Posted on 2014-01-09
8
492 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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 143

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 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