Another Stop-Gap Solution for the SQL Server 2012 Identity Crisis

                I haven’t seen this many fake ID’s since I was in college.
                Back on April 26, Grant Fritchey (of execution plan fame) reported a strange bug in the newly released version of SQL Server, in which the values of certain identity columns are suddenly incremented by about 1,000. Since then, 36 other users have confirmed the problem and 15 have posted in the thread at Microsoft Connect titled Failover or Restart Results in Reseed of Identity. The circumstances under which the bug occurs are still a bit murky; at first, it was believed to occur only in situations where the new AlwaysOn availability feature was in use, but it has since become clear that it happens sometimes after server restarts. User Leo G. Miller speculated in that thread that it may be related to the implementation of the new Sequence feature in SQL Server 2012, but as of yet there is no confirmation of this from Microsoft’s end. There isn’t a fix yet either, although a Microsoft employee asked for more information about the circumstances of the error on Oct. 4 and added that the company is “trying to quickly assess the impact of this issue.” One user in that discussion reported that it was a potentially serious error, without indicating if data loss was involved. I can speak from experience, however, that this “identity crisis” can cause significant data integrity issues, to the point where a patch is urgently needed. Until one is released, the SQL Server community will have to make do with the three workarounds posted in that thread. While in the midst of writing the series of self-tutorials on SQL Server Data Mining (SSDM) I have been talking about for months, I had to take time out to deal with this error so often that I ended up developing my own duct-tape level solution. In lieu of my perpetually postponed SSDM series, I figured I’d post my observations and workaround here, in the hopes that they can be of use to others who might be suffering significant accuracy problems as a result – perhaps without even knowing it.
               This can turn into a serious data integrity issue under the wrong circumstances, as I found out first-hand. Some of the single-user .Net applications I maintain suffered significant data loss when their datasets generated new identity values that were consecutive, as expected, which put them out of synch with the ones SQL Server had accidentally incremented by 1,000. When this occurs, the INSERT statements for the new records will succeed, but UPDATE statements to them will fail without warning if they are dependent on primary keys that use these faulty identity values. A user may create a new record in a front-end application and immediately begin changing values in it, but the changes won’t be recorded as expected, resulting in data loss that continues to accumulate until the .Net applications are restarted. For example, a user may create records with primary keys based on identity values that are reported as 55, 56, and 57 and so on by the front end app, but are assigned the erroneously incremented values of 1,055 through 1,057 and so on in SQL Server. Until the front-end app restarts or refreshes its data, any updates made to values 55 through 57 may fail without error because those primary keys don’t correspond to any record on the server. So in essence, the user may type away for days on end without having any of their changes actually recorded to the database. This probably will not be as much of an issue for OLTP systems, where developers expect the front-end apps to generate identity values that can conflict with each other and code accordingly to retrieve the actual values generated by the server. Single-user applications may not have these kinds of protections built in because concurrency issues with generated identity values are not expected. I have several such applications that worked well for years, until I upgraded the databases behind them to SQL Server 2012. Until this issue is addressed, upgrading to 2012 can be considered a breaking change for applications of that kind.
               I can only contribute a few observations on the circumstances behind the error, which in my case may have occurred in between server restarts. I have not seen the bug crop up between restarts since it came to my attention, but I vaguely remember a few identical episodes of data loss happening in the midst of periods when these single-user .Net apps had been in use for many hours consecutively. Yet thanks to the stored procedure I developed to deal with the problem, I can say for certain that the only tables that I am seeing the auto-increment problem on are ones that updates or inserts prior to the last restart. Inactive tables or ones that are merely queried with SELECTs don’t seem to be affected, at least in the cases I’ve seen to date. I was able to determine this by running my procedure, dbo.ReturnIdentityInfoSP, which returns a wealth of identity-related information on every user table in your database. As depicted below, the tables and their identity columns are identified by the SchemaName, TableName, ColumnName and object_id columns returned by the procedure. Count represents the total number of rows in the respective table, while last_value represents the last_value column of the sys.identity_columns system information view. LastUsedIDValue is generated by running the ident_current function and MaxID is the maximum value for the identity column retrieved from directly from the table itself. CountDifference, LastIDDifference and MaxIDDifference equal last_value minus Count, LastUsedIDValue and MaxID respectively. If you see any of these three columns hovering around values of about 1,000 it may be a good idea to the run the CHECKIDENT code that is automatically created for that table, in the GeneratedDBCCCode column, after manually inspecting the data of course. In the illustration below, Table3 appears to have fallen victim to the Identity Crisis bug (click the link to expand this wide table to its full size):  

 dbo.ReturnIdentityInfoSP Results

               You can of course add ORDER BY clauses at the end of the stored procedure to sort by CountDifference, LastIDDifference and MaxIDDifference to identify the tables with the biggest identity gaps. One interesting thing I have noticed each time I have run the procedure is that the last_value and LastUsedIDValue (i.e., ident_current) always report the new, false auto-increment value as having been used already, whereas MaxID shows that it has not. The last_value column of sys.identity_columns and the ident_current function are supposed to report only values that have actually been used by the column, so either they are consistently reporting the numbers wrong, or worse yet, the server is actually inserting and deleting records up to that point at a server restart. On some occasions, you may see the bug produce a last_value, LastUsedIDValue and MaxID that are roughly equivalent in conjunction with a CountDifference that is about 1,000 records off where you expect it to be. In these cases, the bug has probably false incremented the identity seed by about 1,000 in the past, but records were successfully added to the table afterwards at some point using these new fake ID’s. You may not experience further data loss in these cases, but there will be a permanent gap in your identity values of about a thousand records unless you take corrective action.

Before getting to the text of the procedure, some credits and notes are in order:

  • I got the row count into this by making use of the brilliant solutions mentioned by RBarryYoung, Jeff Moden and others on Aug. 10, 2008 in the thread titled Get Record Count For Each Table In A Database at SQLServerCentral.
  • At first IDENT_CURRENT kept returning NULL, which can often be solved by granting permissions on the table being viewed to the user running the query; in this case, however, I initially failed to perform the necessary step of qualifying the table name with the schema name.
  • For the sake of legibility, I did not add any brackets to handle object names that may have spaces. I never include spaces (or underscores, for that matter) in my object names, so it’s not an issue for me, but as the saying goes, your mileage may vary. There are probably other undiscovered kinks to work out in the stored procedure that I have yet to encounter myself. I haven’t added any error checking either.
  • Take care not to run this procedure when your servers are under heavy loads. Even without cursors, the execution plans have more branches than any I’ve ever seen before (since they’re performing complicated queries on each user table in your database) so they’ll probably have some kind of performance impact.
  • If you run the generated DBCC commands to reseed the tables, you will have to insert the records with false identity values back into the table, then delete them. Otherwise, once you have added another thousand rows to the table, you may inadvertently end up inserting a duplicate primary key, causing the failure of your INSERT statements.

               I am particularly proud of this solution, since it shines a bit more light on the problem. Most of all, however, it doesn’t use cursors. I was diagnosed with a severe allergy to cursors six or seven years ago and am happy to report that I have never used a single one since; by hook or crook, I have been able to find a way around them in every bit of T-SQL code I’ve written since SQL Server 2005. I thought I would have to break down and actually type the word F*tch for the first time in years, but with a bit of perseverance I was able to lash together a table variable, WHILE loop and sp_executesql to accomplish what I needed. I essentially came up with a seemingly unique way to retrieve aggregates from all user tables in the same query, which isn’t possible with any other cursor-free approach I have ever seen posted on the Internet, like user functions, sp_msforeachtable and about a half-dozen others. Hopefully this will impress the first companies apply to, whenever I’m ready to start looking for my first DBA job. In the meantime, I can use all the constructive feedback I get from my ReturnIdentityInfoSP stored procedure (a.k.a, my Fake ID Detector):

ALTER PROCEDURE [dbo].[ReturnIdentityInfoSP]

AS

BEGIN

DECLARE @SchemaName nvarchar(128),

@TableName nvarchar(128),

@ColumnName nvarchar(128)

 

DECLARE @MyTableVar table(

                                ID bigint IDENTITY (1,1) PRIMARY KEY CLUSTERED,

      SchemaName nvarchar(128),

                  TableName nvarchar(128),

                  ColumnName nvarchar(128),

                  object_id int,

                  last_value sql_variant,

                  Count bigint,

                  LastUsedIDValue bigint,

                 CountDifference bigint,

                 LastIDDifference bigint,

                  GeneratedDBCCCode nvarchar(max),

                  MaxID bigint

                  )

INSERT INTO @MyTableVar

(SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, CountDifference, LastIDDifference)

SELECT SCHEMA_NAME(schema_id) AS SchemaName, T1.name AS TableName, T2.name AS ColumnName, T2.object_id,

last_value, T3.Rows AS Count, ident_current(SCHEMA_NAME(schema_id) + ‘.’ +  ‘[' + T1.name + ']‘) AS LastUsedIDValue,

CAST(last_value AS bigint) – T3.Rows AS CountDifference,  CAST(last_value AS bigint) – CAST(ident_current(SCHEMA_NAME(schema_id) + ‘.’ +  ‘[' + T1.name + ']‘) AS bigint) AS LastIDDifference

 

FROM sys.tables AS T1

                 INNER JOIN sys.identity_columns AS T2

                 ON T1.object_id = T2.object_id

                                INNER JOIN dbo.SysIndexes AS T3

                                ON T3.ID    = T1.object_ID

WHERE TYPE_DESC = ‘USER_TABLE’

AND is_identity = 1

AND T3.IndID < 2

AND OBJECTPROPERTY(T1.object_ID,’IsMSShipped’) = 0

 

DECLARE @CurrentTableVarID bigint = 0,

@MaxTableVarID bigint = 0,

@CounterCheck bigint = 0,

@SQLString nvarchar(max),

@ParameterDefinition nvarchar(500),

@MaxID bigint,

@MaxIDOut bigint

SELECT @MaxTableVarID = Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID ASC

SELECT @CurrentTableVarID =Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID  DESC

WHILE @CurrentTableVarID <= @MaxTableVarID

BEGIN  

 

                                SELECT @SchemaName = SchemaNAme, @TableName = TableName,

                                @ColumnName = ColumnName

                                FROM @MyTableVar

                                WHERE ID = @CurrentTableVarID

 

                                SET @ParameterDefinition = ‘@MaxIDOut bigint OUTPUT';

                                SET @SQLString = ‘SELECT @MaxIDOut = Max(‘ + @ColumnName + ‘) FROM [' + @SchemaName + '].[' + @TableName + '] GROUP BY ‘ + @ColumnName + ‘ ORDER BY ‘ + @ColumnName + ‘ ASC’

                                EXEC sp_executesql @SQLString, @ParameterDefinition, @MaxIDOut = @MaxID  OUTPUT

                                UPDATE @MyTableVar

                                SET MaxID = @MaxID

                                WHERE ID = @CurrentTableVarID

 

                                SET                        @CounterCheck = @CounterCheck  + 1

                SET @CurrentTableVarID = @CurrentTableVarID + 1 — increment the loop

END

SELECT SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, MaxID, CountDifference, LastIDDifference, CAST(last_value AS bigint) – MaxID AS MaxIDDifference,

‘DBCC CHECKIDENT (”’ + SchemaName + ‘.’ + TableName + ”’ , RESEED, ‘ + CAST(MaxID AS nvarchar(50)) + ‘)’ AS GeneratedDBCCCode

FROM @MyTableVar

ORDER BY MaxIDDifference DESC

END

About these ads

About Stevan Bolton

I am a VB programmer and SQL Server DBA with an interest in MDX and multidimensional applications. I have an alphabet's soup of certifications: * 3 MCTS certifications in SQL Server 2008 R2, including a recent exam in MDX and Analysis Services * an MCDBA in SQL Server 2000 * an MCSD in VB6. I've kept up with each version of VB since then but haven't taken the newer exams * I also have a Master's in American history with a concentration in foreign affairs, as well as some work towards a doctorate in Latin American history * My B.S. is in print journalism I'll be posting whatever code I can to help out the SQL Server and VB developer communities. There is always someone out there more knowledgeable, so if you're a guru, feel free to correct any information I might post. I haven't yet been paid professionally to work with some of the technologies I've been trained in and enjoy, like MDX, so the word of those who have ought to carry more weight. There's a shortage of information on some of the topics I'll be posting on, such as the arcane error messages in Analysis Services (SSAS), so users might still find some value in my posts. If you learn of any job openings for MDX, SSAS, SQL Server and VB, feel free to E-mail me.

Posted on October 16, 2012, in Uncategorized. Bookmark the permalink. 12 Comments.

  1. I read your post with interest but I do have a question. You refer to front end applications “reporting” identity values that turn out to be 1000 or so off from the actual value in SQL Server. Were the lower values somehow calculated/”created” within the front end application? If so, why? Or, more precisely, why would anyone expect a value that is calculated/set in a front end application to in any way be related to an identity column’s value?

    Perhaps a better solution would be to:
    1) Include a new column (ApplicationIDentityValue) into which the front end’s “identity” value would be inserted;
    2) Use an OUTPUT clause in the INSERT statement to return the Identity value that SQL Server assigned;
    3) Use one or both of the above values to identify the row to be updated.

    I have had many arguments with developers (especially web developers) about the use of an “Identity” value that was generated by the front end application. I have been told more times than I can remember that the front end application can protect the database against having duplicate rows inserted and, therefor, there is no need for putting any sort of uniqueness constraint on columns within tables. I have occasionally had to let the developers discover the fallacy of their arguments the hard way; however, I have ALWAYS arranged to have some sort of identity column within the tables, whether the developers chose to use it or not.

    If the front end is generating the “identity” value, then it should be inserted into an integer column that has a Unique index on it . . . but don’t call it an Identity Column because it isn’t one. If, on the other hand, what you are writing about is a value that is actually generated within the database as an Identity (1-1) column’s value and returned to the front end application as one value when it is actually stored in the table’s row as another value, then that is a whole different can of worms and is, in fact, much uglier than it may first appear because it is a fundamental breakdown in the SQL Server database engine.

    • Thanks for the feedback. Typically, .Net will separately generate identity values that are in synch with the identity values generated by SQL Server, at least for single-user applications like mine where concurrency is not an issue; until upgrading to SQL Server 2012, I never experienced a single identity mismatch problem like this. My first reaction when I began discovering data loss was to assume that the front-end applications were the culprits, perhaps because I configured their datasets wrongly. But there’s no doubt about it: the .Net apps continue to calculate the identity values correctly as usual, but the identity values generated on the server side suddenly go out of whack. I’ve verified it by checking the tables in SQL Server Management Studio, debugging in Visual Studio and using SQL Server Profiler. There are of course a lot of ways around this, such as my preferred method of retrieving the newly generated ID directly from the database to make sure they’re in synch, but the point is that they were never necessary with these applications in the past because their identity values were never out of synch with SQL Server’s. Developers need to be aware of this because this assumption no longer holds when they upgrade to SQL Server 2012; validation code for ID values is not just a good idea now, but absolutely necessary because you can count on the values frequently going out of synch now. For the moment I’ve put off adding identity validation code on the front end, because using my procedure to detect and reseed the offending identity values at startup has so far prevented the bug from becoming an issue again. I’m also using Profiler while debugging the applications, to make sure the server isn’t suddenly generating “fake IDs” in between server restarts as well. Thankfully, I haven’t seen any direct evidence of that yet (besides some vague recollections that this may have occurred weeks ago), but I’m keeping a sharp lookout for it.

      — Steve

  2. Hi Steve,

    Thanks for raising awareness of this via SQLServerCentral.

    I ran your script and it stumbles over VIEWs – can you post a modified version that excludes these objects, please? As it barfs, I get no results, so am a bit stumped…

    Thanks again.

    • Thanks for the feedback. I’m stumped as well, because whenever I run it no views are included in the results. This is the expected behavior, since the line WHERE TYPE_DESC = ‘USER_TABLE’ ought to exclude views from the results. Can you post the error message you’re getting and the line number when CREATE is the first line? I figured people would have to edit the code to suit their own needs (especially for things like handling spaces in object names) but this functionality should already be in the procedure.

      Thanks,

      Steve

  3. begin
    set nocount on
    declare
    @obj int,
    @max bigint,
    @sql nvarchar(max)
    declare @list table (
    [object] int primary key clustered,
    [schema] sysname,
    [table] sysname,
    [column] sysname,
    [last] bigint,
    [max] bigint default 0,
    [diff] as [last]-[max],
    [sql] as ‘select @max = max($identity) from ‘+quotename([schema])+’.’+quotename([table]),
    [dbcc] as ‘dbcc checkident(”’+quotename([schema])+’.’+quotename([table])+”’,reseed,’+cast([max] as sysname)+’)’
    )
    insert into @list (
    [object],
    [schema],
    [table],
    [column],
    [last])
    select
    i.object_id,
    s.name,
    t.name,
    i.name,
    cast(i.last_value as bigint)
    from sys.identity_columns i
    inner join sys.tables t
    on i.object_id = t.object_id
    inner join sys.schemas s
    on t.schema_id = s.schema_id
    select @obj = min([object]) from @list
    while @obj is not null
    begin
    select
    @sql = [sql]
    from @list
    where [object] = @obj
    execute sys.sp_executesql @sql, N’@max bigint output’, @max output
    update @list set
    [max] = @max
    where [object] = @obj
    select
    @obj = min([object])
    from @list
    where @obj < [object]
    end
    select
    [object],
    [schema],
    [table],
    [column],
    [last],
    [max],
    [dbcc]
    from @list
    where [last] != [max]
    end

    This only gets the core info needed to detect and resolve the issue – NB: you still need to investigate on a case-by-case basis if the issue causes referential integrity problems.

  4. Hi. The autoincrement of columns is Microsoft problem. I have multiple database atached and problem is ad-hoc. What can i do is wait from Microsoft to resolve this isue or was solved in cumulative update. I dont know. I will never use new products until they have fully tested.

  5. I search and found this. Maybe help you until Microsoft solve the problem…
    https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details
    see workarounds

    • Yes, this is the Microsoft Connect post I linked to in the third sentence. My procedure is on the Workarounds list, along with some good ones by other SQL Server users with more experience than I have.

      — Steve

  6. Correction: While adapting some of this code for other purposes the other day, I realized that the code in the While loop wasn’t working correctly. I didn’t spot the error, however,because @CurrentTableVarID is always equal to @MaxTableVarID, therefore the loop is never triggered. The rest of the code works just fine and has been useful to me on many occasions when the SQL Server 2012 identity crisis has risen its ugly head, but the While loop doesn’t do anything. Also, for anyone following this post, it may be helpful to know that the identity crisis bug is also causing SQL Server to generate fake IDs in between server restarts on rare occasions, in increments of 12. I’ve seen it happen before my eyes in Profiler twice.

  7. I forgot to come back and post the updated code for this workaround, which now works as intended. I accidentally posted an earlier version of the procedure in which the MaxIDDifference column wasn’t calculated correctly. The updated code also takes an optional parameter that allows you to sort by MaxIDDifference, LastIDDifference, CountDifference, TableName or SchemaName.

    ======================================================================

    /* I got the rowcount into this by making use of the brilliant solutions mentioned in this discusison
    by RBarryYoung, JeffModen and others at “Get Record Count for each table in a database.’ at SQLServerCentral at 8/10/2008 8:48:25 PM
    http://www.sqlservercentral.com/Forums/Topic403906-555-2.aspx#bm549918

    the ident_current will of course return NULL if you don’t have explicit permissions on that object
    and may not reflect the actual values if other users are inserting new records as you run the query

    at first IDENT_CURRENT kept returning NULL, which can usually be solved by granting permissions on the table being viewed to the user
    running the query; then I discovered that qualifying the table name with the schema name is a necessary step, as seen below:

    *** You will have to go through and manually insert and delete the records with values that are too high
    */
    ALTER PROCEDURE [dbo].[sp_ReturnIdentityInfoSP]
    @OrderBy nvarchar(128) = ‘MaxIDDifference’
    AS
    BEGIN

    DECLARE @SchemaName nvarchar(128),
    @TableName nvarchar(128),
    @ColumnName nvarchar(128)

    DECLARE @MyTableVar table(
    ID bigint IDENTITY (1,1) PRIMARY KEY CLUSTERED,
    SchemaName nvarchar(128),
    TableName nvarchar(128),
    ColumnName nvarchar(128),
    object_id int,
    last_value sql_variant,
    Count bigint,
    LastUsedIDValue bigint,
    CountDifference bigint,
    LastIDDifference bigint,
    GeneratedDBCCCode nvarchar(max),
    MaxID bigint
    )

    INSERT INTO @MyTableVar
    (SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, CountDifference, LastIDDifference)
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, T1.name AS TableName, T2.name AS ColumnName, T2.object_id,
    last_value, T3.Rows AS Count, ident_current(SCHEMA_NAME(schema_id) + ‘.’ + ‘[' + T1.name + ']‘) AS LastUsedIDValue,
    CAST(last_value AS bigint) – T3.Rows AS CountDifference, CAST(last_value AS bigint) – CAST(ident_current(SCHEMA_NAME(schema_id) + ‘.’ + ‘[' + T1.name + ']‘) AS bigint) AS LastIDDifference
    — ‘DBCC CHECKIDENT (”’ + SCHEMA_NAME(schema_id) + ‘.’ + T1.name + ”’ , RESEED, ‘ + CAST(MaxID AS nvarchar(50)) + ‘)’ AS GeneratedDBCCCode

    FROM sys.tables AS T1
    INNER JOIN sys.identity_columns AS T2
    ON T1.object_id = T2.object_id
    INNER JOIN dbo.SysIndexes AS T3
    ON T3.ID = T1.object_ID
    WHERE TYPE_DESC = ‘USER_TABLE’
    AND is_identity = 1
    AND T3.IndID < 2
    AND OBJECTPROPERTY(T1.object_ID,'IsMSShipped') = 0

    DECLARE @CurrentTableVarID bigint = 0,
    @MaxTableVarID bigint = 0,
    @CounterCheck bigint = 0,
    @SQLString nvarchar(max),
    @ParameterDefinition nvarchar(500),
    @MaxID bigint,
    @MaxIDOut bigint

    SELECT @MaxTableVarID = Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID ASC
    SELECT @CurrentTableVarID =Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID DESC

    WHILE @CurrentTableVarID <= @MaxTableVarID
    BEGIN

    SELECT @SchemaName = SchemaNAme, @TableName = TableName,
    @ColumnName = ColumnName
    FROM @MyTableVar
    WHERE ID = @CurrentTableVarID

    SET @ParameterDefinition = '@MaxIDOut bigint OUTPUT';
    SET @SQLString = 'SELECT @MaxIDOut = Max(' + @ColumnName + ') FROM [' + @SchemaName + '].[' + @TableName + '] GROUP BY ' + @ColumnName + ' ORDER BY ' + @ColumnName + ' ASC'

    EXEC sp_executesql @SQLString, @ParameterDefinition, @MaxIDOut = @MaxID OUTPUT

    UPDATE @MyTableVar
    SET MaxID = @MaxID
    WHERE ID = @CurrentTableVarID

    SET @CounterCheck = @CounterCheck + 1
    SET @CurrentTableVarID = @CurrentTableVarID + 1 — increment the loop
    END

    /* the subquery is included solely to allow sorting on the MaxIDDifference measure */
    SELECT SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, MaxID, CountDifference, LastIDDifference, MaxIDDifference
    FROM (
    SELECT SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, MaxID, CountDifference, LastIDDifference, CAST(last_value AS bigint) – MaxID AS MaxIDDifference,
    'DBCC CHECKIDENT (''' + SchemaName + '.' + TableName + ''' , RESEED, ' + CAST(MaxID AS nvarchar(50)) + ')' AS GeneratedDBCCCode
    FROM @MyTableVar) AS T1
    ORDER BY
    CASE WHEN @OrderBy = 'CountDifference' THEN CountDifference END DESC,
    CASE WHEN @OrderBy = 'LastIDDifference' THEN LastIDDifference END DESC,
    CASE WHEN @OrderBy = 'MaxIDDifference' THEN MaxIDDifference END DESC,
    CASE WHEN @OrderBy = 'TableName' THEN TableName END ASC,
    CASE WHEN @OrderBy = 'SchemaName' THEN SchemaName END ASC
    END

    ===================================================================

  8. P.S. #1 – I can’t recall the bug occurring since I applied 2012 Service Pack 1, so perhaps it included a fix.

    P.S. #2 – Just in case the bug is still out there, it might be helpful to know that I witnessed identity values suddenly jump by intervals of less than 10 on a couple of occasions last winter, perhaps around 6 or so. Cases mentioned elsewhere on the Web typically involved increments on a base-10 scale, usually around 1,000, so this was unusual. It only happened a couple times, but there was no doubt about it; I saw the numbers jump before my eyes in Profiler. Hopefully this was fixed in the Service Pack and is now a moot point.

  1. Pingback: SQL Server 2012 – List of Bugs – Are you aware of? | SQL DBA Support . COM - Knowledge Centre for SQL DBAs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: