May 30, 2013 at 7:51 am
Is it possible to identify candidate keys using dbcc show statistics and if so how would i do it?
I am married to a download process that moves everything all the time because the business stewards maintain that there is no way to tell one record from another, so they must extract the entire system every night.
This flies in the face of reality to me because if there is no way to tell what is unique, how can their application work correctly?
But the fact is that their application is a 35 year old file based system that has no database, that application logic does everything, e.g., there is no database back end minding the store.
Can this command help me identify what combination of columns are candidates for uniqueness so that we do not have to download and reload the same zillion records every night?
thanks
May 30, 2013 at 9:16 am
show_statistics will display the distribution of the data on any given set of statistics on a table. It's not going to tell you what might be a good index because it just shows the data distribution and the selectivity of the index. You can interpret those results yourself. What you're looking for are stats that have a fairly high density or selectivity, and a good distribution of the data. The first value is in the second data set returned by show_statistics. The smaller the number the better. The second value requires you to look at the third data set, the histogram. That will show you how the data is distributed across the set of statistics. A fairly even distribution, meaning each of the 200 steps contains about the same number of rows (approximately) shows even data distribution.
All that indicates a good candidate for an index. I doesn't necessarily make it a good candidate for a key. That requires the column to be none nullable and to only contain unique values. That's going to require you to examine the table structure and the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 30, 2013 at 10:49 am
Got it
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply