Table creation in HANA

[Former Member]

Hi

How we can classify the data base table while creating i.e row table or column table.

on what basis we can decide creation of table is row table or column table.

Santhosh

[tomas-krojzl]

Hello,

most probably you want to create column based table.

Simple rule to decide is if you wish to create models using this table. If the answer is yes then you MUST create table as column based because row based tables cannot be used in modeling. If the answer is no then more detailed discussion is required.

Decision if table is row based or column based is part of CREATE TABLE statement (or part of relevant dialogue in SAP HANA Studio). If required table can be later converted.

Please post what you plan to do with the table and what data it will contain.

Tomas

[rama_shankar3]

Santosh,

In general, if you are looking at creating a table to store large volumes of data ( master / transaction), it is recommended that you create a column table.

You specify the type using the create table syntax followed by column / row key word.

Please go through the SQL scrip guide for full syntax.

Hope this helps.

Rama

[rindia]

Hi Santhosh,

There are 3 scenarios we can consider while creation of tables.

• Tables are created as Column store while doing data replication (SLT/BODS/DXC).
• When we Import table from local file, we can specify a table can be either row store or column store. In this case if you choose column store then you need to define at least one of the fields as key but for row store, key is optional as shown below:

          

• We can also create tables using SQL in SQLeditor.

CREATE COLUMN TABLE "BWBOHANA"."EMP1"(

"EID" SMALLINT not null, 

"ENAME" NVARCHAR (30) not null, 

primary key ("EID"));

This creates the column table. If you do not use the word COLUMN while defining the table then it creates the row store.

Column store is preferred when the transactional data is huge and involves arithmetic operations and aggregations.

Row store is preferred when the table does not used for any calculations. Example for configuration tables.

Regards

Raj

[former_member184768]

Hi Santosh,

"Row based" or "Column based" criteria is related to the internal storage of data in the database. As the name implies, ROW based stores dat row wise, that is, data for the entire row is stored together. Whereas the COLUMN based stores data for the column together.

So depending upon your application, whether you need to read data for a complete row or need to read data for a column, you can choose which storage to use.

Traditionally DW applications do not read data for all the columns in a row, that is, it doesn't read data row wise, but needs data for few columns. In such cases, it is advisable to store the data with column store. Also column store in HANA provides high degree of compression.

You can read the basics of ROW and COLUMN store in HANA manuals for additional details.

Regards,

Ravi