Database Reference
In-Depth Information
SeparatION OF DUtIeS IN DeVeLOpMeNt teaMS
it is impossible not to talk about separation of duties within development teams. Unfortunately, even nowadays
when systems work with hundreds of gigabytes or even terabytes of data, you will rarely find teams with a
dedicated
database professional
role. every developer is expected to know SQL and to be able to write database
code. Agile development teams usually focus on the functional items and do not spend much time on optimization
and performance testing.
i use the term “database professional” rather than “database developer” on purpose. the line between database
developers and administrators is very thin nowadays. it is impossible to develop efficient database code without a
deep understanding of how SQL Server works under the hood.
it is expected that database administrators will perform index tuning and optimizations at a very late stage
of development or even after production deployment. however, at that time, the code and database schema
refactoring becomes very expensive and, frequently, leads to missed deadlines. it is always better and cheaper
not to make mistakes in the first place, rather than address them later.
the team should always include database professionals when working on complex projects. these professionals
should take ownership of the data access layer and database code. it does not necessarily mean that database
developers should write all of the database code; however, they should make important database design
decisions and review queries against critical tables with large amounts of data. this will help reduce development
time and avoid costly mistakes during development.
One of the common problems frequently encountered in systems that query database objects directly is an
excessive amount of dynamic and ad-hoc SQL. Queries are generated on the fly, and they frequently use constants rather
than parameters. This negatively affects SQL Server performance by adding extra CPU load due to recompilations and
increases plan cache memory usage. Moreover, it makes an application vulnerable to SQL Injection attacks.
■
Note
You can read about SQL injection at:
http://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx
.
We will discuss recompilations and plan caching in greater detail in Chapter 26, “plan Caching.”
Security is another important aspect to consider. When database tables are accessed directly, permissions should
be managed at the objects or schema level. You cannot prevent users from accessing data from a subset of columns
unless you are using column-level permissions, which introduce management overhead. With all that being said, it
is completely normal to work with database tables directly, especially in small systems. Make sure that you have all of
the database code separated in the data access layer and avoid using non-parameterized ad-hoc SQL.
Database Views
Database views
provide a basic level of abstraction, hiding database schema implementation details from the client
applications. They help address some security issues; that is, you can grant permissions on views rather than tables,
therefore, client applications would not have access to columns that are not included in the views.
Similar to database tables, queries that reference views from the client applications can be refactored on the
fly without any database changes required. This works perfectly for queries that read data; however, updating data
through views introduces a new set of challenges. As we discussed in Chapter 9, there is a set of restrictions that can
prevent a view from being updateable. Alternatively, you can define
INSTEAD OF
triggers on views, although they work
less efficiently as compared to updating tables directly.
