SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can beachieved 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 theparameters for the function here

       @NationalIDNumber VARCHAR(9)

)

RETURNS TABLE

AS

RETURN

(

       — Add theSELECT statement with parameter references here

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

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

              pnt.NameAS PhoneNumberType,ea.EmailAddress,

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

              sp.NameAS StateProvinceName,a.PostalCode,

              cr.NameAS CountryRegionName,

              p.AdditionalContactInfo

       FROM            HumanResources.Employee AS e INNERJOIN

              Person.Person AS p

              ONp.BusinessEntityID =e.BusinessEntityID INNERJOIN

              Person.BusinessEntityAddressAS bea

              ONbea.BusinessEntityID =e.BusinessEntityID INNERJOIN

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

              Person.StateProvince AS sp

              ONsp.StateProvinceID =a.StateProvinceID INNERJOIN

              Person.CountryRegion AS cr

              ONcr.CountryRegionCode =sp.CountryRegionCode LEFTOUTER JOIN

              Person.PersonPhone AS pp

              ONpp.BusinessEntityID =p.BusinessEntityID LEFTOUTER JOIN

              Person.PhoneNumberType AS pnt

              ONpp.PhoneNumberTypeID =pnt.PhoneNumberTypeID LEFTOUTER JOIN

              Person.EmailAddress AS ea

              ONp.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: 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

)