Different between DateTime and DateTime2 Datatype

    If you rely want to store more fraction part of second then you should select datetime2. Here the major flexibility is you can define your own fraction part of second till 7 digit. Here the datetime and datetime2 is not different , just datetime2 is the extension of existing datetime.

Point	DateTime	DateTime2
Syntax	Datetime	Datetime (Fractional second precision)
Example	DECLARE @MyDatetime datetime CREATE TABLE Table1 ( Column1 datetime )	DECLARE @MyDatetime2 datetime2(7) CREATE TABLE Table1 ( Column1 datetime2(7) )
Description	Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.	Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.
Date Range	January 1, 1753, through December 31, 9999	0001-01-01 through 9999-12-31 January 1,1 AD through December 31, 9999 AD
Time Range	00:00:00 through 23:59:59.997	00:00:00 through 23:59:59.9999999
Precision, scale	None	0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.
Storage size	8 byte	6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.
Accuracy	Rounded to increments of .000, .003, or .007 seconds	100 nanoseconds
Default value	1900-01-01 00:00:00	1900-01-01 00:00:00

declare @dob datetime

set @dob=getdate()

select @dob

set @dob='2014-02-23 08:54:59.697'

select @dob

------------------

declare @dob2 datetime2(7)

set @dob2='2014-02-23 08:54:59.6971234'

select @dob2

declare @dob3 datetime2(7)

set @dob3=getdate()

select @dob3

Isolation level

Transactional Integrity

ACID Properties

1. Atomic: A transaction must be all or nothing, no partial transaction. Each transaction is a complete unit of work. The entire transaction is either successes or fails no partial transaction.

2. Consistent:  Each transaction preserves database consistency. The database goes from one consistent state to another consistent state.

3. Isolation: Each transaction is separate, means the worked done by transaction A should not affect transaction B.

4.  Durable: Each transaction should be permanent, Means once transaction commit, is always be there.

Transactional Trouble:

1.       Dirty Read

2.       Non Repeatable read

      3.  Phantom rows

Dirty Read : In case of dirty read you will see the modified data by session one even session1 not committed the transaction.

Non Repeatable read: In this Case you can see the different data in same transaction. 

Phantom row : In this case the row inserted by session1 or updated by session one will get visible in session2 even that session started before inserting this row and not yet committed