Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

ORDER BY non-sequential number sequence Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 2:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
steve 50602 (1/13/2013)
If I wanted the sort order changeable via the website, then I would definitely use something like CHARINDEX, as it seems a pretty flexible solution. I'd probably just put the concatenation and CAST inside the CHARINDEX function, rather than using a CROSS APPLY.
Might be but definitely not from query performance perspective.as it doesnt get any help when you put any function on column

like
WHERE left (column_name,1) = 'L' ORDER BY CHARINDEX(statusId,'32451')  



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1406615
Posted Monday, January 14, 2013 5:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 15,646, Visits: 28,027
It's worth mentioning that having a function like that on the ORDER BY can prevent index use. Depending on the size of the data set and the complexity of the rest of the query, while that function provides flexibility, it could seriously kill performance. A structural solution such as having a sorting column is a fairly standard approach and not (in my, admittedly, not worth anything more than anyone else's, opinion) an example of over-normalization.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1406665
Posted Monday, January 14, 2013 5:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 1,076, Visits: 6,454
WolfgangE (1/14/2013)
....
The disadvantages using the cross-apply-method I see are performance and maintenance:
If the number of rows of the outer select increases the query will slow down as the apply-operator does the subselect for any outer row....


Using a single CROSS APPLY valued to the row (i.e. with no table reference) to replace a single calculation referenced in two or more parts of the same query is quite likely to improve performance. Performance change, a very slight change in either direction, will scale in a linear manner with rowcount. It's not a subselect. It's most likely to show in the plan as a compute scalar.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1406672
Posted Tuesday, January 15, 2013 10:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Here's another alternative you could try:


IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[StatusID] [int] NULL,
PRIMARY KEY (ID))

INSERT INTO #TempTable
SELECT 'George','Washington','1'
UNION ALL SELECT 'John','Adams','2'
UNION ALL SELECT 'Thomas','Jefferson','3'
UNION ALL SELECT 'James','Madison','4'
UNION ALL SELECT 'James','Monroe','5'
UNION ALL SELECT 'Andrew','Jackson','6'
UNION ALL SELECT 'John Quincy','Adams','7'

--Original order
SELECT * FROM #TempTable ORDER BY ID

--Order by this string order
DECLARE @SortOrder VARCHAR(50)
SET @SortOrder = '3,2,4,5,1,6,7'

SELECT
ID
,FirstName
,LastName
,StatusID
FROM
#TempTable AS tt
INNER JOIN
dbo.DelimitedSplit8K(@SortOrder,',') AS dsk
ON tt.StatusID = dsk.Item


For those who don't have the DelimitedSplit8K function already:


CREATE FUNCTION [dbo].[DelimitedSplit8K]
(
@pString VARCHAR(8000)
,@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

WITH E1(N)
AS (
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
UNION ALL
SELECT
1
), --10E+1 or 10 rows
E2(N)
AS (
SELECT
1
FROM
E1 a
,E1 b
), --10E+2 or 100 rows
E4(N)
AS (
SELECT
1
FROM
E2 a
,E2 b
), --10E+4 or 10,000 rows max
cteTally(N)
AS (
SELECT TOP (ISNULL(DATALENGTH(@pString),0))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
),
cteStart(N1)
AS (
SELECT
1
UNION ALL
SELECT
t.N + 1
FROM
cteTally t
WHERE
SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1)
AS (
SELECT
s.N1
,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)
FROM
cteStart s
)

SELECT
ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)
,Item = SUBSTRING(@pString,l.N1,l.L1)
FROM
cteLen l ;

GO


 
Post #1407359
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse