Hello, code...

最近在測試一個展序號的Stored Procedure

其中有一段是在根據使用者定義規則，產生流水碼之前的固定碼

EX:

    使用這設定 [3碼固定碼](ABC) + [YYWW](年周) + [5碼流水號]

    那這段SP理論上會先把[3碼固定碼](ABC) + [YYWW](年周)找出來，最後再加上流水號

    擷取這段CODE如下:

Create Table #SN_Rule(No varchar(1),colName varchar(20),Code varchar(20))

Insert into #SN_Rule
select  '1' as No,'FIXCODE1',FIXCODE1 as Code
FROM Release_SN_RULE WHERE CUSTOMER='1234'
union
select '2' as No,'FIXCODE2', FIXCODE2 as Code
FROM Release_SN_RULE WHERE CUSTOMER='1234'
union
select '6' as No,'DateCode1', DateCode1 as Code
FROM Release_SN_RULE WHERE CUSTOMER='1234'
union
select '7' as No,'DateCode2', DateCode2 as Code
FROM Release_SN_RULE WHERE CUSTOMER='1234'
union 
select '9' as no,'Ext_Seq','' as code
union
select '8' as no,'Seq','' as code

Insert into #SN_Rule
select '3' as No,'PreCODE1', 'PMOQTW' as Code

Insert into #SN_Rule
select '4' as No,'PreCODE2', '' as Code
	 
Insert into #SN_Rule
select '5' as No,'PreCODE3', '' as Code

Update #SN_Rule Set Code=dbo.[GetDateTimeFormat](GETDATE(),Code,'XXXX') where no in(6,7) 
update #SN_Rule set code=rtrim(code) 
Update #SN_Rule Set Code='' where no not like '[368]'

Create Table #Order_SN_Rule(OrderNo  int,Formatno int,Code varchar(20))

select charindex(no,'9368') [OrderNo],no,Code FROM #SN_Rule where charindex(no,'9368')>0  order by charindex(no,'9368')        

Insert into #Order_SN_Rule
select charindex(no,'9368'),no,Code FROM #SN_Rule where charindex(no,'9368')>0  order by charindex(no,'9368')

SELECT * FROM #Order_SN_Rule

Declare @Code varchar(20)=''

select @Code=@Code+Code from #Order_SN_Rule
where orderno between (select orderno from #Order_SN_Rule  where formatno=9) and (select orderno from #Order_SN_Rule  where formatno=8) 

SELECT @Code


DROP TABLE #SN_Rule, #Order_SN_Rule

預期的結果是會產生固定碼: PMOQTW1606

但是在測試環境跑出來卻變成: 1606PMOQTW

可是拿到正式環境測試卻又沒有問題，且比對collation也是一樣的

於是加了一段 Script 比較寫入 Temp Table 前後的比較，發現寫入 Temp Table 後的排序變的怪怪的

select charindex(no,'9368') [OrderNo],no,Code FROM #SN_Rule where charindex(no,'9368')>0  order by charindex(no,'9368')        

Insert into #Order_SN_Rule
select charindex(no,'9368'),no,Code FROM #SN_Rule where charindex(no,'9368')>0  order by charindex(no,'9368')

SELECT * FROM #Order_SN_Rule

找了很久都找不出原因...

由於只有在測試環境有這種問題，因此開始比對兩個DB的屬性設定是否有差異

最後找到原因是我把測試環境的COMPATIBILITY LEVEL設為2012，而正式環境是2005

把COMPATIBILITY LEVEL設回2008或2005後這個問題就解決了

USE [master]
GO
ALTER DATABASE [TEST] SET COMPATIBILITY_LEVEL = 110  --110=2012, 100=2008, 90=2005
GO

 

比較了正式環境和測試環境的執行計畫，有發現差異，但仍無法理解造成此差異的原因。希望各位高手不吝賜教...

正式:

測試: