One of the most exciting capabilities of SQL Server is the merge statement, which allows two data sets to be merged. For example, if I have a data set containing rows that I want to push into an existing table, I can use the merge statement to insert rows that don't exist in the destination table. It's then possible to update rows that exist in the destination table See code below. The merge statement allows a high degree of performance and extensibility. For example, the merge statement could delete rows on the destination table that exist in the source table and destination table See code below.
SQL Server Enterprise Edition enables native compression on database and transaction log backups. SQL Server database and transaction log backups are invaluable on servers when:. I believe that these improvements are so robust that once you start building reports in Visual Studio , you won't want to go back. Overall, Reporting Services gives developers more functionality and extensibility while also allowing them to generate reports.
Microsoft has added several new report types, including:. Figure 1 click to enlarge. You can render reports in Microsoft Word along with renders for. SQL Server also supported those formats. SQL Server includes support for nested data regions and sub-reports in Excel. Previous versions of Reporting Services were designed to scale up to large numbers of concurrent users; however, Microsoft did not anticipate the need to generate very large reports such as those over 1, pages. Reporting Services is no longer dependent on Internet Information Services IIS , so it can scale to even larger numbers of concurrent users while rendering large multi-page reports.
DBAs can define thresholds for how much memory Reporting Services will consume on the machine it runs on. The Cube and Dimension wizards have much more intelligence built into them, enabling them to autosuggest dimensions and hierarchies and give best practices warnings.
The warnings help you design high-performance Analysis Services cubes. The wizards also are more streamlined in that they require fewer mouse clicks to build a cube.
Microsoft released the feature rich version of SQL Server to manufacturing on August 8, Have you got it installed yet? Possibly, you. The new version of SQL Server offers a host of new features and enhancements that promise to improve performance, increase security, and make life better for.
Running SSIS on a dedicated machine improves performance and eliminates the need to buy multiple licenses. Data profiling allows you to check data quality to see, for example, how dirty your data is. This can be useful in detecting data that's in an unexpected format. For instance, you might expect data to contain St. Data profiler displays such data inconsistencies so you can quantify occurrences and determine if it's a spelling mistake, a data-entry mistake or a legitimate but unexpected entry. Continue to part three. You can see an example of this feature in Figure 1.
Policies are created from a predefined set of facets. Each facet contains a subgroup of SQL Server configuration settings and other events that you can control. You pair these facets with conditions in order to create a policy. Conditions are the values that are allowed for the properties of a facet, the configuration settings, or other events contained within that facet.
Conditions are also values used for policy filters. Say you want the policy to be executed only against a certain database. In this case, you can create a condition that contains the name of the database and then add this condition to the policy. Now the policy will only apply to that one database. Trust me on this—SQL Server Policy Management may sound complex, but once you try it you'll realize it's pretty intuitive.
The new Multiple Server Interaction and Configuration Servers capabilities come in handy when you need to execute queries against multiple servers at the same time. You can register servers in your Management Studio and then place those servers together under a grouping. When you need to execute a policy or query against all the servers in the grouping, you simply right-click on the grouping and do so.
As an added benefit, you can configure this feature to return one resultset per server or merge all the resultsets together into one big resultset.
You can also specify whether you want the server and database names as part of the results so you can separate the individual results from each server. Being able to store the registered servers on the configuration server rather than in each individual's Management Studio is a big benefit. Another nice new management feature is the Data Collector. Database administrators often need to collect management data from a large number of servers, and many of these DBAs have created their own custom solution for doing so.
The Data Collector is a built-in mechanism that eases the task of collecting management-related data. Unlike third-party tools and custom jobs, the Data Collector will be easily understood by most database administrators since it uses SQL Server Agent and SSIS to create a set of jobs and packages to handle the connections, collection, and storage of data as you can see in Figure 2. This central data store makes it much easier to analyze and view the overall management metrics of the database environment.
Over the years, SQL Server database administrators have found their database environments becoming increasingly large. As the size of your database environment increases, you need new methods and tools to achieve the scalability that most enterprises require. SQL Server has introduced several new features that will help.
SQL Server has built-in compression that allows you to compress the database files and the transaction log files associated with the compressed database. With SQL Server , you now get both row-level and page-level compression, offering benefits you don't get with compression at the data file level.
Compression at the row and page levels reduces the amount of data space needed, plus it reduces the amount of memory needed since the data remains compressed while in memory. Compressed data in memory results in increased memory utilization, which benefits the scalability of many systems. SQL Server also introduces compression at the backup level. While database backups only back up the active portion of the database, this still represents as many as hundreds of gigabytes or even dozens of terabytes.
To help conserve on the amount of disk space consumed by a table and it indexes, Table Compression was introduced in SQL Server This captures and places changed data into a set of change tables. Ultimately, it will offer much improved performance and scalability for today's ever-demanding databases. NET Entity Framework now allows developers to create database queries using these entities. With Policy Management, you can consistently manage your SQL Server instances through a set of rules, known as policies. Or maybe you have yet to find any features that would require you to obtain SQL Server Many database developers are responsible for creating the queries that are needed for returning the data required for their applications.
In database environments that have more than one copy of a multi-terabyte backup file, these backups often take up valuable storage space that could be used more effectively. By allowing database administrators to compress their backup files, SQL Server frees up some of this space, so it can be used for live data.
Then there's the Resource Governor. This new feature lets you define the amounts of resources that individual or groupings of workloads are allowed to use during execution. With Resource Governor, you can create an environment in which many different workloads coexist on one server without the fear of one or more of those workloads overwhelming the server and reducing the performance of the other workloads. The benefit of this feature is that you can more effectively use the total amount of resources that are available on your database servers.
Figure 3 shows an example of using the Resource Governor to limit activity on a server. The general performance of databases improves with SQL Server Thanks to several new features found in SQL Server , you can control and monitor the performance of your databases and the applications that execute against them. When you have large numbers of transactions performed every second, the locking that normally occurs during these transactions can have a negative impact on the performance of your database applications.
Here is the link for the new features in Sql Server http: Really nicely put together. Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email. Following are the some of the new features of the Sql Server which are very helpful to the Sql Developers 1 Variable declaration allows initialization: Now instead of writing two statements, we can write a single statement as below: Thanks for the Comments. Could you please let us know your thoughts to make this post better?