You plan to create a database that has multiple tables. The tables will contain product
information. Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters “ADV”
and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
A.
A FOREIGN KEY constraint
B.
A trigger
C.
A user-defined data type
D.
A CHECK constraint
I have read https://msdn.microsoft.com/en-us/library/ms175007.aspx and have no idea hot to make such type. I would go for D.
I think it is D
D
I think the answer C is correct and it refers to CLR data type. You could provide validation like here: http://keithelder.net/2007/10/29/creating-custom-sql-clr-userdefined-types/
D – CHECK
It could be done with user-defined data type (.net library).
However, “The solution must minimize the amount of development effort required.”
Easier and simpler would be CHECK constraint and it’s why D is better choice than C.
No, C is corret. For MULTILE TABLE you should use user-defined data type with a rule.
CREATE RULE [dbo].[sku]
AS
LEN(@sku) = 10 AND @sku like ‘ADV%’
GO
CREATE TYPE [dbo].[sku] FROM [char](10) NOT NULL;
GO
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[sku] [dbo].[sku] NULL
) ON [PRIMARY];
Shoud be D.
CREATE RULE [dbo].[rule_sku]
AS LEN(@rule_sku) = 10 AND @rule_sku like ‘ADV%’
GO
CREATE TYPE [dbo].[type_sku] FROM [char](10) NOT NULL;
GO
sp_bindrule rule_sku, type_sku
GO
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[sku] [dbo].[type_sku] NULL);
— constraint
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[sku] CHAR(10) NULL,
CONSTRAINT check_sku CHECK (LEN([sku])=10 and [sku] like ‘ADVC%’)
)
http://www.codeproject.com/Questions/609687/DifferenceplusbetweenplusConstraintsplusandplusRul
Rules are a backward-compatibility feature that performs some of the same functions as CHECK constraints.
CHECK constraints are the preferred, standard way to restrict the values in a column.
CHECK constraints are also more concise than rules; there can only be one rule applied to a column, but multipleCHECK constraints can be applied. CHECK constraints are specified as part of the CREATE TABLE statement, while rules are created as separate objects and then bound to the column.
Important about RULE:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
C or D ?
Check Constraint is the fastest way if it only applies to 1 column. But the SKU will be in multiple tables and thus from development view the easiest way to maintain. HOWEVER if rules are going to be removed in the future then D would be the correct answer.
doesn’t say anywhere that the SKU will be used in multiple tables, just that you design a database that has multiple tables (duh!). Also it doesn’t say that the SKU will be the primary key in the “products” table, so there’s no reason for me to assume that the SKU will be used in other tables. I’ll go for D, it’s much simpler
read this:
“The tables will contain product information. Each product has a stock-keeping unit (SKU).”
User Defined Data Type is developed once, the check will be repeated on each table
I’ll go for C