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
SELECT statement gotchas in Oracle and Sql Server
Here are some issues which may catch you out from time to time. In each case Sql Server and Oracle, or Oracle Sql and PL/Sql, or versions of Oracle, work differently. Continue reading SELECT statement gotchas in Oracle and Sql Server
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
Apportionment calculations in Sql Server and Oracle
As you probably realise, this post deals with apportionment in finance data warehouses, not the U.S. Constitutional Apportionment Problem, interesting though that topic is. Continue reading Apportionment calculations in Sql Server and Oracle
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
Planning the use of Oracle DML Error Logging to make an ETL Batch more reliable
An ETL batch should not fail because of minor source data errors. In data warehousing it is assumed that there will be data quality issues. These should be dealt with. If they are not handled properly the batch may break on flaws which have no importance in reporting terms.
Oracle’s DML Error Logging feature has been around for a while and can prevent batch failures Continue reading Planning the use of Oracle DML Error Logging to make an ETL Batch more reliable
Holiday Support Rota
Christmas is not too far away now, and I would like to dedicate this haiku to any of my Oracle developer and DBA friends working the holiday period, and who may be from their beds “untimely ripped” to fix something or other: Continue reading Holiday Support Rota
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
Dropping columns from large tables in Oracle
In a data warehouse you may have to drop columns on very large tables (try to avoid it if you can), and the size of the table may cause complications. The most obvious is the time it may take to do a straightforward ALTER TABLE DROP COLUMN statement. You probably don’t want to wait for an hour in the middle of your implementation for the DROP statement to execute. Continue reading Dropping columns from large tables in Oracle
blog.philipsymons.com 