Text in row option for SQL Server 2000 and 2005
Text in row option for SQL Server 2000 and 2005
|
| Release 9.3 |
   |
NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only
SQL Server 2000 and 2005 allow up to 7 KB of image, text, or n text data to be stored directly in a row on a data page. SQL Server 7 stored these data types in separate image pages, located with a 16-byte pointer in the table's data page. Text-in-row at SQL Server 2000 and 2005 permits you to place image type data directly in the data page, and anything exceeding the setting is placed in a traditional image page. All ArcSDE geometry is stored in an image type column in the feature table. ArcSDE raster layers and geodatabase network tables use image data types.
This setting is enabled on tables using the command sp_tableoption. ArcSDE does this automatically for you through the SDE_dbtune table. The SDE_dbtune table sets a default of 256 bytes for many settings.
| Keyword |
Parameter |
Description |
|---|
| DEFAULTS |
B_TEXT_IN_ROW |
Business tables with BLOB data type |
| DEFAULTS |
F_TEXT_IN_ROW |
Feature tables points column |
| NETWORK_DEFAULTS |
B_TEXT_IN_ROW |
Network business table with BLOB type |
| NETWORK_DEFAULTS |
F_TEXT_IN_ROW |
Feature table for generated junctions class |
| NETWORK_DEFAULTS::NETWORK |
B_TEXT_IN_ROW |
N_ tables with image type columns |
This may not be the best size for all your data. You don't want to allow too much text-in-row space on your data pages if you can't fill it. For example, it would be unwise to set this option to 7000 because you would create many more data pages and extents, thus lengthening the distance a query must travel to retrieve data. Likewise, underestimating this number would create more image data pages and could produce the same problem.
One way to determine an optimal table text-in-row setting is to estimate the eightieth percent size of all your image type records and store up to that size in the *_TEXT_IN_ROW setting. First you'll have to load your data, then run this query:
select top 20 percent(datalength(points)) from <name of feature table for a feature class>
order by datalength(points) desc
This query will list the top 20 percent largest data records in the points column of a feature class's feature table. You then pick the smallest value from its result set, use that for a dbtune entry with a new configuration keyword (for details on creating a configuration keyword, see DBTUNE configuration keywords), and reload your data referencing that keyword. If, for example, the eightieth percent value equaled 455, you would make this entry in the dbtune table:
| keyword |
parameter_name |
config_string |
|---|
| CARTO |
F_INDEX_FID |
WITH FILLFACTOR=75 |
| CARTO |
F_TEXT_IN_ROW |
455 |
Now reload this data referencing this keyword to pick up the new F_TEXT_IN_ROW setting. You could also put this value in the DEFAULTS keyword, but then all data loads would use this size. You can use ArcCatalog to load the data and specify the keyword.
You may want to check if a particular table has been enabled for text in row and to what its current size is set. Use the OBJECTPROPERTY Transact-SQL metadata function, for example:
select objectproperty(object_id('boris.f12'),'tabletextinrowlimit')
You may not want to reload your data. In this case, you can enable a table for text in row with the sp_tableoption, and write a query to copy all the existing image data types into a new column. The following is a sample script:
/*
FeatureTable_Text_in_Row.sql
script to copy existing points data into new column
with new text in row value enabled
SQL Server 2000 Only
Script Unsupported by ESRI
*/
sp_tableoption 'boris.f12','text in row',455
"go"
alter table boris.f12
add newpoints image
"go"
update boris.f12
set newpoints=points
"go"
alter table boris.f12
drop column points
"go"
sp_rename 'boris.f12.newpoints','points','column'
"go"
If you are using ArcSDE 9.0 or lower, have a lot of existing data in SQL Server 7.0, and desire to move this data into SQL Server 2000 or 2005, you'll want to move this data into the new text-in-row format. How you perform your upgrade will determine how your data is moved from a traditional image page into text in row.
Text_in_row is enabled with the sp_tableoption command, and ArcSDE sets a text_in_row default (in the dbtune table) for all feature tables to 255 bytes. This means any layer or feature class created in an ArcSDE geodatabase in SQL Server 2000 or SQL Server 2005 will use the text-in-row option. To upgrade an existing SQL Server 7.0 database to SQL Server 2000 and push your image data into text-in-row format, you can:
- Perform a geodatabase copy and paste between two ArcSDE servers: one running SQL Server 7.0 and the other SQL Server 2000 or 2005.
- Reload all your data into ArcSDE on SQL Server 2000 or 2005.
- Run the SQL Server 2000 upgrade wizard or restore your SQL Server 7.0 databases to a SQL Server 2000 or 2005 instance. Once you have done this, run the FeatureTable_Text_in_Row.sql listed above.
Text_in_row for rastersA raster stored in ArcSDE is composed of four tables. Of those, the sde_blk_<n> table holds the largest image type column. You can enable text_in_row for this table with the dbtune entry BLK_TEXT_IN_ROW. For example:
RASTER BLK_TEXT_IN_ROW 2000
This will entail changing the raster tile size as well. ESRI recommends you stay with the defaults because setting a large text_in_row will create too many data pages and extents, eventually slowing your queries.
