Field type for occasional remarks in MS SQL

I need to add a new field to my MS SQL database table. It’s intended for entering some remarks by the user. Now, these remarks can be rather long, like few thousand chars but it is quite likely remarks will be seldom entered.
So I need a field type which will not cost the database/table 64k of whitespace in each record just so that someone can enter a few char remark every now and then. Varchar comes to mind.

What do you suggest I use? a BLOB field in the dictionary and force varchar(max) with FM3 I’m using?

Any other idea?

Thanks

Bostjan

Consider a related table “Remarks” with 1:1 relationship.

2 Likes

I agree with Mike. A separate related table.

Also agree, but would consider whether 1:1 is needed or perhaps 1:M with timestamp & username cols along with the remark.

1 Like

Depending on the expected size, I’d go with VARCHAR (max length 8K). (Assuming 8-bit characters that don’t require NVARCHAR, which is limited to 4K characters).
Unless you pad the string with spaces, SQL will store it very compactly.
And with any of the last few versions of SQL you can always compress the data if needed.

Here are four tables. Three use VARCHAR, which does not store whitespace. One uses CHAR, which does.
Screenshot at the end shows the storage space taken for a million records with the string NULL, empty, or having a bit of data for the VARCHAR tables. And table4, which uses CHAR, with whitespace stored before and after setting PAGE compression.

varchars

SET ANSI_NULLS ON;
GO

CREATE TABLE dbo.test1 (pk INT NULL, theString VARCHAR(1000) NULL);
SET NOCOUNT ON;
DECLARE @x INT=0;
WHILE @x<1000001 BEGIN;
    SET @x += 1;
    INSERT INTO dbo.test1(pk)VALUES(@x);
END;

GO

CREATE TABLE dbo.test2 (pk INT NULL, theString VARCHAR(1000) NULL);
SET NOCOUNT ON;
DECLARE @x INT=0;
WHILE @x<1000001 BEGIN;
    SET @x += 1;
    INSERT INTO dbo.test2(pk,theString)VALUES(@x,'');
END;

GO

CREATE TABLE dbo.test3 (pk INT NULL, theString VARCHAR(1000) NULL);
SET NOCOUNT ON;
DECLARE @x INT=0;
WHILE @x<1000001 BEGIN;
    SET @x += 1;
    INSERT INTO dbo.test3(pk,theString)VALUES(@x,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
END;

GO

CREATE TABLE dbo.test4 (pk INT NULL, theString CHAR(1000) NULL);
SET NOCOUNT ON;
DECLARE @x INT=0;
WHILE @x<1000001 BEGIN;
    SET @x += 1;
    INSERT INTO dbo.test4(pk)VALUES(@x);
END;

GO


EXEC sp_spaceused 'dbo.test1'
EXEC sp_spaceused 'dbo.test2'
EXEC sp_spaceused 'dbo.test3'
EXEC sp_spaceused 'dbo.test4'

ALTER TABLE [dbo].[test4] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
EXEC sp_spaceused 'dbo.test4'
5 Likes

I think all approaches mentioned are applicable.
It would depend more on functional requirements and programmers preferences, because as Jane mentioned, thanks for the example and results by the way, there are ways where disk usage is optimized and hopeful perfomance too.
With functional requirements I meant that with Douglas suggestion it would allow remarks traceability (users and time stamps), and would imply a slighty different user interface (INSERT,CHANGE,DELETE related remarks), while Mike/Sean suggestion, the Remarks Text Field can be implemented transparently to the user, hidding details of how it is stored.
Here it is a related thread:
https://clarionhub.com/t/pervasive-scalable-no-memo-change-to-blob-but-how-can-browse-show-blob/6046
You can see there other benefits of using a VARCHAR on the database and CSTRING on Clarion istead of a BLOB for easier access to the data and using the field directly on the template prompts. A thing to monitor if using a large string on a VIEW (Browse Column/HotField) is that POSITION(view) used on browse class works fine. See PTSS 42989 and this thread: https://clarionhub.com/t/windowmanager-update-calling-browseclass-updateviewrecord-rc-errorcode-error-78-invalid-number-of-parameters/5600/2
But I think with the proper unique keys on other fields it should work fine.

Thank you guys, you are great!

I will go with Mike/Sean’s idea, it’s perfect for this case.

Jane’s analysis is something worth looking at, nice work, thank you.

Sometimes I manage to not come up with simplest solution :slight_smile:

Bostjan