Database Fundamentals #10: Modifying Tables

Database Fundamentals
Invariably, either while building a new database, or while maintaining old ones, you will find that the business needs change or that you’ve made a mistake. Either way, you will need to modify the tables that you’ve created. Change is inevitable and luckily SQL Server takes that into account, providing you with mechanisms to modify structures after you’ve created them. Important Warning One of the fundamental concepts of databases is that the data is stored. You can’t simply throw data away when you need to make a change to a table. There are two levels of changes you can make to a table. The first, is a change that won’t affect data in the table. These are simple changes and we’ll cover them in this blog post. The second level…
Read More

Database Fundamentals #9: Schemas As Containers

Database Fundamentals
Schemas are a very useful tool for managing the objects in your database. From security through process, through placement, schemas provide you with another tool to control how your database behaves. Schemas The tables created so far in this blog series have all been attached to a schema, dbo. Depending on how you login to the server and the security settings for your user, this is usually the default schema. A schema is simply a container in which you place objects. Once placed there, a schema is a method for managing the objects it contains. Schemas give you a simple way to control placement of the objects on filegroups. Schemas are a very easy way to manage security. The use of schemas becomes extremely important as your database becomes more…
Read More

Database Fundamentals #8: All About Data Types

Database Fundamentals
SQL Server provides all different kinds of data types in order to give you more flexibility and control for how you store your data. You define these data types as you define the columns in your tables. If you wanted to store information about the date and time that a purchase was made online you’re going to store the values in a column, or columns, that define dates and times in order to ensure accuracy in your data. Choosing a Data Type You could make the data type into one that stores just about anything you give it, such as one of the character types like char or varchar. Do this and you’re going to run into issues when you start running queries against that data. For example, your business…
Read More

Database Fundamentals #7: Create a Table Using T-SQL

Database Fundamentals, T-SQL
The syntax for creating a table logically follows many of the same steps that you did when using the GUI, but it will all be done with the statements. This script will exactly replicate everything that you did with the GUI: CREATE TABLE dbo.Person ( PersonID int IDENTITY(1,1) NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, DateOfBirth date NULL ) ON [PRIMARY]; Breaking the script into separate lines, it’s easy to see how the TSQL commands perform the actions defined in the GUI (it also makes it easier to read). The CREATE TABLE statement in this context is self-explanatory.  After that you’re defining the schema and the table name. Within the parenthesis you define each of the columns. First is the name of the column followed by the…
Read More

Database Fundamentals #6: Create a Table with the SSMS GUI

Database Fundamentals
The whole idea behind databases is to store information, data, in order to look at it later. The place where that data is going to be kept is referred to as a table. A table consists of columns and each column can be defined to store a variety of specific kinds of data. You can make small tables or large ones. The columns on the tables can be made so that they have to have data in them or they can be empty. The choices are yours to make, but you need to know how to set tables up appropriately . In addition to tables, we're going to start learning about columns. Columns can be very generic in nature, supporting all sorts of different kinds of data, or they can be…
Read More

Database Fundamentals #5: Database Properties

Database Fundamentals
Don’t let the ease of creating databases lull you into a false sense of security. They actually can be very complicated. You can modify and individualize their behavior within your server so that different databases behave in radically different ways. The best way to see all the different manipulations you can make, open the New Database window by right clicking on the Databases folder within the Object Explorer window, assuming you’re already connected to the server. Don’t bother typing anything into the first page. Click on the Options tab on the left side of the window. You’ll see a screen that should look very similar to this: Don’t worry. We won’t be talking about all the possible settings on this page. Instead we’ll focus on a few that are extremely…
Read More

Database Fundamentals #4: Create a Database

Database Fundamentals, T-SQL
SQL Server provides multiple ways to perform most functions. In order to maximize your understanding of how SQL Server works and have as many different mechanisms as possible for getting work done, you’ll use the GUI and TSQL to learn how to create and drop databases. You can then use whichever mechanism works best for you at the moment. Using  each method, we’ll first create a database using the least amount of work possible, just so you can see how easy it is create a database. We’ll go over how to remove databases from the system, getting rid of the database you just created. From there we’ll drill down to create another database, exploring some of the different mechanisms you can use to change how databases get created. Then we’ll…
Read More

Database Fundamentals #3: What’s In a Database?

Database Fundamentals
It’s worth noting that a lot of people will never need to create their own database. You may never create your own tables or other data structures either. You may only ever run backups and restores and manipulate the security on the system and let application installs create databases for you. That’s completely understandable and perfectly in line with the needs of many businesses and many accidental DBAs. However, it's a good idea to understand what this stuff is and how it works as part of understanding SQL Server. A Database Is Actually Files You need to store information that you want to be able to retrieve later. It’s necessary that you organize that information. If you were working with a word processing program, you store different documents in different…
Read More

Database Fundamentals #2: SQL Server Management Studio

Database Fundamentals
The best way to learn any software is to start using it. There are a bunch of software tools in the SQL Server toolbox, but the biggest and most important is SQL Server Management Studio (SSMS). SSMS is where you'll spend most of your time when you start to work with SQL Server. It provides a very large series of graphical user interfaces for creating databases, setting up security, reading data out of the database, and all sorts of other things within your SQL Server instances, the databases stored there, and all the stuff inside those databases. It also supplies you with an interface to the basic scripting language of SQL Server, through which you can do almost anything to the server. The scripting language is called Transact Structured Query…
Read More

Database Fundamentals #1: Install SQL Server

Database Fundamentals
To get started with SQL Server, you need to install it. However, I'm not going to teach you how to do a SQL Server install. Instead, I want to point you towards a couple of sources of information, and, give you some alternatives to installing SQL Server. Install SQL Server As much as I enjoy sharing information with you through this blog, and despite the fact that I read a lot of other technology blogs and other resources online, the single best source of information on SQL Server remains Microsoft and their SQL Server Documentation. This information is up to date and very well maintained. Yes, I'm sure you can find somethings to complain about in there, but overall, it's the starting point for all SQL Server learning and understanding.…
Read More