SQL 2012–Unique Constraint Violation Reporting

I wrote previously about some enhancements to the information in the SQL Server Log, whereby you can tell what database failed to be connected to. That doesn’t appear to be the only enhancement that I have stumbled across while working with SQL 2012. When working with unique constraints, you not only find out about a violation, but also the value which prompted this issue is also indicated. This message is indicated as long as you are running 2012 tools, as you will get the feedback when connecting to a 2008 R2 instance as well.

For example:

create table #temp
	(
	id int,
	constraint pk_temp primary key clustered
		(
		id
		)
	)

insert into #temp(id) values(1)
insert into #temp(id) values(1)

Error:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 11

Violation of PRIMARY KEY constraint ‘pk_temp’. Cannot insert duplicate key in object ‘dbo.#temp’. The duplicate key value is (1).

The statement has been terminated.

While you may still have to go back and group the data up to see what all of the causes of the violation could be, this is quite a nice enhancement to be able to do a quick spot check of the data.

Understanding Draft Pick Valuation Offset in Keeper Leagues

In a serious keeper league, you will eventually end up at a point where trading draft picks is brought up as something that would be fun. However, figuring out how to value a pick is not something that is easily decided upon. Every league has different rules, categories, etc, but one thing that is common to just about every league is that players are able to be ranked to determine who is the best and who is the worst player. With that as a guideline, some analysis can be done to determine how valuable a pick is vs. another pick. I will go into further detail on that at another time. What will be analyzed here is the difference in round value due to the chaotic nature of keepers being pulled out of the draft pool.

Below is an example from a 10 team keeper league, where we were between seasons and trades were being discussed after keepers were selected. The question being asked wasn’t whether pick 1 is better than pick 2, rather, how do the rounds compare to each other based on value?

Setup:

First, determine the rank for every player (I know pitchers and batters have different stats, but they still have an overall valuation that gives them a rank in the scope of all players. We don’t need to know the raw stats for this example). Now, remove all keepers if they have not already been removed. At this point, you are left with only players that can be drafted. Add the sequential draft order to simulate, ordered by rank of those remaining, where they would be drafted (the player with the best rank would be round 1, pick 1).

Analysis:

Since people keep players from all over the ranks, there are gaps. Through this arbitrage between rank and overall draft pick # (in a 10 team league, round 2 pick 1 would be draft pick # 11) we can determine the value. For each draft position, determine the difference between rank and draft pick #.

Now that we have a general metric for determining value beyond draft slot, we need to look at it and see what it means.

1. If there were no gaps in picks, the metric would be the same for all picks.  For example: rank 1 – pick 1 = 0, rank 100 – pick 100 = 0.  If there is an offset, such as 50 total keepers, then drafting begins at pick 51.  Rank 51 – Pick 1 = 50.  Rank 52 – Pick 2 = 50.

2. Just as the #1 pick is better than the #2 pick, a small number is better than a larger number with this metric. If a player with rank 35 is the best available, and the second best is rank 40, the valuation for pick #1 is 34, pick #2 is 39.

Below is what the first 15 rounds of a draft could look like by draft pick value offset:

Round

Avg

Min

Max

1

48.3

35

53

2

58.5

56

62

3

66.2

62

68

4

71.6

68

76

5

76.2

76

77

6

78.4

77

79

7

80

80

80

8

80.7

80

82

9

82

82

82

10

82.2

82

83

11

83

83

83

12

83.4

83

84

13

84.8

84

85

14

85.2

85

87

15

87

87

87

In chart form:

clip_image002

As you can see, the difference between rounds is greater at the beginning of the draft. This is due to people arbitrarily keeping players and throwing others back into the draft. Thus, by rank, the first few rounds provide an enormous amount of increased value per draft position than the later rounds. Once round 6 rolls around, there is not a large arbitrage due to keepers. This was to be expected since those rounds (in this league) are filled with players who would not warrant being kept.

I will be posting some examples using regression analysis and WAR instead of rank soon to get a more in depth draft pick value.

TRY_CONVERT() and TRY_PARSE() for Data Conversions

It is common for a developer to have string data that needs to be parsed into a specific format.  However, it is even more common that not all of the data in the dataset is able to be converted to the specified format.  This leads to SQL Server throwing errors and queries failing unless those scenarios are taken into account – a tedious process of data cleansing.  In SQL Server 2012, some new keywords are available to help with converting data types.

Quick example:

select convert(int, 1)

select convert(int,'abc')

While the first statement will succeed, the second will throw an error.  You could handle this with some general error handling or with an ISNUMERIC(data) = 1, but those can present issues as well when working with some data sets that you need to cleanse.  See if the results to ISNUMERIC(‘2e5’) are what you would expect.  ISNUMERIC() appears to interpret this as 2 x 10^5, but conversion to an int does not.

Now, bump up your database compatibility to 110 (Compatibility level must be bumped up to 110 to use the new keywords).

select try_convert(int, 1)

select try_convert(int,'abc')

Aha!  The second statement returns NULL, indicating that the data isnt appropriate for your data conversion.  This information is quite useful when dealing with string data that can be of many formats.