Special Schema Object Tags

Special Schema Object Tags

Sometimes Insight needs a little bit of help to figure out your schema. In some cases, Insight looks for special tags in the comments of your SQL to do some advanced features.

Indexed Views

If you have a VIEW and you also add a CLUSTERED INDEX on the VIEW (in SQL Server 2008 or later), you are materializing the view into a new table. This is accomplished by creating a VIEW and an INDEX. The VIEW needs to be created before the INDEX, but normally Insight creates INDEXES before VIEWs. Unfortunately, it is difficult for Insight to detect that these scripts are related, so we add an INDEXEDVIEW tag to the VIEW. This tells Insight to create the VIEW before the INDEX.

The Indexed View tag is:

-- INDEXEDVIEW

Example:

-------------------------------------------------------------------
-- INDEXEDVIEW 
-- [VW_BankTransactions_Summary_ByAccount] - indexed view of the bank transactions by account and category
-------------------------------------------------------------------
CREATE VIEW [VW_BankTransactions_Summary_ByAccount]
WITH SCHEMABINDING
AS
    SELECT t.AccountID, CategoryID = COALESCE(t.CategoryID, 0), 
        Count = COUNT_BIG(*), Date = DATEADD (d, 1-DAY (TransactionDate), TransactionDate), TotalAmount = SUM (SplitAmount)
    FROM [dbo].[BankTransactions] t
    -- ignore the Excluded category
    WHERE COALESCE(t.CategoryID, 0) <> -1 
    GROUP BY t.AccountID, COALESCE(t.CategoryID, 0), DATEADD (d, 1-DAY (TransactionDate), TransactionDate)
GO

-------------------------------------------------------------------
-- [IX_BankTransactions_Summary_ByAccount] - indexed view of the bank transactions by account and category
-- Signature Breaker: [2] (update this to force the index to get rebuilt)
-------------------------------------------------------------------
CREATE UNIQUE CLUSTERED INDEX [IX_BankTransactions_Summary_ByAccount] ON  
    [VW_BankTransactions_Summary_ByAccount] 
    (
        AccountID,
        Date,
        CategoryID
    )
GO

User-Defined Scripts

You may need to execute a script upon installation. You have the option of running the script before other objects with the PRESCRIPT tag, or after all other objects with the SCRIPT tag.

The tags are:

-- PRESCRIPT
-- SCRIPT

Example:

-------------------------------------------------------------------
-- SCRIPT [PopulateSystemCategories] - fill in the system categories
-- NOTE: this needs to match the Category enum in Category.cs
------------------------------------------------------------------- 
EXEC BankTransactionCategories_InsertSystemCategory 64,	NULL,	'Auto & Transport'
EXEC BankTransactionCategories_InsertSystemCategory 66,	64,		'Auto Service'
EXEC BankTransactionCategories_InsertSystemCategory 67,	64,		'Auto Payment'
EXEC BankTransactionCategories_InsertSystemCategory 68,	64,		'Auto Registration'
EXEC BankTransactionCategories_InsertSystemCategory 65,	64,		'Gas & Fuel'
EXEC BankTransactionCategories_InsertSystemCategory 83,	64,		'Public Transport'
GO