MSSQL:Tipi di dato

Lista

Elenco completo sufddiviso per classe di tipo: w3schools.com
MS SQL Server Data types:

Data type	Length	Description
bigint	8	Integer from -2^63 (-9 223 372 036 854 775 808) to 2^63-1 (9 223 372 036 854 775 807).
int	4	Integer from -2^31 (-2 147 483 648) to 2^31-1 (2 147 483 647).
smallint	2	Integer from -2^15 (-32 768) to 2^15-1 (32 767).
tinyint	1	Integer from 0 to 255.
bit	1 bit	Integer 0 or 1.
decimal(precision, scale)	5-17	Numeric data type with fixed precision and scale (accuracy 1-38, 18 by default and scale 0-p, 0 by default). precision include sia i numeri a sx della virgola che quelli a destra, scale indica le cifre a dx della virgola. Es.: CREATE TABLE dbo.MyTable ( MyDecimalColumn decimal(5,2) ,MyNumericColumn numeric(10,5) ); ------------- INSERT INTO dbo.MyTable VALUES (123, 12345.12); ------------- SELECT MyDecimalColumn, MyNumericColumn FROM dbo.MyTable; restituirà: MyDecimalColumn MyNumericColumn --------------- --------------- 123.00 12345.12000
numeric	5-17	Same as data type 'decimal'.
money	8	Financial data type from -2^63 (-922 337 203 685 477.5808) to 2^63-1 (922 337 203 685 477.5807) with the precision of one ten-thousandth unit.
smallmoney	4	Financial data type from -2^31 (-214 748.3648) to 2^31-1 (214 748.3647) with the precision of one ten-thousandth unit.
float(n)	4-8	Numeric data type with float precision, where n is the number of mantis bits (1-24, accuracy of 7 digits, size of 4 bytes and 25-53, accuracy of 15 digits and size of 8 bytes).
real	4	Numeric data type with float precision that is defined as a float(24).
datetime	8	Data type representing date and time from 1.1.1753 to 31.12.9999 with precision about 3ms. Values are rounded to .000, .003 and .007.
smalldatetime	4	Data type representing date and time from 1.1.1900 to 6.6.2079 with precision of 1min. Values up to 29.998 are rounded down and values from 29.999 are rounded down to the nearest minute.
char	n	Char string of fixed length and max. length of 8000 chars.
varchar	n	Char string of variable length and max. length of 8000 chars.
text	n	Char string of variable length and max. length of 2^31-1 (2 147 483 647) chars.
nchar	2*n	Unicode char string of fixed length and max. length of 4000 chars.
nvarchar	2*n	Unicode char string of variable length and max. length of 4000 chars.
ntext	2*n	Unicode char string of variable length and max. length of 2^30-1 (1 073 741 823) chars.
sysname	128 * 2*n	come ''nvarchar(128)'' eccetto che per il fatto che, di default, sysname è NOT NULL. E' il tipo dei nomi degli oggetti del DB.
binary	n+4	Binary data of fixed length and max. length of 8000 bytes.
varbinary	n+4	Binary data of variable length and max. length of 8000 bytes. Es.:     Addr AS VARBINARY(MAX)
image	n	Binary data of variable length and max. length of 2^31-1 (2 147 483 647) bytes.
cursor		For storing the reference to cursors in a variable or in a procedure (no for CREATE TABLE).
sql_variant		For storing value of another type (no text, ntext, image, timestamp, sql_variant) of max. length to 8016 bytes. ODBC doesn't fully support this data type.
table		For storing the query result for the later usage.
timestamp	8+4	Data type generates automatically binary numbers, unique in the database, used mostly to the rows identification. There can be only column of this data type in the table.
uniqueidentifier		Data type for storing GUID (new by means of the NEWID function or existing from the string in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, for example 6F9619FF-8B86-D011-B42D-00C04FC964FF).

In particolare

Date

Un tipo particolare è il 'datetimeoffset' che include oltre alla data anche l'ora del giorno in offset alla data stessa per determinare l'orario rispetto all'UTC

MSDN

Dati numerici

Number data types:

Data type	Description	Storage
bit	Integer that can be 0, 1, or NULL
tinyint	Allows whole numbers from 0 to 255	1 byte
smallint	Allows whole numbers between -32,768 and 32,767	2 bytes
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
decimal(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
numeric(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
smallmoney	Monetary data from -214,748.3648 to 214,748.3647	4 bytes
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
float(n)	Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.	4 or 8 bytes
real	Floating precision number data from -3.40E + 38 to 3.40E + 38	4 bytes

Decimal

E' un valore decimale fatto da una parte intera ed una decimale, quella dopo la virgola. Concettualmente è simile al tipo Float solo che quest'ultimo è a virgola mobile mentre il DECIMAL è fissato il numero di bit per parte intera e per la decimale.
Un esempio è:

DECLARE @TipoDEcimal AS DECIMAL(12, 8)

il 12 è la lunghezza TOTALE delle cifre per la sua rappresentazione e 8 sono le cifre decimali usabili dopo la virgola, quindi se il decimale è definito come DECIMAL(n, d) n non può esser maggiore di 38 e n<=d.

Varie

Tabelle temporanee e variabili tabella

Nella stessa Wiki: Tabelle temporanee e var tabella

Mappa e Link

---

MS SQL | TSQL Linguaggio PowerBI Programmazione, DAX