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)
| Value | Description |
| 0 | Returns the server name converted to BIOS format, for example: \\SERVERNAME\MSSQLSERVER\v1\Archive\dbo\Records\Chart\A73F19F7-38EA-4AB0-BB89-E6C545DBD3F9 |
| 1 | Returns the server name without conversion, for example: \\ServerName\MSSQLSERVER\v1\Archive\dbo\Records\Chart\A73F1 |
| 2 | Returns the complete server path, for example: \\ServerName.MyDomain.com\MSSQLSERVER\v1\Archive\dbo\Records\Chart\A73F19F7-38EA-4AB0-BB89-E6C545DBD3F9 |
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
-
Source Click Here.
Post a Comment