SQL Concepts

A database, in the simplest and most fundamental sense, can be described as an organized collection of integrated data or records that are structured in a manner allowing for efficient storage, retrieval, and management. The term "database" itself is quite broad and can refer to virtually any collection of data items or records that have been brought together for a specific purpose.

Each individual record within a database serves as a representation of either a physical or conceptual entity. To illustrate this concept, consider a business scenario where the organization needs to store comprehensive information about its customers, its employees, the purchases those customers make, and the products that are bought. In this context, each of these elements—the customer, the employee, the purchase, and the product—constitutes a distinct entity.

When retrieving a customer record, for example, one would be examining all the descriptive information that characterizes that particular customer. The aggregation of all such records collectively forms the database.

Beyond merely storing data, a database also encompasses what is known as metadata. Metadata refers to information that describes the internal structure of the database. It is not sufficient to simply pile every piece of information into a single undefined container; there must be a system of organization.

This organization is achieved through the creation of structures such as columns, tables, and indexes. A table serves as the core structure for storing data, but even within a table, data must be further broken down into individual columns. For those familiar with spreadsheet software, the concept of a table is quite similar—column headers are placed at the top, and individual columns describe the entity.

Returning to the business example, customer information would be stored by separating elements such as first name, last name, address information, phone number, and credit information into their own respective columns. All of these columns together create the table.

An index, meanwhile, is another structure that can be defined to enable very rapid and efficient retrieval of information when only certain values are desired. These structures collectively form the mechanism by which data is stored. The metadata describes the structure, while the data represents the actual records stored within that structure. In essence, a database contains a description of its own structure and integrates both the data and the relationships among all of it.

For instance, customers make purchases, those purchases are facilitated by employees, and the purchases contain the products being sold. All of this information is interrelated, connected through the data, the structures, and the relationships.

In terms of size, databases exhibit considerable variation. They can be as simple as a standard collection of phone numbers—the traditional phonebook serves as a very simple database consisting of names, addresses, and phone numbers. Conversely, databases can be as complex as commercial-grade systems holding millions upon millions of records.

Smaller databases can often be managed with a personal database designed for single users on a single computer. These are typically very simple in structure and small in size. Slightly larger are public workgroup databases, designed for commercial use within an organization that has focused workgroups. These are larger than personal databases and can handle multiple users or groups of users accessing the same data simultaneously—a capability known as concurrency, which is typically not found in personal databases.

At larger scales, enterprise databases are designed for hundreds of groups of users and essentially model the flow of an entire organization. These can contain millions upon millions of records. The appropriate implementation depends on specific needs, and different implementations exist depending on the size and complexity required.

Database management refers to software that allows data to be managed or queried. A query is executed when there is a need to retrieve information. Examples of database management software include Microsoft SQL Server (frequently called Sequel Server, standing for Structured Query Language), which is an enterprise-level solution, and Microsoft Access, which is suited for workgroup or personal use.

IBM DB2, MySQL, and Oracle are other vendors that create and offer database management software. Collectively, these are known as database management systems, or DBMSs. A DBMS is simply the set of programs used to provision databases and their associated applications and processes. It sits between end users and the actual data, serving as the tool used to build the structure and manage the data contained within the database.

In a visualized representation, the database itself is the actual information residing on a hard disk somewhere, while users sit at their computers using some kind of application program on their desktop system. The DBMS resides in between the two to control how the user accesses that information. There are numerous ways by which this can be accomplished, but the key component is that the application program runs on the desktop computer, while the DBMS usually runs on the server with the database.

They do not have to be on the same server, but generally speaking, DBMSs are server-based, and application programs are desktop-based or client-based. The DBMS is responsible for controlling access to the database.

This can be contrasted with a flat file system. Flat files are simply a collection of files with no real structure. They still contain records—data one after another—and there can still be an organized format to that data.

For example, one might still see something like first name, last name, phone number, and similar fields, but there is no real defined structure—nothing that explicitly states the structure of the table. A flat file is really nothing more than a list that contains just data, and each field in the list would typically have a fixed length in terms of the number of characters it can support.

This approach does provide low overhead, which gives faster response times within associated smaller systems, but it does not scale well to very large systems. Migrating a flat file system takes considerably more time than an already existing DBMS does. Databases tend to grow very large very quickly in some cases, and migrating them from flat files is a laborious task, whereas if the data is already in a DBMS, it is basically already in the correct format to be migrated to something larger.

In a flat file system, application programs access the file system, which ultimately gives them access to the data in the files, but there is no real way to control it. Any user could access any record in any file, and there is no mechanism to prevent two users from attempting to access the same information in the same file at the same time. This is where the lack of scalability becomes apparent.

The relevance of databases and SQL (Structured Query Language) lies in the fact that databases allow for the establishment of rules to keep data consistent and fault tolerant, meaning the system can survive failures. Data can be organized for retrieval upon command and broken down into logical parts. SQL—Structured Query Language—is the language used to communicate with databases.

It is known as an American National Standards Institute (ANSI) language. Without SQL, users would not be able to perform controlled tasks to update, retrieve, and manage data. This is where the DBMS truly excels over a flat file type of system.

Different Models of Databases

There are several different models when it comes to the type of database that might be implemented. The three primary models are the hierarchical database, the relational database, and the network database.

The hierarchical database is a data retrieval architecture that is entirely dependent on hierarchy. To retrieve a record that resides on level 3 or level 4, one cannot go straight to that level to locate it; the hierarchy must be followed. A useful visualization is a genealogy or family tree—one must follow the history, going from parent to child to child to child, and so forth, to locate the appropriate record. The record on its own does not mean a whole lot without knowing the history and the relationship to its parent levels.

The relational database is always the primary choice for non-legacy systems and is used for relational models. The network database consists of databases distributed over a network and provides minimal redundancy. This distributed nature means that part of the database is here and part is there, and if any one component is unavailable, the information cannot be accessed.

This is what is meant by not providing redundancy. However, it can be useful in terms of distributing the performance of the database. While it might perform better because it is distributed, it might be less reliable. Both hierarchical and network databases are somewhat out of date these days. The best qualities of both can be implemented within a relational database, which is always the primary choice for non-legacy or newer applications that do not need to support older systems.

In the relational model, tables store the information that is needed. For example, member information and group information can be stored, and it can be inferred that members are placed into groups. The individual members, the information about the groups, and the information about the two combined—that is the relationship: members are placed into groups.

With a relational database, all of that can be implemented. Within the relational model, all data is represented by what are known as tuples and grouped into relations. A database managed in terms of the relational architecture is a relational database, and this is the primary model used around the world for data storage and processing. Relational databases offer flexibility and are generally far easier to maintain because structures that follow the hierarchical or network architecture have that structure hard-coded into the application, making the user dependent on using that application.

Relational databases store data in tables that are largely independent of each other, except for the relationships between them. The application can be anything—just about any application can be used to access a relational database, so there is no tie to any specific application.

Components of a Relational Model

A tuple is a single row of a table that contains the single record for that relation. The record itself may or may not refer to every piece of information. For example, consider an employee table that stores first name, last name, address information, and social security number. At some point, only some of that information might be retrieved—perhaps only the first name, last name, and phone number are needed.

That can be considered a tuple, but it is still within the single record for that particular employee within the employees table. The relations, in essence, are the tables. They are saved in tabular format, with rows across the horizontal containing each individual piece of data. All of that put together for every record creates the table—it is the collection of all records over that relation.

The relation schema describes the relation identity and attributes. The attributes turn into the columns of the table. In the employees table, first name, last name, phone number, and social security number are all examples of attributes. They describe the record itself—the particular employee—and they also identify that particular employee, indicating which one is being dealt with.

A relation key is, in essence, each attribute of the row. It identifies the row in that particular relation. The relation instance is a specific set of tuples in a relational database architecture. The instances are unique tuples. Each individual employee would have some kind of unique identifying attribute. Retrieving a specific set or even just a single individual employee by its unique identifier is, in essence, the relation instance—one specific instance of that particular employee.

A diagram of a relational database shows the table connecting through to other tables, which then connect through to other tables, describing how that information is all related to each other. The information can be organized into tables and then the tables connected.

Why Relational Databases Are Effective

Relational databases are effective because they have compatibilities that cater to future requirements. They are very extensible and can be developed to any degree. They are effective for records that may not even be added until later on—they can always grow and evolve.

They support the use of complex queries to carry out large-scale tasks using Structured Query Language (SQL). Security can also be managed with various implementations at a custom level to cater to individual needs. Data is added once and remains in the system, in many cases possibly forever until it is removed or changed.

When changes are made, that change is applied once and stays there until changed or removed again. The data is easily accessed and managed, all done through the use of the database management system. This is what will be found in almost every environment that makes use of relational databases.

Variations of Query

Basic structural elements of the SQL language can be examined without worrying too much about syntax at this point, focusing instead on the differences between SQL statements and a procedural type of language.

SQL is not so much concerned with executing in a top-down fashion—this instruction, then this instruction, then this instruction, and so on. To illustrate, consider two very simple statements. A comment in code is a description that can be typed in to inform anyone reading the code why the code is there and what it does. Commenting code is a very good habit to develop. Anything that is commented does not process, and a line can be commented by adding two dashes at the start.

In a demonstration environment, the Microsoft SQL Server Management Studio (Administrator) is open, consisting of a menu bar followed by a toolbar. The menu bar contains several options from File through to Help. The toolbar contains several options such as Save and New Query.

The second level of the toolbar contains options such as Execute and Debug. Below the toolbar, the window is split into two panes. The first pane is the Object Explorer pane, which consists of options such as the Connect drop-down, followed by an expanded SQL9349 (SQL Server 13.0.500 - EASYNOMAD\Administrator) node. The second pane is the query editor, which consists of the Intro to SQL Struct...\Administrator (56))* tab. Below the query editor is a section that consists of two tabs—Results and Messages. The Results tab is open.

The query editor has two comments in green color and their associated sets of code. The first comment reads: "--A basic Select Statement to retrieve all records from a table" followed by "Select * from Customers." The second comment reads: "--A filtered Select Statement to retrieve only specific records from a table" followed by "Select CustomerID, CompanyName, Country from Customers Where Country = 'USA' Order By CompanyName;"

The Results tab below the query editor consists of a table that contains several columns such as CustomerID, CompanyName, and Country, populated by relevant details. The first column, which does not contain any heading, contains serial numbers. At the very bottom of this table, the message "Query executed successfully" is displayed, along with an indication that there are 91 rows in the table.

The first comment is highlighted, and attention is drawn to the two dashes at the beginning. Deleting those dashes changes the color of the comment from green to blue and black and produces errors. Adding the dashes back turns it green again, and anything can be typed. It is literally a descriptive note to anyone reading the code so they can understand its purpose.

The first statement, "A basic Select Statement to retrieve all records from a table," is a single line that is not all that much different from any kind of procedural language because it is a single instruction set—returning every record from the Customers table, every row, every column. The asterisk means everything. Mousing over the asterisk shows all of the columns in that particular table. The code has already been executed, and the results show it executed successfully with 91 rows in total, displaying every column of the table.

When the dashes are retyped before the first comment, the font color of the entire comment turns to green again. The code "Select * from Customers" associated with the first comment is highlighted. When the cursor is placed over the asterisk, a list opens displaying a range of column names from CustomerID(PK, nchar, not null) through to Address(nvarchar, null) through to Fax(nvarchar, null). The messages "Query executed successfully" and "91 rows" displayed at the very bottom of the screen are pointed out, along with the different columns in the table in the Results tab.

The second statement filters the results. The comment reads, "A filtered Select Statement to retrieve only specific records from a table." Now, not every record is of interest, and not every column is of interest. The asterisk is gone, and only CustomerID, CompanyName, and Country from that table are of interest.

Within those three columns, not every Country is of interest—a piece of criteria is specified. The Country must match a specific value for the record to be returned. Of the records that are returned, they should be sorted in alphabetical order by CompanyName.

The second comment is highlighted, and after pointing out the asterisk in the code associated with the first comment, the different elements in the code associated with the second comment are pointed out, including "'USA'" and "Order By." What is not being done here is giving specific procedural instructions—not saying execute this line, then this line, then this line, in a top-down fashion.

Instead, a description of what the results should look like is entered. That is where SQL differs from procedural languages. The statement says, "here is what I want to see. However you have to produce that result set, I really don't care. I just want to see those results." Executing this code shows only exactly what was specified—those three columns, and within them, the Country had to match the one specified. Only those values are seen.

There are now only 13 rows, so this has been quite heavily filtered. The code associated with the second comment is selected and the Execute option is clicked. The table in the Results tab now consists of only four columns. The first column, which bears no heading, contains serial numbers. The second, third, and fourth columns are titled CustomerID, CompanyName, and Country respectively. At the very bottom of the table, it is indicated that there are 13 rows. All the rows in the Country column are now populated by the name "USA." The element "USA" in the code associated with the second set of comments is pointed out, along with the table in the Results tab that has the name USA for all the rows in the Country column.

The criteria can be changed at any time. The goal of finding only specific records has been accomplished without having to give any kind of process or procedure to follow to produce that result set. The statement said, "this is what I want it to look like," and it produced that result set.

The idea behind procedural languages is to perform this step, then perform this step, then perform this step, and so on. SQL in its basic structure does not really have that. It is basically told what is wanted to be seen, and it will go and find those results and present them. More complex statements will be covered later, but for now, retrieving records by simply describing what is wanted to be seen instead of giving step-by-step instructions has been demonstrated.

Dissecting the Code

Structured Query Language code is, in essence, the language used when interacting with databases, but it is normally a nonprocedural programming language. This means that SQL is simply told what is wanted by creating a set of queries and executing them, instead of telling the system how to get what is wanted. There is no concern with saying, "do this, do this, do this, do this, do this" in terms of creating a process or a procedure. What is really being done is describing the results that are wanted. "I want to see all records that match a description. As to how those records are retrieved, I don't care. It does not matter to me in terms of the procedures that are used. I am going to simply describe the result set that I want, and then whatever process has to be invoked to retrieve those records is fine."

Object-oriented functionalities can be used, which in essence means that procedural queries can be created. A step-by-step process can be done—execute this, execute this, execute this—which is more a manner of telling it how to do it instead of what is wanted. In other words, the two can be combined, but normally it is nonprocedural. What is wanted to be seen is described by using criteria, and the DBMS decides the best way to get what was requested. It literally determines for itself the best approach to retrieve those records. A query is simply asking a question to the database. If any of the data in the database complies with the conditions of the query, then SQL retrieves the data.

There are effectively two ways to extract information from the database. The first is to create an ad hoc query from a console by typing in the query and reading the results. This is probably something that a database administrator would do. Database administrators have direct access to the database management system.

They would sit down at the server, open up a query window, and literally type in code. They would execute that right then and there and see the results. They might store those results, save them for future use, or just discard them. The other choice is to execute an application that collects information from the database and reports on the information on the screen or in a printed report. This is something that an end user would do.

End users typically do not have access to the database management system itself. They are calling some kind of procedure. For example, a procedure might be called CustOrders (Customer Orders). The @ symbol with the CustomerID means that the user is going to be prompted for a value so that they can see customer orders for any customer they want. It does not have to be a static value that is always supplied.

Database administrators who have direct access to the database management system can type in a sample of code in a query window to extract information from a database. The code might create a procedure called CustOrders with a parameter @CustomerID of type nchar(5).

Within the procedure, comments might be included, followed by a SELECT statement listing OrderID, OrderDate, RequiredDate, and ShippedDate FROM the Orders table WHERE CustomerID = @CustomerID ORDER BY OrderID. The @CustomerID is what is known as a variable. It holds the value that the end user supplies and then uses that as criteria. This comes back to the description of the data. Only the OrderID, OrderDate, RequiredDate, and ShippedDate are wanted.

There could be dozens of other columns in the table, but those are not of concern. Only those ones are wanted FROM the Orders table WHERE the CustomerID is equal to whatever value the end user supplied. This is where the criteria is seen. No kind of process is being told to follow to locate that data. The statement says, "find me every record WHERE the CustomerID is equal to whatever the user supplied. However you do it, I don't care. Just find the record."

So the result set is being described. If it is a simple value such as customer number 1, the database will look through the records and try to find those ones that reference customer number 1. If that value matches, the record with the ID, the date, the RequiredDate, and the ShippedDate will be returned. Finally, they will be ordered, sequenced by the OrderID. Again, the results are being described—orders for customer number 1 in sequence by the OrderID with those other values listed. It is a description of the end result.

How it is constructed does not matter. Instead of saying WHERE CustomerID is equal to a value supplied by the end user, the number 1 could have just been typed in. That would be the ad hoc query. Results could be printed off if wanted, but the window could just be closed. End users usually do not have access to the server or the database management system. They are probably using a custom-built front-end interface where they can just hit a pick list, choose the customer, hit go or submit or execute, and see the results come back. Those are the two choices.

Basics of SQL Commands

There are three main roles of SQL commands: querying a database to retrieve data, creating the database and defining its structure, and controlling data security, making sure that only the appropriate people have access to certain data. There are subcategories or sublanguages called DML, or Data Manipulation Language, which is a subset of SQL that works with queries and manipulating the data.

Queries are used to simply pull out some of the information from the database, providing a snapshot of some of the contents. There is no concern with every record or even every column in the table—only that which matters is wanted to be seen. For example, one might only be concerned with seeing the ID, FirstName, LastName, when they were hired, and the City they work in or possibly live in.

It is simply that information that is of concern right now. There could be millions of other records and hundreds of other columns, but only that subset of data is of concern. With SQL, what is wanted to be seen is simply described. In a simple example, it might have been something like only show employees by their ID, FirstName, LastName, and HireDate in the cities of Kirkland, London, Redmond, Seattle, or Tacoma. Maybe it was where the Employee ID is between 1 and 9. It does not really matter. All that is being done is filtering the result set so only the appropriate information is seen.

SQL Syntax

The actual syntax of the SELECT statement includes several options. The SELECT statement is how records are retrieved from a table or tables. For now, the focus is on single table queries, but records can be retrieved from multiple tables at the same time. Those implement what are known as join statements, which will be covered later.

The SELECT statement is followed by a list of columns that are wanted to be seen. Every column can be shown by implementing an asterisk. The FROM statement specifies the name of the table or tables. A WHERE clause allows criteria to be specified, filtering out the records that are of interest as opposed to seeing every record. If a WHERE clause is not entered, every record will be seen.

In a demonstration environment, the Microsoft SQL Server Management Studio (Administrator) is open, consisting of a menu bar followed by a toolbar. The menu bar contains several options from File through to Help. The toolbar contains several options such as Save and New Query.

The second level of the toolbar contains options such as Execute and Debug. Below the toolbar, the window is split into two panes. The first pane at the left-hand side is the Object Explorer, which consists of options such as the Connect drop-down, followed by an expanded SQL9349 (SQL Server 13.0.500 - EASYNOMAD\Administrator) node. The second pane at the right-hand side is the query editor, and it contains the Intro to SQL Struct...\Administrator (55))* tab. The section below the query editor contains two tabs—Results and Messages.

The Results tab is open. The query editor includes a series of SQL queries, comments, and the codes associated with their codes. A block comment includes the following structure: SELECT List of columns, FROM Name of Table(s), WHERE Enter Criteria, ORDER BY Specify sorted column(s) and sort order (ASC/DESC), GROUP BY Enter column(s) to categorize results, HAVING Enter Criteria for the grouped column(s). Below this is a comment reading "--Basic Select Statement syntax" followed by "Select * From Customers." The Results tab in the section that follows the query editor contains several columns such as CustomerID, CompanyName, and Country, each populated by relevant details of several records. "SELECT" and "List of columns" are highlighted in the SQL query statement. Next, the code "SELECT * From Customers" associated with the first comment is highlighted. The "FROM" statement and the WHERE clause are pointed out.

ORDER BY allows sorted columns to be specified. There can be more than one, and the sort order can be specified—ascending or descending. Ascending is very common with alphabetical sorting, but descending is very common with numerical sorting when the highest number is wanted at the top. GROUP BY and HAVING are ones that will be covered later. GROUP BY allows results to be categorized.

For example, when looking for sales, one might want to see those sales broken down by a particular product or by a particular salesperson or by a particular region or a particular time frame. It allows them to be collected together so that if all sales of widgets in a region in a time frame are being looked for, they can be grouped together. This is typically known as aggregating. Then one can say, for all widgets in that region for that time frame, what were the total sales? Sum them up. GROUP BYs are almost always seen in conjunction with some kind of calculation so that averages and sums can be seen. "ORDER BY" is pointed out in the SQL query—ORDER BY Specify sorted column(s) and sort order (ASC/DESC).

The different words and phrases in the query are pointed out, including column(s) and (ASC/DESC). The following two SQL queries are highlighted: GROUP BY Enter column(s) to categorize results and HAVING Enter Criteria for the grouped column(s). HAVING allows criteria to be entered for the grouped columns. Again, if looking at widget sales in a region for a time frame, one might say, "let's only see the top ten customers." So one can say, HAVING a total greater than or equal to a certain value. This filters out the ones that are not of interest.

A few examples of SELECT statements are shown. The most basic SELECT statement selects every column from the Customers table—every record in the table, every column, every row. Nothing has been filtered. This really would not be any different than just opening up the table itself.

The query editor is scrolled down to reveal the last comment and its associated code: "--A filtered Select Statement to retrieve only specific records from a table" followed by "Select CustomerID, CompanyName, Country from Customers Where Country = 'Germany' --or Country = 'USA' Order By CompanyName; Go." The results can be filtered to only retrieve specific records. The SELECT statement is followed by the list of columns, which can be as many as wanted—up to and including every column. Each column name has to be separated by a comma.

One nice feature is that after entering a comma and starting to type the name of the next field, such as the contact name, an autocomplete feature known as IntelliSense appears. As long as enough characters are identified, it will pick up on the existence of that column. The Tab key can be hit if that is the one wanted, or the one wanted can be double-clicked. This automatically completes the name and is very useful because it reduces typos. Getting in the habit of using autocomplete or IntelliSense is definitely recommended.

Once the list of columns is completed, the "from table name" is specified. Multiple tables can be used, but that will be covered later. The last comment in the query editor is highlighted: "--A filtered Select Statement to retrieve only specific records from a table" followed by "Select CustomerID, CompanyName, Country from Customers Where Country = 'Germany' --or Country = 'USA' Order By CompanyName; Go." Then the Where statement is entered, which is the criteria. In this case, filtering by the Country means only records where Germany is the Country will be seen. There is also an "or Country = 'USA'" which will be returned to in a moment. Then ordering by CompanyName, which, if ascending or descending is not specified, will default to ascending.

Executing this code narrows down the results. They are sorted alphabetically by the CompanyName in ascending order. To change that to descending, a space can be hit and "DESC" typed in. It is not case sensitive. Keywords, which are in blue, can be separated from the names of fields. Re-executing reverses the sort order, and the last record, Toms, appears at the top. Ascending is the default, so it does not have to be specified if that is what is wanted.

The different elements of the second and third lines of code associated with the last comment are pointed out. The entire code is selected and the Execute option is clicked. The table in the Results tab now consists of five columns. The first column, which contains serial numbers, bears no heading.

The other columns bear the headings CustomerID, CompanyName, Country, and ContactName respectively. The rows in the table are populated by the relevant details of records, and all the rows for the Country column are populated by the name Germany. The CompanyName column is sorted alphabetically in ascending order. The "or" allows more than just one Country to be specified.

Removing the comments shows that it will show any record where the Country is either Germany or USA. One does not have to limit oneself to a single piece of criteria. Both of them come back, still sorted in descending order by the CompanyName. Taking off the DESC will re-sort them in ascending order. The "or" allows as many different countries as wanted to be specified. An "and" statement can also be specified, but an "and" will have to be placed on a different column. For example, one might look for a particular Country, then only want a particular city in that Country. Going back to "Select *" shows that there are different cities listed as well.

Within the United States, different cities can be found. Eugene in the USA is one example. San Francisco is another. An "and" condition can be specified by putting in, where Country is USA and City is Eugene. The "or" in the second line of the code is pointed out—Where Country = 'Germany' --or Country = 'USA'. Both pieces of criteria have to be met. Executing this should only show that one entry where the City was Eugene. Note that the City did not even have to be included in the list of columns to be able to filter that result set. "And" or "ors" can be used to modify which records come back.

Removing the City shows every City coming back. "Or" statements allow multiple pieces of criteria to be added wherein any one of them can be satisfied; "and" statements require both pieces of criteria to be satisfied. It must match the country, and it must match the city in that country. These are some of the basic syntactical elements of SQL statements.

Understanding Data Types

The data types assigned to columns allow specification of what kind of information is going to be stored. There are many different data types, and extra research is encouraged because not all of them can be covered. Some categories include numerics, which refer to entering just regular numbers; Booleans, which refer to one of two possible values for the most part—true or false, yes or no, on or off; intervals and chronological, which usually deal with some kind of time-based information; and strings, which usually deal with text-based information.

It depends on what is wanted to be stored. Some common examples of actual data types seen in SQL Server include numerical data types such as bigint, which stands for big integer; int for just integer; smallint; and tinyint.

The size being referenced is literally how large a number is going to be stored. This can be a concern for very large databases. If the database is fairly small, it might not be that big of an issue. But once databases store millions upon millions of records, assigning a numeric type that is too large for what is really needed wastes a lot of space because it is the amount of characters that are stored in each field.

The appropriate size should be chosen. For example, if selling something and entering a quantity and nobody ever purchases more than four or five of any one particular quantity, then a big integer would not be wanted because that would waste a tremendous amount of space.

The precision is shown in the right-hand column. The first number is the total number of bytes required in that field to store that particular size of number. The lowest number, tinyint, is only a single byte. That is 8 bits or 2^8. For those familiar with IP addresses or good at math, 2^8 gives 256 possible combinations of 1s and 0s in binary.

That translates to a numerical range of 0 through 255 because computers start counting at 0. It is not 1 to 256; it is 0 to 255. That is 256 possible values. The smallint is 2 bytes. That is 16 bits. For those familiar with exponents, that is not 32,000, it is 64,000—more appropriately, possibly 65. But it includes the negative range as well. It goes from negative 32,000 to positive 32,000, and that is a total of 65,000 possible values, which is 2^16.

The same goes for integer. It is 2^32, which is 4 billion. But only 2 billion is seen here because it includes the negative side. Negative 2 billion up to positive 2 billion is 4 billion possible values. Finally, bigint goes from negative 9 quintillion to positive 9 quintillion. That is obviously a very large number. What is also noted about the numerical data types, at least with the integer ones, is that there are no decimal places allowed. These are exact numerical values. If decimal places are required, then ones such as real, float, and decimal are used.

These have approximate numerical values and allow a decimal place to be put in. How many characters are supported? The 7 is 7 bytes, and the 16 is 16 bytes, and that basically means a decimal place with up to that number in terms of decimal places can be had. One can be very precise or somewhat precise. The ones that indicate the letter p means that how many are wanted can actually be specified. Control can be had to a certain degree. Those are common when very specific numbers are needed.

Boolean and string data types: The Boolean is either a true or a false. It is basically just one or the other. It does not have to be just true or false. A yes/no, for example, could be specified as a Boolean data type. Binary is data of binary string value with a fixed length. In other words, how many characters are wanted can be specified. Varchar is character based.

These are letters basically. Variable means that it can be a variable length. How many characters are going to be entered, such as a person's last name or first name, is not really known. But a maximum-value variable length can still be specified up to a total of 20, 30, 40, whatever is wanted. Character is when exactly how many characters will be entered is known. For example, an abbreviation of state or province—the post office abbreviations of only two characters might always be used.

A lot of space can be saved with that. Varbinary is the same as binary, but it is variable in length. There are some options there. Chronological and interval: Interval is a range from a number of integer fields and can represent a period of time such as week by week, month by month. Date stores the day, year, and month data values. Time stores seconds, minutes, and hours. There is a date, time, which includes both.

A timestamp also includes both, but it usually is something that is used to simply enter when a particular record was created, such as hire date. A timestamp might actually just be used. The instant the record is created, it stamps it to say this was the hire date. That is up to the user. It would not have to be done that way because records might be entered for people who have already been