Lesson 1: Creating Tables in Visual Studio .NET

Creation PDF417 in Visual Studio .NET Lesson 1: Creating Tables

The basic analogy for a database table is that it is like a single worksheet within a spreadsheet. As you work with a worksheet, you enter information in rows and columns. Well-designed spreadsheets generally have column headers that give you an idea of the kind of data you find in a column. The problem with working with data in a worksheet, however, is that it doesn t enforce any structure. You can place any type of data in any column without limitation. Database tables also store rows of data in columns. And each of these columns has a name associated with it to provide an easy way to reference a particular piece of data. But what sets a table and database apart from a Microsoft Office Excel worksheet or spreadsheet, for example, is the strict enforcement of the data that you can enter in a column. SQL Server enforces this data structure by using data types as well as properties that you can add to further define a column. In this lesson, you will learn how to make the best choices when defining data types and properties for columns, how to create a table, and then how to assign appropriate permissions to allow access to a table.

After this lesson, you will be able to:

Specify column details including data type. Specify the filegroup. Implement a table. Assign permissions to a role for tables.

Data types limit the type of data that you can store in a column and, in some cases, even limit the range of possible values in the column. The data type that you choose for a column is the most critical decision that you make within your database. If you choose a data type that is too restrictive, applications cannot store the data they are supposed to process, leading to a large design effort. If you choose too broad a data type, however, you wind up consuming more space than necessary on disk and in memory, which can create a resource and performance issue. When selecting a data type for a column, you should choose the data type that allows all the data values that you expect to be stored while doing so in the least amount of

3

space possible. SQL Server data types fall into seven general categories, which Table 3-1 describes.

Table 3-1

General Purpose Stores precise numbers either with or without decimals Stores numeric values with or without decimals Stores numeric values with decimal places; used specifically for currency values with up to four decimal places Stores date and time information and enables special chronological enforcement, such as rejecting a value of February 30 Stores character-based values of varying lengths Stores data in a strict binary (0 and 1) representation Complex data types that require specialized handling, such as XML documents or globally unique identifiers (GUIDs)

Let s look at each of these data type categories to see how you can use the different data types to provide the basic definition of each column in a table. You use these data types when defining permanent tables, temporary tables, table variables, and variables. There are few restrictions to the data types that you can use in stored procedures, triggers, and functions.

For more detailed information about each data type, including explicit storage details and restrictions, see the SQL Server 2005 Books Online topics Data Types (Transact-SQL) and Data Type Conversion (Database Engine). SQL Server 2005 Books Online is installed as part of SQL Server 2005. Updates for SQL Server 2005 Books Online are available for download at www.microsoft.com/ technet/prodtechnol/sql/2005/downloads/books.mspx.

You use exact numeric data types to store numbers that have zero or more decimal places. You can manipulate the numbers that you store in these data types by using any mathematical operation without requiring any special handling. The storage is also precisely defined, so any data stored in these data types returns and calculates to the same value on either an Intel or an AMD processor architecture. Table 3-2 lists the exact numeric data types that SQL Server supports.

Table 3-2