Practical SQL Guide : Data Type and Variables

 What is Data type?

The type of the data that you are going to store or use in the database refers to as datatype. In SQL as per the use there are many datatypes out of which I will discussing a few mostly used ones.

INT & BIGINT : One of the most common types used in SQL and may be in any programming language. Just kidding. There are others as well. INT refers to Integer which is the value, from -2,147,483,648 to 2,147,483,647 and that's a big range for number isn't? well you haven't seen BIGINT yet. Its more like a big brother to INT and ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Dang its like some number from out of the universe 😂 but SQL has the capacity to store such big numbers. What if you don't need such big ranges? and you know the range of values is going to be less than INT or BIGINT? In that case SQL provides two more types, SMALLINT which ranges from -32,768 to 32,767 and TINYINT which ranges from 0 to 255. Now depending on your situation and need, I will suggest you to stick with the data types as mentioned. For example if your job gets done with TINYINT, don't use SMALLINT.

DECIMAL : Now what if your data types contains decimal values? For that type we have a direct datatype that handles it and it is DECIMAL. However unlike there's no fixed range of a decimal value in SQL.  But it depends upon it declaration. For example DECIMAL(5, 2) will hold 5 digits of number including 2 digits of decimal, that it it will hold from -999.99 to 999.99. Similarly if I declare it as DECIMAL(5, 3) it will hold from -99.999 to 99.999. So it's completely on your use case scenario on what to declare. Again here, keep in mind that just use what range you are sure of. Avoid using bigger ranges whenever possible.

BIT : Now what if you don't need any integers and your data is just of Boolean type(true or false). In that case you just need a BIT which represents 0 and 1. That's simple. Hehe.

UNIQUEIDENTIFIER : Sometimes your use case needs some unique data to make it different from other data. This is where you will need UNIQUEIDENTIFIER. If you don't know what that is then let me give an example. Have you ever seen long strings of combination of English alphabets and numbers separated by a hyphen, something like 6B29FC40-CA47-1067-B31D-00DD010662DA this is what a unique-identifier is. Sometimes it is called as GUID type as GUID stands for Global Unique Identifier. GUIDs are primarily used for generating unique keys, which are used for primary keys. Don't hassle much about primary keys now. Later on I will BE going through them in details. Now talking about ranges of GUIDs, there is no such range available as they are uniquely generated. We will also speak about GUIDs later.

VARCHAR & NVARCHAR : Strings are also stored in a database. And they are called as VARCHAR and NVARCHAR data types, They are declared as VARCHAR(N) where N refers to the size of the string that is going to be stored in the database. By size I mean, number of characters in the string. NVARCHAR is also declared in the same way, NVARCHAR(N) where N is the size of string. Now you might ask that what is the difference between the two? Well VARCHAR cannot store Unicode characters but NVARCHAR can store them. Unicode can be understood as something that is not in your keyboard. A-Z, a-z, 1-9, special characters like @, # etc., are ASCII characters, and are standard and can be stored in NVARCHAR but not in VARCHAR.

DATETIME AND DATETIMEOFFSET : These are just two of storing dates and time(combined). Now datetimeoffset defines the difference between the current DateTimeOffset instance's date and time and Coordinated Universal Time (UTC).

Variables

Now you know enough about data types used in SQL. There are many others data types as well beyond these mentioned, but these are mostly used. Now I will speak about variables, how to declare and use them. 

So what are variable anyway? Variables are "storage" where you can store some data, and their value can change overtime. In SQL, you have to give type name after the variable name. Okay wait, before you write you first variable lets understand the types of variable you're going to use. There are two types of variable we will use. One is scalar variable and another is CTE(Common table Expression). We will focus on CTE later in the series after we have learnt about tables and how to create them. But now we will focus on scalar variables. 

Scalar variables are just single-storage variables, meaning they can store only one value of their data type. They are declared using DECLARE command and a  '@' before the variable name. For example if you want to create a variable of the name Variable and want it to be of INT type the you will write it as follows : 

DECLARE @Variable INT

Now you can use it anywhere in your code. Remember @ is inseparable to the variable name. So whenever you'll use that variable you will use it as @Variable. You can give it a value while declaring it, and for that all you have to do is that you have to put a equal sign (=) next to declaration and value you want to initialize it to.

DECLARE @Variable INT = 123456

You can see the result of that variable simply using SELECT statement. Writing it as : 

SELECT @Variable

Let's do it in the playground:

Otherwise if you don't want to directly initialize the variable, you can use the SET command to set the value of the variable as well however you won't be able to directly see it but can later use it. 

There are other ways of using a variable and they are really fun. And when CTEs join the game, its more amazing, and I am excited to share those with you, but we won't be going fast rather slow and steady. So that's all for this blog. Will learn more in the next blogs.

SEE YA!!!