Day 26: Advanced T-SQL features

A. Lesson overview

Activity breakdown

Business scenario. Several loose ends from earlier weeks: customer IDs are allocated with MAX + 1 (Day 17), reports filter on email domains with a leading wildcard (Day 24), a support team needs to look customers up without seeing full contact details, and analysts need read access to sales but not to salaries.

B. Theory explained clearly

Part 1 — Sequences

A sequence is a database object that generates numbers, independent of any table.

CREATE SEQUENCE Lab.CustomerSeq AS int START WITH 41 INCREMENT BY 1 CACHE 50;

SELECT NEXT VALUE FOR Lab.CustomerSeq;                       -- 41, then 42, …
ALTER TABLE Lab.Customer ADD CONSTRAINT DF_LabCustomer_ID
    DEFAULT (NEXT VALUE FOR Lab.CustomerSeq) FOR CustomerID; -- use it as a column default

Neither guarantees gap-free numbers. If the business needs strictly consecutive numbers (some invoice regimes), allocate them in a serialised transaction from a counter table — and accept the contention. CACHE improves performance by allocating values in memory blocks; an unexpected shutdown can skip the unused cached values.

Both solve Day 17’s problem: two sessions can never get the same value, without UPDLOCK, HOLDLOCK on MAX.

Part 2 — Synonyms

A synonym is an alternative name for another object:

CREATE SYNONYM Lab.Orders FOR Sales.SalesOrder;
SELECT COUNT(*) FROM Lab.Orders;           -- reads Sales.SalesOrder

Uses: hide long or remote names (OtherDb.dbo.Table, linked servers), and let code keep working when an object moves — re-point the synonym, not every procedure. Synonyms are resolved at run time (no check that the target exists when created), can’t be used in schema-bound objects, and can’t be chained to other synonyms.

Part 3 — Schemas, roles and permissions

A schema is a namespace and a security container. Granting on a schema covers every current and future object in it.

CREATE ROLE ReportReader;
GRANT SELECT ON SCHEMA::Sales TO ReportReader;     -- every table and view in Sales
DENY  SELECT ON Sales.Payment TO ReportReader;     -- except this one
ALTER ROLE ReportReader ADD MEMBER SomeUser;

Principles: grant to roles, not users; grant at the smallest useful scope; prefer EXECUTE on procedures (with ownership chaining, Day 21) over table access; use DENY sparingly (it’s hard to reason about). Test with CREATE USER … WITHOUT LOGIN and EXECUTE AS USER, and check with sys.fn_my_permissions or HAS_PERMS_BY_NAME.

Part 4 — Computed columns

ALTER TABLE Lab.Customer ADD EmailDomain AS SUBSTRING(Email, CHARINDEX('@', Email) + 1, 255);

To index a computed column, the expression must be deterministic (Day 18) and the session must have the standard SET options (SSMS defaults). An index on EmailDomain makes “customers at example.fr” a seek — the fix for Day 24’s LIKE '%@example.fr'. The optimiser can also match an expression in a query to an indexed computed column with the same definition.

Part 5 — Dynamic Data Masking

Dynamic Data Masking (DDM) hides part of a column’s value in query results for users without the UNMASK permission. The stored data is unchanged.

Since SQL Server 2022, UNMASK can be granted at database, schema, table or column level.

DDM is not encryption and not a security boundary. Users who can query a masked column can still filter on it: WHERE Salary > 100000 or WHERE Email LIKE 'alice%' reveal information by inference. Database owners and members of db_owner always see real data. Use DDM to reduce casual exposure (support screens, ad hoc queries) alongside proper permissions — and Always Encrypted or row-level security when you need real protection.

C. Dataset and starting state

Setup (re-runnable; destructive for the listed Lab objects, users and roles only).

USE RetailLab;
GO

-- Remove test principals from earlier attempts
DROP USER IF EXISTS AnalystUser;  DROP USER IF EXISTS AgentUser;  DROP USER IF EXISTS LeadUser;
DROP ROLE IF EXISTS ReportReader; DROP ROLE IF EXISTS SupportAgent; DROP ROLE IF EXISTS SupportLead;

DROP SYNONYM  IF EXISTS Lab.Orders;
DROP TABLE    IF EXISTS Lab.Customer, Lab.Employee, Lab.SalesOrderLine, Lab.Invoice, Lab.CreditNote;
DROP SEQUENCE IF EXISTS Lab.DemoSeq, Lab.CustomerSeq, Lab.DocumentSeq;
GO

SELECT * INTO Lab.Customer FROM Sales.Customer;
ALTER TABLE Lab.Customer ADD CONSTRAINT PK_LabCustomer PRIMARY KEY (CustomerID);
CREATE UNIQUE INDEX UX_LabCustomer_Email ON Lab.Customer (Email) WHERE Email IS NOT NULL;
ALTER TABLE Lab.Customer ADD CONSTRAINT DF_LabCustomer_IsActive DEFAULT (1) FOR IsActive;

SELECT EmployeeID, FirstName, LastName, JobTitle, DepartmentID, Email, Salary INTO Lab.Employee FROM HR.Employee;
ALTER TABLE Lab.Employee ADD CONSTRAINT PK_LabEmployee PRIMARY KEY (EmployeeID);

SELECT * INTO Lab.SalesOrderLine FROM Sales.SalesOrderLine;
ALTER TABLE Lab.SalesOrderLine ADD CONSTRAINT PK_LabSalesOrderLine PRIMARY KEY (OrderLineID);
GO

Validation. Expected: 40, 16, 702, 0.

SELECT  (SELECT COUNT(*) FROM Lab.Customer)        AS Customers,
        (SELECT COUNT(*) FROM Lab.Employee)        AS Employees,
        (SELECT COUNT(*) FROM Lab.SalesOrderLine)  AS OrderLines,
        (SELECT COUNT(*) FROM sys.sequences WHERE schema_id = SCHEMA_ID('Lab')) AS LabSequences;

Day 20’s Lab.usp_AddCustomer still works against this fresh Lab.Customer; worked example 1 shows the sequence-based replacement.

D. Worked examples

Worked example 1 — A sequence, its gaps, and a default

CREATE SEQUENCE Lab.DemoSeq AS int START WITH 1 INCREMENT BY 1;

SELECT NEXT VALUE FOR Lab.DemoSeq AS v1;        -- 1
SELECT NEXT VALUE FOR Lab.DemoSeq AS v2;        -- 2

BEGIN TRANSACTION;
    SELECT NEXT VALUE FOR Lab.DemoSeq AS InRolledBackTran;   -- 3
ROLLBACK TRANSACTION;

SELECT NEXT VALUE FOR Lab.DemoSeq AS v4;        -- 4: 3 is gone for good
SELECT name, start_value, current_value FROM sys.sequences WHERE name = 'DemoSeq';
GO

-- A sequence as the customer ID default
CREATE SEQUENCE Lab.CustomerSeq AS int START WITH 41 INCREMENT BY 1 CACHE 50;
ALTER TABLE Lab.Customer ADD CONSTRAINT DF_LabCustomer_ID DEFAULT (NEXT VALUE FOR Lab.CustomerSeq) FOR CustomerID;

INSERT INTO Lab.Customer (FirstName, LastName, Email, City, Country, CustomerSegment, DateJoined)
OUTPUT inserted.CustomerID, inserted.FirstName, inserted.LastName
VALUES (N'Daisy', N'Holt',   N'daisy.holt@example.com',  N'Bath',   N'United Kingdom', 'Consumer', '2026-01-02'),
       (N'Kofi',  N'Mensah', N'kofi.mensah@example.com', N'London', N'United Kingdom', 'Consumer', '2026-01-02');

Expected result: 1, 2, 3, 4; DemoSeq 1 4; then two rows 41 Daisy Holt and 42 Kofi Mensah. Sequence values are allocated outside the transaction, so the rollback did not give 3 back. The DEFAULT makes the sequence invisible to inserting code; IsActive was filled by its own default.

Worked example 2 — A synonym

CREATE SYNONYM Lab.Orders FOR Sales.SalesOrder;
SELECT COUNT(*) AS Orders FROM Lab.Orders;

-- Re-point it (e.g. after a migration): drop and recreate
DROP SYNONYM Lab.Orders;
CREATE SYNONYM Lab.Orders FOR Perf.SalesOrder;
SELECT COUNT(*) AS Orders FROM Lab.Orders;

SELECT name, base_object_name FROM sys.synonyms WHERE schema_id = SCHEMA_ID('Lab');

Expected result: 376; then 1000000 (if you still have the Week 4 Perf data — otherwise Invalid object name ‘Perf.SalesOrder’, which shows that synonyms are resolved only when used); then Orders, [Perf].[SalesOrder]. Point it back with DROP SYNONYM Lab.Orders; CREATE SYNONYM Lab.Orders FOR Sales.SalesOrder; before continuing.

Worked example 3 — A computed column with an index

ALTER TABLE Lab.Customer ADD EmailDomain AS SUBSTRING(Email, CHARINDEX('@', Email) + 1, 255);
CREATE INDEX IX_LabCustomer_EmailDomain ON Lab.Customer (EmailDomain);

SELECT EmailDomain, COUNT(*) AS Customers
FROM   Lab.Customer
WHERE  EmailDomain IS NOT NULL
GROUP BY EmailDomain
ORDER BY Customers DESC, EmailDomain;

SELECT CustomerID, FirstName, LastName FROM Lab.Customer WHERE EmailDomain = 'example.fr' ORDER BY CustomerID;
SELECT COLUMNPROPERTY(OBJECT_ID('Lab.Customer'), 'EmailDomain', 'IsDeterministic') AS IsDeterministic;

Expected result: 10 domains — example.com 24 (22 original customers plus the two added in worked example 1), example.fr 4, example.de 2, example.ie 2, then example.dk, example.es, example.it, example.net, example.org, example.se with 1 each. The example.fr query returns 4 customers via an Index Seek on the computed column (check the plan). IsDeterministic = 1. The index stores the computed value even though the column itself isn’t persisted.

Worked example 4 — Schema-level permissions

CREATE ROLE ReportReader;
GRANT SELECT ON SCHEMA::Sales TO ReportReader;
DENY  SELECT ON Sales.Payment  TO ReportReader;

CREATE USER AnalystUser WITHOUT LOGIN;
ALTER ROLE ReportReader ADD MEMBER AnalystUser;
GO

EXECUTE AS USER = 'AnalystUser';
    SELECT COUNT(*) AS Orders FROM Sales.SalesOrder;
    SELECT HAS_PERMS_BY_NAME('Sales.Payment', 'OBJECT', 'SELECT') AS CanReadPayments,
           HAS_PERMS_BY_NAME('HR.Employee',  'OBJECT', 'SELECT') AS CanReadEmployees;
REVERT;
GO
EXECUTE AS USER = 'AnalystUser';
    SELECT COUNT(*) FROM Sales.Payment;      -- Msg 229
REVERT;
GO

Expected result: 376; 0, 0; then Msg 229 … The SELECT permission was denied on the object ‘Payment’, database ‘RetailLab’, schema ‘Sales’. The schema grant covered every Sales table; the DENY carved out payments; HR was never granted. Run the failing query in its own batch so REVERT still runs.

E. Guided practical lab — Masked contact details for the support team

Step 1 — Add masks

1. Do: Mask email and phone on Lab.Customer.
2. Run:

   USE RetailLab;
   GO
   ALTER TABLE Lab.Customer ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
   ALTER TABLE Lab.Customer ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0, "XXXXXXX", 4)');
   
   SELECT c.name, c.is_masked, c.masking_function
   FROM   sys.masked_columns AS c
   WHERE  c.object_id = OBJECT_ID('Lab.Customer');

3. Observe: two rows: Email 1 email(), Phone 1 partial(0, "XXXXXXX", 4).
4. Why it matters: Masks are part of the column definition — no application change needed.
5. Confirm: SELECT Email, Phone FROM Lab.Customer WHERE CustomerID = 1; — you still see real values (you’re the owner).

Step 2 — A support agent’s view

1. Do: Create a role and user with read access, then look through their eyes.

2. Run:

   CREATE ROLE SupportAgent;
   GRANT SELECT ON Lab.Customer TO SupportAgent;
   CREATE USER AgentUser WITHOUT LOGIN;
   ALTER ROLE SupportAgent ADD MEMBER AgentUser;
   GO
   EXECUTE AS USER = 'AgentUser';
       SELECT CustomerID, FirstName, LastName, Email, Phone
       FROM   Lab.Customer WHERE CustomerID IN (1, 2, 3) ORDER BY CustomerID;
   REVERT;

3. Observe:

   CustomerID	FirstName	LastName	Email	Phone
   1	Alice	Morgan	aXXX@XXXX.com	XXXXXXX0101
   2	Ben	Carter	bXXX@XXXX.com	XXXXXXX0102
   3	Chloe	Evans	cXXX@XXXX.com	NULL

4. Why it matters: Agents can confirm “the email starting with a” and the last four digits of a phone number — enough for identity checks, not enough to copy contact lists.

5. Confirm: NULL stays NULL.

Step 3 — Masking is not access control

1. Do: As the agent, filter on the masked column.
2. Run:

   EXECUTE AS USER = 'AgentUser';
       SELECT CustomerID, Email FROM Lab.Customer WHERE Email LIKE 'alice.morgan@example.com';
       SELECT COUNT(*) AS FrenchDomain FROM Lab.Customer WHERE Email LIKE '%@example.fr';
   REVERT;

3. Observe: customer 1 (shown as aXXX@XXXX.com) and 4.
4. Why it matters: The filter runs on real data. Anyone who can query can confirm guesses. That’s why DDM complements permissions rather than replacing them.
5. Confirm: both queries returned results.

Step 4 — Column-level UNMASK for team leads

1. Do: Leads may see emails but not phone numbers.
2. Run:

   CREATE ROLE SupportLead;
   GRANT SELECT ON Lab.Customer TO SupportLead;
   GRANT UNMASK ON Lab.Customer (Email) TO SupportLead;
   CREATE USER LeadUser WITHOUT LOGIN;
   ALTER ROLE SupportLead ADD MEMBER LeadUser;
   GO
   EXECUTE AS USER = 'LeadUser';
       SELECT CustomerID, Email, Phone FROM Lab.Customer WHERE CustomerID = 1;
   REVERT;

3. Observe: 1, alice.morgan@example.com, XXXXXXX0101.
4. Why it matters: Granular UNMASK (SQL Server 2022+) lets you open exactly what a role needs.
5. Confirm: email unmasked, phone masked.

Step 5 — Who can do what?

1. Do: List today’s permissions from the catalog.
2. Run:

   SELECT  pr.name AS Principal, pe.state_desc, pe.permission_name, pe.class_desc,
           CASE pe.class_desc WHEN 'SCHEMA' THEN SCHEMA_NAME(pe.major_id)
                ELSE OBJECT_SCHEMA_NAME(pe.major_id) + '.' + OBJECT_NAME(pe.major_id) END AS SecurableName,
           COL_NAME(pe.major_id, pe.minor_id) AS ColumnName
   FROM    sys.database_permissions AS pe
   JOIN    sys.database_principals  AS pr ON pr.principal_id = pe.grantee_principal_id
   WHERE   pr.name IN ('ReportReader', 'SupportAgent', 'SupportLead')
   ORDER BY Principal, pe.permission_name;

3. Observe: ReportReader: GRANT SELECT SCHEMA Sales, DENY SELECT OBJECT_OR_COLUMN Sales.Payment; SupportAgent: GRANT SELECT … Lab.Customer; SupportLead: GRANT SELECT … Lab.Customer and GRANT UNMASK … Lab.Customer, column Email.
4. Why it matters: Permissions are data you can audit.
5. Confirm: 5 rows. major_id means a schema ID or an object ID depending on class_desc, hence the CASE.

F. Independent practice

Task 1 — One sequence for two document types

Create Lab.DocumentSeq (start 1001). Create Lab.Invoice (DocumentNo int PK DEFAULT NEXT VALUE FOR Lab.DocumentSeq, CustomerID int, Amount decimal(10,2), DocumentRef AS CONCAT('INV-', DocumentNo)) and Lab.CreditNote with the same shape but prefix CRN-. Insert, in this order: an invoice for customer 1 (100.00), a credit note for customer 1 (20.00), an invoice for customer 2 (50.00).

Success criteria: INV-1001, CRN-1002, INV-1003 — numbers unique across both tables. A UNION ALL of both tables ordered by DocumentNo shows them in creation order.

Task 2 — A persisted, indexed line amount

Add LineNet AS CAST(Quantity * UnitPrice * (1 - DiscountPct) AS decimal(12,2)) PERSISTED to Lab.SalesOrderLine, index it, and list the three largest lines. Then compare SUM(LineNet) with the exact total.

Success criteria: SUM(LineNet) = 124384.38; the exact total SUM(Quantity * UnitPrice * (1 - DiscountPct)) = 124384.4345. Explain the 0.05 difference.

Task 3 — Mask salaries and test inference

Mask Lab.Employee.Salary with default(). Grant SELECT on Lab.Employee to SupportAgent. As AgentUser, list employees with their salaries, then count employees earning more than 60,000.

Success criteria: every salary shows 0.00, but the count is 5. Explain what this means for using DDM on salary data.

Task 4 — Revoke versus deny (challenging)

As owner: GRANT SELECT ON Sales.Payment TO AnalystUser (the user directly). Test as AnalystUser. Then REVOKE SELECT ON Sales.Payment FROM ReportReader (removes the role’s DENY) and test again.

Success criteria: first test still fails (role DENY beats user GRANT); after revoking the DENY, the query returns 395.

Stretch challenge — Reserve a range for a batch load

Use sys.sp_sequence_get_range to reserve 3 customer IDs from Lab.CustomerSeq in one call, then insert Ines Moreau, Emma Wilson and Tariq Aziz with those IDs (explicit values override the default). Show the next value afterwards.

Success criteria: first value 43; customers 43, 44, 45 inserted; NEXT VALUE FOR Lab.CustomerSeq then returns 46.

G. Solutions and explanations

CREATE SEQUENCE Lab.DocumentSeq AS int START WITH 1001 INCREMENT BY 1;

CREATE TABLE Lab.Invoice
(
    DocumentNo  int NOT NULL CONSTRAINT PK_LabInvoice PRIMARY KEY
                CONSTRAINT DF_LabInvoice_No DEFAULT (NEXT VALUE FOR Lab.DocumentSeq),
    CustomerID  int           NOT NULL,
    Amount      decimal(10,2) NOT NULL,
    DocumentRef AS CONCAT('INV-', DocumentNo)
);
CREATE TABLE Lab.CreditNote
(
    DocumentNo  int NOT NULL CONSTRAINT PK_LabCreditNote PRIMARY KEY
                CONSTRAINT DF_LabCreditNote_No DEFAULT (NEXT VALUE FOR Lab.DocumentSeq),
    CustomerID  int           NOT NULL,
    Amount      decimal(10,2) NOT NULL,
    DocumentRef AS CONCAT('CRN-', DocumentNo)
);

INSERT INTO Lab.Invoice    (CustomerID, Amount) VALUES (1, 100.00);
INSERT INTO Lab.CreditNote (CustomerID, Amount) VALUES (1,  20.00);
INSERT INTO Lab.Invoice    (CustomerID, Amount) VALUES (2,  50.00);

SELECT DocumentNo, DocumentRef, CustomerID, Amount FROM Lab.Invoice
UNION ALL
SELECT DocumentNo, DocumentRef, CustomerID, Amount FROM Lab.CreditNote
ORDER BY DocumentNo;

ALTER TABLE Lab.SalesOrderLine ADD LineNet AS CAST(Quantity * UnitPrice * (1 - DiscountPct) AS decimal(12,2)) PERSISTED;
CREATE INDEX IX_LabSalesOrderLine_LineNet ON Lab.SalesOrderLine (LineNet);

SELECT TOP (3) WITH TIES OrderLineID, OrderID, ProductID, LineNet
FROM   Lab.SalesOrderLine ORDER BY LineNet DESC;

SELECT SUM(LineNet)                                    AS SumOfRoundedLines,
       SUM(Quantity * UnitPrice * (1 - DiscountPct))   AS ExactTotal
FROM   Lab.SalesOrderLine;

ALTER TABLE Lab.Employee ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');
GRANT SELECT ON Lab.Employee TO SupportAgent;
GO
EXECUTE AS USER = 'AgentUser';
    SELECT EmployeeID, FirstName, LastName, Salary FROM Lab.Employee ORDER BY EmployeeID;
    SELECT COUNT(*) AS Over60k FROM Lab.Employee WHERE Salary > 60000;
REVERT;

GRANT SELECT ON Sales.Payment TO AnalystUser;
GO
EXECUTE AS USER = 'AnalystUser';
    SELECT COUNT(*) FROM Sales.Payment;          -- still Msg 229
REVERT;
GO
REVOKE SELECT ON Sales.Payment FROM ReportReader;
GO
EXECUTE AS USER = 'AnalystUser';
    SELECT COUNT(*) AS Payments FROM Sales.Payment;   -- 395
REVERT;
GO

DECLARE @first sql_variant;
EXEC sys.sp_sequence_get_range
     @sequence_name     = N'Lab.CustomerSeq',
     @range_size        = 3,
     @range_first_value = @first OUTPUT;

DECLARE @id int = CAST(@first AS int);
SELECT @id AS FirstReserved;

INSERT INTO Lab.Customer (CustomerID, FirstName, LastName, Email, City, Country, CustomerSegment, DateJoined)
VALUES (@id,     N'Ines',  N'Moreau', N'ines.moreau@example.fr',  N'Nantes',    N'France',         'Consumer', '2026-01-02'),
       (@id + 1, N'Emma',  N'Wilson', N'emma.wilson@example.com', N'Edinburgh', N'United Kingdom', 'Consumer', '2026-01-02'),
       (@id + 2, N'Tariq', N'Aziz',   NULL,                       N'Bradford',  N'United Kingdom', 'Consumer', '2026-01-02');

SELECT NEXT VALUE FOR Lab.CustomerSeq AS NextValue;

H. Knowledge check

1. Understanding. Give two things a sequence can do that an IDENTITY column can’t.

2. Debugging. After a server restart, new customer IDs jump from 57 to 91. Nothing failed. Why?

3. Understanding. A role has GRANT SELECT ON SCHEMA::Sales. A new table Sales.Refund is created next month. Can role members read it?

4. Query interpretation. A masked user runs SELECT COUNT(*) FROM Lab.Employee WHERE Salary BETWEEN 119000 AND 121000 and gets 1. What does this show?

5. Understanding. What is required to index a computed column?

I. Troubleshooting and cleanup

Cleanup. Remove today’s principals and objects (keeping masks on a Lab copy would confuse later lessons):

REVERT;   -- harmless if you're already yourself
DROP USER IF EXISTS AnalystUser;  DROP USER IF EXISTS AgentUser;  DROP USER IF EXISTS LeadUser;
DROP ROLE IF EXISTS ReportReader; DROP ROLE IF EXISTS SupportAgent; DROP ROLE IF EXISTS SupportLead;
DROP SYNONYM  IF EXISTS Lab.Orders;
DROP TABLE    IF EXISTS Lab.Invoice, Lab.CreditNote, Lab.Employee, Lab.SalesOrderLine;
ALTER TABLE Lab.Customer DROP CONSTRAINT IF EXISTS DF_LabCustomer_ID;
DROP SEQUENCE IF EXISTS Lab.DemoSeq, Lab.CustomerSeq, Lab.DocumentSeq;
DROP TABLE    IF EXISTS Lab.Customer;

Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• The rolled-back sequence value (3) was skipped; Daisy and Kofi received 41 and 42 from the default.
• The synonym read 376 orders (and 1,000,000 when re-pointed, if Perf exists).
• The computed-column index served the example.fr query (4 rows) with a seek.
• The agent saw aXXX@XXXX.com / XXXXXXX0101; the lead saw the real email only.
• Task 3 showed masked salaries but a real count of 5.

Recap

• Sequences: shared, pre-fetchable, reservable numbers; like identity, they leave gaps.
• Synonyms decouple code from object names and locations.
• Grant to roles at schema or object level; DENY overrides GRANT; REVOKE removes either.
• Computed columns encapsulate expressions; deterministic ones can be persisted and indexed — the fix for many non-sargable filters.
• Dynamic Data Masking hides values in results, not from filters; owners always see data; it complements real security.

Next: Day 27 — JSON and XML in T-SQL: producing and consuming documents with FOR JSON, OPENJSON, JSON_VALUE, FOR XML and XQuery methods.

References (Microsoft Learn)

• Sequence numbers
• CREATE SEQUENCE (Transact-SQL)
• sp_sequence_get_range (Transact-SQL)
• CREATE SYNONYM (Transact-SQL)
• GRANT Schema Permissions (Transact-SQL)
• Specify computed columns in a table
• Indexes on computed columns
• Dynamic data masking