Category Archives: SQL

Sql Server Collation Cheat Sheet

This post is not written for an architect or DBA who has to choose a collation and wants an in-depth explanation, but for a developer (perhaps one with an error to fix) who needs a primer on the subject.

Put very simply, collations have to do with languages and with the rules used to compare characters. This is relevant in joins and sorting. You might think that a collation ought to be specified for the query; that in your sql statement you should determine the rules to use for comparing values. You can do that (although you’ll set it at column, not query level), but the columns in the tables have a collation property which is what the query uses by default. This is a convenience as it standardises comparisons and saves you having to specify the rules over and over again. It’s when the two conflict that you have an error which may make it necessary to override one or more default collations. Continue reading Sql Server Collation Cheat Sheet

OPENROWSET and BULK INSERT (3)

{Previous Article in Series}
{Next}
This is the promised post about using a Powershell script to inspect large files. It won’t be possible to open a very large file in Excel of course, and some text readers may struggle under the weight of data. This script is an alternative that runs under most if not all versions of Windows, and doesn’t need an installation or a licence. Continue reading OPENROWSET and BULK INSERT (3)

Just Say No… to Day Number Functions

In a Mental Status Examination a psychiatrist will assess the patient, often to decide whether they should be admitted to hospital. Quoting the article linked above, “Orientation is assessed by asking the patient … what time it is (time, day, date)”. So the patient is asked what day of the week it is.

What if the psychiatrist were to ask instead, “What is the number of today, based upon where it falls among the days of the week”? That might indicate that the doctor himself was under too much pressure and needed to take some time off, and it would also be quite unfair to the patient. We need to know what day it is, we often need to know when the week starts or finishes, but we don’t usually ask ourselves what number the day is.

There are database functions that provide the day number: in Sql Server it’s: SELECT DATEPART(weekday, SYSDATETIME()), and in Oracle it would be TO_CHAR('d', SYSDATE), which you can cast to a number of course. Do we ever need to know it? Continue reading Just Say No… to Day Number Functions

Date Calculations and Daylight Saving Time

Many years ago I worked for an energy business. One nuisance we had to deal with was the “clock shift” when BST (British Summer Time) began or ended. The official prescription for the clock change in the United Kingdom is found at
https://www.gov.uk/when-do-the-clocks-change
“In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.” So the megawatt hours supplied in a contract starting outside BST and ending inside BST would be one hour less than expected, while a contract starting inside BST and ending outside BST would total one megawatt hour more than expected.

Since then I haven’t thought of it much but I was listening to a podcast where a DBA mentioned problems using Sql Server’s GETDATE() or SYSDATETIME() functions Continue reading Date Calculations and Daylight Saving Time

A Column-level Query No-No in SELECT statements

You may sometimes want to do a lookup and return it as a column in your query results – but you don’t want to reference the source table in the FROM clause. In this case you may decide to use a column-level query. I’m not arguing the rights or wrongs of column-level queries, either on performance or theoretical grounds. The problem is that if this approach is applied naively it’s a bug. Continue reading A Column-level Query No-No in SELECT statements

Debugging sql joins and filters in Oracle

This is a basic tip that will save time when a query isn’t returning the expected results (typically, it’s bringing back an empty set) and you need to find out which join or WHERE clause filter is causing the problem. It’s not impossible to do, of course, but it’s nice to have a method that gets a result quickly without too many edits. Take the following query, which should run on your Oracle instance: Continue reading Debugging sql joins and filters in Oracle