Always Encrypted VS Transparent Data Encryption (TDE)

What are the differences between Transparent Data Encryption (TDE) and Always Encrypted?

[TDE VS Always Encrypted]  

What are the differences between Always Encrypted and Transparent Data Encryption (TDE)?

[Always Encrypted VS TDE] 

  

	Transparent Data Encryption (TDE)	Always Encrypted
1	The normal Column encryption which was introduced from SQL Server 2005 uses function EncryptByCert to encrypt the columns.	Always Encrypted is a new feature in SQL Server 2016.
2	TDE is database-level encryption.	Encryption happening during transport. With always encrypted the encryption is done at clients app by API, like ADO.net, JDBC, ODBC. Drivers are installed at clients end to do this encryption. Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data into the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
3	No driver is needed since it is database level. It works for all applications.	Driver specific (we see examples that support for .NET and Java). And not sure about when the application needs to change/upgrade the driver when the data encryption will work or not for existing data.
4	It is accessible to the database itself and to anyone who can own it and obtain the key/certificate/password.	Always Encrypted allows clients to encrypt sensitive data inside client applications, and never reveal the encryption keys to the Database Engine. However the certificate should be installed in Client machine for encrypting the data, in this case DBA/User can see the encrypted data and  by setting "Column Encryption Setting = Enabled" in the SQL Server Management Studio connection string from Client Machine.
5	Data is submitted as clear text in SQL Server and this data can be seen from traces. We can't make it as fully secured since DBA can access the data by providing the key/certificate.	Data can be protected and fully secured when the application is consuming the data through only Web services, hence the encryption is installed in only Web server for encrypting the data, However DBA who has admin access to SQL Server, can see the data by setting  "Column Encryption Setting = Enabled"  in the SQL Server Management Studio connection string from Client Machine as the Certificate is installed in the database server. If DBA does not have access to the web server.
6	Encrypted data must be stored in Varbinary data type.  It is very easy to implement in existing database by adding an additional column with data type varbinary for storing the encrypt data.	It can be any data type except IDENTITY, ROWGUIDCOL, FILESTREAM (text/ntext/image/XML/), hierarchyid, geography, geometry alias types/user-defined data types SQL_VARIANT rowversion (timestamp)) columns. It might be easy for new databases but  implementing the existing database we need to have a detailed study on the following limitations and it may have difficulties on implementing it in existing databases.
7	Displaying/Filtering the decrypted data can be achieved by creating View or Stored procedure.	Displaying/Filtering the decrypted data can be achieved by only when client application and if certificate is installed.
8	SQL greater/less than, pattern matching using the LIKE  operators can be used for filtering the encrypted data.	SQL greater/less than, pattern matching using the LIKE operators can't be used for filtering the encrypted data
9	It is proven since 10 years that there are no much limitations for this approach.	Since it is a new feature, Microsoft has mentioned lot of limitations as following.
		Always Encrypted is not supported for the columns with the below characteristics (e.g. the Encrypted WITH clause cannot be used in CREATE TABLE/ALTER TABLE for a column, if any of the following conditions apply to the column):
		·      Columns using one of the following data types: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types. FILESTREAM columns ·       Columns with the IDENTITY property ·       Columns with ROWGUIDCOL property String (varchar, char, etc.) columns with non-bin2 collations (Randomized Encryption) ·       Columns that are keys for nonclustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine) ·       Columns that are keys for clustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine) ·       Columns that are keys for fulltext indices containing encrypted columns both randomized and deterministic ·       Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted) ·       Sparse column set ·       Columns that are referenced by statistics ·       Columns using alias type ·       Partitioning columns ·       Columns with default constraints ·       Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported) ·       Primary key columns when using randomized encryption (deterministic encryption is supported) ·       Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms ·       Columns referenced by check constraints ·       Columns in tables that use change data capture ·       Primary key columns on tables that have change tracking ·       Columns that are masked (using Dynamic Data Masking) ·       Columns in Stretch Database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.) ·       Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported). ·       Table-valued parameters targeting encrypted columns are not supported.
		The following clauses cannot be used for encrypted columns: ·       FOR XML ·       FOR JSON PATH
		The following features do not work on encrypted columns: ·       Transactional or merge replication ·       Distributed queries (linked servers)
		Tool Requirements : ·       SQL Server Management Studio can decrypt the results retrieved from encrypted columns if you connect with the column encryption setting=enabled in the Additional Properties tab of the Connect to Server dialog. Requires at least SQL Server Management Studio version 17 to insert, update, or filter encrypted columns. ·       Encrypted connections from sqlcmd require at least version 13.1, which is available from the Download Center.
		Key management: (creating/changing/reviewing key metadata in the database) is not possible.
		IMPORTATNT NOTES: ·       The permissions apply to actions using Transact-SQL, Management Studio (dialog boxes and wizard), or PowerShell. ·       The two VIEW permissions are required when selecting encrypted columns, even if the user does not have permission to decrypt the columns. ·       In SQL Server, both VIEW permissions are granted by default to the public fixed database role. A database administrator may choose to revoke (or deny) the VIEW permissions to the public role and grant them to specific roles or users to implement more restricted control. ·       In SQL Database, the VIEW permissions are not granted by default to the public fixed database role. This enables certain existing, legacy tools (using older versions of DacFx) to work properly. Consequently, to work with encrypted columns (even if not decrypting them) a database administrator must explicitly grant the two VIEW permissions. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF

What are the differences between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF [SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF]?

	SET QUOTED_IDENTIFIER ON	SET QUOTED_IDENTIFIER OFF
Use	It is treated as IDENTIFIER Allows characters enclosed with double quotes[“”] for Object names	It is treated as Literal OR Value  Returns the string value enclosed with double quotes[“”] as value
	SET QUOTED_IDENTIFIER ON CREATE TABLE "Child"(RecordId int IDENTITY(100,1)) GO RESULT: Command(s) completed successfully.	SET QUOTED_IDENTIFIER OFF CREATE TABLE "Child"(RecordId int IDENTITY(100,1)) GO RESULT: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'Child'.
	CREATE TABLE Child(RecordId int IDENTITY(100,1)) GO INSERT Child DEFAULT VALUES GO SET QUOTED_IDENTIFIER ON SELECT "RecordId" FROM Child RESULT: RecordId 100	CREATE TABLE Child(RecordId int IDENTITY(100,1)) GO INSERT Child DEFAULT VALUES GO SET QUOTED_IDENTIFIER OFF SELECT "RecordId" FROM Child RESULT: RecordId
	SET QUOTED_IDENTIFIER ON SELECT "RecordId" RESULT: Msg 207, Level 16, State 1, Line 2 Invalid column name 'RecordId'.	SET QUOTED_IDENTIFIER OFF SELECT "RecordId" RESULT: RecordId
	Best practice is to use the SET QUOTED_IDENTIFIER ON setting.	Avoid using SET QUOTED_IDENTIFIER OFF setting. As more and more new features require this setting value to be ON. For example while working with Filtered Indexes SET QUOTED_IDENTIFIER setting should be ON otherwise we will get into an exception.
Objects which are created with SET QUTOED IDENTIFIER ON/OFF	SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 1	SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 0

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF

@@IDENTITY vs SCOPE_IDENTITY()

What are the differences among/ between @@IDENTITY and SCOPE_IDENTITY() and 

IDENT_CURRENT(‘<<tablename>>’) [@@IDENTITY vs SCOPE_IDENTITY()]?

@@IDENTITY	SCOPE_IDENTITY()	IDENT_CURRENT(‘table’)
Returns the last identity value generated for any table in the current session, across all scopes (i.e. current session and global scope).	Returns the last identity value generated for any table in the current session and the current scope (i.e. current session and local scope).	Returns the last identity value generated for a specific table in any session and any scope (i.e. any session and global scope).
CREATE TABLE Parent(RecordId int IDENTITY(1,1)) GO CREATE TABLE Child(RecordId int IDENTITY(100,1)) GO /* -- Trigger definition CREATE TRIGGER TR_Parent_insert ON Parent FOR INSERT AS BEGIN    INSERT Child DEFAULT VALUES END GO -- End of trigger definition */ -- Child Procedure definition CREATE PROCEDURE USP_Child_insert AS BEGIN    INSERT Child DEFAULT VALUES END GO -- End Child Procedure definition -- Parent Procedure definition CREATE PROCEDURE USP_Parent_insert AS BEGIN    INSERT Parent DEFAULT VALUES;    EXEC USP_Child_insert -- (This SP will excute in other scope)    SELECT @@IDENTITY;    SELECT SCOPE_IDENTITY();    SELECT IDENT_CURRENT('Parent');    SELECT IDENT_CURRENT('Child');    END GO -- End Parent Procedure definition -- Execute the Procedure EXEC USP_Parent_insert GO EXAMPLE: 1 RESULT: SELECT @@IDENTITY; -- Returns 100 i.e. Child.RecordId from Child table. SELECT SCOPE_IDENTITY(); -- Returns 1 i.e. Parent.RecordId from Parent table. SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table. EXAMPLE: 2 Execute the following code in same session; you get the results as follows. SELECT @@IDENTITY; -- Returns 100 i.e. Child.RecordId from Child table. SELECT SCOPE_IDENTITY(); -- Returns NULL i.e. Parent.RecordId from Parent table. SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table. EXAMPLE: 3 Execute the following code in another session; you get the results as follows. SELECT @@IDENTITY; -- Returns NULL i.e. Child.RecordId from Child table. SELECT SCOPE_IDENTITY(); -- Returns NULL i.e. Parent.RecordId from Parent table. SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table.

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF