The Red Gate Guide to SQL Server Team-based Development

This book shows how to use of mixture of home-grown scripts, native SQL Server tools, and tools from the Red Gate SQL Toolbelt, to successfully develop database applications in a team environment, and make database development as similar as possible to "normal" development. Introduction 14 Chapter 1: Writing Readable SQL 17 Why Adopt a Standard? 17 Object Naming Conventions 19 Tibbling 19 Pluralizing 20 Abbreviating (or abrvtng) 20 [Escaping] 21 Restricting 23 A guide to sensible object names 24 Code Layout 27 Line-breaks 27 Indenting 28 Formatting lists 28 Punctuation 29 Capitalization 30 Getting off the fence... 31 Summary 35 Chapter 2: Documenting your Database 37 Why Bother to Document Databases? 37 Where the Documentation Should Be Held 38 What Should Be In the Documentation? 40 How Should the Documentation Be Published? 40 What Standards Exist? 41 XMLDOCS 41 YAML and JSON 45 How Headers are Stored in the Database 47 Extended properties 48 Get Information Out of Headers 52 Publishing the Documentation 56 Summary 59 Chapter 3: Change Management and Source Control 60 The Challenges of Team-based Development 61 Environments 62 Development environments 64 Testing, staging and production environments 66 Source Control 70 Source control features 71 Source control systems 73 Database objects in source control 76 Getting your database objects into source control 78 Managing data in source control 88 Summary 94 Chapter 4: Managing Deployments 95 Deployment Schemes 95 Visual Studio 2010 Premium tools 97 Red Gate SQL Source Control 106 Automating Builds for Continuous Integration 115 What is continuous integration? 116 Example: deploying to test 117 Creating test data 119 Automation with MSBuild, NAnt, and PowerShell 119 Automation with CruiseControl 124 Summary 126 Chapter 5: Testing Databases 127 Why Test a Database? 128 Essential Types of Database Testing 128 Black-box and white-box testing 129 Unit testing 131 Integration and acceptance testing 132 Stress testing databases 133 Error testing 134 Essentials for Successful Database Testing 134 The right attitude 134 A test lab 136 Source control 137 Database schema change management 138 Semi- or fully-automated deployment 139 A testing tool 140 A data generation tool 140 How to Test Databases 142 Reverting the database state 142 Simplifying unit tests 146 Testing existing databases 147 Unit Testing Examples: Testing Data and Schema Validity 149 Testing the database interface 149 Testing the database schema 152 Testing tables, views, and UDFs 157 Testing stored procedures 161 Testing authentication and authorization 164 Summary 167 Chapter 6: Reusing T-SQL Code 168 The Dangers of Copy-and-Paste 169 How Reusing Code Improves its Robustness 174 Wrapping SELECTs in Views 178 Reusing Parameterized Queries: Stored Procedures versus Inline UDFs 179 Scalar UDFs and Performance 184 Multi-Statement Table-Valued UDFs 189 Reusing Business Logic: Stored Procedure, Trigger, Constraint or Index? 189 Use constraints where possible 190 Turn to triggers when constraints are not practical 192 Unique filtered indexes (SQL Server 2008 only) 197 Summary 197 Chapter 7: Maintaining a Code Library 199 Coding for Reuse 200 Code comments 200 Parameter naming 202 Unit tests 204 Storing Script Libraries 205 Source control 206 A single file or individual files? 206 Tools for Creating and Managing Code Libraries 207 SQL Server Management Studio 208 Text editors 214 Wikis 216 SQL Prompt 220 Summary 225 Chapter 8: Exploring your Database Schema 226 Building a Snippet Library 227 Interrogating Information Schema and Catalog Views 228 Searching Structural Metadata in Schema-scoped Objects within a Database 230 Tables with no primary keys 231 Tables with no referential constraints 232 Tables with no indexes 233 A one-stop view of your table structures 234 How many of each object... 237 Too many indexes... 238 Seeking out troublesome triggers 239 What objects have been recently modified? 241 Querying the documentation in extended properties 243 Object permissions and owners 244 Searching All Your Databases 246 Investigating Foreign Key Relationships 247 Interrogating Object Dependencies 252 Finding the closest relations 253 Finding the dependency chain 254 Summary 259 Chapter 9: Searching DDL and Build Scripts 260 Searching Within the DDL 261 Why isn't it in SSMS? 261 So how do you do it? 262 Using SSMS to Explore Table Metadata 275 SSMS shortcut keys 279 Useful shortcut queries 280 Useful shortcut stored procedures 285 Generating Build Scripts 286 Summary 293 Chapter 10: Automating CRUD 294 First, Document Your Code 295 Automatically Generating Stored Procedure Calls 298 Automating the Simple Update Statement 302 Generating Code Templates for Table-Valued Functions 307 Automatically Generating Simple INSERT Statements 308 Summary 309 Chapter 11: SQL Refactoring 310 Why Refactor SQL? 310 Requirements for Successful SQL Refactoring 312 A set-based mindset 312 Consistent naming conventions 316 Thorough testing 317 A database abstraction layer 317 Where to Start? 318 SQL Refactoring in Action: Tackling Common Anti-Patterns 321 Using functions on columns in the WHERE clause 321 The "SELECT *" anti-pattern 324 Huge, many-parameter stored procedures 329 The "one subquery per condition" anti-pattern 331 The "cursor is the only way" anti-pattern 334 Using data types that are too large 340 The "data in code" anti-pattern 343 Summary 347