Дизайн базы данных: предпочтительная длина поля для путей к файлам

Ответ 1

Вы можете использовать VARCHAR(MAX) или NVARCHAR(MAX).

Это поля переменной длины, означающие, что они предназначены для хранения значений разной длины. Нет дополнительных накладных расходов для более длинных значений по более коротким значениям.

Определение MAX означает, что поле может содержать до 2 ГБ.

Из MSDN (varchar), nvarchar имеет схожую документацию:

Используйте varchar, когда размеры записей данных столбца значительно различаются.

Используйте varchar (max), когда размеры записей данных столбца сильно различаются, а размер может превышать 8 000 байт.

Ответ 2

Используйте соответствующую длину для данных, которые вы намереваетесь поддерживать. Поскольку вы используете SQL Server, вы должны использовать nvarchar(260) в качестве верхнего предела для хранения имен путей, поскольку это предел спецификации для типичных машин Windows. При определенных обстоятельствах вы можете создавать пути длиннее этого, однако Windows Explorer будет иметь проблемы с их обработкой. SQL Server не может обрабатывать имена файлов длиннее 260 символов. Это включает в себя SQL Server в Linux.

Я могу доказать, что SQL Server использует столбец nvarchar(260) для хранения имен файлов базы данных SQL Server с указанием пути. Проверяя определение представления sys.master_files, мы видим следующий T-SQL:

 CREATE VIEW sys.master_files AS
    SELECT
        database_id     = f.dbid,
        file_id         = f.fileid,
        file_guid       = f.fileguid,
        type            = f.filetype,
        type_desc       = ft.name,
        data_space_id   = f.grpid,
        name            = f.lname,
        physical_name   = f.pname,
        state           = convert(tinyint, case f.filestate     -- Map enum EMDFileState to AvailablityStates
                                when 0 then 0 when 10 then 0    -- ONLINE
                                when 4 then 7   -- DEFUNCT
                                when 5 then 3 when 9 then 3 -- RECOVERY_PENDING
                                when 7 then 1 when 8 then 1 when 11 then 1  -- RESTORING
                                when 12 then 4  -- SUSPECT
                                else 6 end),    -- OFFLINE
        state_desc      = st.name,
        f.size,
        max_size            = f.maxsize,
        f.growth,
        is_media_read_only  = sysconv(bit, f.status & 8),       -- FIL_READONLY_MEDIA
        is_read_only            = sysconv(bit, f.status & 16),  -- FIL_READONLY
        is_sparse           = sysconv(bit, f.status & 256), -- FIL_SPARSE_FILE
        is_percent_growth   = sysconv(bit, f.status & 32),  -- FIL_PERCENT_GROWTH
        is_name_reserved        = sysconv(bit, case f.filestate when 3 then 1 else 0 end), -- x_efs_DroppedReusePending
        create_lsn          = GetNumericLsn(f.createlsn),
        drop_lsn                = GetNumericLsn(f.droplsn),
        read_only_lsn           = GetNumericLsn(f.readonlylsn),
        read_write_lsn      = GetNumericLsn(f.readwritelsn),
        differential_base_lsn   = GetNumericLsn(f.diffbaselsn),
        differential_base_guid  = f.diffbaseguid,
        differential_base_time  = nullif(f.diffbasetime, 0),
        redo_start_lsn          = GetNumericLsn(f.redostartlsn),
        redo_start_fork_guid    = f.redostartforkguid,
        redo_target_lsn     = GetNumericLsn(f.redotargetlsn),
        redo_target_fork_guid   = f.forkguid,
        backup_lsn          = GetNumericLsn(f.backuplsn),
        credential_id       = cr.credential_id
    FROM sys.sysbrickfiles f
    LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate
    LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype
    LEFT JOIN sys.credentials cr ON f.pname LIKE cr.name + N'%' COLLATE database_default
    WHERE f.dbid < 0x7fff -- consistent with sys.databases
        AND f.pruid = 0
        AND f.filestate NOT IN (1, 2)   -- x_efs_Dummy, x_efs_Dropped
        AND has_access('MF', 1) = 1

Документы Microsoft для sys.master_files говорят об этом в столбце physical_name:

физическое_имя nvarchar (260) Имя файла операционной системы.

Но пусть не верят этому. Мы видим, что физическое имя файла упоминается как physical_name = f.pname. И псевдоним таблицы "f" указывает на FROM sys.sysbrickfiles f. Поэтому SQL Server сохраняет имя файла в файле sys.sysbrickfiles, который является внутренней таблицей, видимой только из выделенного соединения администратора или DAC, как его часто называют. Подключаясь к ЦАП и генерируя временную таблицу из выходных данных sys.sysbrickfiles, мы видим следующее:

CREATE TABLE #sysbrickfiles
(
      brickid           int              NOT NULL
    , dbid              int              NOT NULL
    , pruid             int              NOT NULL
    , fileid            int              NOT NULL
    , grpid             int              NOT NULL
    , status            int              NOT NULL
    , filetype          tinyint          NOT NULL
    , filestate         tinyint          NOT NULL
    , size              int              NOT NULL
    , maxsize           int              NOT NULL
    , growth            int              NOT NULL
    , lname             nvarchar(128)    NOT NULL
    , pname             nvarchar(260)    NOT NULL
    , createlsn         binary(10)       NULL
    , droplsn           binary(10)       NULL
    , fileguid          uniqueidentifier NULL
    , internalstatus    int              NOT NULL
    , readonlylsn       binary(10)       NULL
    , readwritelsn      binary(10)       NULL
    , readonlybaselsn   binary(10)       NULL
    , firstupdatelsn    binary(10)       NULL
    , lastupdatelsn     binary(10)       NULL
    , backuplsn         binary(10)       NULL
    , diffbaselsn       binary(10)       NULL
    , diffbaseguid      uniqueidentifier NULL
    , diffbasetime      datetime         NOT NULL
    , diffbaseseclsn    binary(10)       NULL
    , redostartlsn      binary(10)       NULL
    , redotargetlsn     binary(10)       NULL
    , forkguid          uniqueidentifier NULL
    , forklsn           binary(10)       NULL
    , forkvc            bigint           NOT NULL
    , redostartforkguid uniqueidentifier NULL
);

Как видите, столбец pname действительно определен как nvarchar(260).

Кроме того, если мы пытаемся создать базу данных, используя имя файла, которое длиннее 260 символов, мы видим, что возвращается ошибка:

Сообщение 103, Уровень 15, Состояние 3, Строка 7
Файл, который начинается с "F:\AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGH.mdf" слишком долго. Максимальная длина 259.

Использование чего-либо, кроме столбца nvarchar(260) для хранения имен файлов в SQL Server, является и расточительным, и создает технический долг.

Длина столбца чрезвычайно важна с точки зрения производительности. Длина столбца напрямую влияет на:

• память предоставляет для запросов к столбцу. Когда обработчик запросов создает план запроса, он использует размер каждого столбца, присутствующего в запросе, в качестве основы для объема памяти, необходимого для выполнения запроса. Он не использует фактический размер данных, присутствующих в каждом столбце, вместо этого он "догадывается", что средний размер данных будет составлять 50% от максимальной длины столбца.
• Возможность индексировать столбец эффективно. Большие столбцы создают значительно большие индексы. Большие индексы требуют большей памяти и пропускной способности диска, чем меньшие индексы. SQL Server имеет максимальную длину ключа 1700 байт для некластеризованных индексов (по состоянию на SQL Server 2016) и максимальную длину ключа 900 байт для кластеризованных индексов. Если вы попытаетесь создать индекс для столбцов, превышающих эти максимальные суммы, вы получите ошибки, и, возможно, только во время выполнения, когда это может быть очень дорогостоящим, чтобы исправить.
• На производительность символьного первичного/внешнего ключа сильно влияют большие длины столбцов. При обращении к первичным ключам через внешние ключи требования к размеру памяти, диска и ввода-вывода дублируются для каждого внешнего ключа. Возьмем, например, таблицу Customer ключом которой является столбец CustomerName, определенный как varchar(500). Для каждой таблицы, которая ссылается на клиентов, теперь требуется 500-байтовый столбец CustomerName. Если вместо этого этот столбец был определен как varchar(100), каждый запрос, ссылающийся на эти столбцы, будет сохранять 200 байтов на строку в памяти и дисковых операциях ввода-вывода.
• Эрик Дарлинг показывает, что функция Predicate Pushdown не работает для типов данных (MAX), которые могут серьезно ограничивать производительность.

Ответ 3

Если вы используете SQL Server, полезно знать, что Microsoft использует поля nvarchar (260) для хранения пути и имени файла в системных таблицах (например, sys.database_files, или sys.sysaltfiles, или sys.master_files).

Column name      Data type        Description
-------------    -------------    ---------------------------
physical_name    nvarchar(260)    Operating-system file name.

Хорошей практикой может быть использование того же формата для хранения вашего пути и имени файла.

Вам, конечно, нужно будет установить размер в вашем пользовательском интерфейсе, чтобы быть уверенным, что он не будет усечен во время INSERT или UPDATE.

Ответ 4

Я предлагаю вам не хранить пути в существующей таблице. Создайте новую таблицу с последовательным счетчиком в качестве кластерного первичного ключа и столбца символов максимальной длины вашей программы db. Я использую SQL Server, поэтому я бы использовал varchar (max).

Создайте столбец в таблице данных, чтобы сохранить первичный ключ таблицы "пути". Сначала вставьте в таблицу "пути", затем используйте первичный ключ в качестве внешнего ключа в таблице данных.

Преимущество сохранения значения в другой таблице - это не влияет на размер данных базовой таблицы. Запросы базовой таблицы, которые не связаны с "путями", не страдают от необходимости вытягивать большое значение символа, что увеличивает IO-трафик.

Ответ 5

Длина пути к файлу не может быть предсказана. Он может быть очень коротким как 'C:\' или может быть очень длинным, как 'C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn\Resources\1033' или даже больше. Но на уровне базы данных нет никакого вреда, используя что-то вроде VARCHAR(MAX)

См. Максимальный размер VARCHAR (MAX)

Ответ 6

Поле должно быть такой же длины, как длина строки строки.

Как вопрос о длине имени файла, как просить длину строки, задавая длину пути, вы спрашиваете длину всех битов строки в поле неизвестного размера.

Таким образом, единственная разумная опция без какой-либо другой информации не ограничивает длину, например. NVARCHAR (MAX)

Ответ 7

Я бы рекомендовал VARCHAR (2048) или даже VARCHAR (1024), поскольку пути к файлам обычно не длинны 2000 символов.