sql - Table-Valued function - Order by is ignored in output -

-

we moving sql server 2008 sql server 2012 , noticed our table-valued functions no longer deliver temp table contents in correctly sorted order.

code:

insert @customer         select customer_id, name,         case              when expiry_date < getdate() 1              when expired = 1 1              else 0             end         customer **order name**

in sql server 2008 function returns customers sorted name. in sql server 2012 returns table unsorted. "order by" ignored in sql 2012.

do have re-write functions include sort_id , sort them when called in main application or there easy fix??

there 2 things wrong original approach.

  1. on inserting table never guaranteed order by on insert ... select ... order by order rows inserted.
  2. on selecting sql server not guarantee select without order by return rows in particular order such insertion order anyway.

in 2012 looks though behaviour has changed respect item 1. ignores order by on select statement source insert

declare @t table(number int)  insert @t  select number master..spt_values order name

2008 plan

2008 plan

2012 plan

2012 plan

the reason change of behaviour in previous versions sql server produced 1 plan shared between executions set rowcount 0 (off) , set rowcount n. sort operator there ensure correct semantics in case plan run session non 0 rowcount set. top operator left of rowcount top.

sql server 2012 produces separate plans 2 cases there no need add these rowcount 0 version of plan.

a sort may still appear in plan in 2012 if select has explicit top defined (other top 100 percent) still doesn't guarantee actual insertion order of rows, plan might have sort after top n established rows clustered index order example.

for example in question adjust calling code specify order name if requires.

regarding sort_id idea ordering guarantees in sql server guaranteed when inserting table identity order these allocated per order by 

declare @customer table (   sort_id     int identity primary key,   customer_id int,   name        int,   expired     bit )  insert @customer select customer_id,        name,        case          when expiry_date < getdate() 1          when expired = 1 1          else 0        end   customer order  name

but still need order sort_id in selecting queries there no guaranteed ordering without (perhaps sort_id approach might useful in case original columns used ordering aren't being copied table variable)


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more