Difference between DateTime and DateTime2 DataType

DATETIME2 is the new data type introduced in SQL Server 2008

DATETIME2 provides more seconds precision & has larger date range, a larger default fractional precision. DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns

Advantages of DATETIME2

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999

Larger range of values

Better Accuracy

Smaller storage space (if optional user-specified precision is specified)

Precision, scale

                                                                                                        0 to 7 digits, with an accuracy of 100nanoseconds.

                                                                                                        The default precision is 7 digits.

Storage Size

                                                                                                        6 bytes for precision less than 3

                                                                                                        7 bytes for precision 3 and 4.

All other precision require 8 bytes.

DATETIME2 with fractional seconds precision of 3 is same as DATETIME data type. And DATETIME2 (precision of 3) uses 7 bytes of storage instead of 8 byte

If you only need the date without time part use DATE

 If you only need the time without date part use TIME

 Using DATE & TIME data types instead of DATETIME2 in above situations you save the space

PDW Database Backup

The backup is stored in the G:\Backups directory on the appliance 

There are two type of backup 

Full Backup : A full database backup is a backup of an entire  PDW database similar to SQL Server Backup.

Differential Backup :A differential database backup only includes changes made since the last full PDW Database backup 

Syntax :  FULL BACKUP 

use master

backup database AdventureWorksPDW2012 to disk = '\\10.10.10.10\PDWBackups\FULL\05052015\AdventureWorksPDW2012.bak' 

The above statement creates the full backup of of the AdventureWorksPDW2012 database . The backup will be stored in \\10.10.10.10\PDWBackups\FULL\05052015\ location. To grant access on this shared location please use sp_pdw_add_network_credentials  command 

Syntax :  DIFFERENTIAL BACKUP 

backup database AdventureWorksPDW2012 to disk = '\\10.10.10.10\PDWBackups\DIFFERENTIAL\05052015\AdventureWorksPDW2012.bak with differential' 

 

You can not perform differential backup on master database

You can copy a database to a different appliance by copying the backup files to the G:\Backups directory on the Backup node of the target appliance and then performing a restore. However, copying backup files to a different location can negatively impact restore performance. This can occur when the logical disk storage for the backup files varies from the original backup directory.
The target appliance for restoring the backup must have at least as many Compute nodes as the source appliance. The target can have more Compute nodes than the source appliance, but cannot have fewer Compute nodes.

How to check the database size in PDW

Using below DBCC command via NEXUS query editor or SQL Server DATATOOL or Visual Studio

USE DATABASE_NAME

dbcc pdw_showspaceused

You can view the size in Mega Bytes  

SQL Server Latest service packs

Product Version	Latest Service Pack
SQL Server 2014	n/a
SQL Server 2012	SP2
SQL Server 2008 R2	SP2
SQL Server 2008	SP3
SQL Server 2005	SP4
SQL Server 2000	SP4