Here is the code for yesterday's Scenario:
create table #Department
(
DepartmentID int not null primary key clustered,
DepartmentName varchar(10) not null
)

create table #Employee
(
EmployeeID int not null identity primary key clustered,
DepartmentID int,
EmployeeName varchar(100)
)


insert #Department values(1, 'Sales'), (2, 'IT'), (3, 'Finance')
insert #Employee values (1, 'Sales Person1'), (1, 'Sales Person 2'), (2, 'Developer 1'), (2, 'Developer 2'),(2, 'DBA')

How to "aggregate" on strings

If you need to get the sum of salary by department we may be able to use Sum and group by clauses in a single select statement to get it done. But if you have a situation where you need to give a comma separated string of employees for each department, how will you handle it?
Consider these tables:

Department Table

DepartmentID	DepartmentName
1	Sales
2	IT
3	Finance

Employee Table

EmployeeID	DepartmentID	EmployeeName
1	1	Sales Person 1
2	1	Sales Person 2
3	2	Developer 1
4	2	Developer 2
5	2	DBA 1

Now you want the results like this:

DepartmentName	EmployeeList
Sales	Sales Person 1, Sales Person 2
IT	Developer 1, Developer 2, DBA
Finance	NULL

Catching row count and error number

After some days I am writing this blog.
I had a couple of training programs (total of 5.5 days) and then I need to travel to US (on official visit!) so I couldn't update the blog.
So here is the update.

If you need to track error number and row count of a statement how will you handle it?
Both of these are captured using @@ERROR and @@ROWCOUNT system variables.
But there is a catch:
They reflect the value of the immediate SQL statement irrespective of whether it is a DML statement (INSERT, UPDATE, DELETE & SELECT statements) or just a control statement. (Examples are IF, SET, WHILE) That means if you add SET @Err_Variable = @@ERROR after a DML statement, you will be able to catch the error number, but it will take the statement SET @Row_Variable = @@ROWCOUNT to return 1 always
How to resolve it?

For those use SQL logins

Today,  I came to read about this blog.  There seems to be a security issue if you are using SQL logins, but Microsoft says it is not an issue to worry about it.  According to this post, Microsoft will not issue a security patch to resolve this problem. 

Are you worried about this?  You can read this article where it shows how things can go wrong and ways to prevent them.

Is SQL Server's latest security hole a real threat?test-security-hole-real-threat-801

The problem that prevented SQL Server from responsing

I couldn’t write for some time due to the heavy schedule I have. I was asked my Microsoft to do a customized training to one of their clients and it was a 4.5 day training. Preparing the materials took most of my time.

During this time I also went through some troubleshooting processes. Here is one of that:

A particular server failed to respond. Some DBAs tried to restart but the service could not be re-started. Agent was trying to stop before SQL Server stops and that was on waiting. At this moment I got a chance to work with them in resolving the problems. I tried to check on SQL Server error log. I couldn’t even connect. I checked with task manager where it showed that SQL Server was using only a few MB of memory (less than 1 GB) but CPU usage was 70% - 80%

Gift from Quest

Yesterday, When I came from home I had a pleasant surprise.
Exactly one month ago, I wrote a post on free online training on SQL Server which was hosted by Quest. As I participated and filled the survey, Quest decided to honer me by thanking for my participation.

Of course I learned from the participation, But Quest surprised me with added stuff. Even though I participated only on two sessions, I received a Cd with all the sessions they had on that day. In addition to that I got two banners one on SQL server dynamic management views and the other on catalog views. To add, I also got a T-shirt on my size.
Of course the packet included some marketing stuff as well, but it gave me some advantage. As it was categorized as marketing material it bypassed the customs.

I went though the CD, it had the presentations and some additional documents. REALLY COOL stuff.

In summary, I gained by participating (the knowledge), I am gaining by participating (the goodies) and I will be gaining by participating. (I filled the survey so that future training programs will be more beneficial.)

Are you guys thinking of participating at the next training program?

Backup Process

Recently I had a question: What will happen to the transactions executed while a backup job is in progress? This question prompted me to learn more about backup process. Additionally I was asked to give training to a set of administrators so I wanted to be prepared with the answer before they ask the question.

There are a lot of articles in many sites about backup and restore and this article summarizes what I learned from those articles.

Active portion of Transaction Log

A transaction is considered active under various circumstances.

1. In any recovery model, while a transaction is non completed (neither committed nor rolled back) it is considered as active
2. In full or partial recovery model, until a log backup is taken even the committed transactions are considered active. In simple recovery model, it automatically becomes inactive when the transaction is committed.

Active transactions in the log keep the space. There is no way of removing the space without data loss. It is always the inactive part of the log which get cleared when BACKUP LOG with truncate only or SHRINK DATABASE command is executed. (By the way, from SQL Server 2008 onwards, BACKUP LOG WITH TRUNCATE_ONLY has no meaning. Microsoft recommends changing the recover model to simple.)

I have seen log files growing even in simple recovery model. There weren’t many large transactions happen either. I couldn’t figure the reason and I didn’t bother much too as I didn’t become that big issue to investigate.

Recently I read an article, which explained the reason: During backup process, whatever is written to the transaction log is kept active until the end of the backup process. So if the backup job takes more time, or if there were large volume of transactions (need not to be large transactions; small many transactions would do the job!) the entire volume of transaction log will be kept active until the backup job completes.


Wanna read more? Try this: Understanding SQL Backups
This article not only gives the reason for the growth of Transaction log, but also an in-depth understanding of how backup is taken in SQL Server. A good article to all those need in-depth knowledge of SQL backup!

Database Layer Testing

Recently I faced a problem: we had one old complex procedure taking more resources of the server. It had high number of executions, high duration and high reads. Constantly in our weekly reports we see it. At one point, we decided to rewrite the procedure. Even though reading the code and understanding the logic was a big challenge, the biggest challenge was testing it. While we were fine tuning the procedure and make it execute faster, Dev and QA gurus jumped in and identified the areas this procedure was called. As it is called by few core-components and they are used by many systems, QA team said it will take a tremendous effort to test all subsystems.
We came up with a new strategy: I should admit that even though it is yours truly who came-up with the idea, I am sure it came from above. I am not that smart to get something like this just like that
As the core-component cannot be tested directly, we decided to test the procedure directly: For all possible parameters which points to a column in the table we decided to execute both old and new versions of the procedure and compare the results. Unfortunately, the combination of parameters and the representation in the database turned the number of combinations to thousands.
To overcome the time needed for testing we decided to automate it. This is how we did that:

• Create two tables with the same set of columns. The columns name, data type and order should match the order of the output.
• Add additional columns to hold parameters.
• Add additional column to hold return value.
• Add an additional column for row id (we used bigint identity).
  Return value and row id are the only columns which have NOT NULL set to true.
• Create a script which will do the following:
1. Execute the old and new procedures for all possible parameter values and store the data with parameter values.
2. Immediately update the table with the return value and parameters.
   Finally compare the tables for any differences. SQL Compare or TableDiff could be used for this purpose.

For this method to work two conditions should be met:

1. The signatures of the procedures should be identical. The names, order and data types of parameters (input) and results (output) of the new procedure should be identical to the old one.
2. The results should be deterministic. That means the definition of the output should be static. Whatever the value of the parameters are the column names, data types and ordinal position should remain static.

We wrote a script that generated the execute statements which inserted the results into respective tables. At the end we compared both tables and they were exactly the same.
So we concluded that the results were identical. We were able to produce the results and the management was convinced that we do not need additional QA testing. This saved weeks of QA efforts.

Do you think that you need the script which can do that? Unfortunately I do not have a script which can do that magic for all procedures but I am ready to help you in creating one to meet your needs. Please drop me a note :)

IIS Database Manager

Microsoft is trying its very best to make the life of developers easier especially on the database side. Earlier it came with visual database tool, then with LINQ and now it has released the Release Candidate (RC) of IIS database Manager. This product allows developers and system administrators to easily manage the database servers both local and remote to be managed easily and faster. It can run on any Windows 2008 server if you have IIS 7 installed (and IIS Manager). This is specific product for SQL Server and uses SQL Server Native Client 2008. (If you don’t have it right now, don’t worry, during installation it will ask for you permission to download it.)

These are some of the links which may help you all to ready further about the product

Using IIS Database Manager: http://learn.iis.net/page.aspx/552/using-the-iis-database-manager/

Download IIS Manager Release Candidate (32 bit): http://www.microsoft.com/downloads/details.aspx?FamilyID=231989B4-2A52-4C31-B2D6-96E8E97F8295&displaylang=en


Download IIS Manager Release Candidate (64 bit): http://www.microsoft.com/downloads/details.aspx?FamilyID=2F238709-5618-4693-B7BA-D8C4035AC050&displaylang=en


The above links have links to many more pages to read:

I am planning to use it in the future if it seems productive for our environment. We are starting the evaluation…