SQL Server and Implicit Conversion of Types

SQL Server :: implicit conversion of Data Type in query from Microsoft Access?

and I discovered with disgust that Data Types in Access are not the same in SQL Server:

You find that FoxPro data types are different. 
You find that Excel sheets data types are different
You find that SharePoint lists and data types are different
(all of the above are Microsoft products).

You find that MySQL data types are different
You find that Oracle data types are different
And so on. So data types "are" often different.

The result?
You find that quite a bit, if not most data systems do have to work with somewhat different data types. So, the ODBC, or oleDB, or even these days jdbc drivers will handle the required conversions from server to the client software. And this can result in some data types not being supported at all.

does the Data Types commit an implicit conversion when they get translated from Access to SQL Server?

Yes, you are correct.
In fact it is the ODBC driver. SQL server does not "know" if the client query request is from Access, FoxPro, VB6, vb.net, or even some fancy asp.net web site.

In all cases the speed and pull of a data query occurs AT THE SAME RATE.

SQL server does not out of the blue decide that some query in Access, or some sql query from a asp.net web site is to run slower or faster.

The data type conversions (automatic) that ODBC drivers (or the now seldom used oleDB drivers) have NEVER be a significant cost or overhead in regards to pulling data.

So, the speed of Access, or say an asp.net site to pull some data is the SAME.

So, any query in access should not be any slower then a query sent from say asp.net, or some fancy c# client program. They all run the same speed, and the data type translates are a normal part of ALL ODBC or drivers used by client software to pull such data.

So if I use say Access, or FoxPro, or VB6, or C# or some asp.net web site to connect and pull data from SQL server? Well in ALL of these cases, then data type conversions that are compatible with say .net ARE AND WILL occur by the driver(s) and connection stack used. This data type conversion really never factors in ANY significant way in terms of performance.

So, a query submitted from Access, or .net? They should run the same. However, one feature and ability that Access has (and .net and most other connection technologies DO NOT HAVE) is that Access can join between different connection objects. So, I can have a local table, one linked to Foxpro, and another linked to SQL server. In Access you can perform joins and sql queries between those different data source tables. In .net say for example, a query is limited to ONE connection object.

However, this also means that any query that attempts a relational join between two data source tables (even the same database) can occur client side (because Access can do this, and most systems lack this ability). As a result, in some cases, while little if any speed difference in a select query from Access or say asp.net pulling data?

WHEN a relational join is involved, then Access can cause the relational join and work to occur client side as opposed to server side. In these cases, then you can force the query (and join) to occur server side by several approaches. And in these cases such a query will run VERY slow.

Best option:

Use/create a view and link to that view from Access client. This is the BEST option. The reason is you get the same performance as a pass-though query, and you get the same performance as a store procedure. But, there is no code or work to do this. Once done, you once again find the query pull speed in Access client to be the SAME as any other client software (.net, asp.net, c# etc.)

And once again, any consideration of data type translation by the drivers involved is a MOOT point from a performance point of view.

In place of the very little effort and work of a linked view, you can consider a pass-through query. This of course again would be raw T-SQL statements sent from Access client, and again the data type issues are quite much moot since this is t-sql syntax code being sent to sql server, and thus its t-sql taking the sql statements and doing the data type conversions from a ASCII (well ok, uni-code) string, and converting that string into numbers, date types etc. But then again such data conversion occurs for ANY sql statement you write that has values expressed in such a string.

So be it .net, FoxPro, Access, asp.net client software? they all will and have to do data conversion typeing between the data and the client software. For example, .net has several data types that you can define in code that say Access, or FoxPro or even VB6 for that matter (or even c++) does NOT have. So every client system is constantly converting from the native variable and data types in that software to that of data types used on sql server.

So, such data conversions occur for all client software, and this converting is not a performance factor anymore in Access then writing code in c++ or even assembler. The speed of all these systems when pulling a query sent to sql server is the same speed.

SQL Server and implicit conversion of types

This is the list you are after DataType Precedence

In your examples:

WHERE quantity > '3'

'3' is cast to int, matching quantity

WHERE quantityTest > 3

No casting required

WHERE date = 20120101

20120101 as a number is being cast to a date, which is too large. e.g.

select cast(20120101 as datetime)

This is different from

WHERE date = '20120101'

Where the date as a string can be cast.

If you go down a third of the CAST and CONVERT reference to the section Implicit Conversions, there is a table of implicit conversions that are allowed. Just because it is allowed doesn't mean it will work, such as (20120101 -> datetime).

Difference between Implicit Conversion and Explicit Conversion in SQL Server

An explicit conversion occurs when you use the CONVERT or CAST keywords explicitly in your query.

An implicit conversion arises when you have differing datatypes in an expression and SQL Server casts them automatically according to the rules of datatype precedence.

For example nvarchar has higher precedence than varchar

CREATE TABLE Demo
(
X varchar(50) PRIMARY KEY
)

/*Explicit*/
SELECT *
FROM Demo 
WHERE CAST(X AS NVARCHAR(50)) = N'Foo'

/*Implicit*/
SELECT *
FROM Demo 
WHERE X = N'Foo' /*<-- The N prefix means nvarchar*/

The second execution plan shows a predicate of

CONVERT_IMPLICIT(nvarchar(50),[D].[dbo].[Demo].[X],0)=[@1]

Both the explicit and implicit conversions prevent an index seek in this case.

How to resolve implicit conversion differences between SQL Server and .NET when converting from string to another data type

No, there is no (direct) way to work around this and keep the bulk copy. SqlBulkCopy contains code that analyzes the target table and performs its own conversions, and if these are improper according to its rules, it will complain. There is no flag or setting that will convince it not to do this. Source: the source.

As to your bonus question, why does it need to do this? Well, it doesn't, really. It could just send over the data, with exactly the types you define, and have SQL Server figure it out. It could simply send over, say, an SqlInt32 as an INT, and then have SQL Server figure out that you actually want 2 converted to the BIT value 1. The protocol allows for this, but SqlBulkCopy insists on having its ducks in a row before it passes the data on to the server. This isn't completely unreasonable, because the alternative is ignoring the target types and using conservative and overly broad types to convert to before sending over data (all strings would need to be NVARCHAR(MAX), all DateTime as DATETIME2, just in the off chance the target column needs to fit the whole precision), which reduces performance, or else requiring the programmer to specify, down to the exact length and scale, what the SQL types are supposed to be, which is tedious, error-prone, and would yield the obvious question "why doesn't it just ask the server"? And, well, that's what it does now. To do what you want, it would need a separate mode where it checks if you're "really sure" about using a different exact data type than the target, just in case you wanted to leave it to SQL Server do the conversion for you. I guess that feature just didn't make the cut.

Mostly it's considered a Good Thing to do your own conversions to the exact data type in advance. Assuming that "SQL Server knows best" is terrible practice to begin with, because the conversions SQL Server applies are only partially documented, language-dependent, and often downright weird. The example you cite, that '' is converted to 1900-01-01, is a clear argument against relying on them. Rather than assuming your application definitely needs bug-for-bug compatibility, it's probably better to review if you can switch to a properly typed data model, with the few exceptions that may be implicitly relied on explicitly implemented. Maybe your application is counting on SQL Server converting True to the BIT value 1. Maybe it's even relying on it converting 2 to 1. Maybe it even needs the conversion from -,. to the MONEY value 0.00. Maybe, but it probably doesn't, and if it does, it probably shouldn't.

Here endeth the sermon. What if for some ungodly reason you needed this anyway?

First of all, you could simply not use SqlBulkCopy. Generating a huge INSERT statement is very inefficient (the T-SQL parser does not like big statements), but you could issue a lot of individual (parameterized!) INSERT statements and wrap these in a transaction. This is less efficient than a bulk copy, but still far more efficient than individual inserts outside a transaction or ginormous statements, and may be good enough for your purposes.

You could create a staging table (but not a #temporary table) with the desired columns and make all columns (N)VARCHAR(MAX). Then you could SqlBulkCopy into that, and then have SQL Server do the conversions from this table. This can be made to work even if you don't know the columns in advance, with the help of some dynamic SQL. The main objection is that you need additional space for the staging table, and the final update/insert operation isn't bulk logged. In short, this is quite inefficient. It may very well be less efficient than doing the inserts directly, despite the bulk copy step.

You could have your application spit out a flat file with the desired columns and all values in string format, then programmatically invoke bcp. Of course this requires installing bcp(it can be installed separate from SQL Server, as part of the Command Line Utilities package) and you may lose a chunk of performance writing the intermediate files, but it will probably still beat generating huge INSERT statements or intermediate tables. As long as you specify that everything is a (N)VARCHAR in your input, bcp will dutifully leave the conversions to SQL Server.

Last but not least, for completeness, you could conceivably write your own code to wrap around the native bulk copy functions, which don't have this restriction. But that would be quite a bit of work, and all that interop is also not good for performance. SqlBulkCopy doesn't wrap around these functions -- it's a from-scratch implementation of the bulk load operation of TDS, also known as the mysterious INSERT BULK statement. I recommend writing your own implementation of that even less, but now that .NET is open source, you could (say) fork .NET Core and have your own version of SqlRawBulkCopy. With blackjack and hookers.

Why does implicit conversion from some numeric values work but not others?

It tries to convert your string to a numeric(3,2) because that's the type on the right of the multiplication¹. If you can force your value to be a larger numeric type:

select '185.37' * (102.00 - 100.00)

Then it works fine (produces 370.74) because it'll now attempt to convert it to a numeric(5,2).

Rather than doing it by a trick, however, I'd pick an appropriate numeric type for this and explicitly perform the required conversion, to better document what you want to occur:

select CONVERT(numeric(5,2),'185.37') * 2.00

¹And it has a higher precedence.

EDIT (by Gordon Linoff):

SQL Server's use of type precedence for this purpose is explicitly stated in the documentation:

For comparison operators or other expressions, the resulting data type will depend on the rules of data type precedence.

There might be just a little confusion because the documentation is not clear that the scale and precision of numerics is explicitly part of the type (that is, what gets converted is numeric(3, 2) rather than numeric(?, ?) with appropriate scale and precision).

Create implicit conversion rule for custom data type

There is - AFAIK - no way to get your hands onto these internal processes.

From your question I take, that you are writing an XML file, so I assume, that you want the written letter in your XML files for better human readability? If so, better try to convince your people, that there's nothing wrong with 0 and 1.

But you might save some typing:

 CREATE FUNCTION dbo.WriteBit(@b BIT) RETURNS VARCHAR(1)
 AS BEGIN
    RETURN
    (
    SELECT CASE @b WHEN 1 THEN 'Y'
                   WHEN 0 THEN 'N'
                   ELSE NULL END
    ); 
 END;
 GO

DECLARE @mockup TABLE(SomeInt INT, SomeBit BIT);
INSERT INTO @mockup VALUES
 (0,0)
,(1,1);

SELECT SomeInt,dbo.WriteBit(SomeBit) AS SomeBitChar FROM @mockup;

SELECT SomeInt,dbo.WriteBit(SomeBit) AS SomeBitChar 
FROM @mockup
FOR XML PATH('test'),ROOT('root');
GO

DROP FUNCTION dbo.WriteBit;
GO

The result in XML

<root>
  <test>
    <SomeInt>0</SomeInt>
    <SomeBitChar>N</SomeBitChar>
  </test>
  <test>
    <SomeInt>1</SomeInt>
    <SomeBitChar>Y</SomeBitChar>
  </test>
</root>

How does SQL Server implicit type casting work in this case?

The comparison is done using the rules of Data Type Precedence:

When an operator combines two
expressions of different data types,
the rules for data type precedence
specify that the data type with the
lower precedence is converted to the
data type with the higher precedence.

The NVARCHAR type (precedence 25) is converted to int (precedence 16). Note that precedence 1 means 'highest'.