Sql Data Typesaskfasr

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.

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

Pros

Simplistic design (no unnecessary information)
High-quality courses (even the free ones)
Variety of features

Main Features

Nanodegree programs
Suitable for enterprises
Paid certificates of completion

Pros

Professional service
Flexible timetables
A variety of features to choose from

Main Features

Professional certificates of completion
University-level courses
Multiple Online degree programs

Pros

Great user experience
Offers quality content
Very transparent with their pricing

Main Features

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