SQL Server 2012
SQL Server 2012 is Microsoft's relational database management system. The framework is designed and developed for information management and storage. The framework facilitates various activities of business intelligence, analytics operations, and handling of transactions. A full-featured relational database management system, Microsoft SQL Server 2012 provides a range of administrative tools to ease the burden of database creation, maintenance, and management. Microsoft SQL Server 2012 has several features that support database managers, developers of databases, and developers of BIs. SQL Server 2012 Standard Edition includes a business intelligence data and database management system that allows departments and small companies to run their applications. The SQL Server 2012 Standard is particularly suitable for business intelligence and business-critical applications and offers a framework that meets your requirements.
Top features of SQL Server 2012
Get to know the most amazing features of SQL Server 2012
Column Store Indexes
SQL Server 2012 has introduced a new Column Store Index feature that can be used to boost the efficiency of queries. In data warehousing, it is used. You can get this function by right-clicking "Non-Clustered Column store Index" from the index folder, as shown in the figure below. Column storage is the ability to read the values of a table's particular column without needing to read all the other columns' values. This is unlikely in row-oriented storage since the individual column values are physically stored clustered in rows on the pages, and the whole page must be retrieved from memory by reading a page in order to read a column value.
Microsoft implemented a new architecture called the Business Intelligence Semantic Model (BISM) in SQL Server 2012 to accommodate a wide variety of reporting and research needs. A relational model is BISM. That supports two kinds of models that satisfy much of your needs for reporting and analysis. Using the Excel-like Data Analysis Expressions language, most users are familiar with working with tables and relationships and quickly learn to implement business logic. It is based on the current relational model, making it faster, easy to use, and faster to build than the multidimensional model. Multidimensional modeling could be the best solution for your needs if you need complex modeling, business logic, or protection, or if you need a very large-scale solution.
Since Windows Server 2008, the Windows server core has been around. But since it was not officially sponsored, it could not do so. SQL Server 2012 supports this. This is a major new feature of SQL Server 2012 as well. It still seemed hard when we were running the previous version of SQL Server on Windows Server Core. Support for Windows Server Core from SQL Server 2012 makes leaner and more productive installations of SQL Server and also eliminates possible attack vectors and the need for patching. But we would love to run it over an operating system with limited features allowed when we talk about SQL Server. Windows Server Core is designed for infrastructure applications like SQL Server that do not require a GUI on the same server but provide back-end services. That means it is a variant of the Windows operating system that is GUI-less.
If you have programmed in languages such as C# or other similar languages, then the attempt, grab and throw statements are probably familiar to you. Using a try/catch block, Transact-SQL also gives you this option to find an exception. RAISERROR has remained the sole tool for creating your own errors in SQL Server previous versions. A new THROW statement is used in SQL Server 2012 to raise exceptions instead of RAISERROR in your T-SQL code. So let's take a look at a realistic case. The example was created using SQL Server Management Studio in SQL Server 2012.
Enhanced Execute Keyword
For executing a command string, the EXECUTE keyword is used. In SQL Server 2005/2008, you cannot modify the column name and datatype with the executed keyword. The Stored Procedure must be updated accordingly. WITH RECOMPILE, only the earlier version of SQL Server has the option to force a new plan to be re-compiled. This part is significantly improved by the ability to do that in SQL Server 2012. There is no need to change a Stored Process in SQL Server 2012. You may use the Execute Keyword to modify the column name and datatype.
User-Defined Server Roles
SQL Server has many functions for easily controlling the permissions in your databases, which are security principals that group other principals together. There was no choice in SQL Server 2008 R2 to build a server role. Only at the database level can you define a function. If you build a custom function at the level of the database, then delegate it to users. DBAs have always had the ability, but never a server role, to build custom database roles. A development team works on a project with a database on a shared server, for example. That means that on a shared server, the DBA provides the rights to read/write access to any database. It was a little complicated this operation. Either manually or using undocumented methods was the only way to do it. None of them were good options, either. The DBA can now build a read/write access function on any DB on the server, or some other broad custom server role.
Like Visual Studio, SQL Server 2012 supports business IntelliSense and debugging. In addition, SQL Server 2012 has enhanced metadata discovery that allows you to evaluate the predicted output form from queries, tables, Stored Procedures, views, and other objects. As a consequence, business intelligence and debugging are provided by SQL Server.
The SQL Server provider for Windows PowerShell is one of the very cool things about SQL Server 2012. You gain access to a driver that allows you to work with SQL Server 2012 in the same way that you could work with the file system by using the SQL Server provider. Much like the other Windows PowerShell drives, it becomes another drive. PowerShell has a lot to sell to both DBAs and developers of databases. It is ideally equipped to be both a text editor for programming and a shell (for doing things) and it excels at both. This is mainly due to using all that has come before, not just Windows-based systems, but also Linux-based systems, as stepping stones to understand what has worked and what has not performed so well.
Data Quality Services
SQL Server Data Quality Services (DQS) is a data quality product powered by information. DQS helps you to build and use a knowledge base to conduct a number of essential data quality activities, including data correction, enrichment, optimization, and de-duplication. By using cloud-based reference data services offered by reference data providers, DQS helps you to perform data cleaning. DQS also offers you profiling that is incorporated into its tasks of data quality, allowing you to assess the data's credibility. DQS consists of Data Quality Server and Data Quality Client as part of SQL Server 2012, both of which are installed. Data Quality Server is an instance of a SQL Server feature consisting of three SQL Server catalogs with data quality and storage capabilities. The Data Quality Client is a collaborative SQL Server functionality that can be used by business users, information staff, and IT professionals to conduct computer-assisted data quality analyses and interactively control their data quality. By using the DQS Cleansing component in Integration Services and Master Data Services (MDS) data quality features, all of which are based on DQS, you can also perform data quality processes.
Hyper-V is Microsoft's product for hardware virtualization. It allows you to build and run a software version, called a virtual machine, of a device. Every virtual machine operates like a complete computer, running programs, and an operating system. For enterprises, virtualization is very popular: it enables them to better use hardware by integrating multiple servers on a single host, offers HA capabilities, and reduces various costs, such as heating/cooling, SQL Server licenses, and hardware. To help them move from physical to virtual worlds, I have been involved in several ventures with organizations and have helped them experience these benefits. It is always recommended to set reservations for storage when working with SQL Server on VMware, so I had to do some research when I found this Dynamic Memory functionality with Hyper-V. The Configuration Guide for Hyper-V Dynamic Memory has many advantages and device specifications for the use of Dynamic Memory. This feature is pretty cool in how you can have more or less memory for a virtual machine without needing to power it off.
In conclusion, there is no question that it is very important to keep track of newly introduced features of the technology we use so that we can add more efficiency, get more controls provided, and get easier ways to manage your job.