When it comes to any language, whether it is a coding language like C or a query language like SQL, the data type is one of its most integral parts. In fact, SQL has more than 30 data types that are capable of storing all kinds of real-world data. To start working with data, you need to have a clear understanding of
SQL data types. SQL data types define the nature of data that can be stored in database objects, like tables. Every table has columns, and each column has a name and data type associated with it. You can define both of these things during the creation of the table. The data type
lets the database know what to expect from each column and also determines the kind of interactions that can occur. For example, if you want a column to contain only integers, you can use the “int” data type for it. SQL contains a variety of data types, so let’s have a closer look at the most important categories of SQL data types that are available to us. There are six primary categories of
pre-defined SQL data types that you can choose from when creating a table. These categories are as follows: These contain numbers and are divided into two categories: exact and approximate. These can contain numbers,
alphabets, and symbols, and have types like “char” and “varchar.” These are similar to character string data types, but take up twice as much storage space. The characters in these data types are in the hexadecimal format. These are used to store date and time information in data types, such as “timestamp” and “year.” Remaining SQL data
types that serve a variety of functions are grouped together in this category. For example, “CLOB” is used for large character objects and “xml” for XML data. Let’s get a more in-depth insight into each of these categories. The numeric data types are divided into two subcategories: The literal
representation of the data value is stored in exact data types. The exact data types, the range of numbers that can be stored in each of them, and the storage space required are specified below. Data Type From To Storage Space Example bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 8 bytes Population bigint int -2,147,483,648 2,147,483,647 4 bytes ID int smallint -32,768 32,767 2 bytes ID smallint tinyint 0 255 1 byte Age tinyint bit 0 1 1 bit Present bit decimal -10^38 +1 10^38 -1 5 - 17 bytes Balance decimal(8,2) numeric -10^38 +1 10^38 -1 5 - 17 bytes Balance numeric(8,2) For example, if we want to store an attribute like Student ID, “int” or “smallint” would be the most suitable data type. To store a person’s age, “tinyint” should be used. For a large number, such as the world population, you should use “bigint” Precision, or p, is the total count of numbers in the data. Scale, or s, is the count of digits after the decimal point in positive numbers, and
before the decimal point in negative numbers. If you use numeric, you have to define the exact precision(p) and scale(s), but in decimal, precision(p) is flexible; only scale(s) is exact. For example, if you define an attribute balance with a numerical value of (7,2), you can store numbers, such as 34215.23, in this column. The details of these data types are included below. Data Type Storage Value of “n” Precision Minimum Range Maximum Range Example float(n) 4 bytes 1-24 7 digits -1.79E+308 to -2.23E-308 2.23E-308 to 1.79E+308 Pi float(6) float(n) 8 bytes 25-53 15 digits -1.79E+308 to -2.23E-308 2.23E-308 to 1.79E+308 Pi float(34) real 4 bytes N/A 7 digits -3.40E+38 to -1.18E-38 1.18E-38 to 3.40E+38 Money real The “float” and “real” data type belong to this category. These data types also consist of “precision,” which is the exponent of 10. It also specifies how many precise digits are significant in the number and which are signed numeric values. For example, a number like 12789.23 will be expressed as 1.278923*10^4, in this notation. Here, the number four is the precision, and 1.278923 is significant. The
difference between float(n) and “real” is that the variable “n” (which was created when the table was) defines the precision of the float(n), and the precision of “real” is fixed. All these data types can contain alphabets, numbers, and symbols. The details of these data types are shown below. Data Type Type Maximum Size Example char Fixed length 8000 characters Roll_No char(3) varchar Variable length 8000 characters Name varchar(255) varchar(max) Variable length 2^31-1 bytes or 2,147,483,645 characters LargeData varchar(max) text Variable length 2^31-1 bytes or 2,147,483,645 characters LargeData text For example, if you define an attribute, Roll_Number char(3), all entries in this column must contain three characters. For example, the attribute Name varchar(255) can contain both names: Tom and Hannah. The difference between these two data types is that “varchar(max)” has a variable maximum storage size, whereas the maximum storage size for “text” is fixed. The details on different data types in this category are provided below: Data Type Type Maximum Size nchar Fixed length 4000 characters nvarchar Variable length 4000 characters nvarchar(max) Variable length 2^31-1 bytes or 1,073,741,822 characters ntext Variable length 2^31-1 bytes or 1,073,741,822 characters These data types are similar to character string data types, but each Unicode character takes up two bytes of storage space, whereas each non-Unicode character takes up one byte of storage space. Unicode characters require twice the storage space, as each character is converted into a 16-bit number that can be easily stored in any computer system. This is crucial, as it removes the real-world language barrier by encoding every character using a uniform standard. These data types are used to store characters of various languages, such as Arabic and German. The name, type, storage space, and an example of this category's data types are as follows: Data Type Type Maximum Storage Example binary Fixed length 8000 bytes IPv4 binary(2) varbinary Variable length 8000 bytes IP_Address varbinary(4) varbinary(max) Variable length 2GB PDF varbinary(max) image Variable length 2GB Photo image These data types are used to store raw data, like IP addresses. The characters in the data are represented in the hexadecimal format. Each character can assume any value from zero to nine, A, B, C, D, E, and F. Any attribute with binary as the data type is defined as “attribute binary(n),” where n is the data's length. For example, to store the attribute IP address, you can use “varbinary(n)” where n defines the maximum length of the data. This data type can be used to store various types of data, such as images, PDF documents, and MS word files. Data Type Description Range Example date Used to store the date in the YYYY-MM-DDformat. The value of the year should range from zero to 9999; for month it should be from one to 12, and for day, it should be from one to 31. The date 12th June 2020 will be stored as ”date 2020-06-12”. time Used to store time in the HH:MI:SS format. Hour should be between zero and 23, minute value between 00 and 59, and the second value between 00 and 61.999999. 5:30 p.m. will be represented as ”time 17:30:00”. datetime Used to store both date and time information together in the YYYY-MM-DD HH:MI:SS format. The date and time portion follows the “date” and “time” data type rules, respectively. 9:15 p.m. 23rd January 2019 will be stored as “datetime 2019-01-23 21:15:00”. timestamp Used to store both date and time information together in the YYYY-MM-DD HH:MI:SS format. '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. 9:15 p.m. 23rd January, 2019 will be stored as “datetime 2019-01-23 21:15:00”. year Used to store the year in a two-digit or four-digit format. For the two-digit format, the range is from 1901 to 2155, and for the four-digit format, it is from 1970 to 2069. The year 2020 will be represented as “year 2020” in the four-digit format and as “year 20” in the two-digit format. There are other essential SQL data types other than the five primary categories discussed above. Let’s get a glimpse into these as well. Data Type Description xml Used to store XML files that contain unstructured or heterogeneous data. json The data in this is stored as a string, but it can be of two types: data from an external source like a website, or semi-structured data that has been grouped. clob This stores character large objects that cannot be stored in char and varchar. blob This stores binary large objects. For example, in the attribute “Employee_data xml,” we can add multiple types of information, such as name and city. For example, it is beneficial from a business perspective if you want to store
data from different sectors, like finance and trade, together in “Business json”. A clob object is defined as “object clob(n[K|M|G]),” where n is an unsigned integer that represents the length. K, M, and G represent kilobyte, megabyte, and gigabyte,and if any one of these is present, then the length of the data is as follows: This is used to store data, like images, audio files, and videos. It is used to store non-traditional data, like voice recordings and mixed media. Keep in mind that database systems don’t support all data types, so you need to verify before using any given data type. For example, MySQL doesn’t support any Unicode data type, and Oracle doesn’t support “datetime.” Some databases also have their own additional data types that you can use. For example, Microsoft SQL Server has “money” and “smallmoney,” which can be used in place of “float” and “real,” but these are database specific and not available in any other database
system. Occasionally, some databases also use different names for certain data types. For example, in Oracle, “decimal” is called “number” and “blob” is known as “raw.” As such, it is crucial to verify the details about each SQL data type in the database you’re using. With this, we come to the end of this article. In conclusion, data types are the backbone of any database, as you cannot store and manipulate data without
them. Now that you know more about SQL data types, you are ready to start learning how to store, query, and manipulate data to become an expert in SQL. You can take the next steps in your SQL journey by learning and practice with one of the more widely used database systems, like MySQL and Oracle. Check out our next tutorial on Limit in SQL If you
liked this article and want to get certified, you can check out Simplilearn’s Business Analyst Master’s Program, which also covers SQL in great depth. Do you have any questions for us? Ask them in our “All You Need to Know About SQL Data Types” comments section, and we’ll have our experts in the field answer them for you. What type of data is stored in table?In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks.
Which type of database stores data in columns and rows?Columnar database example
This enables individual data elements, such as customer name to be accessed in columns as a group, rather than individually row-by-row. Here is an example of a simple database table with four columns and three rows.
What is a column of data in a database called?The term “field” is usually used interchangeably with “column,” but database purists prefer to use the word “field” to denote a particular value or single item of a column.
What is table and how it is used in storing data?The table is the primary storage object for data in a relational database. In its simplest form, a table consists of row(s) and column(s), both of which hold the data. A table takes up physical space in a database and can be permanent or temporary.
|
When a column in a table is defined, what determines the kind of data it can store?
zusammenhängende Posts
Urheberrechte © © 2024 paraquee Inc.
