Here is a quick demonstration that shows you what can happen when you use defaults that are much shorter than the value that is updated from an insert trigger. The ModifiedBy column has a default of ” but in the trigger it gets updated to ‘SomeapplicationName used by ’ +SUSER_NAME(). Ideally you want the default to also be ‘SomeapplicationName used by ’ +SUSER_NAME()

Let’s take a look, first create the following table

sql CREATE TABLE TestFrag ( id INT NOT NULL IDENTITY PRIMARY KEY, SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid(), Somedate datetime DEFAULT GETDATE(), SomeOtherData CHAR(200) DEFAULT ‘bla’, ModifiedBy varchar(100) DEFAULT “)

Add a trigger on that table

sql
CREATE TRIGGER [dbo].[Tr_TestFrag] ON [dbo].[TestFrag] FOR Insert 
AS
UPDATE t
        SET    ModifiedBy = 'SomeapplicationName used by ' +SUSER_NAME()
        FROM   TestFrag t
        JOIN inserted i ON t.id = i.id
       
GO

Now add another identical table, the only difference is that the default matches what gets updated from within the trigger

sql CREATE TABLE TestFrag2 ( id INT NOT NULL IDENTITY PRIMARY KEY, SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid(), Somedate datetime DEFAULT GETDATE(), SomeOtherData CHAR(200) DEFAULT ‘bla’, ModifiedBy varchar(100) DEFAULT ‘SomeapplicationName used by ’ +SUSER_NAME())


Here is the other trigger

sql
CREATE TRIGGER [dbo].[Tr_TestFrag2] ON [dbo].[TestFrag2] FOR Insert 
AS
UPDATE t
        SET    ModifiedBy = 'SomeapplicationName used by ' +SUSER_NAME()
        FROM   TestFrag2 t
        JOIN inserted i ON t.id = i.id
       
GO

Now let’s pump 100000 rows into both tables

sql INSERT TestFrag(Somedate) SELECT TOP 100000 GETDATE() FROM sysobjects s1 CROSS JOIN sysobjects s2 CROSS JOIN sysobjects s3


sql
INSERT TestFrag2(Somedate)
SELECT TOP 100000 GETDATE() 
FROM sysobjects s1
CROSS JOIN sysobjects s2
CROSS JOIN sysobjects s3

Run this to just verify that we have the same data

sql SELECT TOP 10 * FROM TestFrag SELECT TOP 10 * FROM TestFrag2


Now let's look how much space each table is using

sql
EXEC sp_spaceused 'TestFrag'
EXEC sp_spaceused 'TestFrag2'
name       rows        reserved    data        index_size  unused
TestFrag    100000      47240 KB    47064 KB    104 KB      72 KB
TestFrag2   100000      28744 KB    28576 KB    112 KB      56 KB

See that, the table with the same default as in the trigger is using a lot less data?

Now let’s see how bad the fragmentation is. Here is the 2005 and up version

sql SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName ,indexstats.index_id ,index_type_desc ,avg_fragmentation_in_percent ,page_count ,record_count ,fill_factor FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID AND i.index_id = indexstats.index_id WHERE OBJECT_NAME(i.OBJECT_ID) in (‘TestFrag’,‘TestFrag2’) ```

TableName index_id index_type_desc avg_fragmentation_in_percent page_count record_count fill_factor
TestFrag 1 CLUSTERED INDEX 99.422063573007 5883 100000
TestFrag 1 CLUSTERED INDEX 11 5883
TestFrag 1 CLUSTERED INDEX 1 11
TestFrag2 1 CLUSTERED INDEX 0.335946248600224 3572 100000
TestFrag2 1 CLUSTERED INDEX 12 3572
TestFrag2 1 CLUSTERED INDEX 1 12

Yikes, do you see that, one table is completely fragmented.

Here is the 2000 version by using showcontig

DBCC SHOWCONTIG scanning 'TestFrag' table...
Table: 'TestFrag' (933578364); index ID: 1, database ID: 14
TABLE level scan performed.
- Pages Scanned................................: 5883
- Extents Scanned..............................: 738
- Extent Switches..............................: 5879
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.52% [736:5880]
- Logical Scan Fragmentation ..................: 99.42%
- Extent Scan Fragmentation ...................: 0.27%
- Avg. Bytes Free per Page.....................: 3234.5
- Avg. Page Density (full).....................: 60.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


DBCC SHOWCONTIG scanning 'TestFrag2' table...
Table: 'TestFrag2' (1205579333); index ID: 1, database ID: 14
TABLE level scan performed.
- Pages Scanned................................: 3572
- Extents Scanned..............................: 450
- Extent Switches..............................: 449
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.33% [447:450]
- Logical Scan Fragmentation ..................: 0.34%
- Extent Scan Fragmentation ...................: 0.44%
- Avg. Bytes Free per Page.....................: 89.3
- Avg. Page Density (full).....................: 98.90%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As you can see you will get horrible page splits if your trigger expands the row every time an insert happens.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum