SQL Data Profiler : Recommendation-Related Question

Little warning... the question might in the end be more a conceptual question...

So, some of the feedback is really great, especially for one like me that was off DBs for a while and now jumped into the enterprisey sphere of MS SQL after just learning basics of MYSQL in apprenticeship ;)

But there is a feedback, i do not (fully) understand:

Looking at a bit column getting the recommendation: 'Consider migrating the column to a reference or lookup table since the column contains few distinct elements.'. Maybe some more experienced Databasist (what a word) can enlighten me. What is the point of that recommendation? If i would create a lookup for the possiblity of 2 different values - correct me if im wrong (but bit is the smallest value kind, or not?) - wouldn't i create in the end an overhead by moving these smallest kind of values into a reference/lookup table and referencing them during data queries? Wouldn't the referential key be of the same size, and taking into account that we then not just only have the foreign & primary keys stored, but also the reference, in the end make things actually worse instead of better? In my - currently unelightened - eyes, this does also not have a positive effect on complexity, i see the result being an addition to complexity. Also in terms of normalization, i would say this would be (at least for me) , beyond what i would call normalizing for a purpose. Just to make sure: I'm talking here about non-null bit - yes or no :) . 

Anybody willing to enlighten me? Would offer Swiss Chocolate as thank you ;)

  • You are over thinking the recommendation. The heuristic is not based on data types but these conditions:

    UPDATE #ProfileData


    SET DataTypeComments = ISNULL(DataTypeComments,'') + ' Consider migrating the column to a reference or lookup table since the column contains few distinct elements.'

    WHERE NoDistinct Values< @DistinctValuesMinimum --See Profiling Thresholds dialog using the Gear icon
    AND @ROWCOUNT > @DistinctValuesMinimum
    AND IsFK = 0
    AND PercentageNulls <> 100
    AND NoNulls <> 0

    It's meant to be a generalized recommendation based on the heuristics as stated above and settings in the Profile Threshold settings dialog. I can't disagree with your assessment when it comes to bit fields. This free profiling tool is meant to cover most cases. In the case you stated, that may not apply, so ignore it.