A DBA or a Database Developer?

I often think if the above two roles can be completely separated. From my point of view I would say that this is not 100% possible.

Of course, DBAs are not developers and developers are not DBAs.

The DBA is responsible for the environmental aspects of a DBMS like the databases integrity, backup, security, installation of new DBMS software, etc.

The developer is the person who designs databases and other related database objects. Is the person who writes application code against DBMSs using T-SQL and other data access technologies.

There are many cases where the developer cannot perform the tasks the DBA can. Examples of such tasks are: Implementing High Availability solutions, DBMS security and so on.

Also, there are cases where the DBA cannot perform tasks that the developer can. Such tasks can be: Implementing T-SQL queries and other database objects (i.e. stored procedures and functions) that access and modify data.

Though, as the below figure graphically illustrates, there is an area of responsibility which is common for all the DBMS-related professionals in terms of common tasks or tasks which require the interaction of both DBMS professionals:


As an example, consider the task of Performance Tuning. In this case the DBA will monitor the DBMS instance for identifying bottlenecks on the memory, disk, CPU and network subsystems which are might caused by the operation of the databases under that DBMS instance. If the DBA discover a bottleneck on the pre-mentioned subsystems then is able to proceed with optimizing the DBMS by upgrading the subsystems causing bottlenecks (i.e. add more RAM) or addressing the issue relatively independently.

Though, doing only this is not really enough for performing an efficient and comprehensive performance tuning. Imagine the case where the DBAs runs a trace and finds that a specific database query which exists in a stored procedure and manipulates data has slow performance and needs to be rewritten. In this case the DBA will discuss this with the designer (developer) of the stored procedure in order for the latter to perform the required analysis and then redesign/optimize the stored procedure accordingly.

The above example indicates that performance tuning is one of the DBMS tasks which require the cooperation of the DBA and the Database Developer in some of its aspects.

Another example within the above context is writing T-SQL queries. Sure, the DBA writes T-SQL queries for administrating the DBMS instance whereas the developer writes T-SQL queries for manipulating data but still, in many cases they both write similar code. So under this context, the DBA should be capable of writing T-SQL queries with ease, not for manipulating data within the databases (this is the developer’s job) but for performing automated, administrative tasks. An example for performing automated tasks in a DBMS is to design and implement a stored procedure which can be executed on a scheduled-time basis.

Lately, Business Intelligence (BI) became very popular. To this end, now there is another DBMS role; the BI Developer.

The BI developer is the person who works with business intelligence technologies, in the case of SQL Server, like Reporting Services, Analysis Services, and Integration Services. Is the person who analyzes data in large Data Warehouses and transforms row data into knowledge.

Yet again, as the below figure graphically illustrates, there are still common tasks among the DBA, Database and BI Developer roles which require the interaction of all roles:

Like in the first example, a comprehensive performance tuning would require all three DBMS professionals working together in some of its aspects. Also, all three professionals must be capable of “reading” and writing T-SQL queries. Additionally, all three roles should work together in the case of data archiving and so on.

My conclusion is that even though each DBMS role has its own, genuine areas of responsibility where only professionals under that specific role can perform the related tasks, there will always be some tasks which will need the cooperation of all three (or even more) DBMS roles.

Of course, if a person has expertise on all the aspects of a DBMS operation (Administration, Development, Business Intelligence, etc.), this would be the best case scenario! :)
Read more on this article...

Using the "GO" Command in SQL Server

This post discusses the "GO" command in SQL Server.
Before proceeding with explaining GO, it is first necessary to understand what a "Batch" is.

As described in MSDN BOL, a Batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into an execution plan.

GO is a command used for signaling the end of a batch. Note that it is not a T-SQL statement.
This command is recognized by the following SQL Server utilities:

• sqlcmd
• osql
• SQL Server Management Studio Code editor
  

Using GO, you can specify multiple batches within your T-SQL code.
So, for an example, instead of having different files containing T-SQL code and separately executing each of it, you can just use a single file/code and separate the batches using GO. This is useful in many cases.

An example of some T-SQL code (in SQL Server Management Studio Code Editor) featuring batches is the following:

------------ Example 1------------
--BATCH 1
USE [AdventureWorks]
GO

--BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
SET @STRING1='BATCH 2'
SELECT @STRING1 AS RESULT
GO

--BATCH 3
DECLARE @STRING2 AS VARCHAR(50)
SET @STRING2='BATCH 3'
SELECT @STRING2 AS RESULT
GO
---------------------------------

The above T-SQL code features three batches.
The first batch instructs SQL Server Database Engine to use the "AdventureWorks" database.
The second batch declares a varchar variable, sets a value for it, and prints its value, and the third one does exactly the same with another variable.

So, in this example, the SQL Server Management Studio Code Editor will separately send the three bathes to SQL Server Database Engine for processing.

Note that by the time there are no dependencies between the batches, the above code is executed without any problems.

Now, consider the following code:

------------ Example 2------------
--BATCH 1
USE [AdventureWorks]
GO

--BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
DECLARE @STRING2 AS VARCHAR(50)

SET @STRING1='BATCH 2'
SET @STRING2='BATCH 3'

SELECT @STRING1 AS RESULT
GO

--BATCH 3
SELECT @STRING2 AS RESULT
GO
---------------------------------

Again, the above code features three batches. Though you can see that the variable @STRING2 is declared in Batch 2 but the SELECT statement from within Batch 3 also tries to access it for printing its value. Though, by the time @STRING2 is defined in Batch 2, consequently it will be only available for T-SQL Statements belonging to the scope of Batch 2.

Any other T-SQL statement trying to access it will result to a runtime error because @STRING2 is only visible within Batch 2.

Based on the above example, if you try to execute the entire T-SQL code you will get the following error message:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@STRING2".

GO is very useful in SQL Server. Though, you need to use it only when is really needed.
So, you need to keep in mind that along with defining a batch using the GO command, you define the scope of that specific piece of T-SQL code.
Read more on this article...

24 Hours of PASS!

I got asked to present a one-hour SQL Server development session during the "24 Hours of PASS" event which starts on September 2, 2009, 00:00 GMT.

The "24 Hours of PASS" is a series of live one-hour webcasts broadcast over a 24 hour period that focus on various SQL Server topics.

The event is free and it is an excellent opportunity for learning about Data Warehousing, SQLCLR, SQL Server security, Performance Tuning, SQL Server Development and much more!

You can check out all all the great sessions at the following link.

I will be presenting Session 18, with topic: "Building Flexible Data Services for the Web Using the ADO .NET Data Services Framework". For more info click on this link.

During my session, you can learn how to easily build flexible and scalable data services that are naturally integrated with the Web, by using the ADO .NET Data Services Framework. Also, you can see how to easily build a data service from scratch, map it to a SQL Server database, and expose database data to the Web. Learn how to use URIs and HTTP verbs such as GET, POST, PUT, and DELETE for accessing and manipulating specific pieces of the data. Also, see how Data Services fully support the REST protocol by using well-known formats for representing data, such as JSON and plain XML.

See you all online!
Read more on this article...

Screencast: Object Dependencies in SQL Server 2008

SQL Server 2008 introduces among other new features and enhancements for accurately tracking Object Dependencies.

This is very useful especially in cases where the user wishes to programmatically modify database objects having dependencies and therefore, he/she needs to resolve these dependencies before being able to proceed with the database object modification.

For efficiently tracking dependencies in SQL Server 2008, a new System View (sys.sql_expression_dependencies) and two Dynamic Management Functions (DMFs - sys.dm_sql_referenced_entities, sys.dm_sql_referencing_entities) are introduced.

For more information you can watch the Screencast below!



Read more on this article...

Webcast on SQL Server 2008 T-SQL Enhancements Now Publicly Available!

A few days ago I delivered a Live Webcast on Microsoft SQL Server 2008 Programmability and more specifically on T-SQL Enhancements.

During the Webcast I talked about the following SQL Server 2008 Programmability features:

• Variables Declaration and Initialization
• Compound Assignment Operators
• Large UDTs
• DATE, TIME, DATETIME2
• New & Enhanced Functions supporting DATETIME2 Precision
• Table Value Constructors (Row Constructors)
• Table Types and Table-Valued Parameters (TVPs)
• The MERGE Statement
  
• Grouping Sets
• Object Dependencies
  
• HIERARCHYID Data Type
  
• DDL Trigger Enhancements
  
• Collation Alignment with Windows

You are now able to watch this Webcast here!



Read more on this article...