July 17, 2026 at 12:00 am
Comments posted to this topic are about the item Implicit Conversions Cripple SQL Server
Sabyasachi Mukherjee
July 17, 2026 at 7:36 am
Thank for this simple view into the world of data type conversion. Would the same thing happen if comparing two varchar's of different width ? (example varchar(30) to varchar(255) )
----------------------------------------------------
July 17, 2026 at 8:42 am
Does this mean that if we are using .NET that every single text field has to be Unicode because .Net uses Unicode by default?
July 17, 2026 at 9:29 am
Just to take your example a little further, I've found that the same thing can happen with different numeric datatypes. For example, if one of the datatypes is one of the integer datatypes (int, smallint, etc.) and the other is one of the numeric datatypes (numeric, decimal, etc.) the exact same implicit conversion issue will occur. I'm sure this is due to the way the data is actually stored in the table and indexes. Simply converting or casting the data to the datatype that is in the index will solve the problem.
The best way to avoid the problem is to have strict datatype consistency. Basically if the data is an integer, make the datatype an integer. That is not always possible when you are querying data from different vendors or sometimes self developed databases that were created in different decades.
July 17, 2026 at 9:32 am
I've never had an issue comparing 2 varchars of different lengths, but it might be that comparing a nvarchar to a varchar would have at least a similar problem because those 2 datatypes are stored in different ways in the table and index.
July 17, 2026 at 12:23 pm
Does this mean that if we are using .NET that every single text field has to be Unicode because .Net uses Unicode by default?
That would be the wrong takeaway from this article. Just because .NET uses Unicode as a default doesn't mean you have no choice but to make your SQL data types Unicode. In fact, that would be a very poor design choice if Unicode is not required by the data being stored.
Unicode (any data type starting with N) takes up double the storage space needed for a non-Unicode data type. For instance, NVARCHAR(50) takes up 100 bytes of storage PLUS 2 bytes of overhead, for 102 bytes total. VARCHAR(50) takes up 52 bytes of storage (including the overhead). CHAR(50) only takes up 50 bytes of storage.
Always use the smallest data types possible to reduce database bloat and page splits. If a large data type is needed, absolutely use it. If Unicode is needed, then use it. But don't use it because it's default on .NET.
The better solution is to include conversions in your .NET code or ensure your connection strings don't bring back Unicode data. Connection string flag sendStringParametersAsUnicode=False helps with that.
July 19, 2026 at 4:41 pm
I don't mean to disparage anyone but I think this is exactly why those newer in the SQL Server arena need to work with and learn from those with years of experience before being assigned to design tasks. From all the hullabaloo about the massive data centers being constructed, I fear we are throwing away lots of storage and processing power and favoring speed of accumulation of data over good design. The whole legal aspect of data seems to be fostering a need to accumulate drastic detail instead of good summarization. Earlier on in my experience, as data aged it was appropriate to sacrifice detail for efficiency. For instance, we needed to know how many units of an item were sold monthly twenty years ago but didn't need to know exactly who bought. The need for more and more detail has drastically increased the importance of proper data formats for both storage and proper processing.
Rick
If you do a half-assed job of things, folks will ask 'why did this ass only do half the job?'
July 20, 2026 at 11:33 pm
"Index" was right there. 😉
I think you may have forgotten in your 1,000+ page textbook analogy that it has an index for finding things even faster than the table of contents.
July 21, 2026 at 12:01 pm
Not sure why the last comment was removed as SPAM given its relevance to the actual article.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply