Tag Archives: transact sql

Stored Procedure Performance – What You Can Do

There are lots of things you can do to address stored procedure performance issues in Sql Server (this is based on version 2014 which is what I’m currently working in). In fact, there are so many techniques that they seemed worth listing in a blog. Of course, correct indexing is fundamental; I’m assuming you’ve looked at that already. What is listed below would be more appropriate for problems such as execution plans being compiled with specific parameters and not working well when other parameters are passed in. There’s no detail here though: please bear in mind that some of the commands listed are well-known to be buggy. You’ll need to Google for more information on issues and trade-offs. Continue reading Stored Procedure Performance – What You Can Do

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

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

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