Tag: T-SQL

  • A view has no data

    I have seen quite a few posts and questions lately from people that are trying to change the data in a view, or move data in a view.

    A view has no data.

    It’s that simple. If you have something like this in AdventureWorks:

    SELECT firstname
    , lastname
     FROM HumanResources.vEmployee
     

    And this view is defined as:

    CREATE VIEW [HumanResources].[vEmployee] 
    AS 
    SELECT 
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,e.[Title] AS [JobTitle] 
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,c.[AdditionalContactInfo]
    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]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

    The SELECT statement is the same as running

     SELECT 
     c.[FirstName]
    ,c.[LastName]
    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]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
    

    Note this is exactly the same thing as the view definition with fewer columns included. Or it could be written like this:

    SELECT firstname
    , lastname
     FROM 
     (
    SELECT 
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,e.[Title] AS [JobTitle] 
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,c.[AdditionalContactInfo]
    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]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
     ) a
     

    In this case I’ve moved the view definition into the FROM clause of my SELECT query.

    A view is literally a stored query that you can use to make it easier to write code. There is no data in the view, so if you need to change the data, or “refresh” the data from another database, you need to move the data in the tables that are referenced in the VIEW.

  • The Basics of Joins – Skill #4

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    Databases are built to store data. That’s the primary purpose, and in SQL Server, we store data in a relational form. That means that often we have data spread across multiple tables. Why we do this is a discussion for another day, but suffice it to say that we often have structures like this:

    personcontact

    Part of the person.contact table in AdventureWorks above and the HumanResource.Employee table below.

    employee

    One typical join task might be to get an employee’s name, or a list of employees and their names. Here we have a birthday in the Employee table, but we don’t have a name. That’s in the Person.Contact table. Essentially we want to match these up using basic, elementary school set theory.

    settheory

    In the diagram above, you can think of each letter as a row in a table. As an example, let’s assume that B in the orange circle represents the row in the employee table with a ContactID value of 4. The B in the pink circle would represent the row in the Contact table with a ContactID value of 4 as well.

    When we join these to get the Employee name and birth date, we get:

    join2

    I used a join in my query to get that:

    SELECT 
      c.firstname
    , c.LastName
    , e.BirthDate
     FROM person.contact c
       INNER JOIN HumanResources.Employee e
         ON c.ContactID = e.ContactID
     WHERE c.ContactID = 4
     

    In this query I’ve included two tables in the FROM clause with the INNER JOIN key phrase between them, which specifies I only choose the matching rows. The match is made in the ON clause.

    I’ve also qualified this to only apply to the row with a ContactID of 4 in the WHERE clause.

    There’s a lot more you can do with joins, and you can include more than two tables, such as this query:

    SELECT 
      c.firstname
    , c.LastName
    , e.BirthDate
    , pa.AddressLine1
    , pa.AddressLine2
     FROM person.contact c
       INNER JOIN HumanResources.Employee e
         ON c.ContactID = e.ContactID
       INNER JOIN HumanResources.EmployeeAddress ea
         ON e.EmployeeID = ea.EmployeeID
       INNER JOIN person.Address pa
         ON ea.AddressID = pa.AddressID
     WHERE c.ContactID = 4
     

    I would recommend that you practice working with basic joins, based on the information that you commonly see queried in your application. Sooner or later someone will ask you for some data that isn’t available in the application and you will want to write a query to extract it for them.

  • Basic Inner Joins – Basic Skill #4

    This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #4 – Joins.

    T-SQL Tuesday #23

    This post is also serving as my T-SQL Tuesday post for the month. This month the party is brought to you by Stuart Ainsworth. If you want to know more about what T-SQL Tuesday is about, read Stuart’s post as well as Adam Machanic’s original post and then join in next month.

    An inner join is essentially an intersection of two sets. If you go back to grade school, and think about sets, you can have two items like this:

    set1

    If you look at these two sets, you see that there are various elements in each set. If we were to show the intersection, it would be this set: (B). This is shown below (excuse my horrible artwork):

    set2

    In SQL, we deal with tables, but we can model this as follows:

    CREATE TABLE SET1
    ( mychar varchar(1) ) GO CREATE TABLE SET2
    ( mychar varchar(1) ) GO INSERT SET1 SELECT 'A' INSERT SET1 SELECT 'B' GO INSERT SET2 VALUES ('B'), ('C'), ('D') GO SELECT a.mychar
      , b.mychar
     FROM SET1 a
       INNER JOIN set2 b
         ON a.mychar = b.mychar
    mychar mychar
    ------ ------
    B      B

    The results are the matching values in each table. In this case each table is a single column, modeling the images above where there is a single letter in each item of the set. The matching columns are the join columns, and in database work, these would be the data items that we are storing in both tables.

    However to expand this, in a database table, we usually have multiple items, so each letter could be a series of data elements, or could have a series of other fields attached to it. Suppose I change these “sets” a little:

    DROP TABLE set1
    DROP TABLE dbo.SET2
    go CREATE TABLE SET1
    ( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO CREATE TABLE SET2
    ( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO INSERT SET1 SELECT 'A', 'Bob', '1/1/2011' INSERT SET1 SELECT 'B', 'Bill', '2/1/2011' GO INSERT SET2 VALUES ('B', 'Steve', '1/2/2011'), ('C', 'Andy', '3/1/2011'), ('D', 'Brian', '3/3/2011') GO SELECT a.mychar
      , b.mychar
     FROM SET1 a
       INNER JOIN set2 b
         ON a.mychar = b.mychar

    I still only have one matching row, but there are other data points. If I alter my diagram, they look like this:

    set3

    The matching rows, in this case the rows with a “B” in them, have different data, which seems to be an issue. However suppose set 1 was a list of customers along with their first order date and set 2 was a list of salespeople and the dates they started. Then the join might be on sales, with the letter (A, B, C, D) representing the order.

    That’s the basic of a join. It gets complicated as you look to join three, four, or more tables, but this is the basic idea of an inner join in SQL.

  • Decimal Data Types in SQL Server

    I learned something new about decimal data types in SQL Server recently. If you do something like this:

    DECLARE @d DECIMAL(10,10)
    SET @d = 1.0

     

    You get this:

    Msg 8115, Level 16, State 8, Line 2

    Arithmetic overflow error converting numeric to data type numeric.

    That threw me for a minute since I’d never experienced it, but then I realized what was going on. The declaration of a decimal data type goes like this:

    DECLARE @d DECIMAL( @p, @s)

    Where we have

    • d – the name of the variable
    • p = precision, the number of digits in the number.
    • s = scale, the number of digits for the decimal

    You can think of this as the following,

    @d = nnnn.ssss

    Where the count of n’s and s’s must equal p.

    In the first example above, I’ve essentially declared:

    @d = 0.ssssssssss

    There’s no room for a digit, other than zero, to the left of the decimal. So you get an error. If I’d added one more digit to the p variable, like this:

     
    DECLARE @d DECIMAL(11,10)
    SET @d = 1.0

    I don’t receive an error. Likewise, I can add multiple digits to the other side like this:

    DECLARE @d DECIMAL(10,10)
    SET @d = 0.999999999

    Works fine.

    I had never run into this because I don’t ever declare these the same. I almost always go larger than I need, and allow for more decimals. So for US money, I often declare things at decimal(10,3), giving me more space than needed. It pays to think ahead, and declare your variables properly, and understanding how they are structured is part of that.

    Note: this applies to numeric types as well.