Hashing, Snapshots, Full Text Indexes

Use Automart
--inserting non english characters
--into a table
--the N stands for unicode
--the underlying type must be Nvarchar not varchar
Insert into Person(LastName, FirstName)
Values(N'κονγεροσ',N'Στεφονοσ')

--unicode character set
--1st 255 8 bits Ascii
--32000 16 bit character
--   24 bit character
--   32 bit character
--   64 bit character

Select * From Person

Select * From Customer.RegisteredCustomer

--hashbytes is a function that "hashes"
--a value. You can use various hash methods
--MD5 is one
Declare @password  varbinary(500)
Set @password=Hashbytes('MD5','jpass')
Select @password

--uses shai hash method
Declare @password  varbinary(500)
Set @password=Hashbytes('sha1','jpass')
Select @password

--add a column to the Registered customer table
Alter table Customer.RegisteredCustomer
Add hashedPassword varbinary(500)

Select * from Customer.RegisteredCustomer

--add a hash for the first customer
Update Customer.RegisteredCustomer
Set hashedPassword=HASHBYTES('MD5','jpass')
Where RegisteredCustomerID=1

--simulate checking the hash as if
--for a login
Declare @Password Varbinary(500)
Set @password = HASHBYTES('MD5','jpass')
if Exists
 (Select Hashedpassword from Customer.RegisteredCustomer
   Where hashedPassword=@password)
Begin
 Print 'Login successful'
End
Else
Begin
   Print 'Login Failed'
End

--snapshots
--a snapshot takes a picture of a database
--at a moment in time
--it only physically stores the data if it has changed in 
--the underlying database. the original form is copied to
--the snapshot
Create Database Automart_Snapshot
On
(Name ='Automart', Filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Automart_Snapshot.ds')
As 
Snapshot of Automart

Use Automart_Snapshot

Select * From Person

Use Automart
Delete From Person where Personkey=54
--make some changes in the underlying database and then 
--compare the two database
Update Person
Set FirstName='Jason'
where personkey=1

--in an emergancy you can recover
--a database from a snapshot
use Master
Restore Database Automart from Database_Snapshot='Automart_Snapshot'

--Full Text Catalog
use Master

--add a filegroup
Alter Database Automart
Add Filegroup FullTextCatalog

use Automart
--add a table with some text
Create Table TextTest
(
   TestId int identity (1,1) primary key,
   TestNotes Nvarchar(255)
)

--insert text
Insert into TextTest(TestNotes)
Values('For test to be successful we must have a lot of text'),
('The test was not successful. sad face'),
('there is more than one test that can try a man'),
('Success is a relative term'),
('It is a rare man that is always successful'),
('The root of satisfaction is sad'),
('men want success')

Select * From TextTest

--create full text catalog
Create FullText Catalog TestDescription
on Filegroup FullTextCatalog

--Create a full text index
Create FullText index on textTest(TestNotes)
Key Index PK__TextTest__8CC33160412EB0B6
on TestDescription
With Change_tracking auto

--run queries on the full text catalog

--find all instances that have the word "sad"
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'sad')

--do the same with successful
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success"')

--look for any words containing the letters "success"
--the * is a wildcard
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success*"')

--looks for all grammatical forms of a word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' Formsof (Inflectional, man)')

--finds words near another word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' not near successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' sad near successful')