I have to confess I hadn’t paid too much attention to the changes to T-SQL in SQL Server 2012, and it was only a question at NextGenUG in Abingdon the other night that prompted me to have a look. It turns out there are some really useful new commands, which I could certainly have done with when I was allowed near production code.  So here’s what I found:

TRY_CONVERT().  If I had a penny for every time I fell foul of trying to convert to dates or numeric from random string data I had loaded up in my BI projects… Anyway this is better solution than ISNUMERIC() and ISDATE() and typically looks like this

SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result

returns 2010-12-31 00:00:00.0000000

SELECT TRY_CONVERT(datetime2, '11/31/2010') AS Result

return NULL

So the same syntax as Convert, and you don’t have to do an initial test but you’ll want to include additional processing to handle the NULL when TRY_CONVERT() fails. There is also a new PARSE() & TRY_PARSE() which deal with converting dates and currency formats across different locales.

FORMAT()

Differs from CAST() and CONVERT() by providing localised output from localised input from a different locale e.g.

With the current date is 15/11/2011 with my machine set to a locale of EN_GB

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result

returns 11/15/2011, so the same date but in US format.

Note This uses the CLR (like HierarchyID and other newer data types) but it doesn't need to be turned on:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

DATEFROMPARTS() builds a date from separate variables from year month day...

SELECT DATEFROMPARTS ( 2010, 12, 31 )

returns 2010-12-31

This is one of a set of functions to build up dates and times to various formats e.g TIMEFROMPARTS(), DATETIMEFROMPART()CHOOSE() allows you to specify which item to pick in a list of items e.g.

SELECT CHOOSE( 2, 'IT Professional', 'DBA', 'Developer', 'Tester' ) AS Result

returns DBA

IIF() works in the same way as in Excel – IIF(condition, value if true, value if false)

CONCAT () joins strings together to one output e.g.

SELECT CONCAT ( 'DEEP', 'FAT',’’,’FRYER’, NULL, 50 ) AS Result

returns DEEPFAT FRYER50

Note: The output data type will vary according to what is put in and if one of the inputs is NULL then CONCAT() will just ignore the NULL value and join the non NULL values together. THROW allows you to raise an error in a TRY.. CATCH block e.g.

THROW 51000, 'we have a problem Houston.', 1;

OFFSET..FETCH

This is a modification of the ORDER BY clause in a SELECT statement.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This allows to you to limit which part of the results are returned based on the ordering clause.

SUMMARY

This isn’t an exhaustive list of all this the new T-SQL commands there is other new T-SQL for accessing the other new features in SQL Server 2012. For example, table valued functions for semantic statistical search, file table, security enhancements etc.

Finally these new commands are all in the current beta of SQL Server 2012 (RC0), if you want to try them out.

Sam needs your assistance one last time in her quest to convince her bosses to deploy Windows. Your tips so far have been a massive help, she now knows just how to deploy Windows 7 and has been able to demonstrate why it will be so much easier than initially thought.

Sam now has to make a formal presentation to the executive board of her company, most of whom know very little about IT and are focused on reducing costs in this challenging business environment. They need to know in plain English - what are the killer features that will save them money, provide a quick return on investment and ultimately make Windows 7 the operating system for them?

Head over to YouTube and submit a short clip (3 minutes or less) as a response to our video here to enter. Be as creative as you like, add music, text or just record direct from your webcam. If you haven’t already got your hands on one, you can get one of the last limited edition mugs just for entering! This fortnight you can win a HTC Titan Windows Phone if your video is judged the best. Remember to promote your video wherever you can to be in with a chance of winning our GRAND PRIZE – a 3D TV, Full HD 3D Ready Camcorder and 3D Conversion Lens for the highest number of views.

Check out the Windows 7 Tech Centre for some ideas on what to talk about and Tweet your response #SaveSam.

Full Terms & Conditions are available here.

Earlier this week we launched a brand new tool on the TechNet UK website to help you discover the top content that will help with your Windows 7 Migration. All you have to do is enter where you are with your migration, or if you’ve not started at all the thing that’s holding you back, and the tool will deliver some great information customised to your situation.

We are planning on updating the tool regularly so we would love to hear your feedback on what you like, don’t like and what you think is missing. Let us know in the comments, or Tweet us @TechNetUK!

Give it a try!

Sam’s received some great tips to help her convince her bosses to deploy Windows 7 and you can watch them all below. For more information about many of the great free tools mentioned check out the Windows 7 Tech Centre.

Congratulations this week go to JangidUK, whose animated video is not only creative enough to grab the board’s attention but also covers loads of great points and relates them all to Sam’s business needs. A 4TB NAS Device and Sonos System will be on its way to you soon!

However, the lead has been snatched from JangidUK by yachtinglimited in the competition to win our Grand Prize of a 3D TV, Full HD 3D Ready Camcorder and 3D Conversion Lens! Yachtinglimited’s Ease of Use video from way back in the first fortnight of the competition now has over 4000 views, so the race is on!

This fortnight's prize is a shiny new Dell laptop, check out what you need to do to win it here.

pauliddon

Windows7ITPro

JangidUK

Mike Halsey is a Microsoft MVP and Microsoft Press author having written “Troubleshooting Windows 7 Inside Out” and with two Windows 8 books set for release during 2012.  He is a keen supporter of getting things done which is why he uses Windows Phone.

Mike is also a teacher of adult literacy and numeracy basic skills, helping people get a much needed step up in life.  He lives in Yorkshire with his rescue border-collie, Jed. 

Most smartphone platforms these days are designed with consumers in mind. After all, what business wanting its staff to be productive wants a handset that encourages updating Facebook, playing Angry Birds and engaging in online chat?

All the major smartphones are guilty of these including Blackberry which made its name in the business space. Now though, Windows Phone’s latest update, Mango, is changing the way businesses can work with and utilise smartphones.

The previous choices for smartphones in the Enterprise were either the aforementioned Blackberry or the now retired Windows Mobile. When Windows Phone first launched a year ago some features were incomplete, though in Microsoft’s defence they managed to pull the entire platform together from scratch in just 18 months.

I thought I’d detail here for you why I now believe that Windows Phone is the only smartphone platform you should be deploying in your business.

Some of the changes are simple and elegant, such as new threaded email views and direct integration with Office 365. Most business want more server-centric features however and Mango delivers these.

Exchange support has been greatly improved and now offers Information Rights Managed restrictions to be placed on messages, determining what recipients can and cannot do with them once they have been received. Exchange support has been tightened up greatly from the first builds of the Windows Phone OS.

There’s better password support enabling IT administrators to enforce password management through Exchange group policy in the same way as exists on the desktop.

Wireless and VPN network access has been improved with support for hidden and fully configurable networks. Search is automatically extended to your Exchange server as well when you set up an Exchange account on a phone.

Server support is comprehensive with not just with a new generation of content-rich apps, but also a targeted application distribution channel for businesses to push their own bespoke applications to handsets.

Integration with Microsoft’s new communication platform, Lync, is built in along with other links to Office and Server-side features, and the full editions of Word, Excel, PowerPoint and OneNote, with the online sync they bring are simply excellent in my view and I use them constantly.

On top of this there are many other improvements in Mango including full support for HTML5, integration with LinkedIn and new richer live tiles bringing the latest and most relevant information directly to the attention of the user.

These are not features offered by other smartphone makers who operate in different ways. Apple’s “buy more apps” approach has left its platform confined to consumers, and Blackberry’s traditional mobile approach has become dated and unpopular with their market share dropping month on month, especially as they try harder to consumerise their platform. Finally Android’s security and malware problems are keeping Google’s OS firmly away from the Enterprise.

With mango, Windows Phone is completely ready for business and with a focus that concentrates on information, productivity and accessibility, and that can be configured for consumer and business-centric environments separately; it stands apart from the competition in my view as being far from a gimmick.

Windows Phone, just one year from its launch is a mature and Enterprise-ready platform that will surprise many as it gains further traction and brings new levels of efficiency to the workplace.

If you would like to read more of Mike’s articles then please visit his website.

You may have noticed my first post to TechNet on Friday 11th November.  I’m Sarah Lamb, the newest addition to the TechNet team.  I’ll be looking after the UK TechNet site, Flash Newsletter and Editing the blog content.  I managed to find a spare 5 minutes to answer the same 10 questions that I asked Dan last week.  I thought it would give you a fun insight into who I am.

In order to help get to know our readers better it would be awesome if you too gave your answers to the same 10 questions I’ll do the same and post my answers next week for you. I’ll also be going round the rest of the IT Pro team and getting them to do the same! And remember that you can also follow us on Twitter!

Those were great tips on Deployment, the board were impressed! Now the board want to know, if they deploy Windows 7, how can they manage it day-to-day?

Here’s the story so far:

Sam is an IT Project Manager who has purchased a large batch of Windows 7 Enterprise licenses without consulting her bosses. They don’t want to move from XP and now her job’s at threat! She needs your help as an IT Professional to convince the board that Windows 7 is worth deploying.

Sam’s bosses are concerned that changes in user profiles will make it difficult to migrate people from XP to Windows 7. She needs to know, how does Windows 7 make management and administration easier for the IT department and save money for the business?

We still have some free limited edition mugs available for new entrants so spread the word and get your friends involved! This fortnight you can get your hands on an Intel Core i3 Dell Inspiron Q15R Laptop if your video is judged the best. Remember to promote your video wherever you can to be in with a chance of winning our GRAND PRIZE – a 3D TV, Full HD 3D Ready Camcorder and 3D Conversion Lens for the highest number of views.

Head over to YouTube and submit a short clip (3 minutes or less) as a response to our video here to enter. Be as creative as you like, add music, text or just record direct from your webcam.

Check out the Windows 7 Tech Centre for some ideas on what to talk about and Tweet your response #SaveSam.

Full Terms & Conditions are available here.