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)
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 :)
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
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?
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
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
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.
select name,totmark from
( select name,totmark,RANK() over (order by totmark desc) as RankVal
from dbo.student)RankReport where RankVal=5
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.
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
Awesome. Simple, direct, and clear. Thank you!
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.