SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can be achieved by creating an in-line table valued function. Let’s see how to convert a commonly used view HumanResources.vEmployee in AdventureWorks to a parametrized view.

CREATE FUNCTION PV_GetEmployeeInformationBySSN

(     

       -- Add the parameters for the function here

       @NationalIDNumber VARCHAR(9)

)

RETURNS TABLE

AS

RETURN

(

       -- Add the SELECT statement with parameter references here

       SELECT   e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,

              p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,

              pnt.Name AS PhoneNumberType, ea.EmailAddress,

              p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,

              sp.Name AS StateProvinceName, a.PostalCode,

              cr.Name AS CountryRegionName,

              p.AdditionalContactInfo

       FROM            HumanResources.Employee AS e INNER JOIN

              Person.Person AS p

              ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN

              Person.BusinessEntityAddress AS bea

              ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN

              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN

              Person.StateProvince AS sp

              ON sp.StateProvinceID = a.StateProvinceID INNER JOIN

              Person.CountryRegion AS cr

              ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN

              Person.PersonPhone AS pp

              ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN

              Person.PhoneNumberType AS pnt

              ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN

              Person.EmailAddress AS ea

              ON p.BusinessEntityID = ea.BusinessEntityID

       WHERE e.NationalIDNumber = @NationalIDNumber

)

GO

How to use it. Very Simple :)

SELECT * FROM PV_GetEmployeeInformationBySSN ('112457891')

SQL Server: Does Unwanted Tables in a Query or View Affect Performance

Recently a friend of mine asked, that is it true that presence of extra tables in joins section of a query, will affect query performance. Extra tables means,tables which can be skipped from query without affecting query result. For example following query has extra tables (other than vendor and contact tables) in join section

USE AdventureWorks

GO

SELECT Vendor.Name,

Contact.Title,

Contact.FirstName,

Contact.MiddleName

FROM Person.Address AS a

INNER JOIN Purchasing.VendorAddress AS VendorAddress

ON a.AddressID = VendorAddress.AddressID

INNER JOIN Person.StateProvince AS StateProvince

ON StateProvince.StateProvinceID = a.StateProvinceID

INNER JOIN Person.CountryRegion AS CountryRegion

ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode

INNER JOIN Purchasing.Vendor AS Vendor

INNER JOIN Purchasing.VendorContact AS VendorContact

ON VendorContact.VendorID = Vendor.VendorID

INNER JOIN Person.Contact AS Contact

ON Contact.ContactID = VendorContact.ContactID

INNER JOIN Person.ContactType AS ContactType

ON VendorContact.ContactTypeID = ContactType.ContactTypeID

ON VendorAddress.VendorID = Vendor.VendorID

Though this is NOT common to have extra tables in our usual queries but it could be possible in views. A view can be created with multiple tables and selecting columns from each joined table. And later on when we will query this view we can use only few columns in our select statement. So when we will execute above query SQL Server Query Analyzer will skip all those tables which are not part of game. Here is execution plan of above query.

Same query with more columns, pushing all tables in action.

SELECT Vendor.Name,

ContactType.Name AS ContactType,

Contact.Title,

Contact.FirstName,

Contact.MiddleName,

a.AddressLine1,

a.AddressLine2,

a.City,

StateProvince.Name AS StateProvinceName,

a.PostalCode,

CountryRegion.Name AS CountryRegionName,

Vendor.VendorID

FROM Person.Address AS a

INNER JOIN Purchasing.VendorAddress AS VendorAddress

ON a.AddressID = VendorAddress.AddressID

INNER JOIN Person.StateProvince AS StateProvince

ON StateProvince.StateProvinceID = a.StateProvinceID

INNER JOIN Person.CountryRegion AS CountryRegion

ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode

INNER JOIN Purchasing.Vendor AS Vendor

INNER JOIN Purchasing.VendorContact AS VendorContact

ON VendorContact.VendorID = Vendor.VendorID

INNER JOIN Person.Contact AS Contact

ON Contact.ContactID = VendorContact.ContactID

INNER JOIN Person.ContactType AS ContactType

ON VendorContact.ContactTypeID = ContactType.ContactTypeID

ON VendorAddress.VendorID = Vendor.VendorID

If we create a view using our second query and use our view in following style then execution plan will be same to our first query.

SELECT Name,

Title,

FirstName,

MiddleName

FROM vw_MyView

Hence, SQL Server Query Analyser is quite smart and work on only those tables which are part of actual game and it doesn’t matter that extra tables are part of your query or a view.

Sql Server Error: (Msg 101000) Cannot Create Index on View Because it Contains the DISTINCT Keyword

Creating an indexed view, a common error massage is Msg101000. Yes you cannot use DISTINCT keyword to create an indexed view (or materilized view). It will return error as follow:

Msg 10100, Level 16, State 1, Line 4

Cannot create index on view “YourViewNameHere” because it contains the DISTINCT keyword. Consider removing DISTINCT from the view or not indexing the view. Alternatively, consider replacing DISTINCT with GROUP BY or COUNT_BIG(*) to simulate DISTINCT on grouping columns.

Error itself explains it “How to resolve”.  Consider following query which creates a view using AdventureWorks database and then try to create CLUSTERED INDEX (which will make it an INDEXED VIEW).

USE AdventureWorks

GO

-- Create a schema binded view and use DISTINCT keyword to have unique records

CREATE  VIEW [HumanResources].[vDistinctEmployee]

WITH SCHEMABINDING

AS

SELECT DISTINCT

    e.[EmployeeID]

    ,c.[Title]

    ,c.[FirstName]

    ,c.[MiddleName]

    ,c.[LastName]

    ,sp.[Name] AS [StateProvinceName]

  FROM [HumanResources].[Employee] e

    INNER JOIN [Person].[Contact] c

    ON c.[ContactID] = e.[ContactID]

    INNER JOIN [HumanResources].[EmployeeAddress] ea

    ON e.[EmployeeID] = ea.[EmployeeID]

    INNER JOIN [Person].[Address] a

    ON ea.[AddressID] = a.[AddressID]

    INNER JOIN [Person].[StateProvince] sp

    ON sp.[StateProvinceID] = a.[StateProvinceID];

GO

-- Create clustered index to make in indexed view

CREATE UNIQUE CLUSTERED INDEX [IX_ DistEmployeeID] ON [HumanResources].[vDistinctEmployee]

(

       [EmployeeID] ASC

)

It will generate above mentioned error. Lets resolve it according to given explanation:

USE AdventureWorks

GO

-- Remove DISTINCT keyword and add GROUP BY cluase with an additional column COUNT_BIG(*)

ALTER  VIEW [HumanResources].[vDistinctEmployee]

WITH SCHEMABINDING

AS

SELECT

    e.[EmployeeID]

    ,c.[Title]

    ,c.[FirstName]

    ,c.[MiddleName]

    ,c.[LastName]

    ,sp.[Name] AS [StateProvinceName]

    ,COUNT_BIG(*) AS countbig -- TO RETURN DISTINCT ROWS

  FROM [HumanResources].[Employee] e

    INNER JOIN [Person].[Contact] c

    ON c.[ContactID] = e.[ContactID]

    INNER JOIN [HumanResources].[EmployeeAddress] ea

    ON e.[EmployeeID] = ea.[EmployeeID]

    INNER JOIN [Person].[Address] a

    ON ea.[AddressID] = a.[AddressID]

    INNER JOIN [Person].[StateProvince] sp

    ON sp.[StateProvinceID] = a.[StateProvinceID]

 GROUP BY e.[EmployeeID]

    ,c.[Title]

    ,c.[FirstName]

    ,c.[MiddleName]

    ,c.[LastName]

    ,sp.[Name]

      

GO

-- NOW CREATE UNIQUE CLUSTERED INDEX

CREATE UNIQUE CLUSTERED INDEX [IX_DistEmployeeID] ON [HumanResources].[vDistinctEmployee]

(

       [EmployeeID] ASC

)