SQL Guidelines for HANA and MSSQL

[former_member184713]

This guide shows common SQL differences between MSSQL and Hana and provide guidelines to ease the migration of a software to hana.

It can help you using SAP Business One database/schema on Hana, and may also help you if you create your own schema like Nware does.

This is a list of common tricks to help you migrate. It is not complete as some sql queries are not common and need specific correction.

You can find SQL Documentation for the hana database here

http://help.sap.com/hana/html/index.html

http://help.sap.com/hana/hana_sql_ref_en.pdf

This guide assume that you already know how to connect to the database using the odbc driver, or the java driver. See my howto document for an example of connectionstring for the odbc driver. http://scn.sap.com/docs/DOC-33628

Common :

To replace a returned NULL value with another value, use COALESCE.

Reason : ISNULL is only available on MSSQL. IFNULL is the Hana equivalent. COALESCE is a ansi-92 sql standard and is working on both databases. There is almost no performance drawback, only some small usage difference on specific case.

Example : Do Select coalesce(null,'notnullvalue') as Field

Don't Select isnull(null,'notnullvalue') as Field

When dealing with fields name that are sql reserved word like "Group","Order", or fields with unicode characters in them like "Français", or fields that are case sensitive, use double quotes when accessing them.

Reason :

Microsoft SQL Support Square Braquets [] between fields, but hana does not support this.

Double quotes are supported by both database and is an ansi standard.

Example : Do Select Captions."Français" from Captions

Don't SELECT Captions.[Français] FROM Captions

To change a field type, use the CAST command

Reason : CONVERT does not exists on Hana.

Example : Do select Cast(Oitm."ItemCode" as varchar) as ItemCode from SBODEMOUS.OITM

Don't select Convert(varchar, Oitm."ItemCode") as ItemCode from OITM

Don't use MSSQL specific code when a standard sql syntax can be used. Standard sql syntax is more likely to work on hana than mssql sql syntax.

Reference : http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt and the hana reference guide above.

Reason : Using standard sql queries will ease the migration process and will reduce code rewrite

Example : DO SELECT OITM."ItemCode" As CustomName FROM OITM

Don't SELECT CustomName = OITM."ItemCode" FROM OITM

Example : DO SELECT CASE WHEN (...) END AS ProductType FROM OITM WHERE "ItemCode" = ''

Don't SELECT ProductType = CASE WHEN (...) END FROM OITM WHERE "ItemCode" = ''

Example : DO Delete from TABLE where 1=2

Don't Delete TABLE where 1=2

To rename a field with a AS, use double quotes instead of single quotes or don't add either of them.

Reason : single quotes work on MSSQL but does not work on Hana. Double quotes work on both databases.

Example : Do SELECT "ItemCode" as "MyField", "ItemName" as MyField2 FROM OITM

Don't SELECT "ItemCode" as 'MyField' FROM OITM

SAP Business One:

Use Quotes around fields in all your sql operations accessing SAP Databases.

Reason :

All sap fields are Case-sensitive. Table are Case Insensitive. Schema are Case Insensitive.

All queries in SAP Business One database need to have double quotes around all fiels and all fields need to be written

in the exact same case as they are in hana. If you don't have access to Hana to see the case sensitivity of the fields, for example in a SAP Business One database, use the same case as the field in MSSQL for a similar database. Microsoft will still work if you use the wrong case, hana will not.

Examples : Do select RDR1."Price" * RDR1."Quantity" as RawTotal from RDR1 where RDR1."DocEntry" = 1

Don't do select "PRICE", "price", price,"prICE" from RDR1

User Defined Table are all created in upper case in the database, regardless on how you write them in the user interface or in the SDK when creating them. When checking if they exists in Hana, always search for the uppercase name. You will also find it in MSSQL as field search is case insensitive on a case insensitive database.

Data type :

Datetime :

If you want to save the current date and time in the database, use current_timestamp.

Reason : GetDate() does not exists in Hana. current_timestamp is supported by both database and is an ansi standard.

Examples : DO Update LisaTransation Set SAPDateTime = current_timestamp where ...

Don't  Update LisaTransation Set SAPDateTime = getdate() where ...

on't insert/Cast stupid values. Only use ISO-8061 standard for the date format like yyyymmdd and yyyy-MM-ddTHH:mm:ss.fff

On mssql, if you Cast('' as datetime), you will get 1900-01-01 00:00:00.

On hana, if you cast('' as datetime), you will get an invalid date "near" 0000-00-00 00:00:00. It can't be replaced by coalesce

because it's not null. It will appert as ? ( the same as null in the studio) and crash most DateTime variable type in the programming language

because they do not support such a low value like 0001-00-00. you can partially fix it by adding 0 seconds like this : do select add_seconds(cast('' as datetime),0) from dummy. You can still use the values in select where to update it to a correct value or deleting it.

on mssql, if you cast(0 as datetime), you also get 1900-01-01 00:00:00

on hana, if you cast(0 as datetime), you get 0001-00-00 00:00:00

Varchar,nvarchar,text :

All text search operations are case sensitive in hana. "Motherboard" is different from "MOTHERboarD".

If a particular field should not be case sensitive, you have to do all search in an case-insensitive manner using the UPPER sql command.

Reason : In MSSQL, the collation you use may be case insensitive. In Hana, there is no collation support, or only 1 collation and it is case sensitive.

Some examples fields where search should be done case insensitive. This list is is not complete :

OITM.ItemCode

OITW.WhsCode

Example : Do  Select "ItemName" from OITM where UPPER("ItemCode") = UPPER('user input')

Don't  Select "ItemName" from OITM where "ItemCode" = 'user input'

try to keep the same case sensitivity between all duplicated text keys in the database.

Reason : as previously noted, all search are case sensitive. If SAP.OITM.ItemCode is different in case-sensitive collation than othertable.ForeignKeyToItemCode, then any join will fail.

If you cannot keep the sensitivity, then you must use UPPER when comparing both fields in join.

Default ordering of text value may be different in MSSQL and Hana.

Reason : This is because of case sensitivity. For complete detail, please read NOTE 1760104 - SQL statement results are ordered differently in MS SQL Server and SAP HANA

To convert text data to uppercase, use the UPPER function.

Reason : UCASE exists on Hana but does not exists on MSSQL. UPPER exists on both.

Do : select UPPER('My teXt')

Don't select UCASE('My teXt')

Trim your text string before sending them to the database. In Hana, the text will be written exactly as it is written in the sql query.

Reason : Hana does not automatically trim your space in varchar field like Microsoft SQL. In Microsoft SQL, space are automatically trimmed.

Do : Update "Table" SET "MyTextField" = 'my text' WHERE "Code" = '0001'; 

Don't : Update "Table" SET "MyTextField" = 'my text         ' WHERE "Code" = '0001    ';

Don't result :

Hana in database : 'my text         '

MSSQL in database : 'my text'

Hana Where : '0001    ' -- the where will likely not even work

MSSQL Where : '0001'

Program :

When dealing with boolean flags (usually configurable settings) in an application, implicitely convert the database value to Boolean when using in a if.

Reason : In MSSQL, the field type is bit and is automatically converted to true/false. In Hana, the bit field does not exists and you can use the tinyint data type that contains integer between 0 and 255. Interger value 0 or 1 are not automatically converted to true/false and the programmer need to convert that value to boolean.

Example : Do  c# If ( Convert.ToBoolean(   rsSQL(  "ManBtchNum" ) )) {

Don't  If (  rsSQL( "ManBtchNum") ){

Common Problem not yet resolved :

Some of these problems will require IF Hana then do this sql in the code.

sql "IF" : They don't work as standard SQL In hana. It only work in SQLScript function. We use them most of the time to check if a table/column exists, and create it if not. This method don't work on hana yet. If you can do this in a separate function, migration will be easier. In Hana Sys.Tables and SYS.view_columns provide the field existance functionnality, but you cannot do if Hana exists then create table.

Example :

Do :  C#  if ( CheckIfColumnExistsFunction ("Table","Column") {

             strSQL = "Alter Table ..."

      }

Don't : strsql = "IF syscolumns.. .. then alter table ... "

DateTime operations : there is no cross compatible way to do datetime operation on both database.

MSSQL : you can cast to a decimal, which return the number of days with decimals between the date and 1900-01-01, DateAdd, DateDiff,DatePart

Hana : Cannot cast to decimal, Add_days, Days_between, specific function for extracting date part.

Text concatenation : there is no cross compatible way to do text concatenation with Hana and MSSQL version below 2012.

With MSSQL 2012, you can use CONCAT as a cross compatible concatenation function.

Microsoft SQL use + and Hana use ||.

Execution of multiple queries in one go does not work with the ODBC Driver.

With other database, we can split multiple queries within only one by specifying ; 

With Hana ODBC driver, this functionnality is not available.

With Hana JDBC driver, you may utilize addBatch() to execute multiple queries.

Example that does not work :

CREATE LOCAL TEMPORARY  table #prc as (select '1' AS Test from dummy);

SELECT * FROM #prc;

DROP TABLE #prc

Workaround : instead of sending 1 big query with multiple query inside, send 3 different query to execute.

Performance : If this was to designed to enhance performance, consider using Pre-compiled sql statement with parameters. As you don't need to compile a statement each time, you gain speed. If this is not enough, go with multiple connection to do the work (insert, update...)