Category Archives: View

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’)
Advertisements

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
)