Oracle ROWNUM vs. SQL Server TOP – Differences & How To Do Pagination In Both

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

Oracle & SQL Server: COUNT(*) vs. COUNT(1) vs. COUNT(ColumnName) – Differences

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

Oracle Gotcha For The SQL Server Developer – DDL’s COMMIT Transactions Implicitly!

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!

SQL Server Dynamic SQL – Use Bind Variables

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

Oracle/SQL Server – RANK, DENSE_RANK and ROW_NUMBER Analytic Functions – Basic Examples

Note: The illustrations on this post are done with Oracle. However, by making minor changes, you should be able to run it on SQL Server and the concepts remain the same. Please refer this post for changes needed. RANK, DENSE_RANK and ROW_NUMBER are three powerful analytic functions that every database developer should be thoroughly familiar … Continue reading Oracle/SQL Server – RANK, DENSE_RANK and ROW_NUMBER Analytic Functions – Basic Examples