Terminology Flashcards
SQL
Structured Query Language
T-SQL
Transact-Structures Query Language
Record
A set of fields in a row
Field
One or more characters of related information of the same data type
Schema
A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. A schema is associated with a username which is known as the schema owner, who is the owner of the logically related database objects.
View
A View is a virtual table whose contents are defined by a query.
Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database.
The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
Collation
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data.
SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications in different parts of the world.
How many data types?
30
char(n)
Fixed width character string
Max= 8000 characters
defined width
varchar(n)
Variable width character string
Max=8000 characters
2 bytes + # of chars
varchar(max)
Variable width character string
Max=1,073,741,824 characters
2 bytes + # of chars
text
Variable width character string
Max=2GB of text data
4 bytes + # of chars
nchar
Fixed width Unicode string
Max=4000 characters
defined width x 2
nvarchar
Variable width Unicode string
Max=4000 characters
nvarchar(max)
Variable width Unicode string
Max=536,870,912 characters
ntext
Variable width Unicode text
Max=2GB text data
binary(n)
Fixed width binary string
Max=8000 bytes
varbinary
Variable width binary string
Max=8000 bytes
varbinary(max)
Variable width binary string
Max=2GB
image
Variable width binary string
Max=2GB
bit
Integer
0, 1, Null
tinyint
Whole number
0 - 255
1 byte
smallint
Whole number
-32,768 to 32,767
2 bytes
int
Whole number
-2,147,483,648 to 2,147,483,647
4 bytes
bigint
Whole number
-9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
8 bytes
decimal(p,s)
Fixed precision and scale numbers
p = max digits that can be stored (1 to 38) default=18
s = max digits on the right side of decimal (0 to p) default=0
5-17 bytes
numeric(p,s)
Fixed precision and scale numbers
p = max digits that can be stored (1 to 38) default=18
s = max digits on the right side of decimal (0 to p) default=0
5-17 bytes
money
Monetary data
-214,748.3648 to 214,748.3647
8 bytes
float(n)
Floating precision
n = indicates 4 or 8 bytes
4 or 8 bytes
real
Floating precision
-3.40E + 38 to 3.40E + 38
4 bytes
datetime
January 1, 1753 -> December 31, 9999
accuracy = 3.33 milliseconds
8 bytes
datetime2
January 1, 0001 -> December 31, 9999
accuracy = 100 nanoseconds
6-8 bytes
smalldatetime
January 1, 1900 -> June 6, 2079
accuracy = 1 minute
4 bytes
date
January 1, 0001 -> December 31, 9999
3 bytes
time
accuracy 100 nanoseconds
3-5 bytes
datetimeoffset
Same of datetime2 but with time zone offset
8-10 bytes
timestamp
Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable
sql_variant
Stores 8000 bytes of data
except text, ntext, timestamp
uniqueidentifier
Stores globally unique identifier (GUID)
xml
Stores formatted data (Extensible Markup Language)
Max = 2GB
Truncate Table
Deletes the data inside a table, but not the table itself
TRUNCATE TABLE [table]
Add Constraint
Create a constraint AFTER a table is already created
ALTER TABLE [table]
ADD CONSTRAINT [pk_constraint] PRIMARY KEY ([columns])
