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

Question

Since a few weeks I'm administering a SQL Server 2019 constantly hit by Microsoft Access applications.

I've spotted this Brent Ozar post (thank you @Brent) and I discovered that Data Types in Access are not the same in SQL Server:

I know there are many ways to improve performances but what I need to know is: does the Data Types commit an implicit conversion when they get translated from Access to SQL Server?

For example, how Short Text in Access can be interpreted by SQL Server as bigint, char(10), datetimeoffset, nchar(10), varchar(50), etc...?

To me they all look like implicit conversion, right?

Answer 1

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.