TL;DR – Different database management systems use different server data types, so it's vital to always use relevant ones.
SQL Server data types Overview. In SQL Server, a column, variable, and parameter holds a value that associated with a type, or also known as a data type. A data type is an attribute that specifies the type of data that these objects can store. It can be an integer, character string, monetary, date and time, and so on. SQL Server provides a list. Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server To get the Table in information in SQL Server, we can use below Query: SELECT. FROM INFORMATIONSCHEMA.COLUMNS WHERE TABLENAME = 'Users' ORDER BY ORDINALPOSITION. SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft.NET Framework. Alias data types are based on the system-supplied data types. For more information about alias data types, see CREATE TYPE (Transact-SQL). Query below lists all tables in SQL Server database. Query select schemaname(t.schemaid) as schemaname, t.name as tablename, t.createdate, t.modifydate from sys.tables t order by schemaname, tablename.
Contents
SQL Server Data Types
SQL Server String Data Types
Type | Description | Storage |
---|---|---|
char(n) | A character string of a defined width | Up to 8,000 characters |
varchar(n) | A variable string of a defined width | Up to 8,000 characters. |
varchar(max) | A variable string of a defined width | Up to 1,073,741,824 characters |
text | A variable string of a defined width | Up to 2GB data of text |
nchar | A Unicode string of a defined width | Up to 4,000 chars |
nvarchar | A Unicode string of a variable width | Up to 4,000 chars |
nvarchar(max) | A Unicode string of a variable width | Up to 536,870,912 chars |
ntext | A Unicode string of a variable width | Up to 2GB text data |
binary(n) | A binary string of a defined width | Up to 8,000 bytes |
varbinary | A binary string of a variable width | Up to 8,000 bytes |
varbinary(max) | A binary string of a variable width | Up to 2 GB |
image | A binary string of a variable width | Up to 2 GB |
SQL Server Number Data Types
Type | Description | Storage |
---|---|---|
bit | Allows to use 0.1 and NULL. | |
tinyint | Allows to use numbers in the range of 0 to 255 | 1 byte |
smallint | Allows to use numbers in the range of -32,768 to 32,767 | 2 bytes |
int | Allows to use numbers in the range of -2,147,483,648 to 2,147,483,647 | 4 bytes |
bigint | Allows to use numbers in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 8 bytes |
decimal(p,s) | Defines scale and precision numbers, allows to use numbers in the range of -10^38 to 10^38 –1 | 5–17 bytes |
numeric(p,s) | Defines scale and precision numbers, allows to use numbers in the range of -10^38 +1 to 10^38 –1. The total amount of containable digits is defined in the p parameter which needs to have a value in the range of 1 to 38. To define the amount of digits to be contained in the right side, you define the s parameter. It has to have a value in the range from 0 to p. | 5-17 bytes |
smallmoney | Fiscal data in the range of -214,748,3648 to 214,748,3647 | 4 bytes |
money | Fiscal data in the range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes |
float(n) | Data in the range of -1.79E + 308 to 1.79E + 308 of floating precision numbers. Define the data record field byte length (4 or 8) in the n parameter. | 4 or 8 bytes |
real | Data in the range of -3.40E + 38 to 3.40E + 38 of floating precision numbers. | 4 bytes |
SQL Server Date Data Types
Type | Description | Storage |
---|---|---|
datetime | Has the range of January 1, 1753 to December 31, 9999 and the precision of 3.33 ms | 8 bytes |
datetime2 | Has the range of January 1, 0001 to December 31, 9999 and the precision of 100 ns | 6-8 bytes |
smalldatetime | Has the range of January 1, 1900 to June 6, 2079 and the precision of 1 minute | 4 bytes |
date | Has the range of January 1, 0001 to December 31, 9999. Only good for containing a date | 3 bytes |
time | Has the range of January 1, 0001 to December 31, 9999 and the precision of 100 ns. Only good for containing a date | 3-5 bytes |
datetimeoffset | A duplicate of datetime2 with an offset of a time zone. | 8-10 bytes |
timestamp | Contains a number that is unique and refreshes with every new row. It works around an internal clock (not real time). Use limited to a single timestamp per table |
Other SQL Server Data Types
Type | Description |
---|---|
sql_variant | Contains a maximum of 8,000 bytes of different data types apart from timestamp, ntext and text |
uniqueidentifier | Contains a GUID |
xml | Contains up to 2GB of data formatted as XML |
cursor | Contains references to the database operation cursor |
table | Contains a set of results used for later processing |
- Simplistic design (no unnecessary information)
- High-quality courses (even the free ones)
- Variety of features
- Nanodegree programs
- Suitable for enterprises
- Paid certificates of completion
- Professional service
- Flexible timetables
- A variety of features to choose from
- Professional certificates of completion
- University-level courses
- Multiple Online degree programs
- Great user experience
- Offers quality content
- Very transparent with their pricing
- Free certificates of completion
- Focused on data science skills
- Flexible learning timetable
MySQL Data Types
MySQL Text Data Types
Type | Description | Storage |
---|---|---|
CHAR(size) | Contains a string with a defined length (numbers, special characters, letters), defined using parentheses | Up to 255 characters |
VARCHAR(size) | Contains a string with a defined length (numbers, special characters, letters), defined using parentheses | Up to 255 characters (if more, will be changed to the text type) |
TINYTEXT | Contains a string | Up to 255 characters |
TEXT | Contains a string | Up to 65,535 characters |
BLOB | Contains data | Up to 65,535 bytes |
MEDIUMTEXT | Contains a string | Up to 16,777,215 characters |
MEDIUMBLOB | Contains data | Up to 16,777,215 bytes |
LONGTEXT | Contains a string | Up to 4,294,967,295 characters |
LONGBLOB | Contains data | Up to 4,294,967,295 bytes |
ENUM(x,y,z,etc.) | Allows you to insert an available value list (if no value is defined, a blank one is created) | Up to 65,535 bytes |
SET | A list with various choices of storage possibilities | Up to 64 items |
MySQL Number Data Types
Type | Description |
---|---|
TINYINT(size) | Can contain numbers from -128 to 127 or 0 to 255 UNSIGNED. The length is defined using parentheses. |
SMALLINT(size) | Can contain numbers from -32768 to 32767 or 0 to 65535 UNSIGNED. The length is defined using parentheses. |
MEDIUMINT(size) | Can contain numbers from -8388608 to 8388607 or 0 to 16777215 UNSIGNED. The length is defined using parentheses. |
INT(size) | Can contain numbers from -2147483648 to 2147483647 or 0 to 4294967295 UNSIGNED. The length is defined using parentheses. |
BIGINT(size) | Can contain numbers from -9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615 UNSIGNED. The length is defined using parentheses. |
FLOAT(size,d) | Contains a number with a floating decimal point. The length is defined using the parameter size d. |
DOUBLE(size,d) | Contains a big number with a floating decimal point. The length is defined using the parameter size d. |
DECIMAL(size,d) | Contains a big number with a floating decimal point. Used for fixed decimal points. The length is defined using the parameter size d. The above text type is contained as string. |
Other MySQL Data Types
Type | Description | Format | Length range |
---|---|---|---|
DATE() | A date | YYYY-MM-DD | '1000-01-01' to '9999-12-31' |
DATETIME() | Date/Time | YYYY-MM-DD HH:MI:SS | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
TIMESTAMP() | A timestamp wuth its values contained as seconds from the start of Unix ('1970-01-01 00:00:00' UTC). | YYYY-MM-DD HH:MI:SS | '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC |
TIME() | A time | HH:MI:SS | '-838:59:59' to '838:59:59' |
YEAR() | Two or four digit format year. Range in four digit: 1901 to 2155. In two digit: 70 to 69 (1970 to 2069) |
Microsoft Access Data Types
Type | Description | Storage |
---|---|---|
Text | Needed to combine number with text | Up to 255 characters |
Memo | Needed to work with a text of a large volume. Can be searched through but not sorted | Up to 65,536 characters |
Byte | Allows to use a number between 0 and 255 | 1 byte |
Integer | Allows to use a number from -32,768 to 32,767 | 2 bytes |
Long | Allows to use a number from -2,147,483,648 to 2,147,483,647 | 4 bytes |
Single | Works with most decimals. Singular floating-point precision | 4 bytes |
Double | Works with most decimals. Dual floating-point precision | 8 bytes |
Currency | Needed to choose the country's currency. A 15 digit container with 4 decimal locations. | 8 bytes |
AutoNumber | Assigns a unique number to each data record | 4 bytes |
Date/Time | Needed when working with time and date | 8 bytes |
Yes/No | Used for logical data record fields. True or False statements are used. No NULL values are allowed | 1 bit |
Ole Object | Used to contain audio, pictures, BLOBs and video | up to 1GB |
Hyperlink | Used to link web pages and files | |
Lookup Wizard | Used to look up a drop-down option list | 4 bytes |
By: Matteo Lorini | Updated: 2017-04-18 | Comments (1) | Related: More >T-SQL
Problem
Within the last 15+ years of work experience as an MSSQL DBA and Developer for small, mediumand large corporations the simple task of keeping table schemas in syncamongst development, QA and production environments seems to be a recurring task.The market offers a variety of good tools that can help tosynchronize SQL Server environments, however there is a quick and dirty way thatcan be used to compare table schemas directly from Microsoft SQL Server which wewill cover in this tip.
Solution
Without the use of third party tools, one way to compare tableschemas using native T-SQL is to query the INFORMATION_SCHEMA.COLUMNS metadataand build a custom made procedure or logic that compares two tables.
Another way to do this is to use the dynamic management functionsys.dm_exec_describe_first_result_set,available since SQL Server 2012 that takes a T-SQL statement as inputparameter and outputs the metadata description.
Sql Server Data Types
Let's see how we can take advantageof this dynamic management function to build a way to compare table structures.
Create Sample Tables
Let's create two tables as follows. For simplicity I am creating thesein the same database, but this could be done among different databases and evendifferent instances of SQL Server and we could useLinkedServers andfour part naming to address the objects if we want to compare againstdifferent servers.
Output from dm_exec_describe_first_result_set
Let's get familiar with the dynamic management function to see what kind ofmetadata information it retrieves. We will just pull back data for theusrID and usrPhonNum columns.
We can see the metadata that is returned.
Running Comparisons
Let's now compare the tables between our two tables.
You can see below that I am pulling back various columns from the dynamicmanagement function and doing an OUTER JOIN between the two tables. This willallow me to join the two tables based on the column name.
We can see a comparison between the two datasets. We can see these tablesare almost identical, except that the usrID is not an identity on WebUsers2.
Make Changes and Compare Again
Let's now change the schema of the WebUsers2 table by making column WebAddr nullable and by adding a newcolumn named usrWebIP. We will just drop and recreate the table asfollows.
We can now see the differences between the two tables.
Make More Changes and Compare
Let's change the schema of the WebUsers2 table again and re-run the compare script.
We can see the differences highlighted in blue.
Conclusion
We have seen how we can utilize a simple dynamic management function to createsomething meaningful and useful to quickly compare table metadata. A possibleuse of such function is to create a scheduled job that will detect any metadatachanges from different environments.
References
- Information Schema UsageINFORMATION_SCHEMA.COLUMNS
Next Steps
- Try to use Information_Schema to create a table compare procedure.
- Check out theseInformation Schema Columns Tips
- Evaluate theseSQL Server Comparison Tools
- Read this tip:Ways to compare and find differences for SQL Server tables and data.
Last Updated: 2017-04-18
Sql Data Typesaskfasr Data
About the author
View all my tips