What is my identity burn factor vulnerability ri

DBA > Job Interview Questions > Sybase Interview Questions and Answers

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

What is my identity burn factor vulnerability right now in Sybase?

Identities are created type numeric, scale 0, and precision X. A precision of 9 means the largest identity value the server will be able to process is 10^9-1, or 1,000,000,000-1, or 999,999,999. However, when it comes to Burning identities, the server will burn (based on the default value of 5000) .05% of 1,000,000,000 or 500,000 values in the case of a crash. (You may think an identity precision allowing for 1 Billion rows is optimistic, but I once saw a precision set at 14...then the database crashed and their identity values jumped 5 TRILLION. Needless to say they abandoned their original design. Even worse, SQL server defaults precision to 18 if you don't specify it upon table creation...that's a MINIMUM 10,000,000,000 jump in identity values upon a crash with the absolute minimum burn factor)

Lets say you have inserted 5 rows into a table, and then you crash your server and then insert 3 more rows. If you select all the values of your identity field, it will look like this:

1. select identity_field from id_test
2. go
identity_field
--------------
1
2
3
4
5
500006
500007
500008

(8 rows affected)

Here's your Identity burning options (based on a precision of 10^9 as above):

Burn value	% of values	# values burned during crash

5000		.05%		500,000

1000		.01%		100,000

100		.001%		10,000

10		.0001%		1,000

1 		.00001%		100

So, the absolute lowest amount of numbers you'll burn, assuming you configure the burn factor down to 1 (sp_configure "identity burning set factor", 1) and a precision of 9, is 100 values.

(Continued on next question...)