SQL Server 2008 discontinued feature

• backup database with 
• replace dump with backup
• replace load with restore
• backup log with no_log | truncate only not supported, switch to simple recovery mode

SQL Server 2012 discontinued features

https://msdn.microsoft.com/library/ms144262(v=sql.110)

• backup with media password
• replace VIA protocol with TCP
• replace sp_dboption with alter database
• replace SQL mail with Database mail
• replace *= and =* with ansi join sytax

Discontinued features of MS SQL Server

https://msdn.microsoft.com/en-us/library/ms144262.aspx

SQL Server 2014 new features

http://windowsitpro.com/sql-server-2014/top-ten-new-features-sql-server-2014

• In-Memory OLTP Engine. ...

• AlwaysOn Enhancements. ...

• Buffer Pool Extension. ...

• Updateable Columnstore Indexes. ...

• Storage I/O control. ...

• Power View for Multidimensional Models. ...

• Power BI for Office 365 Integration. ...

• SQL Server Data Tools for Business Intelligence.
• Backup Encryption
• SQL Server Managed Backup to Windows Azure

SQL Server 2000 new features

http://sqlmag.com/t-sql/sql-server-2000s-coolest-features

7. Integrated XML Support

XML support is key to Microsoft's push to Web-enable SQL Server 2000. The new release supports T-SQL extensions that let SQL Server produce a result set as an XML document. In addition, you can use XML documents to directly insert and update SQL Server tables.

6. INSTEAD OF Triggers

SQL Server 2000's new INSTEAD OF triggers aren't exactly like BEFORE triggers, but you can use them in a similar way. Unlike AFTER triggers, which the system executes after a transaction changes the database, INSTEAD OF triggers execute instead of the triggering action.

5. Table Data Type

The new table data type lets an application store temporary results as a table that you can manipulate by using a select statement or even action queries—just as you can manipulate any standard user table.

4. User-Defined Functions

User-defined functions enhance T-SQL's programmability by letting you create and use your own functions in T-SQL stored procedures and batches. SQL Server 2000 executes user-defined functions in the same way that it executes T-SQL's built-in functions, and user-defined functions can return either scalar variables or table data types.

3. Indexed Views

Unlike standard views, in which SQL Server resolves the data-access path dynamically at execution time, the new indexed views feature lets you store views in the database just as you store tables. Indexed views, which are persistent, can significantly improve application performance by eliminating the work that the query processor must perform to resolve the views.

2. Federated Database Support

Federated database support, which lets you use SQL Server 2000's distributed partitioned views to horizontally partition tables across multiple servers, enabled SQL Server 2000 to set its new Transaction Processing Performance Council (TPC) TPC-C benchmark of 262,243 transactions per minute (tpmC) with 12 clustered Compaq systems.

1. Cascading DRI

Cascading Declarative Referential Integrity (DRI) might not be SQL Server's sexiest new feature, but it's the feature you'll most likely use. With cascading DRI, you specify a relationship between a parent and a dependent table so that a deletion or update of a row in the parent table automatically cascades to corresponding rows in the dependent table.

SQL Server 2005 new features

http://www.techrepublic.com/article/top-10-advanced-features-of-sql-server-2005/

1. Database mirroring

Database mirroring is a new high-availability feature in SQL Server 2005. It's similar to server clustering in that failover is achieved by the use of a stand-by server; the difference is that the failover is at the database level rather than the server level. The primary database continuously sends transaction logs to the backup database on a separate SQL Server instance. A third SQL Server instance is then used as a witness database to monitor the interaction between the primary and the mirror databases.

2. Database snapshots

A database snapshot is essentially an instant read-only copy of a database, and it is a great candidate for any type of reporting solution for your company. In addition to being a great reporting tool, you can revert control from your primary database to your snapshot database in the event of an error. The only data loss would be from the point of creation of the database snapshot to the event of failure.

3. CLR integration

With SQL Server 2005, you now have the ability to create custom .NET objects with the database engine. For example, stored procedures, triggers, and functions can now be created using familiar .NET languages such as VB and C#. Exposing this functionality gives you tools that you never had access to before such as regular expressions.

Weekly SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

4. Service Broker

This feature gives you the ability to create asynchronous, message-based applications in the database entirely through TSQL. The database engine guarantees message delivery, message order consistency, and handles message grouping. In addition, Service Broker gives you the ability to send messages between different SQL Server instances. Server Broker is also used in several other features in SQL Server 2005. For example, you can define Event Nonfictions in the database to send a message to a Queue in the database when someone attempts to alter a table structure, of if there is a string of login failures.

5. DDL triggers

In previous articles, I outlined how you can use data definition language (DDL) triggers in SQL Server 2005 to implement custom database and server auditing solutions for Sarbanes-Oxley compliance (here are part one and part two of my SOX articles). DDL triggers are defined at the server or database level and fire when DDL statements occur. This gives you the ability to audit when new tables, stored procedures, or logins are created.

6. Ranking functions

SQL Server 2005 provides you with the ability to rank result sets returned from the database engine. This allows you to customize the manner in which result sets are returned, such as creating customized paging functions for Web site data.

7. Row versioning-based isolation levels

This new database engine feature improves database read concurrency by reducing the amount of locks being used in your database. There are two versions of this feature (both of which must be enabled at the database level):

• Read Committed Isolation Using Row Versioning is used at the individual statement level, and guarantees that the data is consistent for the duration of the statement.
• Snapshot Isolation is used at the transaction level, and guarantees that the data is consistent for the duration of the transaction.

The database engine is able to guarantee the consistency through row versions stored in the tempdb database. When a statement or transaction is issued with their respective isolation levels, read operations accessing the same data that is being involved in a transaction will read from the previous version of the data that is stored in tempdb. Using these techniques in the appropriate situations can significantly decrease your database locking issues.

8. XML integration

SQL Server 2005 introduces the new XML data-type. You can store full XML documents in this new data-type, and you can place validations on the well-formed documents in the database. Additional enhancements include the ability to query the XML documents and create indexes on the XML data-type.

9. TRY...CATCH

In a previous article, I outlined how you can use the new TRY...CATCH constructs in SQL Server 2005 to catch and handle deadlocks when they occur in the database. This long-awaited feature simplifies error handling in the database.

10. Database Mail

Database Mail, the eventual successor to SQL Mail, is a greatly enhanced e-mail solution available in the database engine. With Database Mail, there is no longer a dependency on Microsoft Outlook or MAPI e-mail clients. Database Mail uses standard SMTP to send e-mail messages. These messages may contain query results, attachments (which can be governed by the DBA), and is fully cluster aware. In addition, the e-mail process runs outside of the database engine space, which means that messages can continue to be queued even when the database engine has stopped.

SQL Server 2008 new features

http://www.databasejournal.com/features/mssql/article.php/3816486/10-New-SQL-Server-2008-Features.htm

Geospatial Data Types and Functions

Finally, some Geographic Information System (GIS) capabilities have been incorporated into SQL Server. With SQL Server 2008 two geospatial data types where introduced and a number of geographic functions. The two new data types are geometry and geography. The geometry data type is a planer data type that represents the Euclidean coordinate system, or more commonly called the flat earth model. The geography data type represents ellipsoidal data and represents the round earth model. With these new data types, you can identify a specific point on the earth, or other geographic artifacts like roads, lakes, cities, countries, etc.

With the support for the geometry and geography data types Microsoft provides a number of different types of Open Geospatial Consortium (OGC) methods. These methods allow you to define points, linestrings, polygon, distance, intersection, etc. These GIS enhancements should now allow you to easily incorporate mapping functionality into your applications.

Resource Governor

When you are running many different kinds of processes on your SQL Server machine, you need a way to control the resource intensive processes so they don’t consume all the resources of your machine. The Resource Governor is a way to control those processes that are resource hogs. With the Resource Governor, you can limit CPU and Memory resources for those sessions that can be identified as using excessive resources. The Resource Governor uses classifier functions to identify which workload a particular session should use. The classification of a session in turn associates them with Resource Pools. By limiting the amount of CPU and Memory associated with a Resource Pool, you can control the amount of resources classified sessions can consume. By using the Resource Governor available with SQL Server 2008, a DBA can better control resources usage to provide a balanced throughput of all sessions. The Resource Governor is only available with the Enterprise and Developer editions of SQL Server 2008.

Policy-Based Management

With Policy Management, you can consistently manage your SQL Server 2008 instances through a set of rules, known as policies. You can write policies that will control things like naming standards, server configuration, import/export requirements, etc. By using a standard set of policies across all your SQL Server instances, you can consistently manage your servers and minimize your administrative costs to enforce those policies.

A policy consists of a condition that is defined against a facet that is checked against the appropriate target SQL Server components associated with the facet. A facet is a single SQL Server component that contains properties. Here is a short list of some of the facets available: Credential, Data File, Database, Index, Login, Name, Schema, Server, Table, User and View. The condition is a logical expression that validates that the properties of the facet meet your policy requirements. By using the condition defined in a policy you can control what is appropriate and not appropriate for a given facet.

Policy Management is available in all editions of SQL Server 2008.

Table Compression

To help conserve on the amount of disk space consumed by a table and it indexes, Table Compression was introduced in SQL Server 2008. This feature is only available in the Enterprise and Developer editions of SQL Server 2008.

With Table Compression, there are two different types of compression that can be used: Row and Page. Row compression compresses the fixed fields to save additional space in a row. Page compression goes a step further than row compression. It first does row compression and then performs prefix compression and dictionary compression. Prefix Compression is the process of taking reoccurring column prefix values and storing them in a Compression Information (CI) structure stored in the header of a page, and then replaces the actual values with an index to the value stored in the CI structure. Dictionary Compression is the process of taking reoccurring values in a page and replacing them with the index of the value stored in the CI structure.

Since Table compression is done within the database engine, it is completely transparent to the application. From my limited testing of compressing, it appears that 40-60% space savings can be gained by using Table compression. Your compression savings might vary based on the data types you use and data stored in your database tables.

With table compression there is also the possibility to improve the elapsed time of some of your processes. The performance boost occurs because fewer I/Os need to be performed to read the data. Keep in mind that the additional overhead to compress and decompress also is a cost associated with Table compression. Therefore, to improve the elapsed time of your TSQL statement the cost savings associated with performing less I/Os needs to be greater than the additional CPU cost incurred by performing Table Compression.

Backup Compression

New in SQL Server 2008 Enterprise and Developer edition is database Backup Compression. With Backup Compression, the database backups are compressed as the backup file is being written. By using compressed backups, you can save valuable disks space if a disk device is the target for your backups. Compressing backup will help minimize the amount of space needed for your backup. This could help if you don’t have a lot of free disk space.

Because less I/O is needed to write compressed database backups to a file, you might see your backup processes run fasters. Keep in mind compressing backups is a CPU intensive process. So if you are running compressed backup statements while other CPU intensive processes are running both the backup and the processes will be affected if you max out the CPU capacity of your machine.

Data Collections/Management Data Warehouse

There is a new statistic gathering and reporting mechanism called Data Collection. By using Data Collection a DBA can easily collect, store and manage statistics about their SQL Server instances. These statistics are stored in a database known as the Management Data Warehouse. The Data Collection mechanism does not only collect data but it also purges old data. When you define data collections, you identify how long you want to keep the data. Using this retention period SQL Server has a built in processes to automatically purge your Data Collection data from the Management Data Warehouse.

Out of the box, Microsoft has provided three different system Data Collections. One collection collects statistics about queries that are run. Another one collects disk space information. The last Data Collection collects information about the System performance. You can also define your own custom data collections.

Also provided are some canned reports that report on the data collect by the system Data Collections. These reports allow you to view trend information from the data collected by these system Data Collections. The reports are feature rich with hyperlinks to allow you to drill down from a high-level report to more specific detailed report. Using these canned reports, you can get a good picture of what is going on within your server.

FILESTREAM

Much of the data we work with is unstructured data, like images, Word documents, etc. This type of data is typically called binary large objects (BLOBs). Now with SQL Server 2008 you are able use the database engine to manage and store BLOBs as an NTFS file using FILESTREAM. With FILESTREAM, you are able to store BLOBs that exceed 2 gigabytes of space. By default, SQL Server 2008 has disabled FILESTREAM storage. You need to turn it on to take advantage of this kind of storage. Storing your images, Word documents or other BLOBs as a FILESTREAM object allows these objects to be under the control of the database engine. This allows the database engine to backup and restore these objects as part of the database backup/restore process. Also having these objects controlled by the database allows the database engine to also control the security related to these objects. Therefore, users don’t have access to FILESTREAM objects unless they are granted access. FILESTREAM is supported by all editions of SQL Server 2008.

New Date and Time Data Types

Finally, Microsoft has realized applications do not always need a time value stored with a date, or a date stored with a time value. With SQL Server 2008 two new data types, DATE and TIME, became available. Now you can use these new data types to store just the component of a point in time that you need, either a DATE or a TIME. Also provided is a new date/time data type known as DATETIME2. DATETIME2 increases the amount of precision you can have on the time portion of your date/time value. With DATETIME2 data types, you can store up to 7 digits of precision with accuracy down to 100 nanoseconds. DATETIME2 is also a variable length field, meaning you can define the number of digits of precision you want to store for the time portion. Allowing you to identify the precision allows you to conserve disk space for your DATETIME2 columns. Lastly, a new DATETIMEOFFSET data type was introduced. This new date type allows you to store date and time values that are time zone aware. This data type also allows you to define the number of digits of time precision you require for your DATETIMEOFFSET values.

Transparent Data Encryption

Transparent Data Encryption is just want it sounds like. Transparent Data Encryption is the process of encrypting your databases at rest transparently from the application. The encryption is done as blocks are written to disks, and then are decompressed when they are read back from disk. So within the buffer pool the database engine works the same, since the data is unencrypted in the buffer pool. There is some increased overhead to perform Transparent Data Encryption. As a number of the other new features mentioned here, this feature is also only available in the Enterprise and Developer editions of SQL Server 2008. When you use Transparent Data Encryption your databases data is encrypted at rest. So if you detach a database it is encrypted and can’t be moved to another server unless the encryption keys are also moved to that other server. The database backups are also encrypted, so you can only restore them to other servers if you also backup and restore the encryption keys.

Change Data Capture

SQL Server 2008 is now able to track changes to your database overtime using a new feature called “Changed Data Capture”. This new change tracking feature is only available in the Enterprise Edition and Developer edition. Once a table in a database is enabled for change data capture all changes to that table are tracked by storing changes in a change table. The change table will contain one record for every INSERT that can be used to identify column values for the inserted records. Each time a DELETE is performed the change table will contain one record for each DELETE that will show the values in each column prior to the DELETE. When an UPDATE is preformed, against a change data capture enabled table, two records will be created in the change table, one with the updated column values and one with the original column values. By using change data capture, you can track changes that have occurred over time to your table. This kind of functionality is useful for applications, like a data warehouse load process that need to identify changes so they can correctly apply updates to track historical changes over time.

http://sqlhints.com/2011/09/11/new-features-in-sql-server-2008/

Following are the some of the new features of the Sql Server 2008 which are very helpful to the Sql Developers

1) Variable declaration allows initialization:

Prior to Sql Server 2008 to initialize a variable, we needed to first declare the variable and then we can initialize it by using SET/SELECT statement as shown below:

1 2	DECLARE @COUNT INT SET @COUNT =100

Now in Sql Server 2008 Variable declaration allows initialization similar to the one we do in C#. Now instead of writing two statements, we can write a single statement as below:

1	DECLARE @COUNT INT =100

[ALSO READ] New Features in Sql Server 2012

2) Insert multiple rows using single INSERT Statement

To understand this feature first create an Employee Table by using the below script:

1 2	CREATE TABLE DBO.Employee ( Id INT,  Name VARCHAR(50) )

Prior to Sql Server 2008, to insert multiple records we use to write statements like below:

1 2 3

INSERT INTO dbo.Employee VALUES(1,'Basavaraj') INSERT INTO dbo.Employee VALUES(2,'Shashank') INSERT INTO dbo.Employee VALUES(3,'Monty')

Now in Sql Server 2008 we can accomplish the same by writing script like below:

1 2 3 4

INSERT INTO dbo.Employee VALUES(1,'Basavaraj') ,        (2,'Shashank') ,        (3,'Monty')

3) Arithematic Assignment Operators
Now Sql Server 2008 also supports the Arithematic Assignment Operators like the below ones:

Operator Usage            Description +=       SET @x+=@y       Same as: SET @x = @x + @y -=       SET @x-=@y       Same as: SET @x = @x - @y *=       SET @x*=@y       Same as: SET @x = @x * @y /=       SET @x/=@y       Same as: SET @x = @x / @y %=       SET @x%=@y       Same as: SET @x = @x % @y

Example:

1 2 3

DEClARE @x INT =2 ,@y INT = 2 SET @x+=@y  SELECT @x as x,@y as y

Result: x           y ----------- ----------- 4           2

4) Table-Valued Parameters in Sql Server:
It provides option for the Client Applications to pass multiple rows of Data to Sql Server.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml /comma separated values and pass it to the stored procedure and in Stored Procedure convert this xml/comma separated values to a table variable/temporary table.

You can find detailed information on the Table-Valued Parameters and also on calling Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code @http://sqlhints.com/2011/09/09/table-valued-parameters-in-sql-server/

5) MERGE Statement

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

You can find detailed information on MERGE Statement @http://sqlhints.com/2011/09/24/merge-statement-in-sql-server-2008/

6) Sparse Column

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

You can find detailed information on Sparse Column @http://sqlhints.com/2011/11/12/sparse-columns-in-sql-server-2008/

7) Date and Time Data Types

Date, Time, DateTime2 etc are the new date and time data type introduced in SQL SERVER 2008. You can find detailed information on DateTime2 datatype in the article Difference between DateTime and DateTime2 DataType.

8) As SP_Depends results were not always reliable/accurate/correct. To resolve this in SQL SERVER 2008 following two DMV’s are introduced

sys.dm_sql_referencing_entities

sys.dm_sql_referenced_entities

9) Filtered Indexes

Filtered Index (i.e. Index with where clause) is one of the new feature introduced in Sql Server 2008. It is a non-clustered index, which can be used to index only subset of the records of a table. As it will have only the subset of the records, so the storage size will be less and hence they perform better from performance perspective compared to the classic non-clustered indexes.