MarkPryceMaherMSFT

-- Run against master database
SELECT  db.name [Database]
,	    ds.edition [Edition]
,	    ds.service_objective [Service Objective]
FROM    sys.database_service_objectives   AS ds
JOIN    sys.databases                     AS db ON ds.database_id = db.database_id;

MarkPryceMaherMSFT

-- distributions
 select * from   sys.pdw_distributions

MarkPryceMaherMSFT

create table test_table 
(
	col_a int,
	col_b int
) with ( distribution=round_robin)

declare @table_object_id bigint;

select object_id, * from sys.tables where name like 'test_table'
select @table_object_id = object_id from sys.tables where name like 'test_table'

MarkPryceMaherMSFT

/*
drop table [dbo].[round_robin_test1] 

create table [dbo].[round_robin_test1]  
with ( distribution = round_robin) as select * from [dbo].[lineitem_source] 
where 1 = 0

drop table [dbo].[round_robin_test1] 

create table [dbo].[round_robin_test1]  

MarkPryceMaherMSFT

/* 
create table [dbo].[round_robin_test1]  
with ( distribution = round_robin) as select * from [dbo].[lineitem_source] where 1 = 0 

insert into [dbo].[round_robin_test1]  
	 select top 157 * from [dbo].[lineitem_source] 
*/
exec gettbldetails '%round_robin_test1%'

SELECT

MarkPryceMaherMSFT

/* 
create table [dbo].[round_robin_test1]  
with ( distribution = round_robin) as select * from [dbo].[lineitem_source] where 1 = 0 

insert into [dbo].[round_robin_test1]  
	 select top 158 * from [dbo].[lineitem_source] -- ( just slight more than 32kb)
*/
--exec gettbldetails '%round_robin_test1%'

SELECT

MarkPryceMaherMSFT

create PROC [dbo].[gettbldetails] @tblname [varchar](255) AS
begin

SELECT
    [Fully Entity Name]                 = t.full_entity_name,
    [Schema Name]                       = t.schema_name,
    [Entity Name]                       = t.entity_name,
    [Current Distribution Method]       = t.distribution_method,
    [Current Distribution Column]       = ISNULL(t.distribution_column, '-'),

MarkPryceMaherMSFT

Create PROC [dbo].[wait_for] @seconds [bigint] AS
begin
	declare @quit bit  = 0;
	declare @ConpareDateTime datetime2;
	set @ConpareDateTime = dateadd(s, @seconds, getdate() )

	while(@quit=0)
	begin
		if getdate() > @ConpareDateTime
			set @quit = 1;

MarkPryceMaherMSFT

-- size
SELECT DATABASEPROPERTYEX (DB_NAME(), 'ServiceObjective' ) as ServiceObjective
-- How many nodes....
select * from sys.dm_pdw_nodes; -- DWU100 to DWU400

-- distributions
 select * from   sys.pdw_distributions

MarkPryceMaherMSFT

--exec profile_table  'dbo','Copy_into_example_c'

/*
Proc to profile the data in a table.
This proc will give to the min and max values in every column, with is useful when selecting the right numberic data type - bit/int/bigint

It will give you the length of the of the value, which is useful when picking the right variable length datatype - char/varchar/nvarchar

It will also give you the number of distinct values, which is useful for selecting a distribution column.