Archive for May, 2012

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:

GO is not a valid TSQL statement

May 6, 2012 1 comment

SQL Server support 2 commands that are not Transact SQL Statements, but are recognized by SQL Server utilities such as: SQL Server Management Studio (SSMS), sqlcmd and osql.

These are:
1. GO (Batch separator)
2. \ (Backslash)

The above 2 keywords can be used to facilitate the readability and execution of batches and scripts.

–> “GO” (batch separator):

– “GO” is not a valid Transact-SQL statement.
– SQL Server utilities interpret “GO” as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.
– The “GO” command is never send to the server.
– Scope of Variables is limited to its batch, once defined in a batch they cannot be used after the “GO” command in another batch.
– You can also use the “GO” command to execute same batch multiple times. Just add a number after it, like: “GO 100” will execute the batch 100 times.
– Command “GO” can even be changed with some other name. In SSMS goto menu, select Tools -> Options -> Query Execution: Here you change the batch separator by-default “GO” to something else. But the SQL Scripts you’ve created earlier will fail if they contain “GO” keyword now.

–> \ (Backslash): You can break a long string into multiple lines for good readability.

sqlcmd & osql are command line utilities so the parameters passed to them and options they take should not be split into multiple lines. Otherwise it may raise an error or you may get unexpected results.

To maintain formatting and readability of your SQL statements you can use “\” (backslash) in your SQL statements when putting them as parameters in sqlcmd or osql utilities.

Categories: SQL Tips Tags:

All about IDENTITY columns in SQL Server

May 3, 2012 1 comment

We know that IDENTITY column property creates an IDENTITY column in a table. This property can be used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
But there are certain things that some of us are not aware of about the IDENTITY property.

Do you know that:

1. Not only INT, but you can also use IDENTITY property with other datatypes like: SMALLINT, TINY INT, BIGINT, NUMERIC and DECIMAL.

2. System function @@IDENTITY returns the last identity value used by the current session.

3. Function IDENT_SEED returns the seed value which is the starting number of IDENTITY column, default is 1.

4. Function IDENT_INCR returns the increment value of IDENTITY column, default is 1.

5. Function IDENT_CURRENT accepts table name as parameter and returns the last identity value inserted into that table.

6. IDENTITY() function as mentioned above can be used with CREATE/ALTER TABLE statements, but it can also be used with SELECT INTO statement while creating a table on the fly with SELECT statement. Check [here].

7. With INSERT statement you can’t provide IDENTITY column name and value, unless you explicitly SET IDENTITY_INSERT ON.

8. SET IDENTITY_INSERT ON does not work with Table Variables.

9. Keyword IDENTITYCOL automatically refers to the IDENTITY column of the table.

–> Let’s check how we can use IDENTITYCOL keyword mentioned on 9th point discussed above:

-- Using IDENTITYCOL at WHERE clause and ORDER BY clause:
FROM [Sales].[SalesOrderDetail]

-- Using IDENTITYCOL with COUNT() function instead of the original column name & with Column name separately:
	COUNT(SalesOrderDetailID) AS Cnt2
FROM [Sales].[SalesOrderDetail]
WHERE ModifiedDate BETWEEN '01/01/2006' AND '02/01/2006'

-- Using IDENTITYCOL at column level and at JOIN's ON clause:
	D.SalesOrderID, D.IDENTITYCOL, D.OrderQty, D.UnitPrice, D.UnitPriceDiscount, [ProductID], 
	H.AccountNumber, H.SubTotal, H.TotalDue
FROM [Sales].[SalesOrderDetail] D
INNER JOIN [Sales].[SalesOrderHeader] H
WHERE H.OrderDate BETWEEN '01/01/2006' AND '02/01/2006'

Check the output of the last 2 SQL statements:
– The 1st output shows same COUNTS for both the columns.
– And 2nd output shows the original name of Column name appearing on the header even when we are using the IDENTITYCOL keyword.

As shown above you don’t have to go, look and type the actual IDENTITY column names of different tables and can be taken care of by using the common IDENTITYCOL keyword.

Check the same demo here in YouTube:

Categories: SQL Tips Tags: ,