S

SAVE (reserved word), 460

SAVEPOINT (reserved word), 465

scalar expressions, 280–283

scalar functions, 386–390

scaling considerations, 250–251

SCHEMA (reserved word), 460, 462

SCHEMABINDING option, 338

SCHEMA_LEVEL_ROUTINE view, 516

SCHEMA_NAME view, 516

schema.object, 449

SCHEMA_OWNER view, 516

SCHEMATA views

CATALOG_NAME view, 516

DEFAULT_CHARACTER_SET_CATALOG view, 516

DEFAULT_CHARACTER_SET_NAME view, 516

DEFAULT_CHARACTER_SET_SCHEMA view, 516

SCHEMA_NAME view, 516

SCHEMA_OWNER view, 516

SCOPE (reserved word), 465

SCOPE_CATALOG view, 516

SCOPE_IDENTITY() function, 202, 482

SCOPE_NAME view, 516

SCOPE_SCHEMA view, 516

script comment conventions

comment block, 459

in-line comment, 459

single-line comment, 459

script generation, 115

script management, 116

scripting options in SQL Server Management Studio, 87–88

scripting practices, 121–123

SCROLL (reserved word), 462, 465

SEARCH (reserved word), 465

second normal form — 2NF, 32

SECOND (reserved word), 462, 465

SECTION (reserved word), 462, 465

securing data, 363

security functions

fn_trace_geteventinfo() function, 199, 478

fn_trace_getfilterinfo() function, 199, 478

fn_trace_getinfo() function, 199, 478

fn_trace_gettable() function, 199, 478

HAS_DBACCESS() function, 199, 478

IS_MEMBER() function, 200, 478

IS_SRVROLEMEMBER() function, 200, 478

overview, 199, 478

SUSER_SID() function, 200, 478

SUSER_SNAME() function, 200, 478

USER_ID() function, 478

USER_id() function, 200

USER_NAME() function, 200

security procedures, 501–504

security requirements in application programming models, 400

SELECT command

AND operator, 141

BETWEEN operator, 145

calculated columns, 135–137

clauses in, 128

column aliasing, 134–135

columns, choosing, 128–132

comparison operators, 140–148

derived columns, 135–137

filtering rows, 137

filtering techniques, extended, 144–145

IN () function, 145–146

joins and, 244–245

logical comparisons, 141

NOT operator, 142–143

Null value, 143–144

operator precedence, 146–148

OR operator, 142

ORDER BY clause, 150–152

overview, 108, 128, 258, 450–451

parentheses, use of, 148–150

PERCENT statement, 155–156

results, sorting, 150–152

SQL Server 2005 schemas, 133–134

top values, 152–154

variables, used to assign, 163

WITH TIES statement, 154–155

WHERE clause, 138–148

SELECT INTO command, 262, 451

SELECT (reserved word), 460, 462

SELF option, 344

semantics, 26–27

SEQUEL (Structured English Query Language), 105

SEQUENCE (reserved word), 465

SERIALIZABLE locking option, 394

Server Network Utility, 49

server.database..object, 449

server.database.schema.object, 449

@@SERVERNAME, 469

server...object, 449

SERVERPROPERTY() function, 202, 482

server..schema.object, 449

server-side SQL objects, 405–406

Service Manager, 49

@@SERVICENAME, 168, 469

SESSION (reserved word), 462, 465

SESSIONPROPERTY() function, 202, 482

SESSION_USER() function, 202, 482

SESSION_USER (reserved word), 461, 462

SET operator

overview, 456

variables, used to assign, 162–163

SET (reserved word), 461, 463

set-based operations, 109

SETS (reserved word), 465

SETUSER (reserved word), 461

shared locks, 17

SHUTDOWN (reserved word), 461

SIGN() function, 193, 476

SIN() function, 193, 476

single quotes, 313

single-line comment, 459

SIZE (reserved word), 463, 465

SMALLINT (reserved word), 463, 465

SOAP (Simple Object Application Protocol), 411

SOME (reserved word), 461, 463

SOUNDEX() function, 302–304, 479

SPACE() function, 191, 479

SPACE (reserved word), 463, 465

sp_ActiveDirectory_Obj, 483

sp_ActiveDirectory_SCP, 483

sp_add_data_file_recover_suspect_db, 485

sp_addextendedproc, 485

sp_addextendedproperty, 485

sp_addlinkedserver, 489

sp_addlinkedsrvlogin, 489

sp_add_log_file_recover_suspect_db, 485

sp_add_log_shipping_alert_job, 492

sp_add_log_shipping_primary_database, 492

sp_add_log_shipping_primary_secondary, 492

sp_add_log_shipping_secondary_primary, 492

sp_add_maintenance_plan, 489

sp_add_maintenance_plan_db, 489

sp_add_maintenance_plan_job, 489

sp_addmessage, 485

sp_addtype, 485

sp_addumpdevice, 485

sp_altermessage, 485

sp_attach_db, 485

sp_attach_single_file_db, 485

sp_autostats, 485

sp_bindefault, 485

sp_bindrule, 485

sp_bindsession, 485

sp_catalogs, 489

sp_certify_removable, 485

sp_change_log_shipping_primary_database, 492

sp_change_log_shipping_secondary_database, 492

sp_change_log_shipping_secondary_primary, 492

sp_cleanup_log_shipping_history, 492

sp_column_privileges, 483

sp_column_privileges_ex, 483, 489

sp_columns, 483

sp_columns_ex, 483, 489

sp_configure, 485

sp_create_removable, 486

sp_createstats, 486

sp_cursor_list, 484

sp_cycle_errorlog, 486

sp_databases, 484

sp_datatype_info, 486

sp_dbcmptlevel, 486

sp_dboption, 486

sp_dbremove, 486

sp_delete_backuphistory, 486

sp_delete_log_shipping_alert_job, 492

sp_delete_log_shipping_primary_database, 492

sp_delete_log_shipping_primary_secondary, 492

sp_delete_log_shipping_secondary_database, 492

sp_delete_log_shipping_secondary_primary, 492

sp_delete_maintenance_plan, 489

sp_delete_maintenance_plan_db, 489

sp_delete_maintenance_plan_job, 489

sp_depends, 486

sp_describe_cursor, 484

sp_describe_cursor_columns, 484

sp_describe_cursor_tables, 484

sp_detach_db, 486

sp_dropdevice, 486

sp_dropextendedproc, 486

sp_dropextendedproperty, 486

sp_droplinkedsrvlogin, 489

sp_dropmessage, 486

sp_droptype, 486

special-purpose join operations, 241–246

SPECIFIC (reserved word), 465

SPECIFIC_CATALOG view, 515

SPECIFIC_NAME view, 515

SPECIFIC_SCHEMA view, 515

SPECIFICTYPE (reserved word), 465

sp_executesql, 486

sp_fkeys, 484

sp_foreignkeys, 489

sp_fulltext_catalog, 491

sp_fulltext_column, 491

sp_fulltext_database, 491

sp_fulltext_service, 491

sp_fulltext_table, 491

sp_getapplock, 486

sp_getbindtoken, 487

sp_help, 487

sp_helpconstraint, 487

sp_helpdb, 487

sp_helpdevice, 487

sp_helpextendedproc, 487

sp_helpfile, 487

sp_helpfilegroup, 487

sp_help_fulltext_catalogs, 491

sp_help_fulltext_catalogs_cursor, 491

sp_help_fulltext_columns, 491

sp_help_fulltext_columns_cursor, 491

sp_help_fulltext_tables, 491

sp_help_fulltext_tables_cursor, 491

sp_helpindex, 487

sp_helplanguage, 487

sp_help_log_shipping_alert_job, 492

sp_help_log_shipping_monitor_primary, 492

sp_help_log_shipping_monitor_secondary, 492

sp_help_log_shipping_primary_database, 492

sp_help_log_shipping_primary_secondary, 492

sp_help_log_shipping_secondary_database, 492

sp_help_log_shipping_secondary_primary, 492

sp_help_maintenance_plan, 489

sp_helpserver, 487

sp_helpsort, 487

sp_helpstats, 487

sp_helptext, 487

sp_helptrigger, 487

sp_indexes, 490

sp_indexoption, 487

sp_invalidate_textptr, 487

sp_linkedservers, 490

sp_lock, 488

sp_monitor, 488

sp_OACreate, 495

sp_OADestroy, 495

sp_OAGetErrorInfo, 495

sp_OAGetProperty, 495

sp_OAMethod, 495

sp_OASetProperty, 495

sp_OAStop, 495

sp_pkeys, 484

sp_primarykeys, 490

sp_procoption, 488

sp_recompile, 488

sp_refresh_log_shipping_monitor, 492

sp_refreshview, 488

sp_releaseapplock, 488

sp_rename, 488

sp_renamedb, 488

sp_resetstatus, 488

sp_resolve_logins, 492

sp_send_dbmail, 493

sp_server_info, 484

sp_serveroption, 488, 490

sp_setnetname, 488

sp_settriggerorder, 488

sp_spaceused, 488

sp_special_columns, 484

sp_sproc_columns, 484

sp_statistics, 484

sp_stored_procedures, 484

sp_tableoption, 488

sp_table_privileges, 484

sp_table_privileges_ex, 484, 490

sp_tables, 484

sp_tables_ex, 490

sp_trace_create, 495

sp_trace_generateevent, 495

sp_trace_setevent, 495

sp_trace_setfilter, 495

sp_trace_setstatus, 495

sp_unbindefault, 488

sp_unbindrule, 488

sp_updateextendedproperty, 488

sp_updatestats, 488

sp_validname, 488

sp_who, 488

sp_xml_preparedocument, 508

sp_xml_removedocument, 508

SQL Agent procedures, 504–507

SQL Computer Manager, 74

SQL expression, modifying, 58–62

SQL language

Data Manipulation Language (DML), 107–117

nature of SQL and, 106–107

origins of, 105–106

SQL pane in Query Designer tool, 53

SQL (reserved word), 463, 465

SQL Script, creating views in, 354–356

SQL Server

client/server processes, 42

conceptual design, 27–28

described, 23–25

foreign keys, 30

identity keys, 30

logical design, 28

physical design, 28–29

primary keys, 29–30

query processing, 42–44

relationships, 29

semantics, 26–27

surrogate keys, 30

terminology, changing, 27–29

unique identifiers, 30

who is using, 23–25

SQL Server 2000

catalogs, managing and populating, 305–306

described, 25

Enterprise Edition, 25

error handling in, 374–377

full-text index queries, 305–306

Standard Edition, 25

templates folder path, 115

SQL Server 2005

catalogs, managing and populating, 307–313

described, 25–26

error handling in, 378

full-text index queries, 307–313

(max) option, 12–13

.NET Framework incorporated with, 3–4

new features, 3

schemas, 133–134

templates folder path, 115

WITH command, 450

SQL Server Books Online, 74

SQL Server Management Studio

docking window placement, 77–80

down arrow icon, 75

graphical query designer, 88–93

Object Explorer, 83–84

opening, 74

overview, 74

projects, 82–83

queries, writing, 84–86

scripting options, 87–88

templates, 94–96

thumbtack icon, 75

tool windows, 75–81

X icon, 75

SQL Server Reporting Services

application integration, 444–447

data caching, 443–444

datasets, 418–423

default query view, 419

features of, 413–414

fields, adding, 430–431

format properties, setting, 431

groups, adding, 436–442

overview, 413

parameter lookup list, 424–425

parameterized queries, 420–423

queries, 418–423

Report Designer, 414–418

report layout, 426–430

Report Manager, 443

report parameters, configuring, 431–436

viewing report with Report Manager, 443

SQL-92 settings, 456

SQLCA (reserved word), 463

SQLCMD command-line utility, 99–101

SQLCODE (reserved word), 463

SQL_DATA_ACCESS view, 516

SQLERROR (reserved word), 463

SQLEXCEPTION (reserved word), 465

SQL_PATH view, 516

SQLSTATE (reserved word), 463, 465

SQLWARNING (reserved word), 463, 465

SQRT() function, 193, 476

SQUARE() function, 193, 476

START (reserved word), 465

STATE (reserved word), 465

STATEMENT (reserved word), 465

STATIC (reserved word), 465

statistical functions, 210–212

statistics, displaying, 456

STATISTICS (reserved word), 461

STATS_DATE() function, 202, 482

STDEV() function, 208, 212, 472

STDEVP() function, 208, 212, 472

storage and retrieval, 127–128

storage space, 400

stored procedures

active directory procedures, 483

business logic, processing, 378–380

CASE statement, 382–384

catalog procedures, 483–484

conditional logic, 380–382

creation of, 118–119

cursor management procedures, 484

database engine procedures, 485–488

database maintenance procedures, 489

Delete procedure, 374

distributed queries procedures, 489–490

errors, handling and raising, 374–378

external systems and extended procedures, 490–491

full-text index/search procedures, 491

IF statement, 380–382

Insert procedure, 371–373

insets managed using, 262–263

log shipping procedures, 492

looping, 384–386

mail procedures, 492–493

naming, 324

notification services procedures, 493–494

OLE automation procedures, 495

profiler procedures, 495

record maintenance, 370

replication procedures, 496–501

security procedures, 501–504

SQL Agent procedures, 504–507

SQL Server 2000, error handling in, 374–377

SQL Server 2005, error handling in, 378

Update procedure, 373–374

updating records using, 267–269

using parameters, 367–369

values, returning, 369–370

XML procedures, 508

STR() function, 174–175, 479

string manipulation functions

ASCII() function, 181–184, 478

CHAR() function, 182, 478

CHARINDEX() function, 184–185, 478

DIFFERENCE() function, 478

LEFT() function, 185–186, 478

LEN() function, 185, 478

LOWER() function, 188–190, 478

LTRIM() function, 190–191, 479

NCHAR() function, 184, 479

overview, 181

PATINDEX() function, 185, 479

QUOTENAME() function, 192, 479

REPLACE() function, 191, 479

REPLICATE() function, 191, 479

REVERSE() function, 191–192, 479

RIGHT() function, 185–186

RTRIM() function, 190–191, 479

SOUNDEX() function, 479

SPACE() function, 191, 479

STR() function, 479

STUFF() function, 192, 479

SUBSTRING() function, 186–188, 479

UNICODE() function, 184, 479

UPPER() function, 188–190, 479

STRUCTURE (reserved word), 465

Structured English Query Language (SEQUEL), 105

STUFF() function, 192, 479

subgrouping, 221

subqueries

aggregate functions used in, 286

benchmarking, 291–293

best practices, 291–293

business cases for, 293–296

consulting billing time, 294–296

correlated subqueries, 290–291

derived table, creating, 287

embedded SELECT statements, 280–283

EXISTS() function, 288–289

HAVING clause, 286

IN() function, 287–288

inner join subqueries, 283–284

nested loop inner join, 291

NOT EXISTS() function, 289

outer join subqueries, 284–285

overview, 233, 279

scalar expressions, 280–283

table aliasing, 286

top sales by territory, 293

unshipped product orders, 294

SUBSTRING() function, 186–188, 479

SUBSTRING (reserved word), 463

subtotals, 219–220

SUM() function, 167, 208, 209, 472

SUM (reserved word), 463

surrogate keys, 30, 37

SUSER_SID() function, 200, 478

SUSER_SNAME() function, 200, 478

Sybase SQLAnywhere, 20

sysmail_add_account_sp, 493

sysmail_add_principalprofile_sp, 493

sysmail_add_profileaccount_sp, 493

sysmail_add_profile_sp, 493

sysmail_configure_sp, 493

sysmail_delete_account_sp, 493

sysmail_delete_principalprofile_sp, 493

sysmail_delete_profileaccount_sp, 493

sysmail_delete_profile_sp, 493

sysmail_help_account_sp, 493

sysmail_help_configure_sp, 493

sysmail_help_principalprofile_sp, 493

sysmail_help_profileaccount_sp, 493

sysmail_help_profile_sp, 493

sysmail_start_sp, 493

sysmail_stop_sp, 493

sysmail_update_account_sp, 493

sysmail_update_principalprofile_sp, 493

sysmail_update_profileaccount_sp, 493

sysmail_update_profile_sp, 493

system availability requirements, 400

system functions and variables

APP_NAME() function, 200, 480

COALESCE() function, 200, 202–203, 480

COLLATIONPROPERTY() function, 200, 480

CURRENT_TIMESTAMP() function, 200, 480

CURRENT_USER() function, 200, 480

DATALENGTH() function, 200, 203–204, 480

ENCRYPT() function, 480

@@ERROR, 204, 470

fn_Get_SQL() function, 480

fn_HelpCollations() function, 480

fn_helpcollations() function, 200

fn_ServerSharedDrives() function, 480

fn_servershareddrives() function, 200

fn_VirtualFileStats() function, 480

fn_virtualfilestats() function, 201

FORMATMESSAGE() function, 201, 480

GETANSINULL() function, 201, 480

HOST_ID() function, 201, 481

HOST_NAME() function, 201, 481

IDENT_CURRENT() function, 201, 481

IDENT_INCR() function, 201, 481

@@IDENTITY, 204, 470

IDENTITY() function, 201, 481

IDENT_SEED() function, 201, 481

ISDATE() function, 201, 481

ISNULL() function, 201, 481

ISNUMERIC() function, 201, 481

NEWID() function, 201, 481

NULLIF() function, 201, 481

overview, 200, 471–482, 480–482

PARSENAME() function, 201, 481

PERMISSIONS() function, 201, 481

PWDCONPARE() function, 481

PWDENCRYPT() function, 482

@@ROWCOUNT, 204, 470

ROWCOUNT_BIG() function, 201, 482

SCOPE_IDENTITY() function, 202, 482

SERVERPROPERTY() function, 202, 482

SESSIONPROPERTY() function, 202, 482

SESSION_USER() function, 202, 482

STATS_DATE() function, 202, 482

SYSTEM_USER() function, 202, 482

@@TRANCOUNT, 204, 470

USER_NAME() function, 202, 482

system global variables, 467–471

system integration and data exchange, 412

system statistical functions and variables

@@CONNECTIONS, 204, 470

@@CPU_BUSY, 204, 470

fn_virtualfilestats() function, 482

@@IDLE, 204, 470

@@IO_BUSY, 204, 470

overview, 204, 470–471, 482

@@PACKET_ERRORS, 204, 471

@@PACK_RECEIVED, 204, 470

@@PACK_SENT, 204, 470

@@TIMETICKS, 204, 471

@@TOTAL_ERRORS, 204, 471

@@TOTAL_READ, 204, 471

@@TOTAL_WRITE, 204, 471

system-supplied aggregate functions used to summarize column values, list of, 208

SYSTEM_USER() function, 202, 482

SYSTEM_USER (reserved word), 461, 463