Skip to main content
Disciplines
Automotive Business IT + Informatics Construction + Real Estate Electrical Engineering + Electronics Energy + Sustainability Insurance + Risk Finance + Banking Management + Leadership Marketing + Sales Mechanical Engineering + Materials
Events
DE
EN
Books
Journals
Topic Page
Marketing
Start single access now
Access for companies
EXTENDED SEARCH
Log in
Top

2015 | Book

Overview and Installation Read chapter Read first chapter

Beginning SQL Server for Developers

Fourth Edition

Author: Robin Dewson

Publisher: Apress

Part of: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Login to get access
insite
SEARCH

About this book

Beginning SQL Server for Developers is the perfect book for developers new to SQL Server and planning to create and deploy applications against Microsoft’s market-leading database system for the Windows platform. Now in its fourth edition, the book is enhanced to cover the very latest developments in SQL Server, including the in-memory features that are introduced in SQL Server 2014.

Within the book, there are plenty of examples of tasks that developers routinely perform. You’ll learn to create tables and indexes, and be introduced to best practices for securing your valuable data. You’ll learn design tradeoffs and find out how to make sound decisions resulting in scalable databases and maintainable code.

SQL Server 2014 introduces in-memory tables and stored procedures. It's now possible to accelerate applications by creating tables (and their indexes) that reside entirely in memory, and never on disk. These new, in-memory structures differ from caching mechanisms of the past, and make possible the extraordinarily swift execution of certain types of queries such as are used in business intelligence applications. Beginning SQL Server for Developers helps you realize the promises of this new feature set while avoiding pitfalls that can occur when mixing in-memory tables and code with traditional, disk-based tables and code.

Beginning SQL Server for Developers takes you through the entire database development process, from installing the software to creating a database to writing the code to connect to that database and move data in and out. By the end of the book, you’ll be able to design and create solid and reliable database solutions using SQL Server.

Takes you through the entire database application development lifecycleIncludes brand new coverage of the in-memory featuresIntroduces the freely-available Express Edition

Table of Contents

Frontmatter
Chapter 1. Overview and Installation
Abstract
Welcome to Beginning SQL Server for Developers. This book has been written for those who are interested in learning how to create solutions with Microsoft SQL Server, but have no prior knowledge of SQL Server. You may well have had exposure to other database management systems (DBMSs), such as MySQL, Oracle, or Microsoft Access, but SQL Server uses different interfaces and has a different way of working compared to much of the competition. The aim of this book is to bring you quickly up to a level at which you are developing competently with SQL Server. This book is specifically dedicated to beginners and to those who at this stage wish to use only SQL Server. You may find this book useful for understanding the basics of other database management systems in the marketplace, especially when working with T-SQL within this book. Many DBMSs use an ANSI-standard SQL, so moving from SQL Server to Oracle, Sybase, and so on, will be a great deal easier after reading this book.
Robin Dewson
Chapter 2. SQL Server Management Studio
Abstract
Now that SQL Server is successfully installed on your machine, you can start exploring the various areas that make this an easy and effective product to use. With SQL Server, all the administration interfaces that reside in SQL Server can be accessed by graphical interfaces called studios, such as SQL Server Management Studio, which features tools for working with developing database solutions. This chapter concentrates on SQL Server Management Studio (SSMS), which you will use to develop and maintain your databases and the objects SSMS contains.
Robin Dewson
Chapter 3. Database Design and Creation
Abstract
Now that you’ve installed SQL Server and examined the main tools you’ll use as a SQL Server developer or administrator, it’s almost time to start building the ApressFinancial database solution. However, you can’t do this yet because you still don’t know what the database will hold. At this point in time, all the information you have is that you’ll be building a database to hold some financial transactions for a personal or corporate financial tool. You’ll gear this database toward a financial tool that a corporation might use because SQL Server can be scaled from one user to thousands of users very easily. The next step is to gather more information about the requirements of the solution and about what data is required to be stored in the database. Once the information-gathering stage is complete, you’ll then be able to create the database within SQL Server throughout the book. The book’s main intent is to demonstrate to you how to build a database solution from the ground up.
Robin Dewson
Chapter 4. Security and Compliance
Abstract
Security is important—more so, in fact, than design, creation, and performance. If your database had no security measures in place, absolutely anyone could come along and steal or corrupt the data, causing havoc to you and your company—and not in just one database, but in every database in every server.
Robin Dewson
Chapter 5. Defining Tables
Abstract
Now that you have created the database, it needs to have the ability to store information. After all, without this, what is the point of a database? The first area that needs to be worked on is the table definitions.
Robin Dewson
Chapter 6. Creating Indexes and Database Diagramming
Abstract
Now that you have created the tables, you could stop at this point and just work with the data from here. However, this would not be a good decision. As soon as any table contained a reasonable amount of information and you wished to find a particular record, it would take SQL Server a fair amount of time to locate it. Performance would suffer, and your users would soon get annoyed with the slowdown in speed.
Robin Dewson
Chapter 7. In-Memory Tables
Abstract
Chapter 5 introduced you to disk-based tables and how they are defined within a database. As I have mentioned in previous chapters, SQL Server tries to be as fast as possible in completing the given unit of work. The slowest component of completing the unit of work will be accessing data in tables on a disk, physical I/O. To try to reduce physical I/O and instead use logical I/O, SQL Server will try to hold data in memory based on its own calculations on what data should be held and how recently the data was last accessed. It would be better for you to be able to define to SQL Server which tables are so important that the data they hold should be permanently held in memory. This is where in-memory tables come in to their own. Up to and including SQL Server 2012, the only way to achieve holding data permanently within memory was to pin a table to being held in memory. SQL Server used disk-based optimizations as the table was on disk, but from SQL Server 2014 it is possible to define a table to permanently reside in-memory, and it now also has its own database optimized engine to fully utilize in-memory held data.
Robin Dewson
Chapter 8. Database Backups and Recovery
Abstract
Before inserting and manipulating the data in the database you have created in the previous chapters, it is a good idea to back it up so that if things go wrong, it will be possible to recover the data back to a stable point.
Robin Dewson
Chapter 9. Database Maintenance
Abstract
At this point, you have now created a backup and performed a restore of the example database. You have also covered the different methods to back up and restore the database. However, there is no real plan for regular maintenance and detection and reporting of problems in the database strategy. Any jobs for backup of the database or transaction log that I have demonstrated so far are held as single units of work called steps. Not only that, but also there is nothing in place that will look after the data and indexes held within the database to ensure that they are still functioning correctly and that the data are still stored in the optimal fashion. Without a process that runs regularly, you would need to perform all of this by hand regularly and check the results each time. What a waste of time, and boring to boot!
Robin Dewson
Chapter 10. Data Insertion, Deletion, and Transactions—Disk-Based
Abstract
The database is now defined, has a backup and recovery plan in place, and logins to the server and users to the database are defined, but you have not yet had to work with any data at this point. Within this chapter, you will learn about data insertion and deletion. In Chapter 12, this will progress to retrieving and removing data.
Robin Dewson
Chapter 11. Selecting and Updating Data from Disk-Based Tables
Abstract
In this chapter, you will see details on how to retrieve data correctly and professionally in a production environment, and this chapter will lay the foundation for the more advanced T-SQL found in the forthcoming chapters. This chapter also sets the stage for selecting and updating data in in-memory tables. There are a great many similarities between disk-based and in-memory tables. You’ll learn more about in-memory tables in Chapter 13.
Robin Dewson
Chapter 12. Working with In-Memory Tables
Abstract
In Chapter 7 you were introduced to how to build an in-memory table and the choice of either using schema-only storage or also storing the data on disk. The aim of this chapter is to move forward and look at how to work with that data outside of stored procedures, as well as at the performance improvements in-memory-based data can provide. You will also see how in-memory tables use a concurrency model that tries to ensure that processes work as quickly as possible. You’ll look at the overheads this model provides and compare it to a model that locks data so that only one process can work with a row of data. You will also look at how to merge data from a source location to a target location.
Robin Dewson
Chapter 13. Building a View
Abstract
A view is a virtual table that, in itself, doesn’t contain any data or information. All it contains is the query that the user defines when creating the view. A view is a named query that can be used against one or more tables. Views are used as a security measure by restricting users to accessing only certain columns or rows from an underlying table, as a method of joining data from multiple tables and presenting it as if that data resided in one table, and as a method of returning summary data instead of detailed data.
Robin Dewson
Chapter 14. Stored Procedures, Functions, and Security
Abstract
Now that you know how to build queries written as single executable lines of T-SQL statements, it is time to look at how to place these into a stored procedure or a function within SQL Server, allowing them to be run as often as they are required without the need to be retyped every time. This also allows code to be written that can accept input parameters, return a status, return rows of data, and, most important, allow you to improve the security of your database. You will see all of this and more in this chapter.
Robin Dewson
Chapter 15. Natively Compiled Stored Procedures
Abstract
Now that disk-based stored procedures have been covered it’s time to look at natively compiled stored procedures. These procedures are built to execute against in-memory tables as quickly and as efficiently as possible. For the most part, the functionality available in standard stored procedures exists within natively compiled stored procedures, but there are important differences that this chapter will go through.
Robin Dewson
Chapter 16. Essentials for Effective Coding
Abstract
Now that you know how to build and work with SQL Server objects, and how to insert, update, and delete data as well as retrieve it, you can move on to more of the T-SQL essentials required to complete your programming knowledge.
Robin Dewson
Chapter 17. Advanced T-SQL and Debugging
Abstract
By now, you really are becoming proficient in SQL Server and writing code to work with the data and the objects within the database. Already you have seen some T-SQL code and encountered some scenarios that have advanced your skills as a T-SQL developer. You can now look at more advanced areas of T-SQL programming to round out your knowledge and really get you going with queries that do more than the basics.
Robin Dewson
Chapter 18. Triggers
Abstract
At times a modification to data somewhere within your database will require an automatic action on data elsewhere, either in your database, another database, or elsewhere within SQL Server; a trigger is the object that will do this for you. When a modification to your data occurs, SQL Server will fire a trigger, which is a specialized stored procedure that will run, performing the actions that you desire. Triggers are most commonly used to perform business rules validation, carry out cascading data modifications (changes on one table causing changes to be made on other tables), and keep track of changes for each record (audit trail). You can also do any other processing that you require when data on a specific table are modified, such as auditing. You actually have come across triggers when looking at Declarative Management Framework earlier in the book in Chapter 3. These specialized system triggers are built to ensure the system’s integrity. You will see how these work by building your own DDL trigger later in this chapter.
Robin Dewson
Chapter 19. Connecting via Code
Abstract
If you defined yourself as a pure database developer, you would only be writing T-SQL code building on your knowledge gained within this book. However, some DBAs write code outside of the database to access data and execute stored procedures, as well as many other tasks. This can be accomplished in many different programming languages and tools. The aim of this chapter is to demonstrate how you can connect to SQL Server and accomplish working with your data using Excel VBA, .NET, and Java, as these are the most common coding platforms that you are likely to use. To be clear, the aim of this chapter is not to make you an expert or even quite proficient in these languages or tools but to demonstrate basic code that provides you with a good start to expand on via Apress books or other media.
Robin Dewson
Backmatter
Metadata
Title
Beginning SQL Server for Developers
Author
Robin Dewson
Copyright Year
2015
Publisher
Apress
Electronic ISBN
978-1-4842-0280-7
Print ISBN
978-1-4842-0281-4
DOI
https://doi.org/10.1007/978-1-4842-0280-7

Premium Partner

    Image Credits