Posts Tagged ‘Denali’

SQL Server 2012 Upgrade Technical Guide and Why Upgrade

May 16, 2012 Leave a comment

Microsoft SQL Server is a comprehensive database server and information platform offering a complete set of enterprise-ready technologies and tools that help people derive the most value from information at the lowest total-cost-of-ownership. Enjoy high levels of performance, availability, and security; employ more productive management and development tools; and deliver pervasive insight with self-service business intelligence (BI).

A complete and integrated platform, Microsoft SQL Server brings it all together to get more value out of existing IT skills and assets, increase the productivity and agility of IT departments, and quickly build flexible, innovative applications.

Our SQL Server team have published Customer-ready SQL Server 2012 Upgrade Technical Guide, link.

We encourage customers to upgrade to SQL Server 2012 using this guide (HOW TO UPGRADE), What’s New whitepaper (WHY UPGRADE) created by Joanne Hodgins, and other resources in this web site.

SQL Server 2012 (a.k.a Denali) – New Functions: CONCAT & FORMAT

May 9, 2012 2 comments

SQL Server 2012 gem collection has become bigger with the addition of new items with this release. In my previous blog post [link] I’ve discussed about some of these, like:

1. The new CHOOSE() & IFF() logical functions.

2. PARSE(), TRY_CONVERT() & TRY_PARSE() conversion functions.

3. FIRST_VALUE(), LAST_VALUE(), LAG() & LEAD() Analytical functions.

Here I’ll discuss about new String Functions, CONCAT() & FORMAT(). I welcome both of them as they will provide much ease to the developers without depending on various workarounds and considering certain things people normally miss and end up on various goof ups.

–> CONCAT() function: as its name suggests helps combine values of columns into a single string value. Well you can also do this by using “+” operator and combine columns. But what about NULL values and datatype conversion. You can also do it but what if somehow you miss. This function takes care aboout all these things, NULL values and datatype conversion.

Let’s check this:

USE [AdventureWorks2012]

-- Handling NULL implicitly:
	-- Combining columns by using + operator (NULL values are not handled):
	[FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS 'FullName',

	-- Combining columns by using + operator by handling NULL values explicitly:
	ISNULL([FirstName],'') + ' ' + ISNULL([MiddleName],'') + ' ' + ISNULL([LastName],'') AS 'FullName_with_ISNULL',

	-- Using CONCAT() function (NULL values are implicitly handled):
	CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS 'FullName_with_CONCAT'

FROM [Person].[Person]


So, the output above lists NULL values when you combine multiple columns with NULL values. The 2nd column lists out expected results as we’ve handled NULL values manually. The 3rd column also results expected results as NULLs are getting handled implicitly by the CONCAT() function.

Now, let’s check how CONCAT() handles Datatype Conversion:

-- Combining columns of different datatypes, gives error:
	BusinessEntityID + LastName + EmailPromotion + ModifiedDate AS PersonDetails
FROM [Person].[Person]
Error Message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Sánchez' to data type int.
-- CONCAT() Handling Datatype conversion implicitly:
	CONCAT(BusinessEntityID, LastName, EmailPromotion, ModifiedDate) AS PersonDetails
FROM [Person].[Person]


1Sánchez0Feb  8 2003 12:00AM
2Duffy1Feb 24 2002 12:00AM
3Tamburello0Dec  5 2001 12:00AM
4Walters0Dec 29 2001 12:00AM
5Erickson0Jan 30 2002 12:00AM
6Goldberg0Feb 17 2002 12:00AM
7Miller2Mar  5 2003 12:00AM
8Margheim0Jan 23 2003 12:00AM
9Matthew0Feb 10 2003 12:00AM
10Raheem2May 28 2003 12:00AM

Check this Video tutorial on FORMAT() function:


–> FORMAT() function: Well I’ve used this function with only dates now and I’m quite happy with it. Now to convert a date to a different format you don’t have to CAST/CONVERT your datetime with lot of different options you don’t memorize or remember. With this new function you can format your dates or datetime values in various formats and in different langauges.

Let’s check this:

	GETDATE()											AS DateToday,
	FORMAT (GETDATE(), 'D')								AS DateFormatted, 
	FORMAT (GETDATE(), 'dd/MM/yyyy (MMM)')				AS Date_ddmmyyyyMON, 
	FORMAT (CAST('2012-02-01' AS DATE), 'MMM dd yyyy')	AS Date_MONddyyyy, 
	FORMAT (CAST('2012-02-01' AS DATE), 'MMMM dd yyyy') AS Date_Monthddyyyy;

SET @someDate = '2012-02-01 14:54:39.300'

	FORMAT (@someDate, 'D', 'de')		AS DateNew_German,
	FORMAT (@someDate, 'f', 'fr')		AS DateNew_French,
	FORMAT (@someDate, 'D', 'es-US')	AS DateNew_Snanish,
	FORMAT (@someDate, 'f', 'zh')		AS DateNew_Chinese,
	FORMAT (@someDate, 'D', 'ar')		AS DateNew_Arabic,
	FORMAT (@someDate, 'f', 'hi-IN')	AS DateNew_Hindi;


The 3rd parameter Culture is actaully used to specify the locale and is optional. You can use different Cultures to modify your datetimes in different languages and formats as shown above.

Check this Video tutorial on CONCAT() function:

SQL Server 2012 (a.k.a. Denali) | The Fantastic 12 of 2012

April 24, 2012 2 comments

SQL Server team has come up with a new series to promote its new product and create awareness among techies about what SQL Server 2012 is capable of and what new features it comes packed with. Check out the full series of The Fantastic 12 of SQL Server 2012 here.

The above link will provide you direct access to the technet blogs with detailed info on all 12 topics and videos.

The series will talk about 12 new features, which are as follows:

1. Required 9s Data Protection:
– Always On
– New support for Windows Server Core

2. Blazing-Fast Performance:
– New ColumnStore Index, [video]
– Improved Full Text Search
– Good Compression capabilities.

3. Organizational Security and Compliance:
– Data Protection (Encryption and Compression)
– Control Access (User-Defined Server Roles, Default Schema for Groups, Contained Database Authentication and Active Directory)
– Ensure Compliance (SQL Server Audit and Policy-Based Management)

4. Peace of Mind:
– Product Enhancements (Distributed Replay, System Center Alignment, System Center Advisor and No-fee Service Packs)
– Free Planning Tools
– Tailored Support and Licensing Programs (Mission Critical Support and Enrollment for Application Platform)

5. Rapid Data Exploration & Visualization:
– Self-Service Analytics (Power Pivot)
– Stunning, interactive data visualization (Power View)

6. Managed Self-Service BI:
– Gain insight and oversight (Power Pivot for SharePoint)
– Enable IT Efficiency (End user created, IT managed, Ease of administration through SharePoint and SQL Azure Reporting)

7. Credible, Consistent Data:
– BI Semantic Model
– Integration Services
– Data Quality Services
– Master Data Services

8. Scalable Analytics & Data Warehousing:
– Flexibility and Choice (Hardware and deployment options and Optimized Solutions)
– Massive Scale at Low Cost (Built-in Functionality, Support for Powerful Hardware and Parallel Data Warehouse)
– Complete BI Solution (Scalable OLAP)

9. Scale On Demand:
– Self-service Deployments
– SQL Azure Federation
– Contained Databases
– License Mobility
– Data Sync

10. Fast Time To Solution:
– Optimized Transaction processing and data warehousing
– Complete solutions co-engineered with hardware partners
– Agile to market from weeks and months to days

11. Optimized Productivity:
– SQL Server Data Tools
– T-SQL Enhancements
– Common tools
– SQL Server Management Studio

12. Extend Any Data, Anywhere:
– Support for Any Data
– Statistical Semantic Full-Text Search
– OData
– SQL Azure DataMarket
– Enhanced interop support

This “Fantastic 12” feature list is also available for download, and the PDF is here.

Check out the Webcast/Videos for all these 12 features, here.

SQL Server 2012 (a.k.a. Denali) – Best Practices Analyzer

April 23, 2012 Leave a comment

Microsoft SQL Server 2012 released its Best Practices Analyzer (BPA) on 1st week of April and is available free for download at following [link].

-> Overview …as mentioned in the link

BPA is a diagnostic tool that performs the following functions:
1. Gathers information about a Server and a Microsoft SQL Server 2012 instance installed on that Server.
2. Determines if the configurations are set according to the recommended best practices.
3. Reports on all configurations, indicating settings that differ from recommendations.
4. Indicates potential problems in the installed instance of SQL Server.
5. Recommends solutions to potential problems.

-> System Requirements to install
– PowerShell V2.0
Microsoft Baseline Configuration Analyzer V2.0

The download link also provides information about:
– Installation Instructions.
– And some Additional Information about its capabilities.

… stay tuned for more updates about SQL Server 2012.

SQL Server 2012 (a.k.a. Denali) | “SET FMTONLY ON/OFF” Deprecated | new SPs and DMFs Introduced

April 17, 2012 Leave a comment

While running SQL Server Profiler to know what’s running behind the Application you might have noticed “SET FMTONLY ON” with other SQL statements. Most of you would be knowing this, the main point of putting this post is to let people know that this feature is deprecated in Denali and not to use it in new development work.

“SET FMTONLY ON” Returns only metadata to the client. It can be used to test the format of the response without actually running the query.
Let’s check how it is being used and what it returns:

USE AdventureWorks2012;

-- Simple SELECT without using the "SET FMTONLY ON", OFF by-default:
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

-- Using "SET FMTONLY":

	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]



This feature is deprecated in Denali and should not be used in production code.

Now in replacement to this Denali has introduced 4 new objects to get the same information in easy and more informative way.
These are as follows:

--> 1. SP: sp_describe_first_result_set
-- Returns the metadata for the first possible result set of the Transact-SQL batch.
EXEC sp_describe_first_result_set @tsql = N'SELECT * FROM HumanResources.Employee'

--> 2. SP: sp_describe_undeclared_parameters
-- Returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.
EXEC sp_describe_undeclared_parameters @tsql = N'SELECT * FROM HumanResources.Employee where BusinessEntityID = @id AND BirthDate > @dt'

--> 3. DMF: sys.dm_exec_describe_first_result_set()
-- This is a DMF (Dynamic Management Function) which takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
SELECT * FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM HumanResources.Employee', NULL, 0)
-- It is similar to the SP sp_describe_first_result_set mentioned above.

--> 4. DMF: sys.dm_exec_describe_first_result_set_for_object()
-- This is a DMF (Dynamic Management Function) which takes an @object_id as a parameter and describes the first result metadata for the module with that ID.
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('uspGetEmployeeManagers'), 0)
-- It has same set definition as sys.dm_exec_describe_first_result_set() DMF mentioned above.

-- The last mentioned DMF can also be used with the sys.procedures to get the metadata of the Stored Procedures:
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS X

… Just execute each one of these and check what all information they provide.

I really like these SPs and DM Functions, will be very useful in Metadata based based Dev work.

More info on this check MS BOL: