PARTITION BY

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

All rows of a table partition are physically colocated. (Sybase SQL Language Reference)

This syntax is not needed in Snowflake.

Grammar Syntax

PARTITION BY 
     <range-partitioning-scheme>
     | <hash-partitioning-scheme> 
     | <composite-partitioning-scheme>
     
<range-partitioning-scheme> ::=
   RANGE ( <partition-key> ) ( <range-partition-decl> [,<range-partition-decl> … ] )

<partition-key> ::= <column-name>

<range-partition-declaration> ::=
    <range-partition-name> VALUES <= ( {<constant> |  MAX } ) [ IN <dbspace-name> ]

<hash-partitioning-scheme> ::=
   HASH ( <partition-key> [ , <partition-key>, … ] )

<composite-partitioning-scheme> ::=
   <hash-partitioning-scheme> SUBPARTITION BY <range-partitioning-scheme>

Sample Source Patterns

Input Code:

Sybase

IN -> Sybase_01.sql

-- Range Partitioning
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date) (
    p1 VALUES <= ('2023-01-01'),
    p2 VALUES <= ('2024-01-01'),
    p3 VALUES <= (MAXVALUE)
);

-- Hash Partitioning
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(255)
)
PARTITION BY HASH (customer_id);

-- Composite Partitioning (Hash-Range)
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY HASH (customer_id)
SUBPARTITION BY RANGE (order_date) (
    p1 VALUES <= ('2023-01-01'),
    p2 VALUES <= ('2024-01-01'),
    p3 VALUES <= (MAXVALUE)
);

Output Code:

Snowflake

OUT -> Sybase_01.sql

-- Range Partitioning
CREATE OR REPLACE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "sybase",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
;

-- Hash Partitioning
CREATE OR REPLACE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(255)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "sybase",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
;

-- Composite Partitioning (Hash-Range)
CREATE OR REPLACE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "sybase",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
;