One day you write some code on a new system, or using tables in the database you’ve never used before, and BOOM! A perfectly ordinary query blows up… what is this collation business??!

Ignore SourceID at your peril!

When creating joins in a SQL query, best performance is often attained by including as many primary key columns in the JOIN predicates as possible. In Meditech’s Data Repository, the SourceID column is often overlooked, especially by SQL newbies. SourceID is the first primary key column in virtually every table in Data Repository databases and simply adding this column to your joins can improve performance dramatically.

Here’s a sad scene: You’ve been creating and scheduling SQL Agent jobs for years. You’ve got database maintenance jobs set up, backups, SSIS Packages to import and export data, SQL-only jobs to build datamarts. A whole ecosystem of finely tuned, perfectly running activities … all keeping your DR in tip-top condition.

For today's blog post, we want to help solve this common problem: how to clean up custom objects like #Temp or ##Temp tables in a SQL database when they are no longer needed.

Introduction

As we create custom objects in a SQL database like the DR, we need to be mindful of cleaning them up when they are no longer needed, or when we need to clear an old object out, before re-creating it.

For today's blog post, we want to help solve this common problem: how to limit the number of columns in a matrix to prevent it from exceeding the page width, when there may be any number of columns.

The following information is based on Limit no of columns in SSRS matrix report By Challen Fu, Microsoft.

There is lots of data stored in MEDITECH data, but invariably you are going to encounter that one bit of data that you need, that is not helpfully stored in the tables you are using.

As we go through our daily lives of reporting and analyzing data, we use SELECT constantly. It’s like breathing after a while… and while we might be keeping an eye on the performance of our queries, so that we don’t use unnecessary system resources, they generally don’t crash out on a resource constraint issue and interrupt other processes. SQL Server is good at managing them.

In a modern hospital environment, you inevitably have multiple applications holding disparate sets of data. But that data is often related to the data in other systems, and sooner or later (generally sooner) someone is going to ask you something like:

MEDITECH stores a lot of bulk text in the DR; usually as a sequenced table of lines, with some line length. That text may or may not be formatted with extra characters to control fonts and bold/italic/etc.