d
-
- 1 / 48
-
Flashcards
Which Categories for Datatypes exist for SQL Server?
- Exact numerics
- Approximate numerics
- Date and time
- Character strings
- Unicode character strings
- Binary strings
- Other data types
Exact numerics
- bigint
- bit
- decimal
- money
- numeric
- smallint
- smallmoney
- tinyint
Approximate numerics
- float
- real
Date and Time
- date
- datetime2
- datetime
- datetimeoffset
- smalldatetime
- time
Character strings
- char
- varchar
- text
Unicode character strings
- nchar
- nvarchar
- ntext
Binary strings
- binary
- varbinary
- image
Other data types
- cursor
- hierarchyid
- sql_variant
- Spatial Geometry Types
- table
- rowversion
- uniqueidentifier
- xml
- Spatial Geography Types
bigint
Category: Exact numerics
Storage: 8 bytes
Range: -2^63 to 2^63-1
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
9trio
int
Category: Exact numerics
Storage: 4 bytes
Range: -2^31 to 2^31-1
-2,147,483,648 to 2,147,483,647
2mia
smallint
Category: Exact numerics
Storage: 2 bytes
Range: -2^15 to 2^15-1
-32,768 to 32,767
tinyint
Category: Exact numerics
Storage: 1 byte
Range: 0 - 255
bit
Category: Exact numerics
Storage: 1bit (per 8 bit 1 bytes)
Range: 0/1
decimal / numeric
Category: Exact numerics
Storage: 5-17bytes
Range: 10^p (p max = 38)
-----
decimal(p,s)
p =precision (1-38) default 18
both sites of decimal
s = scale (0-p) default p
right side of decimal
money
Category: Exact numerics
Storage: 8 bytes
Range:
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
900bio
represents currency values
smallmoney
Category: Exact numerics
Storage:4 bytes
Range:
- 214,748.3648 to 214,748.3647
represents currency values
real / float(24)
Category: Approximate numerics
Storage:4 - 8bytes
Range:
-1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 depending on n
Depending on n
real = 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 (4Bytes)
float(n)
n =
1-24 = 7 digits (4Bytes)
25-53 = 15 digits (8bytes)
date
Category: Date and Time
Range: 0001-01-01 to 9999-12-31
Default literal: YYY-MM-DD
Default Value: 1900-01-01
Storage: 3Bytes
datetime
Category: Date and Time
Range: 1753-01-01 to 9999-12-31
Default Value: 1900-01-01
Storage: 8Bytes
Default Value: 1900-01-01 00:00:00
datetime2
Category: Date and Time
Date Range: 0001-01-01 to 9999-12-31
Time Range: 00:00:00 to 23:59:59.9999999
Default literal: YYYY-MM-DD hh:mm:ss[.fractional seconds]
Default Value: 1900-01-01 00:00:00
Storage: 6 bytes for precision less than 3; 7 bytes for precisions 3 and 4. All other precisions 8bytes
Accuracy: 100 nanoseconds default precision = 7 digits
datetimeoffset
Category: Date and Time
Date Range: 0001-01-01 to 9999-12-31
Time Range: 00:00:00 to 23:59:59.9999999
Timezone offset range: -14:00 to +14:00
Default literal: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
Default Value: 1900-01-01 00:00:00
Storage: 10 bytes
Accuracy: 100 nanoseconds
datetimeoffset(fsp)
smalldatetime
Category: Date and Time
Date Range: 1900-01-01 to 2079-06-06
Time Range: 00:00:00 to 23:59:59
Timezone offset range: -14:00 to +14:00
Default Value: 1900-01-01 00:00:00
Storage: 4 bytes
Accuracy: One Minute
datetimeoffset(fsp)
time
Category: Date and Time
Time Range: 00:00:00 to 23:59:59.9999999
Default literal: hh:mm:ss[.nnnnnnn]
Default Value: 00:00:00
Storage: 5 bytes fixed
Accuracy: 100 nanoseconds
time(fsp)
Create a Schema named Production and Authorize user dbo
CREATE SCHEMA Production
AUTHORIZE dbo
How do you make sql server storing the value of the following computed column:
YearOfBirth AS DATEPART(year, DateOfBirth)
YearOfBirth AS DATEPART(year, DateOfBirth) PERSISTED
Types of Data Integrity
- Domain Integrity (Column)
- Entity Integrity (Row)
- Referential Integrity (Cross-Table)
How much data does a page store?
8kb
OLTP
Online Transaction Processing
- Very efficient for INSERT, UPDATE, DELETE
- Reading becomes less efficient over time
OLAP
Online Analytical Processing
- Data Warehouse
- Data Inserts are not efficient
CRUD
CREATE
READ
UPDATE
DELETE
ACID
Atomicity = all or nothing
Consistency = database goes from one consistent(valid) state to another
Isolation = transaction should happen independently from others at the same time
Durability = once commited it remains stable in the system
DDL
Data Definition Language
Statements that are used to create and modify the databases, tables and other objects.
(i.e: CREATE, ALTER, DROP, TRUNCATE)
DML
Data Modification Language
Statements that work with the Database Tables
(i.e: SELECT, INSERT, UPDATE, DELETE)
Wildcard Character: %
Any string of zero or more characters
Wildcard Character: _
Any single character
Wildcard Character: []
Any single character within the specified range
Wildcard Character: [^]
Any single character not within the specified range
T-SQL Command: INTERSECT
Returns distinct rows for values that are found in each table
T-SQL Command: EXCEPT
returns distinct rows for values that are not found in each table
T-SQL Command: FETCH
Limits the number of rows that are returned from the resultset
ORDER BY is required
