Difference between SQL Server 2008 R2 and SQL Server 2012 // SQL Server 2012 Vs 2008 R2

What are the Difference between SQL server 2008 R2 (10.5) and SQL Server 2012 (11)?

(OR)

Difference between SQL Server 2008 R2 and SQL Server 2012

Character	Sql Server 2012	Sql Server 2008 R2
1.       Code Name	SQL Server 2008 R2 is codenamed as Kilimanjaro	SQL Server 2012 is codenamed as Denali
2.       New Functions	CONCAT(), FORMAT() and TRY_CONVERT() ,functions are not available in SQL Server 2012	CONCAT(), FORMAT() and TRY_CONVERT() functions are not available in SQL Server 2008 R2
3.       Concurrent Connection	SQL server 2012 has unlimited concurrent connections.	SQL server 2008 R2 has 32767 concurrent connections.
4.       Working with CONVERT(  )       and FORMAT(  )  functions	SQL Server 2012 come up with new function FORMAT() same as .format() C# function for change the date/currency format etc. This example displays the date in Taiwan format. Taiwan uses traditional Chinese characters. DECLARE @date DATETIME = '12/21/2011'; SELECT FORMAT ( @date, 'MMMM dddd d', 'zh-TW' ) AS FormattedDate; In US format DECLARE @date DATETIME = convert(datetime,'2011/01/01 2:00:00'); SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mm ss tt','en-US' ) AS FormattedDate; Result FormattedDate 2011/01/01 02:00:00 AM	CONVERT() function for convert date format. for that we need to memorizing cryptic style codes like 101 and 103 for converting datetime values to localized presentation formats. CONVERT(DATETIME, '7/24/2010 12:00:00 AM', 101)
5.       Currency / Numbers	SQL Server 2012 have the solution for cast a money field, this is done by FORMAT() function Example Let's see some of the numeric formats that we can display using the FORMAT() function. Personally, I format numbers for currencies and number of decimal characters or for percentage. Here are some examples. To display the number with currency using locale, use the following example. Example 1: DECLARE @money money = '125000'; SELECT FORMAT ( @money, 'C') AS MyMoney; Result: $125,000.00	There is no direct way to cast a money/currency field in SQL Server 2008
6.       Server Down	In SQL Server 2012, server down time is reduced by 50% , hence OS patching is not rebooting n times.	In SQL Server 2008 R2 , rebooting is requisite for OS patching , hence server down time is high.
7.       New  Objects	SQL Server 2012 has “SEQUENCE” as new Object To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.	SQL Server 2008 R2 has  no “SEQUENCE” Object
8.       IIF( ) Function	SQL Server 2012 has “IIF” as new Function	SQL Server 2008 R2 has no “IIF” Function

Typical Use

---

To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName

    AS int

    INCREMENT BY 1 ;

To create an integer sequence number similar to an identity column that increments by 1 from 1 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName

    AS int

    START WITH 1

    INCREMENT BY 1 ;

Syntax:

CREATE SEQUENCE [schema_name . ] sequence_name

 [ AS [built_in_integer_type | user-defined_integer_type ] ]

 [ START WITH  ]

 [ INCREMENT BY  ]

 [ { MINVALUE [  ] } | { NO MINVALUE } ]

 [ { MAXVALUE [  ] } | { NO MAXVALUE } ]

 [ CYCLE | { NO CYCLE } ]

 [ { CACHE [  ] } | { NO CACHE } ]

 [ ; ]

Where:
Start with:             the initial value to start with sequence.
Increment by:       the step by which the values will get incremented or decremented.
Minvalue:              the minimum value of the sequence.
Maxvalue:             the maximum value of the sequence.
Cycle / No Cycle:  to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
Cache / No Cache:  to pre-allocate the number of sequences specified by the given value.

Example: 

USE TempDB
GO
-- Create sequence
CREATE SEQUENCE dbo.SequenceID AS BIGINT
START WITH 3
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
NO CACHE;
GO
-- Following will return 3
SELECT next value FOR dbo.SequenceID;
-- Following will return 4
SELECT next value FOR dbo.SequenceID;
-- Following will return 5
SELECT next value FOR dbo.SequenceID;
-- Following will return which number
SELECT next value FOR dbo.SequenceID;
-- Clean up
DROP SEQUENCE dbo.SequenceID;
GO

SQL Server 2008 R2 (SQL Server 10.5) :

1.SQL Server 2008 R2 is codenamed as Kilimanjaro
***2.In SQL Server 2008 R2 , rebooting is requisite for OS patching , hence server down time is high
3.SQL Server 2008 R2 does not have this feature of availability groups, hence fast recovery is not possible.
4.The SQL Server 2012 uses 48 bit precision for spatial calculations
5.CONCAT(), FORMAT() and TRY_CONVERT() functions are not available in SQL Server 2008
6.SQL Server 2008 R2 is slow compared to SQL Server 2012.
7.However buffer rate is less because there is no data redundancy in SQL Server 2008 R2
8.Data visualization is not supported in SQL Server 2008 R2
9.Spatial features are not supported more in SQL Server 2008 R2. Instead a traditional way for geographical elements have been set in SQL Server 2008 R2.
10.The Maximum number concurrent connections to SQL Server 2008 is 32767.
11. SQL Server have old CONVERT() function for convert date format. for that we need to memorizing cryptic style codes like 101 and 103 for converting datetime values to localized presentation formats
CONVERT(DATETIME, '7/24/2010 12:00:00 AM', 101)
12. There is no direct way to cast a money/currency field in SQL Server 2008

SQL Server 2012 (SQL Server 11) :

1.SQL Server 2012 is codenamed as Denali
2.In SQL Server 2012, server down time is reduced by 50% , hence OS patching is not rebooting n times.
3.In SQL Server 2012, high availability and disaster recovery factor has been introduced which duplicates the data and rapidly recovers the loss.
4.The SQL Server 2012 uses 48 bit precision for spatial calculations
5.CONCAT(), FORMAT() and TRY_CONVERT() functions are newly included in SQL Server 2012
6.In SQL Server 2012, the performance is 10 times faster than the predecessor.
7.Buffer rate is high in SQL Server 2012 because of data compression.
8.Data visualization tool is available in SQL Server 2012.This allows snapshots of data.
9.Support for persistent computed columns and extra geographical approach is possible with spatial features in SQL Server 2012.
10.SQL server 2012 has unlimited concurrent connections.

11. SQL Server 2012 come up with new function FORMAT() same as .format() C# function for change the date/currency format etc.
This example displays the date in Taiwan format. Taiwan uses traditional Chinese characters.
DECLARE @date DATETIME = '12/21/2011';
SELECT FORMAT ( @date, 'MMMM dddd d', 'zh-TW' ) AS FormattedDate;

In US format
DECLARE @date DATETIME = convert(datetime,'2011/01/01 2:00:00');
SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mms tt','en-US' ) AS FormattedDate;
Result
FormattedDate
2011/01/01 02:00:00 AM

12. SQL Server 2012 have the solution for cast a money field, this is done by FORMAT() function
Example
Let's see some of the numeric formats that we can display using the FORMAT() function. Personally, I format numbers for currencies and number of decimal characters or for percentage. Here are some examples.

To display the number with currency using locale, use the following example.
Example 1:

DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C') AS MyMoney;
Result:
$125,000.00

Here, I am getting the currency symbol ‘$’ because my current locale language setting is en-us. I could also display the currency ‘$’ explicitly by using the culture parameter as shown below.
DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C', 'en-US' ) AS MyMoney;
for more example http://www.databasejournal.com/features/...-2012.html

References http://onlydifferencefaqs.blogspot.in/20...8-and.html