#SQLSERVERLearning : Export Table data as Insert statement in SQL Server.

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 for any Oracle guy can be access here.

With Oracle, SQL Developer and Toad console provide an option to export Table data from Result set itself.
With SQL Server, we would need to follow series of steps to export table as Insert statement.

Lets Iterate through steps to Export table data in SQL Server. (Tested on SQL Server 2012)
For demonstration purpose, we would take “AdventureWorks2012” database as sample data-set.

Step 1:Use Generate Scripts Task for exporting table data.
step1_export_table

Step 2:Select Database object to export
We can select multiple object for export as insert scripts from table or view.
step2_export_table

Step 3:Select Option on how to Save Export Insert Script.
step3_export_table

Step 4:Select Export Type for database object.
Click on Advanced option to change default export properties.
By default only Schema creation script can be exported, if data is also need we can select “Schema and Data Option”.
If only Data is needed, we can select only data options.
step4_export_table

Step 5:Export Summary.
Check and Validate option chosen for export, before proceeding further.
step5_export_table

Step 6:Export Execution Status.
step6_export_table

Step 7:Final Export Script.
step7_export_table

Using above steps, we can export complete set of records for a table.
But in case we need to export only specific set of records from a tables, not sure whether we have any option provided by SSMS.

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

#SQLSERVERLearning : Column Data Type Conversion in Oracle and SQL Server.

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 for any Oracle guy can be access here.

Ideally converting data type after having underlying data on table is tedious.It required special consideration(Index/Change Code/Conversion) to avoid any failure or run-time exceptions.
Once Schema is design , data getting stored in Column need to adhere its type.

Only Number can be stored in NUMBER datatype and so on.
We can say it is a datatype constraint getting enforce for a column, it define type of data we would be storing as strong handshake.

Let start with an example in oracle,
We will create a table with a column as Varchar2 datatype and try to modify it to Number.

DROP TABLE TEST123;

CREATE TABLE TEST123
(COL1 VARCHAR(10) , COL2 INTEGER);

INSERT INTO TEST123 VALUES ('11', 112);
INSERT INTO TEST123 VALUES ('12', 112);
INSERT INTO TEST123 VALUES ('1343', 112);
INSERT INTO TEST123 VALUES ('-1133', 112);

we are storing number in varchar data type.
Also note all data currently in Col1 is also a Legal Number, hence conversion should not be a problem.
We should not get any data type mismatch for same.

ALTER TABLE TAB_TEST123 MODIFY COL1 NUMBER(10,0); -- modify column to be NUMBER

SQL Error: ORA-01439: column to be modified must be empty to change datatype
01439. 00000 - "column to be modified must be empty to change datatype"


Oracle does not allow to typecast column data type to a Number, though all data was valid Number.
For data type modification column should be empty, it should not have any data.

We have many approach for data type modification, but personally prefer adding Virtual column with desired data type.

ALTER TABLE TEST123 ADD (COL1_NUM AS (TO_NUMBER(COL1))); --adding virtual column
Table TEST123 altered.

It will add one more column with data type as NUMBER and whose values is getting source from Col1.

DESC TEST123;

Name Null Type
-------- ---- ------------
COL1           VARCHAR2(10)
COL2          NUMBER(38)
COL1_NUM NUMBER

Using Virtual Column we would not need to change most of logic or index structure and many more constraint.

Let see how we can achieve same in SQL Server.we will be using similar table as created for Oracle.
Let try to convert same to Integer datatype

DROP TABLE TEST123;

CREATE TABLE TEST123
(COL1 VARCHAR(10) , COL2 INTEGER);

INSERT INTO TEST123 VALUES ('11', 112);
INSERT INTO TEST123 VALUES ('12', 112);
INSERT INTO TEST123 VALUES ('1343', 112);
INSERT INTO TEST123 VALUES ('-1133.12', 112); -- Added a Decimal Values

ALTER TABLE TEST123 ALTER COLUMN COL1 INT;

SQL Server will try to modify data type for column and will perform implicit check on column data for exact data type match.
In current data set for value -1133.12, it can’t be cast to int datatype.
It will give below exception.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-1133.12' to data type int.
The statement has been terminated.

As compare to oracle, SQL server will cast data type of column though it has some existing data.

Conversion to Float will be successful.
ALTER TABLE TEST123 ALTER COLUMN COL1 FLOAT;

TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
------------- ------------------------ ----------
TEST123 COL1 6 float
TEST123 COL2 4 int

Posted in Oracle Internal, SQL Server Learning | Tagged , , , | Leave a comment

Interesting New WITH Clause Feature in Oracle 12c.

#TGIF
In Oracle 12c, WITH clause is capable to have PL-SQL type Function or procedure inline with underlying SQL.
Function or Procedural within WITH clause is scope till SQL Execution lifetime and cease to exist after execution completion.

Having Function as part of WITH clause has it owns advantages, some as below.

1. Inline function within SQL reduces traditional Context Switch between SQL Engine and Pl-SQL Engine.
2. Computational function specific to a SQL can be inline instead of being an object in database Schema.
3. For Read only database, we can have Function inline with SQL.
4. WITH clause help SQL to use programming logic as with PLSQL (Pls. Note := it has some limitations.)
5. We can test a function without creating it as part of database schema.
6. Procedural or Function within WITH clause get priority over objects defined with same name in database schema.

Lets start with some example around New WITH clause.
We will create function as part of WITH clause and use same in underlying SQL.

WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 + 10;
END;
FUNCTION FN_ADD_11(OPERAND1 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 + 11;
END;
SELECT FN_ADD_10(1) ADD_10,FN_ADD_11(1) ADD_11 FROM DUAL;

ADD_10 ADD_11
———- ———-
11               12

Now lets evaluate different scenario and get deep understanding of WITH Clause.
Demonstrate SQL were not working on my VM SQL Developer version (Version 4.1.1.19), So for demonstrate purpose let use Oracle Live SQL.
Oracle Live SQL :: Nice online console to play around SQL with Oracle latest version.

1. WITH_PLSQL Hint.
If With clause has a function/procedure and it is not the Top level declaration then SQL fails.

with_plsql

Not Sure, why getting Invalid Statement while running same in Oracle Live SQL.please ignore same.

SELECT * FROM ( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10; END; SELECT FN_ADD_10(2) FROM DUAL);

SQL will failed with below Error.
ORA-32034: unsupported use of WITH clause

To avoid such error, use WITH_PLSQL hint.
assuming it is some sort of compiler directive to tell oracle, WITH clause is not top level declaration but part of SQL with function.

SELECT /*+ WITH_PLSQL*/* FROM ( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10; END; SELECT FN_ADD_10(2) FROM DUAL)


output :
FN_ADD_10(2)
----------
12

2.Priority over function defined in Schema
Creating function with same name “FN_ADD_10”, but subtracting 10 from input variable.

CREATE OR REPLACE FUNCTION FN_ADD_10(
OPERAND1 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 - 10;
END;

within SQL will use same Name function as part of WITH clause.

SELECT
/*+ WITH_PLSQL*/
ADD_10 ,
FN_ADD_10(2)
FROM
( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10;
END;
SELECT FN_ADD_10(2) AS ADD_10 FROM DUAL
)

Function as part of WITH clause is consider over schema level functions.
we can use similar method for any testing before implementing same at schema level.

with_plsql_1

3.Context Switch Performance
As function defined is inline it give advantage over context switch betn SQL and PLSQL engine.

SELECT SYSTIMESTAMP FROM DUAL;
DECLARE
L_CURSOR SYS_REFCURSOR;
TYPE T_TAB IS TABLE OF NUMBER;
L_TAB T_TAB;
BEGIN
DBMS_OUTPUT.PUT_LINE('WITHOUT WITH CLAUSE');
OPEN L_CURSOR FOR 'SELECT FN_ADD_DD(ROWNUM) COL1 FROM DUAL CONNECT BY ROWNUM < 1000000';
FETCH L_CURSOR BULK COLLECT INTO L_TAB;

CLOSE L_CURSOR;
END;
SELECT SYSTIMESTAMP FROM DUAL;
DECLARE
L_CURSOR SYS_REFCURSOR;
TYPE T_TAB IS TABLE OF NUMBER;
L_TAB T_TAB;
BEGIN
DBMS_OUTPUT.PUT_LINE(' WITH CLAUSE');
OPEN L_CURSOR FOR 'WITH FUNCTION FN_ADD_DD(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN (OPERAND1 - TO_NUMBER(TO_CHAR(SYSDATE , ''DD''))); END; SELECT FN_ADD_DD(ROWNUM) COL1 FROM DUAL CONNECT BY ROWNUM < 1000000';
FETCH L_CURSOR BULK COLLECT INTO L_TAB;

CLOSE L_CURSOR;
END;
SELECT SYSTIMESTAMP FROM DUAL;

with_plsql_2

Traditional function took roughly 11 sec and with inline function it took 5 sec.
With advantages, it also has many restriction and limitations.it is always wise to test and test and test before making any permanent changes.

Posted in Oracle 12c New Features | Tagged , , , , , , | Leave a comment

MIN , MAX and INDEX Access in Oracle.

All Entries in Index are stored as sorted either in ascending or descending order.
If we need MIN or MAX for any column being indexed, its should be easier for oracle to fetch same as both would be at index end points.

Lets understand it better and how it is access to end point within index is implemented with an example.

CREATE TABLE IDX_MIN_MAX
AS
SELECT ROWNUM RN , 'EATSPACE' COL1 FROM DUAL CONNECT BY ROWNUM < 1000000;

CREATE INDEX IDX_RN ON IDX_MIN_MAX(RN);

We have an index created on RN column, let now query to get MIN or MAX in same statement and check underlying execution plan.

SELECT MIN(RN),MAX(RN) FROM IDX_MIN_MAX;

min_max_index_1

Table full scan is perform to read MIN and MAX data and total memory buffer access to get desired data is 2628.
As we are querying MIN and MAX in same statement, oracle is not able to utilize Index data though it is sorted and required data that are at Extreme ends.
If we need Extreme end point for any indexed columns i.e. MIN and MAX for a indexed column, querying it within same SQL statement scope will impact performance.

Oracle provide “INDEX FULL SCAN (MIN/MAX)” to read extreme end point of indexed data.
It only read extreme ends point of index either MIN or MAX and procure desired result for us.

Lets Rewrite above statement with segregation of MIN and MAX in two different SQL statement scope.

SET AUTOTRACE ON;
SELECT (SELECT MIN(RN) FROM IDX_MIN_MAX) MIN_RN ,
(SELECT MAX(RN) FROM IDX_MIN_MAX) MAX_RN FROM DUAL;

we are querying MIN and MAX in two different SQL statement scope.
lets check underlying execution plan for same.

min_max_index_2

Elapsed time for SQL execution is minimal and total memory buffer access to get desired output reduced to only 3.

#Learning,
If we need indexed column extreme end points, query both in different SQL statement scope to take advantage of INDEX FULL SCAN (MIN/MAX) access path.
Also if we frequently querying MIN or MAX for a column then having an index on same would give good performance gain.

Posted in Oracle Internal, Oracle Performance | Tagged , , , , | Leave a comment

#SQLSERVERLearning : String Concatenation for NULL and Empty String in Oracle and SQL Server

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 for any Oracle guy can be access here.

Most of times during our day to day development, we work with Concatenation of string data type in database.
Its important to understand how it works with Empty String ” and NULL while concatenating in Oracle and SQL Server.

Very interesting blog with Quiz on Aggregation and Concat with Empty String and NULL and help me to explore difference between NULL and Empty String between Oracle and SQL Server.
Click to Explore

Lets first start with Oracle and try to understand how it treat NULL and Empty String.

ORACLE ::
WITH DATA AS
(
SELECT 'A' AS COL1 FROM DUAL UNION ALL
SELECT NULL AS COL1 FROM DUAL UNION ALL
SELECT '' AS COL1 FROM DUAL
)
SELECT * FROM DATA;

concat_blog

As we can see for Empty String also Oracle project it as NULL.
Oracle Treat Empty String as NULL, So ideally in oracle Empty String and NULL are treated Same.!

With NULL we are aware, NULL with any other expression force output to be NULL.
SELECT NULL + 1 , NULL -1 , NULL *1 , NULL /2 FROM DUAL;
Output for each operator will be NULL.

But it differ on how it is treated with in Concat operations in oracle.With concate in oracle, NULL does not cause output to be NULL.
|| operator will project operand that is not null, concat with NULL or Empty String will not cause make it NULL.

SELECT 'A' || '1' , 'A' || '' , 'A' || NULL , NULL || '' FROM DUAL
'A'||'1' 'A'||'' 'A'||NULL NULL||''
-------- ------- --------- --------
A1 A A NULL

With Concat in oracle, output will be NULL only when all operand is NULL or empty string.
As a best practise for concat with NULL, it is always advisory to use NVL function to replace NULL with Empty String ”.

SQL SERVER
In SQL Server NULL and Empty String are treated different.

sql_concat

As we can see in output projected, Empty Sring is not displayed as NULL.
It is just Empty.

Let see how Concat(+) behave in SQL Server, when we have Empty String or NULL as operand in
sql_concat-1jpg

Observations in SQL Server:
1. Concat with any NULL operand is always NULL.
2. Concat of all Empty String Operand is Empty String.
2. Concat with Empty String ” is NON Empty String operand.

So Concat with NULL or Empty String differ a lot in both database.
Hope information share was valuable, please share with others.

Posted in Oracle Internal, SQL Server Learning | Tagged , , , , , , | 2 Comments

#SQLSERVERLearning : CASE Statement Insight

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 for any Oracle guy can be access here.

#TGIF, had decided to publish at least one blog weekly and preferably on Friday!

Today will post about CASE statement in SQL server and how it differ with comparison in Oracle.
CASE is widely used for expression comparison with varying WHEN conditions and desired THEN for respective conditions.
IF nothing match we can use a default ELSE expression.

CASE help to compare different type of condition other then equality as compared to DECODE and get us desire output.
Whenever we compare any expression, it’s key factor to manage data type of operator for comparision.
Today will explore CASE expression with different data type and understand how it’s behave in Oracle and SQL server.

Lets start with below CASE statement,

SELECT CASE WHEN 1=1 THEN 1
WHEN '2' = 2 THEN '2'
WHEN GETDATE() = GETDATE() THEN GETDATE()
ELSE NULL
END

In above case statement, you will observe below thing

1. Data type of each expression in WHEN is different. (NUMBER – VARCHAR – DATETIME)
2. Return type for each expression is different.
3. It will match only for first WHEN expression, remaining WHEN expression and not reachable.

Now let see what the output for same.
Output :: 1900-01-02 00:00:00.000

Ideally at first thought, we would think output would be 1, as it match for first WHEN expression or some exception for data type mismatch due to different result expression.
But output is of DATE datatype and value is somewhat as of now irrelevant.

Now let route to Oracle Database and see how it behave for such different data type expression.

SELECT CASE WHEN 1=1 THEN 1
WHEN '2' = '2' THEN '2'
WHEN SYSDATE = SYSDATE THEN SYSDATE
ELSE NULL
END FROM DUAL;
Output :ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

In Oracle it is mandate for each result expression to be for same data type, implicit conversion is not impose.

Let understand, how SQL server behave with varying data type in CASE Expression.

1. Data type for Return expression is decided based on “Data Type Precedence”.
When we have expression that combine different data type, as in our case NUMBER , VARCHAR and DATE.It convert data type of lower precedence to higher precedence IFF valid.
For CASE example mentioned, expected return data type will be “DATE”

So in CASE statement examples, final output base on match was after implicit conversion of first match result expression (1) to DATE.
i.e. SELECT CAST(1 as DATETIME)
Output :: 1900-01-02 00:00:00.000

2.
Now let see what would be happen when second expression is match.
SELECT CASE WHEN 1!=1 THEN 1
WHEN '2' = 2 THEN '2'
WHEN GETDATE() = GETDATE() THEN GETDATE()
ELSE NULL
END
Output :: Conversion failed when converting date and/or time from character string.

Now we got an exception as we are trying to convert match result expression character data type to Date, which is not valid and hence not permitted.

Ideally it is always good practice to have similar datatype for each condition within any expression.
It will avoid any conversion failure at run-time.

Please share any feedback if any.!

Posted in 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.
Please note, these parameter is at System level, hence its impact is across session i.e. System level.

Lets suppose, fixed date is not use and we have current real-time date setup as below.
SELECT SYSDATE , CURRENT_DATE , SYSTIMESTAMP FROM DUAL;

SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ ---------------------------------------
12-DEC-2016 12-DEC-2016 12-12-16 12:58:09.295000000 PM +05:30

We will change date to backdated one, for instance 05-Jan-2015.

ALTER SYSTEM SET FIXED_DATE='05-JAN-2015';

Using FIXED_DATE parameter, we had set reset Sysdate to 05-Jan-2015.

Now let re run above query to check Sysdate, Current Date and Systimestamp information.

SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ ---------------------------------------
05-JAN-2015 12-DEC-2016 12-12-16 01:02:56.743000000 PM +05:30

Only SYSDATE change using FIXED_DATE parameter, CURRENT_DATE and SYSTIMESTAMP shows AS OF Date/Time information.
It it important to understand, it will impact only sysdate at database level.

Now let reset date to a constant date with time component.

ALTER SYSTEM SET FIXED_DATE='05-JAN-2015 10:12:11';

If NLS_DATE_FORMAT is not set properly, it would give below exceptions.

SQL Error: ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02078: invalid setting for ALTER SYSTEM FIXED_DATE
02097. 00000 - "parameter cannot be modified because specified value is invalid"

Reset NLS_DATE_FORMAT and let try again.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SYSTEM SET FIXED_DATE='05-JAN-2015 10:12:11';

Now it will successful and now time component is also FIXED.
Let recheck Different date function values.

SYSDATE CURRENT_DATE SYSTIMESTAMP
---------------------- ---------------------- ---------------------------------------
05-JAN-2015 10:12:11 12-DEC-2016 13:10:00 12-12-16 01:10:00.419000000 PM +05:30

Now we have SYSDATE with Time component as FIXED, it wont change!.
Time component will be FIXED, it wont changed!

CURRENT_DATE and SYSTIMESTAMP has no impact with FIXED_DATE Parameter.

When we are done with our testing, we need to reset FIXED_DATE to get actual real time date information.
We can reset using “NONE”

ALTER SYSTEM SET FIXED_DATE=NONE;

SYSDATE CURRENT_DATE SYSTIMESTAMP
---------------------- ---------------------- ---------------------------------------
12-DEC-2016 13:13:58 12-DEC-2016 13:13:58 12-12-16 01:13:58.636000000 PM +05:30

It reset to real time date informations.

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 creating a table
–> adding a row
–> Drop a table that does not exists

In Database session 2 :
–> Query newly created table in session 1 and check row count.

Database Session 1 :

DROP TABLE TAB;
CREATE TABLE TAB
AS
SELECT 1 COL1 FROM DUAL;
INSERT INTO TAB VALUES(2); --inserting a values into table.


DROP TABLE TABLE_DOES_NOT_EXITS; --Dropping a table that does not exists in database.

We are dropping a table which does not exists in Database. Hence Drop command would failed with below exception.
SQL Error: ORA-00942: table or view does not exist

Database Session 2 :
In Another session we are trying to query table TAB and check total number of rows.
select count(*) from TAB;
Please note we have not perform any commit in Database Session 1.

So the questions is When we Query Select count(*) from TAB; in Database Session 2 what will be row count.

Please update your answer within below Polls.

Will update my answer on same with a reason.

Thanks a lot for voting, please find Voting results as of today(12th Dec 2016).We can also View result in poll itself.
poll_result

Correct answer for SQL concept questions is “TWO”.

Let reiterate with steps in DB Session 1, to understand it better.

Step 1 :
DROP TABLE TAB;
Table does not exists, hence it would give exceptions.

Step 2 :
CREATE TABLE TAB
AS
SELECT 1 COL1 FROM DUAL;

We are using CTAS (Create table as ) for creating new table TAB with a row.
Create table is Auto Commit statement, hence table will have one row and it persist for each and every session for database.
So after these statement, every session in database will show record count as One.

Step 3 :
INSERT INTO TAB VALUES(2);

We are inserting a record in newly created table and no Commit statement is executed.
Hence newly inserted rows won’t be visible in any other session other then current one.

Step 4 :
DROP TABLE TABLE_DOES_NOT_EXITS;
These steps is very Tricky!

We all know it will failed as table we are trying to drop does not exists in DB.
But Statement we are trying to execute is an Data Definition Language (DDL – CREATE/ALTER/DROP/TRUNCATE).
For any DDL in oracle, We perform “Implicit Two Commit“. One before executing Statement and one after executing statement.
So though Statement had failed, but First Implicit commit before executing failed statement cause data to get commit within session.
Hence though we did not have any explicit commit in Database session 1, but first implicit commit of DDL cause data to Commit and visible for each and every session in database.

Key Learning :
DDL operate as Two phase commit statement, one before executing or parsing other after successful execution.

Hope it was interesting learning, please share it with others.!

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, we need multiplication of a column data within a partition column scope and increase as partition scope increase.
As Date increase multiplier scope also increase.
To understand it better, please check below expected output

ITEM DATE_TIME MULTIPLER EXPECTED_OUTPUT
----- --------- ---------- -----------------
ITEM1 14-NOV-16 1 1
ITEM1 21-NOV-16 2 2 --> 1*2
ITEM1 28-NOV-16 3 6 --> 1*2*3
ITEM1 05-DEC-16 4 24 --> 1*2*3*4
ITEM1 12-DEC-16 5 120 --> 1*2*3*4*5
ITEM1 19-DEC-16 6 720 --> 1*2*3*4*5*6
ITEM1 26-DEC-16 7 5040 --> 1*2*3*4*5*6*7
ITEM1 02-JAN-17 8 40320 --> 1*2*3*4*5*6*7*8

ITEM2 14-NOV-16 9 9
ITEM2 21-NOV-16 10 90
ITEM2 28-NOV-16 11 990
ITEM2 05-DEC-16 12 11880
ITEM2 12-DEC-16 13 154440
ITEM2 19-DEC-16 14 2162160
ITEM2 26-DEC-16 15 32432400
ITEM2 02-JAN-17 16 518918400

As Date increase for a Item, Multiplication scope also increase based on ascending date.
For E.g. for Item1 with date 02-JAN-2017, expected output should be multiplication of all rows data within Item1 scope.

Before moving towards solution to achieve same, lets explore some mathematical function Oracle or SQL Server provides.

1. Exponential function : EXP
It return e raised to Nth Power, i.e. “e^N”

SELECT 'Exponential Raised to Natural log e (2.71828183)' AS EXPLANATION , ROUND(EXP(2),2) FROM DUAL;


EXPLANATION ROUND(EXP(2),2)
----------------------------------------------------------------------
Exponential Raised to Natural log e (2.71828183) 7.39

2. Natural Log function : (Oracle :: LN // SQL SERVER : LOG)
It return natural log of input values.

SELECT 'Natural log with base as e (2.71828183) ' AS EXPLANATION , ROUND(LN(7.39),2) FROM DUAL;


EXPLANATION ROUND(LN(7.39),2)
---------------------------------------- ------------------
Natural log with base as e (2.71828183) 2

Log is exponential factor with base as e.
i.e. e^2 := 7.39
Log of 7.39 with base as e := 2.

Reason to explore above mathematical function is to get our multiplication factor using log and exp formula.
e^x * e^y := e^(x+y)
i.e. log(x*y) := log(x) + log(y) (natural log)

example ::
for 10*10 we will use logarithm and exponential as below.
log(10) + log (10) := 2.302 + 2.302 := 4.604
later exp(4.604) := (2.71828183)^4.604 := 100 approximate.

For more information, please refer below link.

Multiplying Numbers Using Log Tables

After a bit of mathematics, lets see database code to achieve multiplication of data.

SELECT A.*,
ROUND(EXP(SUM(LN(ABS(A.MULTIPLER))) OVER (PARTITION BY ITEMNAME ORDER BY DATE_TIME)),2) EXPECTED_OUTPUT
FROM MULTI_ANALYTICAL A;

Basically we are using SUM of LOG and later using EXP with rounding function.

Using SUM of LN and later applying EXP, our expected output is multiplication of data.
And its beauty increase with help of analytical function.

multi

To explore more on Analytical function check here. and here

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

#SQLSERVERLearning : Identity Column Insight.

All my blogs with #SQLSERVERLearning are intended to cover all stuff identified as the 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 for any Oracle guy can be access #SQLSERVER Learning..

Identity column helps to have a column with auto increment counter based on SEED/StartWith and Increment value on each request.
Identity column was part of SQL Server since 2008 release and a genuine need was for oracle developer to have same.
Finally in oracle 12c release, Identity column and sequence as default were part of new Feature.

Ideally comparison of Identity column behaviour in oracle and SQL server can be debatable, as both have different implementation logic.
But for understanding point of views, lets walk through Identity column implementations and its behaviour on different actions.

Lets start with field work by understanding how identity works in SQL Server.

CREATE TABLE TAB_IDENTITY
(
EMP_ID INT IDENTITY(1,1), --Identity column that will start with 1 and always increment by 1.
EMP_NAME VARCHAR(1000),
DATE_JOIN DATE DEFAULT GETDATE()
);
check table propery having identity column.
SP_HELP 'TAB_IDENTITY';

sql_identity

Emp_id is identity column having Not Null constraint implicitly added by SQL Server.
By Specifying not NULL and Identity, we would need to always adhere to column value from identity itself, we can’t insert custom values.
Below insert with insert EMP_ID data as per identity properties.
Insert Into Tab_Identity(emp_name,Date_Join) Values('ENAME1', GETDATE()-4);

–> Custom Data in Identity column.
when we try to explicitly provide a value for emp_id, it would give an exception.

Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(2,'ENAME2', GETDATE()-3);
Error message ::
Cannot insert explicit value for identity column in table 'tab_identity' when IDENTITY_INSERT is set to OFF.

Now lets try to Set IDENTITY_INSERT ON;
set IDENTITY_INSERT identitydb.dbo.Tab_Identity ON;

It will allows insertion of custom identity values to be inserted.

Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(2,'ENAME2', GETDATE()-3);
Insert Into Tab_Identity(emp_id, Emp_Name,Date_Join) Values(10,'ENAME2', GETDATE()-3);

By adding custom values greater then Current Seed, i.e by updating Emp_id from 2 to 10 we also updated identity SEED values.
SQL Server provide DBCC CHECKIDENT to check identity column property within a table.

DBCC CHECKIDENT ('TAB_IDENTITY');
Checking identity information: current identity value '10', current column value '10'.

–>Truncate impact on identity
let see what happen when we try to truncate table having identity column.

TRUNCATE TABLE TAB_IDENTITY;
DBCC CHECKIDENT ('TAB_IDENTITY');
Checking identity information: current identity value 'NULL', current column value 'NULL'.

Truncate cause reset of identity column and it will altogether start with initial seed.

–>Drop impact on identity
Reason to include was its behavious in oracle when objects still exists in Recycle bin.

DROP TABLE TAB_IDENTITY;
DBCC CHECKIDENT ('TAB_IDENTITY');
output::
Cannot find a table or object with the name "TAB_IDENTITY". Check the system catalog.

As table is drop, corresponding identity columns is also drop.

–>Number of identity column in a table
Any table in oracle or be in SQL server can have only one identity column.

If in case we want any other column to get increment based on some SEED and Increment factor, we can use Sequence as Default.
CREATE SEQUENCE SEQ_IDENTITY
START WITH 1
INCREMENT BY 1;

CREATE TABLE TAB_IDENTITY_SEQ
(
EMP_ID INT IDENTITY(1,1),
ROW_NO INT DEFAULT NEXT VALUE FOR SEQ_IDENTITY,
);

CONSTRAINT_TYPE CONSTRAINT_KEYS
--------------------------- -------------------------------
DEFAULT ON COLUMN ROW_NO (NEXT VALUE FOR [SEQ_IDENTITY])

With Oracle 12c, we can also have a custom sequence next val as default for column values.

–>Insert for a table having only Identity columns
Suppose a Scenario in which we have a table with only 1 column which is identity column.
With traditional insert syntax, we can’t insert values within such tables.

CREATE TABLE T_IDENTITY
(
EMP_ID INT IDENTITY(1,1)
);

INSERT T_IDENTITY DEFAULT VALUES; -- using default values we can cause identity value to get populated.
(1 ROW(S) AFFECTED)

DBCC CHECKIDENT ('T_IDENTITY');
CHECKING IDENTITY INFORMATION: CURRENT IDENTITY VALUE '1', CURRENT COLUMN VALUE '1'.

–>Change SEED value for a Identity Column
DBCC CHECKIDENT provide an option to change Seed value for identity column.
With identity column we can only change current SEED value but Increment factor once set can’t be change.

Lets take above table for current example.
DBCC CHECKIDENT ('T_IDENTITY', RESEED , -10000);
INSERT T_IDENTITY DEFAULT VALUES;
SELECT * FROM T_IDENTITY

EMP_ID
-----------
1
-9999

Posted in SQL Server Learning | Tagged , , , , | 1 Comment