SQL Server 2012: overview of SQL Server 2012

sqlserver2012features

Microsoft’s RDBMS product for desktops and enterprise is the SQL Server and SQL Server 2012 is the version released to the market in 2012. The SQL Server 2012 evolved from the 2005 version. The SQL server code started out to be jointly owned by Microsoft and two other companies named Ashton Tate and Sybase. It was released as the desktop product on PS/2 with OS/2 environment back in 1989. By the time several upgrades were released to arrive at the sql server version 2005, Microsoft has own version of the code contributed by Ashton Tate by this time. The 2012 version evolved through 2008, 2008 R2 versions to become the SQL Server 2012.

The SQL Server 2012 grew into the SQL Server 2014 version and the latest product from Microsoft stable is the community technology preview 2016 that has just been released recently. The sql server 2012 adds two major trends to the feature list that was available with the previous 2008 R2 version.

  1. One is to make the existing RDBMS product easier to administer and to simplify development work.
  2. Equipping the products to help in ever larger data handling and analysis is the other set of goals. The SQL Server 2012 adds many such important features to the existing repertoire.

With the SQL Server 2012, Microsoft decided to support only ODBC (open database connectivity). Even the interconnectivity between MS document types supported through OLE was to be dropped. The general release of the SQL Server 2012 came in Mar, 2012 followed by release of Service pack 1 in Nov 2012 and the pack2, in Nov, 2014. The added features include a PowerView. When creating business intelligence reports, you can mash up various data sets in SQL Server 2012 through this feature. Operationally, the AlwaysOn feature adds handling of database failures easier. Multiple copies created by the system in SQL Server 2012 helps make disaster recover quite straightforward. There is even a stripped down version which is not as resources hungry as the full version of the SQL Server 2012 but can let you carry out the most important operations. Programming features enhanced in the SQL Server 2012 include a ColumnStore indexes feature. These are read-only indexes that help optimizing processing queries on large data warehouses. Testing of the SQL Server 2012 procedures and queries can be played out (tried out) on a non-production server without risking crashes affecting your production operations. Operationally, an audit trail is a very important tool for systematic management of the database operations but a security safeguard against malicious activities, if any.

By this point in the development of the SQL Server product, SQL Server 2012 has evolved into an enterprise level DBMS with the ability to handle large volume of enterprise level data, supported by the administrative, program development and security features required for efficient operations. The next upgrade to SQL Server 2012 came in 2014.

If you have any questions, please contact us at SQL Server Tutorial 2012; we are always happy to help!

 

SQL Server Enterprise – The most powerful edition of SQL Server

The Microsoft RDBMS product SQL Server is available in several editions. The most common ones are the Express, Standard and the Enterprise editions. Standard versions of each release will have more features and capabilities than the Express version. SQL Server Enterprise edition, in turn, has more capability than the standard editions of each release. Depending on the release version of the product, there may be additional versions.

For example, the 2012 release has a business intelligence edition. We shall concentrate on this latest product here, the SQL Server 2012 Enterprise version. While the Express edition is for familiarization, the SQL Server Enterprise is for serous applications like implementing high capability data centers with extensive database and business intelligence solutions.



Some of the notable features of the SQL Server Enterprise edition 2012 include the following:

  • Data quality services that help profile, clean up and match data ensuring the quality of the data going into the database is dependable
  • Power View or a means for data visualization and exploration  by users conveniently
  • Distributed replay features helps testing applications, configurations and upgrades. This can be very handy in SQL 2012  Enterprise adoption in the organization. A range of free tools are available so that users can be sure of a hassle-free  transition to the new version
  • Enhanced audit facility helps flexibility in auditing, and making audit possible in all versions of the product. The audit trail thus created in SQL Server Enterprise helps ensure all activity by authorized people can be tracked
  • SQL Server Enterprise has an AlwaysOn feature that manages higher uptime, quickest possible fail-over and utilization of hardware. The SQL Server AlwaysOn feature has been newly introduced and ensures higher availability and business continuity in case of disasters
  • xVelocity features in the SQL Server 2012 Enterprise help in a significant rise in performance in the intended applications of business intelligence and warehousing. xVelocity supports in-memory data processing technologies and can boost performance by a factor of 10 to 100 times.

The huge amounts of data that gets generated today needs to be managed and stored. One never knows what kind of insight is available from the big data collection. Besides the efficient storage and management implemented in SQL Server Enterprise edition, it ensures the ability to view the data to discern any valuable patterns. An additional tool of Power Pivot in the Power View feature of the SQL Enterprise version helps users to access and combine data from other sources to derive meaning to data visualizations being made. Support of cleansing of data helps the IT department to ensure BI solutions that are dependable. There will be no need to drill down to low-level data to ensure they are good data.

Whenever a new version of a product arrives on the scene, a question that arises without fail is whether existing users should upgrade. ROI percentage and the time it takes to recoup the investment are good metrics to help you decide. According to a Forrester study commissioned by Microsoft for an upgrade to SQL Server Enterprise 2012 has indicated that ROI percentage is 189%, and the payback time is just one year. In order to learn more, check out SQL Server 2012 Training.

 

SQL Server Management Studio 2012 – What is it?

SQL Server Management Studio or SSMS is a product that is a part of Microsoft’s SQL Server 2012 product and has been available from the time the SQL Server 2005 came out. This is a component that helps manage all the parts of the SQL Server. SQL Server Management Studio 2012 can do the following for all the software modules in the server:

  • Configuring
  • Administering
  • Managing

SSMS is a crucial product and a critical piece in managing the database product; it is necessary to become familiar with the product first. Starting with the SQL Server Management Studio Express studio then seems to be a prudent approach. Getting the express version of the SQL Server, which includes the Management Studio could be downloaded from the Microsoft site. Installing the product should not create any problems. You should invest in a regular version after your team is comfortable with the SQL Server Management Studio 2012.

The SQL server Management Studio is like the control center for the product and helps you manage the features of the major modules of the product. MS has their products available in different configurations. The express version is usually a free product that helps users get familiar with the tool before plunging in into larger configurations that are priced. The SQL Management Studio 2012 product download module includes a complete tutorial. The first step for familiarization should be to study this guide. You are provided an access to the user community. This can help with any queries you may have regarding the product, its installation and configuration.

The SQL Management Studio provides the following features that will help manage the server:

  • A minimal database engine
  • An integrated environment to let you work with the product, and any scripting necessary
  • Data analysis services
  • Reporting services

The Microsoft SSMS 2012 provides an activity monitor. It is possible to schedule actions at different times and to have multiple tool open at the same time. The SQL Server Management Studio can monitor these activities. If you needed an uncluttered and reduced higher level view, it is possible to apply filters.

The SQL Management Studio has an object explorer that can provide access to all types of objects within the server environment. Access to the SQL server agent for administering them in a replicated environment, filtering of schema, name or date is available. The SSMS will let you filter parts of these objects too. Objects inside the database could be populated asynchronously. If necessary, the objects could be filtered with the metadata associated to the objects.

Scripting capabilities keep getting updated with the server version releases. This is supported with templates for quick development; script editing could be off-line, even when disconnected from the server and a source control tool for managing the script sources.

SQL Server Management Studio capabilities could be extended, given the way it is built. However, the Microsoft does not promise any support for extensions users may build. No official documents that may help in such developments are also published. This policy is so strict that when you seek customer support, you need to remove any such extensions if you have any installed.

SQL Server Management Studio – Related Links

 

 

 

Overview of SQL Server Management Studio

SQL Server Management Studio aka SSMS can be defined as an environment that is integrated for managing the SQL Server Infrastructure. It is a combination of tools that can be used for configuring, monitoring and administering the SQL Server.  SQL Server Management Studio also makes available tools for deployment, monitoring and up-gradation of the data tier components like data warehouses and databases that are brought into use by applications and construct queries and corresponding scripts.

Benefits of SQL Server Management

There are a number of benefits of SQL Server Management Studio, the most important being that it is a comprehensive utility that manages SQL Server objects. It combines a graphical interface and capabilities related to scripting. It can also be used for managing Database Engine, Analysis Services, Integration Services, and Reporting Services

Features of SQL Server Management Studio

SQL Server Management Studio supports a number of highly developed features. The general features of the SQL Server are that it is able to support most of the administrative functions and an environment that is integrated and single allows effective authoring and management. There are dialogs that for managing various aspects like objects, Analysis and Reporting Services. Here is a screen capture of what SSMS looks like for SQL Server 2012.

 

It also supports new Scripting Capabilities. This is supported by the integrated script editors that are contained in the SQL Server Management Studio. The script editors allow authoring Transact-SQL, MDX, DMX, and XML/A. The main features that SQL Server Management Studio supports under this head are a dynamic environment allowing access to information without any loss of time, various ready templates and the ability to customize them, renders support for functions like editing and writing queries and scripts while providing a new interface that allows XML results to be viewed. The Object Explorer part of the SQL Server Management Studio filtration using the entire name, date or schema or a part of it and the asynchronous members are filtered using the metadata. SQL Server Management Studio supports plug-ins and add-ins (extensibility) for it s built upon the Visual Studio Isolated Shell. This allows usage of extensions that have been developed by third parties.


Tool Windows in SQL Server Management Studio

For all stages of development and administration, the SQL Server Management Studio provides a number of powerful tool windows. Some of these tools are capable of being used on any SQL Server part while others are limited to only specific components. The tools that can be used for all the components of SQL server and their purpose are enumerated in the table below:

 

Tool

Purpose

Can be Viewed

Object Explorer This is used for browsing servers, creating and locating objects. It is also used for managing data sources and viewing logs. This tool can be accessed from the View menu.
Solution Explorer This tool is used for storing and organizing scripts and connection information. SQL Server Scripts can be stored as Solutions and use the source control for managing scripts as their evolvement takes place. This tool can be accessed from the View menu.
Template Explorer This is used for creating queries that are based on the templates that exist. Custom queries can also be created and alter the templates that are existing. This tool can be accessed from the View menu.
Dynamic Help This is used for displaying a list of Help topics that are related. For viewing a particular topic, one needs to click on the name of the specific component.

The tools in the SQL Server Management Studio work with each other. To explain it with an example, a server can be registered with the Object Explorer and for opening a SQL Editor window that is connected to a specific database.

We provide SQL Server 2012 Tutorial videos including topics on Administration, Development, Programming and Business Intelligence areas. Feel free to check sample training at this page: http://sqlserver2012tutorial.com/free-video-training/

 

 

 

 

Top 5 Transact SQL Commands in SQL Server 2012

SQL Server 2012 has quite a few new Transact SQL Commands. We are going to discuss Top 5 of these SQL statements, here are they:

  1. SEQUENCE
  2. NEW Date functions
  3. New Logical functions
  4. CONCAT TSQL
  5. Format command in SQL 2012

 

SEQUENCE in MS SQL 2012

Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records. We are including some examples here that you can use with SQL Server 2012.

–By Itself

create sequence dbsequence
start with 1 increment by 5

select next value for dbsequence

Returns 1

select next value for dbsequence

Returns 6

drop sequence dbsequence

–With a table

create sequence idsequence
start with 1 increment by 3

create table Products_ext
(
id int,
Name varchar(50)
)

INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, ‘ProductItem’)

select * from Products_ext

/* If you run the above statement two types, you will get the following:-

1    ProductItem
4    ProductItem

*/

drop table Products_ext
drop sequence idsequence

Date Functions in MS SQL 2012

There are a number of new date functions in Microsoft SQL Server 2012. We are going to discuss two of the following functions:

  1. DATEFROMPARTS Function
  2. EOMONTH Function

DATEFROMPARTS function takes three integer parameters and builds a date data type out of them. Here is some SQL 2012 Transact SQL that shows you the DATEFROMPARTS date function in action:

declare
@year int,
@month int,
@day int

set @year=2012
set @month=9
set @day=28

select datefromparts(@year, @month, @day) as mydate

/*
mydate
———-
2012-09-28
*/

EOMONTH Function: Many applications need the last day of the month for a specific month. EOMONTH Function lets you do just that. Lets check out the following Sql statements:

declare @mydate datetime
set @mydate=’07/23/12′

select eomonth(@mydate)
2012-07-31

Other useful datetime functions in SQL Server 2012 are:

-TIMEFROMPARTS
-DATETIMEFROMPARTS
-SMALLDATETIMEFROMPARTS
-DATETIMEOFFSETFROMPARTS

 

New Logical functions in MS SQL 2012

Just like the Data functions, SQL Server 2012 has new features in regards to Logical functions as well. We will take a look at two of them: CHOOSE and IIF SQL functions.

CHOOSE is a simple function and returns the specified index from a list of values. Here are two examples:

Select CHOOSE(2, ‘Kash’, ‘Money’) –Money is second value
Select CHOOSE(1, ‘Kash’, ‘Money’) –Kash is first value

IIF function which is quite popular in MS Office applications is not available in SQL Server 2012. This function takes three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result. In other words, it kinda works like a case statement. Here is the TSQL syntax with examples:

–select iif(first argument, second argument, third argument)

select IIF(100>50, ‘true’, ‘false’)

Returns true

select IIF(100<50, ‘true’, ‘false’)

Returns false

CONCAT Transact SQL

When you need to combine different text values in SQL Server 2012, you can use the new feature CONCAT. This function helps you concatenate two or strings together. Here is my name with CONCAT function!!

SELECT CONCAT (‘Kash’, ‘Money’, ‘Millionaire’)

Returns KashMoneyMillionaire

–Also can be used in a table, using employees from Northwind database

select * from employees

select concat(firstname, ‘ ‘, lastname) as FullName
from employees

FORMAT command in MS SQL 2012

Using FORMAT command is especially helpful when you are working with dates in SQL Server 2012. Syntax is as follows:

FORMAT (value, format [,culture])

Lets go ahead and see the Format command in action.

SELECT
GETDATE(), –native date
FORMAT (GETDATE(), ‘d’), –without leading zero
FORMAT (GETDATE(), ‘dd/MM/yyyy’) –custom format

/*

Output for this is:

2012-08-20 10:36:59.247    8/20/2012    20/08/2012

*/

If you would like to learn more, check out our page on SQL Server 2012 Tutorial.

 

 

 

Follow us on: