August 20, 2018 at 12:13 pm
I have SQL Server 2012 and all my inputs got changed to Unicode. I have a week to change my SPs and views, and provide final results to the management.
The server encoding is Latin1_General_CI_AS and the DB also is Latin1_General_CI_AS. The server is reunning Windows Server 2012 R2.
What would be the easiest way to do this? Thanks.
August 20, 2018 at 1:31 pm
Changing the collation isn't needed. What you need to change are your VARCHAR declared columns and parameters to NVARCHAR declared columns and parameters.
August 21, 2018 at 3:43 am
Thanks for confirming Lynn.
August 21, 2018 at 4:42 am
Apart from that- change all your literal constants from 'String' to N'String'
_____________
Code for TallyGenerator
August 21, 2018 at 8:50 am
Sergiy - Tuesday, August 21, 2018 4:42 AMApart from that- change all your literal constants from 'String' to N'String'
Careful, Sergiy, someone (not me) out there may take exception with that.
August 21, 2018 at 5:14 pm
August 21, 2018 at 5:31 pm
tinausa - Monday, August 20, 2018 12:13 PMI have SQL Server 2012 and all my inputs got changed to Unicode. I have a week to change my SPs and views, and provide final results to the management.
The server encoding is Latin1_General_CI_AS and the DB also is Latin1_General_CI_AS. The server is reunning Windows Server 2012 R2.
What would be the easiest way to do this? Thanks.
Hopefully, you're not a bank because it's going to take longer than a week just to do the regression testing to make sure than nothing broke in the process. Like Sergiy and Lynn have also commented, it's not just the SPs and Views you need to change. You'll need to change all of the columns in the underlying data because if you play a VARCHAR column against NVARCHAR criteria, you'll get nothing but index scans thanks to data type precedence. To a lesser extent, you also want to avoid implicit conversions, which means that anywhere you use string literals, you should change them include the "N" prefix.
Good thing management gave you "so much" time, huh? Must've been one hell of conversation in the elevator or out on the golf course. Depending on how much data and how much code, I'd be tempted to declare it as an NFW project if given only a week. Hope they're prepared for some downtime.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2018 at 5:36 pm
p.s. And they did remember to plan for you to nearly double the size of all the databases and the backups, right? All that extra space is already available, right? And they did plan for the addition of memory to the system because they just cut the capacity of the memory nearly in half. They did all of that and ordered the extra hardware to handle all of that, right?
It would have been a whole lot easier and safer if they had identified only what needed to change. Of course, that would have actually taken some planning by folks that actually know what they're doing. I feel for you on this one.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2018 at 7:01 pm
Jeff Moden - Tuesday, August 21, 2018 5:31 PMtinausa - Monday, August 20, 2018 12:13 PMI have SQL Server 2012 and all my inputs got changed to Unicode. I have a week to change my SPs and views, and provide final results to the management.
The server encoding is Latin1_General_CI_AS and the DB also is Latin1_General_CI_AS. The server is reunning Windows Server 2012 R2.
What would be the easiest way to do this? Thanks.Hopefully, you're not a bank because it's going to take longer than a week just to do the regression testing to make sure than nothing broke in the process. Like Sergiy and Lynn have also commented, it's not just the SPs and Views you need to change. You'll need to change all of the columns in the underlying data because if you play a VARCHAR column against NVARCHAR criteria, you'll get nothing but index scans thanks to data type precedence. To a lesser extent, you also want to avoid implicit conversions, which means that anywhere you use string literals, you should change them include the "N" prefix.
Good thing management gave you "so much" time, huh? Must've been one hell of conversation in the elevator or out on the golf course. Depending on how much data and how much code, I'd be tempted to declare it as an NFW project if given only a week. Hope they're prepared for some downtime.
Actually, Jeff, looking back at the collation being used they may still use the indexes, just not as efficiently. It looks like they are using a Windows collation rather than a SQL collation.
August 21, 2018 at 9:11 pm
Lynn Pettis - Tuesday, August 21, 2018 7:01 PMJeff Moden - Tuesday, August 21, 2018 5:31 PMtinausa - Monday, August 20, 2018 12:13 PMI have SQL Server 2012 and all my inputs got changed to Unicode. I have a week to change my SPs and views, and provide final results to the management.
The server encoding is Latin1_General_CI_AS and the DB also is Latin1_General_CI_AS. The server is reunning Windows Server 2012 R2.
What would be the easiest way to do this? Thanks.Hopefully, you're not a bank because it's going to take longer than a week just to do the regression testing to make sure than nothing broke in the process. Like Sergiy and Lynn have also commented, it's not just the SPs and Views you need to change. You'll need to change all of the columns in the underlying data because if you play a VARCHAR column against NVARCHAR criteria, you'll get nothing but index scans thanks to data type precedence. To a lesser extent, you also want to avoid implicit conversions, which means that anywhere you use string literals, you should change them include the "N" prefix.
Good thing management gave you "so much" time, huh? Must've been one hell of conversation in the elevator or out on the golf course. Depending on how much data and how much code, I'd be tempted to declare it as an NFW project if given only a week. Hope they're prepared for some downtime.
Actually, Jeff, looking back at the collation being used they may still use the indexes, just not as efficiently. It looks like they are using a Windows collation rather than a SQL collation.
Collation won't matter if they change the "parameters" to NVARCHAR and those are played against VARCHAR columns. Since NVARCHAR has a higher precedence than VARCHAR, the whole column will need to be scanned as an implicit conversion before any comparison is done. This is one of the reasons why the use of ORMs can kill performance unless you make it so the ORM doesn't make all text based parameters NVARCHAR (or worse). Bottom line, if you have an NVARCHAR literal or variable that you're comparing a VARCHAR column to, you end up with a non-SARGable query. Gotta watch the datatypes and that's what Sergiy was talking about.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2018 at 11:24 pm
Jeff Moden - Tuesday, August 21, 2018 9:11 PMLynn Pettis - Tuesday, August 21, 2018 7:01 PMJeff Moden - Tuesday, August 21, 2018 5:31 PMtinausa - Monday, August 20, 2018 12:13 PMI have SQL Server 2012 and all my inputs got changed to Unicode. I have a week to change my SPs and views, and provide final results to the management.
The server encoding is Latin1_General_CI_AS and the DB also is Latin1_General_CI_AS. The server is reunning Windows Server 2012 R2.
What would be the easiest way to do this? Thanks.Hopefully, you're not a bank because it's going to take longer than a week just to do the regression testing to make sure than nothing broke in the process. Like Sergiy and Lynn have also commented, it's not just the SPs and Views you need to change. You'll need to change all of the columns in the underlying data because if you play a VARCHAR column against NVARCHAR criteria, you'll get nothing but index scans thanks to data type precedence. To a lesser extent, you also want to avoid implicit conversions, which means that anywhere you use string literals, you should change them include the "N" prefix.
Good thing management gave you "so much" time, huh? Must've been one hell of conversation in the elevator or out on the golf course. Depending on how much data and how much code, I'd be tempted to declare it as an NFW project if given only a week. Hope they're prepared for some downtime.
Actually, Jeff, looking back at the collation being used they may still use the indexes, just not as efficiently. It looks like they are using a Windows collation rather than a SQL collation.
Collation won't matter if they change the "parameters" to NVARCHAR and those are played against VARCHAR columns. Since NVARCHAR has a higher precedence than VARCHAR, the whole column will need to be scanned as an implicit conversion before any comparison is done. This is one of the reasons why the use of ORMs can kill performance unless you make it so the ORM doesn't make all text based parameters NVARCHAR (or worse). Bottom line, if you have an NVARCHAR literal or variable that you're comparing a VARCHAR column to, you end up with a non-SARGable query. Gotta watch the datatypes and that's what Sergiy was talking about.
Test it using windows collation shown in the original post, you may be surprised . I know I was when I did the testing myself.
August 22, 2018 at 12:45 pm
Thanks everyone, I know what I have to do. There is no short-cut, have to bite the bullet and change all varchar etc. to nvarchar, etc.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply