1

I'm adapting an audit scheme from this: https://weblogs.asp.net/jongalloway/adding-simple-trigger-based-auditing-to-your-sql-server-database which calls for creating an audit table like this:

CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserNamevarchar(128)
)

I want to ensure that I'm storing as little as possible for each row that is inserted into the Audit table. In my case, I can shorten the width of TableName, PrimaryKeyField, PrimaryKeyValue and FieldName because I know the actual max lengths of those things. I have one particular table that has a particular column defined as nvarchar(2000), so if I stay with this generic approach I'll need to use nvarchar(2000) for OldValue and NewValue.

I was wondering about SQL Server's internal optimizations regarding storage space actually used in a page. When a row is stored with long varchars or nvarchars, is the full defined length actually used to store the row in a page, or does SQL Server only store the actual bytes used. For example, if only Hello! is stored in OldValue above, how many bytes will the OldValue actually occupy in the page for such a row?

Brian Eriksen
  • 77
  • 6

2 Answers2

0

For nvarchar(N) it would be 2 + (2*N) maximum bytes stored in row for this value. The first 2 are the length, and then 2 bytes per unicode character actually stored in the row. This is the maximum space occupied.

If your unicode string is only 15 characters then it would be 32 bytes (even as an nvarchar(2000) datatype).

I would also recommend using sysname for names of system objects for forward (and backward) compatibility in your scripts.

You also might be interested in this audit script: Quick And Easy Audit Tables - Dave Britten

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Thanks, SqlZim, for the quick reply! So why does SQL Server bother with `nvarchar(N)`? I understand the difference between in-page storage for `nvarchar(N)` where N <= 4000 vs large object storage for `nvarchar(max)`, but why not have just nvarchar(4000) and `nvarchar(max)` to keep things simple? Are there some additional optimizations internal to SQL Server that make, say, `nvarchar(100)` more efficient than `nvarchar(4000)`? – Brian Eriksen Apr 09 '17 at 13:46
  • `nvarchar(max)` is handled differently, and even as a variable can have significant impact on performance, as it supports up to 2gb of data. Using a smaller maximum (N) for `nvarchar()` would have an impact on calculations for estimating memory grants. There is also index key width to be considered as well: [Maximum Size of Index Keys](https://technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx) – SqlZim Apr 09 '17 at 13:53
0

When a row is stored with long varchars or nvarchars, is the full defined length actually used to store the row in a page, or does SQL Server only store the actual bytes used

Even though ,you define a variable length column ,SQL will use the storage space for only the values that are declared..

This has been covered here :How are varchar values stored in a SQL Server database?

Below is the relevant part taken from answer of Martin Smith

All varchar data is stored at the end of the row in a variable length section (or in offrow pages if it can't fit in row). The amount of space it consumes in that section (and whether or not it ends up off row) is entirely dependant upon the length of the actual data not the column declaration

Below is a sample demo to prove the same

create table dbo.test1

(
fixedlencol varchar(400),
varlengthcol varchar(max),
var_len_Nvarcahrcol nvarchar(max)
)

insert into dbo.test1
values
('a','abc','def')

Now if use DBCC PAGE and DBCC IND following the example from here:Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back

below is the output,i got for first row insert

Slot 0 Column 1 Offset 0xf Length 1 Length (physical) 1

fixedlencol = a

varlengthcol = [BLOB Inline Data] Slot 0 Column 2 Offset 0x10 Length 3 Length (physical) 3

varlengthcol = 0x616263

var_len_Nvarcahrcol = [BLOB Inline Data] Slot 0 Column 3 Offset 0x13 Length 6 Length (physical) 6

Reason for last column taking double bytes than actual length is due to Nvarchar..

Community
  • 1
  • 1
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94