Does using BigInt Datatype in SQL table for Low Cardinality Column impair data transfer

[Daryl-Lynch-Bzy]

I noticed recently that BigInt datatype and large non-sequential numbers were being used for Primary and Foreign Keys in SQL Database.  It occurred to me that large numbers could slow the data transfer from SQL to Power BI beause each BigInt record would require 8 bytes instead of 2 bytes for smallint and 1 byte for tinyint.  Luckily, the VertiPaq Engine in Power BI is not inheirenting this limitation.

Is it common to use BigInt for Low Cardinality columns?  Thoughts?? 

Power BI BigInt Import vs SmallInt Import.docx

@cwebb 

[v-yingjl]

Hi @Daryl-Lynch-Bzy ,

From this article which has been mentioned in the doc, implicit conversions in SQL Server can result in performance degradation.

Value conversions in SQL Server follow preset precedence rules. Small data types are always up-converted to larger data types. Then SQL Server can compare the values.

In addition, when right-sizing data types, a best practice is to analyze whether a data type is the appropriate container for the value that will be stored.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daryl-Lynch-Bzy]

Thanks @v-yingjl.  The behaviour of the Power BI does seem to up-convert the integers.  I am happy that Power BI does this.  With the way the VertiPaq stores data (i.e. the dictionary), all key are converted to HASH values anyway. 

The point of my post is more the pipeline between Server (SQL or others) and Client (Power BI Webservice).  The data volume will be potentially larger and therefore slower because the Server has less optimal Integers.  However, it would still be far worse to pull the String value instead of the Int value.