Menu
I am trying to work out how to detect if data in a VARCHAR(n) column in SQL Server 2008 is being stored in-row or off-row. Does anyone know how to do this?
Also, is there a way to keep the data in-row if we want it there?
208
In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber) ) piv; See Demo.
To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.
I used STRING_SPLIT() which is a table valued function supports SQL server 2016 and higher versions. You need to provide the formatted string into this function and use cross apply to join and generate the desired output.
To see if a value is in-row or off-row you can use DBCC PAGE
A way to force a VARCHAR(N) column to be in-row (not a VARCHAR(MAX) is to make it part of the clustered index key. This of course limits the length of the field to the maximum index key size of 900.
121
Check out the "large value types out of row" option in SQL Server, if it's a VARCHAR(MAX) column. The documentation is worth reading, because setting the option does not immediately convert the data in the table.
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
