Difference between revisions of "MSSQL:Tipi di dato"
From Aino Wiki
(→Mappa e Link) |
(No difference)
|
Latest revision as of 13:47, 9 July 2024
Contents
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
Dati numerici
Lista
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
