Things I learnt today - PathName(), Text in row can be configured, Rows can overflow in SQL 2005.
As I mentioned I've been at Kalens seminar today and as
always picked up some things I never new about and also was reminded of others
things I had forgotten. The latter is far too common with SQL being such a large
product.
PathName can take a parameter
The first is the new PathName function for getting the virtual path for
filestream has an optional parameter. This parameter confirgures the format of
the server name being returned. The following is copied direct from books
online. (http://msdn.microsoft.com/en-us/library/bb895239.aspx)
I foresee that many people in a will need to use option 2 to ensure correct
resolution of the server name.
The text in row can be configured
I knew that you can configure text data to be stored in row but didn't
realise that there was a configuration option to say at which point you want
data to be stored off the row. Normally with sp_tableoption you only specify 0
or 1 to turn a feature on or off. However with the "text in row" option you can
specify a value between 24 and 7000. Any text data under that will be considered
to be stored "in row" if there is space. For more details have a look at http://msdn.microsoft.com/en-us/library/ms173530.aspx
Rows can overflow
I completely forgot that normal variable length data can overflow in SQL
2005, even if you are not using the new max types you can still store more than
8060 bytes in a row (as long as the extra is of variable length type).
This means that you can have a table with 100 varchar (100) columns that are
fully populated, i.e. a row size ~ 10k. This is clearly more than 8060
bytes and so would fail in SQL 2000. However in SQL 2005 and later the data
is stored by putting the extra data on OVERFLOW pages, with pointers from
the main row of data to the relevant overflow page(s).
There is downside in that doing this can result in poor performance if you
query is having to go off to lots of overflow pages to get data.
So thats some of the tips I learnt today.
Cheers Kalen
-