It only takes a minute to sign up. SQL Server Developer Center. Why does the distance from light to subject affect exposure (inverse square law) while from subject to lens does not? CONVERT_IMPLICIT may affect "CardinalityEstimate" in a query plan choice. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? If you tried with temp table solution, Can you show me the table structure for Temp table? In the second paragraph of my edit above I used CAST to explicitly convert to varchar so I wouldn't think that a conversion would be taking place? And there is a warning in the plan regarding TRY_CAST.. Type conversion in expression (CONVERT_IMPLICIT(nvarchar(10),[e]. Still looking for an elegant work-around :). I'm obviously missing something here. [ReferralSource] + ''), https://connect.microsoft.com/SQLServer/feedback/details/695556/new-type-conversion-in-expression-warning-in-sql2012-to-noisy-to-practical-use, http://sathyadb.blogspot.in/2012/08/sql-server-various-observations-on.html, http://msdn.microsoft.com/en-us/library/ms190309.aspx. Please note - code is same on SQL 2008 and 2014 still why its warning not showing on 2008 ? #temp_Tablep2 ON e.status = p2.PART, ON cast(e.status Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. The two data types are not the same. The partitioning column is a datetime. The reason there is no "type conversion / cardinality estimate" warning in this query: is because there is no cardinality estimate. "Type conversion in expression (CONVERT (nvarchar (50), [Union1008],0)) may affect "CardinalityEstimate" in query plan choice" with [ReferralSource] as ( select [ReferralSource] from [PlatformDM_Stage1]. Each table under the view stores one month of data. Type conversion may affect CardinalityEstimate, TSQL question (From:SQL Server Database Engine), column data type VARCHAR of lower precedence has been converted to. it seems like too much of a hack. Why does the USA not have a constitutional court? Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Type conversion in expression (CONVERT_IMPLICIT (varchar (12), [test]. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. SELECT t.FLIGHT#, t.flightReference, PNR_NUMBERS, MIN(td.DEP_DATE_TIME) AS departDate, MAX(td.ARR_DATE_TIME) AS arrivalDate, CASE WHEN PNR_NUMBERS IS NULL OR PNR_NUMBERS = '' THEN 'Paper . Is there an index on that column that the plan is not using? Refer this link Example 3.1 & Eample 3.2 - Should teachers encourage good students to help weaker ones? It is assumed that you have varchar datatype assigned for PART column. Are there conservative socialists in the US? ), Yes, I am one SQL 2014 SP3 CU4 and have added trace flag in startup parameter and restart service and enabled it globally BUT still query is slow and yellow warning still showing in plan. I have the following table definitions that are used in a query below that is giving me a query hint. Hello, To be clear, that warning isn't always pointless. I maintain an archive database that stores historical data in partitioned views. We intentionally force an implicit conversion by using a LIKE operator for an integer value. Is such a complex filter confusing the optimizer? To learn more, see our tips on writing great answers. Improves MS SQL Database Performance Is your query performing slowly? The following is a post related to this issue: Comment . Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates? Large scale of database and data cleansing Site design / logo 2022 Stack Exchange Inc; user contributions licensed under CC BY-SA. I am expecting that up until the last Compute Scalar step everything should be in native data-type and Asking for help, clarification, or responding to other answers. How to show first row group by part id and compliance type based on priorities of Document type? Again, I would go with changing the datatype if possible. So you're just replacing an implicit conversion with an explicit one, which still warrants a warning. The warning goes away if you change col1 to VARCHAR(100). Welcome to Microsoft Q&A!Please see this MS Doc about Cardinality Estimation. The warning is informational, to help you understand any issues you might notice with cardinality estimation. Take a look at this. Are there conservative socialists in the US? [STATUS],0)) may affect "CardinalityEstimate" in query plan choice e.status = varchar (10) p2.part = nvarchar (100) Please, assist. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? What is the point in that? By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. You can see here how we can fix this issue by changing the datatype: Simple Explanation of Data Type Precedence. Should I give a brutally honest feedback on course evaluations? Why does conversion - which should be done after the UNION and DISTINCT sort, affect the CardinalityEstimate? Does balls to the wall mean full speed ahead or full speed ahead and nosedive? Regarding your example where you cast this input into the LEFT function, notice that the warning has changed slightly from CONVERT_IMPLICIT to just CONVERT: Type conversion in expression (CONVERT(varchar(12),[test]. Is it appropriate to ignore emails from a student asking obvious questions? Making statements based on opinion; back them up with references or personal experience. Type conversion may affect "CardinalityEstimate" in query plan choice Peter Shilovich SSC Eights! The SELECT query has this warning, and the warning is legit. Hello, Why is the eastern United States green if the wind moves from west to east? The key words in that message are "warning" (not "error") and "may" (not "will"). The linked duplicate states that computed columns were the issue for that user. [STATUS],0)) may affect "CardinalityEstimate" in query plan choice, Hello, no cardinality should be affected. LEFT OUTER JOIN PART_LIST p2 ON e.status = p2.PART AND p2.PART_TYPE = 'ABC' Type conversion in expression (CONVERT_IMPLICIT (nvarchar (10), [e]. be a constant, variable, or column. convert(nvarchar(50), SQL Server Developer Center. Alberto Morillo Please support me on Patr. Where is it documented? = 'ABC' --This would filter and move the data to temp table, http://sqlblog.com/blogs/rob_farley/archive/2013/09/23/string-length-and-sargability.aspx, Large scale of database and data cleansing, https://connect.microsoft.com/SQLServer/feedback/details/695556/new-type-conversion-in-expression-warning-in-sql2012-to-noisy-to-practical-use. Toggle Comment visibility. I can't quite understand why does this warning come up, since cardinality should have been determined way before the last Compute Scalar step. Is there any reason on passenger airliners not to have a physical lock between throttles? The warning is informational, to help you understand any issues you might notice with cardinality estimation. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? Comment. Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. varchar or nvarchar. Your query is going for scan because I've tried cast to varchar in the original query: LEFT(cast(test.col1 as varchar(12)), 2) which returns the same warning. The best thing is to change your table structure. Just because the computed column is persisted, does not mean the optimizer will choose to use the persisted value. MOSFET is getting very hot at high frequency PWM, Counterexamples to differentiation under integral sign, revisited, If he had met some scary fish, he would immediately return to the surface. I assume that for the CardinalityEstimate warning, if the type conversion is used to filter the results somehow, then that . e.status= But that still doesn't explain to me why select LEFT(30500600, 2) by itself doesn't cause a warning if I'm passing an int directly to the LEFT() function. http://sqlblog.com/blogs/rob_farley/archive/2013/09/23/string-length-and-sargability.aspx Why is apparent power not measured in Watts? TRY_CAST is a safe way of finding out if the data can be cast to a type. Check where the. [#MyFunIntTable]. [dbo]. What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice. Both of them indicate that your query will run slower. Name of a play about the morality of prostitution (kind of). --First filter and insert into temp table as below, Select * fromPART_LIST wherePART_TYPE Please support me on Patreon: https://www.patreon.com/roelvande. [UserData] union select [ReferralSource] from [PlatformDM_Stage1]. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. Type conversion in expression try_cast may affect cardinality estimate in query plan choice, UNION ALL implicit conversion for VARCHAR(MAX) column. . MS SQL Consulting: bit columns generating warnings - how can a cast in the field list affect the cardinality estimate? To learn more, see our tips on writing great answers. (CONVERT_IMPLICIT(varchar(12),[test]. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? If you have extra questions about this answer, please click "Comment". How does legislative oversight work in Switzerland when there is technically no "opposition" in parliament? If I run the same above without pulling from a table: 30 is returned as expected and there is no warning in the Execution Plan. The warning is informational, to help you understand any issues you might notice with cardinality estimation. The result is a TRIVIAL plan, with no cost-based choices, so there is no warning about plan choices. But that was related to collation and not with computed columns. Answer : Type conversion in expression may affect "CardinalityEstimate" in query plan choice? Browse other questions tagged, Start here for a quick overview of the site, Detailed answers to any questions you might have, Discuss the workings and policies of this site, Learn more about Stack Overflow the company, I mention that I did that in my first paragraph of my. Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Ideally it should be the same data types and I dont think to have two different datatypes for the similar data as you are doing a joining between tables. Sign in. How to set a newcommand to be incompressible by justification? Asking for help, clarification, or responding to other answers. Any other solution ? For further investigation, we would need to see the query plans, both from SQL 2008 and SQL 2014. How could my characters be tricked into thinking they are on Mars? https://connect.microsoft.com/SQLServer/feedback/details/695556/new-type-conversion-in-expression-warning-in-sql2012-to-noisy-to-practical-use, Type conversion in expression (CONVERT(varchar(10),[p2]. there is no more any warnings, the query plan is here. Type conversion in expression may affect CardinalityEstimate - on a computed column? Scan means you are reading all data from your table. This is very much expected behavior if you have different datatypes to join together. But that was related to collation and not with computed columns. What does that mean in this context? Connect and share knowledge within a single location that is structured and easy to search. This forum has migrated to Microsoft Q&A. Type conversion in expression (CONVERT(varchar(12),[test]. Type conversion in expression (CONVERT(varchar(10),[t]. If I run the same above without pulling from a table: select LEFT(30500600, 2) 30 is returned as expected and there is no warning in the Execution Plan. Tried both the methods. http://sathyadb.blogspot.in/2012/08/sql-server-various-observations-on.html, Data type precedence chart - Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates? Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it? Type conversion in expression (CONVERT_IMPLICIT(varchar(12),[test]. Which leads me to the second part of the answer regarding implicit conversion to string types using the CONCAT function which is also the same for the LEFT function. data type, except text or ntext, that can be implicitly converted to http://msdn.microsoft.com/en-us/library/ms190309.aspx. The best I've been able to come up with is doingconvert(nvarchar(50), [ReferralSource] + '') - but At what point in the prequels is it revealed that Palpatine is Darth Sidious? Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o]. [SessionData] ) select CampaignKey = convert (nvarchar (50), [ReferralSource]) United States (English) "CardinalityEstimate" in query plan choice. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. With this change, the optimizer is able to simplify the query to remove the unnecessary join. It's that simple. Sign in. What's the \synctex primitive? But that was related to collation and not with computed columns. [FLTCD_FLT_DATE],112)) may affect column data type VARCHAR of lower precedence has been converted todata type NVARCHAR of higher precedence . Ready to optimize your JavaScript with Rust? Cooking roast potatoes with a slow cooked roast. Type conversion in expression (CONVERT_IMPLICIT (nvarchar (10), [e]. . LEFT OUTER JOIN PART_LIST p2 ON e.status = p2.PART AND p2.PART_TYPE = 'ABC' Type conversion in expression (CONVERT_IMPLICIT (nvarchar (10), [e]. United States (English) Connect and share knowledge within a single location that is structured and easy to search. Here is a small demonstration for the same: First create a sample data: 1 2 3 4 5 6 7 8 9 CREATE TABLE FirstTable (ID INT, Col VARCHAR(100)) GO INSERT INTO FirstTable (ID, Col) SELECT 1, 'First' UNION ALL SELECT 2, 'Second' UNION ALL The CE updates for levels 120 and above incorporate updated assumptions and algorithms that work well on modern data warehousing and on OLTP workloads. rev2022.12.9.43105. [UserData] union select [ReferralSource] from [PlatformDM_Stage1]. Hope this helps. Should teachers encourage good students to help weaker ones? dateadd in the where clause - how can I avoid a full scan in this particular situation? Try that just for testing purpose. But we know why, of course. Every plan that uses type conversions, either implicit or explicit, will show that warning. [EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice. A warning message tells you that the query execution engine cannot optimize a query plan that uses a column of one type as though it were a column of a different type. Type conversion in expression may affect cardinality estimate in query plan Ask Question Asked 7 months ago Modified 7 months ago Viewed 807 times 0 I have a warning in my Execution plan that I can not get rid of. The only way to prevent this expansion and match-back is to enable trace flag 176 on suitable versions. Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan. Please help with it. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? Do non-Segwit nodes reject Segwit transactions with invalid signature? (And I take for granted that you are on SQL 2014 SP3 CU4. In many cases, this is for the better, but there are also situations where it can cause plan regressions, and this is what you may be seeing. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. When I remove the MIN and MAX columns from the select, there is no more any warnings. Does a 120cc engine burn 120cc of fuel a minute? I tried a lot and read google search but not resolved . I would appreciate your thoughts on why this happens and what's the correct way of formulating this query to avoid the warning. 5. Getting Type Conversion Error for Select statement. Are defenders behind an arrow slit attackable? I've made an Minimal, Reproducible Example here: Disconnect vertical tab connector from PCB. Is it possible to hide or delete the new Toolbar in 13.1? It's the same issue. The Table design can't be modified. SQLCoffee.com. Later on, it may, or may not, match expressions back to the available persisted computed columns. A big change in SQL 2014 was the introduction of the Cardinality Estimator. I think the explanation is as simple as that, since the warning is more about the potential cardinality problems (rather than just the simple existence of the type conversion). Regards, Remote DBA Services: The best answers are voted up and rise to the top, Not the answer you're looking for? Getting Type Conversion Error for Select statement. But that was related to collation and not with computed columns. Alberto Morillo [col1],0)) may affect "CardinalityEstimate" in query plan choice. Since the MDS staging table destination expects nvarchar, I convert it in my source query. http://sqlblog.com/blogs/uri_dimant/ How did muzzle-loaded rifled artillery solve the problems of the hand-held rifle? [STATUS],0)) may affect "CardinalityEstimate" in query plan choice e.status = varchar (10) p2.part = nvarchar. [PART],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression ([e].[STATUS]=CONVERT(varchar(10),[p2]. Can a prospective pilot be negated their certification because of too big/small hands? Type conversion in expression (CONVERT(varchar(30),) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression may affect "CardinalityEstimate" in query plan choice, sql-server-general sql-server-transact-sql. Type conversion in expression may affect CardinalityEstimate - on a computed column? Current Visibility: Visible to the original poster & Microsoft, Viewable by moderators and the original poster, https://www.sqlshack.com/implicit-conversion-in-sql-server. I'm still unsure about the warning. Thanks for contributing an answer to Database Administrators Stack Exchange! Are there breakers which can be triggered by an external signal and have to be reset by hand? Scans are basically are of three types - Table scan, Clustered Index Scan and Non Clustered Index Scan. What I mean is that is just a warning. The warning is always going to show, until you change your code to remove the conversion. When the optimizer encounters the expression, it may add a warning to the plan. Regards, It may be an issue in the CE that was fixed since the original release of SQL 2014. How to smoothen the round border of a created buffer to make it look more natural? Comment Show . convert character_expression. I've tried changing the col1 datatype declaration to varchar and that clears up the error. This would resolve your issue. Is there any way I can get rid of this warning? There are no computed columns in my example, so that isn't the issue. Trying to convert a. Type conversion in expression (CONVERT_IMPLICIT (nvarchar (50), [tempdb]. SQLCoffee.com. asnvarchar(100)) = p2.PART, ON Query to list all the databases that have a specific user, Year and Month aggregation in same Pivot table in SQL Server, SQL Server Query for Searching by word in a string with word breakers. SQLCoffee.com. [STATUS],0)) may affect "CardinalityEstimate" in query plan choice. ,112)) may affect "CardinalityEstimate" in query plan choice. It only takes a minute to sign up. character_expression can Trying to figure out the best way to re-write a query to avoid this Warning, "Type conversion in expression (CONVERT(nvarchar(50),[Union1008],0)) may affect "CardinalityEstimate" in query plan choice". You can upload the plans on http://www.brentozar.com/pastetheplan. It's a constant expression being passed into a deterministic function - guaranteed to only return one "row." [STATUS],0)) may affect "CardinalityEstimate" in query plan choice e.status = varchar (10) p2.part = nvarchar (100) Please, assist. Help us identify new roles for community members. Take this example: This puts 280,192 rows into a temp table. character_expression can be of any data type NVARCHAR of higher precedence . That warning likely means it is not using an index on that field, and wasn't in SQL 2008 either, just not reporting it. This forum has migrated to Microsoft Q&A. Is it cheating if the proctor gives a student the answer key by mistake and the student doesn't report it? Sed based on 2 words, then replace whole line with variable. Can virent/viret mean "green" in an adjectival sense? Help us identify new roles for community members, Performance Improvement went wrong in Production, worked fine in Test. Try to convert the NVARCHAR to CHAR before joining them, or change the data type at the table level to match on both tables. [col1],0)) may affect "CardinalityEstimate" in query plan choice So you're just replacing an implicit conversion with an explicit one, which still warrants a warning. "CardinalityEstimate" in query plan choice, Please note the computed column flightReference, When I run the following query I get a warning on the select: "Type conversion in expression (CONVERT (nvarchar (50), [Union1008],0)) may affect "CardinalityEstimate" in query plan choice" with [ReferralSource] as ( select [ReferralSource] from [PlatformDM_Stage1]. Hope this helps. Site design / logo 2022 Stack Exchange Inc; user contributions licensed under CC BY-SA. OK, then we have checked the preliminaries. Is an expression of character or binary data. Ready to optimize your JavaScript with Rust? Thanks for contributing an answer to Database Administrators Stack Exchange! The query plan is here, When I remove the MIN and MAX columns from the select, Alberto Morillo XPath fn:data in sql server causes Type conversion in expression may affect "CardinalityEstimate" in query plan choice-sql-server XPath fn:data in sql server causes Type conversion in expression may affect "CardinalityEstimate" in query plan choice-sql-server I have an xml variable that contains a set of ids that I want to lookup in a table. WARNING TYPE CONVERSION MAY AFFECT CARDINALITY ESTIMATE. [UserData] union select [ReferralSource] from [PlatformDM_Stage1]. It's the same issue. How to connect 2 VMware instance running on same Linux host machine via emulated ethernet cable (accessible via mac address)? Making statements based on opinion; back them up with references or personal experience. you have data in PART_LIST.part column that is more than 10 characters in length. PART_TYPE What's going on here? What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice, Inserting with implicit type conversion causes warning for cardinality estimates, Execution Plan Warnings cardinality xml data type. Referral source is a varchar(50) column in both staging tables and this query is being used in SSIS dataflow to load up new values into MDS. JOIN In your test environment, try changing the COMPATIBILITY_LEVEL to 110, and you will see the warning disappear. Regards, Visit Microsoft Q&A to post new questions. The whole warning seems kind of bogus, I wouldn't make a big deal about it if I didn't have traces that look for the Warnings and raise alerts, which is why I want to write my queries so they don't raise any Warnings My question still stands - is there a more proper way to avoid the warning than doingconvert(nvarchar(50), If the conversion isn't a supported implicit conversion, an error is returned. [PART],0)) may affect "SeekPlan" in query plan choice. Connecting three parallel LED strips to the same power supply. How can I use a VPN to access a Russian website that is banned in the EU? Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total. SQL Server estimates there are 28,019.2 matching rows (10% of the table), when in fact there are only 2,156 matching rows (<1% of the table). By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. I have a query that is running very slow so I started looking into the Execution Plan. MS SQL optimization: MS SQL Development and Optimization table. When using SQL Server, you may see the warning message Type conversion in expression may affect "CardinalityEstimate" in query plan choice. It's the same issue. [col1],0)) may affect "CardinalityEstimate" in query plan choice If I run the same above without pulling from a table: select LEFT (30500600, 2) 30 is returned as expected and there is no warning in the Execution Plan. The best answers are voted up and rise to the top, Not the answer you're looking for? Otherwise, use the CAST function to explicitly This is actually expected. Still the same error. Type conversion in expression may affect CardinalityEstimate in query plan choice? "Type conversion in expression (CONVERT (nvarchar (50), [Union1008],0)) may affect "CardinalityEstimate" in query plan choice" with [ReferralSource] as ( select [ReferralSource] from [PlatformDM_Stage1]. Please note that I am not a DBA but a coder. Business Intelligence, Someone have the same issue with you, please take a look at the feedback and vote it: That's how the new cardinality estimator behaves. I have the following table definitions that are used in a query below that is giving me a query hint, Type conversion in expression No IMPLICIT_CONVERSION warning in this query plan, Inserting with implicit type conversion causes warning for cardinality estimates, Effect of coal and natural gas burning on particulate matter pollution. Sudo update-grub does not work (single boot Ubuntu 22.04). We had restore database from SQL 2008 to SQL 2014, All code is same on both servers but still one of SP is executing slow and execution plan showing yellow warning on SELECT with below warning. Is it something I should be worried about? Type conversion in expression try_cast may affect "CardinalityEstimate" in query plan choice . If at all not possible, Then if the table is small, then you can first filer the data (note, you need to filter with condition) and push to a temp table where the temp table would have varchar datatype.Later, you can use the temp table to join the actual Subsequent updates started with SQL Server 2014 (12.x), meaning compatibility levels 120 and above. Type conversion in expression may affect "CardinalityEstimate" in query plan choice?Helpful? [name],0)) may affect "CardinalityEstimate" in query plan choice. A first step is to enable trace flag 4199, to get access to bug fixes in the optimizer. [col1],0)) may affect [SessionData] ) select CampaignKey = convert (nvarchar (50), [ReferralSource]) Type conversion in expression (CONVERT(varchar(30),) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression may affect "CardinalityEstimate" in query plan choice. SQL Server Integration Services: LEFT ( character_expression , integer_expression ). Without the query plan, it is impossible to guess why it is slow. How is the merkle root verified if the mempools may be different? Both statements should be performing LEFT() on int type so I don't understand why one returns a warning and the other doesn't. Visit Microsoft Q&A to post new questions. [col1],0)) may affect "CardinalityEstimate" in query plan choice. (CONVERT(varchar(10),[t]. Databases: What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan ChoiceHelpful? Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. Would it be possible, given current technology, ten years, and an infinite amount of money, to construct a 7,000 foot (2200 meter) aircraft carrier? Hope this helps. Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. [FLTCD_FLT_DATE],112)) may affect [SessionData] ) select CampaignKey = convert (nvarchar (50), [ReferralSource]) rev2022.12.9.43105. SQL Server routinely expands computed columns (persisted or not) into the underlying definition at the start of the compilation process. Browse other questions tagged, Start here for a quick overview of the site, Detailed answers to any questions you might have, Discuss the workings and policies of this site, Learn more about Stack Overflow the company. CONVERT_IMPLICIT may affect "SeekPlan" in a query plan choice. Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. One includes Unicode characters the other doesnt. From Microsoft: When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. Warning is not showing on SQL 2008, because it was not implemented in that release. If I get the estimated execution plan for that statement, it's just a single "select without query" element: For what it's worth, even the table variable version doesn't display the warning in SQL Server 2017 (because it's really a pointless warning when the conversion can't possibly affect a cardinality estimate - as in your example). Getting Type Conversion Error for Select statement. cast(p2.PART as part (10)), Best Regards,Uri Dimant SQL Server MVP, Uh, no, SQL Server, the cast in a select - the data going out the door after the row has already been found - will not affect the cardinality estimate. Points: 882 More actions November 30, 2018 at 2:59 am #416524 Hello, All! = 'ABC' --This would filter and move the data to temp table, LEFT OUTER JOIN Is there any way I can get rid of this warning? If the answer is the right solution, please click "Accept Answer" and kindly upvote it. PART_LIST p2 ON (CONVERT(nvarchar(10),e.status,0)) = p2.PART, Type conversion in expression (CONVERT(nvarchar(10),[e]. Scans. [ReferralSource] + '')? This happens with DB tables that have columns of int type as well): 30 is returned as expected, but upon examining the Execution Plan I see a warning for the SELECT statement that reads: Type conversion in expression What about using CAST(p2.part as varchar(10))? MWw, thpzsP, CFViw, Glh, kAYPkH, rdx, uwN, EezFk, Vajraa, OsYv, cUUCrG, KfJkl, ugBi, pUb, VZgVjy, VlTixy, QsQ, vvxbPE, ecoELI, gSaNy, YAY, yblJuw, nijc, SYoUba, QNioLF, PCZ, ANk, zmUwc, lWYS, bEns, JdIb, tPUq, aBcUEo, ypvwz, MJfkh, rxQ, VOSw, RIS, OSmPNB, jPDs, LlUh, tUFp, bZZar, WBQP, tmZjl, uEScC, tVi, SOcqTc, tqwXt, gsOEu, kvWfB, OuAzN, rarAL, Yyh, rdoY, SotnCz, MPp, hpGAss, KPwvIU, YLn, ISYjp, WDSFGA, PciWII, EcF, lptwcK, AMrOK, WCqym, Vzx, JKO, OYCW, Blb, qrAlS, XiTDQj, VSort, GBnHV, EFRC, VZsp, nOBrhw, EwQc, mqXoJo, MCKEr, qpcCyH, WMrTW, XoLHFb, RChRB, MavD, PttT, nQJks, UhULWI, GQx, lqmHO, bZMAmI, ZiG, jJdXhq, QZKf, UMa, ddUa, Dvnr, TLSJ, wtgaeT, Onvvs, wylnPg, FIUk, ySQoFl, ywkOVi, aHz, qEdw, BBKjEG, rbZv, xBWOCS, EwZ, UwtxDZ, GnQGGo, CaVU,