Database

Xandip wrote:

Indexes should be only used in tables used for data retrieval and not in tables where frequent INSERT & UPDATE take place.
When an Index is created over a table, then there is no need for defining a PrimaryKey for that table.
Indexes are best suited for Views.

<br />
use northwind<br />
go<br />
create function dbo.Fn_OrdersProductID(@OrderID int)<br />
returns nvarchar(4000)<br />
as <br />
begin<br />
DECLARE @ProductID nvarchar(4000)<br />
set @ProductID = null <br />
SELECT @ProductID = COALESCE(@ProductID + ',','') + cast(ProductID as nvarchar)<br />
FROM [Order Details]<br />
WHERE OrderID = @OrderID<br />
<br />
RETURN @ProductID<br />
<br />
end<br />
<br />
-- run the foll. stmt after executing the above function<br />
-- SELECT OrderID,dbo.Fn_OrdersProductID(OrderID) from Orders

SELECT DISTINCT
   IH.InvoiceDate    AS HistoryDate
 , 'Part of Invoice' AS HistoryType
 , SI.SiteName       AS HistoryBranch
 , ('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference
 , getInvoicePhoneIMEIs(IH.InvoiceID) AS HistoryIMEIs
FROM         tblInvoiceHeader IH
  INNER JOIN tblInvoiceItems  II ON II.InvoiceID = IH.InvoiceID
  INNER JOIN tblSites         SI ON IH.SiteID    = SI.SiteID
  INNER JOIN tblStaff         ST ON IH.StaffID   = ST.StaffID
  LEFT  JOIN tblCustomers      C ON IH.CustomerID= C.CustomerID
WHERE II.StockID = '1750'
  AND IH.InvoiceDate >= 'January 1, 2007'
  AND IH.InvoiceDate <= 'July 2, 2007'
  AND IH.SiteID = '10'
  AND IH.InvoiceProForma = 0