The challenges involved in changing int identity column (i.e. int identity(1,1)) to bigint depends on size of the database and references to identity key in other tables and objects such as user defined procedure/function, sequence and indexes (cluster non cluster).
Let’s try changing identity column data type from int to bigint using SQL Server AdventureWorksLT2022 sample database (https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2022.bak).
Approaches
There are number of way of achieving the desired outcome and various techniques have been noted in various articles in the internet.
- Using ALTER table command – provided the table is small and recreating indexes will not impact database performance.
- Reseeding int and start from -1 to – max int value
- Adding a field to impacted table and carryout relevant changes.
- Create a duplicate table with same structure except changing int to bigint.
- Copy data into the new table.
- Keep it sync up to the final cutover. Swap the tables as required.
- You have to repeat the process for each table needed updating and corresponding foreign key in other tables
- Create a new database and generate tables and objects using modified DDL script. Copy data into relevant tables and keep new database in sync using SQL Server CDC.
Preferred approach using ETL script
Changing data type of a database with large datasets (over terabytes) need careful planning. The process needs to be executed few times on dev/test database before applying the techniques on final database. A good plan and preparation would minimise downtime and the risk of getting things wrong. The new database needs to run in parallel for good few weeks. Data validation check must be carried out to ensure the schema changes does not affect other systems or applications or break database objects such as sequences, user defined functions and procedures. Data check is needed for for missing data or row count.
Steps for creating tables and objects
- Generate DDL script of current database using SSMS (Task -> Generate Script (Use Advanced and select Drop and Create and Database Name as False and leave other as default values)
- Edit the schema and change Int to BigInt in the identity column and other places where it has been referenced
- Create a blank database and run the modified DDL script to generate tables and other objects
- Copy data from the source database to the newly created database. You may use SQL Server Import Utility tool or custom ETL script to copy data to your new database. If you are using SQL Server Import, then make sure you tick the identity column to True, and you have to do this foreach identity column tables. Or you may use a custom ETL script for the copying data from source to target.
- Assuming your source database has Change Data Capture (CDC) is enabled for the database and tables. Make sure SQL Server Agent is running.
- Use CDC data to bring your target database in sync.
For large tables, copy data in batches. Do lots of preparation and testing before the final cutover. Keep running the target database in parallel for a few weeks.
Validate the target database – ensure there are no missing data, views, user defined functions, and procedures work as expected. Check the row count of each table and make sure they match.
ETL Script
import pymssql
def get_connection(database):
conn = pymssql.connect(host ='localhost',
user = r'user',
password = r'password',
database = database
)
return conn
# utility methods
def get_table_schema(tablename):
# get 1st row
conn = get_connection(target_database)
query = f"""select top 1 * from {tablename}"""
cursor = conn.cursor(as_dict=True)
cursor.execute(query)
columns = [i[0] for i in cursor.description if i[0] not in ['LineTotal','TotalDue','SalesOrderNumber']]
columns = ','.join(columns)
conn.close()
return columns
# get list of tables with identity column
# list of tables without entity - WHERE NOT EXISTS
def get_table_names(not_identity=''):
query = f"""
SELECT
CONCAT(s.name,'.',t.name) as tablename,
case
when i.is_identity is not null then 1
else 0
end as is_identity
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
LEFT JOIN sys.identity_columns AS i
ON t.[object_id] = i.[object_id]
"""
conn = get_connection(target_database)
# Create a connection object using a jdbc-url, + sql uname & pass
cursor = conn.cursor(as_dict=True)
# execute query
cursor.execute(query)
results = cursor.fetchall()
conn.close()
#tables = [row['tablename'] for row in results]
return results
def copy_data(tablename,columns,is_identity=1):
if is_identity == 1:
statement = f"""
SET IDENTITY_INSERT {tablename} ON;
INSERT INTO {tablename} ({columns})
SELECT {columns} FROM {source_database}.{tablename};
SET IDENTITY_INSERT {tablename} OFF;
"""
else:
statement = f"""
INSERT INTO {tablename} ({columns})
SELECT {columns} FROM {source_database}.{tablename};
"""
# Create a connection object using a jdbc-url, + sql uname & pass
conn = get_connection(target_database)
cursor = conn.cursor(as_dict=True)
# Create callable statement and execute it
cursor.execute(statement)
conn.commit()
# Close connection
conn.close()
return True
# load data to each empty table - initial
def load_data():
results = get_table_names()
tables = [row['tablename'] for row in results]
for row in results:
tablename = row['tablename']
is_identity = row['is_identity']
if "history." not in tablename:
columns = get_table_schema(tablename)
try:
rc = copy_data(tablename,columns,is_identity)
print('data load completed for:',tablename)
except Exception as inst:
print('data not loaded:',tablename)
print(inst)
else:
print('data from history schema is not loaded:',tablename)
return True
# main
# exclude SalesOrderNumber, LineTotal and TotalDue (calculated fields)
source_database = 'AdventureWorksLT2022'
target_database = 'LT'
# load data into tables with identity
rc = load_data()
Change Data Capture (CDC)
SQL Server provides a feature for capturing data changes (CDC) in a database. You can enable CDC on a tble basis as well.
Steps
- Enable SQL Agent
- Enable CDC on database
- Enable CDC on tables
SQL Script
USE AdventureWorksLT2022;
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
@source_schema = N'SalesLT',
@source_name = N'Customer',
@role_name = NULL;
Insert data into SalesLT.Customer table
USE AdventureWorksLT2022;
SET IDENTITY_INSERT AdventureWorksLT2022.SalesLT.Customer ON;
insert into AdventureWorksLT2022.SalesLT.Customer
(CustomerID,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,CompanyName,SalesPerson,EmailAddress,Phone,PasswordHash,PasswordSalt)
select
CustomerID + 1220000 as CustomerID,
NameStyle,Title,FirstName,MiddleName,LastName,Suffix,CompanyName,SalesPerson,EmailAddress,Phone,PasswordHash,PasswordSalt
from AdventureWorksLT2022.SalesLT.Customer;
SET IDENTITY_INSERT AdventureWorksLT2022.SalesLT.Customer OFF;Validate insert
Run the following SQL command to carry out rows count
CREATE TABLE #counts
(
table_name varchar(255),
row_count int,
database_name varchar(255)
);
USE LT;
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count, database_name)
SELECT ''?'', COUNT(*), ''LT'' FROM ?';
USE AdventureWorksLT2022;
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count, database_name)
SELECT ''?'', COUNT(*), ''AdventureWorksLT2022'' FROM ?';
SELECT table_name, row_count, database_name FROM #counts ORDER BY table_name, row_count DESC
Output after insert on CDC enabled table
Applying CDC on new database
Using MERGE statement
--using merge
USE AdventureWorksLT2022;
DECLARE @CustomerID BIGINT = 1
DECLARE @begin_time datetime = GETDATE()-1
DECLARE @end_time datetime = GETDATE() + 1
DECLARE @begin_lsn binary(10) = (select sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time))
DECLARE @end_lsn binary(10) = (select sys.fn_cdc_map_time_to_lsn('largest less than', @end_time))
SET IDENTITY_INSERT LT.SalesLT.Customer ON;
MERGE INTO LT.SalesLT.Customer tgt
USING (
SELECT *
FROM cdc.fn_cdc_get_net_changes_SalesLT_Customer(@begin_lsn, @end_lsn, 'all')
) src ON tgt.CustomerID = src.CustomerID
WHEN MATCHED THEN
UPDATE SET
tgt.ModifiedDate = src.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,CompanyName,SalesPerson,EmailAddress,Phone,PasswordHash,PasswordSalt)
VALUES (src.CustomerID,src.NameStyle,src.Title,src.FirstName,src.MiddleName,src.LastName,src.Suffix,
src.CompanyName,src.SalesPerson,src.EmailAddress,src.Phone,src.PasswordHash,src.PasswordSalt)
WHEN NOT MATCHED BY SOURCE THEN DELETE
;
SET IDENTITY_INSERT LT.SalesLT.Customer OFF;
COMMIT;Output
SQL Error [547] [23000]: The MERGE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeader_Customer_CustomerID". The conflict occurred in database "LT", table "SalesLT.SalesOrderHeader", column 'CustomerID'.Useful SQL scripts
-lists all Primary Key, Unique Keys, and other constraints on a database.
USE LT;
SELECT table_view,
object_type,
constraint_type,
constraint_name,
details
FROM (
SELECT schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
when i.[type] = 1 then 'Unique clustered index'
when i.type = 2 then 'Unique index'
end as constraint_type,
isnull(c.[name], i.[name]) as constraint_name,
substring(column_names, 1, len(column_names)-1) as [details]
FROM sys.objects t
left outer join sys.indexes i
on t.object_id = i.object_id
left outer join sys.key_constraints c
on i.object_id = c.parent_object_id
AND i.index_id = c.unique_index_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
AND ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
WHERE is_unique = 1
AND t.is_ms_shipped <> 1
UNION ALL
SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'Table',
'Foreign key',
fk.name as fk_constraint_name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name
FROM sys.foreign_keys fk
inner join sys.tables fk_tab
on fk_tab.object_id = fk.parent_object_id
inner join sys.tables pk_tab
on pk_tab.object_id = fk.referenced_object_id
inner join sys.foreign_key_columns fk_cols
on fk_cols.constraint_object_id = fk.object_id
UNION ALL
SELECT schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Check constraint',
con.[name] as constraint_name,
con.[definition]
FROM sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
AND con.parent_object_id = col.object_id
UNION ALL
SELECT schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition]
FROM sys.default_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id) a
ORDER BY table_view, constraint_type, constraint_name;
--lists all Primary Indexes on a database.
USE LT;
SELECT i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as index_type,
case when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [unique],
schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type]
FROM sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
AND ic.index_id = i.index_id
order by key_ordinal
for xml path ('') ) D (column_names)
WHERE t.is_ms_shipped <> 1
AND index_id > 0
ORDER BY i.[name];
--list asll CDC enabled tables
USE VE;
select
name,
is_tracked_by_cdc
from sys.tables
where is_tracked_by_cdc = 1;
--list constraints
USE AdventureWorksLT2022;
WITH ALL_FK AS (
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
)
SELECT * FROM ALL_FK
ORDER BY PK_Table;
PySpark (AWS Glue). script
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
## @params: [JOB_NAME]
args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
# job configuration
job = Job(glueContext)
job.init(args['JOB_NAME'], args)
# cponnection variables
url = 'jdbc:sqlserver://ecn-n-nnn-nn-nn.eu-west-1.compute.amazonaws.com:1433;database=AdventureWorks2016v1'
table = 'Person.Person'
user = 'user
password = 'password'
driver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
# utility methods
def get_table_schema(tablename):
# get 1st row
query = f"""(select top 1 * from {tablename}) as inputtable"""
df = (spark.read.format("jdbc")
.option("url", url)
.option("dbtable", query)
.option("user", user)
.option("password", password)
.option("driver", driver)
).load()
columns = ','.join(df.columns)
return columns
def copy_data(tablename,columns,is_identity=True):
if is_ientity:
statement = f"""
SET IDENTITY_INSERT {tablename} ON;
INSERT INTO {tablename} ({columns})
SELECT {columns} FROM VoucherEngine.{tablename};
SET IDENTITY_INSERT {tablename} OFF;
"""
else:
statement = f"""
INSERT INTO {tablename} ({columns})
SELECT {columns} FROM VoucherEngine.{tablename};
"""
# Fetch the driver manager from your spark context
driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
# Create a connection object using a jdbc-url, + sql uname & pass
con = driver_manager.getConnection(url, user, password)
# Create callable statement and execute it
exec_statement = con.prepareCall(statement)
exec_statement.execute()
# Close connections
exec_statement.close()
con.close()# Write your SQL statement as a string
return True
# list of tables with identity column
# list of tables without entity - WHERE NOT EXISTS
def get_table_names(not_identity=''):
query = f"""
(SELECT
CONCAT(s.name,'.',t.name) as tablename
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE {not_identity} EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
)
) as tables
"""
df_tables = (spark.read.format("jdbc")
.option("url", url)
.option("dbtable", query)
.option("user", user)
.option("password", password)
.option("driver", driver)
.option("numPartitions", "3")
).load()
tables = [row[0] for row in df_tables.collect()]
return tables
# load data to eachj empty table - initial
def load_data(is_identity=True):
if is_identity:
tables = get_table_names()
else:
tables = get_table_names('NOT')
for tablename in tables:
if "history." not in tablename:
columns = get_table_schema(tablename)
try:
rc = copy_data(tablename,columns,is_identity)
print('data load completed for:',tablename)
except:
print('data not loaded:',tablename)
else:
print('data from history schema is not loaded:',tablename)
return True
# load data into tables with identity
rc = load_data(is_identity=True)
# load data into tables without identity
rc = load_data(is_identity=False)
job.commit()DDL Script
--USE master;
--CREATE DATABASE VE;
USE LT;
CREATE SCHEMA SalesLT;
CREATE TYPE [dbo].[AccountNumber] FROM [nvarchar](15) NULL;
/****** Object: UserDefinedDataType [dbo].[Flag] Script Date: 6/19/2024 10:33:04 AM ******/
CREATE TYPE [dbo].[Flag] FROM [bit] NOT NULL;
/****** Object: UserDefinedDataType [dbo].[Name] Script Date: 6/19/2024 10:33:04 AM ******/
CREATE TYPE [dbo].[Name] FROM [nvarchar](50) NULL;
/****** Object: UserDefinedDataType [dbo].[NameStyle] Script Date: 6/19/2024 10:33:04 AM ******/
CREATE TYPE [dbo].[NameStyle] FROM [bit] NOT NULL;
/****** Object: UserDefinedDataType [dbo].[OrderNumber] Script Date: 6/19/2024 10:33:04 AM ******/
CREATE TYPE [dbo].[OrderNumber] FROM [nvarchar](25) NULL;
/****** Object: UserDefinedDataType [dbo].[Phone] Script Date: 6/19/2024 10:33:04 AM ******/
CREATE TYPE [dbo].[Phone] FROM [nvarchar](25) NULL;
-- CREATE SCHEMA SalesLT;
-- SalesLT.Address definition
-- Drop table
-- DROP TABLE SalesLT.Address;
CREATE TABLE SalesLT.Address (
AddressID bigint IDENTITY(1,1) NOT NULL,
AddressLine1 nvarchar(60) NOT NULL,
AddressLine2 nvarchar(60) NULL,
City nvarchar(30) NOT NULL,
StateProvince Name NOT NULL,
CountryRegion Name NOT NULL,
PostalCode nvarchar(15) NOT NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_Address_rowguid UNIQUE (rowguid),
CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID)
);
CREATE NONCLUSTERED INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion ON SalesLT.Address (AddressLine1, AddressLine2, City, StateProvince, PostalCode, CountryRegion);
CREATE NONCLUSTERED INDEX IX_Address_StateProvince ON SalesLT.Address (StateProvince);
-- SalesLT.Customer definition
-- Drop table
-- DROP TABLE SalesLT.Customer;
CREATE TABLE SalesLT.Customer (
CustomerID bigint IDENTITY(1,1) NOT NULL,
NameStyle NameStyle DEFAULT 0 NOT NULL,
Title nvarchar(8) NULL,
FirstName Name NOT NULL,
MiddleName Name NULL,
LastName Name NOT NULL,
Suffix nvarchar(10) NULL,
CompanyName nvarchar(128) NULL,
SalesPerson nvarchar(256) NULL,
EmailAddress nvarchar(50) NULL,
Phone Phone NULL,
PasswordHash varchar(128) NOT NULL,
PasswordSalt varchar(10) NOT NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_Customer_rowguid UNIQUE (rowguid),
CONSTRAINT PK_Customer_CustomerID PRIMARY KEY (CustomerID)
);
CREATE NONCLUSTERED INDEX IX_Customer_EmailAddress ON SalesLT.Customer (EmailAddress);
-- SalesLT.CustomerAddress definition
-- Drop table
-- DROP TABLE SalesLT.CustomerAddress;
CREATE TABLE SalesLT.CustomerAddress (
CustomerID bigint NOT NULL,
AddressID bigint NOT NULL,
AddressType Name NOT NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_CustomerAddress_rowguid UNIQUE (rowguid),
CONSTRAINT PK_CustomerAddress_CustomerID_AddressID PRIMARY KEY (CustomerID,AddressID)
);
-- SalesLT.CustomerAddress foreign keys
ALTER TABLE SalesLT.CustomerAddress ADD CONSTRAINT FK_CustomerAddress_Address_AddressID FOREIGN KEY (AddressID) REFERENCES SalesLT.Address(AddressID);
ALTER TABLE SalesLT.CustomerAddress ADD CONSTRAINT FK_CustomerAddress_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES SalesLT.Customer(CustomerID);
-- SalesLT.Product definition
-- Drop table
-- DROP TABLE SalesLT.Product;
CREATE TABLE SalesLT.Product (
ProductID bigint IDENTITY(1,1) NOT NULL,
Name Name NOT NULL,
ProductNumber nvarchar(25) NOT NULL,
Color nvarchar(15) NULL,
StandardCost money NOT NULL,
ListPrice money NOT NULL,
[Size] nvarchar(5) NULL,
Weight decimal(8,2) NULL,
ProductCategoryID bigint NULL,
ProductModelID bigint NULL,
SellStartDate datetime NOT NULL,
SellEndDate datetime NULL,
DiscontinuedDate datetime NULL,
ThumbNailPhoto varbinary(MAX) NULL,
ThumbnailPhotoFileName nvarchar(50) NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_Product_Name UNIQUE (Name),
CONSTRAINT AK_Product_ProductNumber UNIQUE (ProductNumber),
CONSTRAINT AK_Product_rowguid UNIQUE (rowguid),
CONSTRAINT PK_Product_ProductID PRIMARY KEY (ProductID)
);
ALTER TABLE SalesLT.Product WITH NOCHECK ADD CONSTRAINT CK_Product_StandardCost CHECK (([StandardCost]>=(0.00)));
ALTER TABLE SalesLT.Product WITH NOCHECK ADD CONSTRAINT CK_Product_ListPrice CHECK (([ListPrice]>=(0.00)));
ALTER TABLE SalesLT.Product WITH NOCHECK ADD CONSTRAINT CK_Product_Weight CHECK (([Weight]>(0.00)));
ALTER TABLE SalesLT.Product WITH NOCHECK ADD CONSTRAINT CK_Product_SellEndDate CHECK (([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL));
-- SalesLT.Product foreign keys
ALTER TABLE SalesLT.Product ADD CONSTRAINT FK_Product_ProductCategory_ProductCategoryID FOREIGN KEY (ProductCategoryID) REFERENCES SalesLT.ProductCategory(ProductCategoryID);
ALTER TABLE SalesLT.Product ADD CONSTRAINT FK_Product_ProductModel_ProductModelID FOREIGN KEY (ProductModelID) REFERENCES SalesLT.ProductModel(ProductModelID);
-- SalesLT.ProductCategory definition
-- Drop table
-- DROP TABLE SalesLT.ProductCategory;
CREATE TABLE SalesLT.ProductCategory (
ProductCategoryID bigint IDENTITY(1,1) NOT NULL,
ParentProductCategoryID bigint NULL,
Name Name NOT NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_ProductCategory_Name UNIQUE (Name),
CONSTRAINT AK_ProductCategory_rowguid UNIQUE (rowguid),
CONSTRAINT PK_ProductCategory_ProductCategoryID PRIMARY KEY (ProductCategoryID),
CONSTRAINT FK_ProductCategory_ProductCategory_ParentProductCategoryID_ProductCategoryID FOREIGN KEY (ParentProductCategoryID) REFERENCES SalesLT.ProductCategory(ProductCategoryID)
);
-- SalesLT.ProductDescription definition
-- Drop table
-- DROP TABLE SalesLT.ProductDescription;
CREATE TABLE SalesLT.ProductDescription (
ProductDescriptionID bigint IDENTITY(1,1) NOT NULL,
Description nvarchar(400) NOT NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_ProductDescription_rowguid UNIQUE (rowguid),
CONSTRAINT PK_ProductDescription_ProductDescriptionID PRIMARY KEY (ProductDescriptionID)
);
-- SalesLT.ProductModel definition
-- Drop table
-- DROP TABLE SalesLT.ProductModel;
CREATE TABLE SalesLT.ProductModel (
ProductModelID bigint IDENTITY(1,1) NOT NULL,
Name Name NOT NULL,
CatalogDescription xml NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_ProductModel_Name UNIQUE (Name),
CONSTRAINT AK_ProductModel_rowguid UNIQUE (rowguid),
CONSTRAINT PK_ProductModel_ProductModelID PRIMARY KEY (ProductModelID)
);
CREATE INDEX PXML_ProductModel_CatalogDescription ON SalesLT.ProductModel (CatalogDescription);
-- SalesLT.ProductModelProductDescription definition
-- Drop table
-- DROP TABLE SalesLT.ProductModelProductDescription;
CREATE TABLE SalesLT.ProductModelProductDescription (
ProductModelID bigint NOT NULL,
ProductDescriptionID bigint NOT NULL,
Culture nchar(6) NOT NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_ProductModelProductDescription_rowguid UNIQUE (rowguid),
CONSTRAINT PK_ProductModelProductDescription_ProductModelID_ProductDescriptionID_Culture PRIMARY KEY (ProductModelID,ProductDescriptionID,Culture)
);
-- SalesLT.ProductModelProductDescription foreign keys
ALTER TABLE SalesLT.ProductModelProductDescription ADD CONSTRAINT FK_ProductModelProductDescription_ProductDescription_ProductDescriptionID FOREIGN KEY (ProductDescriptionID) REFERENCES SalesLT.ProductDescription(ProductDescriptionID);
ALTER TABLE SalesLT.ProductModelProductDescription ADD CONSTRAINT FK_ProductModelProductDescription_ProductModel_ProductModelID FOREIGN KEY (ProductModelID) REFERENCES SalesLT.ProductModel(ProductModelID);
-- SalesLT.SalesOrderDetail definition
-- Drop table
-- DROP TABLE SalesLT.SalesOrderDetail;
CREATE TABLE SalesLT.SalesOrderDetail (
SalesOrderID bigint NOT NULL,
SalesOrderDetailID bigint IDENTITY(1,1) NOT NULL,
OrderQty smallint NOT NULL,
ProductID bigint NOT NULL,
UnitPrice money NOT NULL,
UnitPriceDiscount money DEFAULT 0.0 NOT NULL,
LineTotal AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_SalesOrderDetail_rowguid UNIQUE (rowguid),
CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY (SalesOrderID,SalesOrderDetailID)
);
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID ON SalesLT.SalesOrderDetail (ProductID);
ALTER TABLE SalesLT.SalesOrderDetail WITH NOCHECK ADD CONSTRAINT CK_SalesOrderDetail_OrderQty CHECK (([OrderQty]>(0)));
ALTER TABLE SalesLT.SalesOrderDetail WITH NOCHECK ADD CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK (([UnitPrice]>=(0.00)));
ALTER TABLE SalesLT.SalesOrderDetail WITH NOCHECK ADD CONSTRAINT CK_SalesOrderDetail_UnitPriceDiscount CHECK (([UnitPriceDiscount]>=(0.00)));
-- SalesLT.SalesOrderDetail foreign keys
ALTER TABLE SalesLT.SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_Product_ProductID FOREIGN KEY (ProductID) REFERENCES SalesLT.Product(ProductID);
ALTER TABLE SalesLT.SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID FOREIGN KEY (SalesOrderID) REFERENCES SalesLT.SalesOrderHeader(SalesOrderID) ON DELETE CASCADE;
-- SalesLT.SalesOrderHeader definition
-- Drop table
-- DROP TABLE SalesLT.SalesOrderHeader;
CREATE TABLE SalesLT.SalesOrderHeader (
SalesOrderID bigint IDENTITY(1,1) NOT NULL,
RevisionNumber tinyint DEFAULT 0 NOT NULL,
OrderDate datetime DEFAULT getdate() NOT NULL,
DueDate datetime NOT NULL,
ShipDate datetime NULL,
Status tinyint DEFAULT 1 NOT NULL,
OnlineOrderFlag Flag DEFAULT 1 NOT NULL,
SalesOrderNumber AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),
PurchaseOrderNumber OrderNumber NULL,
AccountNumber AccountNumber NULL,
CustomerID bigint NOT NULL,
ShipToAddressID bigint NULL,
BillToAddressID bigint NULL,
ShipMethod nvarchar(50) NOT NULL,
CreditCardApprovalCode varchar(15) NULL,
SubTotal money DEFAULT 0.00 NOT NULL,
TaxAmt money DEFAULT 0.00 NOT NULL,
Freight money DEFAULT 0.00 NOT NULL,
TotalDue AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
Comment nvarchar(MAX) NULL,
rowguid uniqueidentifier DEFAULT newid() NOT NULL,
ModifiedDate datetime DEFAULT getdate() NOT NULL,
CONSTRAINT AK_SalesOrderHeader_SalesOrderNumber UNIQUE (SalesOrderNumber),
CONSTRAINT AK_SalesOrderHeader_rowguid UNIQUE (rowguid),
CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY (SalesOrderID)
);
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON SalesLT.SalesOrderHeader (CustomerID);
ALTER TABLE SalesLT.SalesOrderHeader WITH NOCHECK ADD CONSTRAINT CK_SalesOrderHeader_Status CHECK (([Status]>=(0) AND [Status]<=(8)));
ALTER TABLE SalesLT.SalesOrderHeader WITH NOCHECK ADD CONSTRAINT CK_SalesOrderHeader_DueDate CHECK (([DueDate]>=[OrderDate]));
ALTER TABLE SalesLT.SalesOrderHeader WITH NOCHECK ADD CONSTRAINT CK_SalesOrderHeader_ShipDate CHECK (([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL));
ALTER TABLE SalesLT.SalesOrderHeader WITH NOCHECK ADD CONSTRAINT CK_SalesOrderHeader_SubTotal CHECK (([SubTotal]>=(0.00)));
ALTER TABLE SalesLT.SalesOrderHeader WITH NOCHECK ADD CONSTRAINT CK_SalesOrderHeader_TaxAmt CHECK (([TaxAmt]>=(0.00)));
ALTER TABLE SalesLT.SalesOrderHeader WITH NOCHECK ADD CONSTRAINT CK_SalesOrderHeader_Freight CHECK (([Freight]>=(0.00)));
-- SalesLT.SalesOrderHeader foreign keys
ALTER TABLE SalesLT.SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Address_BillTo_AddressID FOREIGN KEY (BillToAddressID) REFERENCES SalesLT.Address(AddressID);
ALTER TABLE SalesLT.SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Address_ShipTo_AddressID FOREIGN KEY (ShipToAddressID) REFERENCES SalesLT.Address(AddressID);
ALTER TABLE SalesLT.SalesOrderHeader ADD CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES SalesLT.Customer(CustomerID);
-- SalesLT.vGetAllCategories source
CREATE VIEW [SalesLT].[vGetAllCategories]
WITH SCHEMABINDING
AS
-- Returns the CustomerID, first name, and last name for the specified customer.
WITH CategoryCTE([ParentProductCategoryID], [ProductCategoryID], [Name]) AS
(
SELECT [ParentProductCategoryID], [ProductCategoryID], [Name]
FROM SalesLT.ProductCategory
WHERE ParentProductCategoryID IS NULL
UNION ALL
SELECT C.[ParentProductCategoryID], C.[ProductCategoryID], C.[Name]
FROM SalesLT.ProductCategory AS C
INNER JOIN CategoryCTE AS BC ON BC.ProductCategoryID = C.ParentProductCategoryID
)
SELECT PC.[Name] AS [ParentProductCategoryName], CCTE.[Name] as [ProductCategoryName], CCTE.[ProductCategoryID]
FROM CategoryCTE AS CCTE
JOIN SalesLT.ProductCategory AS PC
ON PC.[ProductCategoryID] = CCTE.[ParentProductCategoryID];
-- SalesLT.vProductAndDescription source
CREATE VIEW [SalesLT].[vProductAndDescription]
WITH SCHEMABINDING
AS
-- View (indexed or standard) to display products and product descriptions by language.
SELECT
p.[ProductID]
,p.[Name]
,pm.[Name] AS [ProductModel]
,pmx.[Culture]
,pd.[Description]
FROM [SalesLT].[Product] p
INNER JOIN [SalesLT].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [SalesLT].[ProductModelProductDescription] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [SalesLT].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
-- SalesLT.vProductModelCatalogDescription source
CREATE VIEW [SalesLT].[vProductModelCatalogDescription]
AS
SELECT
[ProductModelID]
,[Name]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace html="http://www.w3.org/1999/xhtml";
(/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience]
,[rowguid]
,[ModifiedDate]
FROM [SalesLT].[ProductModel]
WHERE [CatalogDescription] IS NOT NULL;
/*
Error occurred during SQL script execution
Reason:
SQL Error [8183] [S0001]: Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.
--Wporkaround -> remove NOT NULL as column will not bne NULL or PERSISTED NOT NULL<
SalesOrderNumber AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')) NOT NULL,
--
SalesOrderNumber AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),
https://stackoverflow.com/questions/45727140/sql-only-unique-or-primary-key-constraints-can-be-created-on-computed-columns
you cannot be modified because it is either a computed column or is the result of a UNION operator.
-> you define a computation rule, and then your RDBMS automatically manages it, computing values from other columns as needed.
**/