Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ALTER TABLE dbo.tblBGiftVoucherItem
- ADD isUsGift AS CAST
- (
- ISNULL(
- CASE WHEN sintMarketID = 2
- AND strType = 'CARD'
- AND strTier1 LIKE 'GG%'
- THEN 1
- ELSE 0
- END
- , 0)
- AS BIT
- ) PERSISTED;
- CREATE INDEX FIX_tblBGiftVoucherItem_incl
- ON dbo.tblBGiftVoucherItem (strItemNo)
- INCLUDE (strTier3)
- WHERE isUsGift = 1;
- USE tempdb;
- CREATE TABLE dbo.PersistedViewTest
- (
- PersistedViewTest_ID INT NOT NULL
- CONSTRAINT PK_PersistedViewTest
- PRIMARY KEY CLUSTERED
- IDENTITY(1,1)
- , SomeData VARCHAR(2000) NOT NULL
- , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
- );
- GO
- CREATE VIEW dbo.PersistedViewTest_View
- WITH SCHEMABINDING
- AS
- SELECT PersistedViewTest_ID
- , SomeData
- , TestComputedColumn
- FROM dbo.PersistedViewTest
- WHERE TestComputedColumn < CONVERT(INT, 27);
- CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
- ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
- GO
- INSERT INTO dbo.PersistedViewTest (SomeData)
- SELECT o.name + o1.name + o2.name
- FROM sys.objects o
- CROSS JOIN sys.objects o1
- CROSS JOIN sys.objects o2;
- CREATE STATISTICS ST_PersistedViewTest_View
- ON dbo.PersistedViewTest_View(TestComputedColumn)
- WITH FULLSCAN;
- CREATE INDEX IX_PersistedViewTest_View_TestComputedColumn
- ON dbo.PersistedViewTest_View(TestComputedColumn);
- SELECT pv.PersistedViewTest_ID
- , pv.TestComputedColumn
- FROM dbo.PersistedViewTest pv
- WHERE pv.TestComputedColumn = CONVERT(INT, 26)
- SELECT pv.PersistedViewTest_ID
- , pv.TestComputedColumn
- FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
- WHERE pv.TestComputedColumn = CONVERT(INT, 26)
- -- Create the test table that uses a computed column.
- USE tempdb;
- CREATE TABLE dbo.PersistedViewTest
- (
- PersistedViewTest_ID INT NOT NULL
- CONSTRAINT PK_PersistedViewTest
- PRIMARY KEY CLUSTERED
- IDENTITY(1,1)
- , SomeData VARCHAR(2000) NOT NULL
- , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
- );
- GO
- -- Insert some test data into the table.
- INSERT INTO dbo.PersistedViewTest (SomeData)
- SELECT o.name + o1.name + o2.name
- FROM sys.objects o
- CROSS JOIN sys.objects o1
- CROSS JOIN sys.objects o2;
- GO
- SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
- FROM dbo.PersistedViewTest pv
- WHERE pv.TestComputedColumn = CONVERT(INT, 26)
- GO
- -- Create filtered view on the computed column.
- CREATE VIEW dbo.PersistedViewTest_View
- WITH SCHEMABINDING
- AS
- SELECT PersistedViewTest_ID, SomeData, TestComputedColumn
- FROM dbo.PersistedViewTest
- WHERE TestComputedColumn < CONVERT(INT, 27);
- GO
- -- Create unique clustered index to persist the values, including the computed column.
- CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
- ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
- GO
- SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
- FROM dbo.PersistedViewTest_View pv
- WHERE pv.TestComputedColumn = CONVERT(INT, 26)
- GO
- SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
- FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
- WHERE pv.TestComputedColumn = CONVERT(INT, 26)
- GO
Add Comment
Please, Sign In to add comment