Skip to Content
author's profile photo Former Member
Former Member

Table creation in HANA

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Posted on May 22, 2013 at 03:58 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 22, 2013 at 06:38 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 22, 2013 at 07:15 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 22, 2013 at 08:46 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.