When it comes to converting date and time values in SQL Server, things can get a little complex. But in this article I hope to demystify this topic for anyone who’s confused when it comes to making conversions between dates and other data types, as well as changing the format of date/time values, and other considerations.
Let’s jump straight in.
Continue readingIf you’re getting an error that reads “NEXT VALUE FOR function does not support the PARTITION BY clause” in SQL Server, it’s probably because you’re trying to use the PARTITION BY sub clause in an OVER clause when using NEXT VALUE FOR to increment a sequence object.
In other words, the NEXT VALUE FOR function does not support the PARTITION BY sub clause of the OVER clause.
To fix this issue, either remove the PARTITION BY clause or change the statement to use another method for partitioning the results.
When creating a sequence object in SQL Server, it’s common to set various properties such as the start value, the increment, and even things like the maximum and minimum values for the sequence. But it is also possible to create a sequence without setting any of these properties.
Continue readingIn SQL Server we can query the sys.parameters system catalog view to return all parameters that belong to user-defined objects.
For system objects, we can query the sys.system_parameters view. We can alternatively query the sys.all_parameters system catalog view to return all parameters that belong to either user-defined or system objects.
In SQL Server we can create synonyms, which allow us to provide an alternative name for a database object. Once created, we can reference the synonym instead of the object itself.
Continue readingIn SQL Server, we can query the sys.default_constraints system catalog view to return a list of DEFAULT constraints in the current database.
SQL Server supports sequences. Sequences enable us to create our own sequence of numeric values.
Sequences are similar to identity columns in that they generate a unique value that can be used to identify a column. However, sequences are created completely independently of any table.
Continue readingIn SQL Server, we can use the sys.synonyms system catalog view to get a list of synonyms in the current database. This view returns all objects in the sys.objects view of type SN (which stands for synonym).
In SQL Server, if you ever need to move one or more tables to another schema, you can use the T-SQL ALTER SCHEMA command.
In SQL Server, we can query the sys.check_constraints system catalog view to return a list of CHECK constraints in the current database.