Need help with the SQL Server Database (How to work with data types)

Heidi21

SQLServer2019Chapter8MruachHowtoworkwithdatatypesslides1.pptx

Home >Computer Science homework help >Need help with the SQL Server Database (How to work with data types)

Chapter 8

How to work with data types

Murach's SQL Server 2019

C8, Slide 1

Objectives

Applied

Code queries that use the data conversion functions to work with the data types presented in this chapter.

Knowledge

Describe the data that can be stored in any of the string, numeric, date/time, and large value data types.

Describe how data is stored in the four string data types when you use the default collation.

Describe the differences between implicit and explicit data type conversion.

Murach's SQL Server 2019

C8, Slide 2

SQL Server data type categories

String

Numeric

Temporal (date/time)

Other

Murach's SQL Server 2019

C8, Slide 3

ANSI-standard data types and SQL Server equivalents (part 1)

Synonym for ANSI-standard SQL Server data type used data type

binary varying varbinary

char varying varchar character varying

character char

dec decimal

double precision float

float real or float

integer int

Murach's SQL Server 2019

C8, Slide 4

ANSI-standard data types and SQL Server equivalents (part 2)

Synonym for ANSI-standard SQL Server data type used data type

national char nchar national character

national char varying nvarchar national character varying

national text ntext

timestamp rowversion

Murach's SQL Server 2019

C8, Slide 5

The integer data types

Type Bytes

bigint 8

int 4

smallint 2

tinyint 1

bit 1

Murach's SQL Server 2019

C8, Slide 6

The decimal data types

Type Bytes

decimal[(p[,s])] 5-17

numeric[(p[,s])] 5-17

money 8

smallmoney 4

Murach's SQL Server 2019

C8, Slide 7

The real data types

Type Bytes

float[(n)] 4 or 8

real 4

Murach's SQL Server 2019

C8, Slide 8

Terms to know for numeric data types

Precision

Scale

Exact numeric data types

Floating-point number

Significant digits

Murach's SQL Server 2019

C8, Slide 9

String data types for storing standard characters

Type Bytes

char[(n)] n

varchar[(n)]

String data types for storing Unicode characters

Type Bytes

nchar(n) 2×n

nvarchar(n)

Murach's SQL Server 2019

C8, Slide 10

The string data types with the default collation

Type Original value Value stored Bytes used

CHAR(2) 'CA' 'CA' 2

CHAR(10) 'CA' 'CA ' 10

VARCHAR(20) 'CA' 'CA' 4 (2 + 2)

VARCHAR(20) 'New York' 'New York' 10 (8 + 2)

NCHAR(2) N'CA' N'CA' 4

NCHAR(10) N'CA' N'CA ' 20

NVARCHAR(20) N'CA' N'CA' 6 (4 + 2)

NVARCHAR(20) N'New York' N'New York' 18 (16 + 2)

Murach's SQL Server 2019

C8, Slide 11

Terms to know for string data types

Unicode characters

Fixed-length strings

Variable-length strings

Murach's SQL Server 2019

C8, Slide 12

Date/time data types prior to SQL Server 2008

Type Bytes

datetime 8

smalldatetime 4

Date/time data types for SQL Server 2008 and later

Type Bytes

date 3

time(n) 3-5

datetime2(n) 6-8

datetimeoffset(n) 8-10

Murach's SQL Server 2019

C8, Slide 13

Common date formats

Format Example

yyyy-mm-dd 2020-04-30

mm/dd/yyyy 4/30/2020

mm-dd-yy 4-30-20

Month dd, yyyy April 30, 2020

Mon dd, yy Apr 30, 20

dd Mon yy 30 Apr 20

Common time formats

Format Example

hh:mi 16:20

hh:mi am/pm 4:20 pm

hh:mi:ss 4:20:36

hh:mi:ss:mmm 4:20:36:12

hh:mi:ss.nnnnnnn 4:20:36.1234567

Murach's SQL Server 2019

C8, Slide 14

How to use date/time literals

To code a date/time literal, enclose the date/time value in single quotes.

If you don’t specify a time in a date/time value, the time defaults to 12:00 a.m.

If you don’t specify a date in a date/time value, the date defaults to January 1, 1900.

By default, the years 00 to 49 are interpreted as 2000 to 2049 and the years 50 through 99 are interpreted as 1950 through 1999.

You can specify a time using either a 12-hour or a 24-hour clock.

For a 12-hour clock, am is the default.

Murach's SQL Server 2019

C8, Slide 15

The large value data types for SQL Server 2005 and later

varchar(max)

nvarchar(max)

varbinary(max)

How the large value data types map to the old large object types

SQL Server 2005 and later Prior to 2005

varchar(max) text

nvarchar(max) ntext

varbinary(max) image

Murach's SQL Server 2019

C8, Slide 16

Order of precedence for common data types, from highest to lowest

Category Data type

Date/time datetime2

date

time

Numeric float

real

decimal

money

smallmoney

int

smallint

tinyint

bit

String nvarchar

nchar

varchar

char

Murach's SQL Server 2019

C8, Slide 17

Conversions that can’t be done implicitly

From data type To data type

char, varchar, nchar, nvarchar money, smallmoney

money, smallmoney char, varchar, nchar, nvarchar

Expressions that use implicit conversion

InvoiceTotal * .0775

-- InvoiceTotal (money) converted to decimal

PaymentTotal – 100

-- Numeric literal converted to money

PaymentDate = '2020-04-05'

-- Date literal converted to date value

Murach's SQL Server 2019

C8, Slide 18

Terms to know for data conversion

Implicit conversion

Explicit conversion

Murach's SQL Server 2019

C8, Slide 19

The syntax of the CAST function

CAST(expression AS data_type)

A SELECT statement that uses the CAST function

SELECT InvoiceDate, InvoiceTotal,

CAST(InvoiceDate AS varchar) AS varcharDate,

CAST(InvoiceTotal AS int) AS integerTotal,

CAST(InvoiceTotal AS varchar) AS varcharTotal

FROM Invoices;

Murach's SQL Server 2019

C8, Slide 20

How to convert data when performing integer division

Operation Result

50/100 0

50/CAST(100 AS decimal(3)) .500000

Murach's SQL Server 2019

C8, Slide 21

The syntax of the CONVERT function

CONVERT(data_type, expression [, style])

Convert and format dates

SELECT CONVERT(varchar, InvoiceDate) AS varcharDate,

CONVERT(varchar, InvoiceDate, 1) AS varcharDate_1,

CONVERT(varchar, InvoiceDate, 107) AS varcharDate_107,

CONVERT(varchar, InvoiceTotal) AS varcharTotal,

CONVERT(varchar, InvoiceTotal, 1) AS varcharTotal_1

FROM Invoices;

Murach's SQL Server 2019

C8, Slide 22

Style codes for converting date/time data to character data

Code Output format

0 or 100 Mon dd yyyy hh:miAM/PM

1 or 101 mm/dd/yy or mm/dd/yyyy

7 or 107 Mon dd, yy or Mon dd, yyyy

8 or 108 hh:mi:ss

10 or 110 mm-dd-yy or mm-dd-yyyy

12 or 112 yymmdd or yyyymmdd

14 or 114 hh:mi:ss:mmm (24-hour clock)

Murach's SQL Server 2019

C8, Slide 23

Style codes for converting real data to character data

Code Output

0 (default) 6 digits maximum

1 8 digits; must use scientific notation

2 16 digits; must use scientific notation

Murach's SQL Server 2019

C8, Slide 24

Style codes for converting money data to character data

Code Output

0 (default) 2 digits to the right of the decimal point; no commas to the left

1 2 digits to the right of the decimal point; commas to the left

2 4 digits to the right of the decimal point; no commas to the left

Murach's SQL Server 2019

C8, Slide 25

The syntax of the TRY_CONVERT function

TRY_CONVERT(data_type, expression [, style ])

Convert and format dates

SELECT TRY_CONVERT(varchar, InvoiceDate) AS varcharDate,

TRY_CONVERT(varchar, InvoiceDate, 1) AS varcharDate_1,

TRY_CONVERT(varchar, InvoiceDate, 107)

AS varcharDate_107,

TRY_CONVERT(varchar, InvoiceTotal) AS varcharTotal,

TRY_CONVERT(varchar, InvoiceTotal, 1)

AS varcharTotal_1,

TRY_CONVERT(date, 'Feb 29 2019') AS invalidDate

FROM Invoices;

Murach's SQL Server 2019

C8, Slide 26

Other data conversion functions

STR(float[,length[,decimal]])

CHAR(integer)

ASCII(string)

NCHAR(integer)

UNICODE(string)

Murach's SQL Server 2019

C8, Slide 27

Examples that use the data conversion functions

Function Result

STR(1234.5678, 7, 1) 1234.6

CHAR(79) O

ASCII('Orange') 79

NCHAR(332) O

UNICODE(N'Or') 332

Murach's SQL Server 2019

C8, Slide 28

ASCII codes for common control characters

Control character Value

Tab Char(9)

Line feed Char(10)

Carriage return Char(13)

Use the CHAR function to format output

SELECT VendorName + CHAR(13) + CHAR(10)

+ VendorAddress1 + CHAR(13) + CHAR(10)

+ VendorCity + ', ' + VendorState + ' ‘ + VendorZipCode

FROM Vendors

WHERE VendorID = 1;

US Postal Service

Attn: Supt. Window Services

Madison, WI 53707

Murach's SQL Server 2019

C8, Slide 29