SQL Server data types are used to specify the type of data that is stored in a column of a table. The basic SQL Server data types can be divided into the following categories:
1. Character strings: These data types are used to store textual data. They include:
- ‘CHAR(n)‘: Fixed-length string with a length of n. For example, ‘CHAR(10)‘ can store a string with up to 10 characters.
- ‘VARCHAR(n)‘: Variable-length string with a maximum length of n. For example, ‘VARCHAR(10)‘ can store strings of up to 10 characters long.
- ‘TEXT‘: A string data type that can store up to 23̂1-1 bytes of non-Unicode data.
2. Unicode Character strings: These data types are used to store Unicode-encoded textual data. They include:
- ‘NCHAR(n)‘ : A fixed-length string, storing exactly n Unicode characters. For example, ‘NCHAR(10)‘ can store 10 Unicode characters.
- ‘NVARCHAR(n)‘ : A variable-length Unicode string with a maximum length of n. For example, ‘NVARCHAR(10)‘ can store Unicode strings up to 10 characters long.
- ‘NTEXT‘: Similar to TEXT data type but stores Unicode-encoded data instead.
3. Numeric data types: These data types are used to store numeric data with different levels of precision and range. They include:
- ‘BIT‘: A bit data type that can store 0 or 1.
- ‘TINYINT‘: A small integer data type that can store values from 0 to 255.
- ‘SMALLINT‘: An integer data type that can store values from -32,768 to 32,767.
- ‘INT‘: A standard integer data type that can store values from -23̂1 to 23̂1-1.
- ‘BIGINT‘: A large integer data type that can store values from -26̂3 to 26̂3-1.
- ‘FLOAT(p)‘: A float data type that can store floating-point numbers. p stands for the number of bits used to store the mantissa.
- ‘DECIMAL(p,s)‘: A decimal data type that can store decimal numbers with a precision of p digits and a scale of s digits.
4. Date and time data types: These data types are used to store date and time information. They include:
- ‘DATE‘: Stores only the date and has a range of January 1, 1 AD through December 31, 9999.
- ‘TIME‘: Stores only the time information.
- ‘DATETIME‘: Stores both date and time information. This data is accurate to a single minute and has a range of January 1, 1753, through December 31, 9999.
- ‘DATETIME2‘: Similar to DATETIME, but with a greater precision, down to 100 nanoseconds.
- ‘SMALLDATETIME‘: Accurate to the minute but has a smaller range than DATETIME. This data type has a range of January 1, 1900, through June 6, 2079.
These are the basic SQL Server data types. There are also other data types, such as cursor, cursor variable, table, and XML data types, which provide specific functionality for handling data in SQL Server.