Sunday, July 28, 2013

New Features in Microsoft SQL Server

New features of SQL Server 2005/2008

2005

  • Exception Handling using TRY-CATCH 
  • CTE (Common Table Expression)
  • PIVOT
  • CLR
  • New functions (ROW_NUMBER, RANK, DENSE_RANK )
  • New operands (INTERSECT, EXCEPT)
  • New clause (OUTPUT, TABLESTAMP)
  • New DataType (XML)
  • Data Tuning Advisor

2008





Introduction
At TechED 2009, Microsoft has announced the launch of SQL Server 2008 R2 which was also known to the SQL Server Community by its code name Kilimanjaro. Microsoft will release SQL Server 2008 R2, first Community Technical Preview (CTP) during the second half of 2009. SQL Server 2008 R2 provides lot many new features and capabilities for Business Intelligence users which can be leveraged by many organizations around the world. To know more about how to install SQL Server 2008 R2, I would recommend you to read the article titled How to Install SQL Server 2008 R2 on a Windows Server 2008 R2.
New Features in Microsoft SQL Server 2012

Overview of New Features in Microsoft SQL Server 2008 R2

Microsoft SQL Server 2008 R2 introduces lot many new features for Business Intelligence users, Developers and Database Administrators. This article gives you an overview of some of the new features in SQL Server 2008 R2.

Capitalize on Hardware Innovation

There was always a demand from customers to increase the number of logical processors supported in SQL Server. In SQL Server 2008 R2 the number of logical processors supported has been increased from 64 to 256 and it will take advantage of the multi-core technology. This will help improve performance and scalability for very large data warehousing applications.

Optimize Hardware Resources

This is a great new feature for database administrators as it will provide a real time insight into Server Utilization, Policy Violations etc. This feature will help organizations to strictly apply organization wide policies across servers thereby helping them maintain a healthy system.

Manage Efficiently at Scale

This feature will help database administrator to gain insight into growing applications and databases thereby helping them to ensure better management of database servers.

Enhance Collaboration Across Development and IT

Database Application development will be more closely integrated with Visual Studio 2010 which will help to ensure higher quality during the application development along with easier deployments and better handling of changes over time.

A Master Data Service (MDS) is a kind of data service that is responsible for: managing, in a single place, the uniqueness, integrity, quality, and interrelationships between the data that matters the most. This will improve the quality of your data because of centralized approach of defining; deploying and managing master data thereby ensure reporting consistency across systems.

Build Robust Analytical Applications

Using Microsoft Office Excel 2010 you can build robust analytical applications which will allow in-memory, column oriented processing engine to allow users to interactively explore and perform complex calculations on millions of data at lightening speeds. Using Microsoft Excel 2010 you can easily integrate data from multiple sources such as corporate databases, spreadsheets and external data sources.

Share and Collaborate with Confidence

Using Microsoft Office SharePoint 2010 users can easily publish reports to SharePoint sites thereby making it available across the organizations. The other advantage is that the reports can be refreshed automatically and you can also maintain version controlling and tracking using SharePoint. Once you start using Microsoft Office SharePoint the SharePoint Administrators will have the ability to set server policies and monitor them more effectively thereby securing the business critical information.

Support for Geospatial Visualization

Microsoft SQL Server 2008 R2 will provide support for geospatial visualization including mapping, routing, and custom shapes. It will also support SQL Spatial and will also provide integration with Microsoft Virtual Earth tiles.
Now that you are familiar with the new features in SQL Server 2008 R2, I would recommend you to read the article titled How to Install SQL Server 2008 R2 on a Windows Server 2008 R2.


Read more: http://www.mytechmantra.com/LearnSQLServer/Microsoft_SQL_Server_2008_R2.html#ixzz3h4sYJ5C0
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook



PARSE() Conversion Function

Introduction 

We all know that to convert data type (form one data type to another), we must use the CONVERT or CAST data conversion function. Microsoft SQL Server 2012 gives us some new data type conversion function that we have to understand.  
Here in this article we are trying to discuss about PARSE()  data conversion function came from Microsoft SQL Sever 2012 onwards. Hope all of you find it informative.

PARSE()
PARSE() function convert any string values to Numeric or Date/Time format. If PARSE() cannot convert, it will returns a zero. PARSE() will result to an error. PARSE() function relies on Common Language Runtime (CLR) to convert the string value. If there is no CLR installed on the server, PARSE() function will return an error. 

Syntax: 

PARSE ( String_Value AS data_type   [ USING culture ] )

Parameter Details:

String_Value 
String expression which needs to be parsed.
data_type
Output data type, e.g. INT, NUMERIC, DATETIME etc.
Culture
Optional string that identifies the culture in which String_Value is              formatted. If it is not specified, then it takes the language of the current session

Example

-- PARSE String to INT
SELECT PARSE('1000' AS INT) AS 'String to INT'
GO

String to INT
-------------
1000

-- PARSE String to Numeric
SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'
GO

String to Numeric
---------------------------------------
1000.06

-- PARSE String to DateTime
SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'
GO

String to DATETIME
-----------------------
2013-05-18 00:00:00.000


-- PARSE String to DateTime
SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'
GO

String to DATETIME
-----------------------
2013-05-18 00:00:00.000


-- PARSE string value in the India date format to DateTime
SELECT PARSE('18-05-2013' as DATETIME using 'en-in') AS 'Indian DateTime Format'
GO

Indian DateTime Format
-----------------------
2013-05-18 00:00:00.000

-- PARSE string value is in the US currency format to Money
SELECT PARSE('$2500' as MONEY using 'en-US') AS 'US Currency Format to MONEY'
GO

US Currency Format to MONEY
---------------------------
2500.00


-- Best Conversion by PARSE
SELECT PARSE('08-JUNE-2013' AS DATETIME)
GO

SELECT PARSE('08-JUN-2013' AS DATETIME)
GO

SELECT PARSE('2013JUNE08' AS DATETIME)
GO

SELECT PARSE('08/JUN/2013' AS DATETIME)
GO

Output is
-----------------------
2013-06-08 00:00:00.000

Difference Between PARSE() and CONVERT()
Here we are providing an example where PARSE() is converting the Value to Date/Time but CONVERT() fails to do.

SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) AS 'PARSE Result'
GO

PARSE Result
-----------------------
2013-06-08 00:00:00.000

SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') AS 'CONVERT Result'
GO

CONVERT Result
-----------------------
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

In next several article we are trying to discuss about others datra Conversion Function of Microsoft SQL Server 2012.

Hope you like it.