Category Archives: SQL Server

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

OPENROWSET and BULK INSERT (1)

{Next Article in Series}
Having found out a bit more about the way OPENROWSET works, it seems to me that if you are using a format file for an ad hoc INSERT of file data this is the only way to go. The rest of the statement works like standard SQL, so you have better control (compared with BULK INSERT) over which columns are selected and which are written. Continue reading OPENROWSET and BULK INSERT (1)

File Imports and Sql Server Language Settings

I had a file (historical FTSE values) to load into a database for some development work so I started up the SSIS Import and Export Wizard to bring it in. There was no error but after three or four attempts it was still failing to bring any rows in. Although I’ve done a lot of ETL I’ve generally used utilities or import exes, so this seemed like a opportunity to get my head around BULK INSERT and bcp.  Continue reading File Imports and Sql Server Language Settings

Sequential uniqueidentifiers in Sql Server Tables

I’ve just come across this feature of Sql Server which apparently has been around since Sql 2008 (code in this example tested in Sql Server 2014). There might be good reasons for wanting to use a GUID in a clustered primary key, but since the key is random there may be issues later on with INSERTs into the table.  Continue reading Sequential uniqueidentifiers in Sql Server Tables

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

Logging the execution of stored procedures in Sql Server

Have you ever needed to know how often a stored procedure is called, or whether it is used at all? This is easy to find out within the database, but of course an unknown number of outside applications may check in from time to time and they may be have been written by end users. It would be useful to be able to switch on logging of executions for a period of time (prior to an upgrade of the database for example) so that obsolete code can be removed and problems pre-empted. Continue reading Logging the execution of stored procedures in Sql Server

Concatenation of row values in Sql Server 2012 and 2014

The requirement here is to concatenate multiple rows into a single string, not simply to do a concatenation of columns within a row, which is easy with the the “+” operator or T-Sql’s CONCAT function.

Concatenation of rows is now very straightforward – I believe the change came in with Sql 2012. Continue reading Concatenation of row values in Sql Server 2012 and 2014