Adding columns to large tables

We are using Navision SQL, our database is 14gig. several tables GL entry, Vendor LE, Job LE are well over a gig each. When we add columns to these tables, it takes sometimes 15 minutes for the table to compile. Is this just a function of the server’s horsepower or is there something else we should look at? Thanks

I still have the problem but I think I found the cause. When you create a new field in Navision, SQL creates a column that does not allow Nulls. This means the table has to insert the Navision default value for the datatype into every record. which creates a huge log file and uses a lot of resources especially when the table has 800,000+ records. Does this sound like the cause? And has anyone found a solution? Can you create the column in SQL and then create the field in Navision? (How do you add the Navision field number?) Is there a way to tell Navision to allow NUlls in SQL? Thanks for any help.

Hi Post !? In Database options there is an option to set “ANSI Null default”. ---- from w1w1isgl.pdf----- This setting controls the database default NULL settings for column definitions and user-defined data types. When you select this option, all user-defined data types or columns that have not been explicitly defined as NOT NULL default to allow NULL entries. Columns that have been defined with constraints follow the constraint rules regardless of this setting.

Hi, I have seen this “ansi Null default” but I don’t know if this option setting on true can cause problems in Navision (filtering, reports, speed, …). I think default it’s false, so this mostly a hint that it should be (or that’s the best option) Can anyone give his opinion/experience with this option ??? Thanks in advance, Nico

If this is the cause you can easily see it by running the SQL Profiler then you will see if the ALTER TABLE ADD COLUMN statement is with one running with 15min duration - or not. The setting mentioned does not help you. It is only, as it say, when an explicit NULL or NOT NULL is ommitted from the column and creation time that this setting applies. Navision creates its columns explicitly with NOT NULL (all except the BLOB). Allowing NULLs in columns leads to many many problems. All matches for values in filters must take into account a NULL as a special case. E.g. if we have a field MyField and you would like to find all values not equal to the value ‘GoodValue’ you might make a filter in SQL like: … WHERE MyField <> ‘GoodValue’. Now for records with NULL in this field would you expect them to match this criteria? Well, they wont becase NULL is neither equal to or unequal to any other value. So you have to add a special OR IS NULL for every such field filter. There are good reasons for not allowing NULLs but I see your problem. How often are you adding fields to such a huge table that 15 mins is unacceptable? You can add the column externally in your table, allowing NULL instead of NOT NULL and ensuring the SQL data type matches that which is used by attain (e.g. VARCHAR(10) for a Text10, or whatever). Then in Attain you can set the LinkedObject property of the table to Yes and save it (should be instant). Then add the field to the table in attain and save. Now since this is a linked object and the field already exists in the table, Attain will just validate that the data type is correct in the SQL table, and will save the table description. To make the table owned by Attain again (a LinkedObject means it is no longer owned by Attain), set the LinkedObject property to No. You should be able to use the table normally, except the NULL values present in this field will not match filters in the way I described. If you modify a record, setting any empty text value or undefined date or 0 integer (i.e. an Attain default) this will replace the NULL that was in the field. Attain never stored NULLs in the database, so it is not symetrical.