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 Helper SQL, 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

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, need to move Date generation from Front end to database.
For generating date range and apply necessary function on it, had used Recursive with clause with termination based on Date range conditions.

It will generate dates between specified dates range and get below attribute for each and every date.

1. DATETIME
2. NEXT_DAY_DATE
3. PREV_DAY_DATE
4. QUARTER
5. YEAR
6. MONTH
7. MONTH_NAME
8. WEEK
9. WEEK_DAY
10. DATE_YYYYMMDD
11. DATE_MMYYYY
12. YEAR_YYYY
13. YEAR_YY
14. MONTH_MM
15. WEEK_WW
16. DAY_DD
17. DAY_OF_WEEK
18. DAY_OF_MONTH
19. DAY_OF_YEAR
20. START_OF_YEAR_DATE
21. END_OF_YEAR_DATE
22. NEXT_YEAR_DATE
23. START_OF_WEEK_DATE (based on @@DATEFIRST being default as SUNDAY)
24. END_OF_WEEK_DATE
25. START_OF_MONTH_DATE
26. END_OF_MONTH_DATE
27. NEXT_MONTH_DATE
28. DAYS_IN_YEAR
29. DAYS_IN_MONTH
30. IS_LEAP_DAY (Check for 29th Feb)
31. IS_LEAP_YEAR

Code base :

DECLARE @StartDateTime DATE
DECLARE @EndDateTime DATE
SET @StartDateTime = '2000-01-01'
SET @EndDateTime = '2017-12-31';
WITH DateRange
AS
SELECT
@StartDateTime AS 'DATE_TIME', --CURRENT DATE
'D' AS 'DATE_TYPE' , --DATE TYPE AS NORMAL DAY IT IS HARDCODED.
DATEADD(D, 1, @StartDateTime) AS 'NEXT_DAY_DATE', --NEXT DAY FROM CURRENT DATE BY USING DATEADD TO ADD 1.
DATEADD(D, - 1, @StartDateTime) AS 'PREV_DAY_DATE', --PREVIOUS DAY FROM CURRENT DATE BY USING DATEADD TO ADD -1.
DATEPART(Q, @StartDateTime) AS 'QUARTER', --GET QUARTER OF THE YEAR USING DATEPART FUNCTION.
DATEPART(YYYY, @StartDateTime) AS 'YEAR', --GET YEAR PART OF CURRENT DATE USING DATEPART FUNCTION
DATEPART(MM, @StartDateTime) AS 'MONTH', --GET MONTH PART OF CURRENT DATE USING DATEPART FUNCTION
DATENAME(month, @StartDateTime) AS 'MONTH_NAME' , --GET NAME OF MONTH BASED ON CURRENT DATE PASSED.
DATEPART(WK, @StartDateTime) AS 'WEEK', --GET WEEK PART OF CURRENT DATE USING DATEPART FUNCTION
DATENAME(weekday, @StartDateTime) AS 'WEEK_DAY', --GET WEEKDAY OF CURRENT DATE USING DATEPART FUNCTION
CONVERT(char(10), @StartDateTime, 112) AS 'DATE_YYYYMMDD', --GET DATE AS FORMATTED AS YYYYMMDD USING FORMAT 112
RIGHT (REPLICATE('0', 6) + CAST(DATEPART(MM, @StartDateTime) AS VARCHAR) + CAST(DATEPART(YYYY, @StartDateTime) AS VARCHAR), 6) AS 'DATE_MMYYYY', --GET DATE AS MMYYYY FORMAT.
DATEPART(YYYY, @StartDateTime) AS 'YEAR_YYYY', --GET YEAR PART FROM CURRENT DATE
SUBSTRING(CAST(DATEPART(YYYY, @StartDateTime) AS varchar), 3, 2) AS 'YEAR_YY', --GET YEAR PART AS YY FROM CURRENT DATE USING SUBSTRING FUNCTION AND DATEPART
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(mm, @StartDateTime) AS VARCHAR), 2) AS 'MONTH_MM', --GET MONTH PART AND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(WK, @StartDateTime) AS VARCHAR), 2) AS 'WEEK_WW', --GET WEEK PART AND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, @StartDateTime) AS VARCHAR), 2) AS 'DAY_DD', --GET DAY PART ND PADDED WITH LEADING ZERO UPTO LENGTH TWO (01,02 ..)
DATEPART(WK, @StartDateTime) AS 'DAY_OF_WEEK', --GET WEEK NUMBER FOR CURRENT DATE WITHIN YEAR
DATEPART(DD, @StartDateTime) AS 'DAY_OF_MONTH', --GET DAY OF MONTH FOR CURRENT DATE
DATEPART(DY, @StartDateTime) AS 'DAY_OF_YEAR', --GET DAY NUMBER OF THE YEAR BASED ON CURRENT DATE
DATEADD(yy, DATEDIFF(yy, 0, @StartDateTime), 0) AS 'START_OF_YEAR_DATE', --GET START DATE OF THE YEAR
DATEADD(yy, DATEDIFF(yy, 0, @StartDateTime) + 1, - 1) AS 'END_OF_YEAR_DATE', --GET END DATE OF THE YEAR
DATEADD(year, DATEDIFF(year, - 1, @StartDateTime), 0) AS 'NEXT_YEAR_DATE', --GET NEXT START DATE OF YEAR BASED ON CURRENT DATE
DATEADD(DD, - (CHOOSE(DATEPART(dw, @StartDateTime), 1, 2, 3, 4, 5, 6, 7) - 1), @StartDateTime) AS 'START_OF_WEEK_DATE', --ASSUMING WEEK START FROM SUNDAY, GET START DATE OF WEEK BASED ON CURRENT DATE.
DATEADD(DD, 7 - CHOOSE(DATEPART(dw, @StartDateTime), 1, 2, 3, 4, 5, 6, 7), @StartDateTime) AS 'END_OF_WEEK_DATE', --ASSUMING WEEK START FROM SUNDAY, GET END DATE OF WEEK BASED ON CURRENT DATE.
DATEADD(DAY, 1, EOMONTH(@StartDateTime, - 1)) AS 'START_OF_MONTH_DATE', --GET START OF MONTH DATE BASED ON DATE
EOMONTH(@StartDateTime) AS 'END_OF_MONTH_DATE', --GET MONTH END DATE BASED ON DATE
DATEADD(DD, 1, EOMONTH(@StartDateTime)) AS 'NEXT_MONTH_DATE', --GET START OF NEXT MONTH DATE
DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, @StartDateTime), 12, 31)) AS 'DAYS_IN_YEAR', --GET DAYS WITHIN A YEAR (365/366)
DATEPART(DD, EOMONTH(@StartDateTime)) AS 'DAYS_IN_MONTH', --GET DAYS WITHIN A MONTH
CASE WHEN SUBSTRING(CONVERT(char(10), @StartDateTime, 112), 5, 4) = '0229' THEN 'Y' ELSE 'N' END AS 'IS_LEAP_DAY', --CHECK WHETHER CURRENT DATE IS LEAP DAY (29-FEB)
CASE WHEN DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, @StartDateTime), 12, 31)) = 366 THEN 'Y' ELSE 'N' END AS 'IS_LEAP_YEAR' --CHECK WHETHER CURRENT YEAR IS LEAP YEAR.
UNION ALL
SELECT
DATEADD(D, 1, DATE_TIME) AS 'DATE_TIME',
'D' AS 'DATE_TYPE',
DATEADD(D, 1, DATEADD(D, 1, DATE_TIME)) AS 'NEXT_DAY_DATE',
DATEADD(D, - 1, DATEADD(D, 1, DATE_TIME)) AS 'PREV_DAY_DATE',
DATEPART(Q, DATEADD(D, 1, DATE_TIME)) AS 'QUARTER',
DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS 'YEAR',
DATEPART(MM, DATEADD(D, 1, DATE_TIME)) AS 'MONTH',
DATENAME(month, DATEADD(D, 1, DATE_TIME)) AS 'MONTH_NAME',
DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS 'WEEK',
DATENAME(weekday, DATEADD(D, 1, DATE_TIME)) AS 'WEEK_DAY',
CONVERT(char(10), DATEADD(D, 1, DATE_TIME), 112) AS 'DATE_YYYYMMDD',
RIGHT (REPLICATE('0', 6) + CAST(DATEPART(MM, DATEADD(D, 1, DATE_TIME)) AS VARCHAR) + CAST(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 6) AS 'DATE_MMYYYY',
DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS 'YEAR_YYYY',
SUBSTRING(CAST(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)) AS varchar), 3, 2) AS 'YEAR_YY',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(mm, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'MONTH_MM',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'WEEK_WW',
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, DATEADD(D, 1, DATE_TIME)) AS VARCHAR), 2) AS 'DAY_DD',
DATEPART(WK, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_WEEK',
DATEPART(DD, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_MONTH',
DATEPART(DY, DATEADD(D, 1, DATE_TIME)) AS 'DAY_OF_YEAR',
DATEADD(yy, DATEDIFF(yy, 0, DATEADD(D, 1, DATE_TIME)), 0) AS 'START_OF_YEAR_DATE',
DATEADD(yy, DATEDIFF(yy, 0, DATEADD(D, 1, DATE_TIME)) + 1, - 1) AS 'END_OF_YEAR_DATE',
DATEADD(year, DATEDIFF(year, - 1, DATEADD(D, 1, DATE_TIME)), 0) AS 'NEXT_YEAR_DATE',
DATEADD(DD, - (CHOOSE(DATEPART(dw, DATEADD(D, 1, DATE_TIME)), 1, 2, 3, 4, 5, 6, 7) - 1), DATEADD(D, 1, DATE_TIME)) AS 'START_OF_WEEK_DATE',
DATEADD(DD, 7 - CHOOSE(DATEPART(dw, DATEADD(D, 1, DATE_TIME)), 1, 2, 3, 4, 5, 6, 7), DATEADD(D, 1, DATE_TIME)) AS 'END_OF_WEEK_DATE',
DATEADD(DAY, 1, EOMONTH(DATEADD(D, 1, DATE_TIME), - 1)) AS 'START_OF_MONTH_DATE', EOMONTH(DATEADD(D, 1, DATE_TIME)) AS 'END_OF_MONTH_DATE',
DATEADD(DD, 1, EOMONTH(DATEADD(D, 1, DATE_TIME))) AS 'NEXT_MONTH_DATE',
DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)), 12, 31)) AS 'DAYS_IN_YEAR',
DATEPART(DD, EOMONTH(DATEADD(D, 1, DATE_TIME))) AS 'DAYS_IN_MONTH',
CASE WHEN SUBSTRING(CONVERT(char(10), DATEADD(D, 1, DATE_TIME), 112), 5, 4) = '0229' THEN 'Y' ELSE 'N' END AS 'IS_LEAP_DAY',
CASE WHEN DATEPART(dy, DATEFROMPARTS(DATEPART(YYYY, DATEADD(D, 1, DATE_TIME)), 12, 31)) = 366 THEN 'Y' ELSE 'N' END AS 'IS_LEAP_YEAR'
FROM DateRange
WHERE DATE_TIME < @EndDateTime -- **IMPORTANT** DONT CHANGE, IT MIGHT IMPACT AS NEVER ENDING SQL (AS MAXRECURSION IS SET TO 0)
)
SELECT *
FROM DateRange
OPTION (MAXRECURSION 0) --HINT ADDED TO AVOID RECURSION EXCEPTION CAUSE IN RECURSIVE WITH CLAUSE.

Expected Output:

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

#SQLSERVERLearning : Order By Learning.

Most of folks feel , both(Oracle-SQLServer) are traditional RDBMS database and should or need to work some what “SIMILAR”.
But, But when we actually start working on core with queries, development and tuning.We identified a lot of difference between any two database.

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.

We had a requirement To have Custom Order on Varchar data as per numeric part in data.
For instance for below information.

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
for xml path('') , type ;

Output was ,[p1],[p10],[p2] and our requirement was it should be on basis of numeric part i.e. ,[p1],[p2],[p10].

1. ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

We try to add Custom order ” Order by CAST(SUBSTRING(b,2,len(b)) as INT )” in same query but got a exception.

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
order by cast (SUBSTRING(b,2,len(b)) as INT )
for xml path('') , type ;

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Whenever we have DISTINCT, we can only order by based on column projected in output and can’t have any other Column or expression as part of order by.

below query will also not work,

select distinct ',' + QUOTENAME (c.b)
from (select 'p' + '1' as b
union all
select 'p' + '10'
union all
select 'p' + '2') as c
order by c.b
for xml path('') , type ;

It is tightly coupled with Expression/Column Projected as per Distinct Expression.

2.The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Then we try to Move Order by inline after Distinct and before XML PATH operation.
select ',' + QUOTENAME (c.b)
from
(select B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c order by cast (SUBSTRING(b,2,len(b)) as INT ) ) as c
for xml path('') , type ;

But it give exception and does not allow inline order by.

Exception :
Msg 1033, Level 15, State 1, Line 15
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

So thought to push order by inline by using “TOP 100 PERCENT”, it will cause to fetch all rows and adhere order by constraint.

select ',' + QUOTENAME (c.b)
from
(select TOP 100 PERCENT B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c order by cast (SUBSTRING(b,2,len(b)) as INT ) ) as c
for xml path('') , type ;

Output ::
,[P1],[P10],[P2],[P22],[P3]

Though we had order by, but same was completely ignored by SQL SERVER.
Order by is still based on Distinct operation. and provided Order by expression is ignored.

Underlying Execution plan has an explanation for same.Sort operation is ordering based on default expression projected for XML elements.

orderby1

Sorting is due to DISTINCT operation and not due to our custom provided Order by.
So though we have use TOP 100 PERCENT and Custom Ordering, it was completely ignore by SQL Server.

Final Solution :
Provide required ordering in XML scope and let it get applied after Default Distinct Order by.

select ',' + QUOTENAME (c.b)
from
(select B from
((SELECT DISTINCT C.B
FROM (SELECT 'P' + '1' AS B
UNION ALL
SELECT 'P' + '10' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '2' AS Expr1
UNION ALL
SELECT 'P' + '3' AS Expr1
UNION ALL
SELECT 'P' + '22' AS Expr1) AS C)) as c ) as c
order by cast (SUBSTRING(b,2,len(b)) as INT )
for xml path('') , type ;

Output ::
,[P1],[P2],[P3],[P10],[P22]

Execution Plan :

orderby2

Now we have Two Order by, one implicit as per Distinct and other for our custom ordering.

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

Oracle provide LPAD and RPAD function for padding a specific characters as per specified length.
SQL Server directly don’t provide any in-build function to achieve similar functionality.

We will try to understand, on how similar functionality can be implemented in SQL Server.
Using standard function SQL server provide we will try to implement LPAD and RPAD functionality.

1. LEFT and RIGHT
Return varchar from left or right up to length specified.

SELECT LEFT('abcdefg',1) , RIGHT('abcdefg',1);

LEFT RIGHT
---- -----
a         g

2. REPLICATE
Return replication of characters based on number of times specified.

SELECT REPLICATE('AB', 4) "REPLICATE"

REPLICATE
---------
ABABABAB

We will try to recreate Oracle LPAD and RPAD function using UDF in SQL Server.

--Creating LPAD Function
CREATE FUNCTION LPAD(@STR VARCHAR, @PADD_LEN INT , @PAD_STR VARCHAR = ' ')
RETURNS VARCHAR(max)
AS
BEGIN
RETURN RIGHT( REPLICATE(@PAD_STR, @PADD_LEN) + @STR, @PADD_LEN);
END;

--Creating RPAD Function
CREATE FUNCTION RPAD(@STR VARCHAR, @PADD_LEN INT , @PAD_STR VARCHAR = ' ')
RETURNS VARCHAR(max)
AS
BEGIN
RETURN LEFT(@STR + REPLICATE(@PAD_STR, @PADD_LEN) , @PADD_LEN);
END;

Example from newly created function,

SELECT DBO.LPAD('A',2,'_') "LPAD" , DBO.RPAD('A',2,'_') "RPAD"

LPAD RPAD
----- -----
_A A_

We can implement same in SQL Server to get similar LPAD and RPAD functionality as in Oracle.

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