SOURCE CODE ATTRIBUTION: Before I proceed, I want to give credit to the original procedure by sfrejofsky based on which I created this procedure (mostly improvements). The source of this procedure is https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query I had a need to do dynamic PIVOTing for one of my projects and instead of reinventing the wheel, I used the … Continue reading SQL Server – Dynamically PIVOT ANY Column On ANY Table in ANY Database For ANY Aggregation With ANY Condition
Sometimes we carry knowledge around that can hurt when doing similar things especially when dealing with the different programming languages and constructs. Today, let us see how Oracle’s ROWNUM is different from (as in, almost unrelated) SQL Server’s TOP. SQL Server - TOP: Basics Let us take the TOP function in SQL Server. It is … Continue reading Oracle ROWNUM vs. SQL Server TOP – Differences & How To Do Pagination In Both
NULL's will be eliminated! When doing a count() operation, it helps to understand what is counted and what is not. Count can be done in multiple ways COUNT(*) COUNT(1) COUNT([Column_Name]) COUNT(DISTNCT [Column_Name]) With and without GROUP BY While it is straight-forward to get a count, here are a couple of things to note NULL values … Continue reading Oracle & SQL Server: COUNT(*) vs. COUNT(1) vs. COUNT(ColumnName) – Differences
If you are primarily a SQL Server developer and you are moving to Oracle, this is one of the nastiest surprises in store for you. You are so used to rolling back everything including DDL in SQL Server transactions but in Oracle, DDL inside transactions act as COMMIT. Fear not though, there are a ton … Continue reading Oracle Gotcha For The SQL Server Developer – DDL’s COMMIT Transactions Implicitly!
Dynamic SQL – SQL that is constructed by putting together strings to form executable code that gets parsed for the first time by the compiler at run-time. Justification for Dynamic SQL? There should be relatively few instances where dynamic SQL is the only option. Generally, regular SQL whose syntax can be checked by the compiler … Continue reading SQL Server Dynamic SQL – Use Bind Variables