Loading ...
Sorry, an error occurred while loading the content.

4924RE: [SQLQueriesNoCode] Magic Numbers for NULL and Identity Fields

Expand Messages
  • John Warner
    Aug 14 12:39 AM
    • 0 Attachment

      I have encountered sequences in Oracle and the headaches they can cause (and am glad I mainly work in the SQL Server side of the business). If they are so wedded to Oracle practices, why are they moving off? I know this is not your call but they need a ‘talking to’ that square pegs do not fit in round holes. This may have been the way in Oracle but SQL Server is not Oracle. I do not think there is a good solution to this problem for you other than to ‘recreate’ the Sequence logic in Oracle and good luck with that one. I have a system that has to interface with Oracle (we sort of exchange data) and to manage an alpha/number column on my side I ended up with a nasty little trigger to create some sequences but I typically on my side have to deal with 10 or 20 thousand rows per 24 hour cycle so the performance isn’t an issue. Doesn’t SQL Server 2012 have a Sequence structure now? I have not used it (my older system is 2008R2) but this might be a solution or at least worth investigating.


      John Warner


      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com]
      Sent: Wednesday, August 13, 2014 6:11 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: Re: [SQLQueriesNoCode] Magic Numbers for NULL and Identity Fields


      In Oracle Sequences can be NULLs in a few edge cases.


      Apparently they had been using that for some scenarios then a "best practice" document forbid that.


      The Oracle people had used NULLs in sequences as well by utilizing the edge cases that made it happen.


      On Wed, Aug 13, 2014 at 4:02 AM, 'John Warner' john@... [SQLQueriesNoCode] <SQLQueriesNoCode@yahoogroups.com> wrote:


      I must be missing something here, an Identity column should never have nulls so you would never need to insert 999,999 into it?


      John Warner


      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com]
      Sent: Tuesday, August 12, 2014 12:10 PM
      To: sqlqueriesnocode@yahoogroups.com
      Subject: [SQLQueriesNoCode] Magic Numbers for NULL and Identity Fields


      The SQL Server programmers at work are implementing a DBMS in SQL Server that must fit a corporate standard that is anti-NULL i.e. instead of NULLs they use a magic number say 999,999 in a field.

      But once such a number is inserted of course the newer identity fields generated start at 1 million, 1 million +1, etc.

      The implementers are great database programmers but have a long Oracle history so are not SQL Server veterans and as much as they would like to, the standard can't be changed in the beuracratic organization they are shipping the database and software for.

      Any wisdom and tips on getting the IDs to be below a million even when one of these magic numbers is inserted with SET IDENTITY off?


    • Show all 6 messages in this topic