Lesson 2

smallmoney, monetary or currency values from ___

-214,748.3648 to 214,748.3647

money, monetary or currency values from ___

-922,337,203,685,477.508 to 922,337, 203,685,477.5807

char [(n)] , the value of n must be between ___

1 and 8,000, making the storage size n bytes.

if your columns store 8 bits or fewer, the columns will be stored as ___

1 byte

uniqueidentifier (UUID) ???

16-byte GUID.

if your columns contain 9 to 16 bits, the columns will be stored as ___

2 bytes

how many bytes datetime2 data type requires ___

4 bytes of storage

how many bytes datetime data type uses ___

8 bytes of storage

LOB ?

A value with a size greater than the default threshold, that value will be stored external to the row and identified as a large object.

MAX specifier ?

A value with a size identified up to a certain threshold (the default is 8,000) is stored inline in the row.

Cast is compliance with ___

ANSI standards, which allow you to import or export to other database management systems.

binary ???

Binary data with fixed length.

varbinary ???

Binary data with variable length.

Regular data types include those identified with ___

CHAR and VARCHAR

syntax of the convert function ???

CONVERT ( data_type [ ( length ) ], expression [,style ] )

char ???

Character data type with fixed length.

varchar ???

Character data type with variable length.

datetimeoffset* ???

Defines a date that is combined with a time of day that has time-zone awareness and is based on a 24-hour clock. Range: 0001-01-01 through 9999-12-31 ... 00:00:00 through 23:59:59.9999999. Accuracy: 100 nanoseconds.

datetime2* ???

Defines a date that is combined with a time of day that is based on a 24-hour clock. Range: 0001-01-01 through 9999-12-31. Range: 00:00:00 through 23:59:59.9999999. Accuracy: 100 nanoseconds.

smalldatetime ???

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00), meaning there are no fractional seconds. Range: 1900-01-01 through 2079-06-06 Accuracy: one minute.

date* ???

Defines a date. Range: 0001-01-01 through 9999-12-31. Accuracy: one day.

time* ???

Defines a time of day. This time is without time-zone awareness and is based on a 24-hour clock. Range: 00:00:00.0000000 through 23:59:59.9999999. Accuracy: 100 nanoseconds.

INSERT INTO planets (name, diameter) VALUES ('earth', 10000); if SQL Server didn't support implicit conversion, the following syntax would be needed:

INSERT INTO planets (name, diameter) VALUES ('earth', CAST (10000 as varchar(50)))

NVarchar ???

If you are supporting multiple languages, use this data type.

smallint ???

Integer data from -2^15 (-32,768) to 2^15-1 (32,767).

bigint ???

Integer data from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 8 bytes (9,223,372,036,854,775,807).

tinyint ???

Integer data from 0 to 255.

when you are expressing a Unicode character literal, it must have the letter ___

N (for National) prefixing the single quote. For example: N'This is how a Unicode character string literal looks'

Unicode data types are identified with ___

NCHAR and NVARCHAR

Examples of PascalCase are such names as ___

OrderDetails or CustomerAddresses

Scale(s) ???

Reflects the maximum number of decimal digits that can be stored to the right of the decimal point. This must be a value from 0 through p, but it can be specified only if precision is also specified. The default scale is 0.

Microsoft SQL Server supports only two character string types:

Regular and Unicode.

Steps to create your view:

Right-click the Views folder, then select New View.

SSMS definition :

SQL Server Management Studio

When you use a VAR element ___

SQL Server will preserve space in the row in which that element resides based on the column's defined size (and not on the actual number of characters in the character string itself ), plus an extra two bytes of data for offset data.

VARCHAR(25) ?

Specify that a string supports a maximum of only 25 characters

sql_variant ???

Stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant.

Varchar ???

This character-string data type is commonly used in databases where you are supporting English attributes.

CREATE VIEW vwCustomer AS SELECT CustomerId, Company Name, Phone FROM Customers

This creates a view called vwCustomer that will be stored as an object. Here, the data that is queried from the columns comes from the Customers table.

Boolean ???

This data type converts true and false string values to bit values, with true converted to 1 and false converted to 0.

ntext ??

This data type will be removed in future SQL releases; therefore, use nvarchar(max) instead.

image ???

This data type will be removed in future SQL releases; therefore, use varbinary(max) instead.

text ???

This data type will be removed in future SQL releases; therefore, use varchar(max) instead.

Add Table dialog box ???

This dialog box allows you to do: • specify the table to be used as the primary source... • To use another existing view, click the Views tab... • generate records from a function (Functions tab). • If you want to use more than one source, you can click each of the different tabs to find the table, view, or function you wish to add to your query. • Once you have selected the desired source(s) (click the Add button)• Once you have selected and added all your desired sources (Close button)

Money ???

This numeric data type is used in places where you want money or currency involved in your database;

CONVERT(nvarchar(10), OrderDate, 101) ???

This will convert the OrderDate, which is a DateTime data type to nvarchar value. The 101 style represents USA date with century. mm/dd/yyyy.

numeric & decimal ???

Transact-SQL data types that have a fixed precision and scale. Valid values range from -10^38+1 through 10^38-1.

Money and smallmoney are ___

Transact-SQL data types you would use to represent monetary or currency values. Both data types are accurate to 1/10,000th of the monetary units they represent.

nchar ???

Unicode Character data type with fixed length.

nvarchar ???

Unicode Character data type with variable length.

A view is ___

a virtual table consisting of different columns from one or more tables; ( or ) an object that obtains its data from one or more tables.

datetime2 is the data type to use if you will be storing values ___

between January 1, 1900, and June 6, 2079, that are accurate to only 1 minute.

you should use the datetime data type if you will be storing values ___

between the dates of January 1, 1753, and December 31, 9999, that are accurate to 3.33 milliseconds,

The Boolean data type is also known as the ___

bit data type.

Differences between regular and Unicode are the ___

bytes of storage used for each.

syntax of the cast function ???

cast(source-value AS destination-type)

data set char is identified as ___

char [(n)] and is a fixed-length, non-Unicode character (regular character) with a length of n bytes. The value of n must be between 1 and 8,000, making the storage size n bytes.

cast(count AS float) ???

convert the count variable to a float

Set the Default Value of the DateCreated column to (getdate()); this will insert the ___

current date within each new record for that specific field.

When two expressions (mathematical functions or comparison functions) have different data types, rules for data-type precedence specify that the ___

data type with lower precedence is converted to the data type with higher precedence.

indexing plays a role in query time and an even greater role in ___

database performance improvements.

The date and time data types include ___

date, datetime, datetime2, datetimeoffset, smalldatetime, and time.

The syntax for decimal data type is expressed as ___

decimal[(p[,s])]

An example of executing a stored procedure:

exec usp_displayallusers

if you multiply an item's cost (represented as a float) with the number of items (represented as an integer), the answer will be ___

expressed as a float.

Cast and Convert functions ???

force a conversion

When a conversion (convert values from one data type to another) is done automatically it is called an ___

implicit conversion.

Exact numerics include ___

int, bigint, bit, decimal, numeric, money, and smallmoney

bit ???

is a Transact-SQL integer data type that takes a value of 1, 0, or NULL

Creating tables within SSMS is simple because SSMS ___

is an easy-to-use graphical interface.

Precision (p) ???

is the maximum total number of decimal digits that can be stored, both to the left and the right of the decimal point. This value must be a minimum of 1 and a maximum of 38. The default precision number is 18.

int ??

is the primary integer (whole number) data type. Integer data from -2^31(-2,147,483,648) to 2^31-1 (2,147,483,647).

Examples of camelCase are names like ___

myAddress and vendorTerms

The syntax for numeric data type is expressed as ___

numeric[(p[,s])]

A regular character uses ___

one byte of storage for each character, which allows you to define one of 256 (8 bits are in a byte, and 2^8 = 256) possible characters, accommodating English and some European languages.

Database views are designed to create a virtual table that is representative of ___

one or more tables in an alternative way.

Adding indexes to the schema can actually increase the overhead of your database due to the ___

ongoing maintenance of these indexes.

A stored procedure is a ___

previously written SQL statement that has been "stored" or saved into a database.

The purpose of a table is to ___

provide a structure for storing data within a relational database.

A view is stored in a database as a ___

query object

You should also avoid using spaces because they add complications that make it necessary for you to use ___

quotes

The float numeric data type is commonly used in the ___

scientific community and considered an approximate-number data type. This means that not all values within the data-type range will be represented exactly. In addition, depending on which type of float is used, a 4-byte float supports precision up to 7 digits and an 8-byte float supports precision up to 15 digits.

A view is also a mechanism to ___

simplify query execution.

A regular character literal is always expressed with ___

single quotes. For example: 'This is how a regular character string literal looks'

The integer (int) numeric data type is used to ___

store mathematical computations and is employed when you do not require a decimal point output.

One way to save time when running the same query over and over again is to create a ___

stored procedure that you can then execute from within the database's command environment.

Once you have defined a view, you can reference it as you would any other ___

table in a database.

"exec"

tells SQL Server to execute the code in the stored procedure.

TRUE and FALSE string values can be converted ___

to bit values. Specifically, TRUE is converted to 1 and FALSE is converted to 0.

A Unicode character uses ___

two bytes of storage per character so that you can represent one of 65,536 (16 bits are in 2 bytes, and 2^16 = 65,536) characters. The additional space allows Unicode to store characters from just about any language, including Chinese, Japanese, Arabic, and so on.

Views are referred to as ___

underlying tables.

Datetime ???

used to store date and time data in many different formats.

Complex queries can be stored in the form of a view and data from the view can then be mined ___

using simple query statements.

Non-Unicode data type varchar[(n|max)] is a ___

variable-length data set that can consist of 1 to 8,000 characters.

For example, although a DateTime value is represented as a float, ___

you may not implicitly convert to a float because it is meant to be a date and/or time.

how can you create tables using ANSI SQL syntax?

you will use the create table statement to accomplish this task.

Some common examples of views include the following:

• A subset of rows or columns of a base table• A union of two or more tables • A join of two or more tables• A statistical summary of base tables• A subset of another view or some combination of views and base tables

There are two methods for creating a view:

• By using SSMS • By writing a Transact-SQL statement

Views ensure the security of data by restricting access to the following data:

• Specific rows of tables• Specific columns of tables • Specific rows and columns of tables• Rows obtained by using joins • Statistical summaries of data in given tables • Subsets of another view or subsets of views and tables

Reasons to provide a view instead of enabling users to access the underlying tables in your database:

• Views allow you to limit the type of data users can access. You can grant view permissions in designated tables, and you can also choose to deny permissions for certain information. • Views reduce complexity for end users so they don't have to learn how to write complex SQL queries. Instead, you can write those queries on their behalf and hide them in a view.