Tag: mssql

How to Use the LAST_VALUE() Function in SQL Server

Posted on by Ian

In SQL Server, the LAST_VALUE() function returns the last value in an ordered set of values.

LAST_VALUE() is a window function that enables us to get a value from the last row of a query result set or partition. This can be useful for when we want to do stuff such as compare a value from the current row with a value in the last row or include it in a calculation.

You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because the default window frame ends with the current row. This is covered in the example below.

Continue reading →

How to Use the FIRST_VALUE() Function in SQL Server

Posted on by Ian

In SQL Server, FIRST_VALUE() is a window function that returns the first value in an ordered set of values.

Basically, we can use it to get a value from the first row of a query result set or partition. This can be handy if we want to compare a value in the current row with a value in the first row, or use it as part of a calculation.

Continue reading →

Fix Error 6358 “…is not a valid style number when converting to XML” in SQL Server

Posted on by Ian

If you’re getting error msg 6358 which reads something like 300 is not a valid style number when converting to XML, it’s probably because you’re trying to convert a value to XML, but the style that you’re specifying isn’t supported for conversions to that data type.

It’s not that the conversion can’t happen, it’s just that it can’t happen using the style that you’re specifying.

Continue reading →

Fix Error Msg 6855 “Inline schema is not supported with FOR XML PATH” in SQL Server

Posted on by Ian

If you’re getting error Msg 6855 in SQL Server that reads “Inline schema is not supported with FOR XML PATH“, it’s because you’re trying to add an inline schema to an XML document that you’re generating using PATH mode with the FOR XML clause.

As the message alludes to, PATH mode doesn’t support the ability to create an inline schema when using the FOR XML clause.

To fix this issue, either use a different mode to generate the XML with an inline schema (specifically, use either AUTO or RAW mode), or don’t generate an inline schema at all.

Continue reading →

Fix “Explicit conversion from data type int to date is not allowed.” in SQL Server

Posted on by Ian

If you’re getting SQL Server error Msg 529 that reads something like Explicit conversion from data type int to date is not allowed, it’s because you’re trying to perform an explicit data type conversion that’s not permitted.

SQL Server doesn’t allow certain conversions. If you try to perform such a conversion, you’ll get this error.

Continue reading →

Fix Error Msg 220 “Arithmetic overflow error for data type…” in SQL Server

Posted on by Ian

If you’re getting error msg 220 that reads something like Arithmetic overflow error for 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 smallint but the number’s too big to fit into a smallint.

To fix this issue, make sure you convert the value to a data type that can handle the size of the number that you’re trying to convert.

Continue reading →

2 Ways to Apply a Sequence to a Table in SQL Server

Posted on by Ian

In SQL Server, we can create sequence objects in order to generate a range of numbers that increment with each call to the sequence.

Although sequences are similar to identity columns, they are different in that they are generated independently of any table. It’s up to the application to generate the next sequence number before inserting it into the table. Therefore, we can apply a sequence to a table in the same way we would insert any other data into a table.

Here are two ways to apply sequence numbers into a table in SQL Server.

Continue reading →