SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: June 2011

Replacing Cursors with Set-Based SQL Queries – Part 1

Part 1 of a discussion about replacing cursors with SQL statements for significant speed improvements.

In a previous post I discussed where it is best to put logic – SQL or code. In my view, SQL is great for processing sets of data and poorly structured for row-based processing.… Read more

10 comments, 7,657 reads

Posted in Help With SQL on 27 June 2011

Google as the Ultimate SQL Repository

A discussion of how Google can serve as an invaluable resource when facing a SQL-based challenge.

Today I briefly want to mention a question a colleague at work asked: How can a comma delimited string be split using T-SQL but without using a cursor/loop? I wasn’t sure at first, but… Read more

1 comments, 713 reads

Posted in Help With SQL on 23 June 2011

Table Backups and the TableArchive Database

A discussion of how a separate database is used for storing table backups.

In a recent article, one of the things I mentioned was saving table backups before important and/or risky processes. For example, I generally do this before truncating the next year school assignment and school waitlist tables… Read more

2 comments, 867 reads

Posted in Help With SQL on 20 June 2011

Being Cautious With SQL Based Processes

Why being careful is such an important mindset when working with SQL and databases.

At the Boston Public Schools, certain processes need to be run at particular times of the school year. There’s a couple of specific windows of time where we print choice forms for students. There’s a few… Read more

5 comments, 1,098 reads

Posted in Help With SQL on 17 June 2011

SQL Books Arrived Today

A small post regarding a couple SQL books I bought from Amazon.

A couple new books arrived in the mail from Amazon.com. Both are by Stéphane Faroult, whose videos I mentioned a few days ago.

The books are:

1) Refactoring SQL Applications
2) The Art of SQL

I’m not… Read more

0 comments, 810 reads

Posted in Help With SQL on 15 June 2011

Database Documentation with a Data Dictionary Part 2

A continuation of the data dictionary discussion with information about how the documentation is done.

Today I’m going to continue discussing the data dictionary tables we’ve set up at the Boston Public Schools. The SQL for creating the tables and inserting the baseline example records is in my previous data… Read more

0 comments, 1,434 reads

Posted in Help With SQL on 13 June 2011

Some Good SQL-Related YouTube Videos

A brief discussion and links to a series of YouTube videos from Stéphane Faroult.

I’ll continue the discussion of the table dictionary tomorrow. Today I’m sharing an informative and funny short series of youtube videos I found from Stéphane Faroult:

Part 1
Part 2
Part 3

The videos are humorous,… Read more

1 comments, 1,175 reads

Posted in Help With SQL on 11 June 2011

Database Table Documentation with a Data Dictionary

Why table and column documentation is useful and some SQL for setting up the data dictionary tables.

At Boston Public Schools, we have many hundreds of data tables. Some are used all the time, others are used only during certain times of the year, while others are simply backups or… Read more

3 comments, 1,767 reads

Posted in Help With SQL on 10 June 2011

Using a Staging Environment for Tracking Down Bugs

A discussion why having a staging environment with data that mirrors production is vital to tracking down issues.

Being able to replicate production bugs is highly important. A key aspect to this is having data that is similar or [preferably] identical in a non-production environment where the issue can be… Read more

12 comments, 2,079 reads

Posted in Help With SQL on 8 June 2011

Using Nightly Populated Tables For Faster SQL Queries

A discussion about nightly populated tables and how they have reduced SQL Server load.

What’s more important – speed or data accuracy/quality when generating reports? There’s no definitive correct answer to this question as there’s often a trade-off between the two. In many cases a cost/benefit analysis needs to be… Read more

2 comments, 1,247 reads

Posted in Help With SQL on 5 June 2011

Avoiding OR Conditions with Joins

Why OR conditions in join statements should be avoided and an example fix.

OR conditions and join statements – some things just don’t go well together. Similar to how OR conditions can significantly impair queries having subqueries, they can wreak havoc on joins as well.

Take, for example, the… Read more

1 comments, 1,798 reads

Posted in Help With SQL on 3 June 2011

SQL Exercise During Candidate Interview Process

A discussion of how BPS uses a SQL exercise for candidates and why it is helpful.

At the Boston Public Schools, SQL is highly important. As I mentioned in my introductory post, the data at BPS is vital and a significant chunk of time for any application developer is… Read more

9 comments, 2,644 reads

Posted in Help With SQL on 1 June 2011