In the previous post I talked about what Microsoft SQL Server programming language (T-SQL) is about. I also explained what a table is and what basic information is requires. In this post we’re going to continue the discussion on tables a bit further. Now we try to understand some more features that we need to provide the table in order for it to be created. Keep in mind that when I say table – the content of this particular post is actually more relevant to the data type used by the column within the table rather than the table itself. If you remember from our previous post, we were trying to create a table that stored the ID, name, date of birth and the phone number. Each of these columns stored very specific types of data and therefore we used something called Data Type.
As its name suggests data type literally means the type of data being stored. For example, when I pass the data type is Int what I mean is integer, if I say float it means a floating number (a floating number has decimal places) or when I say date it means the data is of the format date (often saved as yyyy-mm-dd hh:mm:ss.mmm). Pretty much any type of data that you want can be saved inside a string data type. However, if you remember from the previous post data types can be strongly typed in which case the data type itself provides some protection against invalid formats, numbers or information. In addition to providing a check against bad data the data type also helps the operating system as well as the software understand and save the data in a more efficient way. This can result in the data being fetched a lot faster and even reduce the size of the table. Using the right data type is critical to performance. Now that we know this information let’s talk about the script that we would need to write in order to generate the table that would mimic the structure from the first post.
Create Table MyCustomer ( Id int, Name varchar (100), DOB datetime, PhNum varchar(100) )
As you can see from the above script, we’re trying to make a table called MyCustomer. This helps SQL server understand what the name of the table should be once it’s created. For obvious reasons no two tables can have the same name (imagine twins living in the same house with the same name). A table in itself is useless if it doesn’t have columns. This is why the next step involves explicitly defining the columns we want within this table.
The first column will be called ID and will store an integer value. An integer value is basically a number that doesn’t have a decimal place. Think of it as being something similar to your roll number in class or your EmployeeID in office. One of the big reasons why we create such columns is to provide a way to uniquely identify every row within the table therefore the first row would have an ID 1 the second 2 , the third and 3 and so on. This is especially useful for scenarios where two employees might share the same name and maybe even the same date of birth. By uniquely being able to reference every row using its ID we can make sure that we are always fetching exactly the row that we intend.
The next column that we want to create will be called Name. As you can see this column has a very unique data type called varchar. It stands for Variable Number of Characters. As the name indicates it can store data with any number of characters between 0 to 100. Remember that I can even put 1000 within brackets to indicate that can store up to 1000 characters. You will need to be aware of what the maximum possible number of characters a name might have and ensure that you use an appropriate value within the brackets. Too little and you miss out on data too much and you waste space.
The third column is date of birth and as you can imagine we’ve got a date time data type. An interesting point to note over here is; depending on what you’re trying to achieve a date of birth might only require the date or the date and time. For example, if you are a hospital you might want to track the time but if you’re a bank you don’t really need to.
And the last column that we want to store is phone number as you can see even though the column says number, we know for a fact the phone numbers come in a variety of formats some of them include extensions others include hyphens et cetera and in order to understand that information in its natural state we prefer to store the data in a varchar datatype.
With this understanding of a datatype and the above statement we are ready to create our table. To recap in this post we explored what datatypes are , why they are important and how to write the commands that will create a table for us.