Beyond Relational - News Letters

March 1, 2010 [Volume 2, Issue 5]

TSQL Challenge 24 - Find discrepancies in the scheduled activities and actual activities performed by employees

This problem is related to a reporting requirement from the data stored by a 'poorly designed' system. The system has one table that stores the appointments scheduled for each employee. There is another table that keeps tracks of the activities performed by employees. Read More »

TSQL Beginner's Challenge 2 - Fight Your Fear for Date Calculations

This challenge invites you to play with date calculations. A lot of people are scared of date calculations and this challenge aims to help them to fight their fear!

This challenge is to find the date based on year, month, day of the week and weekday number. Say for example, if the question is to find the date of 2nd Sunday of January 2010, the answer should be '2010-01-10'. Read More »

TSQL Challenges - Evaluation Progress

We are working hard on catching up with the evaluation of the challenges. The current plan is to finish the evaluation of a challenge every week (a new challenge is published every two weeks) which will help us to catch up in a few weeks.

• TSQL Challenge 16 – Logic Testing Results
• TSQL Challenge 16 - Winners
• TSQL Challenge 17 – Accepted Solutions
• TSQL Challenge 17 – Basic Testing Results Published
• Logic Testing Data for TSQL Challenge 17

Editorial - TSQL Beginners Challenges are back!

We are very excited to resume the beginners challenges that we started some time back and then paused for a while. A lot of people contacted us and expressed their interest in the entry level challenges and we have been working on setting up a plan to resume the beginners challenges.

The goal of beginners challenges is to help understand the various TSQL constructs, operators, functions and keywords and implement them to solve real world problems. The complexity level of these challenges are relatively low and are ideal for beginners to get started with problem solving in TSQL.

So I invite everyone to explore the beginners challenges and share your valiable comments and feedback with us.

Cheers!
Jacob Sebastian, Editor.

advertisement

Free eBook by expert DBA Brad McGehee

Keep your servers in optimal condition with “Brad's Sure Guide to SQL Server Maintenance Plans” eBook, and try SQL Backup Pro for smaller, faster, more robust SQL Server backups. Download your free resources now.

BI Weekly Capsule by Siddharth Mehta

BI Mentor Siddharth Mehta recommends articles to learn how modeling decisions, inter-technical and cross-technology factors map or influence MS BI technical implementation.

• Skills set required for MS BI Tech Lead
• BI Daily : Azure Platform
• BI Daily : Less known SSAS 2008 R2 enhancements
• BI Daily : Connect to SQL Azure using SSRS 2008 R2

Stored procedure optimization tricks and best practices

SQL Server MVP Pinal Dave has compiled a collection of tips and best practices for optimizing stored procedures. Read More »

XQuery Lab 51 – Querying an RDF document using TSQL and XQuery

This installment of XQuery Labs shows how to read information from an RDF document. Read More »

Why does Excel 2007 prompt for repair when opening a document stored in SQL Server 2008 FILESTREAM Column?

Finally, I figured out why Microsoft Excel prompt for repair when opening Excel documents stored in FILESTREAM columns. Read More »

• Another tricky scenario to test your solutions for TSQL Challenge 24
• Microsoft Tech-ED India 2010 - April 12-14, in Bangalore
• XQuery Lab 52 – A More Intuitive way of reading an RDF document
• TSQL Challenge 24 - Tricky Data Set by Lutz Mueller
• How to disable all jobs using a query?
• Windows Azure Platform Readiness - Download Training Kit

February 15, 2010 [Volume 2, Issue 4]

TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log.

It is again time to play with some date/time values. This challenge involves reading the audit log entries generated by a monitoring application and create a report that shows the up-time and down-time of a system that the application is monitoring. Read More »

Business Intelligence for Beginners

We are working on developing the BI resource center to make it valuable to BI Beginners. The idea is to come up with enough resources for a beginner to get started with Microsoft Business Intelligence and build a career in BI.

Check out this collection of articles for beginners who want to learn SQL Server Integration Services or take a look at this page if SQL Server Reporting Services (SSRS) is your area of interest. For those of you who are interested in building up skills in SQL Server Analysis Services (SSAS), we have added a resource page where we will publish a collection of articles and tutorials. See our Business Intelligence page for more details.

Editorial - Can you build a tricky data set?

We recently observed that a large number of solutions fails the logic testing phase though they succeed the basic testing. One of the reasons could be that the source data for the basic testing is provided along with the challenge description, but the data that we use for the logic testing is unknown when people write their solutions.

Solutions from people with relatively more experience seem to be well written to handle the unexpected scenarios and bad data. Usually such solutions pass the logic testing easily. Many people find it hard to imagine the unexpected scenarios and fail to make their code fool-proof against the odd cases.

We would like to invite people interested in compiling the tricky data for the challenges to come forward and build their version of the tricky data. We will give you early access to the challenges which will give you a chance to view the challenges before they are published. If you have a blog or website, you can publish the tricky data there and send us the URL that we publish along with the challenge. If not, you can send us the tricky data and we can publish it on your behalf.

Building tricky data needs a bit of imagination, creativity and some experience with real-world scenarios. If you think you can build your version of tricky data for the challenges, we will be very happy to support you and recommend your tricky data set to our readers. Send an email to tc@beyondrelational.com if you wish to join this initiative.

Cheers!
Jacob Sebastian, Editor.

advertisement

Too many SQL Servers for one pair of eyes?

Keep on track. Use Red Gate SQL Response’s intuitive interface to react to SQL Server health and activity problems sooner. "SQL Response enables you to monitor, get alerted and respond to problems before they start." H John B Manderson. Download a free trial.

BI Weekly Capsule by Siddharth Mehta

BI Mentor Siddharth Mehta recommends articles to learn how modeling decisions, inter-technical and cross-technology factors map or influence MS BI technical implementation. Read More »

• routine_definition from information_schema.routines
• Converting Hexadcimal to decimal
• BI Daily : Migrating from Cognos to Microsoft BI
• How to generate CDATA and NAMESPACE Declarations using FOR XML EXPLICIT
• SQL Server 2008 R2 Official Release in May - What's my take?
• BI Daily : Impact of Normalization on Dimension
• SQL Server 2008 R2 Readiness : Download Training Kit
• SQL Server Backup command

Brief Note about StreamInsight – What is StreamInsight

SQL Server MVP Pinal Dave gives a brief introduction to a new addition to the SQL Server Product Family - SQL Server Stream Insight. Read More »

How to read and write data from SQL Azure using SQL Server Integration Services 2008 R2

BI Mentor Siddharth Mehta explains how to read and write data from SQL Azure using SQL Server 2008 R2 SSIS. Read More »

A Valentine Day Message to the TSQL FROM Clause

Brad Schulz comes up with another master piece TSQL Fiction - Must read for every TSQL Developer. Read More »

February 1, 2010 [Volume 2, Issue 3]

TSQL Challenge 22 - Filling balls into boxes based on rules and preferences.

Who among us wouldn't want to go back to our childhood and enjoy those lovely playful time once again? Well, here is a challenge that might take you back to your childhood and might keep you busy sorting and filling colorful balls into different boxes. Here is an interesting challenge. Read More »

Introducing New BI Resource Center

We have been working very hard on building a Business Intelligence (BI) resource center at beyondrelational.com. A few weeks back we introduced the XML resource center which is currently at the early stage, but will soon be a valuable location for learning the XML features in SQL Server.

The goal of the BI resource center is to bring you enough resources to learn and master Microsoft Business Intelligence platform and products. We are trying to add enough articles, blogs, tutorials, videos and training materials to the BI resource center which will help people interested in BI to get started and build expertise in Business Intelligence. Read More »

Editorial - Early review of challenges was successful!

We tried the idea of making the challenges avaliable for an early review by selected volunteers with the last TSQL Challenge and it was found to be very helpful. The early review process helped us to add more clarification to the challenge rules and enhance the challenge description. After the challenge is published, we found comparatively less number of questions in the forums. We would like to acknowledge the great efforts those volunteers are putting to make TSQL Challenges better.

Another area that we focus on improving is the the speed of evaluation process. We have achieved some progress in this and you will see some visible improvements very soon.

In addition, we added two focussed sections in the website, one dedicated to SQL Server XML and the other to Business Intelligence. We are working hard on improving the content on these sections and bring more resources that will help you to improve your SQL Server skills on those areas. I look forward to hear your comments and suggestions to come up with more exciting stuff and improve the existing resources.

Cheers!
Jacob Sebastian, Editor.

advertisement

Too many SQL Servers for one pair of eyes?

Keep on track. Use Red Gate SQL Response’s intuitive interface to react to SQL Server health and activity problems sooner. "SQL Response enables you to monitor, get alerted and respond to problems before they start." H John B Manderson. Download a free trial.

BI Weekly Capsule by Siddharth Mehta

BI Expert Siddarth Mehta recommends a few "must-read" BI articles to help BI professionals update their BI expertise and to help BI beginners learn Microsoft BI Platform. Read More »

• Integrating An Existing Application With SQL Azure - I
• Integrating An Existing Application With SQL Azure - II
• Version Control SSIS Packages
• Windows Azure and Cloud Computing Learning Material
• Creating a Business ScoreCard using SSRS
• SQL Server Cheat Sheet - Create Database
• SQL Server Cheat Sheet - Restore Database
• SQL Server Cheat Sheet - Statistics

SQL Azure Frequently Asked Questions

Brent Ozar comes up with a very interesting blog post that lists a collection of SQL Azure frequently asked questions. A recommended read for SQL Azure enthusiasts. Read More »

Interview - Vinod Kumar, Microsoft Technology Evangelist

This is the second part of a long interview where Microsoft Technology Evangelist Vinod Kumar speaks about the Beyond Relational features of SQL Server 2008. Read More »

How to Convert Hex to Decimal?

SQL Server MVP Pinal Dave has posted a tricky questions on converting Hex to Decimal. Read More »

Find alternate week day with TSQL

SQL Server MVP Madhivanan shows a TSQL Script that finds alternate week days. Read More »

January 18, 2010 [Volume 2, Issue 2]

TSQL Challenge 21 - Reading, recognizing and processing graphical representation of digits.

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters. Read More »

Introducing New XML Resource Center

Some of you might have already noticed that there is a new XML section at beyondrelational.com. The goal of this section is to bring enough XML resources to you, which will help you to get started (if you are new to XML) or master (if you are already familiar with XML) the XML features supported by SQL Server.

We have setup this section by compiling some of the XML resources from beyondrelational.com and are working on including helpful XML resources available at other locations too. Contact us with your suggestions and recommendations for including resources that you think are helpful. Read More »

Editorial - Improving TSQL Challenges!

Working on the TSQL Challenges is always a learning experience. Every day we learn something new, something that we need to improve and something that we are badly missing. We would like to thank all of you for your continous support, feedback and suggestions that keep us busy improving the way TSQL Challenges are created, managed and evaluated.

There are a few interesting changes we are bringing into TSQL Challenges. First of all, we will be allowing multi-statement solutions for certain challenges. I think this was one of the most frequently requested change by many of you.

We also started making the challenges available for an 'Early Review' to selected participants. The Early Review takes place before the challenges are published. The challenge description and rules are updated based on the recommendations of the team to make them more comprehensible and clarify any confusing remarks.

In addition, from next challenge onwards there will be a Moderator who will assist you with your questions and comments, a FAQ section compiled by the Early Review Team and physical tables instead of table variables to store the input data.

We still have a long list of stuff to do and there are several areas that still need improvement. I look forward to hear yours suggestions to make TSQL Challenges better.

Cheers!
Jacob Sebastian, Editor.

advertisement

SQL Backup tip of the week

“Periodically test backups to ensure that they are good and can be restored.” Brad McGehee, director of DBA education, Red Gate Software. How to also speed up and strengthen those backups? Use Red Gate SQL Backup Pro. Download a free trial.

• Use of SQL Azure in existing Business Intelligence Projects
• Microsoft Codename Dallas
• Typed XML and SCHEMA Collections
• Validating values with SCHEMA
• Custom Types and Inheritance
• XSD Mixed Types
• Creating variable content containers

A collection of TSQL Best Practices and Worst Practices

I happened to read a very interesting collection of TSQL Best Practices and Bad Practices. A must read for all SQL Server developers. Read More »

SQL Server Storage Basics

For those of you want to learn the basics of storage, there is an excellent series of articles by Brent Ozar. One of the best explanations of the storage basics I have ever seen. Read More »

SQL Azure ready for commercial use, Available in North Europe and interesting “how do I” videos

For those of you interested in SQL Azure and would like to start using it commercially, here is the happy news. SQL Azure team announced two weeks back that the product is available for commercial use and have posted links to various resources that you can use to upgrade a CTP evaluation account to commercial. Read More »

Using CROSS APPLY for more manageable and readable TSQL Code

A very informative TSQL Post by Syed which explains a very interesting usage of the CROSS APPLY operator. Syed explains how to use CROSS APPLY to write much more readable and manageable code. Read More »

Story of Three TSQL Programmers on a mission to save a fair maiden!

A very interesting story by Brad Schulz where he tells the story of three TSQL programmers who are on a mission to save a fair maiden who is kept prisoner in a forest by a troll. Read More »

• Full Backup
• Restore
• Database Mail
• IDENTITY
• SQLCMD

January 4, 2010 [Volume 2, Issue 1]

TSQL Challenge 20 - Identify repeating digits in Fibonacci Series

This challenge has absolutely no relevance to a real-life problem, but it is very interesting because it tests your programming skills and abstract thinking.

The Fibonacci series is defined as 1, 1, 2, 3, 5, 8, 13, 21, 34, 55 etc, where each number in the series is the sum of the two numbers before it. For example: 34 = 13+21, 55 = 21+34 etc. Some Fibonacci numbers have repeating digits, like 55 for example, which has a single repeating digit. The Fibonacci number 17711 has 2 (different) repeating digits.

Your job is to look through the first 92 Fibonacci numbers (since the 93rd number is beyond BIGINT range) and produce a result set showing the 5 lowest Fibonacci numbers for each quantity of (different) repeating digits. Read More »

SQL Server Challenge Idea Contest 2010-Q1

Welcome to the first SQL Server Challenge Idea Contest. This is an opportuity for you to come up with interesting TSQL challenge ideas which you may have seen, heard or solved in the past. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More »

Congratulations to the winners of TSQL Challenge 15!

We are just done with the evaluation of TSQL Challenge 15 and the results are announced.

• Basic Testing Results
• Logic Testing Results
• Load Testing Results
• Winners

Editorial - Identifying the best solution!

One of the 'challenges' that we face while evaluating the solutions is the methods to identify the best solution. We currently calculate a combined rank based on reads, writes, cpu and duration of each solution. There are people who argue that a combined ranking method like this may not help to identify the best solution because in most cases it is only the duration that matters.

There are other people who argue that queries that executes 'super fast' but use a large amount of cpu and excessive reads and writes are not good because they may adversely affect other queries running on the server. We would like to know what you all think about this. Please read this post and share your thoughts on this.

Cheers!
Jacob Sebastian, Editor.

advertisement

SQL Backup tip of the week

If you’re tasked with taking daily SQL Server backups, compressing those backups can save you both valuable time and storage space. SQL Backup Pro from Red Gate Software provides four compression levels and a Compression Analyzer, so you can optimize for faster, smaller backups. Find out more.

In This Issue

• Interview with SQL Server MVP Peter Larsson
• Database Mail Cheat Sheet
• Show space usage for all databases
• Do I need to backup Azure Database?
• The Basics of Storage
• Bad database practices: allowing apps to connect to the server hostname

Also This Month

Use of SQL Azure in existing Business Intelligence Projects

Siddarth Mehta writes about SQL Azure and Business Intelligence. Read More »

XQuery Lab 50 – Reading data from Google Analytics Atom Feed.

I have just made the 50th post in the XQuery Labs series. XQuery Labs is a series of short posts that help to learn XQuery with SQL Server.Read More »

Difference Temp Table and Table Variable – Effect of Transaction

SQL Server MVP Pinal Dave writes about the differences between Temp tables and table variables when used within a transaction. Read More »

A Major Lesson to Learn from 2009 – Don’t Just Backup, Test Recovery

SQL Server MVP Jonathan Kehayias talks about major cases of data loss from missing or damaged backups. Read More »

Quick Reference

• Full Backup
• Restore
• Database Mail
• IDENTITY
• SQLCMD

December 21, 2009 [Volume 1, Issue 3]

TSQL Challenge 19 - Working with consolidated calculations on hierarchical structures

This challenge involves generating an organizational hierarchy and calculating the total orders created by each employee and his/her subordinates. It involves a number of challenges such as generating a resultset in the correct hierarchical form, calculating the orders created by each employee and his/her subordinates and finally calculating the total orders created by self and all the subordinates. Read More »

SQL Server Challenge Idea Contest 2010-Q1

Welcome to the first SQL Server Challenge Idea Contest. This is an opportuity for you to come up with interesting TSQL challenge ideas which you may have seen, heard or solved in the past. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More »

Congratulations to the winners of TSQL Challenge 14!

We are just done with the evaluation of TSQL Challenge 14 and the results are announced.

• Basic Testing Results
• Logic Testing Results
• Load Testing Results
• Winners

Editorial - Challenge Idea Contest!!

It is quite common that we come across a number of programming challenges as part of our day-to-day work-life as an IT professional or developer. Some of them may be quite repetitive and others may be bit uncommon, unique and interesting. Most of the challenges that we publish through TSQL Challenges were originated from our experience as SQL Server developers and Database Administrators.

Everyone who works with SQL Server (or any other technology) goes through a number of such experiences each day. We would like to hear about those interesting SQL Server challenges that you have solved, seen or heard (or problems that you can imagine). I am very glad to announce the first SQL Server Challenge Idea Contest and look forward to see your entries soon!

Cheers!
Jacob Sebastian, Editor.

In This Issue

• TSQL Challenge 19
• TSQL Challenge 14 - Winners
• SQL Server - Database Mail Cheat Sheet
• SQL Server Solutions in the Cloud
• Yes, Table Variables and Temp Tables both use the tempdb
• Interview - SQL Server MVP Vijay Kadiyala, speaks about TSQL Challenges

Also This Week

Delimited String Tennis Anyone?

Did you see the "Delimited String Tennis Tournament" between Brad Schulz and Adam Haines? Read More »

Differences in Vulnerability between Oracle and SQL Server

SQL Server MVP Pinal Dave explores the differences in vulnerabilities between Oracle and SQL Server.Read More »

Performance consideration when using a Table Variable

SQL Server MVP Peter Larsson talks about the performance aspects to be considered while using a Table Variable Read More »

SQL Server 2005 – How To Move 10 Millions Rows In 1 Millisecond

SQL Server MVP Adam Hainess demonstrates a script that generates 10 Million rows in 1 Millisecond Read More »

Quick Syntax Reference

• Using Database Mail
• Common Table Expressions (CTEs)
• Connection to SQL Azure using OLEDB
• Using SQLCMD
• Working with OPENROWSET

Thank you for supporting TSQL Challenges. We have created a number of badges for those of you who had been supporting us continously. Feel free to use them on your blogs, websites, email signatures etc. Read More »

December 7, 2009 [Volume 1, Issue 2]

TSQL Challenge 18 - Generate text formatted month calendars

This is the first time we are coming up with a true Calendar Puzzle. Brad Schulz from TSQL Challenges Team has come up with a very interesting challenge which is all about generating calendars for given number of months. While the real-world application of this challenge is not very common, this is a good chance to test your date/calendar logic. Read More »

An Efficient and Transparent Evaluation Process

One of the areas we always knew that we need improvement, is the evaluation process. We had been constantly working on improving the evaluation process to improve the accuracy, quality and transparency of the process.

We look forward to hear your comments and feedback on the new evaluation process. Read More »

Evaluation of TSQL Challenge 13 Completed!

We are just done with the (re)evaluation of TSQL Challenge 13 and the results are announced. We had been working hard on setting up a new website where we could publish and manage the solutions more effectively. As a result, we came up with a new website, where we have published the results of TSQL Challenge 13.

• TSQL Challenge 13 - Basic Testing Results
• TSQL Challenge 13 - Logic Testing Results
• TSQL Challenge 13 - Load Testing Results
• TSQL Challenge 13 - Winners

Editorial - Challenges dont die anymore!!

It is quite natural that a puzzle dies after the submissions are evaluated and the winners are announced. However, I dont think this is true with a TSQL Challenge. Many times people come to us with better and better solutions after a challenge is closed and winners are announced.

This made us think of keeping the challenges always "ON" mode where people can continue to submit solutions and if one of those solutions are better than the existing 'best' solutions, we will adjust the ranks accordingly. That is what we tried to do with TSQL Challenge 13. Though the challenge is closed and winning solutions are identified, the challenge is still open and we look forward to see if there is a better solution that can beat one of the winning solutions.

Cheers!
Jacob Sebastian, Editor.

Submit a Challenge Idea and get paid!!

Have you got an interesting challenge idea? Send us your challenge ideas and we will publish it through TSQL Challenges and you might even get paid!

In This Issue

• TSQL Challenge 13 - Results
• TSQL Challenge 18
• An efficient and transparent evaluation of challenges!
• Acceptance of SQL Azure Cloud based computing
• SQL Server Backup Cheat Sheet
• Microsoft Business Intelligence Project Booster Kit

Also This Week

Throw Your MONEY Away

Brad Schulz explains why he decided not to use the SQL Server MONEY Data Type. Read More »

SQL Azure - Step by Step.

SQL Server MVP Pinal Dave walks you through the basics of SQL Azure. Read More »

SSIS: How to handle updates

Syed Mehroz explains how to handle updates in SSIS. Read More »

Quick Reference

• Getting Started with XML Schema Collections
• OPENDATASOURCE
• SQL Server Database Mirroring
• Creating a Number Table
• IDENTITY

Support TSQL Challenges

Thank you for supporting TSQL Challenges. We have created a number of badges for those of you who had been supporting us continously. Feel free to use them on your blogs, websites, email signatures etc. Read More »