Opinionated Software

… we have opinions about everything!

Archive for the ‘SQL Server’ 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 […]

PSTREENODE Clustered Index Change —

I noticed that in PT 8.55 (well at least in .08 and later) that the PeopleTools meta-data for the Clustered Index on PSTREENODE lost the “clustered” attribute. I’m not sure why this was changed but you should be aware that this change could impact your execution plans – especially if you use row level security. […]

Opening UTF-16LE files in Perl —

Placeholder for useful code snippet: open my $fh, ‘<:raw:perlio:encoding(UTF-16LE):crlf’, $filenameopen my $fh, ‘<:raw:perlio:encoding(UTF-16LE):crlf’, $filename which will convert CR/LF combinations to LF only. Alternatively, to keep them intact: open my $fh, ‘<:raw:perlio:encoding(UTF-16LE)’, $filenameopen my $fh, ‘<:raw:perlio:encoding(UTF-16LE)’, $filename Useful for reading Windows registry export files, SQL server log export files etc.

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 […]