whynot

Web Architecture

2007-11-02T04:46:00.000-07:00

I had worked on a web site for one of my client and I am sharing the web architecture diagram for latter use. The web architecture diagram is developed using MS-VISIO tool and the flow for the web architecture is described.

The requirement from the client is to allow the on-line customer to view their account status, contract & payment details, create user, modify profile, send note to admin and subscribe newsletters.

Flow Descriptions

(1) Customer requests for the account status page from the webserver.
(2) ASP.NET renders the welcome page with the login screen and presents the web page to the customer.
(3) Customer types their user id and password and clicks on the [submit] button on the webform.
(4) ASP.NET calls the customer web services to call procedure in database via the sbl db components.
(5) Stored procedure verify the username and password if it is exists in the database table and the result is sent back through the db components, webservice and to ASP.NET.
(6) If the customer is not found within the database , the FEW will send the "Access Denied" screen to the customer.
(7) If the customer is found in the database, FEW invokes a method from the customer web service to call procedure in database via the db components.
(8) Stored procedure execute and retrieve the contract details that is belongs to the customer and the result is sent back through the db components, webservice and to ASP.NET.
(9) ASP.NET renders contract details page and present(s) contract summary & contract details in contract summary tab to the customer.
(10) Customer clicks on either payment summary link, payment due link, send note tab, register tab, modify profile tab and sign up link.
(11) ASP.NET retrieves data from web service that pertains to the selected link or tab via the db components in database and ,
(12) Stored procedure execute (the action or select queries) and retrieve the results and the result is sent back through the db components, webservice and to ASP.NET.
(13) ASP.NET renders the page for the selected link or tab to the customer.

SQL Server Integration Services Automation

2007-11-01T05:40:00.000-07:00

Automation Process

I have created five SSIS package for five source systems. I have scheduled the SSIS package as a job using SQL Server Agent. The jobs have been scheduled to run on every hour on weekdays. You could change the frequency interval at your convenience. The primary (Control Report) job has been enabled by default and rest of jobs is not enabled & rename with suffix as "-- Disabled".

The enabled job would start execute, calculate & set the schedule start date for the subsequent month, rename (suffix as "-- Disabled") & disable the job on success. Rename (remove suffix as "-- Disabled") & enable the next job for execution. The cycle spins for other jobs until the final job for a month.

Let us read further to find out how the "Thought Process" has been executed in SQL Server 2005 using integration services.

Display row value as a TABLE column using SQL select statement

2007-10-26T21:21:00.000-07:00

Requirement:

Summarize PIFAmt column divide by Summarize Factors column by StateName column and diplay prior & current year data as a table column by auto and property business values.

Table Structure:

Table Data:

You could avoid the following errors when you execute the enclosed SQL select statement and display the result as I explained in my requirement.

Errors:

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Server: Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SQL Select Statement:

SELECT STATENAME,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_CURR_YR,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_CURR_YR
FROM FACT
WHERE BUSINESS IN ('AUTO', 'PROPERTY') AND EFFYEAR >= 2006 AND
SEGMENT = (CASE WHEN BUSINESS = 'AUTO' THEN SEGMENT ELSE 'HOMEOWNERS' END)
GROUP BY STATENAME

GO

Results:

An Introduction to Clustered and Non-Clustered Index Data Structures

2007-10-17T20:05:00.001-07:00

When I first started using SQL Server as a novice, I was initially confused as to the differences between clustered and non-clustered indexes. As a developer, and new DBA, I took it upon myself to learn everything I could about these index types, and when they should be used. This article is a result of my learning and experience, and explains the differences between clustered and non-clustered index data structures for the DBA or developer new to SQL Server. If you are new to SQL Server, I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code I have provided in order to more fully understand and appreciate the differences between clustered and non-clustered indexes.

When To Use the Order By Clause

2007-10-17T19:59:00.000-07:00

When I was using SQL Server, I was not using the "order by" clause properly. I always say myself that when I want the data in a particular order, I should use order by clause in select statement otherwise leave to the SQL Server. I presumed MS-SQL Server will return the data in its own order. I was not even thinking that am I using and doing correctly. In my initially days in my career, I was happy to retrieve the data and not knowing how it comes. It is the nature for the software engineers at the premature stage or using new command or tool or technology. They will not knowing properly or doing enough research of the each command before commence using the command. I would say it could be because of competition in the IT field and immature in IT field.

Where do we get the word "Order?" I have heard and seen in so many places the use of "Order" word. I have come across the listed places the "Order" word used regularly.

When an applicant applies for a student visa, visa officer will politely inform to the application order the applications and come back.
When a procurer gives a draft to the registrar officer, officer will politely return back the draft and request to order the draft.
Firm will display the designation in a proper order.
Construct a house; we will clean and even the land before establishing the Pillar.

The ultimate aim is "First IN has to first come OUT"

I have decided and kept in my mind that somewhere down the line in my IT career, I should know when I should use "order by" clause in the select statement. I know it will be on one FINE DAY". I will spend some time on "order by" command. That one fine day is TODAY to explore myself and find the ways to use the "order by" clause. This could be useful for a new developer and dba to SQL Server to avoid confusion on using "order by clause" and knowing when to use "order by" clause command.

To come along with me in this article, I have paste the script and result of the output. Example of the script can be used in a sql server to understand completely after reading the article.

User-defined data type dependencies

2007-10-17T19:12:00.000-07:00

MSSQLServer has got sp_depends system sp to display the dependencies for a stored procedure, table and view. sp_depends does not support to display the User-defined data type dependencies. For custom database, we will create user defined data types (UDDT) and add this UDDT to a column/variable in a table/stored procedure. This sp (UDTDepends) will help to display the User-defined data type dependencies in a database for a Tables and Stored Procedure.

Compile this sp in a database and execute the same. It will display the dependencies for the user defined data types if it exists in a database.

Criticize are welcome.

if exists (select * from sysobjects where id = object_id(N'[dbo].[UDTDepends]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UDTDepends]
GO

Create Proc UDTDepends
As

SET NOCOUNT ON

SELECT so.name As "ObjectName", sc.name As "ColumnName", type_name(st.xusertype) As "UserDefinedDataType",
type_name(sc.xtype) As "DataType", st.length As "DataLength"

FROM sysobjects so, syscolumns sc, systypes st

WHERE so.xtype In ('U', 'P') and sc.id = so.id and sc.xusertype = st.xusertype and
sc.usertype Not In (0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 19, 20, 21, 22, 23, 24, 80)

ORDER BY so.xtype desc, so.name, st.name

SET NOCOUNT OFF

Stabilize Index

2007-10-17T19:08:00.000-07:00

This sp is used to update index in a database. This sp has got three optional varchar parameters.
First parameter will take the tablename. Second parameter will take the indexname. Third parameter will take the fillfactor.

The following option we can execute the sp in a user database.

1. exec StabilizeIndex
It will update all index in a user database

2. StabilizeIndex 'TableName'
It will update all index in a table

3. StabilizeIndex 'TableName', 'IndexName'
It will update an index in a table.

4.StabilizeIndex 'TableName', 'IndexName', ‘FillFactor’
It will update an index in a table with fill factor.

Note: This is not used in system database.

/* To Execute the sp in various options
--StabilizeIndex
--StabilizeIndex 'TableName'
--StabilizeIndex 'TableName', 'IndexName'
--StabilizeIndex 'TableName', 'IndexName', 'FillFactor'
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[StabilizeIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StabilizeIndex]
GO

Create Proc StabilizeIndex
(
@TableName Varchar(100) = '',
@IndexName Varchar(100) = '',
@FillFactor Varchar(3) = ''
)
As

Declare @TableID Int, @ObjectName Varchar(100), @IndName Varchar(100), @SQL Varchar(8000)

SET NOCOUNT ON

IF Len(@TableName) = 0
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U'
ORDER BY name
End
Else
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U' and id = object_id(@TableName)
ORDER BY name
End

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN

IF Len(@IndexName) = 0
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and indid > 0
End
Else
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and name = @IndexName and indid > 0
End
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @IndName

WHILE @@FETCH_STATUS = 0
BEGIN
IF Len(@FillFactor) = 0
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ')'
End
Else
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ',' + @FillFactor + ')'
End

Exec (@SQL)

FETCH NEXT FROM index_cursor INTO @IndName

END

CLOSE index_cursor
DEALLOCATE index_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName
END

CLOSE table_cursor
DEALLOCATE table_cursor

SET NOCOUNT OFF

ClearData

2007-09-10T19:14:00.000-07:00

We can use this sp to clear data in tables. I mainly created this sp for two reason. 1.This is useful to commence the testing from scratch. 2.Whenever we ask the database backup from our customer. They hesitant to give the production database because of confidential. I have used Delete and Truncate statement to clear the data. Please refer SQL Books online difference of Delete and Truncate statement.

/* To Execute the sp
--exec ClearTableData
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[ClearTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClearTableData]
GO

Create Proc ClearTableData
As

Declare @TableName Varchar(1000), @Reference Varchar(4), @SQL Varchar(8000), @ObjectName Varchar(8000),
@TabName Varchar(1000)

SET NOCOUNT ON

If db_name() In ('master', 'model', 'msdb', 'tempdb')
Begin
Print 'system database are not allowed to clear'
return 0
End

Set @ObjectName = ''
Set @TabName = ''

-- Delete data in Child Object and its Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select SO.Name "ChildObject",
Case When (Select Count(*) From SysReferences Where rkeyid = SO.id) = 0 Then 'N' Else 'Y' End As Ref
From SysObjects SO, SysReferences SR, SysObjects SO1
Where SO.id = SR.fkeyid And SO.xtype = 'U' And SO1.ID = SR.rkeyid
Order By SO1.Name Desc, SO.Name
OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference

WHILE @@FETCH_STATUS = 0
BEGIN
If @Reference = 'Y'
Begin
-- Concatenate Primary Key Table name
Set @ObjectName = @ObjectName + ',' + @TableName
End
Else If @Reference = 'N'
Begin
Begin
-- Clear data from Foreign Key Tables(Child object)
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor

If Len(@Reference) > 0
Begin
-- Clear data for Concatenated Primary Key Tables
Set @ObjectName = Right(@ObjectName,Len(@ObjectName)-1)

While Len(@ObjectName) > 0
Begin
If (CHARINDEX(',',@ObjectName) > 0)
Begin
Set @TabName = Substring(@ObjectName, 1, CharIndex(',',@ObjectName)-1)
Set @ObjectName = SUBSTRING(@ObjectName, Len(@TabName) + 2, Len(@ObjectName))
End
Else
Begin
Set @TabName = @ObjectName
Set @ObjectName = ''
End
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TabName + ']'
exec (@SQL)
End
End
End
End
--Delete Data in Standalone Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id In (Select rkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TableName + ']'
exec (@SQL)
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
-- Delete data for BaseDate/Master Table
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name

OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

Begin
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
SET NOCOUNT OFF

Convert Unique Identifier to Varchar data type

2007-09-05T19:17:00.000-07:00

SET QUOTED_IDENTIFIER OFF

DECLARE @ObjectID Int, @ObjectName Varchar(100),
@EmptyString Char(1), @IsDefaultBound Char(1),
@ExecuteScript Varchar(8000), @IndexName Varchar(256),
@Loop Int

set nocount on

Create Table #TableInformation (ObjectID Int Not Null, ObjectName Varchar(100) Not Null,
Parent_ObjectID Int Not Null, Parent_ObjectName Varchar(100) Not Null,
ObjectType Char(2) Not Null)
-- IsRowGuid Char(1) Not Null, IsDefaultBound Char(1) Null)
DECLARE csrTableName CURSOR FOR
SELECT SO.ID, SO.Name
FROM SysObjects SO
WHERE SO.xType = 'U'
ORDER BY SO.Name

Set @EmptyString = ''

OPEN csrTableName

FETCH NEXT FROM csrTableName
INTO @ObjectID, @ObjectName

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN

-- 36 = Unique Identifier data type

--Generate Script to Drop Foreign Constraint & Select statement will generate Foreign Key ID, Foreign Key Name,
--Parent Object ID & Name for Foreign Key and Object Type As "FK"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
From SysForeignKeys Where rkeyID = @ObjectID
Union
Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
From SysForeignKeys Where fkeyID = @ObjectID

--Generate Script to Drop Primary and Unique Constraint & Select statement will generate Primary Key ID,
--Primary Key Name, Parent Object ID & Name for Primary Key and Object Type As "PK"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select ID, Object_Name(ID), @ObjectID, @ObjectName, 'C'
From SysObjects Where Parent_Obj = @ObjectID And xType In ('PK', 'UQ')

--Generate Script to Default Constraint & Select statement will generate Parent Object ID, Default Column Name &
--Parent Object ID & Name for Default Column and Object Type As "D"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select cDefault, Object_Name(SC.cDefault), @ObjectID, @ObjectName, 'C'
From SysColumns SC, SysComments SCM
Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsDefaultCnst') = 1 And
SC.xType = 36

--Generate Script to Drop ROWGUID & Select statement will generate RowGUID Column ID, RowGUID Column Name &
--Parent Object ID & Name for RowGUID Column and Object Type As "RG"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select ID, Name, @ObjectID, @ObjectName, 'RG'
From SysColumns Where ID = @ObjectID And Number = 0 And ColumnProperty(@ObjectID, Name, 'IsRowGuidCol') = 1

--Generate Script to Alter Column to Varchar(38) & Select statement will generate UniqueIdentifier Type ID,
--UniqueIdentifier Column Name, Parent Object ID & Name for UniqueIdentifier Column and Object Type As "UI"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select xType, Name, ID, Object_Name(ID), 'UI'
From SysColumns Where ID = @ObjectID And xType = 36

--Generate Script to UnBind Defaults for ID Column & Select statement will generate Parent Object ID,
--Default Column Name & Parent Object ID & Name for Default Column and Object Type As "DF"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select @ObjectID, SC.Name, @ObjectID, @ObjectName, 'DF'
From SysColumns SC, SysComments SCM
Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsConstraint') = 0 And
SC.xType = 36

Declare csrIndexName CURSOR FOR
Select Name From SysIndexes
Where ID = @ObjectID

OPEN csrIndexName

FETCH NEXT FROM csrIndexName
INTO @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Loop = 0
While @Loop < 10
Begin
IF Exists (Select SC.Name, SC.ID From SysColumns SC, SysIndexes SI
Where SC.ID = SI.ID And SI.ID = @ObjectID And
SI.Name = @IndexName And SC.xtype = 36 And SI.Name Not Like '%_WA_%' And
SC.Name = Index_col(Object_Name(@ObjectID), SI.indid,@Loop))
Begin
--Generate Script to drop Index & Select statement will generate Parent Object ID of the Index Column,
--IndexName, Parent Object ID & Name of the Table Name, and Object Type As "I"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select @ObjectID, @IndexName, @ObjectID, @ObjectName, 'I'
Break
End
Set @Loop = @Loop + 1
End

FETCH NEXT FROM csrIndexName
INTO @IndexName
END

CLOSE csrIndexName
DEALLOCATE csrIndexName

FETCH NEXT FROM csrTableName
INTO @ObjectID, @ObjectName
END

CLOSE csrTableName
DEALLOCATE csrTableName

PRINT @EmptyString

Select * From #TableInformation Order By ObjectType

PRINT @EmptyString

DECLARE csrScript CURSOR FOR

Select Case When ObjectType = 'DF' Then
'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Name = "' + ObjectName + '" And cDefault > 0 )' + Char(13) +
'Exec sp_unbindefault "[' + Parent_ObjectName + '].' + ObjectName + '"' + Char(13) --+ 'GO'

When ObjectType = 'RG' Then
'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Number = 0 And
Name = "' + ObjectName + '" And ColumnProperty(Object_ID ("[' + Parent_ObjectName + ']")' + ', Name, "IsRowGuidCol") = 1 )' + Char(13) +
'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] DROP ROWGUIDCOL' + Char(13) --+ 'GO'

When ObjectType = 'C' Then
'If Exists (Select ID From SysObjects Where ID = Object_ID("[' + ObjectName + ']") And Parent_Obj = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) +
'ALTER TABLE [' + Parent_ObjectName + '] DROP CONSTRAINT [' + ObjectName + ']' + Char(13) --+ 'GO'

When ObjectType = 'I' Then
'If Exists (Select ID From SysIndexes Where Name = "' + ObjectName + '" And ID = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) +
'DROP INDEX [' + Parent_ObjectName + '].' + ObjectName + + Char(13) --+ 'GO'

When ObjectType = 'UI' Then
'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] Varchar(38)' + Char(13) --+ 'GO'

End As "Script"
From #TableInformation Order By ObjectType

Set @ExecuteScript = ''

OPEN csrScript

FETCH NEXT FROM csrScript
INTO @ExecuteScript

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN
--Print (@ExecuteScript)
Exec (@ExecuteScript)
FETCH NEXT FROM csrScript
INTO @ExecuteScript
END

CLOSE csrScript
DEALLOCATE csrScript

Drop Table #TableInformation

set nocount off

GO

Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server

2007-09-02T20:04:00.000-07:00

When I first started using SQL Server as a novice, I was initially confused as to the differences between the SELECT statement, views, and stored procedures. They all seemed to perform more or less the same task (retrieve data), and I wanted to know the pros and cons of using each.

Why would SQL Server offer three different options to retrieve data from database? As a developer and new DBA, I took it upon myself to learn everything I could about these options, why they may be required, and when they should be used. This article is a result of my learning and experience, and explains the differences between SELECT statements, views, and stored procedures for the DBA or developer new to SQL Server. I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code into Query Analyzer I have provided in order to more fully understand and appreciate the differences between the SELECT statement, views, and stored procedures. I have divided this article into three parts to better explain this information.

Stored Procedure vs Triggers

2007-08-31T20:00:00.000-07:00

Last time, I wrote an article and focussed on Using SELECT, Views and Stored Procedures in SQL Server. I was quite glad to differentiate the three and its Pros & Cons. I was not even given much importance of Trigger not because of NO TIME. To be honest, I was just a beginner in use of Triggers. In this article I decided to write about Triggers and its difference with Stored Procedure. We had a team meeting to incorporate the audit information in our organization.

After a few months, we had a debate to incorporate the audit information either in Stored Procedure or Trigger. In the meeting, the set of team member has suggested having audit statements in Triggers and another set of team member has recommended having audit statements in Stored Procedures. I was totally confused to take a decision. Then, I told my team members to stop the debate. I requested my team members to involve in R&D for few days before come to the conclusion and judge the Pros & Cons of the two. This is how the article has been born to have audit information in our project. I devoted my influence to write an article on Stored ProcedureVsTrigger and to differentiate the two and its Pros & Cons.

So, how do I start! I decided to use some information from my previous article http://www.sql-server-performance.com/gv_sp_views_selects.asp. I have introduced the characters that would be involving in the article before the initiative. These characters would play important role in the article.

When To Use the Order By Clause

2007-08-31T19:59:00.000-07:00

DisplayColumnNames

2007-08-15T19:16:00.000-07:00

This sp will return the column names for a table(s) and form a "Select Statement" for a table(s). We can pass a table name as a parameter to display a "Select Statement" for that table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[spDisplayColumnName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spDisplayColumnName]
GO
Create Proc spDisplayColumnName (@TableName Varchar(100)='') As
--Exec spDisplayColumnName 'Account'
--Or
--Exec spDisplayColumnName
Declare @ColumnName Varchar(8000), @TableID Int

Set Nocount On

If Len(@TableName) = 0
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where xType = 'U' Order By SO.Name
End
Else
Begin
If Not Exists(Select 1 From SysObjects Where ID = Object_ID(@TableName) And xType = 'U')
Begin
Print 'Passed parameter [' + @TableName + '] is not an User table'
Return 0
End
Else
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where ID = Object_ID(@TableName)
And xType = 'U' Order By SO.Name
End
End

OPEN DisplayColumnName_Cursor

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID

WHILE @@FETCH_STATUS = 0
Begin
Set @ColumnName = ''
Select @ColumnName = @ColumnName + ', ' +Name
From SysColumns Where ID = @TableID
Order By Name
Print Object_Name(@TableID)
Select 'Select ' + Right(@ColumnName,Len(@ColumnName)-1) + ' From ' + Object_Name(@TableID)

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID
End
CLOSE DisplayColumnName_Cursor
DEALLOCATE DisplayColumnName_Cursor

Set Nocount Off
Return 0

SQL Server: Execution Plans

2007-07-23T19:55:00.000-07:00

When I started using SQL Server, I was not using the “Show Execution Plan” properly to analyse the query. I always thought that when I compose a query, it is the best it can be; I never even think of performance on my query and I did not give much importance to looking at the “Execution Plan” on my query. In the initial days of my career, I was happy to retrieve the data, not even knowing how it was returned to me and what sort of “Execution Plan” was used on the query. I presumed SQL Server would handle the query performance. I think it is the nature for software engineers, when starting out, or when first learning new technology, not to take the time to learn everything they really need to know before writing a code. Perhaps this is because of competition and immaturity in the IT field.

Days are starting moving and data has been growing on the database file. On one fine day, my customer was not happy with the performance on the query using the application. He has come to me with an unhappy face stating that he was spending extra time to finish his office work due to the slowness of the queries. Initially, I told the customer to increase the system resources, like increase the hard disk in the machine as a temporary solution. Albeit, hard disk cost is cheap but it is not the permanent solution for the performance degradation in the query. He agreed in half-mind that he will do the required things from his side but he asked me to re-look and fine-tune the queries for a permanent solution instead of suggesting he keep on increasing the system resources. I have to consider his personal opinion because customer satisfaction is important in the IT industry. I have promised to my customer that I will re-look and fine-tune the queries.

HOW?

In the initial days of my career, I know the basic things in MS-SQL Server. To be frank, I was not having any idea on my mind whilst doing promise to my customer. But, I personally felt that I would do something to achieve the task with the help of “GOOGLE” and “BOL”. Thanks to “GOOGLE” and “BOL”.

I was reading MS-SQL books, BOL help and searching on websites. I have heard and crossed the concept of “Show Execution Plan”. We can set this option ON using SQL Query Analyzer. Show Execution Plan is an important graphical tool that enables the developer and DBA to analyse, assist and optimise the query and improve the performance of the query.

Show Execution Plan displays different icons for a different task. I am mainly interested on “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” icons in this article. May be I could be writing in my future article on other icons.

As the days and years have moved along like a formula 1 race car, I have decided that it is time for me to fully understand how “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “ClusteredIndex Seek” icons work.

Now, I am ready to analyse the queries and upgrade the performance on my queries. Before analysing the queries, a few questions have been raised in my mind.

When did MS-SQL Server use “Table Scan”?

When did MS-SQL Server use “Index Scan”?

When did MS-SQL Server use “Index Seek”?

When did MS-SQL Server use “Clustered Index Scan”?

When did MS-SQL Server use “Clustered Index Seek”?

I am mainly concern that in what bases MS-SQL Server would use one of this option to analyse the query. I now have taken that time, and here is what I learned. This information should be useful to new developers and DBAs. I have decided to write this article to share my knowledge to help other to get better idea on these methods.

If you like, you can read this article as is, or in front of a SQL Server, following along with my exercises.

DependencyDetails

2007-07-23T19:15:00.000-07:00

This sp will return the dependency details of the select table or all tables as a resultset. This resultset will
display the dependency details if it is used in
1. select * from table or
2. insert/delete/update dml statement or
3. select col1, col2 from table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[DependencyDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DependencyDetails]
GO

Create Proc DependencyDetails (@TableName As Varchar(100)='')
As

SET NOCOUNT ON

Declare @MinTableID Int, @MaxTableID Int

IF Len(@TableName) = 0
Begin
Select @MinTableID = Min(ID), @MaxTableID = Max(ID) From SysObjects Where xType = 'U'
End
Else IF Len(@TableName) > 0
Begin
IF Exists (Select ID From SysObjects Where Name = @TableName And xType = 'U')
Begin
Select @MinTableID = ID, @MaxTableID = ID
From SysObjects Where Name = @TableName And xType = 'U'
End
Else
Begin
Print 'Passed parameter [' + @TableName + '] is not an User Table'
Return 0
End
End
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Select *' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SelAll = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Insert/Update/Delete' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SD.ResultObj = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Selected Columns' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SD.ReadObj = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Order By SO.Name

SET NOCOUNT OFF

Replace Object Owner

2007-07-21T19:18:00.000-07:00

This SP is used to identify and replace the owner of the database objects. This SP will give display object id,
object name, owner id, owner name and concatenate object + owner name with brackets. SP does change the owner for the following objects. SP should available in "master" database to execute in more than one database.

--$History: $ sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- Drop Procedure
--------------------------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_ReplaceObjectOwner' AND type = 'P')
DROP PROCEDURE sp_ReplaceObjectOwner
GO

--------------------------------------------------------------------------------------------------------------------------------------------
-- Create procedure
--------------------------------------------------------------------------------------------------------------------------------------------

CREATE PROC sp_ReplaceObjectOwner As

--------------------------------------------------------------------------------------------------------------------------------------------
-- OBJECT NAME: sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- AUTHOR: Vijayakumar G
--------------------------------------------------------------------------------------------------------------------------------------------
-- CREATED: 04-04-2005
--------------------------------------------------------------------------------------------------------------------------------------------
/* DESCRIPTION: This SP is used to identify and replace the owner of the database objects. This SP will give display object id,
object name, owner id, owner name and concatenate object + owner name with brackets. SP does change the owner for the following objects.
SP should available in "master" database to execute in more than one database.
1. Table
2. Stored Procedures
3. Function
4. View
We can later add / modify the filter condition */
--------------------------------------------------------------------------------------------------------------------------------------------
-- PARAMETERS: None
--
--------------------------------------------------------------------------------------------------------------------------------------------
-- RETURNS: 0 - success
--
-- returns from SQL Server
--------------------------------------------------------------------------------------------------------------------------------------------

-- VERSION: $Revision: $ UPDATED: $Modtime: $
--------------------------------------------------------------------------------------------------------------------------------------------
set nocount on

DECLARE @Object_ID Int, @Object_Name Sysname,
@Owner_ID Int, @Owner_Name Sysname, @Owner_Object_Name Sysname

Declare @New_Owner_Name Sysname
Set @New_Owner_Name = 'dbo'
DECLARE CUR_ReplaceObjectOwner CURSOR FOR

Select
SO.ID As "Object ID", SO.Name As "Object Name",
SO.UID As "Owner ID", SU.Name As "Owner Name",
'[' + SU.Name + '].' + '[' + SO.Name + ']' As "Owner + Object Name"
From
SysObjects SO, SysUsers SU
Where
SO.UID = SU.UID And
SO.xType In ('U','P', 'FN','V') And
SO.Name Not Like '%dt_%' And
SO.Name Not In ('syssegments', 'sysconstraints')
Order By xType

OPEN CUR_ReplaceObjectOwner

FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name

WHILE @@FETCH_STATUS = 0
BEGIN
If @New_Owner_Name != @Owner_Name
exec sp_changeobjectowner @Owner_Object_Name, @New_Owner_Name
If @@Error != 0
Begin
Return 1
End
-- Get the next author.
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
END

CLOSE CUR_ReplaceObjectOwner
DEALLOCATE CUR_ReplaceObjectOwner

set nocount off
return 0

GO