meilenb (5/23/2015)
I'm in -- except I wouldn't want everybody doing these tests to prove something that has already been proved.I would be more inclined to have a mix of best practices and scientific practices. If someone needs to prove an int is faster than a string in a join (in your organization) because there is a debate, then prove it. Then make it a best practice so that you don't have to keep proving it over and over.
Some people really hate best practices, but maybe that speaks more for the poor quality of the art than the downsides of best practices. Not everything is performance but even then, if we did think performance were the only criteria, would we have to include loops and cursor options in our solution options or could we look at vetted experience of respected folks and take their word? I know I now try a set oriented solution based on what I've read. Is that a bad thing? Should I also include a cursor based example because best practices suck?
Performance as a metric is pretty easy to measure, correctness less so, and subjective areas like maintainability and extendability would require periods of experimental measurement that could be hard to budget, but (except for the best practice haters) we can instead learn from our predecessors on what has been found to work. I'm happy to say for the sake of the thread that these predecessors very well may have also used the scientific method, its just that some metrics of success don't appear instantly.
Also, if you are debating join performance of a string vs. an int, maybe your just having the wrong conversation altogether. Maybe the approach ought to be to ask "why do you want a string vs. an int for your key?". The answer might be "because I don't have to join to "Customer" to get CustomerName" when querying "Orders" for a specific use case.
If this is the answer, then the root of the concern is not necessarily string vs. int. The question might be "to join or not to join".
So the complexion of the concern might be something really different than how the individual is attempting to express his or her concern.
I would be suspicious selecting data types based on join performance, so I'd probably like to have the other conversation too.