SQL Workshop

[Pages:62]SQL Workshop

Data Types

Doug Shook

Data Types

Four categories ? String ? Numeric ? Temporal ? Other

26 types total

2

Numeric Types

The integer data types

Type bigint int smallint tinyint bit

Bytes 8 4 2 1 1

The decimal data types

Type decimal[(p[,s])] numeric[(p[,s])] money 8 smallmoney

Bytes 5-17 5-17

4

The real data types

Type float[(n)] real

Bytes 4 or 8 4

3

Numeric Types

Decimal data types include precision and scale ? Precision: total number of digits ? Scale: number of digits to the right of decimal

Integer and decimal types are exact ? Real data types include a small amount of error

4

String Types

String data types for storing standard characters

Type char[(n)] varchar[(n)]

Bytes n

String data types for storing Unicode characters

Type nchar(n) nvarchar(n)

Bytes 2?n

5

Date/time Types

Date/time data types prior to SQL Server 2008

Type datetime

Bytes 8

smalldatetime

4

Date/time data types for SQL Server 2008 and later

Type date 3 time(n) 3-5 datetime2(n) datetimeoffset(n)

Bytes

6-8 8-10

6

Date/time Types

Common date formats

Format

yyyy-mm-dd mm/dd/yyyy mm-dd-yy Month dd, yyyy Mon dd, yy dd Mon yy

Example

2012-04-30 4/30/2012 4-30-12 April 30, 2012 Apr 30, 12 30 Apr 12

Common time formats

Format

hh:mi 16:20 hh:mi am/pm hh:mi:ss hh:mi:ss:mmm hh:mi:ss.nnnnnnn

Example

4:20 pm 4:20:36 4:20:36:12 4:20:36.1234567

7

Date/time Types

Dates and times can be coded as literals ? Surround with single quotes (`')

If a time is not specified with a date, it defaults to 12AM

If a date is not specified with a time, it defaults to 1/1/1900

The two-digit year cutoff is 50 ? 12 is 2012 ? 75 is 1975

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download