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
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
Part 1 of a discussion about replacing cursors with SQL statements for significant speed improvements.
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
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
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
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:
I’m not… Read more
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
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:
The videos are humorous,… Read more
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
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
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
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
A discussion of how BPS uses a SQL exercise for candidates and why it is helpful.
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
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
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
A discussion of how joins significantly outperform subqueries and how this is more evident when OR conditions are involved.
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
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…