Differences between SQL Server 2008 and 2012

Hi, Here are some Differences between SQL Server 2008/R2 and 2012.

Sr. No.	SQL Server 2008	SQL Server 2012
1	Exceptions handle using TRY….CATCH	Unique Exceptions handling with THROW
2	High Availability features as Log Shipping, Replication, Mirroring & Clustering	New Feature ALWAYS ON introduced with addition of 2008 features.
3	Web Development and Business Intelligence Enhanced with business intelligence features. Excel PowerPivot by adding more drill and KPI through.	In Addition with SQL server 2008, Web Development and Business Intelligence Enhanced with business intelligence features and Excel PowerPivot by adding more drill and KPI’s.
4	Could not supported for Windows Server Core Support.	Supported for Windows Server Core Support
5	Columnstore Indexes not supported.	New Columnstore Indexes feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.
6	PowerShell Supported	Enhanced PowerShell Supported
7	Distributed replay features not available.	Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.
8	PowerView not available in BI features	PowerView is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.
9	EXECUTE … with RECOMPLIE feature	Enhanced EXECUTE with many option like WITH RESULT SET….
10	Maximum numbers of concurrent connections to SQL Server 2008 is 32767	SQL server 2012 has unlimited concurrent connections.
11	The SQL Server 2008 uses 27 bit bit precision for spatial calculations.	The SQL Server 2012 uses 48 bit precision for spatial calculations
12	TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008	TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
13	ORDER BY Clause does not have OFFSET / FETCH options for paging	ORDER BY Clause have OFFSET / FETCH options for paging
14	SQL Server 2008 is code named as Katmai.	SQL Server 2012 is code named as Denali
15	In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition.	In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server.
16	Sequence is not available in SQL Server 2008	Sequence is included in SQL Server 2012.Sequence is a user defined object that generates a sequence of a number
17	The Full Text Search in SQL Server 2008 does not allow us to search and index data stored in extended properties or metadata.	The Full Text Search in SQL Server 2012 has been enhanced by allowing us to search and index data stored in extended properties or metadata. Consider a PDF document that has “properties” filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it’s possible now.
18	Analysis Services in SQL Server does not have BI Semantic Model	Analysis Services will include a new BI Semantic Model (BISM). BISM is a (BISM) concept. 3-layer model that includes: Data Model, Business Logic and Data Access
19	The BCP not support –K Option.	The BCP Utility and SQLCMD Utility utilities enhanced with -K option, which allows to specify read-only access to a secondary replica in an AlwaysOn availability group.
20	sys.dm_exec_query_stats	sys.dm_exec_query_stats  added four columns to help troubleshoot long running queries. You can use the total_rows, min_rows, max_rows and last_rows aggregate row count columns to separate queries that are returning a large number of rows from problematic queries that may be missing an index or have a bad query plan.

Other more precious features will be added with this document very soon.
J

Tips for Advanced MS SQL Server Developers

Hi Guys, Here, some basic tips about How to write optimized Stored Procedure.

• Its always better to use table owner in query like

  SELECT col1,col2….coln FROM dbo.Tablename

• Due to this, SQL query optimizer does not have to decide whether to retrieve from dbo.Tablename or other owner’s table and avoids recompilation.
• Always Use TRUNCATE TABLE statement instead of DELETE clause if its needed to delete all rows from a table. Its more faster then DELETE statement without any conditions. TRUNCATE TABLE always releases all the space occupied by that table’s data and indexes, without logging the individual row deletes.
• Don’t use the “sp_” prefix in a stored procedure name as the “sp_” prefix is reserved for system stored procedures. Any stored procedure that has the “sp_” prefix will cause an extra lookup in the MASTER database If a stored procedure uses same name in both the user database and a system database, the stored procedure in the user database will execute but first it will find the procedure in resource database and then the user database (for SQL server 2005/2008/2012) hence causing an extra burden to the server.
• To get number of rows from table never use “SELECT COUNT(*)” statement, There is other way for much faster to achieve this goal as

  SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘Table_Name’) AND indid <2

• Include “SET NOCOUNT ON” statement in your stored procedures to greatly reduce network traffic.
• Try to use table variables instead of Temporary Tables – Temp tables can cause stored procedures to recompile. (From SQL 2005 onwards, using temporary table not always causing recompilations. But adding rows to temporary tables may cause recompilations). But table variables were designed specifically to guard against stored procedure recompiles during execution. If the result set is not containing a huge number of records then you should stick to table variable, otherwise temp table has its advantages. There is a misconception that temp tables always use the tembdb database but table variable do not. Table variables also use tempdb after a certain size be careful to use them only with not very huge amount of data that you want to allocate in temp tables, otherwise you can easily get the server down.