mssql

MSSQL 기본 타입

moti 2023. 5. 12. 04:49

MSSQL의 타입을 정리해보았다.

글 순서는 먼저 다른 타입끼리 비교나 연산을 할 때 이뤄지는 묵시적 형변환에 대해 설명한 뒤, 날짜 및 시간 타입, 문자열 타입을 정리하였다.

묵시적 형변환

묵시적 형변환은 서로 다른 타입의 값이 연산이나 비교를 수행할 때 이뤄진다. 이 때 우선순위에 따라 높은 우선순위의 타입을 따라 변환된다. 하지만 명시적으로 변환을 시켜야하는 경우나 형변환을 지원하지 않는 경우가 있기 때문에 확인을 먼저 해야한다. 또한 인덱스를 활용해야하는 경우, 인덱스의 키 값에서 묵시적 형변환이 이뤄지면 인덱스를 활용할 수 없기 때문에 안전하게 명시적으로 형변환을 하는것이 낫다.

묵시적 형변환 우선순위

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar (including nvarchar(max) )
  26. nchar
  27. varchar (including varchar(max) )
  28. char
  29. varbinary (including varbinary(max) )
  30. binary (lowest)

형 변환 테이블

날짜, 시간 타입

date

  • 범위 : 0001-01-01 부터 9999-12-31 까지
  • 기본 문자열 포맷 : YYYY-MM-DD
  • 크기 : 3byte 고정
  • 기본 값 : 1900-01-01
DECLARE @date date= '2023-05-05'
SELECT @date AS '@date'
go

+----------+
|@date     |
+----------+
|2023-05-05|
+----------+

String to date

Cast 함수를 사용해 다른 종류의 날짜, 시간 타입의 문자열 형태를 date 타입으로 변환 가능하다

SELECT
     cast('2016-03-03' as date) as 'date',
     cast('2016/03/03' as date) as 'date2',
     cast('2016-03-03 12:35:00' as date) as 'smalldatetime',
     cast('12:35:29. 1234567' as date) as 'time',
     cast('2016-03-03 12:35:29.123' as date) as 'datetime',
     cast('2016-03-03 12:35:29.1234567' as date) as 'datetime2',
     cast('2016-03-03 12:35:29.1234567 +12:15' as date) as 'datetimeoffset'

+----------+----------+-------------+----------+----------+----------+--------------+
|date      |date2     |smalldatetime|time      |datetime  |datetime2 |datetimeoffset|
+----------+----------+-------------+----------+----------+----------+--------------+
|2016-03-03|2016-03-03|2016-03-03   |1900-01-01|2016-03-03|2016-03-03|2016-03-03    |
+----------+----------+-------------+----------+----------+----------+--------------+

time 타입을 date로 변환하면 날짜 데이터가 없어 default 값인 1900-01-01 로 변환되는 것을 알 수 있다

integer to date

정수형을 바로 date로 변경시킬수 없기 때문에 char로 변환 후 다시 date로 변환한다

SELECT CONVERT(DATE, CONVERT(CHAR(10),20160303),112) as 'integer'

+----------+
|integer   |
+----------+
|2016-03-03|
+----------+

Implicit conversions

DECLARE @strdate char(10) = '2016-03-03'
DECLARE @date date = CAST('2016-03-03' as DATE)

IF (@strdate=@date)
BEGIN
    PRINT ('match')
end
ELSE
BEGIN
    PRINT ('not match')
end
go

match

datetime

  • 날짜 범위 : 1753-01-01 부터 9999-12-31 까지
  • 시간 범위 : 00:00:00 부터 23:59:59.997 까지
  • 기본 문자열 포맷 : 없음
  • 크기 : 8byte
  • 기본 값 : 1900-01-01 00:00:00
  • 정확도 : .000, .003, .007로 반올림 된다.

string to datetime

SELECT
     CAST('2016-03-03' as DATETIME) as 'date',
     CAST('2016/03/03' as DATETIME) as 'date2',
     CAST('2016-03-03 12:35:00' as DATETIME) as 'smalldatetime',
     CONVERT(DATETIME, CONVERT(TIME, '12:35:29. 1234567')) as 'time',
     CAST('2016-03-03 12:35:29.123' as DATETIME) as 'datetime',
     CONVERT(DATETIME, CONVERT(DATETIME2,'2016-03-03 12:35:29.1234567')) as 'datetime2',
     CONVERT(DATETIME,CONVERT(DATETIMEOFFSET, '2016-03-03 12:35:29.1234567 +12:15')) as 'datetimeoffset';

+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
|date                   |date2                  |smalldatetime          |time                   |datetime               |datetime2              |datetimeoffset         |
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
|2016-03-03 00:00:00.000|2016-03-03 00:00:00.000|2016-03-03 12:35:00.000|1900-01-01 12:35:29.123|2016-03-03 12:35:29.123|2016-03-03 12:35:29.123|2016-03-03 12:35:29.123|
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+

time, datetime2, datetimeoffset의 문자열 형태는 cast함수로 바로 변환이 불가능하여 convert 함수를 사용하였다.

Implicit conversion

DECLARE @datetime2 datetime2 = '2016-03-03 12:35:29.1234567'
DECLARE @datetime datetime = '2016-03-03 12:35:29.123'
PRINT(datepart(microsecond, @datetime))
PRINT(DATEDIFF(MICROSECOND ,@datetime,@datetime2))
GO

123333
123

datetime에 저장할 수 없는 microsecond를 조회해봤더니 이상한 값이 들어간 것을 알 수 있었다. 만일 datetime과 datetime2의 비교나 연산을 해야할 경우 datetime2를 datetime으로 변환하여 microsecond 연산을 피하는 것이 좋을 것 같다.

datetime2[ (fractional seconds precision) ]

  • 날짜 범위 : 0001-01-01 부터 9999-12-31 까지
  • 시간 범위 : 00:00:00 부터 23:59:59.9999999 까지
  • 기본 문자열 포맷 : YYYY-MM-DD hh:mm:ss[.fractional seconds]
  • 크기 : pricision이 3보다 작을 경우 6byte, 3이나 4일 경우 7byte, 그 이상은 8byte
  • 기본 값 : 1900-01-01 00:00:00

string to datetime2

SELECT
     CAST('2016-03-03' as DATETIME2) as 'date',
     CAST('2016/03/03' as DATETIME2) as 'date2',
     CAST('2016-03-03 12:35:00' as DATETIME2) as 'smalldatetime',
     CAST('12:35:29. 1234567' as DATETIME2) as 'time',
     CAST('2016-03-03 12:35:29.123' as DATETIME2) as 'datetime',
     CAST('2016-03-03 12:35:29.1234567' as DATETIME2) as 'datetime2',
     CAST('2016-03-03 12:35:29.1234567 +12:15' as DATETIME2) as 'datetimeoffset';

+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
|date                       |date2                      |smalldatetime              |time                       |datetime                   |datetime2                  |datetimeoffset             |
+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
|2016-03-03 00:00:00.0000000|2016-03-03 00:00:00.0000000|2016-03-03 12:35:00.0000000|1900-01-01 12:35:29.1234567|2016-03-03 12:35:29.1230000|2016-03-03 12:35:29.1234567|2016-03-03 12:35:29.1234567|
+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+---------------------------+

datetime과는 다르게 time, datetimeoffset에 대한 cast 변환이 이뤄지는 것을 알 수 있다.

implicit conversion

DECLARE @datetime2 datetime2 = '2016-03-03 12:35:29.1234567'
DECLARE @datetimeoffset datetimeoffset = '2016-03-03 12:35:29.1234567 +12:15'
PRINT(DATEDIFF(HOUR ,@datetimeoffset,@datetime2))
GO

12

datetimeoffset[ (fractional seconds precision) ]

  • 날짜 범위 : 0001-01-01 부터 9999-12-31 까지
  • 시간 범위 : 00:00:00 부터 23:59:59.9999999 까지
  • offset 범위 : -14:00 에서 14:00까지
  • 기본 문자열 포맷 : YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
  • 크기 : 10byte
  • 기본 값 : 1900-01-01 00:00:00 00:00

string to datetimeoffset

SELECT
     CAST('2016-03-03' as DATETIMEOFFSET) as 'date',
     CAST('2016/03/03' as DATETIMEOFFSET) as 'date2',
     CAST('2016-03-03 12:35:00' as DATETIMEOFFSET) as 'smalldatetime',
     CAST('12:35:29. 1234567' as DATETIMEOFFSET) as 'time',
     CAST('2016-03-03 12:35:29.123' as DATETIMEOFFSET) as 'datetime',
     CAST('2016-03-03 12:35:29.1234567' as DATETIMEOFFSET) as 'datetime2',
     CAST('2016-03-03 12:35:29.1234567 +12:15' as DATETIMEOFFSET) as 'datetimeoffset';

+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
|date                              |date2                             |smalldatetime                     |time                              |datetime                          |datetime2                         |datetimeoffset                    |
+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
|2016-03-03 00:00:00.0000000 +00:00|2016-03-03 00:00:00.0000000 +00:00|2016-03-03 12:35:00.0000000 +00:00|1900-01-01 12:35:29.1234567 +00:00|2016-03-03 12:35:29.1230000 +00:00|2016-03-03 12:35:29.1234567 +00:00|2016-03-03 12:35:29.1234567 +12:15|
+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+

smalldatetime

  • 날짜 범위 : 1900-01-01 부터 2079-06-06
  • 시간 범위 : 00:00:00 부터 23:59:59까지
  • 기본 문자열 포맷 : 없음
  • 크기 : 4byte 고정
  • 기본 값 : 1900-01-01 00:00:00
  • 정확도 : 1분

string to smalldatetime

SELECT
     CAST('2007-05-08 12:35:29'     AS smalldatetime)
    ,CAST('2007-05-08 12:35:30'     AS smalldatetime)
    ,CAST('2007-05-08 12:59:59.998' AS smalldatetime);

+-------------------+-------------------+-------------------+
|                   |                   |                   |
+-------------------+-------------------+-------------------+
|2007-05-08 12:35:00|2007-05-08 12:36:00|2007-05-08 13:00:00|
+-------------------+-------------------+-------------------+

smalldatetime의 정확도는 1분이기 때문에 초를 반올림하여 분에 더하거나 뺀다.

SELECT
     CAST('2016-03-03' as SMALLDATETIME) as 'date',
     CAST('2016/03/03' as SMALLDATETIME) as 'date2',
     CAST('2016-03-03 12:35:00' as SMALLDATETIME) as 'smalldatetime',
     CONVERT(SMALLDATETIME, CONVERT(TIME, '12:35:29. 1234567')) as 'time',
     CAST('2016-03-03 12:35:29.123' as SMALLDATETIME) as 'datetime',
     CONVERT(SMALLDATETIME, CONVERT(DATETIME2,'2016-03-03 12:35:29.1234567')) as 'datetime2',
     CONVERT(SMALLDATETIME,CONVERT(DATETIMEOFFSET, '2016-03-03 12:35:29.1234567 +12:15')) as 'datetimeoffset';

+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+
|date               |date2              |smalldatetime      |time               |datetime           |datetime2          |datetimeoffset     |
+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+
|2016-03-03 00:00:00|2016-03-03 00:00:00|2016-03-03 12:35:00|1900-01-01 12:35:00|2016-03-03 12:35:00|2016-03-03 12:35:00|2016-03-03 12:35:00|
+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+

datetime과 마찬가지로 cast 변환이 이뤄지지 않는 경우는 convert를 거쳐 날짜,시간 타입으로 변환 후 변환작업을 수행하였음.

앞에서 본 것과 마찬가지로 초 단위는 반올림되어 사라진 것을 알 수 있다.

그럼 과연 데이터를 저장하면 어떻게 나타내질까?

CREATE TABLE smdt_test(
    sm_dt smalldatetime NULL
);

INSERT INTO smdt_test (sm_dt) VALUES ('2020-01-01 00:00:35');

SELECT * FROM smdt_test;

+-------------------+
|sm_dt              |
+-------------------+
|2020-01-01 00:01:00|
+-------------------+

이렇게 반올림 되어 저장되는 것을 알 수 있다.

time[ (fractional second scale) ]

  • 시간 범위 : 00:00:00.0000000 부터 23:59:59.9999999 까지
  • 기본 문자열 포맷 : hh:mm:ss[.nnnnnnn]
  • 크기 : 5byte 고정
  • 기본 값 : 00:00:00

string to time

SELECT
     CAST('2016-03-03' as TIME) as 'date',
     CAST('2016/03/03' as TIME) as 'date2',
     CAST('2016-03-03 12:35:00' as TIME) as 'smalldatetime',
     CAST('12:35:29. 1234567' as TIME) as 'time',
     CAST('2016-03-03 12:35:29.123' as TIME) as 'datetime',
     CAST('2016-03-03 12:35:29.1234567' as TIME) as 'datetime2',
     CAST('2016-03-03 12:35:29.1234567 +12:15' as TIME) as 'datetimeoffset';

+--------+--------+-------------+--------+--------+---------+--------------+
|date    |date2   |smalldatetime|time    |datetime|datetime2|datetimeoffset|
+--------+--------+-------------+--------+--------+---------+--------------+
|00:00:00|00:00:00|12:35:00     |12:35:29|12:35:29|12:35:29 |12:35:29      |
+--------+--------+-------------+--------+--------+---------+--------------+

문자열 & 이진 타입

문자열 타입은 charset과 collation이 필요하다. Charset은 문자열을 어떻게 인코딩하여 바이트로 표현할 것인지를 나타내고, collation은 문자의 비교가 애매한 부분이 있기 때문에 비교 규칙을 설정해 주는 것이다. Collation은 query, row, database, server에서 다르게 설정할 수 있지만, 잘못 설정했을 경우 충돌이 일어나거나 일부 충돌이 일어나지 않는 데이터베이스의 경우에는 자동 변환이 일어나지만 인덱스를 효율적으로 사용하지 못할 수 있다. 따라서 문자열 타입은 char나 varchar처럼 데이터 타입 뿐만아니라 charset과 collation이 일치해야 같은 타입이라고 볼 수 있다.

binary[(n)], varbinary[(n | max)]

이진 데이터 형식

binary

  • 크기 : n byte
  • 범위 : 1 ≤ n ≤ 8000
  • 기본 길이 : 1, cast 함수에선 30

varbinary

  • 크기 : n + 2 byte, max일 때 2^31-1(2GB)
  • 범위 : 1 ≤ n ≤ 8000, max
  • ANSI SQL : binary varying
  • 기본 길이 : 1, cast 함수에선 30

binary 및 varbinary 데이터 변환

만일 binary나 varbinary에서 설정한 크기보다 큰 데이터를 변환한다면 데이터가 잘려서 입력된다.

아래의 타입들은 오른쪽의 데이터가 패딩되거나 잘린다.

  • char
  • varchar
  • nchar
  • nvarchar
  • binary
  • varbinary
  • text
  • ntext
  • image

이 외의 데이터 타입은 왼쪽이 패딩되거나 잘린다.

SELECT CAST(256 as binary(1))
-- 0x00
SELECT CAST('123' as binary(1))
-- 1

Int 타입을 binary로 변환한 경우와 문자열을 binary로 변환한 경우를 비교해봤다.

Int 타입의 경우 0x0100이 원본 값이지만 왼쪽이 잘려 0x00이 출력됐다.

문자열의 경우 ‘123’이 원본 값이지만 오른쪽이 잘려 ‘1’이 출력됐다.

binary collation

Binary collation은 locale과 데이터 타입에 의해 정의된 코딩된 값의 순서로 정렬된다. binary는 유니코드 데이터가 아니기 때문에 ANSI 코드 페이지에 정의되어있는 코드 포인트들로 비교하게된다.

아래의 쿼리로 collation을 조회해보면 charset과 collation이 없는 것을 알 수 있다.

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'smdt_test';

+-----------+------------------+----------------------------+-------------+------------------------+
|COLUMN_NAME|CHARACTER_SET_NAME|COLLATION_NAME              |DATA_TYPE    |CHARACTER_MAXIMUM_LENGTH|
+-----------+------------------+----------------------------+-------------+------------------------+
|sm_dt      |null              |null                        |smalldatetime|null                    |
|str        |iso_1             |SQL_Latin1_General_CP1_CI_AS|varchar      |10                      |
|bin_str    |null              |null                        |binary       |1                       |
+-----------+------------------+----------------------------+-------------+------------------------+

char[(n)], varchar[(n | max)]

문자 데이터 형식

MySQL과는 다르게 n은 문자 수를 정의하는 것이 아니라 byte를 정의한다

UTF-8 문자 인코딩을 사용한다

char[(n)]

  • 범위 : 1 ≤ n ≤ 8000
  • 크기 : n byte
  • 저장 가능한 문자 수 : n / 인코딩된 문자의 byte
  • 기본 값 : 1, cast 함수 사용 시 30

varchar[(n | max)]

  • 범위 : 1 ≤ n ≤ 8000, max
  • 크기 : n + 2 byte, max일 때 2^31-1(2GB)
  • 저장 가능한 문자 수 : n / 인코딩된 문자의 byte
  • 기본 값 : 1, cast 함수 사용 시 30

문자열 비교

문자열 비교는 앞에서 말했듯이 collation 규칙에 따라 달라진다. 하지만 후행 공백을 어떻게 처리하여 비교하는지는 데이터베이스마다 다르다.

char는 고정 크기 문자열이기 때문에 명시된 크기보다 적게 입력하면 남은 길이만큼 후행 공백이 생긴다. 그래서 다른 길이의 char를 비교할 경우 같은 값이 들어있다 하더라도 동일하게 간주되지 않을 것 같다.

DECLARE @char char(2) = '1'
DECLARE @char2 char(3) = '1'

IF (@char2 = @char)
BEGIN
    PRINT ('EQUAL')
end
ELSE
BEGIN
    PRINT ('NOT EQUAL')
end

EQUAL

하지만 ANSI 표준에는 길이가 다른 문자열 비교를 할 경우 패딩으로 길이를 맞추기 때문에 왼쪽 데이터가 같으면 동일하게 간주한다.

패딩을 추가하는 작업 없이 비교하고 싶으면 LIKE 로 비교를 진행하면 된다.

DECLARE @varchar varchar(2) = '1 '
DECLARE @varchar2 varchar(2) = '1'

IF (@varchar2 = @varchar)
BEGIN
    PRINT ('EQUAL')
end
ELSE
BEGIN
    PRINT ('NOT EQUAL')
end

IF (@varchar2 LIKE @varchar)
BEGIN
    PRINT ('EQUAL')
end
ELSE
BEGIN
    PRINT ('NOT EQUAL')
end
GO

EQUAL
NOT EQUAL

하지만 char의 경우 LIKE 를 사용해도 다른 길이의 문자열이 동일하게 간주된다.

DECLARE @char char(2) = '1'
DECLARE @char2 char(3) = '1'
IF (@char2 like @char)
BEGIN
    PRINT ('EQUAL')
end
ELSE
BEGIN
    PRINT ('NOT EQUAL')
end

EQUAL

그래서 두 char 타입 데이터의 길이를 비교해보았다.

DECLARE @char char(2) = '1'
DECLARE @char2 char(3) = '1'

select len(@char2), len(@char)

+-+-+
| | |
+-+-+
|1|1|
+-+-+

즉, char는 데이터 파일에 저장될 때는 길이에 맞춰 패딩이 추가되지만, 데이터를 가져올 때는 후행 공백을 제거하고 가져오는 것을 알 수 있다. 그렇기 때문에 다른 길이의 char 타입 데이터라도 저장하고 있는 값이 같으면 동일하게 간주된다.

nchar[(n)], nvarchar[(n | max)]

문자 데이터 형식

n은 byte 쌍의 개수를 정의한다. 즉, 기존 char, varchar보다 n으로써 정의되는 공간이 두 배 많아진다.

n이 문자 개수라고 착각할 수 있지만 크기를 정의하는 것이고, 한 문자당 2byte를 넘어서는 유니코드 범위에서는 한 문자가 두개의 byte 쌍을 차지할 수 있다.

UTF-16 문자 인코딩을 사용한다

nchar[(n)]

  • 범위 : 1 ≤ n ≤ 4000
  • 크기 : n * 2 byte
  • 저장 가능한 문자 수 : n * 2 / 인코딩된 문자의 byte 보다 크고 가장 가까운 2의 배수
  • 기본값 : 1, cast 함수 사용 시 30

nvarchar[(n | max)]

  • 범위 : 1 ≤ n ≤ 4000, max
  • 크기 : n * 2 byte, max일 때 2^31-1(2GB)
  • 저장 가능한 문자 수 : n * 2 + 2 / 인코딩된 문자의 byte 보다 크고 가장 가까운 2의 배수
  • 기본값 : 1, cast 함수 사용 시 30

ntext, text, image

대용량의 유니코드 및 비유니코드 문자와 이진 데이터를 저장하기 위한 고정 및 가변 길이 데이터 형식

Unicode UCS-2 문자 집합을 사용한다.

ntext, text, image 데이터 형식은 이후 버전에서 제거된다.

대신 nvarchar(max), varchar(max), varbinary(max)를 사용한다

ntext

  • 최대 문자열 길이 : 2^30 - 1(1,073,741,823)바이트
  • 크기 : 입력된 문자열 길이의 두 배
  • 가변 길이 유니코드 데이터

text

  • 최대 문자열 길이 : 2^31 - 1(2,147,483,647)바이트
  • 가변 길이 비유니코드 데이터

image

  • 크기 : 0 ~ 2^31-1(2,147,483,647)바이트
  • 가변 길이 이진 데이터

 

궁금한 점

왜 datetime과 smalldatetime은 오차가 발생하고 cast함수로 변환되지 않는 날짜, 시간 타입이 있는 걸까?

참조

 

INF: SQL Server가 문자열을 후행 공백과 비교 하는 방법 - Microsoft 지원

구독 혜택을 살펴보고, 교육 과정을 찾아보고, 디바이스를 보호하는 방법 등을 알아봅니다. Microsoft 365 구독 혜택 커뮤니티를 통해 질문하고 답변하고, 피드백을 제공하고, 풍부한 지식을 갖춘

support.microsoft.com

 

Transact-SQL 참조(데이터베이스 엔진) - SQL Server

이 문서에서는 T-SQL(Transact-SQL) 참조 문서를 찾고 사용하는 방법에 대한 기본 사항을 제공합니다.

learn.microsoft.com