In MySQL, CURRENT_ROLE() is a built-in function that returns the current active roles for the current session, separated by commas, or NONE if there are none. The current role determines our access privileges.
Category: DBMS
Database Management Systems
How to Update Multiple Fields in a Redis Hash
In Redis, we can update multiple fields in just the same way that we can update a single field. The HSET command allows us to set one or more fields in a hash.
The ability of the HSET command to update multiple fields was introduced in Redis 4.0.0. Prior to that, we needed to use the (now deprecated) HMSET command if we wanted to add more than one field at once.
MySQL COERCIBILITY() Explained
In MySQL, the COERCIBILITY() returns the collation coercibility value of its string argument.
The COERCIBILITY() function defines how collations will be converted in the case of collation conflict. In such conflicts, an expression with a higher coercibility is converted to the collation of an expression with a lower coercibility.
Fix Error Msg 6825 “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML” in SQL Server
If you’re getting error msg 6825 that reads “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML“, it’s probably because you’re trying to use the ELEMENTS directive while using EXPLICIT mode of the FOR XML clause.
As the message alludes to, this option is not available when using the EXPLICIT mode (it’s only allowed with the RAW, AUTO, and PATH modes).
However, SQL Server does provide us with an equivalent that provides the same result. When using EXPLICIT mode, we can use the ELEMENT directive instead. We apply this to the column names, instead of the end of the query. We can alternatively use the ELEMENTXSINIL directive to ensure that elements are generated even if the column contains a NULL value.
How CONNECTION_ID() Works in MySQL
The MySQL CONNECTION_ID() function returns the connection ID (thread ID) for the connection.
In MySQL, every connection has an ID that is unique among the set of currently connected clients. We can use the CONNECTION_ID() function to return that ID.
Fix “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” (Error Msg 11711) in SQL Server
If you’re getting an error that reads “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” in SQL Server, it’s probably because you’re trying to change the data type of a sequence object.
We can’t actually change the data type of a sequence object, so this error is to be expected.
If you need to change the data type of a sequence object, you’ll need to drop the sequence and recreate it with the correct data type.
Continue readingHow SETVAL() Works in PostgreSQL
In PostgreSQL, we can use the setval() function to set a sequence’s value.
We specify the value when we call the function. We also have the option of setting its is_called flag.
SQL Server FOR XML Explained
In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document.
When we use this clause, we have various options for how we want to shape the resulting XML document.
Below is an explanation of how the FOR XML clause works, including the basic options we have when using it.
Three ISDATE() Alternatives that Work with DATETIME2 Values in SQL Server
SQL Server’s ISDATE() function checks whether or not an expression is a valid date. However, you may be aware that this function doesn’t work on datetime2 values. On datetime2 values it returns 0, which means it’s not a valid date, even when the value is a valid date.
This is obviously not ideal, because the datetime2 type is a valid date type. Microsoft even recommends that we use datetime2 instead of datetime for our dates, as it aligns with the SQL Standard and it provides more fractional seconds precision.
Anyway, below are three options we can use to check whether a datetime2 value is a valid date.
Continue readingHow to Create Decrementing Sequence Numbers in SQL Server
When we create a sequence in SQL Server, we have the option of making it an incrementing sequence or decrementing.
By “decrementing”, I mean that the sequence decreases instead of increases. For example, if it starts at 100, the next value is 99, and then 98, and so on.
To create a sequence that decrements, all we do is provide a negative value for the INCREMENT BY argument.