SQL Server sequence objects allow us to increment or decrement through a range of numbers. Once set, we don’t need to change anything. We can simply use NEXT VALUE FOR to generated the next sequential number.
But what if we want to change the sequence to use a different range?
For example, we created a sequence that increments between 0 and 100, but now we want to change it to increment between 200 and 300.
Easy. We can use the ALTER SEQUENCE statement to do just that.
SQL Server provides us with both a @@ROWCOUNT and a ROWCOUNT_BIG() function. You may be wondering what the difference is between these two functions?
Let’s find out.
If you’re getting SQL Server error Msg 529 that reads Explicit conversion from data type date to int is not allowed, it’s because you’re trying to explicitly convert a date data type to an int data type, which is not allowed in SQL Server.
To fix this issue, try converting the date value to a string first, and then to an integer.
Alternatively, change the destination type to one that’s allowed.
Also, check that you’re trying to convert the correct value. For example, you may have selected the wrong column or variable. In this case, selecting the correct column may fix the problem.
If you’re getting error msg 8115 that includes the message Arithmetic overflow error converting expression to data type…, it’s probably because you’re trying to convert a value to a data type that can’t handle that value. For example, trying to convert a number to a tinyint but the number’s too big to fit into a tinyint.
To fix this issue, make sure you convert the value to a data type that can handle the size of the value that you’re trying to convert.
If you’re getting error msg 9809 which reads something like The style … is not supported for conversions from … to … in SQL Server, it’s probably because you’re trying to convert between data types, but the style that you’re specifying isn’t supported for that operation.
It’s not that the conversion can’t happen, it’s just that the style that you’re providing is wrong.
In SQL Server, we can use functions like CONVERT() or FORMAT() to convert a valid date type into a format like yyyymmdd.
This format adheres to the ISO 8601 standard, which defines dates to be written as yyyymmdd, or when using delimiters, as yyyy-mm-dd.
In SQL Server, the date type expresses dates in the yyyy-mm-dd format, but we can use the following technique to remove the delimiters and express the date as yyyymmdd.
When working with SQL Server, if we’re given a number that represents a date in the yyyymmdd format, we can use functions like CAST() or CONVERT() to convert that number to a valid date type. This will enable us to perform date operations against it that we might not be able to do when it’s still in numeric form.
If you’re getting an error that reads something like “Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default“, it’s because you’re trying to use DROP DEFAULT to drop a DEFAULT constraint.
The DROP DEFAULT statement has been flagged for deletion from SQL Server, and Microsoft recommends that we use the ALTER TABLE statement to drop DEFAULT constraints.
Therefore, to fix this issue, use the ALTER TABLE statement to drop the DEFAULT constraint.
When creating a sequence object in SQL Server, the default data type is bigint. However, we can change this so that the sequence uses a data type of our choosing, as long as it’s an integer type (see below for accepted data types).
We can set the data type of a sequence by using the AS argument when defining the sequence.