Sfrgttk

Can we say or write : "No, it'sn't"?

Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis

Is it ever safe to open a suspicious HTML file (e.g. email attachment)?

Calculator final project in Python

Why isn't acceleration always zero whenever velocity is zero, such as the moment a ball bounces off a wall?

Easy to read palindrome checker

Running a General Election and the European Elections together

How to invert MapIndexed on a ragged structure? How to construct a tree from rules?

Does Germany produce more waste than the US?

Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?

If the heap is zero-initialized for security, then why is the stack merely uninitialized?

Is it possible to replace duplicates of a character with one character using tr

How to install OpenCV on Raspbian Stretch?

Grabbing quick drinks

Which one is the true statement?

Proper way to express "He disappeared them"

Why did CATV standarize in 75 ohms and everyone else in 50?

How to prove a simple equation?

Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?

What was the first Unix version to run on a microcomputer?

Make solar eclipses exceedingly rare, but still have new moons

Is there a difference between "Fahrstuhl" and "Aufzug"

TikZ: How to reverse arrow direction without switching start/end point?

Why do remote US companies require working in the US?

Can I make a sequence skip certain values?

0

In SQL server 2014 we are using sequences for some integer surrogate keys.

They begin in the negative values counting up.

Can we program the sequence to skip zero when the time comes?

The reason is because it's too easy for higher layer developers to accidentally initialize to the default value of the int and we'd like to protect against this in business logic (easy enough!)

But zero is actually a possible value in our sequence one day. It's not a problem to use any of the numbers, just easy to erroneously use zero.

We could write any number of validations and constraints to prevent this but I think the sequence itself would be the easiest.

sql-server sql-server-2014 sequence

share|improve this question

edited May 18 '18 at 2:43

MDCCL

6,85331745

asked May 17 '18 at 23:09

MatthewMatthew

79721025

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  5
  

  
  
  
  
  
  

  
  
  There is no "skip a number or range" feature. What data type did you choose where you're going to need to worry about using up half the values but not all of them? In other words, if you're worried about what happens when you get to zero, shouldn't you be more worried about what happens when you hit the other end and exhaust all of the values?
  
  – Aaron Bertrand♦
  May 17 '18 at 23:17
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Perhaps add a constraint - CHECK (table_id <> 0)? The sequence won't skip 0 but the insert will fail.
  
  – ypercubeᵀᴹ
  May 17 '18 at 23:23
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Starting at negative side let's us use the full range of the data type. I'm not concerned about using half. I'm concerned specifically about zero. Both -1 and 1 are fine.... Even zero is fine... But it's easy to accidentally use zero
  
  – Matthew
  May 17 '18 at 23:33
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If you can't or won't change your strategy then I agree with @ypercubeᵀᴹ - add a check constraint and make sure your insert has try/catch logic. That one time the constraint fails, you can catch the error, re-try (now the sequence will generate 1), and even dynamically drop the constraint at that point. Another option is to have a trigger that, when it gets close to 0, automatically adjusts the sequence and even drops itself. Or sends you an e-mail to ask you to do it manually.
  
  – Aaron Bertrand♦
  May 18 '18 at 3:17
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  One option would be to use increment by 2 and start with an odd number. Only half of the possible numbers available but you will not have a zero. create sequence dbo.AtOdds as bigint start with -9223372036854775807 increment by 2;
  
  – Mikael Eriksson
  May 18 '18 at 5:54
  

  
  
  
  

 | 
show 4 more comments

0

In SQL server 2014 we are using sequences for some integer surrogate keys.

They begin in the negative values counting up.

Can we program the sequence to skip zero when the time comes?

The reason is because it's too easy for higher layer developers to accidentally initialize to the default value of the int and we'd like to protect against this in business logic (easy enough!)

But zero is actually a possible value in our sequence one day. It's not a problem to use any of the numbers, just easy to erroneously use zero.

We could write any number of validations and constraints to prevent this but I think the sequence itself would be the easiest.

sql-server sql-server-2014 sequence

share|improve this question

edited May 18 '18 at 2:43

MDCCL

6,85331745

asked May 17 '18 at 23:09

MatthewMatthew

79721025

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  5
  

  
  
  
  
  
  

  
  
  There is no "skip a number or range" feature. What data type did you choose where you're going to need to worry about using up half the values but not all of them? In other words, if you're worried about what happens when you get to zero, shouldn't you be more worried about what happens when you hit the other end and exhaust all of the values?
  
  – Aaron Bertrand♦
  May 17 '18 at 23:17
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Perhaps add a constraint - CHECK (table_id <> 0)? The sequence won't skip 0 but the insert will fail.
  
  – ypercubeᵀᴹ
  May 17 '18 at 23:23
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Starting at negative side let's us use the full range of the data type. I'm not concerned about using half. I'm concerned specifically about zero. Both -1 and 1 are fine.... Even zero is fine... But it's easy to accidentally use zero
  
  – Matthew
  May 17 '18 at 23:33
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  If you can't or won't change your strategy then I agree with @ypercubeᵀᴹ - add a check constraint and make sure your insert has try/catch logic. That one time the constraint fails, you can catch the error, re-try (now the sequence will generate 1), and even dynamically drop the constraint at that point. Another option is to have a trigger that, when it gets close to 0, automatically adjusts the sequence and even drops itself. Or sends you an e-mail to ask you to do it manually.
  
  – Aaron Bertrand♦
  May 18 '18 at 3:17
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  One option would be to use increment by 2 and start with an odd number. Only half of the possible numbers available but you will not have a zero. create sequence dbo.AtOdds as bigint start with -9223372036854775807 increment by 2;
  
  – Mikael Eriksson
  May 18 '18 at 5:54
  

  
  
  
  

 | 
show 4 more comments

In SQL server 2014 we are using sequences for some integer surrogate keys.

They begin in the negative values counting up.

Can we program the sequence to skip zero when the time comes?

The reason is because it's too easy for higher layer developers to accidentally initialize to the default value of the int and we'd like to protect against this in business logic (easy enough!)

But zero is actually a possible value in our sequence one day. It's not a problem to use any of the numbers, just easy to erroneously use zero.

We could write any number of validations and constraints to prevent this but I think the sequence itself would be the easiest.

sql-server sql-server-2014 sequence

share|improve this question

edited May 18 '18 at 2:43

MDCCL

6,85331745

asked May 17 '18 at 23:09

MatthewMatthew

79721025

share|improve this question

edited May 18 '18 at 2:43

MDCCL

6,85331745

asked May 17 '18 at 23:09

MatthewMatthew

79721025

share|improve this question

edited May 18 '18 at 2:43

MDCCL

6,85331745

asked May 17 '18 at 23:09

MatthewMatthew

79721025

edited May 18 '18 at 2:43

MDCCL

6,85331745

edited May 18 '18 at 2:43

MDCCL

6,85331745

asked May 17 '18 at 23:09

MatthewMatthew

79721025

asked May 17 '18 at 23:09

MatthewMatthew

79721025

1 Answer
1

active

oldest

votes

0

Can you explain your requirement with example ?

CREATE SEQUENCE [dbo].[CountBy1] 

 AS [bigint]

 START WITH 1

 INCREMENT BY 1

 MINVALUE -9223372036854775808

 MAXVALUE 9223372036854775807

 CACHE 

GO

Some programming logic to get restart value in variable (say @ID)

Declare @ID int=1



declare @Sql varchar(2000)=

    'ALTER SEQUENCE dbo.CountBy1

    RESTART WITH '+@ID+''



    Exec(@Sql)

share|improve this answer

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f207122%2fcan-i-make-a-sequence-skip-certain-values%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Can you explain your requirement with example ?

CREATE SEQUENCE [dbo].[CountBy1] 

 AS [bigint]

 START WITH 1

 INCREMENT BY 1

 MINVALUE -9223372036854775808

 MAXVALUE 9223372036854775807

 CACHE 

GO

Some programming logic to get restart value in variable (say @ID)

Declare @ID int=1



declare @Sql varchar(2000)=

    'ALTER SEQUENCE dbo.CountBy1

    RESTART WITH '+@ID+''



    Exec(@Sql)

share|improve this answer

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859

add a comment | 

0

Can you explain your requirement with example ?

CREATE SEQUENCE [dbo].[CountBy1] 

 AS [bigint]

 START WITH 1

 INCREMENT BY 1

 MINVALUE -9223372036854775808

 MAXVALUE 9223372036854775807

 CACHE 

GO

Some programming logic to get restart value in variable (say @ID)

Declare @ID int=1



declare @Sql varchar(2000)=

    'ALTER SEQUENCE dbo.CountBy1

    RESTART WITH '+@ID+''



    Exec(@Sql)

share|improve this answer

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859

add a comment | 

Can you explain your requirement with example ?

CREATE SEQUENCE [dbo].[CountBy1] 

 AS [bigint]

 START WITH 1

 INCREMENT BY 1

 MINVALUE -9223372036854775808

 MAXVALUE 9223372036854775807

 CACHE 

GO

Some programming logic to get restart value in variable (say @ID)

Declare @ID int=1



declare @Sql varchar(2000)=

    'ALTER SEQUENCE dbo.CountBy1

    RESTART WITH '+@ID+''



    Exec(@Sql)

share|improve this answer

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859

CREATE SEQUENCE [dbo].[CountBy1] 

 AS [bigint]

 START WITH 1

 INCREMENT BY 1

 MINVALUE -9223372036854775808

 MAXVALUE 9223372036854775807

 CACHE 

GO

Declare @ID int=1



declare @Sql varchar(2000)=

    'ALTER SEQUENCE dbo.CountBy1

    RESTART WITH '+@ID+''



    Exec(@Sql)

share|improve this answer

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859

answered May 18 '18 at 8:05

KumarHarshKumarHarsh

93859