1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
|
CREATE TABLE T_TEST_SPARSE_TSP
(ID INT IDENTITY CONSTRAINT PKS PRIMARY KEY,
NOM CHAR(255) SPARSE NULL,
PRENOM VARCHAR(100) SPARSE NULL);
GO
CREATE TABLE T_TEST_UNSPARSE_TSP
(ID INT IDENTITY CONSTRAINT PK PRIMARY KEY,
NOM CHAR(255) NULL,
PRENOM VARCHAR(100) NULL);
GO
INSERT INTO T_TEST_UNSPARSE_TSP VALUES ('DUPONT', 'Aymeric-Alexandre')
GO 10000
INSERT INTO T_TEST_SPARSE_TSP
SELECT NOM, PRENOM
FROM T_TEST_UNSPARSE_TSP
GO
UPDATE T_TEST_SPARSE_TSP SET NOM = NULL WHERE ABS(CHECKSUM(NEWID())) % 10 = 0
UPDATE T_TEST_SPARSE_TSP SET PRENOM = NULL WHERE ABS(CHECKSUM(NEWID())) % 10 = 0
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 5 AS N
FROM T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET NOM = NULL
WHERE ID IN (SELECT ID FROM T WHERE N = 1);
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 5 AS N
FROM T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET PRENOM = NULL
WHERE ID IN (SELECT ID FROM T WHERE N = 1);
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
ALTER TABLE T_TEST_SPARSE_TSP REBUILD;
ALTER TABLE T_TEST_UNSPARSE_TSP REBUILD;
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 2 AS N
FROM T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET NOM = NULL
WHERE ID IN (SELECT ID FROM T WHERE N = 1);
WITH T AS
(
SELECT ID, ABS(CHECKSUM(NEWID())) % 2 AS N
FROM T_TEST_SPARSE_TSP
)
UPDATE T_TEST_SPARSE_TSP
SET PRENOM = NULL
WHERE ID IN (SELECT ID FROM T WHERE N = 1);
ALTER TABLE T_TEST_SPARSE_TSP REBUILD;
ALTER TABLE T_TEST_UNSPARSE_TSP REBUILD;
-- plus de 50% de vide :
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
/*
name rows reserved data index_size unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_UNSPARSE_TSP 10000 2952 KB 2864 KB 16 KB 72 KB
name rows reserved data index_size unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_SPARSE_TSP 10000 1416 KB 1320 KB 16 KB 80 KB
*/
CREATE INDEX XE ON T_TEST_SPARSE_TSP (NOM, PRENOM)
CREATE INDEX X ON T_TEST_UNSPARSE_TSP (NOM, PRENOM)
EXEC sp_spaceused 'T_TEST_UNSPARSE_TSP'
EXEC sp_spaceused 'T_TEST_SPARSE_TSP'
/*
name rows reserved data index_size unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_UNSPARSE_TSP 10000 5968 KB 2864 KB 3008 KB 96 KB
name rows reserved data index_size unused
------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
T_TEST_SPARSE_TSP 10000 4304 KB 1320 KB 2888 KB 96 KB |
Partager