| Description |
1 online resource |
|
data file |
| Note |
Includes index. |
| Summary |
Annotation Get up to speed on the extensive changes to the newest release of Microsoft SQL ServerThe 2012 release of Microsoft SQL Server changes how you develop applications for SQL Server. With this comprehensive resource, SQL Server authority Robert Vieira presents the fundamentals of database design and SQL concepts, and then shows you how to apply these concepts using the updated SQL Server. Publishing time and date with the 2012 release, Beginning Microsoft SQL Server 2012 Programming begins with a quick overview of database design basics and the SQL query language and then quickly proceeds to show you how to implement the fundamental concepts of Microsoft SQL Server 2012. Youll explore the key additions and changes to this newest version, including conditional action constructs, enhanced controls for results paging, application integration with SharePoint and Excel, and development of BI applications. Covers new features such as SQL Azure for cloud computing, clientconnectivity enhancements, security and compliance, data replication, and data warehouse performance improvementsAddresses essential topics including managing keys, writing scripts, and working with store proceduresShares helpful techniques for creating and changing tables, programming with XML, and using SQL Server Reporting and Integration ServicesBeginning Microsoft SQL Server 2012 Programming demystifies even the most difficult challenges you may face with the new version of Microsoft SQL Server. |
| Contents |
1. RDBMS Basics: What Makes Up a SQL Server Database? -- 2. Learning the Tools of the Trade -- 3. The Foundation Statements of T-SQL -- 4. Joins -- 5. Creating and Altering Tables -- 6. Keys and Constraints -- 7. Adding More to Your Queries -- 8. Being Normal: Normalization and Other Basic Design Issues -- 9. SQL Server Storage and Index Structures -- 10. Views -- 11. Writing Scripts and Batches -- 12. Stored Procedures -- 13. User-Defined Functions -- 14. Transactions and Locks -- 15. Triggers -- 16. A Brief XML Primer -- 17. Business Intelligence Fundamentals -- 18. BI Storage and Reporting Resources -- 19. Reporting for Duty, Sir! A look at Reporting Services -- 20. Getting Integrated with Integration Services -- 21. Playing Administrator. |
|
1. RDBMS Basics: What Makes Up a SQL Server Database? -- An Overview of Database Objects -- The Database Object -- The Transaction Log -- The Most Basic Database Object: Table -- Filegroups -- Diagrams -- Views -- Stored Procedures -- User-Defined Functions -- Sequences -- Users and Roles -- Rules -- Defaults -- User-Defined Data Types -- Full-Text Catalogs -- SQL Server Data Types -- NULL Data -- SQL Server Identifiers for Objects -- What Gets Named? -- Rules for Naming -- Summary -- 2. Learning the Tools of the Trade -- Getting Help with Books Online -- SQL Server Configuration Manager -- Service Management -- Network Configuration -- The Protocols -- On to the Client -- SQL Server Management Studio -- Getting Started with the Management Studio -- Interacting Through the Query Window -- SQL Server Data Tools (formerly BIDS) -- SQL Server Integration Services (SSIS) -- SQL Server Reporting Services (SSRS) -- SQL Server Analysis Services (SSAS) -- Bulk Copy Program (BCP) -- SQL Server Profiler -- sqlcmd -- PowerShell -- Summary -- 3. The Foundation Statements of T-SQL -- Getting Started with a Basic SELECT Statement -- The SELECT Statement and FROM Clause -- The WHERE Clause -- ORDER BY -- Aggregating Data Using the GROUP BY Clause -- Placing Conditions on Groups with the HAVING Clause -- Outputting XML Using the FOR XML Clause -- Making Use of Hints Using the OPTION Clause -- The DISTINCT and ALL Predicates -- Adding Data with the INSERT Statement -- Multirow Inserts -- The INSERT INTO ... SELECT Statement -- Changing What You've Got with the UPDATE Statement -- The DELETE Statement -- Summary -- 4. Joins -- Combining Table Data with JOINs -- Selecting Matching Rows with INNER JOIN -- How an INNER JOIN Is Like a WHERE Clause -- Retrieving More Data with OUTER JOIN -- The Simple OUTER JOIN -- Dealing with More Complex OUTER JOINs -- Seeing Both Sides with FULL JOINs -- Understanding CROSS JOINs -- Exploring Alternative Syntax for Joins -- An Alternative INNER JOIN -- An Alternative OUTER JOIN -- An Alternative CROSS JOIN -- Stacking Results with UNION -- Summary -- 5. Creating and Altering Tables -- Object Names in SQL Server -- Schema Name (aka Ownership) -- The Database Name -- Naming by Server -- Reviewing the Defaults -- The CREATE Statement -- CREATE DATABASE -- Building a Database -- CREATE TABLE -- The ALTER Statement -- ALTER DATABASE -- ALTER TABLE -- The DROP Statement -- Using the GUI Tool -- Creating a Database Using the Management Studio -- Backing into the Code: Creating Scripts with the Management Studio -- Summary -- 6. Keys and Constraints -- Types of Constraints -- Domain Constraints -- Entity Constraints -- Referential Integrity Constraints -- Constraint Naming -- Key Constraints -- Primary Key Constraints -- Foreign Key Constraints -- UNIQUE Constraints -- CHECK Constraints -- DEFAULT Constraints -- Defining a DEFAULT Constraint in Your CREATE TABLE Statement -- Adding a DEFAULT Constraint to an Existing Table -- Disabling Constraints -- Ignoring Bad Data When You Create the Constraint -- Temporarily Disabling an Existing Constraint -- Rules and Defaults -- Cousins of Constraints -- Rules -- Dropping Rules -- Defaults -- Dropping Defaults -- Determining Which Tables and Data Types Use a Given Rule or Default -- Triggers for Data Integrity -- Choosing What to Use -- Summary. |
|
7. Adding More to Your Queries -- What Is a Subquery? -- Building a Nested Subquery -- Building Correlated Subqueries -- How Correlated Subqueries Work -- Dealing with NULL Data -- the ISNULL Function -- Derived Tables -- Using Common Table Expressions (CTEs) -- Using the WITH Keyword -- Using Multiple CTEs -- Recursive CTEs -- Using the EXISTS Operator -- Filtering with EXISTS -- Using EXISTS in Other Ways -- Mixing Data Types: CAST and CONVERT -- Synchronizing Data with the MERGE Command -- The Merge Command in Action -- A Brief Look at BY TARGET versus BY SOURCE -- Gathering Affected Rows with the OUTPUT Clause -- Through the Looking Glass: Windowing Functions -- ROW_ NUMBER -- RANK, DENSE_ RANK, and NTILE -- One Chunk at a Time: Ad Hoc Query Paging -- Performance Considerations -- Measuring Performance -- JOINs versus Subqueries versus CTEs versus ... -- Summary -- 8. Being Normal: Normalization and Other Basic Design Issues -- Understanding Tables -- Keeping Your Data " Normal" -- Before the Beginning -- The First Normal Form -- The Second Normal Form -- The Third Normal Form -- Other Normal Forms -- Understanding Relationships -- One-to-One -- Zero or One-to-One -- One-to-One or Many -- One-to-Zero, One, or Many -- Many-to-Many -- Diagramming Databases -- Tables -- Creating Relationships in Diagrams -- Denormalization -- Beyond Normalization -- Keep It Simple -- Choosing Data Types -- Err on the Side of Storing Things -- Drawing Up a Quick Example -- Creating the Database -- Adding the Diagram and the Initial Tables -- Adding the Relationships -- Adding Some Constraints -- Summary -- 9. SQL Server Storage and Index Structures -- SQL Server Storage -- The Database -- The Extent -- The Page -- Rows -- Sparse Columns -- Understanding Indexes -- B-Trees -- How Data Is Accessed in SQL Server -- Creating, Altering, and Dropping Indexes -- The CREATE INDEX Statement -- Creating XML Indexes -- Implied Indexes Created with Constraints -- Creating Indexes on Sparse and Geospatial Columns -- Choosing Wisely: Deciding Which Index Goes Where and When -- Selectivity -- Watching Costs: When Less Is More -- Choosing That Clustered Index -- Column Order Matters -- Covering Indexes -- Filtered Indexes -- ALTER INDEX -- Dropping Indexes -- Taking a Hint from the Query Plan -- Why Isn't Your Index Being Used? -- Use the Database Engine Tuning Advisor -- Maintaining Your Indexes -- Fragmentation -- Identifying Fragmentation versus Likelihood of Page Splits -- Summary -- 10. Views -- Creating Simple Views -- Views as Filters -- Editing Views with T-SQL -- Dropping Views -- Creating and Editing Views in the Management Studio -- Creating Views in Management Studio -- Editing Views in the Management Studio -- Auditing: Displaying Existing Code -- Protecting Code: Encrypting Views -- About Schema Binding -- Making Your View Look Like a Table with VIEW_ METADATA -- Indexed (Materialized) Views -- Indexing an Aggregate View -- Summary -- 11. Writing Scripts and Batches -- Understanding Script Basics -- Selecting a Database Context with the USE Statement -- Declaring Variables -- Setting the Value in Your Variables -- Reviewing System Functions -- Retrieving IDENTITY Values -- Generating SEQUENCES -- Using @@ROWCOUNT -- Grouping Statements into Batches -- A Line to Itself -- Each Batch Is Sent to the Server Separately -- GO Is Not a T-SQL Command -- Errors in Batches -- When to Use Batches -- Running from the Command Prompt: sqlcmd -- Dynamic SQL: Using the EXEC Command -- Generating Your Code on the Fly -- Understanding the Dangers of Dynamic SQL -- Using Control-of-Flow Statements -- The IF ... ELSE Statement -- The CASE Statement -- Looping with the WHILE Statement -- The WAITFOR Statement -- Dealing with Errors with TRY/CATCH Blocks -- Summary -- 12. Stored Procedures -- Creating the Sproc: Basic Syntax -- An Example of a Basic Sproc -- Changing Stored Procedures with ALTER -- Dropping Sprocs -- Parameterizing Sprocs -- Declaring Parameters -- Confirming Success or Failure with Return Values -- How to Use RETURN -- More on Dealing with Errors -- Handling Inline Errors -- Making Use of @@ERROR -- Using @@ERROR in a Sproc -- Handling Errors Before They Happen -- Manually Raising Errors -- Re-throwing Errors -- Adding Your Own Custom Error Messages -- What a Sproc Offers -- Creating Callable Processes -- Using Sprocs for Security -- Sprocs and Performance -- Extended Stored Procedures (XPs) -- A Brief Look at Recursion -- Debugging -- Starting the Debugger -- Parts of the Debugger -- Taking a Break: Breakpoints -- Using the Debugger Once It's Started -- Understanding .NET Assemblies -- When to Use Stored Procedures -- Summary. |
|
13. User-Defined Functions -- What a UDF Is -- UDFs Returning a Scalar Value -- UDFs That Return a Table -- Inline UDFs -- Understanding Determinism -- Debugging User-Defined Functions -- Using .NET in a Database World -- Summary -- 14. Transactions and Locks -- Understanding Transactions -- ACID Transactions -- Introducing Transaction Operations -- Using BEGIN TRAN -- Using COMMIT TRAN -- Using ROLLBACK TRAN -- Using SAVE TRAN -- How the SQL Server Log Works -- Using the Log for Failure and Recovery -- Activating Implicit Transactions -- Understanding Locks and Concurrency -- Clarifying the Purpose of Locks -- Defining Lockable Resources -- Lock Escalation and Lock Effects on Performance -- Understanding Lock Modes -- Clarifying Lock Compatibility -- Specifying a Specific Lock Type -- Optimizer Hints -- Setting the Isolation Level -- Setting READ COMMITTED -- Setting READ UNCOMMITTED -- Setting REPEATABLE READ -- Setting SERIALIZABLE -- Setting SNAPSHOT -- Dealing with Deadlocks (aka "a 1205") -- How SQL Server Figures Out There's a Deadlock -- How Deadlock Victims Are Chosen -- Avoiding Deadlocks -- Summary -- 15. Triggers -- What Is a Trigger? -- ON -- WITH ENCRYPTION -- FOR The FOR NOT FOR REPLICATION -- AS -- Using Triggers for Data Integrity Rules -- Dealing with Requirements Sourced from Other Tables -- Using Triggers to Check the Delta of an Update -- Using Triggers for Custom Error Messages -- Other Common Uses for Triggers -- Other Trigger Issues -- Triggers Can Be Nested -- Triggers Can Be Recursive -- Triggers Don't Prevent Architectural Changes -- Triggers Can Be Turned Offwithout Being Removed -- Trigger Firing Order -- INSTEAD OF Triggers -- Performance Considerations -- Triggers Are Reactive Rather Than Proactive -- Triggers Don't Have Concurrency Issues with the Process That Fires Them -- Using IF UPDATE() and COLUMNS_UPDATED -- Keep It Short and Sweet -- Don't Forget Triggers When Choosing Indexes -- Try Not to Roll Back within Triggers -- Dropping Triggers -- Debugging Triggers -- Summary -- 16. A Brief XML Primer -- XML Basics -- Parts of an XML Document -- Namespaces -- Element Content -- Being Valid versus Being Well Formed -- Schemas and DTDs -- What SQL Server Brings to the Party -- Defining a Column as Being of XML Type -- XML Schema Collections -- Creating, Altering, and Dropping XML Schema Collections -- XML Data Type Methods -- Enforcing Constraints Beyond the Schema Collection -- Retrieving Relational Data in XML Format -- RAW -- AUTO -- EXPLICIT -- PATH -- OPENXML -- A Brief Word on XSLT -- Summary -- 17. Business Intelligence Fundamentals -- What Is Business Intelligence? -- BI Is Better Than the Alternative -- Making Decisions Based on Data -- Five Experts, Five Opinions -- Past Performance Predicts Future Behavior -- Who Uses BI? -- Those Who Forget History: The Data Warehouse -- The Purpose of the Data Warehouse -- OLTP versus OLAP -- Kimball versus Inmon -- Dimensional Modeling: Why Be Normal? -- Measures and Metrics -- Facts -- Dimensions -- ETLs -- Sourcing Your Data -- Enforcing Conformity -- Resolving Conflicts by Defining Authoritative Data -- Two, Three, or More Stages -- Dealing with Concurrency During ETLs -- Caching Your Extract -- The Operational Data Store (ODS) -- Making Your Data Actionable: BI Reporting Techniques -- Goals -- Key Performance Indicators -- Dashboards -- Scorecards -- Detail Reports -- Drill-Down -- Drill-Through -- Ad Hoc Reporting -- Summary -- 18. BI Storage and Reporting Resources -- BI in T-SQL -- Introducing the AdventureWorks Data Warehouse -- Using T-SQL Analysis Functions -- Columnstore Indexes -- SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube -- Understanding SSAS Modeling -- Building Your First Cube -- Data Sources -- Data Source Views -- Cubes -- Measure Groups -- Dimensions -- The Rest of the Cube -- Self-Service BI: User Tools -- Report Builder -- PowerPivot: Lightweight BI with SharePoint and Excel -- Power View -- Summary -- 19. Reporting for Duty, Sir! A look at Reporting Services -- Reporting Services 101 -- Understanding the SSRS Report Lifecycle -- Developing Reports in SSRS -- Managing Your Reports -- Delivering Reports to Users -- Understanding the Reporting Services Architecture -- SSDT: Report Designer -- SSRS Windows Service -- Report Manager -- Report Builder -- Report Viewer -- Building Simple Report Models -- Creating a Project -- Adding to Your Data Model -- Building and Deploying Your Data Model -- Report Creation -- Report Server Projects -- Building a Report Server Project -- Deploying the Report -- Summary -- 20. Getting Integrated with Integration Services -- Understanding the Problem -- Using the Import/Export Wizard to Generate Basic Packages -- Starting the Wizard -- Choosing Your Data Sources -- Completing the Wizard -- Examining Package Basics -- Connection Managers -- The Package Editor Pane -- Executing Packages -- Executing a Package inside the Development Studio -- Executing within SSMS -- Using the Execute Package Utility -- Executing Using the SQL Server Agent -- Executing a Package from within a Program -- A Final Word on Packages -- Summary -- 21. Playing Administrator -- Scheduling Jobs -- Creating an Operator -- Creating Jobs and Steps -- Logins and Users -- Backup and Recovery -- Creating a Backup -- Recovery Models -- Recovery -- Index Maintenance -- ALTER INDEX -- Policy Based Management -- Automating Administration Tasks with PowerShell -- What Is PowerShell? -- Installing and Enabling PowerShell -- Cmdlets and Modules -- Sqlps -- Deployment and Execution -- Summary. |
| Language |
English. |
| Subject |
SQL server.
|
|
SQL server. |
|
SQL server |
|
Client/server computing.
|
|
Architecture client-serveur (Informatique) |
|
Client/server computing |
| Added Author |
Vieira, Robert.
|
| Added Title |
Microsoft SQL Server 2012 programming |
| Other Form: |
Print version: Atkinson, Paul. Beginning Microsoft SQL Server 2012 programming. Indianapolis, IN : John Wiley & Sons |
| ISBN |
1118102282 |
|
9781118102282 |
|
9781118236215 (electronic bk.) |
|
1118236211 (electronic bk.) |
|
9781118223888 (electronic bk.) |
|
1118223888 (electronic bk.) |
|
9781118262160 |
|
1118262166 |
|
1283941015 |
|
9781283941013 |
|
More Resources