SQLTeam.com | Weblogs | Forums

Order by Clause does not work with Select * into #temp statement in sql server 2008 express


#1

Order by Clause does not work with Select * into #temp statement in sql server 2008 express.

My script is like that.

SELECT BCODE+REPLICATE(' ',20-LEN(BCODE))'BCODE',DES+REPLICATE(' ',20-LEN(DES))'DESCRIPTION',RETAILS+REPLICATE(' ',5-LEN(RETAILS))'RETAIL'
,DISCOUNT+REPLICATE(' ',5-LEN(DISCOUNT))'DISCOUNT' Into TmpItems FROM (SELECT CAST(ART_REFNUMMER AS VARCHAR) 'BCODE'
,RTrim (SUBSTRING(ABT_TEXT, 1, 20)) 'DES',CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'RETAILS',CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'DISCOUNT'
From V_ARTIKEL, V_ABTEIL, V_ART_KOPF
Where ART_ABTEILUNG = ABT_NUMMER AND ART_WARENGR=AGR_WARENGR AND ART_ABTEILUNG=AGR_ABTEILUNG AND ART_TYPE=AGR_TYPE
AND ART_GRPNUMMER=AGR_GRPNUMMER AND ART_LIEFERANT=AGR_LIEFERANT ) T1
UNION
SELECT BCODE+REPLICATE(' ',20-LEN(BCODE))'BCODE',DES+REPLICATE(' ',20-LEN(DES))'DESCRIPTION',RETAILS+REPLICATE(' ',5-LEN(RETAILS))'RETAIL'
,DISCOUNT+REPLICATE(' ',5-LEN(DISCOUNT))'DISCOUNT'
FROM (SELECT CAST(AEA_EANCODE AS VARCHAR) 'BCODE',RTrim (SUBSTRING(ABT_TEXT, 1, 20)) 'DES',CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'RETAILS'
,CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'DISCOUNT' From V_ARTIKEL, V_ABTEIL, V_ART_KOPF, V_ART_EANS
Where ART_ABTEILUNG = ABT_NUMMER AND ART_WARENGR=AGR_WARENGR AND ART_ABTEILUNG=AGR_ABTEILUNG AND ART_TYPE=AGR_TYPE AND
ART_GRPNUMMER=AGR_GRPNUMMER AND ART_LIEFERANT=AGR_LIEFERANT) T1 Order By BCODE

SELECT [BCODE], [DESCRIPTION], [RETAIL], [DISCOUNT] from [dbo].[TmpItems]


#2

SELECT [BCODE], [DESCRIPTION], [RETAIL], [DISCOUNT] from [dbo].[TmpItems] order by BCODE.

Then its sorting.

But when I insert in tmp table why its not sorted?


#3

By definition, a table is an unordered set.

The only way to gaurantee the order of a resultset is to use ORDER BY in a SELECT statement.


#4

For anyone that finds formatted code easier to read... (I don't time this evening to break it all down, but there are is a whole host of "bad TSQL habits" on display here...

    SELECT
    	 BCODE = T1.BCODE + REPLICATE(' ', 20 - LEN(T1.BCODE)), 
    	 [DESCRIPTION] = T1.DES + REPLICATE(' ', 20 - LEN(T1.[DES])), 
    	 RETAIL = T1.RETAILS + REPLICATE(' ', 5 - LEN(T1.RETAILS)), 
    	 DISCOUNT = T1.DISCOUNT + REPLICATE(' ', 5 - LEN(T1.DISCOUNT))
    INTO TmpItems
    FROM (
    	SELECT
    		BCODE = CAST(ART_REFNUMMER AS VARCHAR), 
    		DES = RTRIM(SUBSTRING(ABT_TEXT, 1, 20)), 
    		RETAILS = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR), 
    		DISCOUNT = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR)
    	FROM
    		V_ARTIKEL, 
    		V_ABTEIL, 
    		V_ART_KOPF
    	WHERE
    		ART_ABTEILUNG = ABT_NUMMER
    		AND ART_WARENGR = AGR_WARENGR
    		AND ART_ABTEILUNG = AGR_ABTEILUNG
    		AND ART_TYPE = AGR_TYPE
    		AND ART_GRPNUMMER = AGR_GRPNUMMER
    		AND ART_LIEFERANT = AGR_LIEFERANT
    	) T1
    UNION
    SELECT
    	 BCODE = T1.BCODE + REPLICATE(' ', 20 - LEN(T1.BCODE)), 
    	 [DESCRIPTION] = T1.DES + REPLICATE(' ', 20 - LEN(T1.[DES])), 
    	 RETAIL = T1.RETAILS + REPLICATE(' ', 5 - LEN(T1.RETAILS)), 
    	 DISCOUNT = T1.DISCOUNT + REPLICATE(' ', 5 - LEN(T1.DISCOUNT))
    FROM (
    	SELECT
    		BCODE = CAST(AEA_EANCODE AS VARCHAR), 
    		[DES] = RTRIM(SUBSTRING(ABT_TEXT, 1, 20)), 
    		RETAILS = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR), 
    		DISCOUNT = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR)
    	FROM
    		V_ARTIKEL, 
    		V_ABTEIL, 
    		V_ART_KOPF, 
    		V_ART_EANS
    	WHERE
    		ART_ABTEILUNG = ABT_NUMMER
    		AND ART_WARENGR = AGR_WARENGR
    		AND ART_ABTEILUNG = AGR_ABTEILUNG
    		AND ART_TYPE = AGR_TYPE
    		AND ART_GRPNUMMER = AGR_GRPNUMMER
    		AND ART_LIEFERANT = AGR_LIEFERANT
    	) T1
    ORDER BY
    	BCODE;

SELECT
	ti.BCODE, 
	ti.[DESCRIPTION], 
	ti.RETAIL, 
	ti.DISCOUNT
FROM
	dbo.TmpItems ti;

#5

Thanks Jason for your advice.


#6

It's not a guarantee, but adding a clustered index to the table would (almost) always return the data in order, and, more importantly, it would always avoid a sort every time the query is run.

Of course you'll have to modify the code to add the clus index while keeping the table creation dynamic:

  1. create the table, using into, but with no rows
  2. add the clustered index
  3. load the table using insert ... with (tablock) to get minimal logging if possible

SELECT TOP (0) BCODE+REPLICATE(’ ‘,20-LEN(BCODE))‘BCODE’,DES+REPLICATE(’ ‘,20-LEN(DES))‘DESCRIPTION’,RETAILS+REPLICATE(’ ‘,5-LEN(RETAILS))‘RETAIL’
,DISCOUNT+REPLICATE(’ ',5-LEN(DISCOUNT))‘DISCOUNT’
Into TmpItems
FROM (SELECT CAST(ART_REFNUMMER AS VARCHAR) ‘BCODE’
,RTrim (SUBSTRING(ABT_TEXT, 1, 20)) ‘DES’,CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) ‘RETAILS’,CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) ‘DISCOUNT’
From V_ARTIKEL, V_ABTEIL, V_ART_KOPF
Where ART_ABTEILUNG = ABT_NUMMER AND ART_WARENGR=AGR_WARENGR AND ART_ABTEILUNG=AGR_ABTEILUNG AND ART_TYPE=AGR_TYPE
AND ART_GRPNUMMER=AGR_GRPNUMMER AND ART_LIEFERANT=AGR_LIEFERANT ) T1
--don't need the UNION here

CREATE CLUSTERED INDEX TmpItems__CL ON dbo.TmpItems ( BCODE ) WITH ( FILLFACTOR = 100 );

INSERT INTO dbo.TmpItems
SELECT TOP (0) BCODE+REPLICATE(’ ‘,20-LEN(BCODE))‘BCODE’,DES+REPLICATE(’ ‘,20-LEN(DES))‘DESCRIPTION’,RETAILS+REPLICATE(’ ‘,5-LEN(RETAILS))‘RETAIL’
,DISCOUNT+REPLICATE(’ ',5-LEN(DISCOUNT))‘DISCOUNT’
FROM (SELECT CAST(ART_REFNUMMER AS VARCHAR) ‘BCODE’
...rest same as before, including the union...