Category: Databases

General Database Posts

In SQL Server, not all spaces are equal

In SQL Server, not all spaces are equal

I recently had a challenge mapping attributes using a lookup table. I had the same customer name in two tables “bobs widgets”, but when I tried to join on the names, there was no match. To cut a long story short, there is more than one way to encode a space. This picks up from the…

Read More Read More

Some Refactoring Solutions For Magic Numbers

Some Refactoring Solutions For Magic Numbers

Today I’m looking at solutions to Magic Numbers. Magic numbers are hard coded numbers (or references) which appear in scripts and procedures, they creep in very easily. I don’t think that magic numbers are entirely bad. If you have a stored procedure which modifies data for a specific customer, having the one customer ID in…

Read More Read More

Replacing Repeated Magic Numbers

Replacing Repeated Magic Numbers

If an ID or reference is used more than once in a process, it’s best to store it in a variable and reference that. This can slow down debugging, but makes a process far more portable. It also reduces the number of magic numbers in a process (or at least times they are defined). I…

Read More Read More

Cleaning Strings: Removing & replacing extra characters

Cleaning Strings: Removing & replacing extra characters

Part of managing data is cleaning text. Tabs, spaces and line breaks can sneak in, and confuse output. When working with whitespace characters and line breaks, we can’t see them. This can cause a lot of confusion for users (and developers) when unexpected behavior occurs. Keep in mind we have to be careful, often we…

Read More Read More

Search for text in SQL Procedures & Views etc..

Search for text in SQL Procedures & Views etc..

A new project, incomplete documentation, and the original author gone. I need to know “what updates and uses this object?”. So, how do I search for it?. If there is good source control and code management, a simple search will work. In many cases, there isn’t the luxury of code management. Search the code behind…

Read More Read More

Managing update differences – NULL and NOT NULL

Managing update differences – NULL and NOT NULL

When are two values the same but different? When they are both NULL. Here I compare equality checks, including my datatype neutral method. The Problem Equality (or lack thereof) between values is an easy check. Is 1 = 3, or is 4 <> 5? Many people (and processes) get unexpected results when they try to…

Read More Read More

Number sequences

Number sequences

Here is a quick look at number sequences, which are a brilliant (and fairly database neutral) method to create numbers (and rows) to build datasets from. While working on the dates articles, I used number sequences a lot and thought they were worth coming back to. What is a number sequence A number sequence can…

Read More Read More

Danger! Danger! Float value!

Danger! Danger! Float value!

I’m not saying that floating point numbers are the devil, and I’m not saying that the float data type should be avoided. But they should only be used when it’s entirely necessary and appropriate. The hidden danger of improper use can can become a ticking time bomb! Most (if not all) dangers come from FLOATs…

Read More Read More

Dates In Oracle: Ranges, Manipulation & Loops

Dates In Oracle: Ranges, Manipulation & Loops

Having spent a lot of time looking at dates in SQL Server, I thought I’d take some time to publish some notes on dates in Oracle. Most of the ideas are the same, but there are some subtleties. After my SQL Server articles: Dates In SQL Server: Managing & Manipulating Dates Dates In SQL Server:…

Read More Read More

Dates In SQL Server: Public Holidays

Dates In SQL Server: Public Holidays

For most business reporting, public holidays (or other special days) cary much business impact. Here are some ideas on solutions. It usually doesn’t take long with a data warehouse project before we want to enhance our calendar. Holidays aren’t built into DBMS systems by default, so we have to create our own solution. I feel…

Read More Read More