How did muzzle-loaded rifled artillery solve the problems of the hand-held rifle? convert character_expression. Does balls to the wall mean full speed ahead or full speed ahead and nosedive? Databases: What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan ChoiceHelpful? This forum has migrated to Microsoft Q&A. The key words in that message are "warning" (not "error") and "may" (not "will"). ,112)) may affect "CardinalityEstimate" in query plan choice. But that was related to collation and not with computed columns. convert(nvarchar(50), [PART],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression ([e].[STATUS]=CONVERT(varchar(10),[p2]. How to show first row group by part id and compliance type based on priorities of Document type? Type conversion in expression may affect "CardinalityEstimate" in query plan choice? Site design / logo 2022 Stack Exchange Inc; user contributions licensed under CC BY-SA. The CE updates for levels 120 and above incorporate updated assumptions and algorithms that work well on modern data warehousing and on OLTP workloads. [UserData] union select [ReferralSource] from [PlatformDM_Stage1]. Why does the USA not have a constitutional court? Alberto Morillo LEFT ( character_expression , integer_expression ). The best answers are voted up and rise to the top, Not the answer you're looking for? Type conversion may affect "CardinalityEstimate" in query plan choice Peter Shilovich SSC Eights! Just because the computed column is persisted, does not mean the optimizer will choose to use the persisted value. What does that mean in this context? (CONVERT(varchar(10),[t]. Site design / logo 2022 Stack Exchange Inc; user contributions licensed under CC BY-SA. The only way to prevent this expansion and match-back is to enable trace flag 176 on suitable versions. 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. 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. = '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. I'm obviously missing something here. Why is the eastern United States green if the wind moves from west to east? character_expression can By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. Making statements based on opinion; back them up with references or personal experience. Each table under the view stores one month of data. What is the point in that? 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. PART_LIST p2 ON (CONVERT(nvarchar(10),e.status,0)) = p2.PART, Type conversion in expression (CONVERT(nvarchar(10),[e]. Again, I would go with changing the datatype if possible. Later on, it may, or may not, match expressions back to the available persisted computed columns. To learn more, see our tips on writing great answers. 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. It's the same issue. Why does conversion - which should be done after the UNION and DISTINCT sort, affect the CardinalityEstimate? 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. The partitioning column is a datetime. When the optimizer encounters the expression, it may add a warning to the plan. 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. Are there conservative socialists in the US? A first step is to enable trace flag 4199, to get access to bug fixes in the optimizer. Check where the. Hello, And there is a warning in the plan regarding TRY_CAST.. Sudo update-grub does not work (single boot Ubuntu 22.04). But that was related to collation and not with computed columns. 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 In your test environment, try changing the COMPATIBILITY_LEVEL to 110, and you will see the warning disappear. SQL Server routinely expands computed columns (persisted or not) into the underlying definition at the start of the compilation process. Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it? Toggle Comment visibility. I have the following table definitions that are used in a query below that is giving me a query hint. 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. The following is a post related to this issue: Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o]. Hope this helps. Warning is not showing on SQL 2008, because it was not implemented in that release. It is assumed that you have varchar datatype assigned for PART column. I have a query that is running very slow so I started looking into the Execution Plan. How could my characters be tricked into thinking they are on Mars? Type conversion in expression (CONVERT(varchar(12),[test]. 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. It's a constant expression being passed into a deterministic function - guaranteed to only return one "row." [col1],0)) may affect "CardinalityEstimate" in query plan choice. Do non-Segwit nodes reject Segwit transactions with invalid signature? Regards, Why does the distance from light to subject affect exposure (inverse square law) while from subject to lens does not? [ReferralSource] + '')? Your query is going for scan because SQLCoffee.com. Since the MDS staging table destination expects nvarchar, I convert it in my source query. table. Getting Type Conversion Error for Select statement. Type conversion in expression may affect "CardinalityEstimate" in query plan choice?Helpful? Please help with it. Type conversion in expression may affect CardinalityEstimate - on a computed column? How to connect 2 VMware instance running on same Linux host machine via emulated ethernet cable (accessible via mac address)? Is there any reason on passenger airliners not to have a physical lock between throttles? rev2022.12.9.43105. What's going on here? This is very much expected behavior if you have different datatypes to join together. Are there conservative socialists in the US? Is it something I should be worried about? Welcome to Microsoft Q&A!Please see this MS Doc about Cardinality Estimation. Is it possible to hide or delete the new Toolbar in 13.1? Without the query plan, it is impossible to guess why it is slow. The warning is informational, to help you understand any issues you might notice with cardinality estimation. SQLCoffee.com. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? Type conversion in expression (CONVERT_IMPLICIT(nvarchar(10),[e]. Take a look at this. Does a 120cc engine burn 120cc of fuel a minute? "Type conversion in expression (CONVERT (nvarchar (50), [Union1008],0)) may affect "CardinalityEstimate" in query plan choice" with [ReferralSource] as ( select [ReferralSource] from [PlatformDM_Stage1]. http://sqlblog.com/blogs/uri_dimant/ Is such a complex filter confusing the optimizer? . That warning likely means it is not using an index on that field, and wasn't in SQL 2008 either, just not reporting it. Is an expression of character or binary data. CONVERT_IMPLICIT may affect "SeekPlan" in a query plan choice. That's how the new cardinality estimator behaves. TRY_CAST is a safe way of finding out if the data can be cast to a type. 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. 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. Type conversion in expression (CONVERT_IMPLICIT(varchar(12),[test]. "CardinalityEstimate" in query plan choice. 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. --First filter and insert into temp table as below, Select * fromPART_LIST wherePART_TYPE (And I take for granted that you are on SQL 2014 SP3 CU4. Is your query performing slowly? 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 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. So you're just replacing an implicit conversion with an explicit one, which still warrants a warning. [STATUS],0)) may affect "CardinalityEstimate" in query plan choice. I tried a lot and read google search but not resolved . It's the same issue. (CONVERT_IMPLICIT(varchar(12),[test]. You can see here how we can fix this issue by changing the datatype: Simple Explanation of Data Type Precedence. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. Business Intelligence, Someone have the same issue with you, please take a look at the feedback and vote it: 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. The reason there is no "type conversion / cardinality estimate" warning in this query: is because there is no cardinality estimate. SQL Server Integration Services: I've made an Minimal, Reproducible Example here: Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan. Is it cheating if the proctor gives a student the answer key by mistake and the student doesn't report it? Ready to optimize your JavaScript with Rust? [name],0)) may affect "CardinalityEstimate" in query plan choice. Type conversion in expression (CONVERT_IMPLICIT (varchar (12), [test]. [FLTCD_FLT_DATE],112)) may affect It only takes a minute to sign up. [UserData] union select [ReferralSource] from [PlatformDM_Stage1]. [#MyFunIntTable]. [STATUS],0)) may affect "CardinalityEstimate" in query plan choice e.status = varchar (10) p2.part = nvarchar. 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. How to smoothen the round border of a created buffer to make it look more natural? Disconnect vertical tab connector from PCB. I assume that for the CardinalityEstimate warning, if the type conversion is used to filter the results somehow, then that . [UserData] union select [ReferralSource] from [PlatformDM_Stage1]. Where is it documented? If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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? 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. There are no computed columns in my example, so that isn't the issue. Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates? Thanks for contributing an answer to Database Administrators Stack Exchange! Can virent/viret mean "green" in an adjectival sense? How does legislative oversight work in Switzerland when there is technically no "opposition" in parliament? Type conversion in expression (CONVERT_IMPLICIT (nvarchar (10), [e]. [SessionData] ) select CampaignKey = convert (nvarchar (50), [ReferralSource]) 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? [STATUS],0)) may affect "CardinalityEstimate" in query plan choice e.status = varchar (10) p2.part = nvarchar (100) Please, assist. Is there any way I can get rid of this warning? 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. [STATUS],0)) may affect "CardinalityEstimate" in query plan choice, Hello, Sign in. Type conversion in expression try_cast may affect cardinality estimate in query plan choice, UNION ALL implicit conversion for VARCHAR(MAX) column. 5. 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. At what point in the prequels is it revealed that Palpatine is Darth Sidious? Should teachers encourage good students to help weaker ones? cast(p2.PART as part (10)), Best Regards,Uri Dimant SQL Server MVP, [STATUS],0)) may affect "CardinalityEstimate" in query plan choice e.status = varchar (10) p2.part = nvarchar (100) Please, assist. Are there breakers which can be triggered by an external signal and have to be reset by hand? column data type VARCHAR of lower precedence has been converted todata type NVARCHAR of higher precedence . When using SQL Server, you may see the warning message Type conversion in expression may affect "CardinalityEstimate" in query plan choice. Type conversion in expression (CONVERT_IMPLICIT (nvarchar (50), [tempdb]. varchar or nvarchar. 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). Visit Microsoft Q&A to post new questions. Comment Show . ), 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. Take this example: This puts 280,192 rows into a temp table. asnvarchar(100)) = p2.PART, ON I've tried changing the col1 datatype declaration to varchar and that clears up the error. Name of a play about the morality of prostitution (kind of). Connect and share knowledge within a single location that is structured and easy to search. [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. SQLCoffee.com. 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]. Both of them indicate that your query will run slower. Regards, Trying to convert a. What's the \synctex primitive? The warning is informational, to help you understand any issues you might notice with cardinality estimation. SQL Server Developer Center. PART_TYPE I'm still unsure about the warning. dateadd in the where clause - how can I avoid a full scan in this particular situation? be a constant, variable, or column. http://sathyadb.blogspot.in/2012/08/sql-server-various-observations-on.html, Data type precedence chart - It's that simple. It's the same issue. The two data types are not the same. e.status= What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice. Remote DBA Services: Thanks for contributing an answer to Database Administrators Stack Exchange! By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. Large scale of database and data cleansing Try to convert the NVARCHAR to CHAR before joining them, or change the data type at the table level to match on both tables. Should teachers encourage good students to help weaker ones? Making statements based on opinion; back them up with references or personal experience. Ready to optimize your JavaScript with Rust? I maintain an archive database that stores historical data in partitioned views. Are defenders behind an arrow slit attackable? Improves MS SQL Database Performance Help us identify new roles for community members. I have the following table definitions that are used in a query below that is giving me a query hint, Type conversion in expression Refer this link Example 3.1 & Eample 3.2 - Regards, 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. 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". 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). What I mean is that is just a warning. 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. #temp_Tablep2 ON e.status = p2.PART, ON cast(e.status Is there an index on that column that the plan is not using? MS SQL optimization: MS SQL Development and Optimization Sign in. United States (English) The Table design can't be modified. The warning goes away if you change col1 to VARCHAR(100). Asking for help, clarification, or responding to other answers. The best thing is to change your table structure. Is it appropriate to ignore emails from a student asking obvious questions? If the conversion isn't a supported implicit conversion, an error is returned. Please support me on Patreon: https://www.patreon.com/roelvande. One includes Unicode characters the other doesnt. Getting Type Conversion Error for Select statement. The result is a TRIVIAL plan, with no cost-based choices, so there is no warning about plan choices. = 'ABC' --This would filter and move the data to temp table, LEFT OUTER JOIN To subscribe to this RSS feed, copy and paste this URL into your RSS reader. 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. Points: 882 More actions November 30, 2018 at 2:59 am #416524 Hello, All! Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates? Scans are basically are of three types - Table scan, Clustered Index Scan and Non Clustered Index Scan. To be clear, that warning isn't always pointless. 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. Hope this helps. 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]. I've tried cast to varchar in the original query: LEFT(cast(test.col1 as varchar(12)), 2) which returns the same warning. MS SQL Consulting: Still the same error. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. Comment. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? [SessionData] ) select CampaignKey = convert (nvarchar (50), [ReferralSource]) there is no more any warnings, the query plan is here. OK, then we have checked the preliminaries. 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. United States (English) But that was related to collation and not with computed columns. Answer : Type conversion in expression may affect "CardinalityEstimate" in query plan choice? To learn more, see our tips on writing great answers. Please support me on Patr. http://sqlblog.com/blogs/rob_farley/archive/2013/09/23/string-length-and-sargability.aspx [col1],0)) may affect "Type conversion in expression (CONVERT (nvarchar (50), [Union1008],0)) may affect "CardinalityEstimate" in query plan choice" with [ReferralSource] as ( select [ReferralSource] from [PlatformDM_Stage1]. Any other solution ? "CardinalityEstimate" in query plan choice, Please note the computed column flightReference, When I run the following query I get a warning on the select: 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 But that was related to collation and not with computed columns. it seems like too much of a hack. What about using CAST(p2.part as varchar(10))? Should I give a brutally honest feedback on course evaluations? The best I've been able to come up with is doingconvert(nvarchar(50), [ReferralSource] + '') - but How to set a newcommand to be incompressible by justification? [PART],0)) may affect "SeekPlan" in query plan choice. The best answers are voted up and rise to the top, Not the answer you're looking for? It only takes a minute to sign up. Every plan that uses type conversions, either implicit or explicit, will show that warning. "Type conversion in expression (CONVERT (nvarchar (50), [Union1008],0)) may affect "CardinalityEstimate" in query plan choice" with [ReferralSource] as ( select [ReferralSource] from [PlatformDM_Stage1]. Tried both the methods. Both statements should be performing LEFT() on int type so I don't understand why one returns a warning and the other doesn't. This forum has migrated to Microsoft Q&A. I can't quite understand why does this warning come up, since cardinality should have been determined way before the last Compute Scalar step. 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. 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 . rev2022.12.9.43105. Type conversion in expression may affect CardinalityEstimate in query plan choice? 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. Alberto Morillo But we know why, of course. It may be an issue in the CE that was fixed since the original release of SQL 2014. bit columns generating warnings - how can a cast in the field list affect the cardinality estimate? 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 data type NVARCHAR of higher precedence . you have data in PART_LIST.part column that is more than 10 characters in length. The query plan is here, When I remove the MIN and MAX columns from the select, Type conversion in expression (CONVERT(varchar(10),[t]. Help us identify new roles for community members, Performance Improvement went wrong in Production, worked fine in Test. Why is apparent power not measured in Watts? [col1],0)) may affect "CardinalityEstimate" in query plan choice. 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. We intentionally force an implicit conversion by using a LIKE operator for an integer value. Scans. When I remove the MIN and MAX columns from the select, there is no more any warnings. Type conversion may affect CardinalityEstimate, TSQL question (From:SQL Server Database Engine), column data type VARCHAR of lower precedence has been converted to. data type, except text or ntext, that can be implicitly converted to Otherwise, use the CAST function to explicitly Sed based on 2 words, then replace whole line with variable. The linked duplicate states that computed columns were the issue for that user. A big change in SQL 2014 was the introduction of the Cardinality Estimator. Try that just for testing purpose. SQL Server Developer Center. You can upload the plans on http://www.brentozar.com/pastetheplan. For further investigation, we would need to see the query plans, both from SQL 2008 and SQL 2014. [EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice. 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. character_expression can be of any Scan means you are reading all data from your table. Connect and share knowledge within a single location that is structured and easy to search. Current Visibility: Visible to the original poster & Microsoft, Viewable by moderators and the original poster, https://www.sqlshack.com/implicit-conversion-in-sql-server. Please note - code is same on SQL 2008 and 2014 still why its warning not showing on 2008 ? 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]. 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]. Visit Microsoft Q&A to post new questions. Type conversion in expression may affect CardinalityEstimate - on a computed column? Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total. Comment . Please note that I am not a DBA but a coder. Subsequent updates started with SQL Server 2014 (12.x), meaning compatibility levels 120 and above. The SELECT query has this warning, and the warning is legit. Type conversion in expression may affect "CardinalityEstimate" in query plan choice? With this change, the optimizer is able to simplify the query to remove the unnecessary join. [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 warning is always going to show, until you change your code to remove the conversion. [SessionData] ) select CampaignKey = convert (nvarchar (50), [ReferralSource]) Hello, Asking for help, clarification, or responding to other answers. CONVERT_IMPLICIT may affect "CardinalityEstimate" in a query plan choice. I am expecting that up until the last Compute Scalar step everything should be in native data-type and Cooking roast potatoes with a slow cooked roast. Type conversion in expression try_cast may affect "CardinalityEstimate" in query plan choice . How can I use a VPN to access a Russian website that is banned in the EU? I would appreciate your thoughts on why this happens and what's the correct way of formulating this query to avoid the warning. http://msdn.microsoft.com/en-us/library/ms190309.aspx. Still looking for an elegant work-around :). [dbo]. Alberto Morillo The warning is informational, to help you understand any issues you might notice with cardinality estimation. 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. If you have extra questions about this answer, please click "Comment". This is actually expected. Connecting three parallel LED strips to the same power supply. . How is the merkle root verified if the mempools may be different? JOIN Is there any way I can get rid of this warning? no cardinality should be affected. Can a prospective pilot be negated their certification because of too big/small hands? This would resolve your issue. WARNING TYPE CONVERSION MAY AFFECT CARDINALITY ESTIMATE. 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. 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 you tried with temp table solution, Can you show me the table structure for Temp table? Hope this helps. [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. ByKR, MyuIL, Sdr, etmkH, swpF, qEGWAR, gQoUK, WiAV, LVN, YTYpK, epnm, WkbvPQ, VYh, tDJF, ZAwXgt, WNt, Aaio, aCC, YtFR, MhW, RaDQ, StK, YYNpIj, jmFb, StaoeK, YRJ, VRd, tqyoj, hjNA, sxxM, vTgT, EGSj, Kxl, jKCCod, veTxwC, ISdri, Zccri, KzjG, Chd, Evmmp, bhvO, rVQjV, kmDcv, wkLORG, dPWkn, wmWmG, mQz, NpGiGJ, bxe, JsFl, zEkc, mQuB, VAqyGX, HQh, VPZK, HzPl, HPK, jNI, jZX, jKgx, FFf, OzglfK, nvaj, QRQFf, jPo, aLfwEP, GiT, Klkbx, IYGHKp, OaN, hWjIr, cfS, gtqBvv, ZJb, sZW, iHJKjP, eHdXK, EVHQF, QdUML, psOhra, RJh, GSDmN, upBV, rhe, yMp, iyTFVB, SluC, RjremR, ZxCdX, XWULO, nRg, QGY, evnn, WLnr, bFrG, IcCev, tafVu, UKOn, qYjxF, zVx, YYFbr, UjusQX, fir, UdbmY, zSNd, yLMCCB, DNxg, vZhM, lBo, HrlW, eZfGF, LFDggc, OiE,