Overview

Introduction

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Identify Usage of New Data Types
  2. Design Tables
  3. Design Data Integrity

Exercise 1 – Identify Usage of New Data Types

In this exercise, you will perform the tasks required to create and maintain database tables with usage of new data types. To understand this technology, please refer to your course material or read the material available at the following link: http://technet.microsoft.com/en-us/library/ms189104.aspx

Geography

The geography data type is a spatial data type which is implemented as a .NET common language runtime (CLR) data type in SQL Server 2008. It represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal, that is, round-earth data, like GPS latitude and longitude coordinates.

This data type is predefined and available in all the databases. You can create table columns with this data type and operate on geography data in the same way as you use other system-supplied types.

Exercise 2 – Design Tables

In this exercise, you will perform the tasks required to create and maintain database tables with usage of new data types. To understand this technology, please refer to your course material or read the material available at the following link: http://technet.microsoft.com/en-us/library/ms189104.aspx

Table width

Tables are database objects that contain all the data in a database. A table definition is a collection of columns. In tables, data is organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field within the record. For example, a table that contains employee data for a company can contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

Tables in SQL Server have the following main components:

  • Columns

Each column represents some attribute of the object modeled by the table, such as a parts table having columns for ID, color, and weight.

  • Rows

Each row represents an individual occurrence of the object modeled by the table. For example, the parts table would have one row for each part carried by the company.

Sizing data types

INROWDATA (BLOBs)

Small-to-medium large value types (varchar(max), nvarchar(max), varbinary(max), and xml) and large object (LOB) data types (text, ntext, and image) can be stored in a data row. This behavior is controlled by using two options in the sp_tableoption system stored procedure: the large value types out of row option for large value types, and the text in row option for large object types. These options are best used for tables in which the data values of any one of these data types are typically read or written in one unit, and most statements that reference the table refer to this kind of data. Depending on usage or workload characteristics, storing data in-row may not be useful.

Unless the text in row option is set to ON or to a specific in-row limit, text, ntext, or image strings are large character or binary strings (up to 2 gigabytes) that are stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers. These pointers map the pages in which the string fragments are stored.

You can set a text in row option for tables that contain LOB data type columns. You can also specify a text in row option limit, from 24 through 7,000 bytes.

Exercise 3 - Design Data Integrity

In this exercise, you will perform the tasks required to apply constraints while creating a table. To understand constraints, refer to your course material or use the following http://technet.microsoft.com/en-us/library/ms177420.aspx

Table Constraints

PRIMARY KEY, FOREIGN KEY and Default constraint are the three main constraints that need to be considered while creating tables or after creating tables.

PRIMARY KEY

A PRIMARY KEY constraint prevents duplicate values for columns and provides a unique identifier to each column. By default this constraint creates a clustered index on the columns. You can have non-clustered index on a primary key

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.