Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 DATALENGTH width column : реальность vs документация  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 313
Привет,

Вопрос наверно простейший для местных гуру
Есть таблица MS SQL 2008 Standard:

	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[ValueFloat] [float] NULL,
	[ValueInt] [int] NULL,
	[ValueString] [varchar](max) NULL,
	[ValueBool] [bit] NULL


В ней только одно из Value... может быть заполнено. Американский дизайнер утверждает, что это для того, чтобы "save storage space". Ну то, что Америка всегда не права, это понятно. Кроме того, есть документация, которая утверждает, что fixed width data always uses space even when null.

Я вот думаю, какие есть у него аргументы? Может быть такой запрос заставляет его так думать?
SELECT DATALENGTH(ValueFloat), DATALENGTH(ValueInt)
FROM core.teEventProperty

Который всегда возвращает что-то типа
X NULL
X NULL
NULL X

Я пока что не нашел примеров, НЕ использующих DATALENGTH для подобных целей.
Может подскажете?
12 май 14, 13:36    [16004105]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Glory
Member

Откуда:
Сообщений: 104760
Glebanski
Может подскажете?

Подсказать, что думает "американский дизайнер про save storage space" ?

Glebanski
Я пока что не нашел примеров, НЕ использующих DATALENGTH для подобных целей.

Returns the number of bytes used to represent any expression.

представление/визуализация данных не есть хранение данных
12 май 14, 13:45    [16004156]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Estimate the Size of a Table
sql_variant (Transact-SQL)
12 май 14, 14:04    [16004306]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 313
Это прекрасно, я понимаю разницу м-ду represent и store.
Думаю скорее о том, не пропустил ли я еще что-то мелким шрифтом в документации. Факт в том, что я не DBA со столетним опытом и не собираюсь указывать заморским коллегами как им надо разрабатывать БД
12 май 14, 14:05    [16004313]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
+ Тест
use tempdb
GO
CREATE table dbo.test1 (
	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[ValueFloat] [float] NULL,
	[ValueInt] [int] NULL,
	[ValueString] [varchar](max) NULL,
	[ValueBool] [bit] NULL
)
CREATE table dbo.test2 (
	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[Value] SQL_Variant NULL,
	[ValueBig] [varchar](max) SPARSE,
)
insert into dbo.test1 ([DBID],[EventPropertyTypeID],[EventDtm],[ValueInt]) values (1,1,GetDate(),NULL),(2,2,GetDate(),1)
insert into dbo.test2 ([DBID],[EventPropertyTypeID],[EventDtm],[Value]   ) values (1,1,GetDate(),NULL),(2,2,GetDate(),1)
dbcc showcontig ('dbo.test1') with tableresults
dbcc showcontig ('dbo.test2') with tableresults

DROP TABLE dbo.test2, dbo.test1;
33+33 vs 20+25
12 май 14, 14:06    [16004321]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 313
Mnior
dbcc showcontig ('dbo.test2') with tableresults

DROP TABLE dbo.test2, dbo.test1;

33+33 vs 20+25


Отлично! Спасибо за убойный пример
Главное, никого тыкать в RTFM не придется ;)
12 май 14, 14:12    [16004368]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
он_лжет
Guest
+ нет никакой экономии места


инсерты

use db_drop_me;
go

create table t
(
	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[ValueFloat] [float] NULL,
	[ValueInt] [int] NULL,
	[ValueString] [varchar](max) NULL,
	[ValueBool] [bit] NULL
);
go

insert into t([dbid], EventPropertyTypeID, EventDtm) values(1, 1, getdate());
go

insert into t([dbid], EventPropertyTypeID, EventDtm, ValueFloat, ValueInt, ValueBool) values(1, 2, getdate(), 3.0, 4, 1);
go


дампы строк

insert into t([dbid], EventPropertyTypeID, EventDtm) values(1, 1, getdate());
go

0000000000000000:   10001e00 01020000 004e804d 84380b00 00000000  .........N€M„8......
0000000000000014:   00000008 40040000 00010700 20                 ....@....... 

insert into t([dbid], EventPropertyTypeID, EventDtm, ValueFloat, ValueInt, ValueBool) values(1, 2, getdate(), 3.0, 4, 1);
go

0000000000000000:   10001e00 01010000 00628d4d 84380b00 00b91017  .........bM„8......
0000000000000014:   00000000 70ba1017 00000700 f8                 ....pº......ø

Как видишь строки занимают одинаковое число байт.

[spoiler]

[spoiler подробно]
PAGE: (1:77)


BUFFER:


BUF @0x000000027A146CC0

bpage = 0x000000022C862000          bhash = 0x0000000000000000          bpageno = (1:77)
bdbid = 8                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 18971                       bstat = 0xb
blog = 0x21215acc                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x000000022C862000

m_pageId = (1:77)                   m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594043432960                                
Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0
Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 30                        m_slotCnt = 2                       m_freeCnt = 8026
m_freeData = 162                    m_reservedCnt = 0                   m_lsn = (34:143:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 33

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 33

Memory Dump @0x000000000F54A060

0000000000000000:   10001e00 01020000 004e804d 84380b00 00000000  .........N€M„8......
0000000000000014:   00000008 40040000 00010700 20                 ....@....... 

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

DBID = 1                            

Slot 0 Column 2 Offset 0x5 Length 4 Length (physical) 4

EventPropertyTypeID = 2             

Slot 0 Column 3 Offset 0x9 Length 8 Length (physical) 8

EventDtm = 2014-05-12 14:06:31.18 +00:00                                 

Slot 0 Column 4 Offset 0x11 Length 8 Length (physical) 8

ValueFloat = 3.000000000000000e+000 

Slot 0 Column 5 Offset 0x19 Length 4 Length (physical) 4

ValueInt = 4                        

Slot 0 Column 6 Offset 0x0 Length 0 Length (physical) 0

ValueString = [NULL]                

Slot 0 Column 7 Offset 0x1d Length 1 (Bit position 0)

ValueBool = 1                       

Slot 1 Offset 0x81 Length 33

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 33

Memory Dump @0x000000000F54A081

0000000000000000:   10001e00 01010000 00628d4d 84380b00 00b91017  .........bM„8......
0000000000000014:   00000000 70ba1017 00000700 f8                 ....pº......ø

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

DBID = 1                            

Slot 1 Column 2 Offset 0x5 Length 4 Length (physical) 4

EventPropertyTypeID = 1             

Slot 1 Column 3 Offset 0x9 Length 8 Length (physical) 8

EventDtm = 2014-05-12 14:07:04.66 +00:00                                 

Slot 1 Column 4 Offset 0x0 Length 0 Length (physical) 0

ValueFloat = [NULL]                 

Slot 1 Column 5 Offset 0x0 Length 0 Length (physical) 0

ValueInt = [NULL]                   

Slot 1 Column 6 Offset 0x0 Length 0 Length (physical) 0

ValueString = [NULL]                

Slot 1 Column 7 Offset 0x0 Length 0 Length (physical) 0

ValueBool = [NULL]                  


12 май 14, 14:14    [16004397]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
o-o
Guest
вообще-то есть экономия,
но только в одном конкретном случае, и это не случай ТС.
пример приведен в книге Kalen Delaney Microsoft SQL Server 2008 Internals
(глава 5, подраздел "NULLS and Variable-Length Columns")
вкратце, если есть NULLABLE столбцы переменной длины, к-ые по большей части содержат NULL,
и если они идут последними в определении таблицы, то экономится место на 2-byte column offset entry
на каждом NULL-столбце, к-ый идет за последним заполненным NOT NULL значением
(в каждой строке, где заполнены не все из этих nullable-колонок)

For variable-length columns, NULLs don’t take any space in the variable-length
data part of the row. However, as we saw in Figure 5-11, there is still a 2-byte column offset
entry for each variable-length column, so we can’t say that they take no space at all. However,
if a zero-length value is stored at the end of the list of variable-length data columns, SQL
Server does not store any information about it and does not include the 2 bytes in the column
offset array.

+
create table t
(
	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[ValueFloat] varchar(5) NULL,
	[ValueInt] varchar(5) NULL,
	[ValueString] varchar(5) NULL,
	[ValueBool] varchar(5) NULL
);
go

insert into t([dbid], EventPropertyTypeID, EventDtm) values(1, 1, getdate());
go

insert into t([dbid], EventPropertyTypeID, EventDtm, ValueFloat, ValueInt, ValueBool) values(1, 2, getdate(), '3.0', '4', '1');
go

dbcc ind ('db1', 't', 1)

dbcc traceon(3604);
go
dbcc page (db1, 1, 158, 1);
go

----------------------------------------
...
Slot 0, Offset 0x60, Length 20, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 20

Memory Dump @0x41F1C060

00000000: 10001100 01010000 00de9547 84380b00 †.........Þ•G„8..
00000010: 00070078 ††††††††††††††††††††††††††††...x

Slot 1, Offset 0x74, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 35
Memory Dump @0x41F1C074

00000000: 30001100 01020000 00de9547 84380b00 †0........Þ•G„8..
00000010: 00070020 04002100 22002200 2300332e †... ..!.".".#.3.
00000020: 303431†††††††††††††††††††††††††††††††041


12 май 14, 15:17    [16004845]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
он_лжет
Guest
o-o

хех. ту же самую книжку читали. я когда у видел у автора определение таблицы - у меня бомбануло. :)
12 май 14, 15:41    [16005011]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
o-o
вообще-то есть экономия
По сравнению с чем?
Бородатый анекдот
— Да! Вы не ошиблись! Только не я, а мой сосед. И не вчера, а сегодня. И не выиграл, а проиграл. И не в лотерею, а в карты. И не автомобиль, а 100 рублей.
;)
13 май 14, 19:31    [16012109]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
o-o
Guest
Mnior
o-o
вообще-то есть экономия
По сравнению с чем?

bentornato prilipallus volgaris!!!

проблемы с буржуйским языком али с русским?
еще раз: если имеются NULLable-колонки переменной длины,
про к-ые заранее известно, что их содержимое в основном NULL-ы,
то, располагая такие колонки в определенном порядке, а именно: самые в основном NULL-заполненные -- последними
(среди столбцов переменной длины, фиксированные все равно вперед вынесет),
будет экономия места в хранении. на column offset-ах (по 2 байта с каждого) тех, что NULL, следующих за последним NOT NULL.

заполняю таблицу из моего примера одинаковыми значениями varchar(5): '1', null, null, null
в разном порядке.
самый "экономный" вариант, это когда не null-значение ('1') идет самым первым:

create table t
(
	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[ValueFloat] varchar(5) NULL,
	[ValueInt] varchar(5) NULL,
	[ValueString] varchar(5) NULL,
	[ValueBool] varchar(5) NULL
);
go

insert into t([dbid], EventPropertyTypeID, EventDtm, ValueFloat) values(1, 1, getdate(),'1');
go
insert into t([dbid], EventPropertyTypeID, EventDtm, ValueInt) values(1, 1, getdate(),'1');
go
insert into t([dbid], EventPropertyTypeID, EventDtm, ValueString) values(1, 1, getdate(),'1');
go
insert into t([dbid], EventPropertyTypeID, EventDtm, ValueBool) values(1, 1, getdate(),'1');
go

dbcc ind ('db1', 't', 1)

dbcc traceon(3604);
go
dbcc page (db1, 1, 195, 1);
go

-----------------------------------------------------------------------------------------
DATA:


Slot 0, Offset 0x60, Length 25, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 25
Memory Dump @0x000000000D9AA060

0000000000000000: 30001100 01010000 0077136f 85380b00 †0........w.o…8..
0000000000000010: 00070070 01001900 31†††††††††††††††††...p....1

Slot 1, Offset 0x79, Length 27, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x000000000D9AA079

0000000000000000: 30001100 01010000 007c136f 85380b00 †0........|.o…8..
0000000000000010: 00070068 02001a00 1b0031†††††††††††††...h......1

Slot 2, Offset 0x94, Length 29, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 29
Memory Dump @0x000000000D9AA094

0000000000000000: 30001100 01010000 007d136f 85380b00 †0........}.o…8..
0000000000000010: 00070058 03001c00 1c001d00 31††††††††...X........1

Slot 3, Offset 0xb1, Length 31, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 31
Memory Dump @0x000000000D9AA0B1

0000000000000000: 30001100 01010000 007d136f 85380b00 †0........}.o…8..
0000000000000010: 00070038 04001e00 1e001e00 1f0031††††...8..........1
13 май 14, 22:33    [16012807]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
o-o
проблемы с буржуйским языком али с русским?
Нет, это у кого-то проблемы с пониманием, в том числе риторических вопросов.


Другой бородатый анекдот про блох ... но он не подходит, ибо блохи с собакой хоть как-то связаны, а так полный оффтоп.
o-o
это не случай ТС
После этого осознания - смысл постить про вариабельные колонки отпадает напрочь.
При этом само по себе это явление практической ценности не несёт. Я б даже сказал большее паразитирует и сбивает с толку, всех.
SPARSE колонки будут более практичны. На которые я вскользь упомянул в посте выше.
"Собирать явления" может и хорошо, но понимать их место в общей картине и почему оно там - важнее.

o-o
bentornato prilipallus volgaris!!!
Хоть что-то ценное из этих постов.
Зачем так бурно реагировать? Гус Фраба

PS: А смысловая предпосылка к самоповторению вообще эпична.
Что либо добавлять не имеет смысла.
14 май 14, 23:28    [16018395]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
o-o
Guest
Mnior
Нет, это у кого-то проблемы с пониманием, в том числе риторических вопросов.

o-o
это не случай ТС
После этого осознания - смысл постить про вариабельные колонки отпадает напрочь.


Glebanski
	[DBID] [tinyint] NOT NULL,
	[EventPropertyTypeID] [int] NOT NULL,
	[EventDtm] [datetimeoffset](2) NOT NULL,
	[ValueFloat] [float] NULL,
	[ValueInt] [int] NULL,
	[ValueString] [varchar](max) NULL,
	[ValueBool] [bit] NULL

...
В ней только одно из Value... может быть заполнено. Американский дизайнер утверждает, что это для того, чтобы "save storage space".
Я вот думаю, какие есть у него аргументы? Может быть такой запрос заставляет его так думать?


вопрос был, что товарища заставляет так думать.

мое предположение: где-то что-то слышал об экономии места,
если собрать NULLABLE-колонки в конце таблицы, типа они же не все одновременно будут заполнены.
я и говорю: есть такое, но не в случае ТС, т.к. здесь fixed width.

ну не видите вы лично аналогии, проблемы ваши.
я вижу, что чел собрал эти столбцы в конце таблицы и упирает на то, что NULLABLE и всего одно будет заполнено

у мифов обычно имеется некая действительная основа,
только "выпускается" какой-то определяющий момент,
в результате получается туфтень.

вот кому-то видно, что fixed width-колонки они и есть fixed width,
а кому-то это и неважно, главное NULLABLE и собрать в конце.
из серии: слышал, что хорошо бы базу в нескольких файлах разместить.
ну и заодно несколько лог-файлов создам


15 май 14, 11:04    [16019722]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
o-o, Ок, принимается, но по другим причинам.

А вот то что вы выделии:
o-o
Glebanski
В ней только одно из Value... может быть заполнено
Как раз наоборот полностью нивелирует всё. Ибо смыславая нагрузка на одно значение размазать по нескольким типовым колонкам, а не переместить колонки в конец таблицы (что можно было бы предположить).

Смотря на смысловую нагрузку колонок, как раз порядок полей имеет нормальную природу Ключ-Свойства, "ненормальной" сортировки не наблюдается. Более того, String-овый тип более вероятнее чем остальные.
И если предположить что они забыли про условие "переменной длины", то сортировать надо было от малого размера к большему (т.е. float должен был быть последним).

Тут как минимум два условия "В конце" + "Variable". Про fixed было добавлено именно ТС, а не америкосами (иначе противоречие), у них "null data [not] uses space".

Поэтому я и "спросил" "по сравнению с чем?". Выражение:
Glebanski
Американский дизайнер утверждает, что это для того, чтобы "save storage space".
Что имелось ввиду у ТС под "этим" и что имели ввиду америкосы?!
Я понимаю формулировку "для сохранение типа", "для ускорения", при этом на возражение "но так жрёт больше", было бы отвечено "null не занимает места". Но это на возражение, а не "для чего так".
Тут на каждом шаге рассуждений выносит моск.

Я не знаю что сподвигнуло вас к написанию поста (уже никто не узнает), и я предположил что зацепка к словам, но не к смыслу (тим и всё такое). И тут есть важное замечание, что мне тяжело видеть зацепки по похожести звучания, чем вам (и главное самими америкосами), но я не вижу никаких похожести первоначальных слов (we store one value to multiple columns for ...) или иных "похожестей" у самих америкосов с приведённым вами "явлением".
Т.е. анекдот был в тему.

+ del
o-o
вопрос был, что товарища заставляет так думать
ТС или америкосов? Вы уверены что правильно поняли ТС?
o-o
мое предположение: где-то что-то слышал об экономии места
И это аргумент что одна колонка хуже чем несколько?
o-o
если собрать NULLABLE-колонки в конце таблицы, типа они же не все одновременно будут заполнены.
Про порядок писал. Это ваше предположение что было что-то специально сделано для этого.
o-o
ну не видите вы лично аналогии, проблемы ваши
Нет у меня проблем, кроме как желание помочь в несоотвествии восприятия.
15 май 14, 17:21    [16022745]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
o-o
Guest
+
да вам, видать, просто меньше доводилось слышать подобное.
все там ок с тем, что "всего одно из значений Value... будет заполнено".
это же значит, что "3/4 будут NULL-ы"
и им пофиг, что "размазано" :)
кто "слышал звон" помнит примерно такое:
"много колонок, по бОльшей части заполненных NULL-ами, надо запихнуть последними. наэкономим кучу места".

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

>>>ТС или америкосов? Вы уверены что правильно поняли ТС?

вовсе нет такой уверенности.
на мой взгляд, вопрос вообще задан странно.
не меня, а ТС надо было спрашивать, "экономия по сравнению с чем" имелась в виду.
мне в глаза бросилось не буду в 10ый раз повторять что,
по этому поводу возникло подозрение и пришло возможное объяснение.

не берусь утверждать, что и он_лжет то же самое подумал, ибо мне не совсем ясна фраза
он_лжет
o-o
хех. ту же самую книжку читали. я когда у видел у автора определение таблицы - у меня бомбануло. :)

но, возможно, не только мне в глаза бросилось :)
15 май 14, 18:15    [16023084]     Ответить | Цитировать Сообщить модератору
 Re: DATALENGTH width column : реальность vs документация  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
я бы еще про data_compression не стал забывать.
возможно enterprise у них.
15 май 14, 19:56    [16023561]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить