2013. 4. 1. 17:10 IT/MSSQL

MSSQL Data Type(2)

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

MSSQL NUMERIC DATA TYPE

 

BIGINT, 8 bytes of storage . -263 to 263 -1. If possible avoid this.

 

INT, 4 bytes of storage ; store numbers from -231 to 231 -1.

 

SMALLINT, 2 bytes of storage store numbers from -215 to 215 -1.

 

TINYINT, 1 byte of storage store numbers from 0 to 255.

 

MONEY, 8 bytes of storage.

 

SMALLMONEY, 4 bytes of storage.

 

 

REAL, 4 bytes for storage; precision of 7 digits. The synonym for REAL is FLOAT[(n)] for n = 1 to 7.

 

FLOAT, 8 bytes for storage;precision of 15 digits. The synonym for FLOAT is DOUBLE PRECISION and FLOAT[(n)] for n = 8 to 15.

 

DECIMAL, whose storage size varies based on the specified precision and uses 217 bytes for storage. The synonyms for DECIMAL are DEC and NUMERIC.

 

 

 


Character Data Types

 

 

CHAR(n)s are fixed-length single-byte and can store8,000 bytes of data. Faster than varchar type. Useful for fixed data which invariable. Social Security number could be of CHAR(9)

 


Varchar(n) are non-fixed-length and can store also 8000 bytes of data. = variable char.

"Variable length means that if less data than the specified n bytes is used, the storage size will be the actual length of the data entered."(quoted from "Learning SQL series" by oreilly).

 


Varchar is same as varchar2 in Oracle.

 

 

 


Text: more than 8000 bytes. It is same as Long in Oracle.

 

 

 

 

 

 

Nchar and Nvarchar are for unicode.

 

 


Tips) from Oreilly book "Learning SQL series"


1. Use the variable-length data types (VARCHAR) over fixed-length data types (CHAR) when you expect a lot of null values or a lot of variation in the size of data.

 

2. 데이터 사이즈의 변화가 심하거나 널 가치가 들어갈때에는 char 대신 varchar타입을 사용하라.

 


3. If a column's data does not vary widely in number of characters, consider using CHAR instead of VARCHAR.

 

4. 칼럼의 데이타가 캐릭터 수치상 크게 변하지 않을때는 varchar대신 char를 사용.


5. NVARCHAR or NCHAR data types should not be used unless you need to store 16-bit character (Unicode) data.

NVARCHARs and NCHARs take up twice as much space as VARCHAR or CHAR data types, reducing I/O performance.


6. nvarchar나 nchar,는 꼭 필요할때만 사용한다. varchar나 char보다 2배의 공간을 차지하기 때문이다.

 

 

 

Date and Time Data Types

 


datetime(date in Oracle): up to 8 bytes;

smalldatetime: up to 4 bytes;

Respectively stored as date and time part. Primary key should not be used in these data types.

 

 

 

 


Others


The BINARY data types are BINARY and VARBINARY.

 


1.BINARY data : store strings of bits, hexadecimal (hex) representation. 8000 bytes.

 

널값이나 데이터 사이즈 변화가 자주일어날때는 Varbinary를 사용한다.


2. The VARBINARY data type can store up to 8,000 bytes of variable-length binary data.

 

 

 

 

 

In Oracle, raw is the equivalent of VARBINARY.

 

 

 

 

 

IMAGE DATA TYPE: USED TO STORE PICTURES AND BINARY DATA UP TO 8000 BYTES.

IMAGE IS SAME AS "LONG LAW" IN ORACLE.

 

 

 

 


*BIT DATATYPE: 0 OR 1. Can not be indexed and null.

 

 

 

 

 

 

 


The monetary data types

Monetary data types are generally used to store monetary values.

MONEY: 8 bytes of storage
SMALLMONEY:4 bytes of storage


money


 -922,337,203,685,477.5808 - 922,337,203,685,477.5807
 8바이트
 


smallmoney


 - 214,748.3648 - 214,748.3647
 4바이트
 

 

 

 

 

The TABLE data type

: Used to store function's result.

 

 

 

 

 

 

 

 

 

 

 

 

 

3.1.4.7. The UNIQUEIDENTIFIER data type

 

 

The UNIQUEIDENTIFIER data type, globally unique identifier (GUID)


3.1.4.8. The XML data type

 


Introduced in sql server 2005 for the first time.

 


Tips) Selecting Data Types

If possible, use smallest column sizes to faster sql server.


For example, if you want to store numbers from 1 to 100 in a column, selecting TINYINT is better choice than INT.

 


Better use numeric data type like INTEGER than VARCHAR OR CHAR. Because it requires much less space than character data types.

From. http://blog.daum.net/wonderful_nhk/417

'IT > MSSQL' 카테고리의 다른 글

MSSQL 문자열 함수  (0) 2013.04.09
Trigger 트리거 관련 정보 조회.  (0) 2013.04.09
MSSQL Data Type  (0) 2013.04.01
MSSQL Int (Integer) Type  (0) 2013.04.01
Database Compare Tool.  (0) 2013.03.21
Posted by 당양부부34

블로그 이미지
주요 토렌트를 블로깅하고 있습니다. 토렌트 순위 등은 다른 사이트를 찾아보세요. 주요 웹툰 순위도 게재했어요 경제를 좋아하는 일산의 행복한 프로그래머입니다.
당양부부34
Yesterday
Today
Total

달력

 « |  » 2024.3
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함