-- -- Create the database & table -- -- Use to remove if exists /* DROP DATABASE WILDLIFE GO */ -- Create a very basic database CREATE DATABASE WILDLIFE; GO -- 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 ) GO -- Use to remove data if needed /* TRUNCATE TABLE ANIMALS GO */ -- -- Load the table with data -- -- 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 -- -- Heap Example -- -- Remove clean buffers & clear plan cache CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO -- Show time & i/o SET STATISTICS TIME ON SET STATISTICS IO ON GO -- Select everything with word 'fly' SELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%' GO -- -- NonClustered Index Example -- -- Remove clean buffers & clear plan cache CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO -- Add index on name (non-clustered) CREATE NONCLUSTERED INDEX IDX_ANIMAL_NAME ON DBO.ANIMALS(NAME); GO -- Select everything with word 'fly' SELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%' GO -- Drop index on name DROP INDEX ANIMALS.IDX_ANIMAL_NAME; GO -- -- Clustered Index Example -- -- Remove clean buffers & clear plan cache CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO -- Add index on name (clustered) CREATE CLUSTERED INDEX IDX_ANIMAL_NAME ON DBO.ANIMALS(NAME); GO -- Select everything with word 'fly' SELECT * FROM dbo.ANIMALS WHERE NAME LIKE '%FLY%' GO -- Drop index on name DROP INDEX ANIMALS.IDX_ANIMAL_NAME; GO -- Hide time & i/o SET STATISTICS TIME OFF SET STATISTICS IO OFF GO