Database Design with SSMS

Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…

Basic Training – Tables

Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic part of a database is a TABLE which consists of COLUMNS. A important decision during the initial design is to choose the data types that will capture the information you want in the least amount of space. I did a good job covering the various…

Database Files & Objects – Part 1

I am currently reading Kalen Delany and others book on SQL Server 2008 System internals. If you have not read this book from cover to cover, you should. It has a wealth of knowledge about database engine details. To demonstrate key ideas from the book, I am going to expand on the [MATH] database that contains PRIME numbers. The trial division algorithm inside this database uses a brute force method for calculating prime numbers. This article will focus on files and objects that can be viewed through the system views,…

Database Files – Part 3

DATABASE SNAPSHOTS were added to the SQL Server product in the 2005 version. They are great for saving a version of the database before major data changes happen and perfect for rolling back the changes if they are not successful. While books online suggest other uses, I think this one is the best. Please see the limitations of snapshots before creating one. I am going to explain how to create a database snapshot, how to revert back to a snapshot after corrupted occurs, and how to drop a database snapshot.…

Crafting Databases – Part 1

The CREATE DATABASE SQL statement is part of the Data Definition Language (DML) defined by Codd and is used define (CREATE), modify (ALTER) or remove (DROP) databases along with there associated files. Before I talk about crafting a database, I want to classify SQL statements by four known groups. Data Definition Language (DDL) statements are SQL statements that support the definition or declaration of database objects. CREATE ALTER DROP Data Manipulation Language (DML) is a vocabulary used to add, modify, query, or remove data from a SQL Server database. SELECT…