XML FeedsHere 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
- 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
- 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
- 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
- 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
- INSERT TestFrag(Somedate)
- SELECT TOP 100000 GETDATE()
- FROM sysobjects s1
- CROSS JOIN sysobjects s2
- CROSS JOIN sysobjects s3
- 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
- SELECT TOP 10 * FROM TestFrag
- SELECT TOP 10 * FROM TestFrag2
Now let's look how much space each table is using
- 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
- 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 | 0 |
| TestFrag | 1 | CLUSTERED INDEX | 0 | 11 | 5883 | 0 |
| TestFrag | 1 | CLUSTERED INDEX | 0 | 1 | 11 | 0 |
| TestFrag2 | 1 | CLUSTERED INDEX | 0.335946248600224 | 3572 | 100000 | 0 |
| TestFrag2 | 1 | CLUSTERED INDEX | 0 | 12 | 3572 | 0 |
| TestFrag2 | 1 | CLUSTERED INDEX | 0 | 1 | 12 | 0 |
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
makes perfect sense to me!
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.