/******************************************************
 *
 * Name:         supporting-sharding-article-queries.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     12-11-2022
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Exploring database sharding.
 *
 ******************************************************/



--
-- query S1 - database properties
--

select 
  name, 
  database_id, 
  owner_sid, 
  create_date, 
  compatibility_level, 
  collation_name 
from sys.databases
go


--
-- query S2 - database file size
--

select
  database_id,
  file_id,
  type,
  type_desc,
  name,
  round(((cast(size as float)*8)/1024/1024), 2) as file_size_gb,
  round(((cast(growth as float)*8)/1024/1024), 2) as file_growth_gb
from sys.master_files where database_id = 6
go



--
-- query A1
--

select s.name, o.name, o.type_desc
from sys.objects o join sys.schemas s 
on o.schema_id = s.schema_id
where is_ms_shipped = 0 and s.name in ('DIM', 'FACT')
order by o.type



--
-- query A2
-- 

select
  db_name() as the_database_name,
  s.name as the_schema_name,
  t.name as the_table_name,
  OBJECTPROPERTYEX(t.object_id, 'Cardinality') 
  as the_row_count
from
  sys.tables t 
join 
  sys.schemas s 
on 
  t.schema_id = s.schema_id
where 
  is_ms_shipped = 0 and s.name in ('DIM', 'FACT');



--
-- query A3
--

select 
  o.type_desc as object_type, 
  count(*) as object_count
from sys.objects o join sys.schemas s 
on o.schema_id = s.schema_id
where is_ms_shipped = 0 and s.name in ('DIM', 'FACT')
group by o.type_desc
order by o.type_desc



--
-- query A4
--

CREATE DATABASE [BIG_JONS_BBQ_DW2] ON
(FILENAME = 'C:\MSSQL\DATA\BIG_JONS_BBQ_DW2.mdf'),
(FILENAME = 'C:\MSSQL\LOG\BIG_JONS_BBQ_DW2.ldf')
FOR ATTACH ;


--
-- Clear cache + buffers
--

DBCC FREEPROCCACHE;
GO

DBCC DROPCLEANBUFFERS;
GO


--
-- query A5
--

SELECT 
  c.cus_qtr_key, 
  count(*) as cus_total 
FROM 
  [FACT].[CUSTOMERS] c
GROUP BY 
  c.cus_qtr_key
ORDER BY 
  c.cus_qtr_key;



--
-- query A6
--

SELECT 
  c.cus_qtr_key, 
  c.cus_package_key, 
  count(*) as cus_total 
FROM 
  [FACT].[CUSTOMERS] c
GROUP BY 
  c.cus_qtr_key,
  c.cus_package_key
ORDER BY 
  c.cus_qtr_key,
  c.cus_package_key;



--
-- query A7
--

DELETE 
FROM [FACT].[CUSTOMERS]
WHERE cus_qtr_key = 2011001



--
-- query A8
--

USE [BBQ_SHARD_REPORTING]; 
GO

CREATE SCHEMA FACT AUTHORIZATION DBO;
GO

CREATE SCHEMA DIM AUTHORIZATION DBO;
GO

SELECT * 
INTO [DIM].[DIM_DATE]
FROM [BIG_JONS_BBQ_DW].[DIM].[DIM_DATE];

SELECT * 
INTO [DIM].[PIG_PACKAGES]
FROM [BIG_JONS_BBQ_DW].[DIM].[PIG_PACKAGES];

-- WITH SCHEMABINDING AS 
EXECUTE ('ALTER VIEW [FACT].[CUSTOMERS] AS 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2011001].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2011002].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2011003].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2011004].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2012001].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2012002].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2012003].[FACT].[CUSTOMERS]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [BBQ_SHARD_2012004].[FACT].[CUSTOMERS]
 ');


--
-- query A9
--


-- Declare local variables
DECLARE @VAR_YEAR INT = 2011;
DECLARE @VAR_QTR INT = 1;
DECLARE @VAR_TAG CHAR(7) = '2011001';
DECLARE @VAR_NAME VARCHAR(64);
DECLARE @VAR_STMT VARCHAR(MAX);

-- For two years
WHILE (@VAR_YEAR < 2013)
BEGIN

    -- Set to one
    SET @VAR_QTR = 1;

    -- For four quarters
    WHILE (@VAR_QTR < 5)
    BEGIN

	   --  Make up the database name
	   SELECT @VAR_TAG = @VAR_YEAR * 1000 + @VAR_QTR;
	   --PRINT @VAR_TAG


           --
           -- Create View - Fact Table
           --

	   SET @VAR_STMT = '';
           SET @VAR_STMT = @VAR_STMT + 'USE [BBQ_SHARD_REPORTING]; ';

	   SET @VAR_STMT = @VAR_STMT + 'EXECUTE (' + CHAR(39) 
	   SET @VAR_STMT = @VAR_STMT + 'DROP VIEW IF EXISTS [FACT].[CUSTOMERS_' + @VAR_TAG + ']' 
	   SET @VAR_STMT = @VAR_STMT +  CHAR(39) + ');'

	   SET @VAR_STMT = @VAR_STMT + 'EXECUTE (' + CHAR(39) 
	   SET @VAR_STMT = @VAR_STMT + 'CREATE VIEW [FACT].[CUSTOMERS_' + @VAR_TAG + '] AS '   -- ' WITH SCHEMABINDING AS '
	   SET @VAR_STMT = @VAR_STMT + '    SELECT '
	   SET @VAR_STMT = @VAR_STMT + '      [cus_id]  '
           SET @VAR_STMT = @VAR_STMT + '     ,[cus_lname] ' 
           SET @VAR_STMT = @VAR_STMT + '     ,[cus_fname]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_phone]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_address]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_city]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_state]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_zip]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_package_key]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_start_date_key]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_end_date_key]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_date_str]  '
	   SET @VAR_STMT = @VAR_STMT + '     ,[cus_qtr_key]  '
	   SET @VAR_STMT = @VAR_STMT + ' FROM [BBQ_SHARD_' +  @VAR_TAG + '].[FACT].[CUSTOMERS] '
	   SET @VAR_STMT = @VAR_STMT + CHAR(39) + ');'

	   -- Execute the TSQL
	   EXECUTE (@VAR_STMT);

	   -- Debugging
	   PRINT @VAR_STMT;

	   -- Increment the quarter
	   SET @VAR_QTR = @VAR_QTR + 1

	END

	-- Increment the year
	SET @VAR_YEAR = @VAR_YEAR + 1
END
GO


--
--  Output if you print, instead of exec tsql
--


-- WITH SCHEMABINDING AS 

EXECUTE ('CREATE VIEW [FACT].[CUSTOMERS] AS 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2011001]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2011002]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2011003]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2011004]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2012001]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2012002]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2012003]
 UNION ALL 
 SELECT 
      [cus_id] 
     ,[cus_lname] 
     ,[cus_fname] 
     ,[cus_phone] 
     ,[cus_address] 
     ,[cus_city] 
     ,[cus_state] 
     ,[cus_zip] 
     ,[cus_package_key] 
     ,[cus_start_date_key] 
     ,[cus_end_date_key] 
     ,[cus_date_str] 
     ,[cus_qtr_key] 
 FROM [FACT].[CUSTOMERS_2012004]
 ');