Geek Answers Handbook

What is a supported way to store large text fields without sacrificing performance?

I have been dancing around this problem for a while, but it continues to rise. We have a system, and our tables of our tables start with a description that is initially stored as NVARCHAR(150), and then I get a ticket asking me to increase the size of the field to 250, then 1000, etc. ....

This loop repeats in the “note” and / or “description” field, which we add to most tables. Of course, caring for me is performance and violating the 8k page limit. However, my other problem is to make the system less maintainable by tearing these fields from the EVERY table into the system into a lazy loaded link.

So, I came across the same two options that looked in my face. (others are welcome), please lend me your opinion.

  • Change all notes and / or descriptions to NVARCHAR(MAX)and make sure that we exclude these fields in all lists. In principle, never do a: SELECT * FROM [TableName]unless it retrieves one record.

  • Delete all fields of notes and / or descriptions and replace them with a link to the forign link to the table [Notes].

    CREATE TABLE [dbo].[Notes] (
    [NoteId] [int] NOT NULL,
    [NoteText] [NVARCHAR]    ( MAX)NOT NULL )

Obviously, I would prefer to use option 1, because it will change so much in our system, if we go with 2. However, if option 2 is really the only good way to continue, then at least I can say that these changes are needed, and I did my homework.

UPDATE: 100 000 . , - -, 1, 2. (1000 ), 1 , select. , . -, 50 , 1 , 2 () .

+5
5

2 :

  • , . , .
  • , . Microsoft SQL Server 2008, VARCHAR/VARBINARY.
  • . , , . , , .

1 , 2 . , "", , . , - (SET STATISTICS IO ON) .

, TEXT/NTEXT MSDN:

Microsoft SQL . , . varchar (max), nvarchar (max) varbinary (max) . , . .

+5

2.

, , , , .

+2

. TEXT ; . , - TEXT, , .

, TEXT . , varchar, - , .

+2

TEXT/NTEXT , .

, , "/" .

+1

2

:

MyTable MyTable_V2

Notes Notes ( 1:1)

VIEW MyTable, MyTable_V2 Notes

INSTEAD OF MyTable, Notes Notes (IF NULL Notes, NOT NULL, Insert, , Update). MyTable_V2

. , Computed MyTable_V2 ( , , "" )

Insert/Update/Delete MyTable_V2 Notes

: INSERT OF MyTable, ( , GetDate(), > 24 - ).

, INSTEAD OF MyTable, MyTable_V2!

, .

MyTable MyTable_V2, MyTable SELECT INSTEAD OF.

, Insert/Update/Delete, MyTable. , , , , FIND, .

P.S. 2 , SELECT *. , , Text/Blob - - "" SELECT *. , !

+1

More articles:


All Articles