Fa59UKsi

1EXEC master.dbo.sp_configure 'show advanced options', 1
2RECONFIGURE
3EXEC master.dbo.sp_configure 'xp_cmdshell', 1
4RECONFIGURE 
5
6DROP TABLE IF EXISTS dbo.LobRowstoreTest;
7
8CREATE TABLE dbo.LobTest(
9	c1 int identity(1,1),
10	c2 varchar(max),
11	index CCI_LobTest clustered columnstore );
12
13
14
15-- Bulk Load the data
16INSERT INTO LobTest(c2)  
17SELECT * FROM OPENROWSET(  
18   BULK 'C:\Install\urban_consumers.txt',  
19   SINGLE_BLOB) AS x;   
20
21
22exec sp_spaceused 'dbo.LobTest'
23
24exec dbo.cstore_GetRowGroupsDetails;
25
26select object_name(object_id), *
27	from sys.column_store_row_groups;
28
29select object_name(rg.object_id), 
30	   rg.size_in_bytes as RowGroupSpaceInBytes,
31	   cast(1.0*pstat.reserved_page_count*8/1024 as Decimal(9,2)) as ReservedMB
32	from sys.column_store_row_groups rg
33		inner join sys.partitions part
34			on rg.object_id = part.object_id
35		inner join sys.dm_db_partition_stats AS pstat
36			on pstat.partition_id = part.partition_id;
37
38exec dbo.cstore_GetDictionaries;
39
40select object_name(part.object_id) as TableName, on_disk_size as SizeInBytes
41	from sys.column_store_dictionaries dict
42	inner join sys.partitions part
43			on dict.partition_id = part.partition_id and dict.hobt_id = part.hobt_id;
44
45drop table if exists dbo.ManyLobs;
46
47create table dbo.ManyLobs
48(
49    id int not null,
50    LobData varbinary(max) null,
51	Index CCI_ManyLobs CLUSTERED COLUMNSTORE
52);
53
54;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0)
55,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2)  
56,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2)  
57,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2)  
58,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2) 
59,N6(C) AS (SELECT 0 FROM N5 AS T1 CROSS JOIN N3 AS T2) 
60,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N6)
61insert into dbo.ManyLobs(id, LobData)
62    select   
63        ID
64        ,convert( varbinary(max), replicate(convert(varchar(max),'a'),9000) )
65    from Ids;
66
67exec sp_spaceused 'dbo.ManyLobs';
68exec dbo.cstore_GetRowGroupsDetails @tableName = 'ManyLobs';
69
70drop table if exists dbo.ManyLobs;
71
72create table dbo.ManyLobs
73(
74    id int not null PRIMARY KEY,
75    LobData varchar(max) null,
76	Index CCI_ManyLobs CLUSTERED COLUMNSTORE
77);
78
79
80create nonclustered index IX_ManyLobs_Lob on dbo.ManyLobs (id) include(lobdata);