SQL SERVER – What is AdventureWorks?

NOTE: If you know the answer of this question, then I request you to stop reading this post right now. Please do not leave comment about this blog post not being useful to you, if you knew the answer.

Few days ago, I received DM asking What is an AdventureWorks database and why in all the examples I use that instead of any other database (e.g. Pubs or  Northwind)? As matter of fact, when I went back to my question list, which I have yet not answered, there were a few more variations of this same question.

AdventureWorks is a Sample Database shipped with SQL Server and it can be downloaded from http://codeplex.com site.

AdventureWorks has replaced Northwind and Pubs from the sample database in SQL Server 2005.

The Microsoft team keeps updating the sample database as they release new versions.

Here are some quick links:
AdventureWorks SQL Server 2008 SR4

AdventureWorks 2008R2 November CTP

AdventureWorks for SQL Azure (December CTP)

AventureWorks for SQL Server 2005 SP2A

SQL SERVER – 2008 – Download and Install Samples Database AdventureWorks 2005 – Detail Tutorial

I have previously written few other articles on the same subject; you can find them easily here: Search@SQLAuthority.com

Here is a video which discussing how to install AdventureWorks Database:

UPDATE: Everybody knows Buck Woody (Blog | Twitter)! Buck has written excellent reference article regarding The SQL Server Sample Databases here. I strongly suggest to check that out.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

14 thoughts on “SQL SERVER – What is AdventureWorks?

  1. Hi Pinal,

    I have been reading your blogs from last three years, and learned much more from you. I really appriciate your work.

    I have struck up with some situation, so i need your help:

    I need query help for below senorio, could you please help me.

    TableName = City
    CITYID ContinuationID CITYNAME
    1 1 SAN
    1 2 DIEGO
    2 1 SAN
    2 2 FRANCISCO
    3 1 CHICAGO
    4 1 NEW
    4 2 YORK
    4 3 CITY

    Could you please help me to write a generalized SQL that returns results as given below in the

    Query result
    CITYID NAME1 NAME2 NAME3 NAME4 NAME5
    1 SAN DIEGO
    2 SAN FRANCISCO
    3 CHICAGO
    4 NEW YORK CITY

  2. I tried ur issue by using a cursor .

    here in my example sample is my table name .The below is the code for that. Check whether it may be helpful to you or not.

    Here i didn’t consider the continuation id column ..according to your requirement

    GO
    DECLARE @id INT,@cnt INT,@lpcnt INT,@str VARCHAR(100),@var VARCHAR(100)
    SET @cnt= 0
    SET @lpcnt =0
    SET @str = ”
    DECLARE CONCAT CURSOR FOR
    SELECT DISTINCT id FROM SAMPLE
    OPEN CONCAT
    FETCH NEXT FROM CONCAT INTO @id
    WHILE @@fetch_status =0
    BEGIN
    SELECT @cnt = COUNT(DISTINCT city) FROM SAMPLE WHERE id =@id
    SET @lpcnt= 1
    SET @str = ”
    WHILE (@lpcnt <= @cnt )
    BEGIN
    SELECT @var=city FROM (
    SELECT ROW_NUMBER () OVER (PARTITION BY id ORDER BY id,conid ) AS rowNum,id, city
    FROM SAMPLE) t
    WHERE rownum =@lpcnt AND id =@id
    SET @str=@STR+@var
    SET @lpcnt = @lpcnt + 1
    END
    DELETE from SAMPLE WHERE id=@id
    INSERT INTO SAMPLE(id,city) VALUES(@id, @STR)
    FETCH NEXT FROM CONCAT INTO @id
    END
    CLOSE CONCAT
    DEALLOCATE CONCAT
    GO

    Here the o/p would be :
    id continuationid city

    1 NULL sandeo
    2 NULL SANFRANCISCO
    3 NULL CHICAGO
    4 NULL NEWYORKCITY

    • Thank you for your help Rajesh,
      It works fine , but cursors are very slow .
      Similar query i have to run for Party names (like Buyer, seller etc) nearly 70,000,000 records

      Can we do without using cursor?

  3. If you have a known small maximum number of levels (ei ContinuationID) you could try the following. It should be pretty quick.

    select j1.cityid, j1.CityName + isnull(‘ ‘ + j2.CityName,”) + isnull(‘ ‘ + j3.CityName,”) + isnull(‘ ‘ + j4.CityName,”) as CityName
    from city j1
    left outer join city j2 on j2.ContinuationId=2 and j1.cityid=j2.cityid
    left outer join city j3 on j3.ContinuationId=3 and j1.cityid=j3.cityid
    left outer join city j4 on j4.ContinuationId=4 and j1.cityid=j4.cityid
    where j1.ContinuationId=1

  4. Hi Patil i had tried by using xml . See whether this may solve your issue or not ?

    SELECT DISTINCT Id,
    City = REPLACE(
    (
    SELECT
    City AS [data()]
    FROM
    Sample soh
    WHERE
    soh.Id = c.Id
    FOR XML PATH (”)
    ), ‘ ‘, ‘ ‘)
    FROM
    Sample c
    ORDER BY
    Id

    output :

    ID City
    — ———
    1 sandeo France
    2 SANFRANCISCO America
    3 CHICAGO
    4 NEWYORKCITY

  5. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

  6. iam selecting top 10 rank records for the sql table. i want to get the particular rank holder for the table. for example i want to know the third rank holder only. how to write query for this. please help me.

  7. select name,totmark from
    ( select name,totmark,RANK() over (order by totmark desc) as RankVal
    from dbo.student)RankReport where RankVal=5

  8. Getting this type of error

    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘AdventureWorks2012′. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database ‘AdventureWorks2012′ cannot be opened because it is version 705. This server supports version 661 and earlier. A downgrade path is not supported.

  9. i have downloaded both sql server management studio 2008 and also adventure works I need to have server name to log on to sql server

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  11. I’m trying to use INTERSECT and / or EXCEPT in SQL SERVER.
    but each time I’m facing same error. “Syntax error near INTERSECT / EXCEPT”.

    Please guide me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s