Thursday, December 31, 2009

Case Sensitive database and use of Distinct clause


In general, we don't have case sensitive database but whenever we have a sensitive database we need to watch out for Distinct statement.

Let's create a new database with coalation as case sensitive:

Click on Options and change the COLLATION to SQL_LATIN1_GENERAL_CP1_CS_AS

Now let's create a table in it and insert data in that table.
create table dbo.DisTest
Id int identity,
name varchar(100)

insert into dbo.DisTest
values ('Name1'), 

select * from dbo.DisTest

Now let's enter name as 'NAME3' & use distinct with select statement.
Insert into dbo.DisTest

Select distinct name from dbo.DisTest

In this situation, what to do to avoid duplicate values:

We can execute following query to get distinct values:

Select distinct name Collate SQL_LATIN1_GENERAL_CP1_CI_AS from dbo.DisTest

Finally, We saw that whenever we setup/create database we must think about what collation we want.

Do let me know if there is any issue.

Happy SQL Coding

Monday, December 28, 2009

OUTPUT Command


Today let's discuss about OUTPUT command introduced in SQL 2005. Also we will see how MERGE statement use OUTPUT command.

We need to audit the changes done on a table and there is a catch that this table is being modified from several applications but we need to audit only specific fields.

Use Triggers, write it on the table and whenever there is any update, we check for the fields that are required for Audit and if any of those fields are updated, insert data in the Audit table.

In this case, what will happen if one of the user is updating comments fields, which is not required for Auditing and he has to update approx 1 million records.

Now Triggers won't be efficient because this user updates 1 million records. Moreover he is updating comments field only, which is not required for Audit, the result will be poor performance.

In this case OUTPUT command will be the better solution because we can add OUTPUT clause.

As per BOL, The OUTPUT clause is not supported in the following statements:
  • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
  • INSERT statements that contain an EXECUTE statement.
  • Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
  • The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
  • A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

 Let's take a look at examples of OUTPUT clause:
Create table test
(a int identity,
b varchar(100))

insert into test
output inserted.*
select 'Simple Test'

A simple delete example:
delete test
output deleted.*

A simple Update example:
update test
Set b='Update Tested'
Output Inserted.*, deleted.*

Now let's insert data in a permanent table, so that we can audit the changes.
Create table Orders
(OrderId int identity,
CustomerName varchar(100))

Create table Orders_Adt
(OrderId int,
OldCustomerName varchar(100),
NewCustomerName varchar(100),
RecType char(1))

insert into Orders
output inserted.OrderId, null, inserted.CustomerName,'I' into Orders_Adt
values('Output Clause')

select * from Orders
select * from Orders_Adt


Let's update the customer name and store data in the Audit table:
update Orders
Set CustomerName='Updated Customer'
Output inserted.OrderId, deleted.CustomerName, inserted.CustomerName, 'U' into Orders_Adt
Where OrderId=1

select * from Orders
select * from Orders_Adt

Same way we can use Delete.

Delete Orders
Output deleted.OrderId, deleted.CustomerName,null,'D' into Orders_Adt

select * from Orders
select * from Orders_Adt

Now let's see how OUTPUT can be used with MERGE command:

Merge performs insert, update, or delete operations on a target table based on the results of a join with a source table.It helps synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Use AdventureWorks database for following example:

TARGET TABLE = Production.ProductInventory
SOURCE TABLES = Sales.SalesOrderHeader, Sales.SalesOrderDetail

IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
--In this example we are updating data based on date parameter passed to the procedure.

EXECUTE Production.usp_UpdateInventory '20030501'

This example basically matches two tables (Source and Target),
when the data matches and
target table (Production.ProductInventory) quantity - source table quantity <=0
then delete the record from TARGET table.

When the data matches then
update target table quantity with target table quantity - source table order quantity.

At the end we are using OUTPUT command "OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,Deleted.Quantity, Deleted.ModifiedDate;" to get output.

$action indicate the type of operation (INSERT / UPDATE / DELETE)

This way we can use OUTPUT command and overcome some problems with Trigger.

Hope this will be helpful. If you have any query/concern, write to me.


Wednesday, December 23, 2009

Policy Health State for Policy Based Management demo


A few days ago, I created a policy which used to validate the names of procedures. No procedure must start with "sp_".

Today while working, I found something interesting that reflect the procedures which don't meet the policy condition. The information is available from Database to object level.

Following is the policy:

Now let's look at Object Explorer, how we can validate if there is any object that doesn't match this policy or any other policy. Make sure that the policy is enabled.

In the above image, when we click on the server, it display all the objects in Object Explorer. Now let's click on the Show Policy Health State for all nodes button, which is highlighted.

This changes the Databases object to Critical under Policy Health State column. Let's drill down to the actual objects, which are causing the database to be critical. First will be which type of database(s) is/are critical.

This shows that LearningCT database health state is critical. Let's drill down again to find which object(s) is/are in critical state.

This shows that Stored Procedures have some issues. Let's drill down again to find which stored procedures are not adhering to the policy.

Ok, now we see that 2 procedures doesn't comply with the policy, which says that there shouldn't be any procedure that starts with "sp_", but we have 2 procedures.

We can select both procedures and choose Policies=>Delete Health States, it prompts to delete policy health state of the selected objects. Click on OK button will remove the health state of policy.

Now refresh the screen.

If we run through the process again, it will again show the health state of policy.

This is very helpful when we have not enforced any policy but want to know which objects fail the policy check. Another way to enforce this from happening is to prevent users from creating the objects which does not satisfy policy condition.

Hope this will be helpful.

Happy SQL Coding.

T-SQL Debugger is back in SQL Server 2008 ... debugging now made easy

Let's discuss about debugging a routine like procedure, UDF and Trigger using SQL Server 2008.

Earlier Query Analyzer and Enterprise Manager were separate in SQL Server 2000 then Query Analyzer and Enterprise Manager are merged into 1 tool (SSMS) SQL Server Management Studio in 2005 but without any debugging option. To debug a routine we had to use Visual studio with debugging abilities.

SQL Server 2008 introduced debugging capability that allow to debug routines like procedure, UDF and Triggers. With SQL 2008, we can navigate our code, line by line, watch the state/values of variables, an output window is available to observe what's happening, error windows. We can also move through lines, view call stacks for execution flow, place "breakpoints" to ensure that execution stops at that point to review the state.

How to start debuging

We can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu or pressing ALT+F5 on SSMS.

The image shows a simple statement with breakpoints. It also has a Locals, Breakpoints & Watch window. These are very useful when we are doing complicated calculations.

An example to demonstrate Call Stack window.
create proc prc

select GETDATE()
exec proc1

create proc proc1
print getdate()

exec prc
In the above script we have created 2 procedures, in this 1 proc is called from another proc. It has a Call Stack window that shows the execution flow.

SQL Server Management Studio must be running under a Windows/SQL Server account that is a member of the sysadmin fixed server role. If you are running the T-SQL debugger when SQL Server Management Studio is running on a different computer from the instance of the Database Engine, you must enable program and port exceptions by using the Windows Firewall Control Panel application on both computers.

Its recommended that T-SQL debugging should be done on a development/test server only and not on a production server. We can't debug a SQL 2005 database routine from SQL 2008, it throws exception when we try to debug a SQL 2005 routine.

Happy T-SQL coding and debugging. :)

Sunday, December 20, 2009

SQL TRAN - Translation from mySQL to SQL Server

Hi Guys,

Today I came across a product that help translate mySQL objects to SQL Server. The best part is that it is close to 100% correct while converting the code to SQL Server.

SqlTran is an advanced relational database translator. It analyzes whole database in a source database format and writes equivalent database in target database format. We strive to have 100% fully automated translation, with all programming logic preserved.

Following objects can be translated from mySQL to SQL Server:
  • Tables
  • Indexes
  • Constraints
  • Views
  • Procedures
  • Triggers
  • Functions
Databases currently supported are:

• Microsoft SQL Server

For more information, I strongly urge people who want to migrate their current database from mySQL to SQL Server to download SQLTran & start converting code. It will be easy to understand the business logic written in Procedures, Triggers, View etc using SQLTran rather than assumtion.
You can download SQLTran from :
All the best.
Do let me know if you need more information.

Monday, December 14, 2009

Community Technology Update 2009, Singapore

Hi Guys,

CTU 2009, Singapore is just around the corner on 19 Dec 2009 from 9am till 5pm at Microsoft office. Check out the Agenda here:


For register procedure click on following link:

Register here

Do let me know if you have any concern / question.



Friday, December 4, 2009

Best of Vol 7


I am sure you must be aware of books published by SQL Server They have recently released their VOL 7. Its a very good book. You can download it from

If there is any issue with download, let me know I will send the copy to you.

Enjoy SQL Coding.