Storage Settings for Tables and Indexes

When you create a table or an index, SQL Server permits you to define a number of storage options by use of the WITH and ON clauses for a table key, an index or a statistics. Not all are exactly "storage" options, but we use this name for the entire group for simplicity.

This page concerns features that have to be considered as "advanced" and the topic assumes that you are familiar with the AbaPerls File-Loading Process (to which page this page is a sub-topic), and not the least how table and index files are loaded in general. The topic also assumes that you are familiar with update scripts generated by DBUPDGEN.

Note: the functionality in this topic requires at least version L1.0.0280 of the ABAPERLS subsystem. See the section Legacy Databases for the behaviour when this is not the case.

Contents:
   Settings in Scope for this Topic
   Storage Settings and Blocking Table Reload
   Source File vs. Database Settings
      Reloading Table Files
      Reloading Index Files
   Controlling Autostats
   Legacy Databases

Settings in Scope for this Topic

This topic concerns the following clauses and commands in a table file:

AbaPerls imposes a restriction on how you can specify the filegroup and compression setting for a table with a clustered key. For a table with a clustered key, SQL Server permits you to specify filegroup and compression setting either with the key or at the end of the table. For SQL Server, all these are legal:

CREATE TABLE tbl (a int NOT NULL,
                  b nvarchar(400) NULL,
                  CONSTRAINT pk PRIMARY KEY (a) 
                     WITH (DATA_COMPRESSION = ROW) ON that_filegroup
)

CREATE TABLE tbl (a int NOT NULL,
                  b nvarchar(400) NULL,
                  CONSTRAINT pk PRIMARY KEY (a)
)
ON that_filergroup
WITH (DATA_COMPRESSION = ROW)

CREATE TABLE tbl (a int NOT NULL,
                  b nvarchar(400) NULL,
                  CONSTRAINT pk PRIMARY KEY (a) 
                     WITH (DATA_COMPRESSION = ROW) ON that_filegroup
)
ON that_filergroup
WITH (DATA_COMPRESSION = ROW)

However, AbaPerls requires that you use the first form, and will give an error message for the other two. Note that the below is legal for AbaPerls:

CREATE TABLE tbl (a int NOT NULL,
                  b nvarchar(400) NULL,
                  CONSTRAINT pk PRIMARY KEY NONCLUSTERED (a)
)
ON that_filergroup
WITH (DATA_COMPRESSION = ROW)

That is, AbaPerls permits you to specify filegroup and comrpression setting on table level for heaps or tables where the clustered index is not on a key in the .tbl file. (The sole reason for this restriction is ease of implementation, nothing else.)

For the CREATE INDEX and CREATE STATISTICS statements, this topic concerns the WITH, ON and FILESTREAM_ON clauses, with the following notes:

AbaPerls does not consider the use of sp_indexoption, as this is a legacy procedure offering no unique functionality.

Storage Settings and Blocking Table Reload

As described in the section Pre-SQL Analysis: Reloading a Table, when you load a .tbl file, and the table already exists and have data (or dropping is blocked by referencing foreign keys or objects with schema-binding), AbaPerls creates the table in a temporary schema and investigates whether it is possible to use ALTER TABLE or other means to modify the existing table to be like the definition in the source file. If this is not possible, AbaPerls produces an error.

The section referred to above lists all details on the logical definition of the table, that is, columns and constraints. Here we detail how the storage settings affects table reload.

AbaPerls attempts as far as possible to use various commands – ALTER TABLE, ALTER INDEX REBUILD, ALTER INDEX SET, CREATE INDEX (WITH DROP_EXISTING = ON), sp_tableoption – to transfer the settings. but there are a few cases where it has to give up:

To this come situations where AbaPerls attempts ALTER TABLE, but where SQL Server does not permit it, for instance changing FILESTREAM_ON for a table that already has a FILESTREAM column.

Note that in some cases that even if ALTER TABLE is possible, it may still be preferable to run a table-reload in the cases when ALTER TABLE is not only a metadata change, for instance when moving the table from one filegroup to another. An ALTER TABLE statement will be a single transaction that could cause a strain on the transaction log, whereas a table reload permits you to move the data in batches. In this case, you will need to modify the upgrade script.

Source File vs. Database Settings

An interesting aspect of these options is that, with the exception of IGNORE_DROP_KEY, they have no logical effect on the application whatsoever. For this reason, AbaPerls will retain these settings when you reload a .tbl file, as long as the setting is not explicitly mentioned in the source file. One scenario where this is useful is when you install the database schema at different sites with different usage patterns and data volumes. Some sites may want to save disk space by using compression, but you don't want to put this in the source file as it could hamper performance. And more importantly: some of your customers only have Standard Edition which does not support compression in older versions of SQL Server.

More precisely, this applies to the following settings:

SettingSQL Syntax where it appears
ALLOW_ROW_LOCKSWITH clause for key in CREATE TABLE.
WITH clause in CREATE INDEX.
ALLOW_PAGE_LOCKSWITH clause for key in CREATE TABLE.
WITH clause in CREATE INDEX.
DATA_COMPRESSIONWITH clause for key in CREATE TABLE.
WITH clause at the end of CREATE TABLE (not permitted by AbaPerls if there is a clustered key).
WITH clause in CREATE INDEX.
FILESTREAM_ONClause for at the end of CREATE TABLE.
Last clause in CREATE INDEX, for clustered indexes only.
FILLFACTORWITH clause for key in CREATE TABLE.
WITH clause in CREATE INDEX.
HISTORY_RETENTION_PERIOD WITH clause for key in CREATE TABLE for a temporal table. (Not permitted in source files by AbaPerls.)
large value types out of rowsp_tableoption.
LOCK_ESCALATIONALTER TABLE.
NORECOMPUTEWITH clause in CREATE STATISTICS.
ON filegroup | partition_schemeON clause for key in CREATE TABLE.
ON clause at the end of CREATE TABLE (not permitted by AbaPerls if there is a clustered key).
ON clause in CREATE INDEX.
OPTIMIZE_FOR_SEQUENTIAL_KEYWITH clause for key in CREATE TABLE.
WITH clause in CREATE INDEX.
PAD_INDEXWITH clause for key in CREATE TABLE.
WITH clause in CREATE INDEX.
STATISTICS_NORECOMPUTEWITH clause for key in CREATE TABLE.
WITH clause in CREATE INDEX.
table lock on bulk loadsp_tableoption.
TEXTIMAGE_ONClause at the end of CREATE TABLE.
text in rowsp_tableoption.
vardecimal storage formatsp_tableoption.

There are a few more possible elements in the WITH clauses for keys and indexes: IGNORE_DUP_KEY, DROP_EXISTING, MAXDOP, ONLINE, SORT_IN_TEMPDB as well as a few more that applies to statistics only. Of these, SQL Server only saves the setting for IGNORE_DUP_KEY, but as noted above this setting affects application behaviour, why AbaPerls ignores the setting in the database, and takes the source file to be the sole master for this setting. The other settings affect only the creation of the index or statistics, and since the information is not stored in the database, there is nothing for AbaPerls to honour. As noted above, AbaPerls reserves DROP_EXISTING for its own use, and does not permit it to appear in source files.

Reloading Table Files

So how does this work? Let's look at it through examples. We will first look at table files. Say that you start with this source file:

CREATE TABLE mytbl (
   myid      int         NOT NULL,
   mydata    varchar(23) NOT NULL,
   col1      int         NULL,
   col2      int         NULL,
   col3      int         NULL,
   CONSTRAINT pk_mydata PRIMARY KEY (myid),
   CONSTRAINT u_mydata  UNIQUE (mydata) WITH (FILLFACTOR = 82),
)
ALTER TABLE mytbl SET (LOCK_ESCALATION = AUTO)

Then in the database, someone make changes to storage options, so that when scripted from SQL Server Management Studio, you see this (with the SET commands that SSMS spews out removed and reformatted for legibility):

CREATE TABLE [dbo].[mytbl](
	[myid] [int] NOT NULL,
	[col1] [int] NULL,
        [col2] [int] NULL,
        [col3] [int] NULL,
	[mydata] [varchar](23) NOT NULL,
 CONSTRAINT [pk_mydata] PRIMARY KEY CLUSTERED ([myid] ASC)
     WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, 
           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY],
 CONSTRAINT [u_mydata] UNIQUE NONCLUSTERED ([mydata] ASC) 
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[mytbl] SET (LOCK_ESCALATION = DISABLE)

I have highlighted the changes with regards to the original definition. Later at some point, you run an update script generated by DBUPDGEN on the database, which includes this new version of mytbl.tbl

CREATE TABLE mytbl (
   myid      int         NOT NULL,
   mydata    varchar(23) NOT NULL,
   newcol    int         NULL,
   col1      int         NULL,
   col2      int         NULL,
   col3      int         NULL,
   CONSTRAINT pk_mydata PRIMARY KEY (myid),
   CONSTRAINT u_mydata  UNIQUE (mydata) WITH (FILLFACTOR = 82),
)

There is one new column, and the setting for lock escalation has been removed. The update script generated by DBUPDGEN includes this line:

if (not abasqlfile('mytbl.tbl')) 

Assuming that there is data in the table at this point, AbaPerls will create the table in a temporary schema. Before creating the table, AbaPerls checks if the database has any specific storage settings and in such case incorporates these in the script, but only if they are not mentioned in the source text. In the example there are four changes to the table in the database with regards to the original source in mytbl.tbl.

  1. Change in PAD_INDEX for pk_mydata.
  2. Change in FILLFACTOR for pk_mydata.
  3. Change in FILLFACTOR for u_mydata.
  4. Change in lock escalation for the table.

Of these, AbaPerls honours the first two and ignores the last two. The source file does not include LOCK_ESCALATION. However, when the file originally was loaded, AbaPerls stored the settings that were mentioned in the file in the table abaobjectstoragesettings. Therefore, AbaPerls knows that the source code owns this setting and disregards the database setting. (Else an old setting in the source file that might have been in error could be retained forever. AbaPerls only stores that the setting was mentioned, not the actual value for the setting, so it can't tell that DISABLE was set locally.) This results in AbaPerls sending these commands to SQL Server:

 CREATE TABLE #temp1360168434_13 . mytbl  (
 myid int NOT NULL ,
 newcol int NULL ,
 col1 int NULL ,
 col2 int NULL ,
 col3 int NULL ,
 mydata varchar ( 23 ) NOT NULL ,
 CONSTRAINT pk_mydata PRIMARY KEY ( myid ) 
     WITH(PAD_INDEX=ON, FILLFACTOR=70) ON [PRIMARY] ,
 CONSTRAINT u_mydata UNIQUE ( mydata ) 
    WITH ( FILLFACTOR = 82 ) ON [PRIMARY] ,
 ) ;

You may note that the script also includes ON [PRIMARY] for the filegroup, although this is the default. AbaPerls could have added the other settings as well, for instance ALLOW_ROW_LOCKS=ON or an explicit ALTER TABLE command to set LOCK_ESCALATION on table level. This behaviour is accidental and nothing you should rely on.

When AbaPerls has loaded the new definition of mytbl in the temporary schema, it will investigate whether it can use ALTER TABLE to change the existing table to match the new definition. However, since the new column is not the last column in the table, this fails. The upgrade script therefore enters the section where it moves the original table to the AbaPerls side schema. The update script again loads the file mytbl.tbl. This time the table is created successfully directly in the dbo schema. Save for the different schema, the statement will be the same as above. That is, AbaPerls looks at the table in the side schema and incorporates the specific database settings.

You may wonder what happens with the lock-escalation setting next time you load the file? The answer is that this time, the database setting will prevail, because AbaPerls updates abaobjectstoragesettings, and lock escalation will no longer be listed as a storage setting owned by the source code.

There is one small restriction in this scheme: if the table is a heap, AbaPerls does not honour the settings for ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS. SQL Server permits you to set these for a heap, but in quite roundabout ways. If the situation arises, AbaPerls produces a warning.

Reloading Index Files

The original appearance of  mytbl.ix is:

CREATE INDEX first_ix ON mytbl (col1, col2)
   WITH (DATA_COMPRESSION = ROW)
CREATE INDEX second_ix ON mytbl(col2) INCLUDE (col1, col3)
   WITH (DATA_COMPRESSION = ROW)
CREATE INDEX third_ix ON mytbl (col3) INCLUDE (col2)
   WITH (DATA_COMPRESSION = ROW)

Then someone locally makes changes to these indexes, so that scripted from SSMS they look like this after reformatting and with changes highlighted:

CREATE NONCLUSTERED INDEX [first_ix] ON [dbo].[mytbl]
  ([col1] ASC,	[col2] ASC)
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
         SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
         FILLFACTOR = 80, DATA_COMPRESSION = NONE) 
   ON [IndexFileGroup]
GO
CREATE NONCLUSTERED INDEX [second_ix] ON [dbo].[mytbl]
  ([col2] ASC)
   INCLUDE ([col1], [col3]) 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
        SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
        FILLFACTOR = 80, DATA_COMPRESSION = ROW) 
  ON [IndexFileGroup]
GO
CREATE NONCLUSTERED INDEX [third_ix] ON [dbo].[mytbl] 
  ([col3] ASC) 
  INCLUDE ([col2]) 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
        SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
        FILLFACTOR = 80, DATA_COMPRESSION = ROW) 
  ON [IndexFileGroup]
GO

Next, the source file is changed, so that it now reads:

CREATE INDEX first_ix ON mytbl (col1, col2)
   WITH (DATA_COMPRESSION = ROW)
CREATE INDEX second_ix ON mytbl(col2) INCLUDE (col1, col3)
   WITH (DATA_COMPRESSION = ROW)
CREATE INDEX third_ix ON mytbl (col3) INCLUDE (col2, mydata)
   WITH (DATA_COMPRESSION = ROW)

When you load the changed file to the database, AbaPerls retrieves the storage settings from the database and incorporates the settings that do not appear in the source file and neither are stored in abaobjectstoragesettings. When you load an index, AbaPerls saves information about the index in the table abaindexdefinitions, and from this it is able to tell that:

  1. first_ix is different in the database than in the source file, because the setting for DATA_COMPRESSION is different.
  2. second_ix is unchanged with regards to the source file. The fill factor and filegroup have changed, but these settings are not mentioned in the source file.
  3. third_ix is different in the database, and also different from what was loaded the last time.

This results in the following code being sent to SQL Server (with some reformatting):

PRINT 'Index "mytbl.second_ix" is unchanged. Skipping.'
go
CREATE INDEX first_ix ON mytbl ( col1 , col2 ) 
    WITH ( DATA_COMPRESSION = ROW , FILLFACTOR=80, 
           DROP_EXISTING=ON ) 
    ON [IndexFileGroup]

/* Unchanged: CREATE INDEX second_ix ON mytbl(col2) 
    INCLUDE (col1, col3)
    WITH (DATA_COMPRESSION = ROW)
 */

CREATE INDEX third_ix ON mytbl ( col3 ) INCLUDE ( col2 , mydata ) 
    WITH ( DATA_COMPRESSION = ROW , FILLFACTOR=80, 
    DROP_EXISTING=ON ) 
    ON [IndexFileGroup]

Observe that the fill factor and filegroup are respected in all three cases, but the change in compression is not.

This example tacitly assumed that we reloaded only mytbl.ix, but what happens in an update script, where the original table is moved to a side schema, and new instance of the table is created? The result will be the same. When there is an instance of the old table definition in the side schema, AbaPerls understands to read storage settings from this table. There is one exception, though. If the table is empty, AbaPerls may drop and recreate the table (still honouring storage settings on table and key level). When the update script comes to loading the index file, there is no old table to read settings from. Since few of these settings are particularly interesting for empty tables, this should not be much of an issue.

Controlling Autostats

SQL Server permits you to control auto statistics on several levels. You can set it on database level with ALTER DATABASE, and then you can override it on table level with sp_autostats or UPDATE STATISTICS, for an individual index with the STATISTICS_NORECOMPUTE clause and for a statistics with the NORECOMPUTE clause.

AbaPerls only understands use of the latter two. You can put a call to sp_autostats in a .tbl file, and AbaPerls will accept it without warning. However, it is not really supported, and AbaPerls leaves no guarantees for what will actually happen. So much is clear that AbaPerls will not record it as a setting in abaobjectstoragesettings.

Legacy Databases

The following applies when ABAPERLS subsystem is not present in the database, or is at an earlier version than L1.0.0280:

If the ABAPERLS subsystem was not at label L1.0.0280 when the table or index most recently was loaded, but is on this level when the file is reloaded, the following applies:

This last two restrictions also applies to DBBUILD ‑restruct, as this command empties the tables abaobjectstoragesettings and abaindexdefinitions when it starts it work.