I was getting tons of issues related with a specific scenario on my current enterprise.
We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to 100, but some of our store procedure are having a problem, demonstrated by the code below:
CREATE TABLE #PRUEBA (texto1 char(30), condicion char(30))
INSERT INTO #PRUEBA VALUES ('PFI','1')
INSERT INTO #PRUEBA VALUES ('CFI','2')
SELECT
CASE
WHEN condicion= '1' THEN texto1
ELSE 'TFI'
END
FROM #PRUEBA
DROP TABLE #PRUEBA
Compatibility -> 80
'PFI ' -> char of 30 length
'TFI ' -> char of 30 length
Compatibility -> 90
'PFI ' -> varchar of 30 length
'TFI' -> varchar of 3 length
Compatibility -> 100
'PFI ' -> varchar of 30 length
'TFI' -> varchar of 3 length
Why did this happen?
Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?
Is there any solution to get the same result on compatibility 100 like compatibility 80?