Tuesday, June 15, 2010

Creating Indexed View in Microsoft SQL Server 2005 & 2008

Indexed View is the one of the best solutions to Business users

use Northwind

sp_help Orders
sp_help Customers

select * from Customers
select * from Orders

-- Select data from two tables for test
select OrderID, OrderDate , Orders.CustomerID,CompanyName
from Orders Inner Join Customers
on Orders.CustomerID = Customers.CustomerID

-- Step 1

-- create view from joined two tables
create view [dbo].[vw_OrderbyCustomer] with schemabinding as
select [OrderID], [OrderDate] , [Orders].CustomerID, [CompanyName]
from [dbo].[Orders]
Inner Join [dbo].Customers on [Orders].CustomerID = [Customers].CustomerID

select * from [vw_OrderbyCustomer]

-- Step 2
-- create a Clustered Index on View
create unique clustered index Idx_CI_View_OrderId_CustomerId on [vw_OrderbyCustomer](OrderID,CustomerID)

-- check the Index from Tables
sp_helpIndex vw_OrderbyCustomer

-- create a NonClustered Index on View
create unique index Idx_NCI_View_OrderId on [vw_OrderbyCustomer](OrderID)
create index Idx_NCI_View_CustomerId on [vw_OrderbyCustomer](CustomerID)
create index Idx_NCI_View_OrderDate on [vw_OrderbyCustomer](OrderDate)
create index Idx_NCI_View_CompanyName on [vw_OrderbyCustomer](CompanyName)

-- Step 3
sp_helpIndex vw_OrderbyCustomer

Idx_CI_View_OrderId_CustomerId clustered, unique located on PRIMARY OrderID, CustomerID
Idx_NCI_View_CompanyName nonclustered located on PRIMARY CompanyName
Idx_NCI_View_CustomerId nonclustered located on PRIMARY CustomerID
Idx_NCI_View_OrderDate nonclustered located on PRIMARY OrderDate
Idx_NCI_View_OrderId nonclustered, unique located on PRIMARY OrderID

Thursday, June 10, 2010

Settingup new subscription for Merge Publication Server

Settingup new subscription for Merge Publication Server in SQL 2000

Issue : Schema Definition of Base Table at Subscriber Does Not Match Table in Publisher Database

1. Check the table structure of the Publisher database and Subscriber database are same.

2. Take latest copy of Merge Publcation database backup and restore it on to New Subscriber database server.

Install SQL Server 2005 on Windows XP

Installing SQL Server 2005 Standard Edition on Windows XP will be failed.

Installation of MSXML 6 Service Pack 2 (KB954459) failed because a higher version already exists on the machine.

Using Windows Installer CleanUp utility to remove references to MSXML 6.0 Service Pack 2.

Install SQL Server 2005 on Windows xp.