Column Encryption And Decryption In MSSQL

-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY
    PASSWORD ='mandep@123'
GO
  
-- Create Certificate
CREATE CERTIFICATE test
    WITH SUBJECT='mandeep'
GO
 
 
-- Create Symmetric Key
CREATE SYMMETRIC KEY TESTKEY
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE tEST
GO

CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),
UPASSWORD VARBINARY(256))

-- Create a Procedure to Insert Data in Table 

CREATE PROC InsertUSER
  @UFNAME VARCHAR(100),
  @ULNAME VARCHAR(100),
  @ULOGINDI VARCHAR(12),
  @UPASSWORD VARCHAR(20)
AS
BEGIN
-- you must open the key as it is not already
    OPEN SYMMETRIC KEY TESTKEY
        DECRYPTION BY CERTIFICATE TEST;
    
-- Insert statement
    INSERT INTO [USERS]
    (UFNAME, ULNAME, ULOGINDI, UPASSWORD)
    VALUES
    (@UFNAME, @ULNAME, @ULOGINDI,
     EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));
     
END;

Predefined SQL Database Roles In Sql Server

SQL roles make your work easier, they allow assigning permissions to a role, or group of users, instead to individual users

Predefined SQL database roles are:

db_owner - members have full access
db_accessadmin - members can manage Windows groups and SQL Server logins
db_datareader - members can read all data
db_datawriter - Members can add, delete, or modify data
db_ddladmin - members can run data definition statements
db_securityadmin - members can modify role membership and manage permissions
db_bckupoperator - members can create backups
db_denydatareader - members cannot see the database data
db_denydatawriter - members cannot change/delete database data

Reserved keywords In Sql Server

Reserved keywords

Avoid using reserved keywords for SQL Server database object names. If you do, make sure you use either quoted identifiers or delimited identifiers

Here are the lists of SQL Server, ODBC and future SQL Server reserved keywords

SQL Reserved Key words List.

ADD	EXTERNAL	PROCEDURE
ALL	FETCH	PUBLIC
ALTER	FILE	RAISERROR
AND	FILLFACTOR	READ
ANY	FOR	READTEXT
AS	FOREIGN	RECONFIGURE
ASC	FREETEXT	REFERENCES
AUTHORIZATION	FREETEXTTABLE	REPLICATION
BACKUP	FROM	RESTORE
BEGIN	FULL	RESTRICT
BETWEEN	FUNCTION	RETURN
BREAK	GOTO	REVERT
BROWSE	GRANT	REVOKE
BULK	GROUP	RIGHT
BY	HAVING	ROLLBACK
CASCADE	HOLDLOCK	ROWCOUNT
CASE	IDENTITY	ROWGUIDCOL
CHECK	IDENTITY_INSERT	RULE
CHECKPOINT	IDENTITYCOL	SAVE
CLOSE	IF	SCHEMA
CLUSTERED	IN	SECURITYAUDIT
COALESCE	INDEX	SELECT
COLLATE	INNER	SEMANTICKEYPHRASETABLE
COLUMN	INSERT	SEMANTICSIMILARITYDETAILSTABLE
COMMIT	INTERSECT	SEMANTICSIMILARITYTABLE
COMPUTE	INTO	SESSION_USER
CONSTRAINT	IS	SET
CONTAINS	JOIN	SETUSER
CONTAINSTABLE	KEY	SHUTDOWN
CONTINUE	KILL	SOME
CONVERT	LEFT	STATISTICS
CREATE	LIKE	SYSTEM_USER
CROSS	LINENO	TABLE
CURRENT	LOAD	TABLESAMPLE
CURRENT_DATE	MERGE	TEXTSIZE
CURRENT_TIME	NATIONAL	THEN
CURRENT_TIMESTAMP	NOCHECK	TO
CURRENT_USER	NONCLUSTERED	TOP
CURSOR	NOT	TRAN
DATABASE	NULL	TRANSACTION
DBCC	NULLIF	TRIGGER
DEALLOCATE	OF	TRUNCATE
DECLARE	OFF	TRY_CONVERT
DEFAULT	OFFSETS	TSEQUAL
DELETE	ON	UNION
DENY	OPEN	UNIQUE
DESC	OPENDATASOURCE	UNPIVOT
DISK	OPENQUERY	UPDATE
DISTINCT	OPENROWSET	UPDATETEXT
DISTRIBUTED	OPENXML	USE
DOUBLE	OPTION	USER
DROP	OR	VALUES
DUMP	ORDER	VARYING
ELSE	OUTER	VIEW
END	OVER	WAITFOR
ERRLVL	PERCENT	WHEN
ESCAPE	PIVOT	WHERE
EXCEPT	PLAN	WHILE
EXEC	PRECISION	WITH
EXECUTE	PRIMARY	WITHIN GROUP
EXISTS	PRINT	WRITETEXT
EXIT	PROC

How to add a timestamp to a backup name

Schedule a SQL Server job to create database backups. Instead of using a script such as:

BACKUP DATABASE [AdventureWorks2012]

TO DISK = N'E:\Test\AdventureWorks.bak' 

Use :

DECLARE @SQLStatement VARCHAR(2000)

SET @SQLStatement= 'E:\Test\AdventureWorks' +_ CONVERT(nvarchar(30), GETDATE(), 110) +'.bak'

BACKUP DATABASE [AdventureWorks2012] TO DISK = @SQLStatement

The variable and CONVERT(nvarchar(30), GETDATE(), 110) allow to add the current date. The backups created are named like below.

AdventureWorks_07-29-2013

AdventureWorks_07-30-2013

AdventureWorks_07-31-2013

View SQL Server Trace

To view a specific SQL trace, use fn_trace_getinfo and specify the ID of the trace

 SELECT *_

FROM ::fn_trace_getinfo(trace_id)

For this option, you would have to know the trace ID

Another option, which I find more user friendly is simply by opening SQL Server Profiler and selecting a specific file trace

Mail From Sqlserver Database Error solution

Minimal permissions to send Database mail

If you encounter the following error when trying to send a database mail

EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'

you have a problem with SQL Server privileges

The easies way to fix this is to make your user a member of the *DatabaseMailUserRole* database role in the msdb database. You can do that in SQL Server Management Studio, or using the following SQL

EXEC msdb.dbo.sp_addrolemember @rolename='DatabaseMailUserRole' ,@membername='<user or role name>'

Note that the DatabaseMailUserRole doesn't exist in the SQL Server Security | Server Role list, just in the msdb database roles

Oracle 12c New Features for Developers

Oracle Database 12c introduces a new multitenant architecture that makes it easy to deploy and manage database clouds. Oracle 12c is a pluggable database environment, where we can plug multiple databases into single database container. All these databases then share same background processes and memory. This helps in reducing the overhead of managing multiple databases.

I have tried to compile some of the important new features of Oracle Database 12c. Below are the top 15 new features of Oracle Database 12c for Oracle Developer & professional.

1. Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.

Example:

create sequence test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);

2. Invisible column:
Oracle Database 12c provides you the Invisible column feature. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column need to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the database into invisible columns.

Example:

SQL> create table my_table
  2  (
  3  id number,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );
  
SQL> ALTER TABLE my_table MODIFY (password visible);  

3. Multiple indexes on the same column
Before Oracle Database 12c, we could not have multiple indexes on a single column. In Oracle Database 12c a column may have multiple indexes but all should be of different types. Like a column may have B-Tree and BitMap Index both. But, only one index will be used at a given time.


4. VARCHAR2 length up to 32767
Form Oracle Database 12c, a varchar2 column can be sized upto 32767, which was earlier 4000. The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes. Increasing the allotted size for these data types allows users to store more information in character data types before switching to large objects (LOBs).


5. Top-N feature
A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set
Example:

SQL> SELECT value
  2  FROM   mytable
  3  ORDER BY value DESC
  4  FETCH FIRST 10 ROWS ONLY;

6. IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is a American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).
Example:

SQL> create table my_table
  2  (
  3  id number generated as identity,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );

7. With Clause improvement
In Oracle 12c, we can declare PL/SQL functions in the WITH Clause of a select statement and use it as an ordinary function. Using this construct results in better performance as compared with schema-level functions
Example:

SQL> WITH
  2    FUNCTION f_test(n IN NUMBER) RETURN NUMBER IS
  3    BEGIN
  4      RETURN n+1;
  5    END;
  6  SELECT f_test(1)
  7  FROM   dual
  8  ;

8. Cascade for TRUNCATE and EXCHANGE partition.
With Oracle Database 12c, The TRUNCATE can be executed with CASCADE option which will also delete the child records.


9. Online RENAME/MOVE of Datafiles
Oracle Database 12c has provided a simple way to online renamed or moved data files by simply "ALTER DATABASE MOVE DATAFILE" command. Data files can also be migrated online from ASM to NON-ASM and NON-ASM to ASM easily now.

Examples:

Rename datafile:  
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/indx_01.dbf';
Move Datafile:    
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '/u01/oradata/orcl/indx.dbf';
NON-ASM to ASM:   
  SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/indx.dbf' TO '+DISKGROUP_DATA01';

10. Move table partition to different Tablespace online
From Oracle 12c, it become very easy to move Table Partition to different tablespace and does not require complex steps
Example:

  SQL> ALTER TABLE MY_LARGE_TABLE MOVE PARTITION MY_LARGE_TABLE_PART1 TO TABLESPACE USERS_NEW;

11. Temporary Undo
Before Oracle Database 12c, undo records of temporary tables used to be stored in undo tablespace. With the temporary undo feature in Oracle Database 12c, the undo records of temporary tables can now be stored in a temporary table instead of stored in undo tablespace. The main benefits of temporary undo are 1) Low undo tablespace usages 2) less redo data generation. For using this feature Compatibility parameter must be set to 12.0.0 or higher and TEMP_UNDO_ENABLED initialization parameter must be Enabled.


12. DDL logging
By using the ENABLE_DDL_LOGGING initiation parameter in Oracle Database 12c, we can now log the DDL action into xml and log files to capture when the drop or create command was executed and by whom under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. The parameter can be set at the database or session levels.
Example:

  SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE;

13. PGA_AGGREGATE_LIMIT parameter
Oracle Database 12c has provided us a way to limit PGA by PGA_AGGREGATE_LIMIT parameter. Before Oracle Database 12c there was no option to limit and control the PGA size. Oracle will automatically abort the session that holds the most untenable PGA memory when PGA limits exceeds the defined value.


14. SQL statement in RMAN
From Oracle Database 12c, we can execute any SQL and PL/SQL commands in RMAN without SQL prefix
Example:

  RMAN> SELECT username,machine FROM v$session;

15. Turning off redo for Data Pump the import
The new TRANSFORM option, DISABLE_ARCHIVE_LOGGING, to the impdp command line causes Oracle Data Pump to disable redo logging when loading data into tables and when creating indexes. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
Example:

  impdp directory=mydir dumpfile=mydmp.dmp logfile=mydmp.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y