[Resolved] - AdventureWorks2008 sample database not available after installation

 

Many of you who have tried installing AdventureWorks2008 sample database, might have come across with an issue, which after installing the database package (using the executable), 'AdventureWorks2008' is missing from the database list. This is not a bug in SQL server or any error in the installer.
 

 
In order to get the 'AdventureWorks2008' installed following should be fulfilled:
 
1.          Full-text search should be installed and running
 

 
2.          FILESTREAM should be enabled and running
 

 
Enabling FILESTREAM:

1. Go to SQL Server Configuration Manager
2. Click on SQL Server Services (left pane)
3. Right click on the correct SQL Server Service (if you have more than one instance)
4. Select properties from the menu
5. Go to FILESTREAM tab and check the ‘Enable FILESTREAM for Transact-SQL access’ check box.
6. Apply changes.

And if you still don’t see it in your database list, please reinstall the package.

Repeating a SQL row based on a value in a different column

There are times that we get requirements such as populating and duplicate SQL rows, based on a value, on another column. E.g.: In an inventory system when items are received those details will be saved in the following format (ItemDetails) :

And we are asked to create a GUI for end user to enter ‘Serial Numbers’ for each item. And we have to repeat the above mentioned item codes number of times which equals to the ‘ItemQty’. Of course we can achieve that using a SQL cursor or iterate using C# coding. But following example I will show how to do it using SQL.

The task would have been very simple if we would have a another table with a structure similar to this: (TempTable)

So when the two table are joined ‘ItemDetails’ will repeat according to the row count of the ‘TempTable’. But it is not very practical, and it will result in duplicating data, which will grow your database un-necessary when time goes.

But instead we can use on single table which contains a series of numbers. These numbers will start from ‘1’. And the end should be the maximum quantity which an Item can have. For this example I will take ‘10’ as the maximum value. And that table should have the following structure.

Use the following T-SQL statement to create the table:

CREATE TABLE [IntermediateTable](
    [MaxQty] [int] NULL
) ON [PRIMARY]

For this example I have inserted up to 20. But in a real world scenario it may be required to enter values (More than 1000). In a similar situation you can use the following T-SQL statement to insert values.

insert into IntermediateTable
select 
    thousand.number*1000 + 
    hundred.number*100 + 
    ten.number*10 + 
    one.number
from(
    select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) one
cross join (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) ten
cross join (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) hundred
cross join (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) thousand
where (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number) > 0 and (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number) <= 2000
order by (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number)

** Please Note : Above statement will insert values from ‘1’ to ‘2000’. But removing the where condition will insert values from ‘0’ to ‘10000’.

And using the following T-SQL statement we can join the table and produce the required result.

select A.*
from ItemDetails as A
join IntermediateTable as B on B.MaxQty <= A.ItemQty
where A.BatchNo = 'B1'

Passing parameters for dynamically created SQL queries

There are times that we need to create SQL queries dynamically and pass values to parameters. You can always assign values with a syntax similar to “… WHERE ColumnName = ‘ + @Value + ‘ and …”. But the disadvantage of using the above syntax is, that you have to provide correct formatting according the data type of the column.

This can be prevented using this type of solution. (Assume you have to get a count of records which matches a certain condition which will be provide outside the query)

 

   1: declare @Value            as nvarchar(50)

   2: declare @Sql            as nvarchar(100)

   3: declare @Parameters        as nvarchar(100)

   4: declare @Count            as int

   5:  

   6: set @Value = 'ValueX'

   7: set @Sql = 'set @Count = (select count(*) from TableName where ColValue = @Value)'

   8: set @Parameters = '@Count int output, @Value nvarchar(100)'

   9:  

  10: exec sp_executesql @Sql,@Parameters,@Count output,@Value

  11:  

  12: select @Count

Removing Duplicate Records From a MS SQL Table – (MS SQL 2005 or above)

Have you ever been in a situation that your SQL tables contain duplicate records, where you have not defined a primary key or an auto increment field. And you need to keep one record and delete the rest.

The usual method of  doing this is to use a temporary table or to use a cursor. But there is another method of doing this using a single query in SQL 2005 or above.

To illustrate this first I will create the following table.

create table SampleTable(
    id        int not null,
    name    varchar(20) not null,
    age        int not null
    )

Now I will insert some duplicate records to the above created table.

insert into SampleTable    (id,name,age) values (1,'John',30)
insert into SampleTable    (id,name,age) values (1,'John',30)
insert into SampleTable    (id,name,age) values (1,'John',30)
insert into SampleTable    (id,name,age) values (1,'John',30)
insert into SampleTable    (id,name,age) values (1,'John',30)
insert into SampleTable    (id,name,age) values (2,'Mary',26)
insert into SampleTable    (id,name,age) values (2,'Mary',26)
insert into SampleTable    (id,name,age) values (2,'Mary',26)
insert into SampleTable    (id,name,age) values (2,'Mary',26)
insert into SampleTable    (id,name,age) values (3,'Ann',25)
insert into SampleTable    (id,name,age) values (3,'Ann',25)
insert into SampleTable    (id,name,age) values (3,'Ann',25)
insert into SampleTable    (id,name,age) values (3,'Ann',25)
insert into SampleTable    (id,name,age) values (3,'Ann',25)
insert into SampleTable    (id,name,age) values (4,'James',21)

 

Using the below given query you can easily find out the duplicates (number of duplicate records).

select SUM(rec_count) as rec_count from(
select COUNT (*) - 1 as rec_count from SampleTable group by CHECKSUM(*)
) T having COUNT(*) > 1

On the above query I have remove one record (COUNT (*) - 1), since one should be there as a valid record. And you really don’t need ‘having COUNT(*) > 1’, since non duplicate record count(*) will return 1 and count(*)-1 will be 0. It’s there for the ease of readability. So if you execute the above query you will get 11 records as the record count (Total 15 records, 4 valid records. So 15-4 = 11 records).

If you can see I have used ‘CHECKSUM(*)’. This to avoid typing all field names. Without using that the query would be like ‘group by id,name,age’.

And finally we can build the query to delete duplicates like this. First we must find the valid records, which should not be deleted. The way to do is using the function ‘ROW_NUMBER’. Using that we assign a unique row number for each record and select the maximum row number for each group. Then we will only get one record per group.

select MAX(row_num) from (
select ROW_NUMBER() over (order by checksum(*)) as row_num, CHECKSUM(*) as ChkSum  
from SampleTable
) as T Group By ChkSum

And if you execute the above query you will get the following result:

It will return row numbers 5,9,14 and 15 as valid records which we must keep. And we must only delete records which the row number is not equal to the ones that’s been returned from the above mentioned query. First we’ll select those records (Only for checking purpose). You can select those records using the following query.

select T.* from(
    select ROW_NUMBER() over (order by checksum(*)) as row_num, CHECKSUM(*) 
    as ChkSum from SampleTable) as T
    where T.row_num not in (
        select MAX(row_num) from (
            select ROW_NUMBER() over (order by checksum(*)) as row_num, CHECKSUM(*) 
            as ChkSum from SampleTable
        ) as T Group By ChkSum
    )

And if you execute the above query you will get the following result.

So if you see closely row numbers 5,9,14 and 15 are not there. So we can sure, that we are deleting the correct set of records. So in order to delete the duplicated we can use the following query.

    
delete T from(
    select ROW_NUMBER() over (order by checksum(*)) as row_num, CHECKSUM(*) 
    as ChkSum from SampleTable) as T
    where T.row_num not in (
        select MAX(row_num) from (
            select ROW_NUMBER() over (order by checksum(*)) as row_num, CHECKSUM(*) 
            as ChkSum  from SampleTable
        ) as T Group By ChkSum
    )    
    

And if you query the table you will get the following result.