USE InsideTSQL2008;
IF OBJECT_ID('dbo.VIEWxxx', 'V') IS NOT NULL DROP VIEW dbo.VIEWxxx; 
GO 

CREATE VIEW dbo.VIEWxxx  
WITH  SCHEMABINDING  
AS    

SELECT     Sales.Orders.shipregion, Sales.Orders.orderid, Sales.Orders.custid, Sales.Orders.empid, Sales.Orders.orderdate, Sales.Orders.requireddate, 
                      Sales.Orders.shippeddate, Sales.Orders.shipperid, Sales.Orders.freight, Sales.Orders.shipname, Sales.Orders.shipaddress, Sales.Orders.shipcity, 
                      Sales.Orders.shippostalcode, Sales.Orders.shipcountry, Sales.Customers.region,CASE WHEN region <> '' THEN region ELSE '' END AS destdiv, 
                      COUNT_BIG(*) AS conta, row_number() OVER(ORDER BY orderid) as rn

FROM         Sales.Orders LEFT OUTER JOIN
                      Sales.Customers ON Sales.Orders.custid = Sales.Customers.custid
                      
GROUP BY Sales.Orders.shipregion, Sales.Orders.orderid, Sales.Orders.custid, Sales.Orders.empid, Sales.Orders.orderdate, Sales.Orders.requireddate, 
                      Sales.Orders.shippeddate, Sales.Orders.shipperid, Sales.Orders.freight, Sales.Orders.shipname, Sales.Orders.shipaddress, Sales.Orders.shipcity, 
                      Sales.Orders.shippostalcode, Sales.Orders.shipcountry, Sales.Customers.region,CASE WHEN region <> '' THEN region ELSE '' END  
GO

CREATE UNIQUE CLUSTERED INDEX idx_campo ON dbo.VIEWxxx(orderid);