-- -- Create the database & table -- -- Use the master database USE [master]; GO -- Delete existing database IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE') 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 [dbo].[ANIMALS] ( ID INT NOT NULL IDENTITY (1, 1), NAME VARCHAR(200) NOT NULL ); 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 -- -- Delete does not reseed id -- -- Remove two hundred items DELETE FROM dbo.ANIMALS WHERE ID > 245; GO -- Notice that next identity value is 446 DBCC CHECKIDENT ('dbo.ANIMALS', NORESEED); GO -- Add back last 5 items INSERT INTO ANIMALS (NAME) VALUES ('Worm'), ('Wren'), ('X-ray fish'), ('Yak'), ('Zebra'); GO -- Notice that id started with 446 SELECT TOP 5 * FROM dbo.ANIMALS ORDER BY ID DESC; GO -- -- Use DBCC CHECKIDENT to reseed -- -- Remove last 5 items DELETE FROM dbo.ANIMALS WHERE ID >= 446; GO -- Reset the sequence DBCC CHECKIDENT ('dbo.ANIMALS', RESEED, 245); GO -- Add back last 5 items INSERT INTO ANIMALS (NAME) VALUES ('Worm'), ('Wren'), ('X-ray fish'), ('Yak'), ('Zebra'); GO -- Notice that our sequence is restored SELECT TOP 5 * FROM dbo.ANIMALS ORDER BY ID DESC; GO -- -- Truncate table reseeds the identity -- -- Remove all data TRUNCATE TABLE dbo.ANIMALS; GO -- Add back last 5 items INSERT INTO ANIMALS (NAME) VALUES ('Worm'), ('Wren'), ('X-ray fish'), ('Yak'), ('Zebra'); GO -- Notice that our sequence is restored SELECT * FROM dbo.ANIMALS; GO