Wednesday, January 2, 2008

Why use object type identifier ?

Following is the typical T-SQL statement found in most of the database object deployment scripts. Let me describe the script a little bit - the below script will find the object "[dbo].[fnHasMarketingOptions]" which is of TYPE FN (Scalar function), IF (In-lined table-function) OR TF (Table function) and if FOUND it will drop that function.


    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fnHasMarketingOptions]') and xtype in (N'FN', N'IF', N'TF'))

    DROP FUNCTION [dbo].[fnHasMarketingOptions]
    GO


I don't understand why use "and xtype in (N'FN', N'IF', N'TF')" this part - because anyway you can't create a TABLE and PROCEDURE/FUNCTION with the same names in a database. Below is the illustration T-SQL statements,



    CREATE TABLE Test (col1 varchar(30))

    GO

    CREATE PROC test
    AS
    SELECT GETDATE()

Here is the error message….

    Msg 2714, Level 16, State 5, Procedure test, Line 4
    There is already an object named 'test' in the database.

If two object types (TABLE/PROCEDURE/TRIGGER) cannot be created with same name then why use "and xtype in (N'FN', N'IF', N'TF')" ?

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.