AdventureWorks

a bicycle manufacturer - Adventure Works Cycles

Scenarios include Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources

大略可分為9組DR關聯圖

Business Entities

People

Human Resources

Products

Manufacturing

Purchasing

Inventory

Sales

Admin

AdventureWorks – Data Dictionary

https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/home.html

Query Table Description from AdventureWorks Database

SELECT 

TABLE_NAME AS TableName, 

    --TABLE_TYPE AS tTableType,

    --q.epTableName,

    q.epExtendedProperty AS ExtendedProperty

FROM information_schema.tables AS t

    LEFT OUTER JOIN (SELECT OBJECT_NAME(ep.major_id) AS [epTableName],

        CAST(ep.Value AS nvarchar(500)) AS [epExtendedProperty]

        FROM sys.extended_properties ep

        WHERE ep.name = N'MS_Description' AND ep.minor_id = 0) As q

    ON t.table_name = q.epTableName 

WHERE TABLE_TYPE = N'BASE TABLE' 

ORDER BY TABLE_NAME

TableName	ExtendedProperty
Address	Street address information for customers, employees, and vendors.
AddressType	Types of addresses stored in the Address table.
AWBuildVersion	Current version number of the AdventureWorks 2016 sample database.
BillOfMaterials	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
BusinessEntity	Source of the ID that connects vendors, customers, and employees with address and contact information.
BusinessEntityAddress	Cross-reference table mapping customers, vendors, and employees to their addresses.
BusinessEntityContact	Cross-reference table mapping stores, vendors, and employees to people
ContactType	Lookup table containing the types of business entity contacts.
CountryRegion	Lookup table containing the ISO standard codes for countries and regions.
CountryRegionCurrency	Cross-reference table mapping ISO currency codes to a country or region.
CreditCard	Customer credit card information.
Culture	Lookup table containing the languages in which some AdventureWorks data is stored.
Currency	Lookup table containing standard ISO currencies.
CurrencyRate	Currency exchange rates.
Customer	Current customer information. Also see the Person and Store tables.
DatabaseLog	Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.
Department	Lookup table containing the departments within the Adventure Works Cycles company.
Document	Product maintenance documents.
EmailAddress	Where to send a person email.
Employee	Employee information such as salary, department, and title.
EmployeeDepartmentHistory	Employee department transfers.
EmployeePayHistory	Employee pay history.
ErrorLog	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.
Illustration	Bicycle assembly diagrams.
JobCandidate	Résumés submitted to Human Resources by job applicants.
Location	Product inventory and manufacturing locations.
Password	One way hashed authentication information
Person	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
PersonCreditCard	Cross-reference table mapping people to their credit card information in the CreditCard table.
PersonPhone	Telephone number and type of a person.
PhoneNumberType	Type of phone number of a person.
Product	Products sold or used in the manfacturing of sold products.
ProductCategory	High-level product categorization.
ProductCostHistory	Changes in the cost of a product over time.
ProductDescription	Product descriptions in several languages.
ProductDocument	Cross-reference table mapping products to related product documents.
ProductInventory	Product inventory information.
ProductListPriceHistory	Changes in the list price of a product over time.
ProductModel	Product model classification.
ProductModelIllustration	Cross-reference table mapping product models and illustrations.
ProductModelProductDescriptionCulture	Cross-reference table mapping product descriptions and the language the description is written in.
ProductPhoto	Product images.
ProductProductPhoto	Cross-reference table mapping products and product photos.
ProductReview	Customer reviews of products they have purchased.
ProductSubcategory	Product subcategories. See ProductCategory table.
ProductVendor	Cross-reference table mapping vendors with the products they supply.
PurchaseOrderDetail	Individual products associated with a specific purchase order. See PurchaseOrderHeader.
PurchaseOrderHeader	General purchase order information. See PurchaseOrderDetail.
SalesOrderDetail	Individual products associated with a specific sales order. See SalesOrderHeader.
SalesOrderHeader	General sales order information.
SalesOrderHeaderSalesReason	Cross-reference table mapping sales orders to sales reason codes.
SalesPerson	Sales representative current information.
SalesPersonQuotaHistory	Sales performance tracking.
SalesReason	Lookup table of customer purchase reasons.
SalesTaxRate	Tax rate lookup table.
SalesTerritory	Sales territory lookup table.
SalesTerritoryHistory	Sales representative transfers to other sales territories.
ScrapReason	Manufacturing failure reasons lookup table.
Shift	Work shift lookup table.
ShipMethod	Shipping company lookup table.
ShoppingCartItem	Contains online customer orders until the order is submitted or cancelled.
SpecialOffer	Sale discounts lookup table.
SpecialOfferProduct	Cross-reference table mapping products to special offer discounts.
StateProvince	State and province lookup table.
Store	Customers (resellers) of Adventure Works products.
TransactionHistory	Record of each purchase order, sales order, or work order transaction year to date.
TransactionHistoryArchive	Transactions for previous years.
UnitMeasure	Unit of measure lookup table.
Vendor	Companies from whom Adventure Works Cycles purchases parts or other goods.
WorkOrder	Manufacturing work orders.
WorkOrderRouting	Work order details.

SELECT objname, name, value  

FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'WorkOrder', 'column', default);  

GO

objname	name	value
WorkOrderID	MS_Description	Primary key for WorkOrder records.
ProductID	MS_Description	Product identification number. Foreign key to Product.ProductID.
OrderQty	MS_Description	Product quantity to build.
StockedQty	MS_Description	Quantity built and put in inventory.
ScrappedQty	MS_Description	Quantity that failed inspection.
StartDate	MS_Description	Work order start date.
EndDate	MS_Description	Work order end date.
DueDate	MS_Description	Work order due date.
ScrapReasonID	MS_Description	Reason for inspection failure.
ModifiedDate	MS_Description	Date and time the record was last updated.

SELECT objname, name, value  

FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', 'SalesOrderHeader', 'column', default);  

GO

objname	name	value
SalesOrderID	MS_Description	Primary key.
RevisionNumber	MS_Description	Incremental number to track changes to the sales order over time.
OrderDate	MS_Description	Dates the sales order was created.
DueDate	MS_Description	Date the order is due to the customer.
ShipDate	MS_Description	Date the order was shipped to the customer.
Status	MS_Description	Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlag	MS_Description	0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber	MS_Description	Unique sales order identification number.
PurchaseOrderNumber	MS_Description	Customer purchase order number reference.
AccountNumber	MS_Description	Financial accounting number reference.
CustomerID	MS_Description	Customer identification number. Foreign key to Customer.BusinessEntityID.
SalesPersonID	MS_Description	Sales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID.
TerritoryID	MS_Description	Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressID	MS_Description	Customer billing address. Foreign key to Address.AddressID.
ShipToAddressID	MS_Description	Customer shipping address. Foreign key to Address.AddressID.
ShipMethodID	MS_Description	Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardID	MS_Description	Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCode	MS_Description	Approval code provided by the credit card company.
CurrencyRateID	MS_Description	Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotal	MS_Description	Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmt	MS_Description	Tax amount.
Freight	MS_Description	Shipping cost.
TotalDue	MS_Description	Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Comment	MS_Description	Sales representative comments.
rowguid	MS_Description	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	MS_Description	Date and time the record was last updated.

change the datatype and size

change the datatype and size 轉換資料型態注意事項

以NVARCHAR轉換成VARCHAR為例

轉換資料型別

When you change the datatype from nvarchar to varchar, and the column contains the Unicode string, then SQL Server returns the error and terminates the statement.

when converting from NVARCHAR to VARCHAR, as this may lead to data loss if the original data contains characters that are not supported in VARCHAR

轉換資料長度大小

decrease the size of the column, the SQL Server will check the data of the table, and if the length of the data is higher than the new length, it returns the warning and terminate the statement

CREATE TABLE [dbo].[Table_2](

[c1] [int] NULL,

[c2] [nvarchar](10) NULL

)

-- find the rows that can't be converted (利用明確轉換 Explicit conversion來檢查資料是否可以正確轉換)

SELECT t.*

FROM [dbo].[Table_2] t

WHERE NULLIF(REPLACE(TRY_CONVERT(varchar(max), t.c2), '?',''), '') IS NULL

-- 使用ALTER TABLE語法變更型別並對資料進行隱含轉換 (隱含轉換 Implicit conversion)

ALTER TABLE [dbo].[Table_2] ALTER COLUMN c2 varchar(20) ;

-- 轉換時目的地的資料長度，建議指定為兩倍，因為當nvarchar(10)裡面的值如果是中文且長度LEN有超過5，轉換成varchar(10)則會失敗出現以下錯誤，因為varchar(10)只能儲存5個中文字元

ALTER TABLE [dbo].[Table_2] ALTER COLUMN c2 varchar(10) ;

Msg 2628, Level 16, State 1, Line 6

String or binary data would be truncated in table 'TESTDB.dbo.Table_2', column 'c2'. Truncated value: ''.

The statement has been terminated.

-- 若要採用明確轉換 Explicit conversion，則可使用INSERT SELECT與rename Table方式完成資料轉換轉換

A. Change the data type of a column

The following example changes a column of a table from INT to DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT) ;

GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;

GO

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;

GO

DROP TABLE dbo.doc_exy ;

GO

B. Change the size of a column

The following example increases the size of a varchar column and the precision and scale of a decimal column. Because the columns contain data, the column size can only be increased. Also notice that col_a is defined in a unique index. The size of col_a can still be increased because the data type is a varchar and the index isn't the result of a PRIMARY KEY constraint.

-- Create a two-column table with a unique index on the varchar column.

CREATE TABLE dbo.doc_exy (col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2)) ;

GO

INSERT INTO dbo.doc_exy VALUES ('Test', 99.99) ;

GO

-- Verify the current column size.

SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale

FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy') ;

GO

-- Increase the size of the varchar column.

ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25) ;

GO

-- Increase the scale and precision of the decimal column.

ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4) ;

GO

-- Insert a new row.

INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;

GO

-- Verify the current column size.

SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale

FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy') ;

ALTER TABLE (Transact-SQL)

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16

Data type conversion (Database Engine)

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16

SQL Server Analysis Service

 SQL Server Analysis Service

DAX and MDX

DAX and MDX

SQL Server Plan Cache

 SQL Server Plan Cache 執行計畫Cache

SQL Server Planning, Pricing and License

 

Server-CALs授權模式

1.需購買的量應該是所有終端用戶的電腦

例如:一台Data warehouse主機 與 一台Web報表主機，但公司有50人或電腦會連進Web報表主機開啟報表，則應該每一台用戶端電腦都需要有CALs授權，Data warehouse主機購買Server授權

2.此授權模式的SQL Server的CPU(Processor)限制只能用到20 cores，如需大量運算或高負載環境可能會有效能不足狀況

Enterprise edition with Server + Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance. (This licensing isn't available for new agreements.) There are no limits under the Core-based Server Licensing model.

Feature Enterprise Standard Web Express with Advanced Services Express Maximum compute capacity used by a single instance - SQL Server Database Engine 1 Operating system maximum Limited to lesser of 4 sockets or 24 cores Limited to lesser of 4 sockets or 16 cores Limited to lesser of 1 socket or 4 cores Limited to lesser of 1 socket or 4 cores Maximum compute capacity used by a single instance - Analysis Services or Reporting Services Operating system maximum Limited to lesser of 4 sockets or 24 cores Limited to lesser of 4 sockets or 16 cores Limited to lesser of 1 socket or 4 cores Limited to lesser of 1 socket or 4 cores Maximum memory for buffer pool per instance of SQL Server Database Engine Operating System Maximum 128 GB 64 GB 1410 MB 1410 MB

Editions and supported features of SQL Server 2019

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16

SQL Server edition	Maximum compute capacity for a single instance (SQL Server Database Engine)	Maximum compute capacity for a single instance (AS, RS)
Enterprise edition: Core-based licensing 1	Operating system maximum	Operating system maximum
Developer	Operating system maximum	Operating system maximum
Standard	Limited to lesser of 4 sockets or 24 cores	Limited to lesser of 4 sockets or 24 cores
Express	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores

1 Enterprise edition with Server + Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance. (This licensing isn't available for new agreements.) There are no limits under the Core-based Server Licensing model.

Compute capacity limits by edition of SQL Server

https://learn.microsoft.com/en-us/sql/sql-server/compute-capacity-limits-by-edition-of-sql-server?view=sql-server-ver16

SQL Server 2019 pricing

https://www.microsoft.com/en-us/sql-server/sql-server-2019-pricing

https://download.microsoft.com/download/f/0/d/f0d7004e-9e39-4991-853b-2aa09e4ce456/SQL%20Server%202019%20%20Licensing%20Datasheet.pdf

SQL Server Failover Cluster Instance只需要買Active Instance的SQL Server License

2022/5詢問零壹，現在規定須再加買3年SA才合規(以前SA一次可以只買一年，現在SA改成一次須買3年)

How much CPU does Microsoft’s SQL Server use?

https://www.heroix.com/blog/how-much-cpu-does-microsofts-sql-server-2016-use-2/