text: /*
  	==========
  	SETUP DEMO
  	==========
  */
  
  select databasepropertyex(db_name(), 'ServiceObjective')
  go
  
  /*
  -- Run if needed
  create schema [test]
  go
  */
  
  drop table if exists [test].[ORDERS];
  create table [test].[ORDERS]
  (
  	[O_ORDERKEY] [int] not null,
  	[O_CUSTKEY] [int] not null,
  	[O_ORDERSTATUS] [char](1) not null,
  	[O_TOTALPRICE] [decimal](15, 2) not null,
  	[O_ORDERDATE] [date] not null,
  	[O_ORDERPRIORITY] [char](15) not null,
  	[O_CLERK] [char](15) not null,
  	[O_SHIPPRIORITY] [int] not null,
  	[O_COMMENT] [varchar](79) not null,
  	[O_ORDERYEAR] as (datepart(year,[O_ORDERDATE])) persisted,
  	[O_ORDERMONTH] as (datepart(month,[O_ORDERDATE]))
  )
  go
  
  /*
  -- Run if needed
  drop external data source [Azure-Storage];
  drop database scoped credential [Azure-Storage-Credentials]
  go
  */
  
  select * from sys.[external_data_sources] where [name] like 'Azure-Storage%'
  select * from sys.[database_scoped_credentials] where [name] like 'Azure-Storage%'
  go 
  
  create database scoped credential [Azure-Storage-Credentials]
  with identity = 'SHARED ACCESS SIGNATURE',
  secret = 'sv=2019-12-12&st=2021-01-28T22%3A00%3A27Z&se=2021-01-29T22%3A00%3A27Z&sr=c&sp=rl&sig=0vLycIw%2Fmu7DK%2FZDVAd7hQMaT5B7b6Kccrahma8C6kk%3D'; -- Remove starting question mark
  go
  
  create external data source [Azure-Storage]
  with 
  ( 
  	type = blob_storage,
   	location = 'https://dmstore2.blob.core.windows.net/tpch',
   	credential= [Azure-Storage-Credentials]
  );
  
  set nocount on;
  go
  
  bulk insert [test].[ORDERS]
  from '10GB/orders.tbl'
  with
  (
  	data_source = 'Azure-Storage',
  	fieldterminator = '|',
  	batchsize=10000
  )
  go
  
  /*
  	========
  	RUN DEMO
  	========
  */
  
  select distinct datefromparts(year(O_ORDERDATE),1,1) from [test].[ORDERS] order by 1
  go
  
  create partition function pf_OrderDate(date)
  as range right for values 
  ('1992-01-01', '1993-01-01', '1994-01-01', '1995-01-01', '1996-01-01', '1997-01-01', '1998-01-01')
  go
  
  create partition scheme ps_OrderDate 
  as partition pf_OrderDate
  all to ([Primary])
  go
  
  create clustered index IXC on [test].[ORDERS] (O_ORDERKEY, O_ORDERDATE) 
  on ps_OrderDate(O_ORDERDATE) 
  go
  
  select count(*) from [test].[ORDERS] where O_ORDERDATE = '1995-02-07'
  go
  
  select $partition.pf_OrderDate('1992-01-01')
  go
  
  /*
  	truncate a partition
  */
  truncate table [test].[ORDERS] with (partitions (2)) 
  go
  
  select * from [test].[ORDERS] where O_ORDERDATE = '1992-01-07'
  go
  
  /*
  	switch partition
  */
  drop table if exists [test].[ORDERS_1995];
  create table [test].[ORDERS_1995]
  (
  	[O_ORDERKEY] [int] not null,
  	[O_CUSTKEY] [int] not null,
  	[O_ORDERSTATUS] [char](1) not null,
  	[O_TOTALPRICE] [decimal](15, 2) not null,
  	[O_ORDERDATE] [date] not null,
  	[O_ORDERPRIORITY] [char](15) not null,
  	[O_CLERK] [char](15) not null,
  	[O_SHIPPRIORITY] [int] not null,
  	[O_COMMENT] [varchar](79) not null,
  	[O_ORDERYEAR] as (datepart(year,[O_ORDERDATE])) persisted,
  	[O_ORDERMONTH] as (datepart(month,[O_ORDERDATE]))
  )
  go
  
  create clustered index IXC on [test].[ORDERS_1995] (O_ORDERKEY, O_ORDERDATE) 
  go
  
  alter table [test].[ORDERS_1995]
  add constraint check_year_1995
  check (O_ORDERDATE >= '1995-01-01' and O_ORDERDATE < '1996-01-01')
  go
  
  select $partition.pf_OrderDate('1995-01-01')
  go
  
  -- Switch Out
  alter table test.[ORDERS]
  switch partition 5 to test.[ORDERS_1995]
  go
  
  select * from [test].[ORDERS] where O_ORDERDATE = '1995-01-07'
  go
  
  select * from [test].[ORDERS_1995] where O_ORDERDATE = '1995-01-07'
  go
  
  -- Switch In
  alter table test.[ORDERS_1995]
  switch to [test].[ORDERS] partition 5
  go
  
  select * from [test].[ORDERS] where O_ORDERDATE = '1995-01-07'
  go
  
  select * from [test].[ORDERS_1995] where O_ORDERDATE = '1995-01-07'
  go
  
  