SQL Server supports creating indexes on a view. It is the same concept as Materialized Views in Oracle. The main difference in the syntax in normal views and indexed views is the clausa “with schemabinding”
CREATE VIEW [dbo].[HistoryTable] with schemabinding
SELECT id, activityID, userID, value, date
Now go ahead amd create the index on the view you just created.
CREATE UNIQUE CLUSTERED INDEX [IDX_ActivityStatusID] ON [dbo].[HistoryTable]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Lets say you want to rebuild indexes for a particular table in SQL Server 2000. Then you can generate the script to rebuild the indexes using the below command in Query Analyzer. Where xxx is the name of the database who indexes you want to rebuild
SELECT ‘dbcc dbreindex(”xxx.DBO.’ +name+ ”’)’
FROM sysobjects WHERE xtype=’U’
In case your mirroring has been suspended and you have tried everything else and nothing works. Your last option is to break the mirroring and restore the mirror database and connect the client application to the mirror site.
Before proceeding with this please note that doing this will require you to rebuild the Data Mirroring again.
At the Principal Site
ALTER DATABASE SET PARTNER OFF
At Mirror Site
RESTORE DATABASE WITH RECOVERY
Incase you want to change the mode of the mirroring from High Performance to High Safety. You can do it from the command line.
ALTER DATABASE SET PARTNER SAFETY OFF;
Please note that SQL Server 2008 Standard Edition does not allow changing the safety level. It will throw the below error
“Msg 1473, Level 16, State 6, Line 2
This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed”
After you have restored the mirror database and configured Data mirroring, when you start the mirroring it throws this error.
The solution to resolving this is to restore the backup of the transaction log to the Mirror database.
1. Take backup of the TLOG at Principal site
BACKUP LOG TO DISK = N’E:\transaction_log_bkp\Tlog_Bkp.trn’ WITH NOFORMAT, NOINIT, NAME = N’Test-Transaction Log Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
copy this backup to the Mirror site
2. Restore the TLOG backup to the mirror site.
RESTORE LOG FROM DISK = N’D:\transaction_log_backup\Tlog_Bkp.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
Now restart the mirroring session and it should be ok.