#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.!

Advertisements
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

Oracle 12c : Default values on NULL.

In Oracle, DEFAULT provide an option to have any default expression when column data is not specified during data manipulations.
During Table creation or using Alter we can provide DEFAULT value for column.

While data manipulation we can use “default” keyword to use default value specified as part of table structure.

But suppose a scenario , when we want a default value for all NULL data getting inserted.
Pre 12c,no option was provided to have any Default value for NULL Data.

let’s walk through sample code to understand it better,

CREATE TABLE T_DEFAULT_ON_NULL
(
EMP_ID NUMBER ,
EMP_NAME VARCHAR2(1000),
DATE_JOIN DATE
);
Table T_DEFAULT_ON_NULL can have emp_id as null and currently for handling NULL data we are using NVL function.
To handle NULL data we use NVL function to replace NULL data with our expected default "-1".

INSERT INTO T_DEFAULT_ON_NULL(EMP_ID, EMP_NAME, DATE_JOIN) VALUES (NVL(NULL,-1), 'NAME1',SYSDATE-10);
INSERT INTO T_DEFAULT_ON_NULL (EMP_ID, EMP_NAME, DATE_JOIN) VALUES (0,'NAME2',SYSDATE-9);
INSERT INTO T_DEFAULT_ON_NULL VALUES (1, 'NAME3',SYSDATE-8);
INSERT INTO T_DEFAULT_ON_NULL VALUES (2, 'NAME4',SYSDATE-7);
INSERT INTO T_DEFAULT_ON_NULL VALUES (3, 'NAME5',SYSDATE-6);

With 12c Default on NULL option, we will alter table or implement on table creation to use it.

ALTER TABLE T_DEFAULT_ON_NULL
MODIFY (EMP_ID NUMBER DEFAULT ON NULL -1);

we can explicitly insert any data as needed and whenever column data is null it will get overwrite with default value -1.

All below insert statement are legit.

INSERT INTO T_DEFAULT_ON_NULL VALUES (DEFAULT, 'NAME1',SYSDATE-10); --using default keyword
INSERT INTO T_DEFAULT_ON_NULL VALUES (NULL, 'NAME1',SYSDATE-10);--using NULL
INSERT INTO T_DEFAULT_ON_NULL(EMP_NAME, DATE_JOIN) VALUES ('NAME1',SYSDATE-10);

If you can check our table creation scripts, for column emp_id we did not specify NULL or NOT NULL constraint.
With Default on NULL, Oracle explicitly enforced NOT NULL constraint as after default on null we won’t have any Nullable data.

In 12c new columns are added are data dictionary to check DEFAULT_ON_NULL property.

SELECT COLUMN_NAME , NULLABLE , DEFAULT_ON_NULL , DEFAULT_LENGTH, DATA_DEFAULT
FROM USER_TAB_COLS
WHERE TABLE_NAME LIKE 'T_DEFAULT_ON_NULL';

default_on_null

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

Aggregate Function , Distinct and NULL Values in Oracle.

While working on SQL Server with queries having aggregation got a warning message.
“Warning: Null value is eliminated by an aggregate or other SET operation.”

So thought let write up something on Aggregation and NULL Values for Tradition RDBMS for basic understanding.
Aggregation is key operations for any analytics or summarize information.
Our traditional RDBMS has NULL to mark any information Undefined or Unknown.

Both Aggregation and NULL are related in a way for different standard aggregation method oracle provides (MIN/MAX/COUNT/AVG/SUM).
Lets walk through a Query example and will discuss more on same.

WITH DATA1 AS
(
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 2 COL1 FROM DUAL
)
SELECT 'NON - DISTINCT' AS INFO, COUNT(*) "CNT_*", COUNT(1) CNT_ROW, COUNT(COL1) CNT_COLUMN , COUNT(DISTINCT COL1) CNT_DISTINCT,
COUNT(NVL2(COL1, NULL ,1)) CNT_NULL,
AVG(COL1) AVG, SUM(COL1) SUM, MIN(COL1) MIN , MAX(COL1) MAX FROM DATA1
UNION ALL
SELECT 'DISTINCT' AS INFO, COUNT(*), COUNT(DISTINCT 1) CNT_ROW, COUNT(COL1) CNT_COLUMN , COUNT(DISTINCT COL1) CNT_DISTINCT,
COUNT(NVL2(COL1, NULL ,1)) CNT_NULL,
AVG(DISTINCT COL1) AVG, SUM(DISTINCT COL1) SUM, MIN(DISTINCT COL1) MIN , MAX(DISTINCT COL1) MAX FROM DATA1
;

We have total 6 Rows, with 2 as NULL and 2 distinct values.
Let see output for different aggregation functions and understand it better.

Output ::
aggregate

Key Findings:
1. Count(*) and Count(1) (Or any constant expression like count(‘a’) count(23123)) are similar and are use to count total rows return by a SQL.
COUNT function without any table column as input expression are use to count total number of rows return by query.

2. Count (Column) and Count(Distinct Column)
Will Ignore NULL Values for counting.Output values will be based on count of all Non Null-able values within that column.

3. Count(Distinct *)
Distinct * is not allowed, it would give an Exception.
Distinct within aggregation only works for any constant or column expression.

4. Count(NULL) will always be 0.
Expression as NULL can’t be evaluated as it is always Not Defined and hence output for same would be always Zero.

WITH DATA1 AS
(
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 2 COL1 FROM DUAL
)
SELECT COUNT(NULL) FROM DATA1;
output :
COUNT(NULL)
-----------
0

5.COUNT(DISTINCT 1)
Distinct on any constant expression will always give output as 1, as we don’t have any scope for non distinct values.

6.AVG/SUM/MIN/MAX will ignore NULL values for any evaluations.
In Avg while consideration of denominator all NULL values are ignore.
So basically it is
AVG ( (SUM of all Non NULL Column data)/(Count of Column values) i.e. AVG(SUM(COL1)/COUNT(COL1))

7. AVG(DISTINCT COL1) and AVG(COL1)
When we add Distinct with column expression for AVG function, it turn out to be
AVG ( (SUM of all non null and distinct column data) / (distinct count) i.e. AVG(SUM(DISTINCT COL1)/COUNT(DISTINCT COL1))

Distinct is impose for both SUM and Count of denominator.

8. How to Count NULL for a column.
We can use below expression to count total number of nulls for a column.
COUNT(NVL2(COL1, NULL ,1))

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

#SQLSERVERLearning : Exception on Invalid/Incorrect Hints?

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 findings or Observations with SQL Server for any Oracle guy can be accessed here.

Ideally, don’t encourage the use of Hints in any database, provided we dint have any other alternative.
Hint helps us to play with execution plan for testing by influencing Optimizer to use, provided query options (Join method, Access path, Index usage, pushing predicate….).

In today’s blog will discuss, what happen when we specify incorrect/invalid hints in Oracle and SQL server.
Does database give any exception or warning, that provided hints are incorrect and cannot be enforced or it simply ignores it.

So what exactly Hint is? Whether its an option for Oracle Optimizer to consider or an order to follow.

The hint is a Command.!
If Hint is Valid, Authentic as per SQL Compliance, the Optimizer is bound to follow it.
But there are many cases when Hint can be Invalid.

Will talk through an example, when we would try to apply Invalid Hint and check how Oracle and SQL Server behave.

Let’s start with Oracle, we will use standard Emp and Dept tables for demo.
Before starting with the demo will like to talk a bit about Hash Join.

Hash join is only use to join two tables based on Equality expression.It can’t evaluate >= or <= or any other non equality expression.
hence if we try to push hash join for below query it will be invalid.

SELECT /*+ USE_HASH(DEPT)*/EMP.*,DEPT.*
FROM EMP , DEPT
WHERE EMP.DEPTNO <= DEPT.DEPTNO;

We are trying to join based on Expression other than equality and hence it would require either Nested loop or Merge join to evaluate queries.

For above SQL, Oracle uses below Plan.
-----------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59 |
| 1 | MERGE JOIN | | 59 |
| 2 | SORT JOIN | | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 7 |
| 4 | INDEX FULL SCAN | PK_DEPT | 7 |
|* 5 | SORT JOIN | | 14 |
| 6 | TABLE ACCESS FULL | EMP | 14 |
-----------------------------------------------------------------

In oracle, when we try to Run above statement, it would run successfully without giving any warning or errors.
We would need to cross verify whether hints are applied or not by checking underlying plan.
For Checking whether there was any issue to apply mention hint, we would need to enable 10053 trace and check Dumping hints section to verify whether any error was reported.

Let see how SQL Server behaves for the same case.

SELECT * FROM HUMANRESOURCES.DEPARTMENT D , HUMANRESOURCES.EMPLOYEE E
WHERE D.MODIFIEDDATE <= E.MODIFIEDDATE OPTION (HASH JOIN);

In SQL Server, we apply hint using OPTION Clause.
When we try to run above statement, we will get an exception message from the SQL Server Engine.

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

hint

SQL Server provides a way of throwing back some error, when we try to provide any Invalid hints.
It won’t execute statement with some other valid joins methods, instead try to Force mention hint.

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

#SQLSERVERLearning : Does Unique Index Stored NULL Values in Index?

All my blogs with #SQLSERVERLearning are intended to cover all stuff identified as difference between Oracle and SQL Server.

Hopefully it might be helpful for any Oracle guy also working with SQL Server.
All finding or Observations with SQL Server of mine can be find here.

In Oracle, Single Column index does not store NULL Values with in Index Structure.
Whenever we query based on column having null values with NULL condition’s (Specially for IS NULL, for IS NOT NULL it might fetch from index also.) it fetches through Table access, as index does not store NULL Values.
For more information on INDEX and NULL Values in Oracle, please check here.

Unique index in Oracle allows multiple NULL values getting stored with uniqueness getting imposed ONLY on Non Null values.
Ideally with DBMS core concepts, Null can’t be Comparable.
Unique index in oracle also does not enforce uniqueness on NULL data by comparing NULL with NULL, hence it allows multiple NULL by not storing NULL data within Index Structure for Unique Column.

lets walk through an example in Oracle, then will move to SQL Server part.

CREATE TABLE TAB_UNIQ_NULL(COL1 NUMBER , COL2 VARCHAR2(1));
CREATE UNIQUE INDEX UNQ_COL1_IDX ON TAB_UNIQ_NULL(COL1);

INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'A');
INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'B');
COMMIT;

Output:
1 rows inserted.
1 rows inserted.

As expected, though we had unique index created on Col1, it allows Multiple NULL values to get stored.

now try to fetch only col1 data based on IS NULL conditions, let check from where data would be fetch Index or Table level.

EXPLAIN PLAN FOR
SELECT COL1 FROM TAB_UNIQ_NULL WHERE COL1 IS NULL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 2 |
|* 1 | TABLE ACCESS FULL| TAB_UNIQ_NULL | 2 |
---------------------------------------------------

From Execution Plan we can say, in Oracle unique index does not store Null data and null data can only be fetched at table storage.
Ideally not pushing NULL in Index structure allow Oracle to push multiple NULL data for Unique index.

Now lets try same set of example for SQL Server and see how it behave for Unique and NULL data!.

CREATE TABLE TAB_UNIQ_NULL(COL1 INT , COL2 VARCHAR(1))
GO
CREATE UNIQUE INDEX UNQ_COL1_IDX ON TAB_UNIQ_NULL(COL1)
GO

INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'A')
INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'B')
GO

Msg 2601, Level 14, State 1, Line 15
Cannot insert duplicate key row in object 'dbo.tab_uniq_null' with unique index 'unq_col1_idx'. The duplicate key value is ("NULL").
The statement has been terminated.

First Insert statement got inserted successfully, above Exception was for Second Insert.

SQL Server actually compare NULL with NULL for Uniqueness.!
It only allows One NULL data due to Unique index on column.
Ideally for any Database design related to Unique index in SQL Server above behaviour need to be taken into considerations.

Now let see how exactly single null data is getting fetched that got successfully inserted within table.

SELECT * FROM TAB_UNIQ_NULL;


COL1 COL2
----------- ----
NULL A

Will try to fetch COL1 data based on NULL Condition and check underlying execution plan for query.
(Execution plan reading are the best way to learn internal processing within any Database)

SELECT COL1 FROM TAB_UNIQ_NULL WHERE COL1 IS NULL;

Execution plan for same,

blog1

Index SEEK Access path tell us our data is fetch from Index itself and not at table level through any key look up.(Yet to learn a lot on SQL Server Query tuning and plan reading :-|)
Within SQL Server for Unique index, First NULL was store at Index level or got push-in index structure.
hence any further null data are not allowed with Unique Column.

SQL Server provide Filter Index were we can filter data getting push into index.
For Uniqueness and allowing multiple NULL getting inserted we would need to restructure our DDL as below.

Let Restructure our table with Filter Unique Index.

CREATE TABLE TAB_UNIQ_NULL(COL1 INT , COL2 VARCHAR(1))
GO
CREATE UNIQUE INDEX UNQ_COL1_IDX ON TAB_UNIQ_NULL(COL1) WHERE COL1 IS NOT NULL;
/* Added Where condition to restrict inclusion of NULL data within Index*/
GO


INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'A')
INSERT INTO TAB_UNIQ_NULL VALUES (NULL,'B')
GO

Now our insertion would be Successful without any exception.

let recheck execution plan for same statement,

blog2

Now for NULL data, SQL Server need to perform table access as NULL data are not getting pushed into Index.!!
Post filter Index, conceptually it works similar as Oracle.

Final thoughts:
Ideally it was confusing as NULL is not comparable and we cant enforce uniqueness by comparing NULL with NULL.
So SQL Server can have only one NULL on Single Column with Unique index, as compare to Oracle allowing multiple NULL with Unique Index.
With Filter index in SQL Server we can achieve same.

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

#SQLSERVERLearning : Only Execute Current Statement in SQL Server Mgmt Studio??

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.

For complete list of topics on SQL Server, please check Menu – SQL Server Learning.

After setting CTRL-ENTER as shortcut for running a SQL statement was feeling kinda cool, now can run query in my usual way!
For exploring how to change shortcut for query executions please check here

But wait SQL Server had some new problem area to get unfold and frankly speaking really annoying for any Oracle Guy!
Whenever we run a single statement without selecting statement, we end up running entire statement in Query Editor!

YES Entire statement!, Not sure why the need for same.

blog1

Thought let google the answer, But no forum or blog were able to provide Simple-Single-Shortcut to Execute Current Highlighted statement.
SQL Developer Allows that and it make sense, why any one would want to run and each and every statement with in a query editor as Day to Day normal Executions.
Its should be up to us on what we need to execute, Current statement or set of statement without Selecting SQL text every-time for executions.

Found some Add On, Plug in but everyone don’t have leverage to install external software.

If some one have any better way to execute current statement, will be keen to get help.

Currently using below methods to run individual SQL statement.

IF ( SQL statement Text is with in single line )
Then
1. SHIFT – Upper Arrow
Select Text to run
2. Then use already Set Shortcut to Run SQL Statement, CTRL- ENTER.

ELSE
Thought lets try to use Collapse and Expand options to run SQL having text across multiple lines or formatted queries.

Using Keyboard Shortcuts, had set new Shortcut for Collapse and Expand options in SQL Server Query Editor.

blog2

blog3

For Execution now need to follow following options.

1. Collapse SQL Text using ALT-C
2. Repeat Steps Mention in above IF Clause.

End If;

I know these all problems are non technical,but having ease on analysis and running SQL efficiently equally matter.

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