SQLSENSE: SQL Server Practical Approaches

User Permissions in all databases in a SQL Server

2017-10-16T17:49:00.003-05:00

Audit is a regular activity for databases. Sometime internal, sometimes external, sometimes bi-yearly, sometimes yearly, sometimes for preparation for external audit, sometimes corrections to existing audits and so on. Doing it manually is quite time-consuming and ofcourse, boring due to repetitive nature of activities. I prefer to automate generation of portions of audit information as much as possible. For database audits, mostly it is about logins, what access they have, permissions by groups, etc. So I thought of creating a table with columns that would contain the data/information that auditors generally seek and a simple SP that would populate this table. The SP would traverse user databases to get the information. Usually DBA's have a database specific to high end DBA activities like Service broker, job progression, etc. Let us call it my DBADB for this article's sake. This kind of table can reside in such a database where interference is minimal. So let us start with the table, dbo.DB_Permissions_By_Group some column names of which are self explanatory:

USE [DBADB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF;
go
CREATE TABLE [dbo].[DB_Permissions_By_Group](
[DatabaseName] [varchar](32) NOT NULL,
[Class_Desc] [varchar](128) NULL,
[ObjectName] [varchar](64) NULL,
[Minor_Id] [smallint] NULL,
[Type] [varchar](24) NULL,
[Permission_Name] [varchar](24) NULL,
[State_Desc] [varchar](16) NULL,
[GroupName] [varchar](64) NULL
) ON [PRIMARY]
with (data_compression = page)
GO

The other columns that need some description are following:
[Class_Desc] is type of objects which can be a database, schema, table, SP, etc.
[Type] is type of permission. For e.g. Execute, Select, Update, View, Showplan, etc.
[GroupName] is a Windows AD group that has some permission in some application database on this server. There can be one or more individual logins or groups within a given Windows group. And these members will have the same permission as the parent Windows group that they belong to.

Now to the SP that would populate this table, [dbo].[DB_Permissions_By_Group]. The SP is called
[dbo].[dbp_Show_Permissions]. This SP scans all the non-system databases and fetches the permission on a given object for each user that exists in that database. Here is the code of this SP:

USE [DBADB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or alter procedure [dbo].[dbp_Show_Permissions]
as
set nocount on
declare @l_exec_String varchar(8000)
       ,@l_db_name     varchar(32)
truncate table SBMDB.dbo.DB_Permissions_By_Group
declare DB_Cursor scroll cursor
for
select name from sys.databases
where name not in ('master', 'model', 'msdb', 'tempdb', 'SSISDB')
open DB_Cursor
fetch next from DB_Cursor into
     @l_db_name
while (@@FETCH_STATUS = 0)
begin
select @l_exec_String = 'Use ' + @l_db_name + '; select db_name() DatabaseName, a.Class_Desc, case when a.class_desc = ' + '''' + 'Schema' + ''''
        + ' then schema_name(a.major_id) else object_name(a.major_id) end ObjectName '
        + ',a.minor_id, a.[Type], a.[permission_name], a.state_desc ,b.name from sys.database_permissions a '
        + 'inner join sys.database_Principals b '
        + 'on a.grantee_principal_id = b.principal_id '
        + 'where b.name like ' + '''%MyDomain\%'''
insert into [DBADB].dbo.DB_Permissions_By_Group (DatabaseName, Class_Desc, ObjectName, Minor_Id, [Type], [Permission_Name], State_Desc, GroupName)
exec (@l_exec_String)
    fetch next from DB_Cursor into
            @l_db_name
end
close DB_Cursor
deallocate DB_Cursor
set nocount off;
GO
Explanation: Basically, it joins the DMV's sys.database_permissions (for permissions) and sys.database_principals (for users) and matches them based on the principal_id which is the ID of the database user for a given login. It does it for each user database and puts the result in our table,  [DBADB].dbo.DB_Permissions_By_Group.

Since it sifts through only the DMV's in each user database, performance is not a concern. It takes less than a few seconds to go through a few dozen databases. Once this SP gets executed, the user needs to simply extract the contents of the table, [DBADB].dbo.DB_Permissions_By_Group into an Excel file and send it to auditors.

Execution: execute [DBADB].[dbo].[dbp_Show_Permissions]

Results:      Select * from [DBADB].dbo.DB_Permissions_By_Group with (nolock)

Finding Permission for user tables by Login

2017-06-20T17:08:00.002-05:00

Sometimes, we need to find permissions of a SQL Server user or a Windows login on database tables. Doing so using the Object Explorer > Security > Logins or Object Explorer > [Database] > Security > Users in SSMS is quite tedious and hard to document. One simple way could be the use of the T-SQL system function, HAS_PERMS_BY_NAME. Let us say, I want to find out the permissions that a login called ReportLogin used in the data sources of all SSRS/Sharepoint reports has on the application tables in a database called MyDB. This is what I do:

Use MyDB
go
execute as login = 'ReportLogin'
go
select SUSER_sNAME()
go
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
SELECT DB_NAme() DatabaseName
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'SELECT') AS have_SELECT
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'INSERT') AS have_INSERT
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'UPDATE') AS have_UPDATE
      ,HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'DELETE') AS have_DELETE
      ,QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) TABLE_NAME
      ,*
   FROM sys.objects where [type] in ('U')
go
revert
go
select SUSER_sNAME()
go

That does the following:

Executes the code as the SQL login called ReportLogin,
Checks if that login has any access to the database MyDB,
Gives the result as to the type of access (select, insert, update, delete) in the first four columns for each user table and
Then reverts back to my login.

If it is a windows login whose access needs to be verified, simply substitute it as

Execute as Login = 'Domain\LoginName'

The rest of the code remains the same. Please note that this code doesn't work for Windows groups. That is because 'Execute As Login' doesn't work for Windows Groups. The next article would address this goal.

Possibilities:

The code above can be converted to an SP.
The SP can loop through differ logins in the syslogins catalog view.
For each login, it can insert data into a table.
Adding exec sp_MSforEachDB to execute that SP would execute it on all databases if DB_NAME() is added in the select list.
I would leave that portion to the reader.

Find occurrence of a specific day in a month

2017-05-30T17:01:00.000-05:00

I am resuming blogging. Sorry for being away for so long. Many a time, we encounter situations where something is to be executed on a 2nd Saturday, a 4th Tuesday of a month or likewise. It would be so handy to have something that easily verifies the occurrence of a specific weekday in a given month and then executes other TSQL code after such an occurrence has been verified. A reusable code that returns a true or false is much easier than actually going through the dates and counting the occurrence of a specific day. So, I embark on creating a scalar function for this purpose (In this function, I have used CTE, Recursive CTE, IIF, EOMonth):

USE [DB_NAME]
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go

create function [dbo].[dbf_Is_day_Nth_occurrence]
(@i_Day varchar(10)
,@i_Nth_Occurrence tinyint)

Returns bit
as
/************************************************************************************************************************************************************
Date      Altered by          Description
__________ ___________________ ___________________________________________________________________________________________________________________________
04/15/2017 Suresh K. Maganti   Created. True or False. is it the 2nd Tuesday of this Month?
************************************************************************************************************************************************************/
begin
   declare @o_Return bit

;with CTE_Base (First_of_Month, Last_of_Month)
as
(select convert(datetime, convert(varchar(4), year(getdate())) + right('0' + convert(varchar(2), month (getdate())), 2) + '01'), convert(datetime, EOMonth(getdate())) )
--select * from CTE_Base

,CTE_Recursive (The_Date, The_Day, Occurrence_in_Month, Last_of_Month)
as
(select First_of_Month, datename(WEEKDAY, First_of_Month), 1, Last_of_Month from CTE_Base
   union all
   select The_Date + 1, datename(WEEKDAY, The_Date + 1), Occurrence_in_Month + 1, Last_of_Month from CTE_Recursive
   where The_Date < Last_of_Month)

,CTE_Final (The_Date, The_Day, Nth_Occurrence_of_Day)
as
(select The_Date, The_Day, Row_Number() over(partition by The_Day order by Occurrence_In_Month)
   from CTE_Recursive)

select @o_Return = iif ( (The_Day = @i_Day) and (Nth_Occurrence_of_Day = @i_Nth_Occurrence), 1, 0)
from CTE_Final
where convert(varchar, The_Date, 112) = convert(varchar, getdate(), 112)

return @o_Return
end
go

Today, 05/30/2017 is 5th Tuesday of May 2017. Now, let me check using the function if today is the 1st Sunday of this month:

select [dbo].[dbf_Is_day_Nth_occurrence] ('Sunday', 1)
I get a 0. The answer is No.

Let me try to see if it is the 5th Tuesday of this month:

select [dbo].[dbf_Is_day_Nth_occurrence] ('Tuesday', 5)
I get a 1. The answer is Yes.

So, I can do the next set of things that I can do for today as follows:

if [dbo].[dbf_Is_day_Nth_occurrence] ('Tuesday', 5) = 1
begin
   Print 'The next article would be a bigger one.'
end
go

Index Keys & Properties as a step in Database Tuning

2013-03-14T18:09:00.003-05:00

Introduction

As DBA's, we encounter long running queries that end-users compalin about. Indexing strategy is one area of database where developers usually tend to create indexes on the fly without much consideration for or analysis on how an index that helps one query marginally can slow down a bunch of other queries or stored procedures drastically. Some (not all) of the situations that we usually come across are following:

Dbo.Orders table is being joined to dbo.Order_Details table but both of them exist on the same filegroup and on the same set of underlying discs. This would cause disc queuing and the consequent slowness.
Dbo.Orders table is being joined to dbo.Order_Details table on the common column Product_ID but Product_ID is the leftmost colmn of a non-clustered index on dbo.Orders but is the third column in another non-clustered index on dbo.Order_Details. So these indexes don't help this join much.
Updates are taking place on a column that has a non-clustered index that is compressed. It would be good not to have an index at all on a column being updated. In case such an index is really required, not compressing it would be beneficial. Otherwise the update is much slower than when the index is uncompressed.
A majority of values in a particular column used in the WHERE clause of a query have a value but the query is designed to fetch only such rows where the value is null. May be a filtered-index would have helped speed it up.

In such situations and some others, we would like to have a quick gance at atleast the following to begin with:

Whether the two tables being joined are physically resident on different filegroups.
The columns being joined are the leftmost columns in their respective indexes.
The column being updated is not part of an index (first of all) that is compressed in any way (definitely not).
In case, a query is looking for a small subset of values from a column, is the column a part of an index that has the appropriate filter defined on the index.
Etc.

Instead of trying to procure such relevant information painstakingly one at a time, an easier way would be to have a stored procedure return the same. This article is aimed at creating such a stored procedure that could come in handy in performance situations described above.

Details of the Stored procedure
The text of the store procedure is given below:

If exists (select 1 from sys.procedures
               where USER_NAME(schema_id) = 'dbo'
               and name = 'dbp_Find_Index_Properties_by_Column'
               and type = 'P')
begin
     drop procedure dbo.dbp_Find_Index_Properties_by_Column
end
go
create procedure dbo.dbp_Find_Index_Properties_by_Column
@i_Table_Name     varchar(64) = null
,@i_Column_Name varchar(24) = null
,@l_Column_Position_in_Index tinyint = null
as
*******************************************************************************************************************************
Created by: Suresh K Maganti
Purpose    : Find if a column is used in an index. View additional properties of the index.
                   For now, I have included only clustered and non-clustered indexes.
Input
Parameters: If provided, the result set is appropriately filtered. Else, the complete result set is returned.
********************************************************************************************************************************/
set  nocount on;

with CTE_Base (Table_Name, Index_Name, Leftmost_Column_in_Index, Position_in_Index, Columns_in_the_Index, Index_Type, Is_Primary_Key, Is_The_Index_Unique, Fill_Factor_When_Created, File_Group_Name, Is_It_A_Filtered_Index, Filter_Definition, Is_An_Included_Column, Is_Compressed, Type_Of_Compression_Used)
as
(    select C.Name Table_Name, b.name Index_Name
,col_name(b.[object_id], (select e.column_id from sys.index_columns e
                                          where e.index_column_id = 1
                                          and     e.[object_id] = b.[object_id]
                                          and     e.index_id = b.index_id))                   Leftmost_Column
,case
    when @i_Column_Name is null then null
    else a.key_ordinal
end                                                                                                         Position_in_Index

,replace((select col_name(f.[object_id], f.column_id) + ' ###' 'data()'
               from sys.index_columns f
               where f.[object_id] = a.[object_id]
               and     f.index_id = a.index_id
               order by f.key_ordinal asc
               for XML Path ('')), '###', ',')                                                   Columns_in_the_Index

,case b.Type
      when 0 then 'Heap'
      when 1 then 'Clustered'
      when 2 then 'Nonclustered'
      when 3 then 'XML'
      when 4 then 'Spatial'
end                                                                                                         Index_Type

      ,case b.is_primary_key
       when 1 then 'Yes'
       else 'No'
end                                                                                                         Is_Primary_Key

,case b.is_unique
       when 1 then 'Yes'
       else 'No'
end                                                                                                        Is_The_Index_Unique

,b.fill_factor                                                                                          Fill_Factor_When_Created

,FILEGROUP_NAME (b.data_space_id)                                            File_Group_Name

,case b.has_filter
     when 1 then 'Yes'
     else 'No'
end                                                                                                        Is_It_A_Filtered_Index

,b.filter_definition

,case a.is_included_column
      when 1 then 'Yes'
      else 'No'
end                                                                                                        Is_An_Included_Column

,case
     when d.data_compression > 0 then 'Yes'
     else 'No'
end                                                                                                       Is_Compressed

,d.data_compression_desc Type_Of_Compression_Used

from                   sys.tables c left join sys.index_columns a

on c.[object_id] = a.[object_id]

inner join sys.indexes b

on    a.[object_id] = b.[object_id]
and a.index_id = b.index_id

inner join sys.partitions d
on     b.[object_id] = d.[object_id]
and   b.index_id = d.index_id

where b.[type] in (1, 2)
and a.key_ordinal = isnull(@l_Column_Position_in_Index, a.key_ordinal)
and OBJECTPROPERTY (b.[object_id], 'IsSystemTable') = 0
and OBJECTPROPERTY (b.[object_id], 'IsTable') = 1
and col_name(b.[object_id], a.column_id) = ISNULL(@i_Column_Name, col_name(b.[object_id], a.column_id))
and c.name = ISNULL(@i_Table_Name, c.name))

select distinct Table_Name, Index_Name, Leftmost_Column_in_Index, Position_in_Index
,substring(Columns_in_the_Index, 1, LEN(Columns_in_the_Index) - 1) Columns_in_the_Index
,Index_Type, Is_The_Index_Unique, Fill_Factor_When_Created, File_Group_Name, Is_It_A_Filtered_Index, Filter_Definition, Is_An_Included_Column, Is_Compressed, Type_Of_Compression_Used

from CTE_Base
order by Table_Name

set nocount off;

go

For now, I have included only clustered and non-clustered index including filtered indexes.
This stored procedure basically uses a few catalogue views:

sys.partitions
sys.tables
sys.index_columns
sys.indexes

and a few system functions like:

OBJECTPROPERTY
FILEGROUP_NAME
COL_NAME

and a new construct FOR XML PATH that was introduced in Microsoft SQL Server 2008. XML PATH is being used in this stored procedure to generate a comma separated list of columns that are part of a given index ordered by their order of occurrence within the index. You may notice an interesting fact here. I am using ORDER BY inside the subquery. Normally ORDER BY is the last part of any SELECT statement. But when used with XML PATH, it comes before the FOR XML PATH construct. Another thing you may notice is that when ORDER BY is used inside a subquery, usage of TOP 100 PERCENT after the keyword SELECT, is mandatory in most cases. Not so in this case.

The rest are all basic joins.

Now we will get to the usage of this stored procedure.

Usage
For simplicity, I have used Microsoft's Adventureworks database in the examples below.

Execution 1:

Please execute the following TSQL code without any input parameters:

execute dbo.dbp_Find_Index_Properties_by_Column

Figure 1

Results for all clustered and non-clustered indexes in the Adventureworks database are displayed.

Execution 2:

Please execute the following TSQL code. It will display the results for all tables and their indexes in the Adventureworks database the column AddressID is a part of:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', null

Figure 2

Execution 3:

Now execute the following TSQL statements to change the fillfactor on the index Purchasing.VendorAddress.IX_VendorAddress_AddressID from 80 to 50:

use Adventureworks
go
alter index IX_VendorAddress_AddressID on Purchasing.VendorAddress rebuild with (statistics_norecompute = on, fillfactor = 50, Online = ON, maxdop = 8, sort_in_tempdb = on, allow_page_locks = on)
go

Now execute the statement below to see if the change in fill factor for this index has been captured:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', null

Figure 3

As you have noticed in the screenshot above, the stored procedure displays 50 (Figure 3) intead of 80 (Figure 2) for the fill factor for this index.

Execution 4:

Now let us add a secondary filegroup named SecondaryFileGroup to the database Adventureworks, add a data file to this new filegroup, then drop the index Purchasing.VendorAddress.IX_VendorAddress_AddressID and finally recreate it on the new filegroup, SecondaryFileGroup using data compression and a filter on the AddressID column. The same is accomplished by executing the following code. Please note that you may have to change the path of the new data file in the code below as per you custom configured disc layout:

alter database Adventureworks add filegroup SecondaryFileGroup
go
alter database Adventureworks add file (Name = Adventureworks_2, filename = 'E:\Data3\MSSQL\Data\Adventureworks_2.ndf', size = 10MB, Maxsize = 100MB, filegrowth = 10%) to filegroup SecondaryFileGroup
go
drop index Purchasing.VendorAddress.IX_VendorAddress_AddressID
go
create index IX_VendorAddress_AddressID on Purchasing.VendorAddress (AddressID)
where AddressID is not null with
(statistics_norecompute = on, fillfactor = 50, data_compression = Page, Online = ON, maxdop = 8, sort_in_tempdb = on, allow_page_locks = on) on SecondaryFileGroup
go
Now execute the statement below to see if the changes for this index have been captured:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', null

Figure 4

The changes have been captured.

Execution 5:

Lets check to see on which tables the column, AddressID is being used in an index while occurring at the second position.

Please execute the statement below to find this out:

execute dbo.dbp_Find_Index_Properties_by_Column null, 'AddressID', 2

Figure 5

AddressID occurs as the second column in the three indexes on the three tables shown in Figure 5 above.

Summary:
The stored procedure dbo.dbp_Find_Index_Properties_by_Column can be used to find various aspects of how a column is involved within an index. This SP also provides details on various indexes which can then be used to refine existing indexing strategy based on various application stored procedures. I haven't delved deeper into performance optimization with respect to indexing strategy in this article. I have only attempted to provide an easier starting piont for a preliminary analysis.

As usual, please let me know of your thoughts and comments.

Usage of CTE - Trick with Dates

2011-11-18T11:50:00.000-06:00

Situations arise where logical data generation becomes a requirement. By 'Logical Data Generation', I mean generating additional data based on a logic being applied to existing data. Of course, there are multiple ways of doing the same. In this article, I would like to demonstrate how it conventionally being done using cursors and how it can be done much more efficiently using CTE's. The concept is being brought out using a simple example.

The problem:
In order to explain the problem, let me first create two tables, dbo.abc and dbo.def. Once we get familiarity with the data, it would be easier to explain the problem. The table dbo.abc has four columns namely, SeqNo, Date_Field, Month_Count, and Payment. Lets create and populate it by executing the code below:

if exists (select * from sys.tables
           where name = 'abc'
           and   USER_NAME(schema_id) = 'dbo'
           and   [type] = 'U')
drop table dbo.abc
go

create table dbo.abc
(SeqNo smallint
,Date_Field datetime
,Month_Count tinyint
,Payment decimal(10,2))
go
insert into dbo.abc (SeqNo, Date_Field, Month_Count, Payment)
values (1, '20090101', 10, 100)
          ,(2, '20100101', 7, 200)
        ,(3, '20110101', 5, 300)
go
Lets create another table dbo.DEF with the columns SeqNo, Date_Field, and Payment only. We will keep it empty for now.

if exists (select * from sys.tables
           where name = 'def'
           and   USER_NAME(schema_id) = 'dbo'
           and   [type] = 'U')
drop table dbo.def
go

create table dbo.def
(SeqNo smallint
,Date_Field datetime
,Payment decimal(10,2))
go
Please execute the code below to view the data in the table, dbo.abc:

select * from abc

go
The lets define the problem. Each row in the table, dbo.abc has to be duplicated as many times as the value of Month_Count in that row. Currently the Date_Field has a value that represents the first day of an year. While duplicating the data, we need to ensure that each new row has its Date_Field value incremented by one month. We need to stop the duplication as soon as we reach the month value in the column Month_Count for that row. The values for the columns SeqNo and Payment should remain as they are in the original row. This process needs to be done for all rows in the table, dbo.abc. Finally we need to display all the original and duplicated rows.

Now let us write a conventional SQL Server 2000 type program. Since I have to loop through the data in each row, I am ending up using a CURSOR and a WHILE loop. The T-SQL program is given below:

declare @l_SeqNo int
           ,@l_date_field datetime
           ,@l_Month_Count smallint
           ,@l_Payment decimal(10, 2)
           ,@l_counter smallint

select @l_counter = 0
set nocount on;
declare i_Cursor insensitive cursor
for
select SeqNo, Date_Field, Month_Count, Payment from dbo.abc

open i_Cursor

fetch next from i_Cursor into
                             @l_SeqNo
                            ,@l_date_field
                            ,@l_Month_Count
                            ,@l_Payment

while @@fetch_status = 0
begin
      select @l_counter = 0

      while (@l_counter < @l_Month_Count)
      begin
            insert into dbo.def (SeqNo, Date_Field, Payment)
            select @l_SeqNo, dateadd(mm, @l_counter, @l_date_field), @l_Payment

            select @l_counter = @l_counter + 1
      end

      fetch next from i_Cursor into
                                   @l_SeqNo
                                  ,@l_date_field
                                  ,@l_Month_Count
                                  ,@l_Payment
end
close i_Cursor
deallocate i_Cursor
set nocount off;
go

After executing the above program, let us check the table dbo.DEF:
select * from def

It may be noted in the result set above that the row with SeqNo 1 has been duplicated 10 times, 10 being the value of the Month_Count field in table dbo.abc. Similarly, the row with SeqNo 2 has been duplicated 7 times, 7 being the value of the Month_Count field in table dbo.abc. Finally the row with SeqNo 3 in the table dbo.abc has been duplicated 5 times, 5 being the value of the Month_Count field in table dbo.abc. We have kept the values of SeqNo and Payment columns intact through this duplication exercise.

The problem has been solved. Now let us solve the same problem in a different way using Recursive CTE in SQL Server 2005/2008 style of coding. The code is given below. please execute the same:

with CTE_Base (SeqNo, Date_Field, Month_Count, Payment, Begin_Date, End_Date, Frequency)
as
(select SeqNo, Date_Field, Month_Count, Payment, Date_Field, dateadd(mm, Month_Count-1, Date_Field), 1 from abc)
,CTE_Level1 (SeqNo, Date_Field, Month_Count, Payment, Begin_Date, End_Date, Frequency)
as
(select SeqNo, Date_Field, Month_Count, Payment, Begin_Date, End_Date, Frequency from CTE_Base
union all
select SeqNo, dateadd(mm, Frequency, Date_Field), Month_Count, Payment, Begin_Date, End_Date, Frequency
from CTE_Level1
where dateadd(mm, Frequency, Date_Field) between Begin_Date and End_Date)

select SeqNo, Date_Field, Payment
from CTE_Level1
where Date_Field between Begin_Date and End_Date
order by SeqNo, Date_Field
go

Here is the result set:

If we set "Show Actual Execution Plan" and then run both the queries (the cursor based one and the recursive CTE based one), you will note that the query cost of the conventional cursor based approach to this problem's solution is 94% where as it is 6% with the recursive CTE based solution.

It may also be noted that in the recursive CTE not a single variable has been used unlike the cursor based methodology.

The problem I have used in this article is just to solve a situation where new data is to be generated based on applying certain logic on existing data. In practice, there are and would be many more such situations where recursive CTE's can be put to good use for efficieny in execution and brevity of code.

Using Recursive CTE to Minimize Table Scans

2011-08-25T17:26:00.000-05:00

Company XYZ WORKS has four vendors. Each vendor is paid based on goods supplied by them to XYZ WORKS. Based on the payment history, the company wants to know the least non-zero amount that was paid to each vendor. If a vendor wasn't ever paid any amount, then that vendor should also fall in ths list. It is this extra requirement that adds a little complication to the task. Lets solve the problem now.

First, we would create a table called dbo.Payment_Details as following:

if exists (select 1 from sys.tables
           where Schema_NAME(schema_ID) = 'dbo'
           and   name = 'Payment_Details')
begin
   drop table dbo.Payment_Details
end;

create table dbo.Payment_Details
(Name                varchar(10)
,Amount             int
,Payment_Date smalldatetime)

Next, lets populate this table with relevant data:

insert into dbo.Payment_Details
values ('Veronica', '0', '20110201')
      ,('Veronica', '10', '20110301')
      ,('Veronica', '20', '20110401')
      ,('Veronica', '30', '20110501')
      ,('Tiffany', '40', '20110201')
      ,('Tiffany', '50', '20110301')
      ,('Tiffany', '60', '20110401')
      ,('John', '0', '20110201')
      ,('John', '70', '20110301')
      ,('John', '80', '20110401')
      ,('Anthony', '0', '20110201')

Lets see the contents of the table, dbo.Payment_Details:

select * from Payment_Details;

What we expect in the final solution is following:

Name             Amount
------------      ------------
Anthony         0
John               70
Tiffany           40
Veronica        10

Lets code some solutions:

Variation 1:

In this solution, we are using CTE but not recursively. In other words, a CTE does not reference itself. In this solution, the table, dbo.Payment_Details is being referenced twice, once per CTE.

with CTE_1 (Name, Amount)
as (select Name, min(Amount)
     from dbo.Payment_Details with (nolock)
   where Amount > 0
   group by Name)
,CTE_2 (Name, Amount)
as (select b.Name, b.Amount from dbo.Payment_Details b with (nolock)
    where b.Name not in (select Name from CTE_1)
     and b.Amount = 0
     union all
     select Name, Amount from CTE_1)

select * from CTE_2;

Variation 2:

In this solution, we are again using CTE but not recursively. In this solution, the table, dbo.Payment_Details is being referenced twice, both times in the first CTE.

with CTE_1 (Name, Amount, RowNumber)
as (select Name, Amount, row_number() over(Partition by Name order by Name, Amount) RowNumber
    from dbo.Payment_Details with (nolock)
    where Amount = 0
    union
    select Name, Amount, row_number() over(Partition by Name order by Name, Amount desc) RowNumber
    from dbo.Payment_Details with (nolock)
    where Amount > 0)
,CTE_2 (Name, Amount, RowNumber)
as (select Name, Amount, row_number() over(partition by Name order by Name, RowNumber desc) RowNumber
     from CTE_1)

select Name, Amount from CTE_2
where RowNumber = 1;

Variation 3:

In this solution, we are again using CTE but recursively. In other words, a CTE is referencing itself within itself. In this solution, the table, dbo.Payment_Details is being referenced only once.

with CTE_1 (Name, Amount, RowNumber)
as (select distinct Name, Amount, row_number() over(Partition by Name order by Name, Amount) RowNumber
     from (select Name, Amount from dbo.Payment_Details with (nolock)) a
   union ALL
   select Name, Amount, row_number() over(Partition by Name order by Name, RowNumber desc) RowNumber
    from CTE_1
     where RowNumber = 2)

,CTE_2 (Name, Min_Amt, Max_Amt, RowNumber)
as (select distinct Name, min(Amount) over(partition by Name, RowNumber) Min_Amt, MAX(Amount) over(partition   by Name, RowNumber) Max_Amt, RowNumber
     from CTE_1
     where RowNumber = 1)

SELECT Name, case
                              when Min_Amt = 0 then Max_Amt
                            else Min_Amt
                            end Amount
FROM CTE_2;

In this article, we have shown that Recursive CTE could be used effectively to reduce the number of Scans of the referenced table.

Calculating Cumulative percentages with CTE

2011-08-25T15:41:00.000-05:00

A company sells different products on the internet. For the sake of this example, lets say it sells bicycles, scooters and lawn mowers. Everyday, customers order different quantities of these three products. The company's capacity is to ship upto 25% of pending orders by sale amount of each product everyday. And the company wants to meet its capacity every weekday while making sure that customers are being served as per their order date starting with the earliest
pending order for a given product. For example, if there are pending orders worth $1000 for bicycles today, bicycles worth upto $250 should be shipped today in the order in which orders were placed by the customers. At the same time, orders should be shipped in full and not partially.

I am using just one table for this demo. It is not in the third normal form just to make the explanation simple. So lets create a base table with the relevant sample data. Please note that the code below is compatible with microsoft SQL Server 2008. With slight modifications, it can work in Microsoft SQL Server 2005 as well :

if exists (select * from sys.tables
           where Schema_NAME(schema_ID) = 'dbo'
           and   name = 'Order_details')
begin
   drop table dbo.Order_details
end;

create table dbo.Order_details
(Order_ID                     int identity(1, 1) primary key clustered with (fillfactor = 50, data_compression = page)
,Order_Date                  smalldatetime not null
,Product_Name             varchar(50) not null
,Unit_Price                    decimal(28,2) not null
,Quantity_Ordered         smallint not null
,Order_Value                 as (Quantity_Ordered * Unit_Price)
,Order_Value_As_Percent_of_Pending_Orders_By_Product decimal(28, 10) null
,CutOff_Value_By_Product                                                     decimal(28, 10) null
,Ship_It_Today              bit not null default (0)
,Shipping_Date              smalldatetime null)

Next, let us populate the table, dbo.Order_Details.

insert into dbo.Order_details (Order_Date, Product_Name, Unit_Price, Quantity_Ordered)
--10 orders for bicycles
values (GETDATE()-10 , 'Bicycle', 100.00, 1)
,(GETDATE()-9 , 'Bicycle', 100.00, 2)
,(GETDATE()-8 , 'Bicycle', 100.00, 3)
,(GETDATE()-7 , 'Bicycle', 100.00, 4)
,(GETDATE()-6 , 'Bicycle', 100.00, 5)
,(GETDATE()-5 , 'Bicycle', 100.00, 6)
,(GETDATE()-4 , 'Bicycle', 100.00, 7)
,(GETDATE()-3 , 'Bicycle', 100.00, 8)
,(GETDATE(), 'Bicycle', 100.00, 2)
,(GETDATE(), 'Bicycle', 100.00, 10)
--10 orders for scooters
,(GETDATE()-10 , 'Scooter', 300.00, 5)
,(GETDATE()-9 , 'Scooter', 300.00, 2)
,(GETDATE()-8 , 'Scooter', 300.00, 4)
,(GETDATE()-7 , 'Scooter', 300.00, 1)
,(GETDATE()-6 , 'Scooter', 300.00, 6)
,(GETDATE()-5 , 'Scooter', 300.00, 9)
,(GETDATE()-4 , 'Scooter', 300.00, 10)
,(GETDATE()-3 , 'Scooter', 300.00, 2)
,(GETDATE(), 'Scooter', 300.00, 15)
,(GETDATE(), 'Scooter', 300.00, 13)
--10 orders for lawn mowers
,(GETDATE()-10 , 'Lawn Mower', 500.00, 1)
,(GETDATE()-9 , 'Lawn Mower', 500.00, 2)
,(GETDATE()-8 , 'Lawn Mower', 500.00, 1)
,(GETDATE()-7 , 'Lawn Mower', 500.00, 2)
,(GETDATE()-6 , 'Lawn Mower', 500.00, 1)
,(GETDATE()-5 , 'Lawn Mower', 500.00, 2)
,(GETDATE()-4 , 'Lawn Mower', 500.00, 1)
,(GETDATE()-3 , 'Lawn Mower', 500.00, 1)
,(GETDATE(), 'Lawn Mower', 500.00, 5)
,(GETDATE(), 'Lawn Mower', 500.00, 3)

Lets read the contents of the table dbo.Order_Details:

select * from dbo.Order_details;

Our purpose is to mark the orders to be shipped today based on upto 25% of the pending order value for each product. This is accomplished by the code below:

With CTE_Base (Order_ID, Order_Date, Product_Name, Order_Value, RowNumber, Total_Value_of_Pending_Orders_By_Product, Order_Value_As_Percent_of_Pending_Orders_By_Product)
as
    (select Order_ID
              ,Order_Date
              ,Product_Name
              ,Order_Value
              ,ROW_NUMBER () over(partition by Product_Name order by Product_Name, Order_Date) RowNumber
              ,SUM(Order_Value) over(partition by Product_Name) Total_Value_of_Pending_Orders_By_Product
              ,convert(decimal(28, 15), convert(decimal(28, 15), Order_Value) / convert(decimal(28, 15), SUM(Order_Value) over(partition by Product_Name))) Order_Value_As_Percent_of_Pending_Orders_By_Product
     from dbo.Order_details with (nolock)
     where Ship_It_Today = 0)

--select * from CTE_Base

,CTE_Level1 (Product_Name, RowNumber, CutOff_Value_By_Product)
as
    (select a.Product_Name
               ,a.RowNumber
               ,(select SUM(b.Order_Value) from CTE_Base b
                 where b.Product_Name = a.Product_Name
                 and   b.RowNumber <= a.RowNumber) / a.Total_Value_of_Pending_Orders_By_Product
     from CTE_Base a)

--select * from CTE_Level1

update a
set a.Order_Value_As_Percent_of_Pending_Orders_By_Product = b.Order_Value_As_Percent_of_Pending_Orders_By_Product
   ,a.CutOff_Value_By_Product  = c.CutOff_Value_By_Product
   ,a.Ship_It_Today                      = case
                                                              when c.CutOff_Value_By_Product <= 0.26 then 1
                                                              else a.Ship_It_Today
                                                         end
   ,a.Shipping_Date                      = case
                                                              when c.CutOff_Value_By_Product <= 0.26 then GETDATE()
                                                              else null
                                                        end
from dbo.Order_details a
inner join CTE_Base b
on a.Order_ID = b.Order_ID
inner join CTE_Level1 c
on b.Product_Name = c.Product_Name
and b.RowNumber    = c.RowNumber
where a.Ship_It_Today = 0
and a.Shipping_Date is null;

Now lets see the orders that need to be shipped today. It may be noted that we have shipped upto 25% of the orders by value for each product today:

select * from dbo.Order_details;

Table_Sizes

2011-08-25T11:34:00.000-05:00

Introduction:

Designing databases is a common activity for most of us. Assigning proper datatypes to attributes within entities is a part of that. Once promoted to production when thousands of users pound these tables, the DBA usually keeps track of the growth trends of databases and tables within them. For a DBA, maintaining these statistics (apart from numerous others) is important to see how the application is doing, how the
user-response is varying and how the hardware is being utilized.

When an application slows down and the database response is poorer, there could be several contributing factors like bad database layout, sub-optimal database design, ill-designed application (no SP's/adhoc TSQL), ill-written stored procedures, improper allocation of CPU/memory, and a few more and several
combinations of them. A common problem that I as a DBA came across is inapropriate dataypes for fields during design phase but detected a few months after implementation. This is so rampant especially when a DBA is not part of the design or is hired after the fact. I would like to share one such case with you today.

Building up a scenario:

The first step for me is to get a list of a few of the largest tables (in terms of row counts) in a database. Then I need to get the size of these individual tables (on disc) as well. Having done that, I can figure out the largest tables in terms of rowcount and size on disc.

I would also like to see how many rows (on an average) reside on each data page allocated to the table. Let us call it "row density" for our discussion.

To get this data, I used to use different methods in Microsoft SQL Server 2000 and 2005. However in SQL Server 2008, I use the undocumented system function, sys.fn_PhysLocFormatter and the system stored procedure sp_MSForEachTable. I use them for a quick view of the database tables for my own benefit rather than to send a report to the CTO as these objects are unsupported.

select sys.fn_PhysLocFormatter (%%physloc%%) Rows_per_Page from Orders

The results look like the following:

Rows_per_Page
-------------------------
(1:90536:0)
(1:90536:1)
(1:90536:2)

Each row in the result set represents one row in Orders table. So there would be as many rows in this result set as there are rows in the table, Orders.

Let's describe the three portions in the result above: 1:90536:0. First portion, 1 is the id of the data file on which the page (90536) containing this row (first row = Row 0) exists. You may observe that rows numbered 1 and 2 in the result set above also reside on the same page, 90536. Please note the first row
contained in a page is the 0th row in the result set above as the counting starts from 0 onwards instead of from 1. In effect, there are 3 rows in the page 90536 for this table. So our row_density is 3.0.

Most of us are very familiar with the system stored procedure, sp_msforeachtable. It performs the same set of prescribed activities on all tables in a given database. As it has been discussed so often on the forums, I would skip its description here.

Now let's customize the usage of sys.fn_PhysLocFormatter and sp_msforeachtable for each table so that we can obtain more user-friendly results for analysis and action:

/*******************************
NAME : Table Size script.
Author : Suresh K Maganti
Purpose: Get Pages, pages per row, Table size for each table. Find large tables with low row-density.
********************************/
if OBJECT_ID('tempdb..#TEMP') is not null
drop table #TEMP

create table #TEMP (Table_Name varchar(100) null
,Pages int null
,Rows_Per_Page decimal(28,4) null
,Rows_in_Table as convert(decimal(28, 0), Pages * Rows_Per_Page)
,Table_Size_MB as convert(decimal(28, 0), (convert(decimal(28,2), Pages) * 8192.00) / (1024.00 * 1024.00)))

insert into #TEMP (Table_Name, Pages, Rows_Per_Page)
exec sp_msforeachtable 'select ''?'', count (Page_Number) Numer_of_Pages_in_Table, isnull(Avg (isnull(convert(decimal(28, 4), Rows_Per_Page), 0.0000)), 0.0000) Avg_Rows_Per_Page
from
(select substring(SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1, charindex('':'', SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1)-1) Page_Number, COUNT(1) Rows_Per_Page
from
(select sys.fn_PhysLocFormatter (%%physloc%%) File_Page_Row from ?) a
group by substring(SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1, charindex('':'', SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1)-1)) b'

select * from #TEMP
order by 2 desc, 3 asc

if OBJECT_ID('tempdb..#TEMP') is not null
drop table #TEMP

Here is the result set:

Table_Name Pages Rows_Per_Page Rows_in_Table Table_Size_MB
----------------------------------- --------------- ------------------------ ----------------------- ----------------------
[dbo].[Order_Details] 100021 35.0380 3511193 783
[dbo].[Order_Master] 3345 83.1629 278180 26
[dbo].[Customer_Details] 2376 526.0118 1249804 19
[dbo].[Customer_Master] 358 404.1006 144668 3
...

I have shown the partial result set above for our explanation.

From past designs and analyses, I generally mark tables with under 100 rows/page as problematic. I always try to find ways to bring this figure to 100 rows/page or more in consultaion with the application development teams. Sometimes it requires further normalization as well. In our example, Order_Details
table is our largest (~3.5 million rows) occupying ~783 MB of disc space. However, the row density is just around ~35. If we could increase the row density somehow, we could auotmatically bring down the table size on disc.

Let's see the table structure now:

Sp_help [Order_Details]

Column_name Type Computed Length Prec Scale Nullable

--------------------- ------------ ---------------- -------------- ---------- ----------- -----------------
Order_ID int no 4 10 0 no
Product_ID int no 4 10 0 no
Specifications nvarchar no 400 yes
Status varchar no 255 yes

Observations and verifications:

The column, [Specifications] has the datatype nvarchar(400). In our case, we do not store unicode values in this column as per the application team.
There is a composite clustered index on the columns (Order_ID, Product_ID) with fillfactor = 100.
There is no compression on this table or its index.
This is a pretty normalized table with just 4 columns.
This table is not partitioned.
Recommendations:

If we could change the dataype for the column, [Specifications] to varchar(400), we could reduce the storage space.
If we could compress the lone index and the table data, we could reduce the table size further.

Evaluation of recommendations:
Let us estimate our gains through page compression:

sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'Order_Details', @index_id = 1, @partition_number = 1, @data_compression = 'Page'

Here is the result:
object_name schema_name index_id partition_number size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB)
Order_Details dbo 1 1 983688 758784

That's almost a gain of 23% space. Let us estimate our gains through row compression:

sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'Order_Details', @index_id = 1, @partition_number = 1, @data_compression = 'Page'

Here is the result:
object_name schema_name index_id partition_number size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB)
Order_Details dbo 1 1 983688 969192

With row compression, our gain is around 1.5% only. So we could definitely benefit from Page compression in this case.

Implementation:
So here we go with the implementation:

--Change the datatype from nvarchar(400) to varchar(400)
alter table [dbo].[Order_Details] alter column [Specifications] varchar(400) null

--Compress the clustered index using PAGE compression:
alter index all on [dbo].[Order_Details] rebuild with (fillfactor = 95, Data_Compression = PAGE, maxdop = 1)

--Compress the table using PAGE compression (Not necessary if the above step is done):
alter table [dbo].[Order_Details] rebuild Partition = All with (Data_Compression = PAGE, maxdop = 1)

Testing:

Now let's run our script again to see the row density and table sizes:

/*******************************
NAME : Table Size script.
Author : Suresh K Maganti
Purpose: Get Pages, pages per row, Table size for each table. Find large tables with low row-density.
********************************/
if OBJECT_ID('tempdb..#TEMP') is not null
drop table #TEMP

create table #TEMP (Table_Name varchar(100) null
,Pages int null
,Rows_Per_Page decimal(28,4) null
,Rows_in_Table as convert(decimal(28, 0), Pages * Rows_Per_Page)
,Table_Size_MB as convert(decimal(28, 0), (convert(decimal(28,2), Pages) * 8192.00) / (1024.00 * 1024.00)))

insert into #TEMP (Table_Name, Pages, Rows_Per_Page)
exec sp_msforeachtable 'select ''?'', count (Page_Number) Numer_of_Pages_in_Table, isnull(Avg (isnull(convert(decimal(28, 4), Rows_Per_Page), 0.0000)), 0.0000) Avg_Rows_Per_Page
from
(select substring(SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1, charindex('':'', SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1)-1) Page_Number, COUNT(1) Rows_Per_Page
from
(select sys.fn_PhysLocFormatter (%%physloc%%) File_Page_Row from ?) a
group by substring(SUBSTRING(File_Page_Row, CHARINDEX('':'', File_Page_Row, 1) + 1, 100), 1, charindex('':'', SUBSTRING(File_Page_Row, CHARINDEX('':'',

File_Page_Row, 1) + 1, 100), 1)-1)) b'

select * from #TEMP
order by 2 desc, 3 asc

if OBJECT_ID('tempdb..#TEMP') is not null
drop table #TEMP

Here is the result set:

Table_Name Pages Rows_Per_Page Rows_in_Table Table_Size_MB
----------------------------------- ------------- ------------------------ ----------------------- ---------------
[dbo].[Order_Details] 51103 68.7081 3511190 399
[dbo].[Order_Master] 3345 83.1629 278180 26
[dbo].[Customer_Details] 2376 526.0118 1249804 19
[dbo].[Customer_Master] 358 404.1006 144668 3
...

Please note that for the table, [dbo].[Order_Details] the row density(rows per page) has gone up from 35 to 68 now. At the same time, the table size itself has come down from 783MB to 399MB, a saving of almost 50% disc space. But that is not all. Let's try to compare the statistics before and after our changes to the Order_Details table by simply getting its rowcount using the following code:

set statistics io on
set statistics time on
set statistics profile on

select COUNT(1) from dbo.Order_Details with (nolock)

set statistics io off
set statistics time off
set statistics profile off

Before changes:
Table 'Order_Details'. Scan count 3, logical reads 79825, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 500 ms, elapsed time = 247 ms.

After changes:

Table 'Order_Details'. Scan count 3, logical reads 51193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 483 ms, elapsed time = 241 ms.

You may note that the logical reads have come down by almost 36%. CPU time has reduced only marginally because there is a storage overhead due to compression. However reduction in logical reads enhances performance when this same table is joined with other tables.

Conclusion:

Required Design changes can be ascertained by our script.
Implemenation of optimal datatypes and appropriate compression level could reduce the disc space usage by around 50% and logical reads by around 36%. (Please note that these results could vary by tables depending on the datatypes and characteristics of the columns within the tables.)
Different studies show an increase in CPU utilization by 1% to 2% due to compression (not in our specific case though). So please do test and verify your improvements before implementation.

Database File Usage & Growth

2010-06-25T20:02:00.001-05:00

In this article, I would focus on finding empty claimable spaces within databases. Once found, the space can be claimed back using various methods which we would discuss in a later article.

The following script provides the details on a database, its data and log files, the respective filegroups which they logically belong to, the current fize size, the actual space used in the file and the growth factor for the file. I have used this code with the system stored procedure, sp_msforeachdb so as to gather this data for all databases on the server. It may be noted that the temporary table that is being used here, #temp_Data_Sizes is compressed. The next SELECT statement then displays those database files that have 50% or more free space in them. The TSQL code here is for Microsoft SQL Server 2008 and later.

if OBJECT_ID ('tempdb..#temp_Data_Sizes') is not null
drop table #temp_Data_Sizes
go

create table #temp_Data_Sizes
(DatabaseName varchar(255) not null
,Fully_Qualified_File_Name varchar(512) not null
,File_Type varchar(45) not null
,FileGroup varchar(25) not null
,Current_File_Size_GB varchar(25) not null
,Used_File_Space_GB varchar(25) not null
,Growth_Factor varchar(25) not null
,Constraint PK Primary Key clustered (DatabaseName, Fully_Qualified_File_Name) with (fillfactor = 50)
)
with (data_compression = Page)
go

set nocount on;
insert into #temp_Data_Sizes (DatabaseName, Fully_Qualified_File_Name, File_Type, FileGroup, Current_File_Size_GB, Used_File_Space_GB, Growth_Factor)
exec sp_msforeachdb ' use [?]
select DB_NAME(database_id) DatabaseName
,physical_name Fully_Qualified_File_Name
,case [type]
when 0 then ''Data''
when 1 then ''Log''
end +
case fileproperty (name, ''IsLogFile'')
when 0 then case fileproperty (name, ''IsPrimaryFile'')
when 0 then '' - Secondary Data File''
when 1 then '' - Primary Data File''
end
when 1 then ''''
end File_Type
,isnull(Filegroup_name (data_space_id), '''') FileGroup
,convert(decimal(28,2), (size * 8192.0) / (1024.0 * 1024.0 * 1024.0)) Current_File_Size_GB
,convert(decimal(28,2), (fileproperty (name, ''spaceused'') * 8192.0) / (1024.0 * 1024.0 * 1024.0)) Used_File_Space_GB
,case
when is_percent_growth = 0 then convert(varchar(20), convert(decimal(28, 4), (growth * 8192.0) / (1024.0 * 1024.0 * 1024.0))) + '' GB''
when is_percent_growth = 1 then CONVERT(varchar(3), growth) + ''%''
end Growth_Factor
from sys.master_files
where database_id = DB_ID ()'

select * from #temp_Data_Sizes
where 100.00 * ((CONVERT(Decimal(28, 4), Current_File_Size_GB) - CONVERT(Decimal(28, 4), Used_File_Space_GB)) / CONVERT(Decimal(28, 4), Current_File_Size_GB)) >= 50.00
and CONVERT(Decimal(28, 4), Current_File_Size_GB) > 0.0000
order by CONVERT(Decimal(28, 4), Current_File_Size_GB) desc

set nocount off;

if OBJECT_ID ('tempdb..#temp_Data_Sizes') is not null
drop table #temp_Data_Sizes
go

Eliminating Duplicate Rows - The PARTITION BY clause

2010-02-14T03:29:00.000-06:00

Often we come across situations where duplicate rows exist in a table. And a need arises to eliminate such duplicates/triplcates, etc. Microsoft SQL Server 2005 & of course 2008 offer a simple option of eliminating such duplicates. In SQL server 2000, a program to eliminate duplicates used to be a bit long. It is a combination of derived tables and Row_Number() Over() funtion in 2005 & 2008 that makes this elimination process so simple. Let's look into it from an example:

--create a table like then one below:

create table Emp_Details (Emp_Name varchar(10), Company varchar(15), Join_Date datetime, Resigned_Date datetime)
go

--Insert some values into this table. Ensure that you insert atleast one duplicate and one triplicate for an easier understanding of the help that the Row_Number() Over() feature offers us:

insert into Emp_Details (Emp_Name, Company, Join_Date, Resigned_Date)
values ('John', 'Software Inc', '20060101', '20061231')
,('John', 'Software Inc', '20060101', '20061231')
,('John', 'Software Inc', '20060101', '20061231')
,('John', 'SuperSoft Inc', '20070101', '20071231')
,('John', 'UltraSoft Inc', '20070201', '20080131')
,('John', 'ImproSoft Inc', '20080201', '20081231')
,('John', 'ImproSoft Inc', '20080201', '20081231')
,('Mary', 'Software Inc', '20060101', '20081231')
,('Mary', 'SuperSoft Inc', '20090101', '20090531')
,('Mary', 'SuperSoft Inc', '20090101', '20090531')
,('Mary', 'UltraSoft Inc', '20090601', '20100531')
,('Mary', 'UltraSoft Inc', '20090601', '20100531')

--Let's see the duplicates/triplicates, etc now using the old method:

select *, COUNT(*) from Emp_Details
group by Emp_Name, Company, Join_Date, Resigned_Date

Emp_Name Company Join_Date Resigned_Date Occurrences
--------- --------------- ----------------------- ----------------------- -----------
John ImproSoft Inc 2008-02-01 00:00:00.000 2008-12-31 00:00:00.000 2
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 3
John SuperSoft Inc 2007-01-01 00:00:00.000 2007-12-31 00:00:00.000 1
John UltraSoft Inc 2007-02-01 00:00:00.000 2008-01-31 00:00:00.000 1
Mary Software Inc 2006-01-01 00:00:00.000 2008-12-31 00:00:00.000 1
Mary SuperSoft Inc 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 2
Mary UltraSoft Inc 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000 2

--Although the table has twelve row, only seven are good ones. The remaining are duplicates or triplicates as we see from the Occurrences column above.

--A better was of seeing the duplicates & more is the query below where Row_Number() Over() has been used with teh Partition By clause:

select Emp_Name, Company, Join_Date, Resigned_Date, ROW_NUMBER() over (partition by Emp_Name, Company, Join_Date, Resigned_Date order by Emp_Name, Company, Join_Date, Resigned_Date) RowNumber from Emp_Details

Emp_Name Company Join_Date Resigned_Date RowNumber
---------- --------------- ----------------------- ----------------------- ----------
John ImproSoft Inc 2008-02-01 00:00:00.000 2008-12-31 00:00:00.000 1
John ImproSoft Inc 2008-02-01 00:00:00.000 2008-12-31 00:00:00.000 2
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 1
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 2
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 3
John SuperSoft Inc 2007-01-01 00:00:00.000 2007-12-31 00:00:00.000 1
John UltraSoft Inc 2007-02-01 00:00:00.000 2008-01-31 00:00:00.000 1
Mary Software Inc 2006-01-01 00:00:00.000 2008-12-31 00:00:00.000 1
Mary SuperSoft Inc 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 1
Mary SuperSoft Inc 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 2
Mary UltraSoft Inc 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000 1
Mary UltraSoft Inc 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000 2

--Wherever RowNumber is greater than 1, its is a duplicate row.

--Now lets remove the duplicates/triplicates in one query in an efficient way using Row_Number() Over() using Partition By clause:

delete from a
from
(select Emp_Name, Company, Join_Date, Resigned_Date, ROW_NUMBER() over (partition by Emp_Name, Company, Join_Date, Resigned_Date order by Emp_Name, Company, Join_Date, Resigned_Date) RowNumber from Emp_Details) a
where a.RowNumber > 1

--Let's verify:

select *, COUNT(*) Occurrences from Emp_Details
group by Emp_Name, Company, Join_Date, Resigned_Date

Emp_Name Company Join_Date Resigned_Date Occurrences
--------- --------------- ----------------------- ----------------------- ----------
John ImproSoft Inc 2008-02-01 00:00:00.000 2008-12-31 00:00:00.000 1
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 1
John SuperSoft Inc 2007-01-01 00:00:00.000 2007-12-31 00:00:00.000 1
John UltraSoft Inc 2007-02-01 00:00:00.000 2008-01-31 00:00:00.000 1
Mary Software Inc 2006-01-01 00:00:00.000 2008-12-31 00:00:00.000 1
Mary SuperSoft Inc 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 1
Mary UltraSoft Inc 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000 1

The duplicates/triplcates are gone.

A slightly more involved way of eliminating them would have been using Row_Number() Over() without the Partition By clause. Please remember to reload the table, Emp_Details before proceeding further. The code below shows the extra rows that need not exist because they are either duplicates or triplicates. Note that in this query, Partition By has not been used:

select a.* from
(select Emp_Name, Company, Join_Date, Resigned_Date, ROW_NUMBER() over (order by Emp_Name, Company, Join_Date, Resigned_Date) RowNumber from Emp_Details) a
inner join
(select Emp_Name, Company, Join_Date, Resigned_Date, ROW_NUMBER() over (order by Emp_Name, Company, Join_Date, Resigned_Date) RowNumber from Emp_Details) b
on a.Emp_Name = b.Emp_Name
and a.Company = b.Company
and a.Join_Date = b.Join_Date
and a.Resigned_Date = b.Resigned_Date
where a.RowNumber = b.RowNumber + 1

Emp_Name Company Join_Date Resigned_Date RowNumber
---------- --------------- ----------------------- ----------------------- ----------
John ImproSoft Inc 2008-02-01 00:00:00.000 2008-12-31 00:00:00.000 2
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 4
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 5
Mary SuperSoft Inc 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 10
Mary UltraSoft Inc 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000 12

--We can delete them as follows as well:

delete from a
from
(select Emp_Name, Company, Join_Date, Resigned_Date, ROW_NUMBER() over (order by Emp_Name, Company, Join_Date, Resigned_Date) RowNumber from Emp_Details) a
inner join
(select Emp_Name, Company, Join_Date, Resigned_Date, ROW_NUMBER() over (order by Emp_Name, Company, Join_Date, Resigned_Date) RowNumber from Emp_Details) b
on a.Emp_Name = b.Emp_Name
and a.Company = b.Company
and a.Join_Date = b.Join_Date
and a.Resigned_Date = b.Resigned_Date
where a.RowNumber = b.RowNumber + 1

--Verify:

select *, COUNT(*) Occurrences from Emp_Details
group by Emp_Name, Company, Join_Date, Resigned_Date

Emp_Name Company Join_Date Resigned_Date Occurrences
--------- --------------- ----------------------- ----------------------- ----------
John ImproSoft Inc 2008-02-01 00:00:00.000 2008-12-31 00:00:00.000 1
John Software Inc 2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 1
John SuperSoft Inc 2007-01-01 00:00:00.000 2007-12-31 00:00:00.000 1
John UltraSoft Inc 2007-02-01 00:00:00.000 2008-01-31 00:00:00.000 1
Mary Software Inc 2006-01-01 00:00:00.000 2008-12-31 00:00:00.000 1
Mary SuperSoft Inc 2009-01-01 00:00:00.000 2009-05-31 00:00:00.000 1
Mary UltraSoft Inc 2009-06-01 00:00:00.000 2010-05-31 00:00:00.000 1

--The duplicates/triplicates are gone.

But note that difference in the two methods. Although Row_Number() Over() has been used in both the methods, Partition By has been used only in the first method.

The partition By clause was more efficient in the following way:

a.] Need to scan the Emp_Details table only once.
b.] Need to have only one derived table instead of two.
c.] No need to join two derived tables.
d.] Less CPU & Memory utilization.

Doing the same in Microsoft SQL Server 2000 and earlier would have been quite a lenghy code which I don't want to write now. However, I want to stress the contribution of the Partition By clause in keeping the code shorter , faster and less demanding on resources.

The RECOMPILE Options

2009-05-29T15:26:00.000-05:00

Recompile Options:
WITH RECOMPILE [at a Stored Procedure level only]
Option (Recompile) [at a TSQL statement level only]

Their functionality:
When used in the code of a particular Stored procedure, Option 1compiles that SP everytime it is executed by any user. It can not be used at a individual statement level. Any existing plan is never reused even if the new plan is exactly the same as any pre-existing plan for that SP. Once the SP is executed, the plan is discarded immediately. There is no caching of the execution plan for future reuse.

When used with a TSQL statement whether inside an SP or adhoc, Option 2 above creates a new execution plan for that particular statement. Any pre- existing plan even if it is exactly the same as the new plan, is not used. The new plan is discarded imeediately after execution of the statement.

General Plan Reuse in brief:
SP:
Assuming both of these options are not being used, an execution of an SP prompts a search for pre-existing plans (one serial plan and one parallel plan) in memory (plan cache). If found, it is reused. New input parameters in the current execution replace the previous input parameters from a previous execution plan in the execution context handle which is part of the overall execution plan. So usage of different input parameters doesn't cause a recompilation. Recompilation happens only when about 20% of the data in the tables being called from within the SP is found to have changed since the last time statistics was updated for those tables and its indexes. If say, you are accessing four tables in that SP and roughly 20% of the data for one table has been found to have changed since last statistics update, then that statement is recompiled. If all four tables have changed by about 20% since last statistics update, then the entire SP is recompiled. This plan reuse system works as long as objects are qualified with it owner and DatabaseName (for e.g. DBName.dbo.TableName instead of just TableName). Since we have auto-update statistics off, we have less recompilation of SP's to begin with. Use of a temporary table within an SP also causes recompilation of that statement. There are other factors also that affect plan reuse but that theory is too detailed. So I have just mentioned the most relevant points. Please note that while searching for an existing execution plan, SQL server searches by the ID of the SP being currently submitted for execution. So the search for existing execution plans is faster in case of SP's.

Adhoc TSQL:

Adhoc TSQL statements follow the same rules (statistics, etc) as an SP regarding plan reuse and some more. An adhoc SQL statement doesn't have an ID to begin with since it is not a database object. So existing plans are searched by the statement structure and the objects contained in that statement. So it takes a bit longer for SQL Engine to search for existing reusable plan in case of adhoc TSQL statements. Any existing execution plan in order to be reused is matched with the previous similar statement in the plan cache referencing the same object(s) (like tables). For e.g. the second statement below would reuse the plan created by the first statement. This is due to a database setting called "Simple Parameterization" which is ON by default (it treats the constant in the WHERE clause as a parameter and just switches the parameter value from 1 to 2 in the execution context of the existing plan created by the first statement). The third statement would also reuse the existing plan. The fourth statement below (it has the exact same functionality as the first statement) will not reuse the existing plan because the structure of the physical query is not exactly the same as the first query (extra spaces before the keyword WHERE). So the fourth statement would be compiled afresh and will have a new execution plan.

select a, b from db.user.abc where c = 1

select a, b from db.user.abc where c = 2

select a, b from db.user.abc where c = @input_parameter

select a, b from db.user.abc where c = 1

Again, if the statement is a bit complex unlike the statements above, even Simple Parameterization won't help and the statement would always generate a fresh execution plan unless the database option called "Forced Parameterization" is set. That again is a different ballgame altogether. The rules for exact match of the complex adhoc TSQL statement still hold good though.

Explanation:
SP:
It is for the reasons given in the theory above that RECOMPILE clause at an SP level should not be used unless we have just dropped and recreated/changed indexes, updated statistics, etc or if it is a new SP in which case it would be automatically generate a fresh plan anyway. Another drawback is that this SP-level RECOMPILE clause would erase any previous execution plan and create a new plan on each execution which is unnecessary and expensive.

AdHoc TSQL:
Using the RECOMPILE option would discard any reusable plans. But most of the existing plans do not get reused in case of adHoc TSQL due to reasons mentioned above. So plan cache ends up holding those un-reusable plans unnecessarily till the time comes when they need to be really discarded to make space for new plans in the procedure cache. Usage of the RECOMPILE option would help reduce the utilization of plan cache in such cases. But again the choice is between RECOMPILE option in adhoc TSQL or, the basic rules to be followed (same structure, same columns, same tables, fully qualified names, etc) for plan reuse. Changing an SP is simple. Changing an adhoc TSQL statement means making changes to the application as well that generates the adhoc TSQL code and recompiling the affected application dll's in most cases. So SP is always the best choice not only for this reason but for scores of other reasons as well whcih we can discuss in a separate thread.

Note: I would like to stress the point. that the Data Access Layer (DAL) would never pose a limitation on the ability to use SP's. If you can call even just one SP through DAL, you can call any number and any type of SP's from the same DAL whether the SP's use static or dynamic TSQL code inside them. DAL can not be blamed under any circumstance for excess usage of adhoc TSQL code in the application.

A slight clarification:
Once these two options are used (SP level and adhoc statement level), there would be no scope for reusing any "good" plans. This is because plans won't exist after an execution is over. They would be discarded immediately after the execution.

Caution:
Developers often tend to use OPTION (RECOMPILE) an an excuse for introducing more and more adhoc TSQL and then tend to eliminate SP's altogether. And then they reach a stage where plan cache gets filled up to the brim again. This defeats the very purpose of Option (recompile) for adhoc TSQL code. Please note that every recompile issues locks on the underlying tables. And every new plan generation process takes an additional time and overhead (CPU time and statistics analysis). Adhoc TSQL reflects/implies inappropriate application design. It also increases network roundtrips and length/duration of a business logic implementation and execution. OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc TSQL code to Stored Procedures is underway.

Suggestion/Conclusion:
Based on the explanation above, using WITH RECOMPILE clause for SP's is not suggested. Using OPTION (RECOMPILE) at a statement level can be tried for adhoc TSQL. The effort in changing this for each adhoc TSQL statement in the application can be of course more productive instead if conversion from adhoc TSQL to Stored Procedures is taken up. This is because SP's are generally much superior to adhoc TSQL statements in all respects.

Database Mirroring in Microsoft SQL Server 2005: The finer points

2008-11-13T10:13:00.001-06:00

I have often wondered why people still use features like log-shipping, transactional replication, etc for their disastery recovery solutions rather than use simpler options. A software DR solution should be easy to implement, be effective and safe as well while ensuring near zero or zero data loss by itself.

Some drawbacks of log-shipping are following:
a.] You need frequent log backups of the production database.
b.] The recoverability of your DR database depends largely on the frequency of transaction log backups and the time taken to copy such backups from production to DR and the time taken to restore these backups.
c.] The log-shipped database remains dormant except having production transaction log backups getting restored on to it.
d.] From an application end-user perspective, the DR database is useful only after a disaster, not anytime else.
e.]The entire transaction log backup is restored at DR and then Uncommitted transactions are rolled back (which is fine). It is not that only committed transactions go from production to DR. So there is unnecessary avoidable network traffic.
f.] The physical transaction log backup file has to be copied to the DR causing avoidable network traffic and causing pressure on the DPC [NIC] CPU's.
h.] With log-shipping, there is always a scope for data loss in case of disasters. And companies do compromise on this front which is not needed.
i.] Too many jobs are in place to run log-shipping (to backup logs at source, copy them to DR and restore them at DR).
j.] Too many time-consuming processes involved when failure occurs especially if the database involved in huge (VLDB).
k.] Failover is always manual.
l.] Even if the data loss is of 10 minutes, the actual downtime would be atleast an hour at a minimum. This directly means loss of revenue for that period.
m.] Manual user redirection in case of failure of the main server/database is needed.
n.] Restored transaction log backup files on DR have to be deleted. Continuous copying and deleting of files make the DR look like a file server and of couse, causes the discs over there to get fragmented much faster.

Drawbacks of Replication:
a.] Complex process to set up involving snapshot replication, etc to begin with.
b.] In certain types of replication, an extra column (GUID) is created at the end of each replicated table causing an increase in disc space usage.
c.] Complex conflict resolution rules have to be put in place during setup.
d.] A failure in replication could actually mean reinitialization of subscriptions most of the time. That's a time-consuming process and means that DR would not be technically existing during that period if replication is being used for DR purposes.
e.] Two things to manage instead of one. You need to manage Production database as well as the replication for failures, conflicts, maintenance, etc.
f.] Needs some skill to manage replication and its own set of problems.
g.] Of course there are certain merits in replication like updatable subscriptions, merge replication, bi-directional transactional replication, availability of the subscriber database for reads/writes, etc. But the drawbacks above make replication a more tediuos and time-consuming operation/process than necessary.
h.] Manual failover and user redirection in case of failure of the main server/database.

What is the easiest alternative? Database Mirroring is. Why? Because of the following reasons:

a.] Easy to set up, easier still if log-shipping is already in place and needs to be replaced with database mirroring.
b.] Easy to monitor. Inbuilt Microsoft Database Mirroring Monitor in SQL Server Management Studio.
c.] Manual or automatic failover.
d.] Separate counters in Windows Performance Monitor for database mirroring.
e.] Dynamic Management Views for up to the point-in-time information on the principal and mirror databases, lag time between mirror and principal and so on.
f.] Minimal or no data loss even in asynchronous mode.
g.] Guaranteed zero-data-loss in synchronous mode.
h.] Possibility of automatic user redirection in case of failures.
i.] Hardly requires maintenance. Set it up and forget it.
j.] Easy to failover. It's a one line code to failover and failback.
k.] Makes other maintenance jobs like Windows patching and SQL Server patching easy with no downtime.
l.] Possibility to use mirror for reporting application using the snapshot feature.
m.] Requires least amount of maintenance of all the DR options.
n.] Easy to use.
o.] Easy to change between different modes of mirroring (synchronous and asynchronous).
p.] Only committed transactions flow to mirror. So less network banmndwidth consumed.
q.] No file transfers involved.
r.] No conflict resolutions needed.
s.] Miniam disc fragmentation as no copying and deleting fo files is involved.
t.] Maintenance actions on principal database automatically move over to mirror. I am talking of operations like shrinking log/data files, etc.

I am not going into the set up of mirroring as it is simple, easy to use, and easy to code. In roughly 10-15 lines of TSQL code, mirroring can be established in the desired mode.

Some points which I would consider important with respect to mirroring are as follows:

Do not install mirror database in a different node of the same cluster where principal resides. This would be a disastrous approach in an active-active cluster when one node has to failover to the other. Always keep you mirror database on a separate cluster or machine if you don't have a cluster.

Do not keep the transaction log file size unnecessarily large. If say your peak transaction log file consumption is 5GB for example, it would be good to have the actual file size within 10GB to 20GB. Don't keep it at say 50GB. That slows down writes to the transaction log file on principal and hence also on mirror. The point is don't keep too much empty space in the transaction log file on principal. Expand it as needed.

Don't create full or differential database backups on principal when you are in the process of restoring logs on to the mirror database to bring it upto speed with the principal database. If by mistake that does happen, stop any scheduled transaction log backups on principal and let the mirror sync up wth principal on its own although it would take a few minutes for the mirror to catch up and get synchronized with the principal this way. While the catching-up process is going on, it would be better to stop transaction log backups on the principal as well. That is okay. Patience helps in this avoidable scenario. After the synchronization is established, turn on the jobs for transaction log backups, full backups and differentail backups back on the principal. Please do consider this point during the setup process for database mirroring just in this avoidable case where somebody performs a full or differential database backup of the principal database by mistake while you are setting up database mirroring.

Start with asynchronous mode to begin with. Observe for a few days how the transactions are doing, whether there is any piling-up of log on mirror and how often the entities are not in sync per day. If you find synchonization 90% of the time, you can consider moving on to synchronous mode if need be.

I would suggest using TSQL code instead of the GUI for the setup. There is a lot more flexibility in TSQL coding than in the GUI. And it is a lot easier to debug/rollback/comeback/startover with TSQL during the setup process if something goes really bad due to circumstances beyond your control. Mirroring is so straightforward that it would not go wrong if you are following the 10-11 simple steps to setting up mirroring. The GUI is good for monitoring though and for setting up alerts.

Do not use synchronous mode if the CPU utilization is more than 50% on your principal server averaged for a given hour. This can potentially cause unwanted automatic failovers. In such a case, either set right you application / database code/architecture/design/indexes, etc first or use asynchronous mode of mirroring only.

suresh_m_kumar@hotmail.com

A Tricky Scenario with T-SQL

2008-09-04T10:12:00.000-05:00

The good part of T-SQL is that unlike most other areas of databases in general, one needs to think in order to write a good piece of valid code. Thinking not just to establish functionality but also to ensure performance. Now there are hundreds, even thousands of scenarios where the code can be better than what it has been already written as just to establish functionality. Let us take up one such scenario.

There is a supplier of Dell, HP, and IBM servers. Servers are shipped from the warehouse to three locations. Each location is represented by a location code. Each machine to be shipped to the warehouse has a ten digit Machine_X_Code that has leading zeroes for data that has less than ten digits in it. All this is represented roughly by the Main_Table below:

create table Main_Table
(Location_Code smallint not null
,Machine_Brand varchar(25) not null
,Machine_X_Code varchar(10) not null
,Number_Of_CPUs tinyint not null
,Procurement_Date Datetime not null default GETDATE()
constraint PK_LC_MC Primary Key Clustered (Location_Code, Machine_X_Code, Procurement_Date) with fillfactor = 70)
go

There is another table called Code_Generation_Table. This table determines what the Machine_X_Code for the next procured machine would be. For a given combination of store location code and a machine brand in this Code_Generation_Table, the Machine_X_Code for the Main_Table needs to be generated. The new machine_X_Code generated should be within the Start_code and End_Code range for that Location_Code and Machine_Brand. The table is as follows:

create table Code_Generation_Table
(Location_Code smallint not null
,Machine_Brand varchar(25) not null
,Start_Code int not null
,Current_Code int not null
,End_Code int not null
,Increment_Value tinyint not null
constraint PK_LC_MB Primary Key clustered (Location_Code, Machine_Brand) with fillfactor = 90)
go

Lets populate these two tables now with relevant data:

insert into Code_Generation_Table
select 1, 'DELL', 1001, 1001, 1005, 1
union all
select 2, 'DELL', 4001, 4001, 4005, 1
union all
select 3, 'DELL', 7001, 7001, 7005, 1
union all
select 1, 'HP', 2001, 2003, 2005, 1
union all
select 2, 'HP', 5001, 5003, 5005, 1
union all
select 1, 'IBM', 3001, 3001, 3005, 1
union all
select 2, 'IBM', 8001, 8003, 8005, 1
union all
select 3, 'IBM', 6001, 6004, 6005, 1
union all
select 3, 'SUN', 6011, 6011, 6015, 1

go

insert into Main_Table (Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs)
select 1, 'DELL', '0000001001', 2
union all
select 1, 'DELL', '0000001002', 4
union all
select 1, 'DELL', '0000001003', 8
union all
select 1, 'DELL', '0000001004', 16
union all
select 1, 'HP', '0000002002', 2
union all
select 1, 'HP', '0000002003', 4
union all
select 1, 'HP', '0000002004', 8
union all
select 1, 'HP', '0000002005', 32
union all
select 1, 'IBM', '0000003003', 2
union all
select 1, 'IBM', '0000003004', 8
union all
select 1, 'IBM', '0000003005', 32
union all
select 2, 'DELL', '0000004001', 2
union all
select 2, 'DELL', '0000004002', 4
union all
select 2, 'DELL', '0000004003', 8
union all
select 3, 'DELL', '0000007004', 16
union all
select 2, 'HP', '0000005002', 2
union all
select 2, 'HP', '0000005003', 4
union all
select 2, 'HP', '0000005004', 8
union all
select 2, 'HP', '0000005005', 32
union all
select 3, 'IBM', '0000006001', 2
union all
select 3, 'IBM', '0000006003', 2
union all
select 3, 'IBM', '0000006004', 8
union all
select 3, 'IBM', '0000006005', 32
Go

Here are the conditions that must be met for generating the Machine_X_Code:
1. Start from Current_Code value in the table, Code_Generation_Table.
2. Look for any values between Current_Code and End_Code that don't already exist in
the Machine_X_Code column of the Main_Table for the given Location_Code and
Machine_Brand. That becomes the new Machine_X_Code.
3. Given a scenario like the one below in the table Code_Generation_Table, the following conditions must also be met:
Location_Code, Machine_Brand, Start_Code, Current_Code, End_Code, Increment_Value
1 DELL 1001 1003 1005 1

Scenario 1:

If there is no record in Main_Table for that Machine Brand, then new Machine_X_Code needs to be 0000001003.

Scenario 2:

If Main_Table has records with Machine_X_Codes 0000001003, 0000001004, 0000001005 then the new Machine_X_Code will be 0000001001.

Scenario 3:

If Main_Table has all records with Machine_X_Code between and including 0000001001 to 0000001005 except 0000001002, then the next newly generated Machine_X_Code should be 0000001002.

The requirements having been made clear, let us see how a developer approached this problem. He chose to create a stored procedure (SP), usp_Generate_Machine_X_Code and a user-defined function (UDF), F_Validate_Machine_Code whihc is invoked from within the SP. The SP gives out the new Machine_X_Code. The unnctionality of the code below is explained in more granular detail just before the object definitions below. The code for both is given below.

Code for the user-defined function (UDF), F_Validate_Machine_Code:

--UDF
--This function checks that thet newly generated Machine_X_Code lies between
--the Start_Code and the End_Code. If it does, then the newly generated Machine_X_Code
--represented by the variable @l_Temp_Machine_X_Value is valid. If by chance, the newly
--generated Machine_X_Code lies outside the range of Start_Code and End_Code, then
--the new machine_x_code is initialized to the Start_Code generated Machine_X_Code
--reprsented by the variable, @l_Start_Machine_X_Value. The newely generated
--machine_X_code, the Start_Code and the ENd_Code are inputs to this function.
--A supposedly valid Machine_X_Code is returned by this function. The output however
--is not guarenteed to be valid unless it is re-checked for validity in the SP,
--usp_Generate_Machine_X_Code. If it is found to be invalid, then this
--user-defined function is invoked again and again as need be.

CREATE Function DBO.F_Validate_Machine_Code
(@l_Temp_Machine_X_Value Int,
@l_Start_Machine_X_Value int,
@l_End_Machine_X_Value int
) Returns Integer
AS
Begin
-- This function validates the whether the newly generated Machine_X_Code lies within the Range of Start_Code and End_Code.
If (@l_Temp_Machine_X_Value > @l_Start_Machine_X_Value)
or (@l_Temp_Machine_X_Value < @l_End_Machine_X_Value)
Set @l_Temp_Machine_X_Value = @l_Temp_Machine_X_Value

If (@l_Temp_Machine_X_Value < @l_Start_Machine_X_Value)
or (@l_Temp_Machine_X_Value > @l_End_Machine_X_Value)
Set @l_Temp_Machine_X_Value = @l_Start_Machine_X_Value

Return @l_Temp_Machine_X_Value
End
GO

Code for the stored procedure (SP), usp_Generate_Machine_X_Code:

--This SP's purpose is to generate a new Machine_X_Code. It generates a new
--Machine_X_Code from the Current_Code column of the table, Code_Generation_Table
--while checking that the newly generated Machine_X_Code does not already exist
--in the Machine_X_Code column of the table Main_Table. In addition it call the
--user-defined function F_Validate_Machine_Code to check that a newly generated
--Machine_X_Code lies between Start_Code and End_Code in the table, Code_Generation_Table.
--If the newly generated machin_x_code falls outside that range, then the machine_x_code
--is reset to Start_Code. In this whole process it keeps updating the Current_Code column
--of the table, Code_Generation_Table with the newly generated Machine_X_Code before its
--validation through the function, F_Validate_Machine_Code. It also updates the the
--Current_Code column of the table, Code_Generation_Table with the next possible
--Machine_X_Code value. Looping occurs till a valid Machine_X_Code is found. So,
--effectively there would be as many iterations of the loop as the number of attempts to
--find one good Machine_X_Number.

Create Procedure usp_Generate_Machine_X_Code
@i_Location_Code Smallint,
@i_Machine_Brand VarChar(10),
@o_Machine_X_Code Integer OutPut,
@o_Return_Value Smallint OutPut
As
Begin
Set nocount on
Set @o_Return_Value = 0

Declare @l_Increment_Value Smallint,
@l_Temp_Machine_X_Value Integer,
@l_Next_Machine_X_Value Integer,
@l_Start_Machine_X_Value Int,
@l_End_Machine_X_Value Integer,
@l_First_Machine_X_Value_Taken Integer,
@l_Affected_Rows Smallint,
@l_Execution_String VarChar(2000)

Select @l_Temp_Machine_X_Value = Current_Code
,@l_Increment_Value = Increment_Value
,@l_Start_Machine_X_Value = Start_Code
,@l_End_Machine_X_Value = End_Code
From DBO.Code_Generation_Table With (NoLock)
Where Location_Code = @i_Location_Code
And Machine_Brand = @i_Machine_Brand

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value

Set @l_Affected_Rows = 0

While @l_Affected_Rows = 0
Begin

Set @l_Next_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Next_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Next_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_Execution_String = 'Update A Set A.Current_Code = ' + Cast(@l_Next_Machine_X_Value as VarChar) +
' From Dbo.Code_Generation_Table A '+
+ ' Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) + ' And Machine_Brand = ' + ''''+ @i_Machine_Brand + '''' +
' And Not Exists (Select ' + ''''
+ 'x' + '''' + ' From Main_Table ' +
'Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) +
' AND Machine_Brand = ' + '''' + @i_Machine_Brand + '''' +
' And Machine_X_Code = ' + '''' + Replicate('0',10 - Len(@l_Temp_Machine_X_Value)) + Cast(@l_Temp_Machine_X_Value as Varchar(10)) + '''' + ')'

Exec (@l_Execution_String)

Set @l_Affected_Rows = @@Rowcount

If @l_Affected_Rows = 1
Begin
Select @o_Machine_X_Code = @l_Temp_Machine_X_Value, @o_Return_Value = 1
Break
End

Set @l_Temp_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

If @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value
Begin
-- For the current day all the Machine X Codes have been used up.
Select @l_Affected_Rows = 2, @o_Return_Value = -1
Break
End

End
Set nocount off
End
GO

Before we proceed further, please familiarize yourself with the logic of the SP and UDF, and the data in the two tables so that we can try to execute his logic. Let us try to generate a new Machine_X_Code for the Location_Code = 1 and Brand_Name = 'Dell'.

TEST 1:
Execute the following:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 1
and Machine_Brand = 'Dell'

The result set is as follows:

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
1 DELL 0000001001 2
1 DELL 0000001002 4
1 DELL 0000001003 8
1 DELL 0000001004 16

So the next generated machine_x_code to has to be 1005. Remember zeroes are suffixed later and that is not so important right now. What is important is generating the new Machine_X_Code that meets all the requirements above.

Now run the following code:

select * from Code_Generation_Table

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1
1 HP 2001 2003 2005 1
1 IBM 3001 3001 3005 1
2 DELL 4001 4001 4005 1
2 HP 5001 5003 5005 1
2 IBM 8001 8003 8005 1
3 DELL 7001 7001 7005 1
3 IBM 6001 6004 6005 1
3 SUN 6011 6011 6015 1

Since the codes (1001, 1002, 1003 and 1004) already exists in the Main_Table for Location_Code =1 and Machine_Brand = 'Dell', the next one has to be 1005 as it exists in the Code_Generation_Table between Start_Code and End_Code. Let's see if the SP generates this machine code. Execute the following code to execute the SP:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 1
,@i_Machine_Brand = 'Dell'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
1005 1

We got the exact machine code. Now let's check the table Code_Generation_Table for the column Current_Code by executing the following code:

select * from Code_Generation_Table
where Location_Code = 1
and Machine_Brand = 'Dell'

The result set is as follows:

Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1

You may note that the Current_Code has been updated. It has actually been initialized with the next possible Current_Code because 1005 has been taken by us via execution of the SP. So the next available value can be 1006. But since 1006 is outside the range of 1001 and 1005, the next possible value has to be re-initialized to the start_code which is 1001. So this condition has also been satisfied. Very well.

TEST 2:
Now let's test another condition:

Scenario 1:

For Location_Code = 2 and Machine_Brand = 'IBM', there is no record in the Main_Table. Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

(0 rows affected)

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8003 8005 1

So as per the given condition, the new machine_x_code shoudl be 8003 and the Current_Code column of the table, Code_Generation_Table should get updated with 8004. So let's execute the SP from the developer by executng the following code:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
8003 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8004 8005 1

So the new Machine_X_Number generated in 8003 which is perfect. And the Crrent_Code column has also been updated to the next possible Machine_X_Number which is 8004. So the Scenario 1 has been satisfied.

TEST 3:
Let's check out if Scenario 2 is satisfied:

For Location_Code = 2 and Machine_Brand = 'HP', there are four records in the Main_Table with machine codes (0000005002, 0000005003, 0000005004 and 0000005005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'HP'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
2 HP 0000005002 2
2 HP 0000005003 4
2 HP 0000005004 8
2 HP 0000005005 32

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5003 5005 1

So as per the given condition for Scenario 2, the new machine_x_code generated should be 5001 and the Current_Code column of the table, Code_Generation_Table should get updated with 8002. So let's execute the SP from the developer by executng the following code:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'HP'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
5001 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5002 5005 1

So the new Machine_X_Number generated in 5001 which is perfect. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 5002. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exists within the range of Start_Code and End_Code. So the Scenario 2 has been satisfied.

TEST 4:
Let's check the Scenario 3, the final scenario:

For Location_Code = 3 and Machine_Brand = 'IBM', there are four records in the Main_Table with machine codes (0000006001, 0000006003, 0000006004 and 0000006005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
3 IBM 0000006001 2
3 IBM 0000006003 2
3 IBM 0000006004 8
3 IBM 0000006005 32

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6004 6005 1

So as per the given condition for Scenario 3, the new machine_x_code generated should be 6002 and the Current_Code column of the table, Code_Generation_Table should get updated with 6003. So let's execute the SP from the developer by executng the following code:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 3
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
6002 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6003 6005 1

So the new Machine_X_Number generated in 6002 which was the missing code in Main_Table for Location_Code = 3 and Machine_Brand = 'IBM'. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 6003. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exist within the range of Start_Code and End_Code. So the Scenario 3 has been satisfied.

The developer has satisifed all the functional requirements. Good job. Now what are the drawbacks in his code? Let's enlist them:

1. He uses an SP and a UDF, two database objects for one purpose. This is a minor drawback though.
2. Looping is being used. In a rare case, it may have just one iteration. But in almost all cases it would have multiple iterations upto a maximum of five iterations. We have a simple set of scenarios here. If the range between Start_Code and Stop_Code runs into several hundreds, the looping will happen those many times most of the time. And the invocation of the UDF would take place double those many times for generating just one new Machine_X_Number.
3. The Update to Code_Generation_Table takes place as many times as the number of iterations which could run into several hundred times. So many UPDATE's just to generate one Machine_X_Number seems too much processing. It should be avoided.
4. The code is accessing the Main_Table as many times as the number of iterations. In our case the Main_Table has just 22 rows. In the real situation, it actually has hundreds of thousands of rows. Why scan that table so many times just to generate one valid Machine_X_Number?
5. The SP performs record-by-record operation or row-by-row. An attempt should be made to perform set based operation for faster and efficient execution. That would also reduce scans of the underlying tables.

These are only a few drawbacks and there are others like usage of dynamic T-SQL which is not needed in this case, etc. We won't go into those detailed aspects in this article. We will concentrate more on logical programming.

Let's try to satisfy the same scenarios after making some refinements to the SP and by eliminating the user-defined function altogether.

A refined version of the SP is given below:

--This SP's purpose is to generate a new Machine_X_Code. It generates a new
--Machine_X_Code from the Current_Code column of the table, Code_Generation_Table
--while checking that the newly generated Machine_X_Code does not already exist
--in the Machine_X_Code column of the table Main_Table. It also checks that a newly
--generated Machine_X_Code lies between Start_Code and End_Code in the table,
--Code_Generation_Table.
--If the newly generated machine_x_code falls outside that range, then the machine_x_code
--is reset to Start_Code. In this whole process it updates the Current_Code column
--of the table, Code_Generation_Table with the newly generated Machine_X_Code once
--or at the most twice. The function, F_Validate_Machine_Code has been eliminated altogether.
--It also updates the the Current_Code column of the table, Code_Generation_Table with
--the next possible Machine_X_Code value. If at all, Looping occurs only twice in a rare case.
--So effectively, there would be no looping except in a scenario where there would be two iterations
--of the code when a new machine_x_code does not exist between the Current_Code and
--the End_Code. In such a scenario, it would check the valid machine codes from Start_Code
--through Current_Code in the second iteration.

Create Procedure usp_Generate_Machine_X_Code
@i_Location_Code Smallint,
@i_Machine_Brand VarChar(10),
@o_Machine_X_Code Integer OutPut,
@o_Return_Value Smallint OutPut
As
Begin
Set nocount on
Set @o_Return_Value = 0
/* WHILE Loop eliminated. Main_Table is now being referenced only once. New code below.
Set @o_Return_Value = 0

Declare @l_Increment_Value Smallint,
@l_Temp_Machine_X_Value Integer,
@l_Next_Machine_X_Value Integer,
@l_Start_Machine_X_Value Int,
@l_End_Machine_X_Value Integer,
@l_First_Machine_X_Value_Taken Integer,
@l_Affected_Rows Smallint,
@l_Execution_String VarChar(2000)

Select @l_Temp_Machine_X_Value = Current_Code
,@l_Increment_Value = Increment_Value
,@l_Start_Machine_X_Value = Start_Code
,@l_End_Machine_X_Value = End_Code
From DBO.Code_Generation_Table With (NoLock)
Where Location_Code = @i_Location_Code
And Machine_Brand = @i_Machine_Brand

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value

Set @l_Affected_Rows = 0

While @l_Affected_Rows = 0
Begin

Set @l_Next_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Next_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Next_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

Set @l_Execution_String = 'Update A Set A.Current_Code = ' + Cast(@l_Next_Machine_X_Value as VarChar) +
' From Dbo.Code_Generation_Table A '+
+ ' Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) + ' And Machine_Brand = ' + ''''+ @i_Machine_Brand + '''' +
' And Not Exists (Select ' + ''''
+ 'x' + '''' + ' From Main_Table ' +
'Where Location_Code = ' + Cast(@i_Location_Code AS VarChar) +
' AND Machine_Brand = ' + '''' + @i_Machine_Brand + '''' +
' And Machine_X_Code = ' + '''' + Replicate('0',10 - Len(@l_Temp_Machine_X_Value)) + Cast(@l_Temp_Machine_X_Value as Varchar(10)) + '''' + ')'

Exec (@l_Execution_String)

Set @l_Affected_Rows = @@Rowcount

If @l_Affected_Rows = 1
Begin
Select @o_Machine_X_Code = @l_Temp_Machine_X_Value, @o_Return_Value = 1
Break
End

Set @l_Temp_Machine_X_Value = @l_Temp_Machine_X_Value + @l_Increment_Value

Set @l_Temp_Machine_X_Value = DBO.F_Validate_Machine_Code (@l_Temp_Machine_X_Value, @l_Start_Machine_X_Value, @l_End_Machine_X_Value)

If @l_First_Machine_X_Value_Taken = @l_Temp_Machine_X_Value
Begin
-- For the current day all the Machine_X_Codes have been used up.
Select @l_Affected_Rows = 2, @o_Return_Value = -1
Break
End
*/

declare @l_next_Machine_X_Code int
,@l_Start_Machine_X_Code int
,@l_End_Machine_X_Code int
,@l_Current_Machine_X_Code int
,@l_Increment_Value tinyint

select @l_Start_Machine_X_Code = Start_Code
,@l_End_Machine_X_Code = End_Code
,@l_Increment_Value = Increment_Value
,@l_Current_Machine_X_Code = Current_Code
from Code_Generation_Table
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand

if ((@l_Current_Machine_X_Code < @l_Start_Machine_X_Code) or (@l_Current_Machine_X_Code > @l_End_Machine_X_Code))
begin
Start_Processing:
update Code_Generation_Table
set @l_Current_Machine_X_Code = Current_Code = @l_Start_Machine_X_Code
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand
end

if object_id ('tempdb.dbo.#Temp_Code_Table') is not null
drop table #Temp_Code_Table

create table #Temp_Code_Table
(Existing_Machine_Codes int
,Valid_machine_Codes int
,Serial_No int identity(0,1))

insert into #Temp_Code_Table (Existing_Machine_Codes, Valid_machine_Codes)
select convert(int, Machine_X_Code), @l_Current_Machine_X_Code from dbo.Main_Table
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand
order by Machine_X_Code

if ((select COUNT(1) from #Temp_Code_Table) = 0)
begin
select @l_next_Machine_X_Code = @l_Current_Machine_X_Code
goto Final_Validation
end

update #Temp_Code_Table
set Valid_machine_Codes = Valid_machine_Codes + (Serial_No * @l_Increment_Value)

select @l_next_Machine_X_Code = min(a.Valid_machine_Codes) from #Temp_Code_Table a
where a.Valid_machine_Codes not in (select b.Existing_Machine_Codes from #Temp_Code_Table b)
and a.Valid_machine_Codes between @l_Start_Machine_X_Code and @l_End_Machine_X_Code

if (@l_next_Machine_X_Code is null)
begin
select @l_next_Machine_X_Code = max(Valid_machine_Codes) + @l_Increment_Value from #Temp_Code_Table
end

Final_Validation:
if (@l_next_Machine_X_Code > @l_End_Machine_X_Code)
begin
goto Start_Processing
end

if (@l_next_Machine_X_Code < @l_Start_Machine_X_Code) or (@l_next_Machine_X_Code > @l_End_Machine_X_Code)
begin
select @l_next_Machine_X_Code = @l_Start_Machine_X_Code
,@o_Return_Value = -1
set nocount off
if object_id ('tempdb.dbo.#Temp_Code_Table') is not null
drop table #Temp_Code_Table
return
end

if (@l_next_Machine_X_Code >= @l_Start_Machine_X_Code) and ((@l_next_Machine_X_Code >= @l_Start_Machine_X_Code) and (@l_next_Machine_X_Code <= @l_End_Machine_X_Code))
begin
update Code_Generation_Table with (readpast, rowlock, xlock)
set Current_Code = case
when @l_next_Machine_X_Code = @l_End_Machine_X_Code then @l_Start_Machine_X_Code
else @l_next_Machine_X_Code + @l_Increment_Value
end
where Location_Code = @i_Location_Code
and Machine_Brand = @i_Machine_Brand

if (@@ROWCOUNT <> 0)
begin
select @o_Machine_X_Code = @l_next_Machine_X_Code
,@o_Return_Value = 1
end
else
set @o_Return_Value = -1
end

if object_id ('tempdb.dbo.#Temp_Code_Table') is not null
drop table #Temp_Code_Table

set nocount off
return
End
GO

The new logic in the SP is given in the comments before the Create Procedure statement. The calls to function have been eliminated. This SP checks for valid Machine_X_Code values between Current_Code and End_Code. If not found, it checks for valid Machine_X_Code values between Start_Code and Current_Code. This is because there is a functional resttriction that the search should always start from the Current_Code only.

Let's now start from the beginning by emptynig (truncating) bith the tables, Code_Generation_Table and Main_Table and filling (inserting) the same data as above into them afresh by executing the following code:

truncate table Code_Generation_Table
truncate table Main_Table

Go

insert into Code_Generation_Table
select 1, 'DELL', 1001, 1001, 1005, 1
union all
select 2, 'DELL', 4001, 4001, 4005, 1
union all
select 3, 'DELL', 7001, 7001, 7005, 1
union all
select 1, 'HP', 2001, 2003, 2005, 1
union all
select 2, 'HP', 5001, 5003, 5005, 1
union all
select 1, 'IBM', 3001, 3001, 3005, 1
union all
select 2, 'IBM', 8001, 8003, 8005, 1
union all
select 3, 'IBM', 6001, 6004, 6005, 1
union all
select 3, 'SUN', 6011, 6011, 6015, 1

go

insert into Main_Table (Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs)
select 1, 'DELL', '0000001001', 2
union all
select 1, 'DELL', '0000001002', 4
union all
select 1, 'DELL', '0000001003', 8
union all
select 1, 'DELL', '0000001004', 16
union all
select 1, 'HP', '0000002002', 2
union all
select 1, 'HP', '0000002003', 4
union all
select 1, 'HP', '0000002004', 8
union all
select 1, 'HP', '0000002005', 32
union all
select 1, 'IBM', '0000003003', 2
union all
select 1, 'IBM', '0000003004', 8
union all
select 1, 'IBM', '0000003005', 32
union all
select 2, 'DELL', '0000004001', 2
union all
select 2, 'DELL', '0000004002', 4
union all
select 2, 'DELL', '0000004003', 8
union all
select 3, 'DELL', '0000007004', 16
union all
select 2, 'HP', '0000005002', 2
union all
select 2, 'HP', '0000005003', 4
union all
select 2, 'HP', '0000005004', 8
union all
select 2, 'HP', '0000005005', 32
union all
select 3, 'IBM', '0000006001', 2
union all
select 3, 'IBM', '0000006003', 2
union all
select 3, 'IBM', '0000006004', 8
union all
select 3, 'IBM', '0000006005', 32
Go

Having filled up the data, it's time to test this new version of the SP.

Let us try to generate a new Machine_X_Code for the Location_Code = 1 and Brand_Name = 'Dell'.

TEST 1:
Execute the following:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 1
and Machine_Brand = 'Dell'

The result set is as follows:

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
1 DELL 0000001001 2
1 DELL 0000001002 4
1 DELL 0000001003 8
1 DELL 0000001004 16

So the next generated machine_x_code to has to be 1005. Remember zeroes are suffixed later and that is not so important right now. What is important is generating the new Machine_X_Code that meets all the requirements above.

Now run the following code:

select * from Code_Generation_Table

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1
1 HP 2001 2003 2005 1
1 IBM 3001 3001 3005 1
2 DELL 4001 4001 4005 1
2 HP 5001 5003 5005 1
2 IBM 8001 8003 8005 1
3 DELL 7001 7001 7005 1
3 IBM 6001 6004 6005 1
3 SUN 6011 6011 6015 1

Since the codes (1001, 1002, 1003 and 1004) already exist in the Main_Table for Location_Code = 1 and Machine_Brand = 'Dell', the next one has to be 1005 as it exists in the Code_Generation_Table between Start_Code and End_Code. Let's see if the SP generates this machine code. Execute the following code to execute the SP:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 1
,@i_Machine_Brand = 'Dell'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
1005 1

We got the exact machine code. Now let's check the table Code_Generation_Table for the column Current_Code by executing the following code:

select * from Code_Generation_Table
where Location_Code = 1
and Machine_Brand = 'Dell'

The result set is as follows:

Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
1 DELL 1001 1001 1005 1

You may note that the Current_Code has been updated. It has actually been initialized with the next possible Current_Code because 1005 has been taken by us via execution of the SP. So the next available value can be 1006. But since 1006 is outside the range of 1001 and 1005, the next possible value has to be re-initialized to the start_code which is 1001. So this condition has also been satisfied by the new version of the SP.

TEST 2:
Now let's test another condition:

Scenario 1:

For Location_Code = 2 and Machine_Brand = 'IBM', there is no record in the Main_Table. Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

(0 rows affected)

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8003 8005 1

So as per the given condition, the new machine_x_code shoudl be 8003 and the Current_Code column of the table, Code_Generation_Table should get updated with 8004. So let's execute the SP from the developer by executng the following code:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
8003 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 IBM 8001 8004 8005 1

So the new Machine_X_Number generated in 8003 which is perfect. And the Crrent_Code column has also been updated to the next possible Machine_X_Number which is 8004. So the Scenario 1 has been satisfied by the new SP.

TEST 3:
Let's check out if Scenario 2 is satisfied:

For Location_Code = 2 and Machine_Brand = 'HP', there are four records in the Main_Table with machine codes (0000005002, 0000005003, 0000005004 and 0000005005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 2
and Machine_Brand = 'HP'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
2 HP 0000005002 2
2 HP 0000005003 4
2 HP 0000005004 8
2 HP 0000005005 32

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5003 5005 1

So as per the given condition for Scenario 2, the new machine_x_code generated should be 5001 and the Current_Code column of the table, Code_Generation_Table should get updated with 8002. So let's execute the SP from the developer by executng the following code:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 2
,@i_Machine_Brand = 'HP'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
5001 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 2
and Machine_Brand = 'HP'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
2 HP 5001 5002 5005 1

So the new Machine_X_Number generated in 5001 which is perfect. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 5002. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exists within the range of Start_Code and End_Code. So the Scenario 2 has been satisfied by the new SP.

TEST 4:
Let's check the Scenario 3, the final scenario:

For Location_Code = 3 and Machine_Brand = 'IBM', there are four records in the Main_Table with machine codes (0000006001, 0000006003, 0000006004 and 0000006005). Let's check by executing the following code:

select Location_Code, Machine_Brand, Machine_X_Code, Number_Of_CPUs from Main_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

Location_Code Machine_Brand Machine_X_Code Number_Of_CPUs
------------- ------------------------- -------------- --------------
3 IBM 0000006001 2
3 IBM 0000006003 2
3 IBM 0000006004 8
3 IBM 0000006005 32

Let's check in the Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6004 6005 1

So as per the given condition for Scenario 3, the new machine_x_code generated should be 6002 and the Current_Code column of the table, Code_Generation_Table should get updated with 6003. So let's execute the SP from the developer by executng the following code:

declare @i_Location_Code Smallint
,@i_Machine_Brand VarChar(10)
,@o_Machine_X_Code Integer
,@o_Return_Value Smallint

select @i_Location_Code = 3
,@i_Machine_Brand = 'IBM'
,@o_Return_Value = 0

execute usp_Generate_Machine_X_Code
@i_Location_Code
,@i_Machine_Brand
,@o_Machine_X_Code OutPut
,@o_Return_Value OutPut

select @o_Machine_X_Code Machine_Code
,@o_Return_Value Return_Value

Here is the result set:

Machine_Code Return_Value
------------ ------------
6002 1

It is perfect. Let's check the Current_Code column of the table, Code_Generation_Table by executing the following code:

select * from Code_Generation_Table
where Location_Code = 3
and Machine_Brand = 'IBM'

The result set is as follows:
Location_Code Machine_Brand Start_Code Current_Code End_Code Increment_Value
------------- ------------ ----------- ------------ ----------- ---------------
3 IBM 6001 6003 6005 1

So the new Machine_X_Number generated in 6002 which was the missing code in Main_Table for Location_Code = 3 and Machine_Brand = 'IBM'. And the Current_Code column has also been updated to the next possible Machine_X_Number which is 6003. The next possible Machine_X_Number should be equal to the output of the SP + Increment_Value and should exist within the range of Start_Code and End_Code. So the Scenario 3 has been satisfied by the new SP.

We too have satisifed all the functional requirements with less scan of the tables Main_Table and Code_Generation_Table. We are not looping except in one scenario where we loop only twice at the most. No matter what the range of values between Start_Code and End_COde, we would scan any table once or at the most twice. Update to Code_Generation_Table also happenns only two times at the most. So the new code can handle larger volumes of data than the original code.

There is another method of optimiznig this SP further whereby there is only once scan of any table. May be in my next article, I would write about it as I am quite tired today. I feel any SP can be optimized no matter how optimized it already is. The dead end is always too distant. I hope you have enjoyed the article. Please let me know of your feedback which is always very welcome.

TSQL and Excel - Data Exchanges

2008-03-13T15:54:00.000-05:00

We are all aware of the applicability of "BCP" for transporting data between external files (text files, Excel files, etc) and database tables, and "Bulk Insert" for sending data one way from external files into database tables. I love BCP. Sometimes, situations arise where we need data from tables to be sent to well formatted Excel spreadsheets and vice-versa. With BCP, one could not send in the Column Headers into the Spreadsheet (it can be done with queryout option but in a round about way) . We always need simple solutions, don't we?

Let's complicate the requirement further. We have to write today's Top three stocks from the database on to a spreadsheet, i.e. the top three gainers in the Stock Market today. We have to also write the top three losing stocks in the Stock Market today into the same Excel spreadsheet but in a different tab. This single Excel spreadsheet with the two tabs, one for gainers and one for losers needs to be e-mailed to some of the investors daily. We would put the e-mailing issue aside as it is quite simple using SQL Server 2005 Database Mail or SQL Server 2000 SQL Mail.

The second requirement is that the data in both the Excel spreadsheet tabs needs to be overwritten with fresh data daily. The source of all data is just one database table which is fed daily. There are two more tabs in the spreadsheet that will have the consolidated gainers and losers respectively for all days as the days progress.

It might have been so simple if this overwritting on two tabs (Gainers and Losers) and appending onto two other tabs (AllGainers and AllLosers) was not required. It would have also been so simple if we could have had four separate spreadsheets instead of four tabs in one single spreadsheet. Then we could have simply used the simple BCP and added a little bit of complication in the bcp command to include the Column Headers in there. But that is not the case as we need to keep the solution simple. Some people say, use CLR and do it row by row. Some say, use VC++.

The issue here is to find a simple solution and not complicate the solution further. We don't want assemblies and DLL's to be prepared, compiled, registered and then modified whenever something needs to be changed. So CLR, VC++ and the like are not an option. We are talking about efficient programming here. And since we are talking about programming and not about packages, DTS and SSIS are also not an option. We need minimum maintenance too. What do we do?

We resort to our contemporary T-SQL database programming language. T-SQL is simple, neat, prolific, dynamic and very flexible. Why not explore OPENDATASOURCE and OPENROWSET? Let's go about solving the problem now.

So let me prepare the data first. Let's create a table called Table_Stock_Value and populate the same. This article is just about concepts. So please don't bother about constraints, indexes and fillfactors right now. All symbols in the data are hypothetical.

if exists (select 1 from sys.tables
where name = 'Table_Stock_Value'
and type = 'U')
drop table Table_Stock_Value
go
create table Table_Stock_Value
(SerialNo int identity (1,1) not null
,NASDAQ_SYMBOL varchar(4) not null
,Company_Name varchar(25) not null
,Change decimal(5,2)
,Date_of_Change datetime not null default getdate())
go
insert into Table_Stock_Value (NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change)

--TOP GAINERS
select 'ABCD', 'ABCD Inc', 2.50, getdate()-3
union all
select 'EFGH', 'EFGH Inc', 2.00, getdate()-3
union all
select 'IJKL', 'IJKL Inc', 1.50, getdate()-3
union all
select 'IJKL', 'IJKL Inc', 1.75, getdate()-2
union all
select 'MNOP', 'MNOP Inc', 1.60, getdate()-2
union all
select 'ABCD', 'ABCD Inc', 1.00, getdate()-2
union all
select 'QRST', 'QRST Inc', 3.50, getdate()-1
union all
select 'UVWX', 'UVWX Inc', 2.50, getdate()-1
union all
select 'ABCD', 'ABCD Inc', 1.25, getdate()-1
union all
select 'IJKL', 'IJKL Inc', 4.95, getdate()
union all
select 'ABCD', 'ABCD Inc', 2.50, getdate()
union all
select 'MNOP', 'MNOP Inc', 1.25, getdate()
union all
select 'ABCD', 'ABCD Inc', 1.90, getdate()+1
union all
select 'EFGH', 'EFGH Inc', 1.20, getdate()+1
union all
select 'QRST', 'QRST Inc', 1.10, getdate()+1
union all
select 'ABCD', 'ABCD Inc', 1.10, getdate()+2
union all
select 'UVWX', 'UVWX Inc', 1.00, getdate()+2
union all
select 'QRST', 'QRST Inc', 0.95, getdate()+2
union all
select 'ABCD', 'ABCD Inc', 1.50, getdate()+3
union all
select 'EFGH', 'EFGH Inc', 1.25, getdate()+3
union all
select 'IJKL', 'IJKL Inc', 1.00, getdate()+3

UNION ALL
--TOP LOSERS
select 'BBBB', 'BBBB Inc', -1.50, getdate()-3
union all
select 'CCCC', 'CCCC Inc', -1.20, getdate()-3
union all
select 'DDDD', 'DDDD Inc', -1.10, getdate()-3
union all
select 'EEEE', 'EEEE Inc', -1.75, getdate()-2
union all
select 'FFFF', 'FFFF Inc', -1.60, getdate()-2
union all
select 'YYYY', 'YYYY Inc', -1.00, getdate()-2
union all
select 'IJKL', 'IJKL Inc', -0.70, getdate()-1
union all
select 'HHHH', 'HHHH Inc', -0.62, getdate()-1
union all
select 'CCCC', 'CCCC Inc', -0.25, getdate()-1
union all
select 'UUUU', 'UUUU Inc', -4.95, getdate()
union all
select 'LLLL', 'LLLL Inc', -2.50, getdate()
union all
select 'AAAA', 'AAAA Inc', -1.25, getdate()
union all
select 'UVWX', 'UVWX Inc', -1.90, getdate()+1
union all
select 'FFFF', 'FFFF Inc', -1.20, getdate()+1
union all
select 'CCCC', 'CCCC Inc', -1.10, getdate()+1
union all
select 'CCCC', 'CCCC Inc', -0.80, getdate()+2
union all
select 'BBBB', 'BBBB Inc', -0.75, getdate()+2
union all
select 'EEEE', 'EEEE Inc', -0.65, getdate()+2
union all
select 'EEEE', 'EEEE Inc', -1.50, getdate()+3
union all
select 'YYYY', 'YYYY Inc', -1.25, getdate()+3
union all
select 'QRST', 'QRST Inc', -1.00, getdate()+3
go
create clustered index ci_date on Table_Stock_Value (Date_of_Change) with fillfactor = 70
go
select * from Table_Stock_Value
go

Next for one time only, we need to prepare an Excel spreadsheet with four tabs in it. The names of the tabs are, Gainers, Losers, AllGainers, and AllLosers. And each tab can have the column names same or different than in the table, Table_Stock_Value created above. For simplicity, let's keep the column names same in the tabs. I have created and kept the Excel spreadsheet in the folder, C:\SQLXL. I have named the Excel spreadsheet as Stock_Overview.xls.

Please remember to close the Excel sheet before the initial compilation of the SP and before each instance of execution of the SP.

Now, let's set out to do what we need to do i.e. writing into the Excel spreadsheet's different tabs as per the requirements stated above. I have written the SP as follows. Please note that the SQL Server advanced configuration option, 'Ad Hoc Distributed Queries' should be set to 1 to read from and write to the Excel Spreadsheet from within a database Stored Procedure:

set ansi_warnings on
go
set ansi_nulls on
Go
execute sp_configure 'show advanced options', 1
go
reconfigure
go
execute sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go

if exists (select 1 from sys.procedures
where type = 'P'
and [NAME] = 'DBP_Prepare_Daily_Stock_Data')
drop procedure [DBP_Prepare_Daily_Stock_Data]
GO
create procedure DBP_Prepare_Daily_Stock_Data
@i_date datetime = null
as
begin
declare @l_File_Exists int
select @i_date = isnull(@i_date, getdate())
execute master.dbo.XP_fileexist 'C:\SQLXL\Stock_Overview.xls', @l_File_Exists output
if (@l_File_Exists = 1)
begin
--Gainers have to be overwritten everyday.
UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source="C:\SQLXL\Stock_Overview.xls";
extended Properties=Excel 8.0')...Gainers$
SET SerialNo = ''
,NASDAQ_SYMBOL = ''
,Company_Name = ''
,Change = ''
,Date_of_Change = ''
WHERE SerialNo is not null
--Losers have to be overwritten everyday.
UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source="C:\SQLXL\Stock_Overview.xls";
extended Properties=Excel 8.0')...Losers$
SET SerialNo = ''
,NASDAQ_SYMBOL = ''
,Company_Name = ''
,Change = ''
,Date_of_Change = ''
WHERE SerialNo is not null
--UPDATE/Overwrite the Gainers tab with top 3 gainers' data for the given day, @i_date.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLXL\Stock_Overview.xls;',
'SELECT SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change FROM [Gainers$A1:E1]')
select top 3 SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change
from Table_Stock_Value with (nolock)
where convert(varchar, Date_of_Change, 112) = convert(varchar, @i_date, 112)
and Change > 0.00
order by Change desc
--UPDATE/Overwrite the Losers tab with top 3 losers' data for the given day, @i_date.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLXL\Stock_Overview.xls;',
'SELECT SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change FROM [Losers$A1:E1]')
select top 3 SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change
from Table_Stock_Value with (nolock)
where convert(varchar, Date_of_Change, 112) = convert(varchar, @i_date, 112)
and Change < 0.00
order by Change desc
--UPDATE/APPEND the AllGainers tab with top 3 gainers' data for the given day, @i_date.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLXL\Stock_Overview.xls;',
'SELECT SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change FROM [AllGainers$A1:E1]')
select top 3 SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change
from Table_Stock_Value with (nolock)
where convert(varchar, Date_of_Change, 112) = convert(varchar, @i_date, 112)
and Change > 0.00
order by Change desc
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLXL\Stock_Overview.xls;',
'SELECT SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change FROM [AllLosers$A1:E1]')
select top 3 SerialNo, NASDAQ_SYMBOL, Company_Name, Change, Date_of_Change
from Table_Stock_Value with (nolock)
where convert(varchar, Date_of_Change, 112) = convert(varchar, @i_date, 112)
and Change < 0.00
order by Change desc
PRINT 'RESULTS HAVE BEEN WRITTEN TO C:\SQLXL\Stock_Overview.xls'
end
else
Begin
Print 'The file Stock_Overview.xls should exist in the correct path, C:\SQLXL.'
PRINT 'RESULTS COULD NOT BE WRITTEN TO C:\SQLXL\Stock_Overview.xls. THE FILE IS OPEN OR DOES NOT EXIST.'
End
end
GO

The SP being ready now, let's check if the all the tabs in the Excel spreadsheet,
C:\SQLXL\Stock_Overview.xls are empty:

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Gainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Losers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllGainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllLosers$]')
go

They are empty.

Let's execute the SP for the earliest date in the table Table_Stock_Value, i.e. getdate()-3:

declare @i_date datetime
select @i_date = getdate()-3
exec DBP_Prepare_Daily_Stock_Data @i_date
go

Now open the spreadsheet, C:\SQLXL\Stock_Overview.xls and check it out. The data has been written to the respective tabs for the given input date to the SP, DBP_Prepare_Daily_Stock_Data.

You can also execute the following T-SQL code from your SQL Server database to check the same:

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Gainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Losers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllGainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllLosers$]')
go

The data in the spreadsheet and the result sets above would match.

Let's execute the SP for the next date in the table Table_Stock_Value, i.e. getdate()-2:

declare @i_date datetime
select @i_date = getdate()-3
exec DBP_Prepare_Daily_Stock_Data @i_date
go

Now open the spreadsheet, C:\SQLXL\Stock_Overview.xls and check it out. The data has been written to the respective tabs for the given input date to the SP, DBP_Prepare_Daily_Stock_Data. Please close the spreadsheet after reading its contents.

To verfiy the results using T-SQL itself without opening the spreadsheet, you can use the following code too and verify the results:

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Gainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Losers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllGainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllLosers$]')
go

Note that we have met the requirement.

Let's do it for getdate()-1 and getdate() as well.

declare @i_date datetime
select @i_date = getdate()-1
exec DBP_Prepare_Daily_Stock_Data @i_date

Now open the Excel Spreadsheet. You would find the Gainer and Losers tabs overwritten with the top 3 gainers and top 3 losers respectively for the date, getdate()-1. You would also notice that the same data has been appended respectively to AllGainers and AllLosers tabs within the same spreadsheet. So, the Gainers and Losers tabs always have three rows each and these rows pertain specifically to the given input date to the SP. The AllGainers and AllLosers tabs have their number of rows growing with each execution of the SP as new rows get appended behind the older rows thus building up a historical data for later analysis. And that's all we want.

Let's execute the SP for @i_date = getdate().

declare @i_date datetime
select @i_date = getdate()
exec DBP_Prepare_Daily_Stock_Data @i_date
go

Now open the Excel Spreadsheet. You would find the Gainer and Losers tabs overwritten with the top 3 gainers and top 3 losers respectively for the given input date, getdate(). You would also notice that the same data has been appended respectively to AllGainers and AllLosers tabs within the same spreadsheet. So, the Gainers and Losers tabs always have three rows each and these rows pertain specifically to the given input date for top three gainers and losers respectively for the respective tabs. The AllGainers and AllLosers tabs have their number of rows growing with each execution as new rows get appended behind the older rows thus building up a historical data for later analysis. We are done with verifying that we have satisfied the requirements that we initially started out with.

You can checkout the contents of the spreasheet by just opening it from its location. Alternatively, you can use the following code. Please close the spreadsheet after physically reading its contents.:

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Gainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [Losers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllGainers$]')
go
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\SQLXL\Stock_Overview.xls', 'Select * from [AllLosers$]')
go

Please remember to close the Excel sheet before each execution of the SP.

Now let me throw a little bit of explanation behind overwriting and appending the Excel spreadsheet tabs:

First of all, in order to read from and write to an external data source like the Excel spreadsheet in this case while not using the BCP or BULK INSERT commands, the advanced SQL Server configuration option 'Ad Hoc Distributed Queries' must be set to 1. This way, writes to and reads from the Excel spreadsheet get enabled. This's what we did first.

Next, we UPDATE any existing records in the Gainers and Losers tabs with an empty string, ''. This is equivalent to DELETEing all existing records in both these tabs. Even then, the empty characher string remains in the existing cells although it is not visible.

The next step is to overwrite these existing cells in the Gainers and Losers tabs. For this purpose, we use the INSERT statement. Please note that we use the starting and ending cells of the first row in the Gainers and Losers tab in the OPENDATASOURCE statement (A1:E1). This will enable overwriting the existing cells. The smart thing here is that any empty strings '' are no more visible even if they exist somwhere else in the spreadsheet.

For the tabs AllGainers and AllLosers, since we don't want to overwrite existing data but instead want to append new data to any existing data, we omit mentioning the starting row A1:E1 in the OPENDATASOURCE function of the INSERT statement. That ensures that new rows are always appended to the existing rows in these two tabs.

You can use the spreadsheet and its tabs as if they were individual tables, for reading and performing calculations. You can apply all types of T-SQL constructs on that data to make aggregations, etc. Please note the sample T-SQL code above that we used to check the result sets in the tabs of the spreadsheet after executing the SP.

One important point to note is that before compiling the SP, the spreadsheet with the appropriate tabs in it should exist in the correct path mentioned in the SP. Otherwise the SP will not get compiled and will instead throw an error.

Another important point is that during compilation and execution of the SP, the spreadsheet itself should not be physically openend from the Windows Explorer.

Ofcourse, sufficient scope exists for making improvisations to the SP to calculate the top stock for the month or for generating some special reports, etc. I am limited myself to just explaining the manipulation of an Excel spreadseet and its tabs using T-SQL. And the example has been designed to just show that. Using Database Mail to e-mail the spreadsheet is outside the scope of this article.

I hope you like the technique of writing to and manipulating spreadsheets and their tabs using simple T-SQL. Please let me know of your comments and thoughts.

Simplifying Code: Updating Variables

2008-03-10T13:27:00.000-05:00

A good programming practice means being able to use every available option one can to reduce code and at the same time increase speed of execution. A merit of blogging is that such methods can be explained but demontration of using the sample code as it is on large databases is not possible because of the obvious limitations on how much can be written inside one article. That fact however does not in any way takes away the applicability of such methods on large databases. In today's article, we will see how an unrelated construct/statement can help in reducing code and speeding up execution.

First let us build the base table, Order_Master:

create table Order_Master
(Order_ID smallint not null
,Begin_Date datetime not null
,End_Date datetime not null
,Delivery_Location varchar(50) not null)
go

Now, let's put in some data into the table, Order_Master:

Insert into Order_Master (Order_ID, Begin_Date, End_Date, Delivery_Location)
select 1, getdate()-100, getdate()+100, 'New York'
union all
select 2, getdate()-50, getdate()+50, 'Washington D.C'
union all
select 3, getdate()-25, getdate()+25, 'Princeton'
union all
select 4, getdate()-12, getdate()+12, 'Dallas'
union all
select 5, getdate()-6, getdate()+6, 'Los Angeles'
go

Let's read from the table, Order_Master:

select * from Order_Master
go

Order_ID Begin_Date End_Date Delivery_Location
-------- ----------------------- ----------------------- --------------------
1 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087 New York
2 2008-01-20 14:06:04.087 2008-04-29 14:06:04.087 Washington D.C
3 2008-02-14 14:06:04.087 2008-04-04 14:06:04.087 Princeton
4 2008-02-27 14:06:04.087 2008-03-22 14:06:04.087 Dallas
5 2008-03-04 14:06:04.087 2008-03-16 14:06:04.087 Los Angeles

Now let's come to the scenario.

1. For a given Order_ID and for a given input date, we need to find out what the begin date and end date for that Order_ID are.

2. We also need to find out whether the given input date falls within the begin date and end date for that order ID.

3. The output needs to be the Begin Date, the end date and a return value which would be 1 if the given input date falls within the begin date and end date for that order ID or 0 if it doesn't.

Given these requirements, a normal Stored Procedure would be like the following:

if exists (select 1 from sys.procedures
where type = 'P'
and user_name(schema_id) = 'dbo'
and [NAME] = 'DBP_ValidateOrderDates')
drop procedure [dbo].[DBP_ValidateOrderDates]
GO

CREATE PROCEDURE [dbo].[DBP_ValidateOrderDates]
@i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit = 0 output,
@o_BeginDate datetime = Null output,
@o_EndDate datetime = Null output
AS
BEGIN
SET NOCOUNT ON;
Select @o_BeginDate = Begin_Date
,@o_EndDate = End_Date
from dbo.Order_Master
where Order_ID = @i_OrderNo
IF @@rowcount = 0
Begin
Set @o_Return = 0
End
Else
If (@i_ValidationDate <= @o_EndDate and @i_ValidationDate >= @o_BeginDate)
Begin
set @o_Return = 1
End
Else
Begin
set @o_Return = 0
End
Set nocount off
END
Go

Now let's compile and then execute this SP for Order_ID = 1 and input date as today's date:

--Declaring the variables:
declare @i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit,
@o_BeginDate datetime,
@o_EndDate datetime

--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()
,@o_Return = null

--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output

--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'

Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 1 because the input date falls between the begin and end dates for that Order_ID:

Return Value Begin Date End Date
------------ ----------------------- -----------------------
1 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087

Now let's execute this SP for the same Order_ID (1) but with the input date not falling within the begin and end dates for that Order_ID.

--Declaring the variables:
declare @i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit,
@o_BeginDate datetime,
@o_EndDate datetime

--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()-200
,@o_Return = null

--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output

--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'

Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 0 because the input date does not fall between the begin and end dates for that Order_ID:

Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087

Now let's take another possible situation. If we were to input an Order_ID (101) that doesn't exist in the Order_Master table, we should be getting nulls for the begin and end dates but the return value should be 0. Let's execute this scenario.

--Declaring the variables:
declare @i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit,
@o_BeginDate datetime,
@o_EndDate datetime

--Assigning values to input variables/parameters:
select @i_OrderNo = 101
,@i_ValidationDate = getdate()
,@o_Return = null

--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output

--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'

The result set matches with the expected result:

Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 NULL NULL

Since the input Order_ID, 101 does not exist in the table, the Begin and End dates are null. Since they are null, the input date can not fall between null begin and end dates and hence is 0.

Issue:

You may observe that there are so many constructs in this SP:

One SELECT statement
Three set variable statements
One usage of @@rowcount to check existence of the record
Three Begin-End blocks
Two IF and one ELSE construct

The SP seems perfectly logical. It has a very logical flow to satisfy all our requirements. But
the question is "Do we really need so many constructs to staify our requirements?". The answer is "May be Not". Remember, all we have to do is assign proper value to the output variables/parameters based on the input variables and the data in the table, Order_Master.

Let's try to simplify the same SP. Below, I have replace the IF BEGIN-END, IF-ELSE and @@rowcount with one UPDATE CASE statement. Let's see how it looks now:

if exists (select 1 from sys.procedures
where type = 'P'
and user_name(schema_id) = 'dbo'
and [NAME] = 'DBP_ValidateOrderDates')
drop procedure [dbo].[DBP_ValidateOrderDates]
GO

CREATE PROCEDURE [dbo].[DBP_ValidateOrderDates]
@i_OrderNo varchar(6),
@i_ValidationDate datetime,
@o_Return bit = 0 output,
@o_BeginDate datetime = Null output,
@o_EndDate datetime = Null output
AS
BEGIN
SET NOCOUNT ON;
update a
set @o_Return = case
when @i_ValidationDate between a.Begin_Date and a.End_Date then 1
else 0
end
,@o_BeginDate = a.Begin_Date
,@o_EndDate = a.End_Date
from (Select Begin_Date, End_Date from dbo.Order_Master with (nolock)
where Order_ID = @i_OrderNo) a

Set nocount off
END
Go

Doesn't the SP look short now? One UPDATE statement takes care of all possibilities and reduces code even though in the original SP, we are not updating any table for that matter. Neither are we updating the table Order_Master in this refined version of the original SP. All we are doing is updating the output variables / parameters based on the input parameters and the corresponding data in the table Order_Master. And that's all that is expected from this SP. So why take the longer route as in the original version? Not necessary.

Now let's execute the refined SP:

Now let's compile and then execute this SP for Order_ID = 1 and input date as today's date. This is the first case above:

--Declaring the variables:
declare @i_OrderNo varchar(6)
,@i_ValidationDate datetime
,@o_Return bit
,@o_BeginDate datetime
,@o_EndDate datetime

--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()
,@o_Return = null

--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output

--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'

Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 1 because the input date falls between the begin and end dates for that Order_ID:

Return Value Begin Date End Date
------------ ----------------------- -----------------------
1 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087

Now let's execute this SP for the same Order_ID (1) but with the input date not falling within the begin and end dates for that Order_ID. This is the second case above.

--Declaring the variables:
declare @i_OrderNo varchar(6)
,@i_ValidationDate datetime
,@o_Return bit
,@o_BeginDate datetime
,@o_EndDate datetime

--Assigning values to input variables/parameters:
select @i_OrderNo = 1
,@i_ValidationDate = getdate()-200
,@o_Return = null

--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output

--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'

Here is the result set as expected. We got the begin and end dates for the given Order_ID (1) and the return value is 0 because the input date does not fall between the begin and end dates for that Order_ID(1):

Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 2007-12-01 14:06:04.087 2008-06-18 14:06:04.087

Now let's take another possible situation, i.e. the third situation above. If we were to input an Order_ID (101) that doesn't exist in the Order_Master table, we should be getting nulls for the begin and end dates but the return value should be
0. Let's execute this scenario.

--Declaring the variables:
declare @i_OrderNo varchar(6)
,@i_ValidationDate datetime
,@o_Return bit
,@o_BeginDate datetime
,@o_EndDate datetime

--Assigning values to input variables/parameters:
select @i_OrderNo = 101
,@i_ValidationDate = getdate()
,@o_Return = null

--Executing the SP with the given input parameters:
execute [dbo].[DBP_ValidateOrderDates]
@i_OrderNo
,@i_ValidationDate
,@o_Return output
,@o_BeginDate output
,@o_EndDate output

--Capturing the output:
select
@o_Return 'Return Value', @o_BeginDate 'Begin Date', @o_EndDate 'End Date'

The result set matches with the expected result:

Return Value Begin Date End Date
------------ ----------------------- -----------------------
0 NULL NULL

Since the input Order_ID, 101 does not exist in the table, the Begin and End dates are null. Since they are null, the input date can not fall between null begin and end dates and hence is 0.

We get the same results with the refined version of the SP but in one statement that in multiple If's and ELSE's and @@rowcount.

The UPDATE statement in this case doesn't perform any updates to the table Order_Master itself. It just updates the output parameters to the correct values.

This is a very simple example of using unrelated operations (UPDATE) where SELECT seems the only solution and thus reducing the amount of code needed to be written and executed. This article just demonstrates that coding flexibility in Microsoft SQL Server's T-SQL is profound. Based on individual situation, there are numerous possibilities in T-SQL that can reduce code and execution time as well. We will follow some more of such techniques in the forthcoming articles.

MERGE statement in SQL Server 2008

2007-12-11T12:29:00.000-06:00

Since the last few days, I have been spending some time on studying new features in the Katmai betas. One of the better coding introductions I like in Microsoft SQL Server 2008 is the MERGE statement. I like the way it provides option to UPDATE, DELETE and INSERT data simultaneously in a single T-SQL statement.

Using it for simple operations is a costly affair when compared to equivalent T-SQL code in 2000/2005. However using it for complex operations is simple and efficient compared to equivalent T-SQL code in 2000/2005. This is the paradox. I find most prolific coders prefer to write compact code even if is is very complex rather than using lengthy but simple code. The reason most often is that the shorter the code, the faster it performs in most cases. Here is where the paradox associated with MERGE statement comes in handy.

Let me create a simple situation first:

--Create a small table.
create table abc
(productid int identity (1,1)
,Quantity decimal(4,0) default 100
,Order_Date datetime default getdate())

--Populate the tables, abc.

insert into abc default values
insert into abc (Quantity) values (125)
insert into abc (Quantity) values (150)
insert into abc (Quantity) values (175)
insert into abc (Quantity) values (200)
insert into abc (Quantity) values (225)
insert into abc (Quantity) values (250)
insert into abc (Quantity) values (275)
insert into abc (Quantity) values (300)
insert into abc (Quantity) values (325)
insert into abc (Quantity) values (350)
insert into abc (Quantity) values (375)

--Check the contents of the table, abc.
select * from abc

--Add a nullable column to the table, abc. This one column is to be populated (updated) later.
alter table abc
add OrderID int null

--Now the MERGE construct updates the OrderID column of the table abc as follows.
--Divide the ProductID by 2. If remainder comes out to be zero, then update
--OrderID with 2. If remainder comes out to a non-zero value, then update
--OrderID as ProductID minus the Quotient.
--The code is given below:

merge abc as master_table
using (select ProductId, ProductId / @l_OrderID_value, ProductId % @l_OrderID_value
from abc) as input (ProductId, Quotient, Remainder)
ON master_Table.ProductId = Input.ProductID
When Matched
THEN UPDATE set master_table.OrderID = case
when input.Remainder = 0 then @l_OrderID_value
else master_Table.ProductId - Input.Quotient
end;

It was very structured but long. This was a simple operation and didn't need such length of code.

--Now let's backup the data in the tables abc.

select * into def from abc

Now, let's write the equivalent T-SQL code as a replacement of the MERGE statement above.

declare @l_OrderID_value int
set @l_OrderID_value = 2

update abc
set OrderID = case
when ProductID%@l_OrderID_value = 0 then @l_OrderID_value
else ProductId - (ProductID/@l_OrderID_value)
end;

This was short, simple and just one UPDATE statement was required to do the entire operation.

If you look for the query cost relative to batch when you run both the statements together and get actual execution plans, the MERGE costs 76% while the single UPDATE costs 24%. The MERGE statement had a SELECT from the table abc, then it uses a derived table, and then it does the UPDATE. In the second statement above, there is a direct UPDATE. So the second statement is expected to be faster. So in this example, MERGE was not really useful. The above example was a simple case, so we won't bother ourselves much with MERGE in simple scenarios.

Now, let's take up something slightly more involved where MERGE can be really useful.

--Create a new table.
create table abc_Summary
(OrderID int
,Order_Date datetime
,Quantity int)

--It is empty.
select * from abc_Summary

MERGE abc_summary as TARGET_Table
using (select OrderID, convert(varchar, Order_Date, 112), sum(Quantity) from abc
group by OrderID, convert(varchar, Order_Date, 112)) as Data (OrderID, Order_Date, Total_Quantity)
on TARGET_Table.OrderID = Data.OrderID
When NOT MATCHED THEN INSERT (OrderID, Order_Date, Quantity)
VALUES (Data.OrderID, Data.Order_Date, Data.Total_Quantity)
WHEN MATCHED THEN
UPDATE SET TARGET_Table.Quantity = Data.Total_Quantity;

--Verify that the empty table has gotten populated with aggregated data from the main table, abc.
select * from abc_Summary

--So this time the INSERT took place. Now lets UPDATE the column abc_Summary.Quantity to --zero. And then we will run the above code again.

update abc_summary
set Quantity = 0
GO

--Verify that Quantity is 0.
select * from abc_Summary
GO

--Run the same code again:
MERGE abc_summary as TARGET_Table
using (select OrderID, convert(varchar, Order_Date, 112), sum(Quantity) from abc
group by OrderID, convert(varchar, Order_Date, 112)) as Data (OrderID, Order_Date, Total_Quantity)
on TARGET_Table.OrderID = Data.OrderID
When NOT MATCHED THEN INSERT (OrderID, Order_Date, Quantity)
VALUES (Data.OrderID, Data.Order_Date, Data.Total_Quantity)
WHEN MATCHED THEN
UPDATE SET TARGET_Table.Quantity = Data.Total_Quantity;

--Verify that the Quantity column in the abc_Summarfy table is updated now with actual summed quantities per OrderID from the parent table, abc.

select * from abc_Summary

Lets do something different now, a bit more involved. UPDATE the table abc_Summary such that OrderID's 1,2 and 3 are removed and Quantity is set to zero for the remaining three rows. Also add one dirty row to the abc_summary table. Note that the table abc_summary should contain only those OrderID's that already exist in the parent table, abc. Since the newly inserted OrderID in the dirty row does not exist in the parent table, abc, it should be deleted from abc_summary. So in all, what we are trying to do here is following:
INSERT missing rows,
UPDATE Quantity for existing three rows, and
DELETE one dirty row.

All this is to be accomplished in one query.
Let's prepare the data now.

--Preparing data.
update abc_summary
set Quantity = 0
GO
delete from abc_summary
where OrderID in (1, 2, 3)
GO
insert into abc_summary (OrderID, Order_Date, Quantity)
values (2001, getdate(), 50000)
go

--Check the data. OrderID's 4,5 and 6 have their Quantity zero'ed out. OrderID's 1,2 and 3 no ---longer exist. A new OrderID 2001 has come in but it doesn't exist in parent table, abc.

select * from abc_Summary

--Now let's get the correct data into the table abc_summary in one single T-SQL query using the MERGE statement which issues INSERT, UPDATE and DELETE simultaneously.

MERGE abc_summary as TARGET_Table
using (select OrderID, convert(varchar, Order_Date, 112), sum(Quantity) from abc
group by OrderID, convert(varchar, Order_Date, 112)) as Data (OrderID, Order_Date, Total_Quantity)
on TARGET_Table.OrderID = Data.OrderID
When NOT MATCHED THEN INSERT (OrderID, Order_Date, Quantity)
VALUES (Data.OrderID, Data.Order_Date, Data.Total_Quantity)
WHEN MATCHED THEN
UPDATE SET TARGET_Table.Quantity = Data.Total_Quantity
WHEN SOURCE NOT MATCHED THEN
DELETE;

--Check the data. OrderID's 4,5 and 6 have their Quantity UPDATEd correctly. --Rows for OrderID's 1,2 and 3 have been INSERTed now. The dirty row for OrderID 2001 has been DELETed as it didn't exist in parent table, abc.

select * from abc_Summary

So this time the INSERT took place and brought in the three mmissing ORDERID's (1,2,3) from
the table abc into the table abc_Summary. Then it UPDATEd the abc_Summary.Quantity
column for ORDERID's 3,4,5 from zero to the correct aggregated quantity from the
parent table, abc. Lastly, it also DELETEd the erroneous row with ORDERID 2001 because this
ORDERID did not exist in the parent table, abc. All these three operations were
performed in one single query.

All this can be done without the MERGE statement as well. But it would be really very lengthy and expensive code. It would contain several IF-ELSE constructs, IF [NOT] EXISTS clauses, multiple instances of usage of the tables abc and abc_Summary, apart from the INSERT, UPDATE, DELETE statements. We don't want to do that, do we? The cost of that query would be much more than the MERGE statement above. Let's verify the same. Let me try to build as short a code as possible to replace the MERGE statement above. Please see the replacement code below:

--Prepare the data first.
update abc_summary
set Quantity = 0
GO
delete from abc_summary
where OrderID in (1, 2, 3)
GO
insert into abc_summary (OrderID, Order_Date, Quantity)
values (2001, getdate(), 50000)
go

--Check the data.
select * from abc_Summary

--Now let's get the correct data into the table abc_summary.
--UPDATE Quantity to the correct aggregated value.
update abc_summary
set abc_summary.Quantity = (select sum(abc.Quantity) from abc
where abc_Summary.OrderID = abc.OrderID)
where exists (select OrderID from abc
where abc_Summary.OrderID = abc.OrderID)

--Check the data. Quantity has been updated correctly.
select * from abc_Summary

--INSERT missing ORDERID's 1,2 and 3.
insert into abc_summary (OrderID, Order_Date, Quantity)
select OrderID, convert(varchar, Order_Date, 112), sum(Quantity) from abc
where OrderID not in (select OrderID from abc_Summary)
group by OrderID, convert(varchar, Order_Date, 112)

--Check the data. Missing ORDERID's have been re-inserted correctly with the right Quantity.
select * from abc_Summary

--DELETE dirty row.
delete from abc_Summary
where OrderID not in (select OrderID from abc)

--Check the data. The row with ORDERID 2001 has been deleted as required.
select * from abc_Summary

So multiple instances of joins between the parent table abc and the child table abc_Summary had to be used.

The execution plan showed 40% cost for the MERGE statement and 60% cost for the replacement statemenets above combined. Here we are talking about just va few rows of data. The benefits rise with increase in the number of rows. You may want to try it out.

So in all, MERGE statement in Microsoft SQL Server 2008 is really good. It reduces code, does multiple operations in a single statement and is less expensive or is more efficient when we need to perform a bit complex operations. Please note that the syntax for INSERT, UPDATE and DELETE constructs within the MERGE statement are slightly different from their original counterparts. The MERGE statement opens up a lot of possibilities. Imagine how interesting it would be to use within an AFTER TRIGGER to perform UPDATE, DELETE and INSERT operations on another table in one shot through the trigger. Although it is possible to do it without the MERGE statement as well, but it gets too lengthy and tedious as I had to implement at one of my clients for certain reasons. MERGE can make it compact, simple and efficient.

In my next article, we will solve some puzzles.

Some T-SQL code in 2005 and 2000.

2007-11-14T09:22:00.000-06:00

I have sometimes been fazed by CTE (Common Table Expressions) and PIVOT in SQL Server 2005. I like the way they are structured but hate the way they perform. I tried comparing them with our old-style drived tables. And to my surprise, code written using derived tables performed much better that the code written using CTE or PIVOT or both combined. Let me share some code here using the SQL Server 2005 Adventureworks database.

--Shows Amount per customer:

WITH Value_from_Customer (CustomerID, SalesOrderID, Total_Value_of_Sale)
AS
(SELECT SOH.CustomerID, SOD.SalesOrderID, sum(SOD.LineTotal)
FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SalesOrderHeader SOH
ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY SOH.CustomerID, SOD.SalesOrderID
)
SELECT CustomerID
,count(SalesOrderID) as Number_of_Sales
,sum(Total_Value_of_Sale) Value_from_Customer
FROM Value_from_Customer
Group by CustomerID
ORDER BY CustomerID
GO

--The same would have been done in SQL Server 2000 as follows:

SELECT CustomerID
,count(SalesOrderID) as Number_of_Sales
,sum(Total_Value_of_Sale) as Value_from_Customer
from
(SELECT SOH.CustomerID, SOD.SalesOrderID,
sum (SOD.LineTotal) as Total_Value_of_Sale
FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SalesOrderHeader SOH
ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY SOH.CustomerID, SOD.SalesOrderID) abc
Group by CustomerID
ORDER BY CustomerID
GO

The CTE above looks very structured and systematic. The equivalent query with derived table looks more like a block of buildings. But I have found in all my tests that derived tables always outperform CTE's. In fact, I can share a real story about it. One of my colleagues had a terabyte-plus sized database for a data warehouse. Somebody wrote a 200 line CTE that would fetch a few rows from a combination of several multi-million row tables. It gave the results but after executing for eight hours. It is hard to withstand eight hours when you know that you need just a few rows at the end. I suggested that CTE's in the query be replaced by regular derived tables. It was a big task but when it was done, the query gave out the same results in 55 seconds. That's the kind of difference I am talking about. After that some more improvements were done to make it finally run in 10 seconds. However, we would not go into the details of that story here but would stick to differences in performance between new and old T-SQL constructs.

My sample queries for 2005 and 2000 above would show only marginal difference in execution times becuase the number of rows in the referenced tables is very small. When the number of rows are increased in the underlying tables, the difference in execution times keeps on rising with the 2000 style code outperforming the 2005 CTE code.

Now let's take up a PIVOT construct.

---Shows Sale per year by territory. PIVOT.

SELECT YEAR_of_SALE
, [1] AS Trtry_1
, [2] AS Trtry_2
, [3] AS Trtry_3
, [4] AS Trtry_4
, [5] AS Trtry_5
, [6] AS Trtry_6
FROM
(SELECT TerritoryID, TotalDue, year (OrderDate) AS YEAR_of_SALE
FROM Sales.SalesOrderHeader) SOH
PIVOT (SUM (TotalDue) FOR TerritoryID IN ( [1], [2], [3], [4], [5], [6] )) AS pvt
ORDER BY YEAR_of_SALE
GO

--The same would have been done in SQL 2000 as follows:

select YEAR_of_SALE
,sum(Trtry_1) as Trtry_1
,sum(Trtry_2) as Trtry_2
,sum(Trtry_3) as Trtry_3
,sum(Trtry_4) as Trtry_4
,sum(Trtry_5) as Trtry_5
,sum(Trtry_6) as Trtry_6
from
(select YEAR_of_SALE
,isnull((select Total_Revenue where TerritoryID = 1), 0) as Trtry_1
,isnull((select Total_Revenue where TerritoryID = 2), 0) as Trtry_2
,isnull((select Total_Revenue where TerritoryID = 3), 0) as Trtry_3
,isnull((select Total_Revenue where TerritoryID = 4), 0) as Trtry_4
,isnull((select Total_Revenue where TerritoryID = 5), 0) as Trtry_5
,isnull((select Total_Revenue where TerritoryID = 6), 0) as Trtry_6

from
(SELECT TerritoryID
,year (OrderDate) AS YEAR_of_SALE
,sum(TotalDue) Total_Revenue
FROM Sales.SalesOrderHeader
where TerritoryID between 1 and 6
GROUP BY TerritoryID, year (OrderDate)) abc) def

GROUP BY YEAR_of_SALE
ORDER BY YEAR_of_SALE
GO

Now as you can see, the 2005 query with PIVOT looks simple and neat. The equivalent query performing the same functionality but written using derived tables looks tedious. However, the second query with derived tables outperforms the first one with PIVOT. I agree that the first query with PIVOT requires much less coding effort and looks much less complex than the second one using derived tables. But what are clients interested in? Coding complexity or performance. It is always performance that everybody is interested in. When a customer clicks on a link, he/she expects results fast and doesn't really care what code is executing in the background. So we have to use code which performs better.

Now lets take another example where I combine a CTE with a PIVOT structure. Please note that below the 2005 style query with CTE and PIVOT combined, I have an equivalent for 2000 using derived tables:

--2005.
--Find number of physical sales by year and their total value.
--Break down the yearly value by territory upto six territories.

WITH Value_of_Sale (SalesOrderID, Transaction_Date, Total_Value_of_Sale)
AS
(SELECT SOD.SalesOrderID, SOH.ModifiedDate, sum(SOD.LineTotal)
FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SalesOrderHeader SOH
ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY SOD.SalesOrderID, SOH.ModifiedDate
)
SELECT count(a.SalesOrderID) Total_Number_of_Sales
,b.YEAR_of_SALE
,sum(a.Total_Value_of_Sale) Total_Value_of_Sale
,b.Trtry_1
,b.Trtry_2
,b.Trtry_3
,b.Trtry_4
,b.Trtry_5
,b.Trtry_6
FROM Value_of_Sale a
inner join
(SELECT YEAR_of_SALE
,[1] AS Trtry_1
,[2] AS Trtry_2
,[3] AS Trtry_3
,[4] AS Trtry_4
,[5] AS Trtry_5
,[6] AS Trtry_6
FROM (SELECT TerritoryID, TotalDue, year (OrderDate) AS YEAR_of_SALE
FROM
Sales.SalesOrderHeader) SOH
PIVOT (SUM (TotalDue) FOR TerritoryID IN ( [1], [2], [3], [4], [5], [6] ) ) AS pvt ) b

on year(a.Transaction_Date) = b.YEAR_of_SALE
Group By b.YEAR_of_SALE
,b.Trtry_1
,b.Trtry_2
,b.Trtry_3
,b.Trtry_4
,b.Trtry_5
,b.Trtry_6
ORDER BY b.YEAR_of_SALE Asc
GO

--The same would have been done in SQL Server 2000 as follows:
---2000.

select count(a.SalesOrderID) Number_of_Sales
,b.YEAR_of_SALE
,sum(a.Total_Value_of_Sale) Total_Value_of_Sale
,b.Trtry_1
,b.Trtry_2
,b.Trtry_3
,b.Trtry_4
,b.Trtry_5
,b.Trtry_6
from

(SELECT SOD.SalesOrderID
,year(SOH.ModifiedDate) AS Transaction_Date
,sum(SOD.LineTotal) AS Total_Value_of_Sale
FROM Sales.SalesOrderDetail AS SOD INNER JOIN Sales.SalesOrderHeader SOH
ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY SOD.SalesOrderID, year(SOH.ModifiedDate)
) a
INNER JOIN
(select YEAR_of_SALE
,sum(Trtry_1) as Trtry_1
,sum(Trtry_2) as Trtry_2
,sum(Trtry_3) as Trtry_3
,sum(Trtry_4) as Trtry_4
,sum(Trtry_5) as Trtry_5
,sum(Trtry_6) as Trtry_6
from
(select YEAR_of_SALE
,isnull((select Total_Revenue where TerritoryID = 1), 0) as Trtry_1
,isnull((select Total_Revenue where TerritoryID = 2), 0) as Trtry_2
,isnull((select Total_Revenue where TerritoryID = 3), 0) as Trtry_3
,isnull((select Total_Revenue where TerritoryID = 4), 0) as Trtry_4
,isnull((select Total_Revenue where TerritoryID = 5), 0) as Trtry_5
,isnull((select Total_Revenue where TerritoryID = 6), 0) as Trtry_6
from
(SELECT TerritoryID
,year (OrderDate) AS YEAR_of_SALE
,sum(TotalDue) Total_Revenue
FROM Sales.SalesOrderHeader
where TerritoryID between 1 and 6
GROUP BY TerritoryID, year (OrderDate)) abc) def
GROUP BY YEAR_of_SALE) b

on a.Transaction_Date = b.YEAR_of_SALE
Group By b.YEAR_of_SALE
,b.Trtry_1
,b.Trtry_2
,b.Trtry_3
,b.Trtry_4
,b.Trtry_5
,b.Trtry_6
ORDER BY b.YEAR_of_SALE Asc
GO

The 2000 code with derived tables involves much more coding and code complexity than the equivalent one in 2005. But the 2000 code outperforms the 2005 code everytime. When I incease the number of rows in the underlying referenced tables within the two queries, the different in execution times gets wider.

I am not denying SQL Server 2005 credit that it deserves. But I still love the SQL Server 2000 coding style becuase of its supeior performance. Please note that all the code was run on SQL Server 2005 Adventureworks database in 90 compatibility mode.

However, there are a few coding improvements that I love in SQL Server 2005. These are the NTILE and ROW_NUMBER() OVER constructs. The reason why I like them is that not only are they short and simple, they also perform better than their equivalnt coding constructs in SQL Server 2000. Let me share some examples below.

Let me create a situation first. An Engineering College conducts nationwide enterance exams. Thousands of students appear in the tests. Let's say only the top 12 scorers qualify for admission. The college first selects the top 12 scorers in the exam. The college then needs to rank these top 12 students based on their total score first. If for any two or more of the top 12 students the total scores match, then the one with the higher score in Mathematics is ranked higher. If total scores and score in Maths are also same, then the one with higher score in Physics is ranked higher. If the total score and individual scores in Maths, Physics and Chemistry also match, then the student with the higher age is to be ranked higher as per the College policy. Once ranks have bene allotted to the top 12 students, they are to be invited in groups/batches of four to permit them to elect their field of interest (Computer Engineering, Electronics Engineering, Electrical Engineering, Mechanical Engineering, Chemical Engineering, Civil Engineering, etc). Each group is based on the ranks of the students and needs to contain exactly four students.

Taking these requirements into consideration, lets design a small table with the top twleve scorers as follows:

--Create the table.
create table Student_Ranks
(Student_Name varchar(2)
,Maths smallint
,Physics smallint
,Chemistry smallint
,Total_Marks AS Maths + Physics + Chemistry
,Age tinyint)
GO

--Populate the table with marks.
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('A', 90, 50, 70, 17)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('B', 70, 60, 90, 16)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('C', 50, 70, 90, 17)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('D', 50, 90, 70, 16)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('E', 100, 90, 45, 16)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('F', 90, 50, 70, 16)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('G', 45, 80, 60, 16)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('H', 52, 49, 35, 17)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('I', 75, 35, 45, 17)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('J', 35, 35, 25, 17)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('K', 10, 45, 35, 17)
insert into Student_Ranks (Student_Name, Maths, Physics, Chemistry, Age)
values ('L', 5, 35, 56, 17)
GO

select * from Student_Ranks
GO

--SQL Server 2005 code for ranking students.
--Ranks students by Total Marks. If equal, then checks for Maths, then Physics, then
--Chemistry. If all equal, then ranks the older one up.

select Row_Number() Over (Order by Total_Marks desc
,Maths desc
,Physics desc
,Chemistry desc
,Age Desc)
as Student_Rank, Student_Name
from
Student_Ranks
GO

--The code otherwise in SQL 2000 that would do the ranking.
--Identity allowed only with SELECT INTO.
--To gaurentee order, Top 100 Percent, Maxdop 1 and Order By are required.

select top 100 percent Student_Name
,Student_Rank = identity(int, 1, 1)
into #Temp_Student_Ranks
from Student_Ranks
Order by Total_Marks desc, Maths desc, Physics desc, Chemistry desc, Age Desc
option (maxdop 1)

select * from #Temp_Student_Ranks
GO
--Clean up.
Drop table #Temp_Student_Ranks
GO

--The college has to call candidates for disciple allotment in batches of 4.
--Who is in which batch/group as per their rank? The first group is called first, the second next
--and so on.

select NTILE(3) Over(Order by Total_Marks desc
,Maths desc
,Physics desc
,Chemistry desc
,Age Desc)
as Batch, Student_Name from Student_Ranks
GO

--In 2000, the same grouping of batches would have been done in the following way:
select top 100 percent Student_Name
,Student_Rank = identity(int, 1, 1)
,0 as Batch
into #Temp_Student_Ranks
from Student_Ranks
Order by Total_Marks desc, Maths desc, Physics desc, Chemistry desc, Age Desc
option (maxdop 1)
GO

update #Temp_Student_Ranks
set Batch = case
when Student_Rank between 1 and 4 then 1
when Student_Rank between 5 and 8 then 2
when Student_Rank between 9 and 12 then 3
else 4
end

select Student_Name, Batch from #Temp_Student_Ranks
GO
--Clean up.
Drop table #Temp_Student_Ranks
GO

You may observe, how easy it is in 2005 to rank and group students using ROW_NUMBER OVER() and NTILE() respectively. The same holds good for other items as far as ranking and grouping is concerned and as far as I have tried. And it performs better with SQL Server 2005 code than with SQL Server 2000 style code.

In my next article, I will come up with some tricky T-SQL code. Please let me know your opinions for scope of improvement.

SQL Server: Making a good product.

2007-10-12T11:25:00.000-05:00

I am a new blogger. Basically an SQL Server DBA and performance tuning guy, I am also interested in poetry, literature and music. The aim of this blog is to express ideas as they come, on any of these topics i.e. SQL Server, poetry, literature and music. All are welcome to join me and express their ideas. The thoughts and ideas expressed in this blog are personal to the blogger alone and nobody need to take any offence to it.

I love MS SQL Server 2000 more than any version of SQL Server and over all other RDBMS's. It was a well made product, Performance wise and feature-wise it was a product in which every feature present worked well. I always feel that the people who developed it did it it with commitment and were very meritorious. It did not have too many bells and whistles but it worked much better than the so-called 'feature-rich' RDBMS's from any vendor. It is so important to hire well-qualified people, for e.g people with a four year bachelor degree in engineering/technology or higher from premier institutes and a good experience atleast in the technical field. Then the product they make or are asked to make would be competent and work efficiently.
These days, companies hire just about anybody based on a voluminous resume. The purpose is to save money because such people have to be paid less. In outsourcing scenarios, such people add to only to headcount and consequently to revenue of the contracted company. But what happens in the bargain is that although the assignment or the product may give results, it may not be efficient or robust. Making something and making something good are two different things. Companies get satisfied with making something and when experts start using these products after purchsing them, such products do not stand the test or pressures of production environments and the purchaser ends up hiring somebody else to make it better or purchasing higher and higher end hardware to make it work to his satisfaction. So in all, the customer loses his investment and gets poor returns. That is why it is so important to make a good product like SQL Server 2000. I remember a few years ago when I was running a poorly written code on SQL Server 7.0. It took around 13 minutes to get executed to completion. Then I ran the same lengthy query in SQL Server 2000. There was no difference in hardware or resource allocation between the two setups. The same query ran on SQL Server 2000 in 7 seconds. Of course I later optimized it and it ran in 00 seconds. But what my point here is that without any changes to code, SQL Server 2000 gave me drastic improvement out of the box with no inputs from my end. This is just one example. I have never seen something similar or other instances of performance improvement out of the box in any other version or RDBMS. Although my clients like to go to newer versions or switch RDBMS's and I participate in upgrades and migrations for them, I still feel that they should stick to a good database like SQL Server 2000 and not to get swayed by rhetoric, bells and whistles. In my opinion, nothing is greater than performance.
I had another client who felt he had an excellent 64-bit hardware and yet the performance was not upto mark. Somebody asked him to upgrade the hardware further and the nclient was not interested. Users of this website i.e. his customers had to wait 10 to 15 seconds on an average for responses. I took their top 10 SP's and optimized them. Each one of these SP's was called thousands of times a day. After optimization, testing and promoting these 10 optimized SP's to production, the total CPU utilization came down from 312 hours to 49 hours. This was in SQL Server 2000. The beauty of this product was that it gave you ample flexibility to code with your imagination only being the limiting factor. Not the same with other DB's though.
But now a days there are so many bells and whistles to RDBMS's that real T-SQL is getting drowned out. That is a sad state of affairs. T-SQL beats CLR in speed in almost all cases (except possibly string manipulation within variables) and yet nobody realises how important T-SQL is. In a good product, all other coding techniques should be part of T-SQL and NOT T-SQL being part of other methods. I again reiterate, simplicity, efficiency, flexibility and performance are more important than the cacophony that the bells and whistles make.
One more thing I like about SQL Server 2000 was the small learning curve. You learn quickly and then you use your imagination to bring out the best possible results. I have seen in some other RDBMS's that the learning curve is so involved and long and flexibiliy so little that the learners keep learning till the next version comes out and then start learning the next version without getting ample time to apply with imagination what they learnt. This is sad and ridiculous. Products should be made keeping the interests of the customer in mind and not keeping the limitations in thoughts of the architects in mind.

In my next post, we would look at some possibly cool coding in T-SQL. Bye for now.