Adding Default Column to a table in Oracle and SQL Server.

Adding a column to an existing table with default exhibit many question.
For instance
What would be value for that column on existing rows ?
What would happen if new column added is Null?
What would happen if new column added is Not Null?

lets see through in both database Oracle and SQL Server on how addition of new column with a default value is perform.

lets start with SQL Server,

SELECT TAB.COL1, TAB.COL2 INTO DEFAULTCOLTAB
FROM
(
VALUES (1,2),(3,4) ,(5,6)
) TAB(COL1,COL2);

SELECT * FROM DEFAULTCOLTAB;

COL1 COL2
----------- -----------
1 2
3 4
5 6

Adding a column with Default and Not null.

ALTER TABLE DEFAULTCOLTAB ADD COL3 INT NOT NULL DEFAULT 0;

COL1 COL2 COL3
----------- ----------- -----------
1 2 0
3 4 0
5 6 0

When we specify column to be not null and with a default it will update default value for existing rows as well.
now lets add a column with default and null-able.

ALTER TABLE DEFAULTCOLTAB ADD COL4 INT DEFAULT 0;

COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 0 NULL
3 4 0 NULL
5 6 0 NULL

When we specify column to be NULL, SQL Server does not update existing rows for newly added column. it is set to NULL and not to Default value.

If in case we want to update, existing rows with default value for null-able column.
we can use “WITH VALUES” clause and update existing rows with default values.

ALTER TABLE DEFAULTCOLTAB ADD COL5 INT DEFAULT 0 WITH VALUES;

COL1 COL2 COL3 COL4 COL5
----------- ----------- ----------- ----------- -----------
1 2 0 NULL 0
3 4 0 NULL 0
5 6 0 NULL 0

Now lets see how it works for Oracle.

CREATE TABLE DEFAULTCOLTAB
AS
SELECT ROWNUM COL1 , ROWNUM +1 COL2 FROM DUAL CONNECT BY ROWNUM <4;

COL1 COL2
---------- ----------
1 2
2 3
3 4

Adding a column with default and not null constraint.
ALTER TABLE DEFAULTCOLTAB ADD COL3 INT DEFAULT(0) NOT NULL ;

COL1 COL2 COL3
---------- ---------- ----------
1 2 0
2 3 0
3 4 0

It behave same as in SQL Server, adding default values for all existing rows.

Adding a column with default and null-able constraint.

ALTER TABLE DEFAULTCOLTAB ADD COL4 INT DEFAULT(0) NULL ;

COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
1 2 0 0
2 3 0 0
3 4 0 0

here it differ on how same work in SQL Server, though column was null-able it still add default values for all existing rows.
it is not dependent on NULL constraint on column, it update default values for all existing rows.

Posted in Oracle Internal | Leave a comment

Table Alias in Oracle and SQL Server.

Sometimes we assume things to work and not test it, but it surprise you and give you a reason to learn.

We have a product that works on top of both Oracle and SQL Server, hence we are bound to compliant SQL syntax as ANSI or validated on both.
For one of our changes, we had written SQL as below to run on both SQL Server and Oracle.

SELECT COL1 AS COLUMN1 FROM TABLE1 AS TABLE_ALIAS

As it being a simple change,we (Primarily I) only tested on SQL Server and was confident enough that it will work in Oracle.
But is unfold surprise with below Exception in Oracle and works fine in SQL Server.

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

lets try to understand with Example in Oracle,

select 'Hi Error' AS Hello from DUAL AS d;

We are using AS keyword to define alias for both Column and Table.
In Oracle, “AS” keyword for Alias only works for Column and not for Table. Didn’t find anything on oracle documentation.

Finally had changed to similar SQL as below.
select 'Hi Error' AS Hello from DUAL d;

HELLO
--------
Hi Error

Learning, Test and don’t assume.!

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

CHAR Datatype and ConCat in Oracle and SQL Server.

Char is a Fixed length data type, provided by database to stored Character Data.
It will allocate fix bytes as per length specified for CHAR datatype and will pad with Space for remaining chunks.

With Variable declare as CHAR(20), Byte allocated will be always 20.

DECLARE @Var CHAR(20)='Hello'
SELECT DATALENGTH(@Var) as DataLen
GO

Output :
DataLen
20

Now lets see how it behave in case of CONCAT for Oracle and SQL Server.

SQL Server:
We are declaring a CHAR Datatype with Length(20), and trying to ConCat.

DECLARE @Var CHAR(20)='Hello-'
SET @Var= @Var+'SQL Server'
SELECT @Var
GO

OUTPUT: "Hello- "

In Case of SQL Server, even after Concat it only displaying previous assign Data due to initial padded space.
With we Declare CHAR(20) and assign it “Hello-“, it is actualy Stored as

“Hello-00000000000000”

0 represent Space and total DataLength is 20.

So any Concat is Beyond length specified for @Var i.e. 20, Hence Concat is not possible.
But it wont throw any Exception or Error, it will whitewash any ConCat.

Oracle:

SET serveroutput ON;
DECLARE
var_1 CHAR(20000) := 'HELLO-';
BEGIN
var_1 := var_1 || 'ORACLE';
dbms_output.put_line(var_1);
END;

Output:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"

Oracle will Throw exception for such Concat with CHAR Datatype.
Unless we Use TRIM, Oracle will throw exception for any Concat with CHAR Datatype.

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

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 check below SQL that we were initially trying to form Year Range,

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS START_OD_YEAR,
DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, -1) AS END_OF_YEAR

Output ::
START_OD_YEAR END_OF_YEAR
----------------------- -----------------------
2017-01-01 00:00:00.000 2017-12-31 00:00:00.000

For END_OF_YEAR we are only getting last day of year, but not till last possible time for end date of year.

Using DATEADD for milliseconds (ms), we can achieve last possible end of time instead for any hard-code.
lets walk trough some example to understand it better.

SELECT DATEADD(MS, -2,GETDATE()) AS MS_2, GETDATE() AS SYS_DATE

MS_2                          SYS_DATE
-----------------------       -----------------------
2017-06-02 16:24:15.577 2017-06-02 16:24:15.580

It should minus 2 milliseconds from current system time but somehow shows as minus by 3 factor. (Not sure why)
For getting last day of year till last time possible, we will follow below pseudo logic.

GET_FIRST_DAY_OF_NEXT_YEAR – (2 MILLISECONDS TO GET LAST TIME POSSIBLE FOR PREVIOUS YEAR)

lets create the SQL to implement same.

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS START_OD_YEAR,
DATEADD(MS,-2,DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0)) AS END_OF_YEAR

START_OD_YEAR END_OF_YEAR
----------------------- -----------------------
2017-01-01 00:00:00.000 2017-12-31 23:59:59.997

Hope it was informative!

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

FIXED length character storage and NULL in SQL Server.

Most of us must be aware of CHAR datatype and its fixed length storage pattern.
If we declare CHAR(32) and store only 4 bytes character ‘ABCD’, It will pads up remaining bytes (28 bytes).
Ultimately, whole bytes is consume for serving only 4 length data.

lets understand it better with field work.

CREATE TABLE TESTCHAR
(
COL1 CHAR(32) NULL
);

INSERT INTO TESTCHAR VALUES ('ABCD');
SELECT COL1 , DATALENGTH(COL1) AS SIZE FROM TESTCHAR

COL1 SIZE
------ -----------
ABCD 32

but what happen for NULL data, will it still consume allocated bytes of 32 or follow some other storage pattern.

lets create tables with single column having CHAR and VARCHAR datatype respectively.

DROP TABLE TESTCHAR
CREATE TABLE TESTCHAR
(
COL1 CHAR(32) NULL
);

CREATE TABLE TESTVARCHAR
(
COL1 VARCHAR(32) NULL
);

Now let insert 1,00,000 null records in both the tables.

;WITH ALIAS1(COL1,COL2)
AS
(SELECT NULL COL1 , 1 COL2
UNION ALL
SELECT NULL , COL2+1 FROM ALIAS1 WHERE COL2 <=100000)
INSERT INTO TESTCHAR
SELECT COL1 FROM ALIAS1 Option (maxrecursion 0)

;WITH ALIAS1(COL1,COL2)
AS
(SELECT NULL COL1 , 1 COL2
UNION ALL
SELECT NULL , COL2+1 FROM ALIAS1 WHERE COL2 <=100000)
INSERT INTO TESTVARCHAR
SELECT COL1 FROM ALIAS1 Option (maxrecursion 0)

lets check size for a column having null as data for both tables.

SELECT TOP 1 COL1 , DATALENGTH(COL1) AS SIZE FROM TESTCHAR;
COL1 SIZE
------- -----------
NULL NULL

SELECT TOP 1 EVENT_GROUP_ID AS COL1 , DATALENGTH(EVENT_GROUP_ID) AS SIZE FROM TESTVARCHAR;
COL1 SIZE
-------- -----------
NULL NULL

DATALENGTH funtion does not shows any bytes for NULL storage for both datatype CHAR and VARCHAR.
Lets check table size for both data type using sp_spaceused.

exec sp_spaceused 'TESTCHAR'
exec sp_spaceused 'TESTVARCHAR'

name rows reserved data index_size unused
------------- -------- ---------- -------- ----------- -------
TESTCHAR 100001 4104 KB 4064 KB 8 KB 32 KB

name rows reserved data index_size unused
------------- -------- ---------- -------- ----------- -------
TESTVARCHAR 100001 1096 KB 1088 KB 8 KB 0 KB

clearly table size for only char datatype having null is almost 4 times more then table size having only varchar for null data.
DATALENGTH function was not able to incept same at row level, but definitely overall table size for CHAR is more then VARCHAR.

So, having column with datatype as CHAR and having most of data as NULL, will impose more storage then VARCHAR datatype.
NULL in char consume more disk then NULL in varchar datatype.

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

APPROX_COUNT_DISTINCT in Oracle 12c.

Most of time during performance analysis to understand Stale stats, we usually need to check estimated NDV (Number of Distinct values).
If data-set is huge , it take considerable time to project desired output using traditional COUNT(DISTINCT) appraoch.
For any such analysis we usually need to understand approximate or estimated distribution of columns values to understand SELECTIVITY AND CARDINALITY on any functional predicates.

With Oracle 12c (12.1.0.2), we have a function “APPROX_COUNT_DISTINCT” , which is claim to faster then tradition COUNT(DISTINCT <>) approach to get an idea on NDV.

Let try to understand same with field work!

--Creating a table to 1000,5000,10000 NDV for some columns.
CREATE TABLE TEST_APROX_CNT_DIS AS
(SELECT ROWNUM AS COL1 ,
MOD(ROWNUM ,1000) COL2 , --NDV 1000
MOD(ROWNUM ,5000) COL3 , --NDV 5000
MOD(ROWNUM ,10000) COL4 --NDV 10000
FROM DUAL
CONNECT BY LEVEL < 1000000
);

For different distinct values, we will compare elapsed time with traditional approach.
APPROX_COUNT_DISTINCT, as name implies wont give exact NDV for a column.
It will calculate an approximate values to get an understanding of column selectivity.

Below is compassion of values projected for different distinct values.
COUNT(DISTINCT COL2) APPROX_COUNT_DISTINCT(COL2)
1000                       1012

COUNT(DISTINCT COL3) APPROX_COUNT_DISTINCT(COL3)
5000                                       4835

COUNT(DISTINCT COL4) APPROX_COUNT_DISTINCT(COL4)
10000                                    10050

From performance point of view, APPROX_COUNT_DISTINCT was always faster then usual COUNT (DISTINCT ) approach for checking column NDV.

SELECT COUNT(DISTINCT COL2) FROM TEST_APROX_CNT_DIS;
Elapsed: 00:00:00.091

SELECT APPROX_COUNT_DISTINCT(COL2) FROM TEST_APROX_CNT_DIS;
Elapsed: 00:00:00.055

SELECT COUNT(DISTINCT COL3) FROM TEST_APROX_CNT_DIS;
Elapsed: 00:00:00.105

SELECT APPROX_COUNT_DISTINCT(COL3) FROM TEST_APROX_CNT_DIS;
Elapsed: 00:00:00.059

SELECT COUNT(DISTINCT COL4) FROM TEST_APROX_CNT_DIS;
Elapsed: 00:00:00.105

SELECT APPROX_COUNT_DISTINCT(COL4) FROM TEST_APROX_CNT_DIS;
Elapsed: 00:00:00.055

Let try to compare underlying execution plan for both approach.

COUNT - DISTINCT
------------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | VIEW | VW_DAG_0 | 1000 |
| 3 | HASH GROUP BY | | 1000 |
| 4 | TABLE ACCESS FULL| TEST_APROX_CNT_DIS | 999K|
------------------------------------------------------------

APPROX_COUNT_DISTINCT
------------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE APPROX| | 1 |
| 2 | TABLE ACCESS FULL | TEST_APROX_CNT_DIS | 999K|
------------------------------------------------------------

With APPROX_COUNT_DISTINCT we got an new Aggregation operation in execution plan “SORT AGGREGATE APPROX”.
As compare to tradition approach, we would need to perform GROUP BY and then AGGREGATION operation to serve COUNT and DISTINCT result.

If for any analysis, we just need an estimate of NDV with acceptable variation from actual values.
APPROX_COUNT_DISTINCT is the function to be used.

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

Lateral Views in Oracle 12c.

#TGIF.

In Oracle 12c, we got introduce to LATERAL Views officially(Before same was getting use internally as part of Query Transformation) to join Inline views, Pre 12c we were not able to achieve same of joining inline views.

With Lateral View hint, we can have correlated inline views in FROM Clause itself.
It allows to refer Inline View to help achieve join in FROM Clause and help to restrict same.

Lets start with examples, Suppose we require data like below.

COL1     COL2
------- ----------
1       1
2       1
2       2
3       1
3       2
3       3
4       1
4       2
4       3
4       4

For a value in COL1, we need to display number from 1 till col1 values in COL2.

below is the sample SQL to achieve similar results,

SELECT L.COL1, M.COL2
FROM
(SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL <= 4 ) L ,
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1) M

Without LATERAL View hint, it won’t allow joining within Inline Views.
It is bound to give below Exception.

ORA-00904: "L"."COL1": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 4 Column: 53

Lets try using LATERAL Hint.

SELECT L.COL1, M.COL2
FROM
(SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL <= 4 ) L ,
LATERAL (SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1) M

COL1 COL2
---------- ----------
1       1
2       1
2       2
3       1
3       2
3       3
4       1
4       2
4       3
4       4

Lets have a look at underlying execution plan,

-------------------------------------------------------------------
| Id | Operation |                         Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT                              | | 1 |
| 1 | NESTED LOOPS                                   | | 1 |
| 2 | VIEW                                          | | 1 |
|* 3 | CONNECT BY WITHOUT FILTERING | | |
| 4 | FAST DUAL                                       | | 1 |
| 5 | VIEW                                 | VW_LAT_A18161FF | 1 |
|* 6 | CONNECT BY WITHOUT FILTERING| | |
| 7 | FAST DUAL                                          | | 1 |
-------------------------------------------------------------------

Its work as nested loop or Filter clause in SQL Server, for each outer inner will get invoke\executed.

Had used Lateral Views to get all prime number till 100.
SQL for same,

SELECT 1 as prime_number FROM DUAL
UNION ALL
SELECT COL1
FROM
(SELECT COL1 ,
COUNT(
CASE
WHEN MOD(COL1 ,
CASE
WHEN COL2 = 1
OR COL2 = COL1
THEN NULL
ELSE COL2
END) = 0
THEN 1
ELSE NULL
END) CNT
FROM
(SELECT LEVEL + 1 COL1 FROM DUAL CONNECT BY LEVEL <= 99
) L ,
LATERAL
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1
)
GROUP BY COL1
ORDER BY 1
)
WHERE CNT = 0;

Posted in Oracle 12c New Features, Oracle Internal | 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 generate tabular data for further joins or any functional usage.
we can have multiple column data as shown below.

SELECT TAB.COL1, TAB.COL2
FROM
(
VALUES (1,2),(3,4) ,(5,6)
) TAB(COL1,COL2);

Output :
COL1 COL2
----------- -----------
1 2
3 4
5 6

(3 row(s) affected)

2. Insert Multiple Records as one statement.

If can be done in a statement do it that way!
Instead of having multiple statement for same usage, it always efficient to accomplish same as single statement.

Using VALUES we can perform similar insert as one statement as compared to separate insert statement for each row..

CREATE TABLE TBL
(
C1 INT ,
C2 INT ,
C3 INT
);

INSERT INTO TBL(C1,C2 ,C3)
VALUES (1, 2, 3)
, (4, 5, 6)
, (7, 8, 9);

3. Get Max value from Set of Column using VALUES.

SELECT * ,
( SELECT MAX(COL) FROM (VALUES (C1) , (C2) , (C3) ) AS TAB(COL)) AS MAX_COL_VALUE
FROM TBL

OUTPUT:
c1 c2 c3 MAX_COL_VALUE
----------- ----------- ----------- -------------
1 2 3 3
4 5 6 6
7 8 9 9

4. SELECT within Values

We can have Select statement as part of VALUES.

INSERT INTO TBL(C1,C2 ,C3)
VALUES ((SELECT 1), (SELECT 2*7), (SELECT TOP 1 C1 FROM TBL))

(1 row(s) affected)

Hope it was a bit interesting and we learn something new.

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

SQL Server : ExecuteNonQuery shows incorrect rows affected.

For one of client as per functionality we were invoking a SQL Server procedure using ExecuteNonQuery method.
Within procedure we had a DML statement performing update and on rows affected as return from ExecuteNonQuery performing further functional stuff.

In one of case for a table, though Rows impacted were only 1 but rows affected return through ExecuteNonQuery was more than 1.
On further analysis, identified we had a trigger enabled on updated table performing some DML actions.

From MSDN documentation:
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1

Let understand it better with field work :
We will create a simple procedure with an insert on table having trigger enabled.

drop table tab_row_affected;
create table tab_row_affected
(col1 int ,col2 datetime ,col3 varchar(10));

drop table tab_row_affected1;
create table tab_row_affected1
(col1 int , col2 datetime ,col3 varchar(10));

--Create procedure with insert on tab_row_affected
CREATE PROCEDURE USP_TESTROWSAFFECTED
AS
INSERT INTO tab_row_affected VALUES (1,GETDATE(),'INSERT');
PRINT @@ROWCOUNT

---create trigger on tab_row_affected
CREATE TRIGGER TRIG_TESTROWSAFFECTED on tab_row_affected
FOR INSERT
AS
INSERT INTO tab_row_affected1 VALUES (1,GETDATE(),'TRIGGER');

Sample C# Code :

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnection myConn = new SqlConnection(@"");
try
{
SqlCommand scommand = new SqlCommand("dbo.USP_TESTROWSAFFECTED", myConn);
scommand.CommandType = System.Data.CommandType.StoredProcedure;
myConn.Open();
Console.WriteLine("Calling Stored Procedure!!");
int rows_affected = scommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected :: " + rows_affected.ToString());
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
myConn.Close();
}}}}

On Executing Above Code, below is the Console output :

As solution to get only Rows affected within procedure and ignore rows affected as part of trigger we can use”SET NOCOUNT ON” in Trigger.
When NOCOUNT is set as ON for a block, it stop sending Count of rows affected.

/*Changing Trigger with NOCOUNT*/
SET NOCOUNT ON, will
CREATE TRIGGER TRIG_TESTROWSAFFECTED on tab_row_affected
FOR INSERT
AS
SET NOCOUNT ON;
INSERT INTO tab_row_affected1 VALUES (1,GETDATE(),'TRIGGER');

Post Trigger changes, now rows affected is reflecting only rows impacted within procedure.

Posted in 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 like check for numeric information [0-9]
3. Concat all number based on regex match and project same.

Declare @s varchar(100),@result varchar(100)
set @s='fdf4ff345rg564rt5434tr'
set @result='';
with alias1(data1, data2) as
(
select 1 as data1 , SUBSTRING(@s,1,1) as data2
union all
select data1+1 , SUBSTRING(@s,data1+1,1)
from alias1 where LEN(@s) > data1 --**Important, does not remove condition
)
select @result = @result + case when data2 like '[0-9]' then data2 else '' end
from
(select data2 from alias1) as final option (MAXRECURSION 0);
select @result as only_numbers;

only_numbers
-----------------
43455645434

please note :
Added option (MAXRECURSION 0) to handle default max recursion limit (100) exception.

Reference to blog for alternate solution :
https://madhivanan.wordpress.com/2017/03/21/extract-only-numbers-from-alphanumeric-string/

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