Difference between Cell- level Encryption and Transparent Data Encryption

Difference between Cell- level Encryption and Transparent Data Encryption (TDE)

Cell- level Encryption	Transparent Data Encryption (TDE)
Granular control over which data is encrypted	Encrypts the entire database
User‐aware encryption can control access on a need‐to‐know basis	Encryption is not user‐aware; data is open to all users who have permission to access the database
Requires analysis to find sensitive data	No analysis required because entire database is encrypted
Affect table structure because encrypted data can only be stored in varbinary data type columns.	No change is required in table structure.
Database applications need to be modified to use specific functions to encrypt and decrypt data.	No database application change needed.
Indexes, primary keys, and foreign keys cannot be encrypted.	No impact on indexing, primary keys, or foreign keys
Potential impact on performance because indexes on encrypted columns are not used while searching a value.	Small impact on performance (up to 5%)

SQL Table Partition.

IF EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TestPartition')

DROP DATABASE TestPartition;

GO

CREATE DATABASE TestPartition

ON PRIMARY

(NAME='TestPartition_Part1', FILENAME= 'D:\Partition test\primary\TestPartitionPrimary.mdf', SIZE=2, MAXSIZE=100, FILEGROWTH=1 ),

FILEGROUP TestPartition_Part2

(NAME = 'TestPartition_Part2', FILENAME = 'D:\Partition test\Secondary\TestPartitionSecondary.ndf', SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );

GO



use TestPartition

go

--DROP TABLE TestTable

GO

DROP PARTITION SCHEME TestPartition_PartitionScheme

GO



DROP PARTITION FUNCTION TestPartition_PartitionRange

GO



CREATE PARTITION FUNCTION TestPartition_PartitionRange (INT)

AS RANGE LEFT FOR

VALUES (10);

GO

CREATE PARTITION SCHEME TestPartition_PartitionScheme

AS PARTITION TestPartition_PartitionRange

TO ([PRIMARY], TestPartition_Part2);

GO







-- drop table TestTable

CREATE TABLE TestTable

(ID INT NOT NULL,

Date DATETIME)

ON TestPartition_PartitionScheme (ID);

GO





CREATE UNIQUE CLUSTERED INDEX IX_TestTable

ON TestTable(ID)

ON TestPartition_PartitionScheme (ID);

GO







--- Step 6 : Insert Data in Partitioned Table

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1

VALUES (1,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (11,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (123,GETDATE());

INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

VALUES (-1,GETDATE());

GO





SELECT *

FROM TestTable;

GO

-- drop table [staging_TestTable_20120704-162405]

SELECT *

FROM [staging_TestTable_20120704-162405];

GO



SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='TestTable';

GO





select GETUTCDATE ()

-- drop table [TestTableArchive]

CREATE TABLE [TestTableArchive]

(ID INT NOT NULL,

Date DATETIME)

ON TestPartition_PartitionScheme (ID);

GO



CREATE UNIQUE CLUSTERED INDEX IX_TestTable

ON [TestTableArchive](ID)

ON TestPartition_PartitionScheme (ID);

GO







ALTER TABLE [TestTable]

SWITCH PARTITION 1

TO [TestTableArchive] PARTITION 1;

select * from [TestTableArchive]

select * from [TestTable]