Category Archives: Helper SQL

SQL SERVER# : Get last possible time for a DAY.

During one of SQL requirement, was trying to form Range of Years for executing a DML in small batches as per YEAR. But due to missing last possible end time, we were not able to manipulate some records. For instance … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , , , | Leave a comment

SQL Server : Value Constructor Insight.

We all must be aware of VALUE Clause for Inserting values specified, in current blob we will explore different ways other then Insert were we can have use VALUE as handy.! 1. Generating Dummy Data! we can use Value to … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , | Leave a comment

SQL Server : Get Numeric part from a varchar/string.

Found question to fetch only numeric information from a string/varchar in some of blogs, so thought let try and share my attempt. Implement logic: 1. Using Recursive With clause, segregated each and every character as separate row. 2. Using Regex … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , , | Leave a comment

Date Generator in SQL Server using Recursive With Clause.

During one of performance analysis, identified lofty insert statements populating static tables with Date information. As part of one of functionality, we were generating dates and applying necessary function on it within date range specified. As part of performance improvement, … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , , , | Leave a comment

#SQLSERVERLearning : String Padding in Sql Server (LPAD/RPAD)

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server. Hopefully it might be helpful for Oracle guy also working in SQL Server world. All finding or Observations with SQL Server … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , , , , , , | Leave a comment

Change Date in Oracle using FIXED_DATE

In our previous project for testing purpose, we required to change Sysdate in database. Changing date is useful for many functional testing to go back date and perform functional compliance operations. Oracle provide FIXED_DATE, system level parameter for changing date. … Continue reading

Rate this:

Posted in Helper SQL | Tagged , , , | Leave a comment

Interesting Database SQL Concept Question, Give it a try??

#TGIF Today’s will try to walk you through a Database concept questions and hopefully you will learn something new. (Obviously if you are not aware of it.!) Suppose we have below scenarios, In Database Session 1 : –> we are … Continue reading

Rate this:

Posted in Helper SQL, Oracle Internal | Tagged , , , , , | 4 Comments

Multiplication across rows and Analytical Functions.

Oracle or SQL Server provide Aggregation functions for MIN/MAX/COUNT/SUM, but suppose a scenario when we want multiplication of rows across a partition or complete table. Database does not provide any buildin function to achieve same. Suppose we have below expectations, … Continue reading

Rate this:

Posted in Helper SQL, Oracle Internal, SQL Server Learning | Tagged , , , , , | 1 Comment

SQL Developer : Add Custom SQL Query as Template.

Daily for SQL plan reading and analysis, using below command quite often from SQL Developer Tool. explain plan for statement_id = ”; select * from gv$sql where sql_text like ‘%%’; select * from table(dbmx_xplan.display); select * from table(dbmx_xplan.display_cursor(”,null,’ALLSTATS_LAST’)); Wouldn’t it … Continue reading

Rate this:

Posted in Helper SQL | Tagged , , | Leave a comment

Analytical Function at Rescue

With Analytical function in oracle, we can leverage very powerful data projections for complex data requirement. It exhibit great tool to play around group sets with in a window and achieve desired outcome. Will walk¬†¬†through one of requirement which application … Continue reading

Rate this:

Posted in Helper SQL | Tagged , , | 1 Comment