Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Replacing Cursors with Set-Based SQL Queries – Part 2

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

In a recent post I discussed cursors and mentioned, at a high level, an example of replacing a cursor with a set-based series of SQL statements. In this post, I’m going to provide a simplified… Read more

9 comments, 2,822 reads

Posted in Help With SQL on 6 July 2011

School Data Move Done!

A brief mention that we are done with the school year flip process for transitioning to the new school year.

I know I haven’t been quite as active on the site the last week or so – I do hope to post more over the upcoming days/weeks. But today I’ll… Read more

0 comments, 383 reads

Posted in Help With SQL on 1 July 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, 2,783 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, 295 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, 406 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, 611 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, 293 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, 913 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, 689 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

2 comments, 992 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, 1,054 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, 523 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, 626 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, 1,219 reads

Posted in Help With SQL on 1 June 2011

SQL Query Run Time Estimation

A followup to a post about subqueries with an estimation for how long it would take for the query to run.

In a recent post about subqueries, I compared 4 queries and how they handled 4 million rows of data. The queries involved:

1) Joins with AND conditions.
2)… Read more

0 comments, 1,215 reads

Posted in Help With SQL on 30 May 2011

Using Views for Faster SQL Query Development

A discussion of how views give a more denormalized means for querying against normalized tables.

Ad-hoc reports are frequently requested at the Boston Public Schools. Generally each request is different but similar to ones previously made. To the degree that such requests are unique, there’ll always be work involved in… Read more

1 comments, 375 reads

Posted in Help With SQL on 28 May 2011

Random Ordering of Results using NEWID

A discussion of how NEWID can be used to help randomize the results returned from a SQL query.

A scenario I hadn’t encountered until recently is as follows: How does one get a certain number of randomly selected records from a table? Nope, I didn’t need to do this for… Read more

0 comments, 580 reads

Posted in Help With SQL on 26 May 2011

Efficiency of Subqueries and OR conditions in SQL

A discussion of how joins significantly outperform subqueries and how this is more evident when OR conditions are involved.

SQL queries can be written in a number of ways and be functionally equivalent but significantly different speed-wise. A great example of this is the use of cross joins instead of… Read more

0 comments, 431 reads

Posted in Help With SQL on 25 May 2011

Double digits in posts!

Thank you to everyone reading this site - Please enjoy your stay!

HelpWithSQL has hit double digits in posts! I hope everyone visiting the site finds the content and examples useful and interesting. The site has quickly evolved over the last couple weeks and it will continue to do so.… Read more

0 comments, 280 reads

Posted in Help With SQL on 24 May 2011

SQL Joins and Subqueries

A discussion about SQL joins and subqueries with information about how to format join statements properly.

In any non-trivial task, developers need to join tables together. Joins are important when we want to:

    1) Display/get data from more than 1 table in a result set.
    2) Filter out records based…

Read more

1 comments, 588 reads

Posted in Help With SQL on 22 May 2011

Older posts