Index
Symbols
@@ functions, 231
@@Identity, 218
@@RowCount, 217
@@TranCount function, 218
explained, 216
% (percent) sign, 104
A
accounts, dbo, 295
action queries
DELETE statement, 146
SELECT INTO statement, 145
TRUNCATE statement, 147
Activity Monitor, 25
Actual Execution Plan button, 361
Add Features to an Existing Installation option, 379
Add Objects dialog box, 306-307
Add Table dialog box, 70-71, 76, 180, 192-193
administration
function permissions, 315
assigning permissions to particular objects, 302-305
assigning permissions to users or roles, 305-309
stored procedure permissions, 314
Windows Administrators group, 296
advanced options (SQL Server), 330-331
Advanced page (Query Options dialog), 365
AdventureWorks2014 database, installing, 27-29
Agent (SQL Server), 17
COUNT_BIG, 112
explained, 110
MAX, 115
MIN, 114
aggregating data with views. See views
aliases, table aliases, 92
ALTER permissions, 309
ALTER VIEW statement, 187
analyzing
Estimated Execution Plan, 358-359
queries, 374
ANSI page (Query Options dialog), 366
Application role, 293
assigning
function permissions, 315
object permissions
for particular objects, 302-305
stored procedure permissions, 314
Attach command, 40
Attach Databases dialog box, 40-41
attaching to existing database, 40-41
audit logs, inserting data into
authentication
logins
granting database access to logins, 284
ownership, 295
roles
user-defined database roles, 293-294
Autogrowth option (New Database dialog box), 37
@AverageFreight variable, 209
B
Back Up Database dialog box, 336-338
backup devices, 23
BEGIN...END construct, 202
BEGIN TRANSACTION statement, 239
Bigint data type, 47
Binary data type, 47
Bit data type, 47
Books Online, 149
Browse for Objects dialog box, 288-292, 308, 312-313
bulkadmin, 286
Bulk Insert Administrators (bulkadmin), 286
bulk logged recovery, 336, 352
Business Intelligence Edition (SQL Server), 15
C
calculating summary statistics, 109-110
cascading deletes, 85
Change Autogrowth dialog box, 37-38
changing sort direction, 102
Char data type, 47
Check Constraints dialog box, 54-55
Choose Name dialog box, 64, 73
choosing recovery model, 336
clauses. See also keywords; statements
FROM, 92
changing sort direction, 102
syntax, 101
WHERE, 119
NULL keyword, 100
Clear Trace Window command, 372
Client Statistics tab (Management Studio), 359-361
COALESCE function, 172
column-level permissions, 315-316
Column Permissions dialog box, 316-317
columns, 45
column-level permissions, 315-316
constraints. See constraints
data types available, 46
explained, 5
finding maximum values in, 115
finding minimum values in, 114
identity specifications, 56
maximum values, 115
selecting, 90
Tables and Columns specifications, 79-81
user-defined data types, 58-60
COMMIT TRANSACTION statement, 239
computed columns, creating, 57-58
configuring SQL Server
connecting to database servers, 25-26
connections options (SQL Server), 328-329
Connect to Server dialog box, 25-26, 342
constraints, 50
default, 53
Not Null, 54
rules, 55
unique, 56
controlling flow of stored procedures
BEGIN...END construct, 202
overview, 200
RETURN statement, 203-206, 212
converting strings
to lowercase, 160
to uppercase, 161
COUNT_BIG function, 112
@Country variable, 201
CREATE VIEW statement, 185-187
cursors, 245
fields, 46
foreign key relationships, 79
joins
full joins, 129
logins
granting database access to logins, 284
stored procedures
with T-SQL, 196
tables
adding to database diagrams, 76
SELECT INTO statement, 145
syntax, 266
views, 179
with Management Studio Query Builder, 179-185
credentials, 23
Credentials node (Management Studio), 23
Cursor data type, 47
@Cursor variable, 249
cursors
defining, 245
looping through records with, 246-249
populating, 246
D
data
deleting
DELETE statement, 146
TRUNCATE statement, 147
inserting
Database Creators (dbcreator), 286
database diagrams
adding tables, 76
definition of term, 6
purpose of, 10
relationships versus, 77
removing tables, 77
Database Engine, 387
Database Engine Configuration step (SQL Server installation), 382
Database Engine Tuning Advisor, 18-19, 341-344
accessing, 341
purpose of, 352
database maintenance plans, 344-351
Database Role – New dialog box, 294
Database Role Properties dialog box, 290-291
AdventureWorks2014, installing, 27-29
attaching to existing database, 40-41
authentication
ownership, 295
database diagrams
adding tables, 76
definition of term, 6
purpose of, 10
relationships versus, 77
removing tables, 77
Database Engine Tuning Advisor, 341-344
accessing, 341
purpose of, 352
database maintenance plans, 344-351
definition of term, 5
design master, 24
logins
granting database access to logins, 284
master database, 20
MSDB, 21
roles
user-defined database roles, 293-294
settings (SQL Server), 329-330
tables. See tables
transaction log, 39
views
customizing user data with, 188
definition of term, 7
indexed views, 7
security, 188
database servers, connecting to, 25-26
Databases node (Management Studio), 19-21
master database, 20
MSDB database, 21
TempDB database, 21
Database User dialog box, 305-306, 308-309
Database User – New dialog box, 300-301
Data Definition Language (DDL), 24
Data Directories tab (SQL Server installation), 382-384
data types
fields, 46
Date data type, 47
DATENAME function, 167
DATEPART function, 98, 166, 174
DateTime2 data type, 47
DateTime data type, 47
date/time functions, 96-98, 163
DATEADD, 168
DATENAME, 167
DAY, 164
MONTH, 163
YEAR, 165
DateTimeOffset data type, 47
DAY function, 164
db_accessadmin, 290
db_backupoperator, 290
dbcreator, 286
db_datareader, 290
db_datawriter, 290
db_ddladmin, 290
db_denydatareader, 290
db_denydatawriter, 290
dbo account, 295
db_owner, 290
db_securityadmin, 290
DDL (Data Definition Language), 24
Decimal data type, 48
DECLARE CURSOR statement, 245
declaring. See creating
default constraints, 53
defining. See creating
DELETE permissions, 310
DeletePerson trigger, 272
Delete rule (relationships), 84-85
DELETE statement
compared to TRUNCATE, 147
explained, 146
deleting
foreign key relationships, 79
spaces from strings
leading spaces, 162
trailing spaces, 163
table data
DELETE statement, 146
stored procedures, 237
TRUNCATE statement, 147
tables from database diagrams, 77
descriptions for foreign key relationships, 81
designing stored procedures
with T-SQL, 196
design master, 24
diagram pane (View Builder), 184
diagrams, database diagrams
adding tables, 76
definition of term, 6
purpose of, 10
relationships versus, 77
removing tables, 77
dialog boxes
Add Table, 70-71, 76, 180, 192-193
Browse for Objects, 288-292, 308, 312-313
Database Role – New, 294
Database Role Properties, 290-291
Database User, 305-306, 308-309
Database User – New, 300-301
Edit Filter, 371
Foreign Key Relationships, 73-76, 84-86
Job Properties, 351
Locate Folder, 34
New Database
defining database options, 36-39
New Job Schedule, 346
New User-defined Data Type, 58-59
Advanced page, 365
ANSI page, 366
General page, 365
Grid page, 366
Text page, 367
Relationships, 75
Save, 74
Select Backup Device, 28, 339-340
Select Login, 301
Select Server Login or Role, 288-289
Select User or Group, 282
Select Users or Roles, 303, 315-316
Server Properties
Database Settings page, 329-330
Server Role Properties, 288
SQL Server Login Properties, 285
differential database backups, 335
diskadmin, 286
Disk Administrators (diskadmin), 286
Display Estimated Execution Plan button, 358
displaying Estimated Execution Plan, 358-359
DISTINCT keyword, 105-107, 119
documents (XML), returning data as, 107-109
DROP statement, 147
E
Edit Filter dialog box, 371
editing. See modifying
eliminating xx row(s) affected message, 215-216
EmpGetByTitle function, 257-258
Enterprise Edition (SQL Server), 16
error handling
explained, 227
returning success/failure information, 229-231
error messages, referential integrity, 83
Estimated Execution Plan, 358-359
EXEC permissions, 312
Execute button, 355
Execute Procedure dialog box, 197, 222
Execute Stored Procedure command, 197
executing
queries in SQL Server Management Studio, 355-358
Execution Plan tab (Management Studio), 359-362
explicit transactions, 237-238
Express Edition (SQL Server), 14, 30
expressions
first non-null expression, returning, 172
rounding to specified length, 150-151
extracting
characters from string
left of string, 152
parts of dates, 166
substrings, 159
F
failure information, returning from stored procedures, 229-231
FC (Fibre Channel), 322
fields, 45
column-level permissions, 315-316
constraints. See constraints
data types available, 46
explained, 5
finding maximum values in, 115
finding minimum values in, 114
identity specifications, 56
maximum values, 115
selecting, 90
Tables and Columns specifications, 79-81
user-defined data types, 58-60
file groups, 39
files
AdventureWorks2014 sample files, 27-29
file groups, 39
file types, 36
log files, 25
explained, 42
inserting data with triggers, 266-274
transaction log, 39
first non-null expression, returning, 172
Float data type, 48
flow of stored procedures, controlling
BEGIN...END construct, 202
overview, 200
RETURN statement, 203-206, 212
foreign key relationships, 51, 65
adding, 79
deleting, 79
naming, 81
in one-to-many relationships, 68
Tables and Columns specifications, 79-81
Foreign Key Relationships dialog box, 73-76
FROM clause, 92
full database backups, 335
full joins, 129
function permissions, 315
functions, 149
@@ functions, 231
explained, 216
@@Identity, 218
@@RowCount, 217
@@TranCount function, 218
COALESCE, 172
COUNT_BIG, 112
DATEADD, 168
DATENAME, 167
DAY, 164
GetAncestor, 259
inline table-valued functions, 257-258
LEFT, 152
LEN, 153
LOWER, 160
LTRIM, 162
MAX, 115
MIN, 114
MONTH, 163
multi-statement table-valued functions, 258-260
NULLIF, 171
permissions, 315
REPLACE, 154
REPLICATE, 156
REVERSE, 155
RTRIM, 163
advantages/disadvantages, 255-256
SPACE, 158
SUBSTRING, 159
UPPER, 161
user-defined functions, 9
YEAR, 165
G
General page (Query Options dialog), 365
Geography data type, 48
Geometry data type, 48
GetAncestor function, 259
GetTotalInventory function, 254-255
global variables
explained, 216
@@Identity, 218
@@RowCount, 217
@@TranCount, 218
granting database access to logins, 284
GRANT statement, 302
Grid page (Query Options dialog), 366
Grid pane (View Builder), 184
groups
file groups, 39
Windows Administrators group, 296
H
hardware, performance tuning
networks, 324
overview, 321
processors, 322
help, Books Online, 149
Hierarchyid data type, 48
I
@@Identity function, 218
identity increments, 56
identity seeds, 56
identity specifications, 56
Image data type, 48
implicit transactions, 237-238
implied permissions, 300
Include Actual Execution Plan button, 359
Include Client Statistics button, 359-361
Index Columns dialog box, 61-62
indexed views, 7
indexes, creating, 60-62, 362-364
Indexes/Keys dialog box, 61-63
Index Properties dialog box, 62-63
inherited/implied permissions, 300, 317
initial size of databases, 36
inline table-valued functions, 257-258
inner joins
explained, 126
INSERT and UPDATE Specification node (relationships), 84-86
INSERT permissions, 310
InsertPerson trigger, 267
installation
AdventureWorks2014 database, 27-29
Add Features to an Existing Installation, 379
Database Engine Configuration step, 382
New SQL Server Stand-alone Installation, 379-380
Ready to Install step, 382-384
Server Configuration step, 381-382
SQL Server Feature Installation step, 381
SQL Server Installation Center, 379-380
Int data type, 48
INTERSECT operator, 137
J-K
Job Properties dialog box, 351
jobs, 17
joining data. See joins; views
inner joins
explained, 126
explained, 127
left outer joins, 124
right outer joins, 125
purpose of, 126
junction tables, 70
keywords. See also clauses; statements
NULL, 100
L
leading spaces, removing from strings, 162
LEFT function, 152
LEN function, 153
length of strings, determining, 153
linked servers, 24
@Locale variable, 201
@LocalError variable, 239
@LocalRows variable, 239
Locate Backup File dialog box, 28, 339
Locate Database Files dialog box, 40-41
Locate Folder dialog box, 34
log files, 25
explained, 42
inserting data with triggers
transaction log, 39
logical names, 36
Login – New dialog box, 281-283
granting database access to logins, 284
Logins node (Management Studio), 22
looping through records with cursors, 247-249
@LoopText variable, 211
@LoopValue variable, 211
lowercase, converting strings to, 160
LOWER function, 160
LTRIM function, 162
M
maintaining databases
Database Engine Tuning Advisor, 341-344, 352
database maintenance plans, 344-351
Maintenance Plan Wizard, 344-351
Management node (Management Studio), 25
Management Studio, 19
master database, 20
MSDB database, 21
TempDB database, 21
Management node, 25
Query Builder
Security node
Credentials, 23
Logins, 22
Server Roles, 22
Server Objects node, 23
backup devices, 23
linked servers, 24
server triggers, 24
many-to-many relationships, 70, 87
master database, 20
MAX function, 115
maximum values, finding, 115
memory
importance of, 333
Messages tab (Management Studio), 358
MIN function, 114
minimum values, finding, 114
views
with Management Studio Query Builder, 184-185
Money data type, 48
monitoring performance. See performance monitoring
MONTH function, 163
Mount command, 377
MSDB database, 21
multi-statement table-valued functions, 258-260
@MyMessage parameter, 226
N
naming foreign key relationships, 81
NChar data type, 48
networks
performance tips, 324
SANs (Storage Area Network), 323
New Database command, 33
New Database dialog box
defining database options, 36-39
New Job Schedule dialog box, 346
New Query toolbar, 223
New SQL Server Stand-alone Installation option, 379-380
New Trigger command, 263
New User-defined Data Type dialog box, 58-59
NoDeleteActive trigger, 273-274
Not Null constraints, 54
NText data type, 48
NULLIF function, 171
NULL keyword, 100
nulls, 170
COALESCE function, 172
NULLIF function, 171
replacing values with, 171
Numeric data type, 48
numeric functions
numeric values, identifying, 149-150
NVarChar data type, 48
NVarChar(MAX) data type, 49
O
Object Explorer, 64
object permissions
assigning for particular objects, 302-305
assigning to users or roles, 305-309
explained, 299
one-to-many relationships, 68
one-to-one relationships, 68-69, 87
operators
INTERSECT, 137
changing sort direction, 102
syntax, 101
explained, 127
left outer joins, 124
right outer joins, 125
ownership, 295
P
parameters of stored procedures, 221
explained, 231
percent (%) sign, 104
overview, 355
queries
Estimated Execution Plan, 358-359
executing in SQL Server Management Studio, 355-358
analyzing trace output, 372-373
hardware
networks, 324
overview, 321
processors, 322
SQL Server
overview, 324
Perform a New Installation of SQL Server 2014 option, 379-380
permissions options (SQL Server), 331-332
permission statements, 302
permissions validation, 299
column-level permissions, 315-316
database users, adding, 300-302
definition of term, 278
function permissions, 315
inherited/implied permissions, 300, 317
object permissions
assigning for particular objects, 302-305
assigning to users or roles, 305-309
explained, 299
permission statements, 302
statement permissions, 299
stored procedure permissions, 314
plans, Estimated Execution Plan, 358-359
populating cursors, 246
primary key constraints, 51, 65
procedures, stored. See stored procedures
procEmployeeGetByTitleAndBirthDateOpt stored procedure, 224-225
procEmployeeGetByTitleAndBirthDate stored procedure, 221
procEmployeeGetYoungSalesReps stored procedure, 221
procEmployeesGetByJobTitleAndHireDate stored procedure, 244
procEmployeesGetByTitleAndBirthDateOutput stored procedure, 225-226
procEmployeesGetCursor stored procedure, 246
procEmployeesGetTemp stored procedure, 243-244
processadmin, 286
Process Administrators (processadmin), 286
procGetString stored procedures, 247-248
procOrderDetailAddHandleErrors2 stored procedure, 229
procOrderDetailAddHandleErrors3 stored procedures, 230-231
procOrderDetailAddOutput stored procedure, 234
procOrderDetailAdd stored procedure, 234
procOrderDetailAddTransaction stored procedure, 238
procSalesOrderDetailDelete stored procedure, 237
procSalesOrderDetailUpdate stored procedure, 235-236
procSalesOrderHeaderUpdate stored procedure, 235
analyzing trace output, 372-373
when to use, 374
properties, Server Properties dialog box
Database Settings page, 329-330
Public role, 292
Q
queries. See also clauses; statements
analyzing, 374
Estimated Execution Plan, 358-359
executing in SQL Server Management Studio, 355-358
joins
explained, 121
purpose of, 126
Advanced page, 365
ANSI page, 366
General page, 365
Grid page, 366
Text page, 367
Query Options command (Query menu), 364
Query Options dialog box, 364-367
Advanced page, 365
ANSI page, 366
Grid page, 366
Text page, 367
R
RAID (Redundant Array of Independent Disks), 323
RAM (random access memory)
importance of, 333
Ready to Install step (SQL Server installation), 382-384
Real data type, 49
records
deleting
DELETE statement, 146
TRUNCATE statement, 147
Redundant Array of Independent Disks (RAID), 323
REFERENCES permissions, 310
refreshing tables list, 64
relationships, 67
database diagrams versus, 77
establishing referential integrity, 81-84
foreign key relationships
adding, 79
deleting, 79
naming, 81
Tables and Columns specifications, 79-81
one-to-many, 68
Relationships dialog box, 75
removing
foreign key relationships, 79
spaces from strings
leading spaces, 162
trailing spaces, 163
table data
DELETE statement, 146
stored procedures, 237
TRUNCATE statement, 147
tables from database diagrams, 77
REPLACE function, 154
replacing
characters in strings, 157
strings
REPLACE function, 154
REPLICATE function, 156
values with nulls, 171
replicas, 24
REPLICATE function, 156
Replication node (Management Studio), 24-25
Restore Database dialog box, 28, 338-341
results pane (View Builder), 184
RETURN statement, 203-206, 212
@return variable, 255
REVERSE function, 155
reversing strings, 155
permissions. See permissions validation
server roles, 22
user-defined database roles, 293-294
ROLLBACK TRANSACTION statement, 238-239
rounding expressions to specified length, 150-151
@@RowCount variable, 217
rows
counting, 111
deleting
DELETE statement, 146
TRUNCATE statement, 147
RTRIM function, 163
rules, 55
check constraints versus, 65
runtime errors, handling, 227-228
S
sample files, installing, 27-29
SANs (Storage Area Network), 323
SAS (Serial Attached Small Computer System Interface), 322
SATA (Serial AT Attachment), 322
Save dialog box, 74
saving tables, 64
advantages/disadvantages, 255-256
security
authentication
ownership, 295
credentials, 23
logins, 22
granting database access to logins, 284
permissions validation, 299
column-level permissions, 315-316
CONTROL permissions, 318
database users, adding, 300-302
function permissions, 315
inherited/implied permissions, 300, 317
object permissions, 299, 302-309
permission statements, 302
statement permissions, 299
stored procedure permissions, 314
roles
user-defined database roles, 293-294
server roles, 22
stored procedures, 250
views, 188
securityadmin, 286
Security Administrators (securityadmin), 286
Security node (Management Studio). See also logins; roles
Credentials node, 23
Logins, 22
Server Roles, 22
Select Backup Devices dialog box, 28, 339-340
SELECT INTO statement, 145
Select Login dialog box, 301
Select Objects dialog box, 306-308
Select Object Types dialog box, 306-307
SELECT permissions, 310
Select Server Login or Role dialog box, 288-289
SELECT statement. See also aggregate functions
@@ functions, 231
explained, 216
@@Identity, 218
@@RowCount, 217
@@TranCount, 218
date and time functions, 96-98
DISTINCT keyword, 105-107, 119
FROM clause, 92
INTERSECT, 137
changing sort direction, 102
syntax, 101
overview, 89
selecting all fields, 90
selecting specific fields, 90
SET NOCOUNT, 231
syntax, 90
when to use, 103
WHERE clause, 119
NULL keyword, 100
wildcard characters, 104
Select User or Group dialog box, 282
Select Users or Roles dialog box, 303, 315-316
Serial AT Attachment (SATA), 322
Serial Attached Small Computer System Interface (SAS), 322
serveradmin, 286
Server Administrators (serveradmin), 286
Server Configuration step (SQL Server installation), 381-382
Server Objects node (Management Studio), 23
backup devices, 23
linked servers, 24
server triggers, 24
Server Properties dialog box
Database Settings page, 329-330
Server Role Properties dialog box, 288
Server Roles node (Management Studio), 22
Server Roles subnode. See roles
servers
SQL Server. See SQL Server
server triggers, 24
SET NOCOUNT statement, 215-216, 231
SET statement, 248
setupadmin, 286
Setup Administrators (setupadmin), 286
short (constraints), 51
simple (constraints), 51
size of databases, 36
SmallDateTime data type, 49
SmallInt data type, 49
SmallMoney data type, 49
Solid State Disks (SSDs), 323-324
sort direction, changing, 102
SPACE function, 158
spaces
returning, 158
removing from strings
leading spaces, 162
trailing spaces, 163
sp_changeobjectowner stored procedure, 295
sp_changeobjectowner system-stored procedure, 295
@SQLCommand variable, 248
SQL pane (View Builder), 184
analyzing trace output, 372-373
when to use, 374
Activity Monitor, 25
authentication
ownership, 295
Database Engine Tuning Advisor, 18-19
databases. See databases
Add Features to an Existing Installation, 379
Database Engine Configuration step, 382
New SQL Server Stand-alone Installation, 379-380
Ready to Install step, 382-384
Server Configuration step, 381-382
SQL Server Feature Installation step, 381
SQL Server Installation Center, 379-380
log files, 25
explained, 42
inserting data with triggers, 266-274
transaction log, 39
logins
granting database access to logins, 284
Management Studio, 19
Management node, 25
performance tuning
overview, 324
analyzing trace output, 372-373
when to use, 374
roles
user-defined database roles, 293-294
SQL Server Agent, 17
SQL Server Database Engine, 387
versions
overview, 13
SQL Server 2014 Business Intelligence Edition, 15
SQL Server 2014 Enterprise Edition, 16
SQL Server 2014 Express, 14
SQL Server Express Edition, 30
SQL Server Standard Edition, 15
views
advantages of, 188
creating with Management Studio Query Builder, 179-185
security, 188
SQL Server 2014 Business Intelligence Edition, 15
SQL Server 2014 Enterprise Edition
installing
Add Features to an Existing Installation, 379
Database Engine Configuration step, 382
New SQL Server Stand-alone Installation, 379-380
Ready to Install step, 382-384
Server Configuration step, 381-382
SQL Server Feature Installation step, 381
SQL Server Installation Center, 379-380
overview, 16
SQL Server 2014 Express, 14
SQL Server Agent, 17
SQL Server and Windows (Mixed) authentication, 278-279, 296
SQL Server Database Engine, 387
SQL Server Express Edition, 30
SQL Server Feature Installation step (SQL Server installation), 381
SQL Server Installation Center, 379
SQL Server Login Properties dialog box, 285
SQL Server Standard Edition, 15
SQL_Variant data type, 49
SSDs (Solid State Disks), 323-324
stable (constraints), 51
Standard Edition (SQL Server), 15
Standard role, 293
statement permissions, 299
statements. See also clauses; functions; keywords
ALTER VIEW, 187
BEGIN TRANSACTION, 239
COMMIT TRANSACTION, 239
DECLARE CURSOR, 245
DELETE
compared to TRUNCATE, 147
explained, 146
DROP, 147
flow control constructs
BEGIN...END, 202
IF...ELSE, 200
GRANT, 302
permissions, 299
ROLLBACK TRANSACTION, 239
SELECT
date and time functions, 96-98
DISTINCT keyword, 105-107, 119
FROM clause, 92
INTERSECT operator, 137
overview, 89
selecting all fields, 90
selecting specific fields, 90
SET NOCOUNT, 231
syntax, 90
when to use, 103
wildcard characters, 104
SELECT INTO, 145
SET, 248
TRUNCATE, 147
stopping traces, 372
Stop Selected Trace command, 372
Storage Area Network (SANs), 323
stored procedure permissions, 314
stored procedures. See also triggers
compared to triggers, 10
controlling flow of
BEGIN...END construct, 202
overview, 200
RETURN statement, 203-206, 212
creating
with T-SQL, 196
defining, 245
looping through records with, 246-249
populating, 246
definition of term, 8
deleting data with, 237
error handling
explained, 227
returning success/failure information, 229-231
parameters, 221
explained, 231
permissions, 314
procEmployeeGetByTitleAndBirthDate, 221
procEmployeeGetByTitleAndBirthDateOpt, 224-225
procEmployeeGetYoungSalesReps, 221
procEmployeesGetByJobTitleAndHireDate, 244
procEmployeesGetByTitleAndBirthDateOutput, 225-226
procEmployeesGetCursor, 246
procOrderDetailAdd, 234
procOrderDetailAddHandleErrors2, 229
procOrderDetailAddHandle-Errors3, 230-231
procOrderDetailAddOutput, 234
procOrderDetailAdd-Transaction, 238
procSalesOrderDetailDelete, 237
procSalesOrderDetailUpdate, 235-236
procSalesOrderHeaderUpdate, 235
returning success/failure information from, 229-231
security, 250
SET NOCOUNT statement, 215-216
sp_changeobjectowner, 295
transactions, 237
implicit versus explicit, 237-238
string functions, 151
LEFT, 152
LEN, 153
LOWER, 160
LTRIM, 162
REPLACE, 154
REPLICATE, 156
REVERSE, 155
RTRIM, 163
SPACE, 158
STUFF, 157
SUBSTRING, 159
UPPER, 161
strings
converting to lowercase, 160
converting to uppercase, 161
determining length of, 153
extracting characters from left, 152
extracting characters from right, 152-153
extracting substrings from, 159
removing spaces from
leading spaces, 162
trailing spaces, 163
replacing
REPLACE function, 154
REPLICATE function, 156
replacing characters in, 157
returning spaces in, 158
reversing, 155
string functions, 151
LEFT, 152
LEN, 153
LOWER, 160
LTRIM, 162
REPLACE, 154
REPLICATE, 156
REVERSE, 155
RTRIM, 163
SPACE, 158
STUFF, 157
SUBSTRING, 159
UPPER, 161
SUBSTRING function, 159
substrings, extracting, 159
success information, returning from stored procedures, 229-231
summarizing table data. See aggregate functions
summary statistics, calculating, 109-110
synchronization, 24
System Administrators (sysadmin), 286
T
table aliases, 92
Table Designer, 46
Table Properties dialog box, 302-304
tables. See also views
adding to database diagrams, 76
aggregate functions
COUNT function, 111
explained, 110
finding minimum values in, 114
MAX function, 115
aliases, 92
columns, 45
column-level permissions, 315-316
constraints. See constraints
data types available, 46
explained, 5
finding maximum values in, 115
finding minimum values in, 114
identity specifications, 56
maximum values, 115
selecting, 90
Tables and Columns specifications, 79-81
user-defined data types, 58-60
constraints. See constraints
deleting data from
DELETE statement, 146
stored procedures, 237
TRUNCATE statement, 147
identity specifications, 56
inserting data into
joins, 188
explained, 121
purpose of, 126
junction tables, 70
nulls, 170
COALESCE function, 172
NULLIF function, 171
refreshing list, 64
relationships, 67
database diagrams versus, 77
establishing referential integrity, 81-84
foreign key relationships, 77-81
one-to-many, 68
removing from database diagrams, 77
rows
counting, 111
saving, 64
table aliases, 92
Tables and Columns specifications, 79-81
temporary tables
when to use, 250
updating
Tables and Columns dialog box, 70, 72, 80
Tables and Columns specifications, 79-81, 86
table-valued functions
inline table-valued functions, 257-258
multi-statement table-valued functions, 258-260
TAKE OWNERSHIP permissions, 310
TempDB database, 21
temporary tables
when to use, 250
Text data type, 49
Text page (Query Options dialog), 367
Time data type, 49
time/date functions, 96-98, 163
DATEADD, 168
DATENAME, 167
DAY, 164
MONTH, 163
YEAR, 165
TimeStamp data type, 50
TinyInt data type, 50
toolbars, New Query, 223
Trace Properties dialog box, 369-371
traces
analyzing trace output, 372-373
stopping, 372
trailing spaces, removing from strings, 163
@@TranCount function, 218
transaction log, 39
transactions, 237
implicit versus explicit, 237-238
transaction log, 39
when to use, 240
triggers. See also stored procedures
compared to stored procedures, 10
syntax, 266
definition of term, 10, 263, 275
server triggers, 24
when to use, 275
TRUNCATE statement, 147
tuning performance. See performance tuning
U
unique constraints, 56
UniqueIdentifier data type, 50
UPDATE permissions, 310
Update rule (relationships), 85-86
updating tables
uppercase, converting strings to, 161
UPPER function, 161
user-defined database roles, 293-294
user-defined data types, 58-60
user-defined functions
benefits of, 9
definition of term, 9
inline table-valued functions, 257-258
multi-statement table-valued functions, 258-260
overview, 253
advantages/disadvantages, 255-256
users
authentication
ownership, 295
ownership, 295
permissions validation, 299
column-level permissions, 315-316
CONTROL permissions, 318
database users, adding, 300-302
function permissions, 315
inherited/implied
object permissions, 299, 302-309
permission statements, 302
statement permissions, 299
stored procedure permissions, 314
V
validating permissions. See permissions validation
values, null
COALESCE function, 172
NULLIF function, 171
VarBinary data type, 50
VarBinary(MAX) data type, 50
VarChar data type, 50
VarChar(MAX) data type, 50
variables
@AverageFreight, 209
@Country, 201
creating in stored procedures, 198-199
@Cursor, 249
global variables
explained, 216
@@Identity, 218
@@RowCount, 217
@@TranCount, 218
@Locale, 201
@LocalError, 239
@LocalRows, 239
@LoopText, 211
@LoopValue, 211
@return, 255
@SQLCommand, 248
versions of SQL Server
overview, 13
SQL Server 2014 Business Intelligence Edition, 15
SQL Server 2014 Enterprise Edition, 16
SQL Server 2014 Express, 14
SQL Server Express Edition, 30
SQL Server Standard Edition, 15
VIEW DEFINITION permissions, 310
View Properties dialog box, 312-313
creating, 179
with Management Studio Query Builder, 179-185
customizing user data with, 188
definition of term, 7
indexed views, 7
modifying
with Management Studio Query Builder, 184-185
security, 188
W
Web Edition (SQL Server), 14-15
WHERE clause, 119
NULL keyword, 100
wildcard characters, 104
Windows Administrators group, 287, 296
Windows Only authentication, 278-279, 296
WITH GRANT statement, 302, 317
wizards, Maintenance Plan Wizard, 344-351
X-Y-Z
XML data type, 50
XML documents, returning data as, 107-109
xx row(s) affected message, eliminating, 215-216
YEAR function, 165