Beginning SQL Server Database Administration: Key Concepts

After inheriting an existing SQL Server infrastructure, I’ve been working on improving my knowledge of database administration and Microsoft SQL Server. This is all being chronicled on the Petri IT Knowledgebase, beginning with Getting Started with Microsoft SQL Server, and I’ll continue in this article by covering my initial experiences through the study of the fundamentals of SQL Server. This article provides a summary of introductory training and initial work with databases, tables, queries and SQL Server Management Studio.

The Petri IT Knowledgebase – Getting Started with Microsoft SQL Server Article Series

Database Types

Databases are primarily one of three different types: flat-file databases, hierarchical databases and relational databases. Flat-file databases have a simplistic, two-dimensional design. Excel spreadsheets and comma-separated value files are examples of flat-file databases. Hierarchical databases have a parent-child relationship built into its structure. An example of a hierarchical database is a file and folder structure on a hard drive. Relational databases are what we now think of when we say database. It’s like taking several of those flat-file databases (‘tables’ in this context) and linking them together through references that they both share in common.

Microsoft SQL Server is relational database software, so let’s explore some relational database concepts.

Relational Database Concepts

A relational database includes one or more tables, which are similar to the flat-file databases. Each table is made up of one or more domains, which are columns that describe the data being recorded. Each entry in the table is referred is an entity.

Normalization, tables, and information consolidation: Normalization is the result of the process of removing redundancies in the data. Normalization is an important concept and is best described with an example.

If you have a table for holding information about people, a simple thing to imagine is using a domain for the city. Let’s assume that you have 10 people on your list, with five living in New York and five living in “Seattle.

To normalize that data, you could create an additional table called cityname. Inside that table, “New York” would be the first city with an ID of “1”, and “Seattle” would be entered with an ID of “2”. You would then update your people table to reference the city table instead of having the cities entered for each field. Even our simple example of 10 people would benefit by having “New York” at 8 characters and “Seattle” at 7 characters replaced with an ID number that is only one character long.

Indexes preload the data so it’s quick to search and sort: An index can be setup to provide faster access to certain key columns of data in a table, such as a title or name field so that queries can be processed more quickly. While each index that you specify has an inherent cost associated with it, it’s worth it to identify those fields that will be searched and sorted so that they can be indexed.

Pages are the building blocks of SQL Server data storage: The databases are stored in files on disk. These files are made up of 8 KB chunks of data called pages. Every single row of data from a table needs to be able to fit inside a page. Pages are loaded into memory to make a change to an entity.

Transaction logs: Transaction logs are used to specify which changes have been made to a page since it was last saved to disk and keep a history of even the smallest changes. This is to protect the data while it is in volatile RAM instead of on the hard drive. In case of a power failure, SQL Server begins replaying all of the unsaved changes back into memory.

Database data types: Data can be stored in several different types. DateTime, Numbers, True/False (Boolean), and Images are examples of different data types. Each data type used has an impact on the size of the database. Large numbers take up more space than a simple true/false when stored on disk, so using the smallest data type possible pays in the long run.

Binary Large Objects (BLOBs): BLOBs are items such as images, sound files, and attachments that are stored on disk as separate files, where a pointer to the file is stored inside the table.

The SQL Language

The SQL language is broken into three parts: Data Manipulation Language (DML), Data Definition Language (DDL), and Control Language.

  • Data Manipulation Language (DML): First, there is the Data Manipulation Language (DML). DML queries and updates data in the tables. SELECT, INSERT, UPDATE and DELETE are all commands from DML.
  • Data Definition Language (DDL): DDL provides control over the database itself instead of the rows of data in the database. USE, CREATE and DROP are all in DDL.
  • Control Language: Control language includes logical structures for controlling flow, repetition and if/then logic.

Introducing SQL Server Management Studio

SQL Server Management Studio (SSMS) is a Microsoft-supplied management tool used for managing your SQL Server instances. It’s installed from the ISO, disk, or download location of your SQL Server software. It includes a place for working with database objects and SQL queries. You can also explore some of the features through loading up a sample database from Microsoft called AdventureWorks.

Download: AdventureWorks OLTP Database Diagram

Tables

Tables can be created in SQL Server Management Studio. You can either use the Object Explorer or the Query Analyzer. You can also use SSMS to review the design of existing tables and script their creation to replicate a table in a different database or environment. SQL Server actually keeps its own configuration inside database tables. These tables are inside the master database, though later versions of SQL Server have moved them from tables to views.

Views

A view can be thought of as a virtual table. It can show some rows or columns, or it can also filter and order a subset of the data in the tables to offer a more selective and useful view of the data. Just like tables, views can be completely managed by using SQL Server Management Studio.

Stored Procedures

Stored procedures are similar to functions in a script. They are programmable tasks that can be called repeatedly. Stored procedures also include security of their own, so some administrators or logged-in users may be able to process some stored procedures, but not others.

Getting up to speed quickly

In our SQL skills progression, this first foray into the terms and descriptions of our SQL components is already starting to take shape. Understanding how all of these components start to work together will be that much easier now.

While I’m still not a SQL expert, I’d love to share what I’ve already learned with you. We’ll be posting more updates to this series in the near future so you can follow my progress. You can also reach out to me on Twitter or Google+ to let me know how you’re doing on your own journey towards SQL Server enlightenment.

Related Article: