SQL Server Data Compression Page vs Row Article 02

SQL Server Data Compression Page vs Row Article 02

                                                    First post I have discuss about DATA_COMPRESSION behavior. Today I have noticed some interesting information about  DATA_COMPRESSION. Normally we already know Unicode data types (nvarchar,nchar) allocate extra space for store data in SQL Server. But with DATA_COMPRESSION is enabled then this extra space NOT allocate. But this only for Page Compression. For example varchar column space allocation and nvarchar column space allocation both same when we specified  “DATA_COMPRESSION=ON”.

For English

use databaseusergroup
GO
--varchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblvarchar];
END
GO
create table tblvarchar(id int IDENTITY(1,1),col1 varchar(100));
GO
INSERT INTO tblvarchar(col1) SELECT 'databas user group';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'varchar - ROW'
GO
sp_spaceused 'tblvarchar';
GO
DROP INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [dbo].[tblvarchar] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'varchar - PAGE'
GO
sp_spaceused 'tblvarchar';
GO
--nvarchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblnvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblnvarchar];
END
GO
create table tblnvarchar(id int IDENTITY(1,1),col1 nvarchar(100));
GO
INSERT INTO tblnvarchar(col1) SELECT 'databas user group';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'Nvarchar - ROW'
GO
sp_spaceused 'tblnvarchar';
GO
DROP INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [dbo].[tblnvarchar];
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'Nvarchar - PAGE'
GO
sp_spaceused 'tblnvarchar';
GO

For Sinhala

use databaseusergroup
GO
--varchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblvarchar];
END
GO
create table tblvarchar(id int IDENTITY(1,1),col1 varchar(100));
GO
INSERT INTO tblvarchar(col1) SELECT N'දත්ත සමුදා පරිශීලක කණ්ඩායම';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'varchar - ROW'
GO
sp_spaceused 'tblvarchar';
GO
DROP INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [dbo].[tblvarchar] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'varchar - PAGE'
GO
sp_spaceused 'tblvarchar';
GO
--nvarchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblnvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblnvarchar];
END
GO
create table tblnvarchar(id int IDENTITY(1,1),col1 nvarchar(100));
GO
INSERT INTO tblnvarchar(col1) SELECT N'දත්ත සමුදා පරිශීලක කණ්ඩායම';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'Nvarchar - ROW'
GO
sp_spaceused 'tblnvarchar';
GO
DROP INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [dbo].[tblnvarchar];
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'Nvarchar - PAGE'
GO
sp_spaceused 'tblnvarchar';
GO

with above example will give you clear idea about this.
Cheers...
and Most welcome your comments and ideas...