book

Microsoft® SQL Server® 2012 Step by Step

by Patrick LeBlanc

February 2013

Beginner

432 pages

10h 19m

English

Microsoft Press

Read now

Unlock full access

Who should read this bookWho should not read this bookOrganization of this bookConventions and features in this bookSystem requirementsCode samplesInstalling the code samplesUsing the code samplesAcknowledgmentsErrata & book supportWe want to hear from youStay in touch
Business intelligenceDatabase EngineT-SQL programming interfaceSecurity subsystemReplicationSQL Server AgentHigh Availability and Disaster Recovery ToolsSQL Server Integration ServicesSQL Server Management ToolsSummary
Editions of SQL Server 2012Choosing hardware for SQL ServerSoftware prerequisitesBefore installationSQL Server instancesService accountsCollation sequencesAuthentication modesInstalling SQL ServerInstalling SQL Server from the Setup WizardAfter installationAssigning a TCP/IP port number to the SQL Server Database EngineOpening a SQL Server instance port using Windows FirewallHow to upgrade to SQL Server 2012In-place upgradeSide-by-side upgradeSummary
Using SQL Server Books OnlineUsing SQL Server Management StudioUsing SQL Server Management Studio to create solutions and projectsUsing SQL Server Data ToolsUsing SQL Server Configuration ManagerSummary
Understanding SQL Server system databasesmaster databasetempdb databasemodel databasemsdb databaseresource databasedistribution databaseUnderstanding the SQL Server database structureCreating a databaseUnderstanding argumentsAdding files and filegroupsDetaching and attaching SQL Server databasesUnderstanding database recovery modelsSimple modelFull modelBulk-logged modelSummary
Developing a naming standardUnderstanding schemasUnderstanding SQL Server data typesNumeric data typesString data typesDate and time data typesOther data typesUnderstanding column propertiesCreating tablesAltering tablesUnderstanding computed columnsAdding constraints to a tablePrimary key constraintsDefault constraintsUnique constraintsCheck constraintsForeign key constraintsUnderstanding the FileTableCreating database diagramsSummary
Index structure overviewClustered index structureNonclustered index structureColumnstore index structureAdding index optionsAdding included columnsAdding filters to indexesPlacing indexesDisabling and dropping indexesSummary

Understanding row compressionRow compression with T-SQLUnderstanding page compressionPage compression with T-SQLEstimating effects of compressionCompression considerationsSummary
Creating a partition functionUsing partition function argumentsCreating a partition schemeSpecifying partition scheme argumentsPartitioning tables and indexesSummary
Understanding database snapshot prerequisites and limitationsCreating and viewing database snapshotsDropping database snapshotsReverting to a database snapshotSummary
Writing a SELECT statementSorting resultsFiltering data with the WHERE clauseUsing comparison operatorsUsing the BETWEEN operatorUsing the WHERE clause with multiple conditionsSearching for a list of valuesUsing a wildcard searchCreating aliasesUsing the JOIN operator to return data from multiple tablesUsing INNER JOINThe JOIN syntaxUsing OUTER JOINsLimiting the data returned in your result setUsing TOPUsing DISTINCT and NULLUsing UNION to combine result setsSummary
Pivoting and unpivoting dataUsing the PIVOT operatorUsing the UNPIVOT operatorPaging dataWriting expressionsUsing variablesSummary
Inserting data into SQL Server tablesUsing the INSERT INTO statementUsing sequence numbersUsing the SELECT INTO statementUpdating data in tablesUpdating rows while referencing multiple tablesDeleting data from tablesMerging dataReturning output dataSummary
Using date and time functionsDeriving dates from partsDifferencing, modifying, and validating date valuesUsing conversion functionsNew SQL Server 2012 conversion functionsUsing string functionsUsing logical functionsSummary
Aggregating, windowing, and groupingPerforming aggregationsPerforming aggregations with groupingsWindowingNew T-SQL windowing featuresUsing the HAVING clauseUsing SQL Server temporary objectsCommon table expressionsTable variablesTemporary tablesHandling T-SQL errorsControlling flow keywordsBEGIN...ENDIF...ELSEWHILESummary
What are views?Creating indexed viewsReferenced table requirementsIndexed view requirementsSummary
Understanding user-defined scalar functionsParameterizing functionsExecuting scalar functionsCalling scalar functions inlineCalling scalar functions using the EXECUTE keywordUnderstanding table-valued functionsUsing table-valued functionsLimitations of functionsSummary
Working with stored proceduresUsing the EXECUTE keywordParameterizing stored proceduresDropping stored proceduresSummary
Types of triggersCreating triggersAltering triggersDropping triggersEnabling and disabling triggersSummary
Types of replicationSnapshotTransactionalMergeReplication agentsSnapshot AgentDistribution AgentLog Reader AgentMerge AgentConfiguring replicationMonitoring replicationSummary
Understanding backup devicesFull database backupsDifferential database backupsTransaction log database backupsRestoring databasesSummary
Checking index fragmentationDefragmenting indexesReorganizing indexesRebuilding indexesChecking index usageCreating and updating database statisticsViewing database statistic optionsUpdating database statisticsSummary
Performing database consistency checksCreating maintenance plansUsing the Maintenance Plan WizardSummary
Understanding SQL Server ProfilerTypical uses of SQL Server ProfilerCreating tracesFiltering a traceCreating trace templatesRunning server-side tracesSummary
Understanding the Extended Events architectureCreating and configuring an Extended Events sessionUsing an Extended Events session to monitor system performanceSummary
Understanding principalsCreating server loginsCreating user-defined server rolesCreating database usersCreating built-in database rolesConfiguring contained databasesCreating a contained userBenefits and limitations of contained databasesSummary
Enabling and disabling Resource GovernorCreating resource poolsCreating a workload groupUsing classifier functionsTesting classifier functionsModifying Resource Governor configurationsSummary
SQL Server Agent componentsViewing SQL Server Agent Configuration Manager optionsConfiguring SQL Server Agent propertiesCreating operatorsConfiguring alertsConfiguring jobsCreating proxiesSummary
Database Mail componentsConfiguring Database MailSending email using Database MailMonitoring Database MailSummary
Types of triggersCreating triggersAltering triggersEnabling and disabling triggersDropping triggersSummary
Retrieving SQL Server metadata from DMOsQuerying server performance statisticsQuerying server informationsys.dm_server_servicessys.dm_os_volume_statssys.dm_os_sys_memorysys.dm_exec_requests and sys.dm_exec_sessionssys.dm_exec_sql_textQuerying performance informationsys.dm_exec_query_statsQuerying indexing statisticssys.dm_db_index_usage_statssys.dm_db_index_physical_statsSummary
Failover cluster instance improvementsMultisubnet clusteringRobust failure detectiontempdb on local driveCreating AlwaysOn Availability GroupsWindows Server Failover ClusteringEnabling AlwaysOnCreating availability groupsAdministering AlwaysOn Availability GroupsReading secondary database copiesSummary
Log shipping componentsLog shipping prerequisitesConfiguring log shippingSummary

Overview

Your hands-on, step-by-step guide to building applications with Microsoft SQL Server 2012

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Discover how to:

Install and work with core components and tools

Create tables and index structures

Manipulate and retrieve data

Secure, manage, back up, and recover databases

Apply techniques for building high-performing applications

Use clustering, database mirroring, and log shipping