SQL SERVER – What is AdventureWorks?

A 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 github 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:

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Documentation, SQL Download
Previous Post
SQL SERVER – Disable Clustered Index and Data Insert
Next Post
SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Related Posts

12 Comments. Leave new

  • I apparently knew only the partial answer to the question…I was not aware that DB was updated at each release.
    Thanks for the information :)

    Reply
  • Madivalappa Patil
    May 4, 2010 1:55 pm

    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

    Reply
  • 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

    Reply
    • Madivalappa Patil
      May 6, 2010 3:32 pm

      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?

      Reply
  • Chris Scimeca
    May 7, 2010 1:43 am

    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

    Reply
  • 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

    Reply
  • 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.

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

    Reply
  • 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.

    Reply
  • Ahmed Nazir
    April 5, 2013 4:32 am

    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

    Reply
  • Awesome. Simple, direct, and clear. Thank you!

    Reply
  • 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.

    Reply

Leave a Reply

Menu
Exit mobile version