Sql server strange Identity increment
azure-sql-database, sql-server
Solution
This is a known issue. There are two related items reported on Connect. This one is actually closed as by design, but I don't think the engineer who left the comment really understood the issue:
http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value
This (earlier!) bug is active:
http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity
The latter has one interesting workaround, and others that raise my eyebrows. The primary workaround on the suggested dupe would be fine for your own box, but good luck running a trace flag in Azure (or creating a startup proc as one of the connect items suggested). Using a SEQUENCE with no cache may be a viable option, but you should validate that the trade-offs are worth avoiding gaps in a meaningless, surrogate number. The other answer there suggests issuing a CHECKPOINT after every insert. Ouch.
In the end, if you want a sequence without gaps, stop using `IDENTITY` - that's not what it was designed to do.
Problem
I have this table setup at SQL Azure ``` CREATE TABLE [dbo].[Sl]( [ID] [int] IDENTITY(1,1) NOT NULL, [PublicId] [uniqueidentifier] NOT NULL, CONSTRAINT [PrimaryKey_ba033f1f-ac1b-4616-8591-fcd47fe0f63d] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON), CONSTRAINT [PublicId_UNIQUE] UNIQUE NONCLUSTERED ( [PublicId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) ``` The very strange thing is that the ID gets incremented in a unexpected way. ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 1011 1012 1013 1014 1015 1019 1020 1021 1022 1023 1024 1025 1026 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 3073 3074 3075 3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 4091 4092 4093 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 6091 6092 7091 7092 7093 7094 7095 7096 7097 7098 7099 7100 7101 7102 7103 7104 7105 7106 7107 8091 8092 8093 8094 8095 8096 8097 9091 9097 9098 9099 9100 9101 9102 9103 9104 9105 9106 9107 9108 9109 9110 9111 9112 9113 9114 9115 9116 9117 10100 10117 10118 10119 10120 10121 10122 10123 10124 10125 10126 11126 11127 11128 11129 11130 11131 11132 11133 11134 11135 11136 11137 11138 11139 11140 11141 11142 11143 11144 11145 11146 12144 12145 12146 12147 12148 12149 12150 12151 12152 12153 12154 12155 12156 12157 12158 12159 12160 12161 12162 12163 12164 12165 12166 12167 12168 12169 12170 12171 12172 12173 12174 12175 12176 12177 12178 12179 12180 12181 12182 12183 13183 13184 13185 13186 13187 13188 13189 13190 13191 13192 13193 13194 13195 13196 13197 13198 13199 13200 13201 13202 13203 14202 14203 14204 14205 14206 14207 14208 14209 14210 14211 14212 14213 14214 15214 15215 15216 15217 15218 15219 15220 15221 15222 15223 15224 15225 15226 15227 15228 15229 15230 15231 15232 15233 16233 16234 16235 16236 16237 16238 16239 16240 16241 16242 16243 16244 16245 16246 16247 16248 16249 ``` Any idea how to make this increment normally? So that the numbers don't jump like that? Any idea how to make this increment normally? So that the numbers don't jump like that?