| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Lecture - Full Text Indexing

Page history last edited by Dr. Ron Eaglin 11 years ago

SQL Server Full Text Indexing

 

Summary of Video

 

Demonstrates how to create a catalog and a full text index in SQL Server.

 

Prerequisites

 

SQL Server Management Studio

Knowledge of SQL

Ability to Use Windows Services Manager

 

Video Link

 

http://online1.daytonastate.edu/player2.php?id=563ca5e068bc78b807910338bb4d4279 

 

 

 

Support Materials

 

USE SampleFullTextDB 
GO
CREATE TABLE TextTable (
 [id] INT PRIMARY KEY IDENTITY(1,1),
 [name] NVARCHAR(200),
 [text] NVARCHAR(MAX))
 
EXEC sp_fulltext_database 'Enable'
 
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.TextTable([text])
  KEY INDEX PK__TextTable__7D78A4E7
INSERT INTO dbo.TextTable 
VALUES('Message 1','I feel like my follwing problem had to with my data in my Assignment 7 part 2 to be weird. At first, I didn t use a SP for this, I inputted them as text by selecting the tables manually. In the designer, it resulted in having 5 entries PER song and underneith showed only 1 of my 5 musicians and their related Instrument. This was correct, however, it was very lenghty at 5 entries per song were being listed and I had 8 songs. So I tried to make a SP for this at Dr. Eaglins suggestion that it may be easier. It did clear up the multiple entries, however, I am stuck with the musicians and instruments not showing up. It is most likely due to the fact my SP is wrong, and my Function I made that is pulled from the SP would now allow it (saying it could not be bound) My Function was for pulling ALL Musicians and Instruments. Has anyone had any duplications while intputting a new musician into their database? I also may be inputing the new data into my database wrong, I executed sp_InsertMusician which contains everything (Artist, Album, Musician, Instrument, and Song, each of which have  "if, else statements" to prevent duplicates). Should I have them all in one SP like this? When adding the rest of my one albums songs I ran into a problem, the musicians and instruments are the same for the whole album, however the SP required me to have ALL fields inputted. So I put them in, one by one for the whole album. Now, for the most part it did not duplicate such as the Artist and Album stayed put, however the Songs table did not. There were, I believe 8 or more entries, before I manually deleted them. Deleting them however did not let me unless I deleted the multiple entries which were in the Musicians field as well. This might have described my problem where Designer was showing 5 entries per song 1 for each Musician and their Instrument on that Song. Does anyone else have their inputting of a new album all together in 1 SP? Or do you have mutiple SP s. Based on my long question, I think I am lost. However not too lost, It did work half way.')
 
INSERT INTO dbo.TextTable
VALUES('Message 2', 'This is a long answer. Using the Query Builder here is the stored procedure that I get when I join all the tables, I also have the output from the query with 2 albums below the query - you will not that for every musician it repeats the artis, album, etc...
This is OK - when creating the report you can use grouping to easily tell it to grou according to song, and this will make the report look nice. Under week 7 ( https://cop4709.pbworks.com/w/page/62351004/COP4709%20Course%20Lectures%20By%20Week )- grouping is the 3rd (of s) video s. I added a list control to the blank report, then I added Musician and Instrument (dragged onto list). Right clicked and added Parent Group Song_name, right clicked and added Album_Name, right clicked and added Artist_Name and got report as shown at bottom - grouped correctly with all data.
SELECT
  Albums.id AS [Albums id]
  ,Albums.artistID
  ,Albums.name AS [Albums name]
  ,Albums.[year]
  ,Artists.id AS [Artists id]
  ,Artists.name AS [Artists name]
  ,InstrumentPlayed.id AS [InstrumentPlayed id]
  ,InstrumentPlayed.SongID
  ,InstrumentPlayed.MusicianID
  ,InstrumentPlayed.name AS [InstrumentPlayed name]
  ,Musician.id AS [Musician id]
  ,Musician.name AS [Musician name]
  ,Songs.id AS [Songs id]
  ,Songs.name AS [Songs name]
  ,Songs.albumID
  ,Songs.TrackNumber
FROM
  Albums
  INNER JOIN Artists
    ON Albums.artistID = Artists.id
  INNER JOIN Songs
    ON Albums.id = Songs.albumID
  INNER JOIN InstrumentPlayed
    ON Songs.id = InstrumentPlayed.SongID
  INNER JOIN Musician
    ON InstrumentPlayed.MusicianID = Musician.id
1    1    Goodbye Yellow Brick Road    NULL    1    Elton John    16    1    1    Bass Guitar    1    Dee Murray    1    Funeral For a Friend/Love Lies Bleeding    1    1
1    1    Goodbye Yellow Brick Road    NULL    1    Elton John    17    1    2    Piano    2    Elton John    1    Funeral For a Friend/Love Lies Bleeding    1    1
1    1    Goodbye Yellow Brick Road    NULL    1    Elton John    18    1    3    Electric Guitar    3    Davey Johnstone    1    Funeral For a Friend/Love Lies Bleeding    1    1
1    1    Goodbye Yellow Brick Road    NULL    1    Elton John    19    1    4    Drums    4    Nigel Olsson    1    Funeral For a Friend/Love Lies Bleeding    1    1
1    1    Goodbye Yellow Brick Road    NULL    1    Elton John    20    1    5    ARP Synthesizer    5    David Hentschel    1    Funeral For a Friend/Love Lies Bleeding    1    1
2    2    Tommy    NULL    2    The Who    21    6    6    Vocals    6    Roger Daltrey    6    Overture    2    1
2    2    Tommy    NULL    2    The Who    22    6    7    Electric Guitar    7    Pete Townshend    6    Overture    2    1
2    2    Tommy    NULL    2    The Who    23    6    8    Bass Guitar    8    John Entwistle    6    Overture    2    1
2    2    Tommy    NULL    2    The Who    24    6    9    Drums    9    Keith Moon    6    Overture    2    1')  
SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text],'Goodbye')
SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text],'SuggeStioN')
SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text], 'Goodbye AND Road')
SELECT id, name FROM dbo.TextTable WHERE CONTAINS([text], ' "Go*" ')
 

 

 

 

All Materials Copyright 2012 Dr. Ron Eaglin

Comments (0)

You don't have permission to comment on this page.