-- -- 1 - Drop existing database -- -- Use master USE [master] GO -- Delete existing databases. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE') DROP DATABASE [WILDLIFE] GO -- -- 2 - Create database using 5 SAN drives -- -- Define the db with file groups CREATE DATABASE [WILDLIFE] ON PRIMARY ( NAME = 'WildLifeData', FILENAME = 'C:\MSSQL\DATA\Wild_Life_Data.mdf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 500KB), FILEGROUP MULTIFILE ( NAME = 'Multi1_Data', FILENAME = 'C:\MSSQL\DATA\Multi1_Data.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10MB), ( NAME = 'Multi2_Data', FILENAME = 'C:\MSSQL\DATA\Multi2_Data.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10MB), ( NAME = 'Multi3_Data', FILENAME = 'C:\MSSQL\DATA\Multi3_Data.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10MB), ( NAME = 'Multi4_Data', FILENAME = 'C:\MSSQL\DATA\Multi4_Data.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10MB), ( NAME = 'Multi5_Data', FILENAME = 'C:\MSSQL\DATA\Multi5_Data.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10MB) LOG ON ( NAME = 'WildLifeLog', FILENAME = 'C:\MSSQL\LOG\Wild_Life_Log.ldf', SIZE = 12MB, MAXSIZE = 120MB, FILEGROWTH = 10MB) GO -- Switch owner to system admin ALTER AUTHORIZATION ON DATABASE::[WILDLIFE] TO SA; GO -- -- 3 - Create table & index using MULTIFILE -- -- Use the database USE [WILDLIFE] GO -- Create the animals table CREATE TABLE ANIMALS ( ID INT NOT NULL IDENTITY (1, 1), NAME VARCHAR(200) NOT NULL, CATEGORY INT DEFAULT (0) ) ON [MULTIFILE] GO -- Add index on name (non-clustered) CREATE CLUSTERED INDEX IDX_ANIMAL_CATEGORY ON DBO.ANIMALS(CATEGORY) ON [MULTIFILE]; GO -- -- Create a function, map name -> code -- CREATE FUNCTION UFN_MY_CATEGORIES(@NAME VARCHAR(200)) RETURNS INT AS BEGIN -- Default value DECLARE @RETVAL INT = 0; -- 2 POWER 0 IF (LOWER(@NAME) LIKE '%fly%') SET @RETVAL = @RETVAL + 1; -- 2 POWER 1 IF (LOWER(@NAME) LIKE '%pig%') SET @RETVAL = @RETVAL + 2; -- 2 POWER 2 IF (LOWER(@NAME) LIKE '%bat%') SET @RETVAL = @RETVAL + 4; -- 2 POWER 3 IF (LOWER(@NAME) LIKE '%dog%') SET @RETVAL = @RETVAL + 8; -- RETURN THE CODE RETURN(@RETVAL); END; -- -- Create a trigger to categorize name -- CREATE TRIGGER TRG_CATEGORIZE_ANIMALS ON ANIMALS AFTER INSERT, UPDATE AS BEGIN -- Detect inserts (1 .. n) IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN -- Update records UPDATE ANIMALS SET CATEGORY = dbo.UFN_MY_CATEGORIES(I.NAME) FROM ANIMALS A JOIN INSERTED I ON A.ID = I.ID; -- Debugging PRINT 'INSERT DETECTED, CREATED CATEGORY'; END; -- Detect deletes (1 .. n) IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted) BEGIN -- Debugging PRINT 'DELETE DETECTED, NOTHING TO DO'; END; -- Detected updates (1 .. n) IF EXISTS (SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN -- Update records UPDATE ANIMALS SET CATEGORY = dbo.UFN_MY_CATEGORIES(I.NAME) FROM ANIMALS A JOIN INSERTED I ON A.ID = I.ID; -- Debugging PRINT 'UPDATE DETECTED, ADJUST CATEGORY'; END; END GO -- Use sample data from WIKIPEDIA INSERT INTO ANIMALS (NAME) VALUES ('Aardvark'), ('Aardwolf'), ('Afghan Hound'), ('Albatross'), ('Alligator'), ('Alpaca'), ('American Robin'), ('Anaconda'), ('Angelfish'), ('Anglerfish'), ('Ant'), ('Anteater'), ('Antelope'), ('Antlion'), ('Ape'), ('Aphid'), ('Armadillo'), ('Arrow crab'), ('Asp'), ('Ass'), ('Baboon'), ('Badger'), ('Bald Eagle'), ('Bandicoot'), ('Barnacle'), ('Basilisk'), ('Barracuda'), ('Bass'), ('Basset Hound'), ('Bat'), ('Beaked whale'), ('Bear'), ('Beaver'), ('Bedbug'), ('Bee'), ('Beetle'), ('Bird'), ('Bison'), ('Blackbird'), ('Black panther'), ('Black Widow Spider'), ('Blue Jay'), ('Blue Whale'), ('Boa'), ('Bobcat'), ('Bobolink'), ('Bonobo'), ('Booby'), ('Box jellyfish'), ('Boston Terrier'), ('Bovid'), ('Buffalo'), ('Bug'), ('Bulldog'), ('Bull Terrier'), ('Butterfly'), ('Buzzard'), ('Camel'), ('Canid'), ('Cape Buffalo'), ('Capybara'), ('Cardinal'), ('Caribou'), ('Carp'), ('Cat'), ('Caterpillar'), ('Catfish'), ('Cattle'), ('Centipede'), ('Cephalopod'), ('Chameleon'), ('Cheetah'), ('Chickadee'), ('Chicken'), ('Chihuahua'), ('Chimpanzee'), ('Chinchilla'), ('Chipmunk'), ('Clam'), ('Clownfish'), ('Cobra'), ('Cockroach'), ('Cod'), ('Collie'), ('Condor'), ('Constrictor'), ('Coral'), ('Cougar'), ('Cow'), ('Coyote'), ('Crab'), ('Crane'), ('Crane Fly'), ('Crawdad'), ('Crayfish'), ('Cricket'), ('Crocodile'), ('Crow'), ('Cuckoo'), ('Daddy longlegs'), ('Damselfly'), ('Deer'), ('Dingo'), ('Dinosaur'), ('Dog'), ('Dolphin'), ('Donkey'), ('Dormouse'), ('Dove'), ('Dragonfly'), ('Duck'), ('Dung beetle'), ('Eagle'), ('Earthworm'), ('Earwig'), ('Echidna'), ('Eel'), ('Egret'), ('Elephant'), ('Elephant seal'), ('Elk'), ('Emu'), ('English pointer'), ('English Setter'), ('Ermine'), ('Falcon'), ('Ferret'), ('Finch'), ('Firefly'), ('Fish'), ('Flamingo'), ('Flea'), ('Fly'), ('Flyingfish'), ('Fowl'), ('Fox'), ('Frog'), ('Fruit Bat'), ('Galliwasp'), ('Gazelle'), ('Gecko'), ('Gerbil'), ('German Shepherd'), ('Giant Panda'), ('Giant squid'), ('Gibbon'), ('Gila monster'), ('Guanaco'), ('Guineafowl'), ('Giraffe'), ('Goat'), ('Golden Retriever'), ('Goldfinch'), ('Goldfish'), ('Goose'), ('Gopher'), ('Gorilla'), ('Grasshopper'), ('Great Blue Heron'), ('Great Dane'), ('Great white shark'), ('Greyhound'), ('Grizzly Bear'), ('Ground Sloth'), ('Grouse'), ('Guinea pig'), ('Gull'), ('Guppy'), ('Haddock'), ('Halibut'), ('Hammerhead shark'), ('Hamster'), ('Hare'), ('Harrier'), ('Hawk'), ('Hedgehog'), ('Hermit crab'), ('Heron'), ('Herring'), ('Hippopotamus'), ('Hookworm'), ('Hornet'), ('Horse'), ('Hound'), ('Hoverfly'), ('Human'), ('Hummingbird'), ('Humpback Whale'), ('Husky'), ('Hyena'), ('Iguana'), ('Impala'), ('Insect'), ('Irish Setter'), ('Irish Wolfhound'), ('Irukandji jellyfish'), ('Jackal'), ('Jaguar'), ('Jay'), ('Jellyfish'), ('Kangaroo'), ('Kangaroo mouse'), ('Kangaroo rat'), ('Kingfisher'), ('Kite'), ('Kiwi'), ('Koala'), ('Koi'), ('Komodo dragon'), ('Krill'), ('Labrador Retriever'), ('Ladybug'), ('Lamprey'), ('Lark'), ('Leech'), ('Lemming'), ('Lemur'), ('Leopard'), ('Leopon'), ('Liger'), ('Lion'), ('Lizard'), ('Llama'), ('Lobster'), ('Locust'), ('Loon'), ('Louse'), ('Lungfish'), ('Lynx'), ('Macaw'), ('Mackerel'), ('Magpie'), ('Mammal'), ('Mammoth'), ('Mandrill'), ('Manta Ray'), ('Marlin'), ('Marmoset'), ('Marmot'), ('Marsupial'), ('Marten'), ('Mastiff'), ('Mastodon'), ('Meadowlark'), ('Meerkat'), ('Mink'), ('Minnow'), ('Mite'), ('Mockingbird'), ('Mole'), ('Mollusk'), ('Mongoose'), ('Monitor lizard'), ('Monkey'), ('Moose'), ('Mosquito'), ('Moth'), ('Mountain goat'), ('Mouse'), ('Mule'), ('Muskox'), ('Mussel'), ('Narwhal'), ('Newt'), ('Nightingale'), ('Ocelot'), ('Octopus'), ('Old English Sheepdog'), ('Opossum'), ('Orangutan'), ('Orca'), ('Ostrich'), ('Otter'), ('Owl'), ('Ox'), ('Oyster'), ('Panda'), ('Panther'), ('Panthera hybrid'), ('Parakeet'), ('Parrot'), ('Parrotfish'), ('Partridge'), ('Peacock'), ('Peafowl'), ('Pekingese'), ('Pelican'), ('Penguin'), ('Perch'), ('Peregrine Falcon'), ('Persian Cat'), ('Pheasant'), ('Pig'), ('Pigeon'), ('Pike'), ('Pilot whale'), ('Pinniped'), ('Piranha'), ('Planarian'), ('Platypus'), ('Polar bear'), ('Pony'), ('Poodle'), ('Porcupine'), ('Porpoise'), ('Portuguese Man o War'), ('Possum'), ('Prairie dog'), ('Prawn'), ('Praying Mantis'), ('Primate'), ('Puffin'), ('Puma'), ('Python'), ('Quail'), ('Quelea'), ('Quokka'), ('Rabbit'), ('Raccoon'), ('Rainbow trout'), ('Rat'), ('Rattlesnake'), ('Raven'), ('Ray (Batoidea)'), ('Ray (Rajiformes)'), ('Red Panda'), ('Reindeer'), ('Rhinoceros'), ('Right whale'), ('Roadrunner'), ('Rodent'), ('Rook'), ('Rooster'), ('Roundworm'), ('Saber-toothed cat'), ('Sailfish'), ('Saint Bernard'), ('Salamander'), ('Salmon'), ('Sawfish'), ('Scale insect'), ('Scallop'), ('Scorpion'), ('Sea anemone'), ('Sea cow'), ('Seahorse'), ('Sea lion'), ('Sea slug'), ('Sea urchin'), ('Setter'), ('Shark'), ('Sheep'), ('Shrew'), ('Shrimp'), ('Siamese Cat'), ('Silkworm'), ('Silverfish'), ('Skink'), ('Skunk'), ('Sloth'), ('Slug'), ('Smelt'), ('Snail'), ('Snake'), ('Snipe'), ('Snow Leopard'), ('Sockeye salmon'), ('Sole'), ('Spaniel'), ('Sparrow'), ('Sperm whale'), ('Spider'), ('Spider monkey'), ('Spoonbill'), ('Squid'), ('Squirrel'), ('Starfish'), ('Star-nosed Mole'), ('Steelhead trout'), ('Stingray'), ('Stoat'), ('Stork'), ('Sturgeon'), ('Sugar Glider'), ('Swallow'), ('Swan'), ('Swift'), ('Swordfish'), ('Swordtail'), ('Tabby cat'), ('Tahr'), ('Takin'), ('Tapeworm'), ('Tapir'), ('Tarantula'), ('Tarsier'), ('Tasmanian Devil'), ('Termite'), ('Tern'), ('Terrier'), ('Thrush'), ('Tick'), ('Tiger'), ('Tiger shark'), ('Tigon'), ('Toad'), ('Tortoise'), ('Toucan'), ('Toy Poodle'), ('Trapdoor spider'), ('Tree frog'), ('Trout'), ('Tuna'), ('Turkey'), ('Turtle'), ('Tyrannosaurus'), ('Urial'), ('Vampire bat'), ('Vampire Squid'), ('Vicuna'), ('Viper'), ('Vole'), ('Vulture'), ('Wallaby'), ('Walrus'), ('Wasp'), ('Warbler'), ('Water Buffalo'), ('Weasel'), ('Whale'), ('Whippet'), ('Whitefish'), ('Whooping Crane'), ('Wildcat'), ('Wildebeest'), ('Wildfowl'), ('Wolf'), ('Wolverine'), ('Wombat'), ('Woodpecker'), ('Worm'), ('Wren'), ('X-ray fish'), ('Yak'), ('Zebra') GO -- Select everything with word 'fly' SELECT * FROM dbo.ANIMALS WHERE CATEGORY = 1 GO