Opinionated Software

… we have opinions about everything!

Archive for the ‘SQL’ Category

PeopleTools and Parallelism on SQL Server —

During a recent discussion with another PS Admin running on SQL Server it became apparent that his efforts to improve application SQL performance through adding more CPUs (with the associated license costs) were based on a fundamentally wrong assumption: PeopleTools/Application SQL will go parallel if needed. This is simply not true. In fact, most SELECT […]

REGEXP Problem —

A good friend asked me how to replace the lastĀ full stop (period) in a field with another character using a regular expression e.g. a comma. When I asked “what language?” it turned out he wanted to do this using REGEXP_REPLACE in Oracle SQL. It’s a problem with a couple of solutions that I could immediately […]

CRLF and “GO” —

I frequently find myself writing SQL that generates SQL. On SQL Server, I like to build a string that contains the SQL statement followed by “GO”. To do that I use CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10) at the end of the string e.g. {some_generated_SQL}+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10) That way, when I paste the query results into SSMS or a text editor I […]

Respect SQL! —

I am by no means an SQL guru – there are many people far more skilled in SQL than I am. But I like to think I write well structured and clean SQL that is maintainable and performs well. Sadly, all to often I find poorly thought out SQL written in hard to read styles […]

A SQL Tip .. In-Line UNION for previous “n” Months —

Here’s an interesting requirement: “A list of Business Units with the period (YYYYMM) and the monthname (e.g. October) against each one for the prior 3 months”. A simple problem, but here’s my solution: SELECT SETCNTRLVALUE AS [BUSINESS_UNIT] , CONVERT(VARCHAR(6),PREVIOUS_MONTHS.MYDATE,112) AS [YYYYMM], DATENAME(MONTH,PREVIOUS_MONTHS.MYDATE) AS [MONTH_NAME] FROM PS_SET_CNTRL_TBL S, ( SELECT DATEADD(MONTH,-1,GETDATE()) AS [MYDATE] UNION ALL SELECT […]

HOLIDAY_SCHEDULE SQL —

If you come across this as one of your top (if not THE top) SQLs in your HR system: SELECT HOLIDAY_SCHEDULE, HOLIDAY, (CONVERT(CHAR(10),HOLIDAY,121)), DESCR FROM PS_HOLIDAY_DATE WHERE HOLIDAY_SCHEDULE=@P1 AND HOLIDAY=@P2 ORDER BY HOLIDAY_SCHEDULE, HOLIDAYSELECT HOLIDAY_SCHEDULE, HOLIDAY, (CONVERT(CHAR(10),HOLIDAY,121)), DESCR FROM PS_HOLIDAY_DATE WHERE HOLIDAY_SCHEDULE=@P1 AND HOLIDAY=@P2 ORDER BY HOLIDAY_SCHEDULE, HOLIDAY then look no further than the ABSW_WRK […]

GETDATE()-1 —

Ever noticed some “odd” predicates in execution plans using GETDATE()-n where “n” is the number of days? Things like: SOME_DATETIME_FIELD >= GETDATE()-‘1900-01-02 00:00:00.000’ It may look a bit odd but it does make sense where you think about the data type precedence rules in SQL Server. In this case, as GETDATE() is a DATETIME, there […]

List of Alphabetic Values in SQL Server —

I really like this use of the undocumented but widely used master.dbo.spt_values table to give a list of characters e.g. a-z or A-Z: SELECT CHAR(NUMBER) FROM master.dbo.spt_values WHERE NUMBER BETWEEN 97 AND 122 — Lowercase a-z –WHERE number between 65 and 90 — Uppercase A-ZSELECT CHAR(number) FROM master.dbo.spt_values WHERE number between 97 and 122 — […]