none
You should not be writing UDFs of any kind?

Answers

  • UDF's are a big bucket, containing

    -TSQL Inline Table-Valued UDFs

    -CLR Scalar-Valued UDFs

    -CLR Table-Valued UDFs

    -TSQL Multi-Statement Table-Valued UDFs

    -TSQL Scalar-Valued UDFs

    My guidance is that:

    You should generally avoid using scalar-valued UDFs that read the database.  It's not a good way to encapsulate data access: write better queries instead.

    You should generally avoid TSQL Scalar-Valued UDFs, as they prevent parallelism in the query plan.

    All the other kinds of UDFs are occasionally useful and not at all harmful.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, June 30, 2013 6:20 PM
  • Hi Kalman,

    most topics have been discussed. But please keep in mind to avoid multi-statement-level functions because they cannot use any statistics from the underlying relations as inline functions will do.

    Take the following example from AdvenureWorks2012

    USE AdventureWorks2012
    GO
    
    CREATE FUNCTION dbo.multi_statement(@orderdate datetime)
    RETURNS @return_set TABLE
    (
    	SalesOrderId	int,
    	OrderDate	datetime
    )
    AS
    BEGIN
    	INSERT INTO @return_set
    	SELECT	SalesOrderId,
    			OrderDate
    	FROM	sales.SalesOrderHeader
    	WHERE	OrderDate = @OrderDate
    
    	RETURN
    END
    GO
    
    CREATE FUNCTION dbo.inline_statement(@orderDate datetime)
    RETURNS TABLE
    AS
    	RETURN
    		SELECT	SalesOrderId,
    				OrderDate
    		FROM	sales.SalesOrderHeader
    		WHERE	OrderDate = @orderDate
    GO

    Now execute both functions with the same value for @OrderDate

    DECLARE	@OrderDate datetime = '20050701'
    SELECT * FROM dbo.multi_statement(@OrderDate);
    SELECT * FROM dbo.inline_statement(@OrderDate);

    If you have a look to the execution plan and the profile you see why both functions will use different execution plans!

    The query processor replaces an inline UDF reference with its definition; in other words, the query processor “expands” the UDF definition and generates an execution plan accessing the underlying objects and can use the statistics!

    So - from my point of view - multi-statement-function maybe harmful :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Monday, July 01, 2013 5:37 AM
  • >> you program better the following AdventureWorks2012 table-valued function? <,

    The DDL stinks and is full of ISO-11179 violations. Heck, nobody even got the word about Pascal and camelCase! For example, the USPS allows 35 characters for an address, not fifty characters from an old ACCESS and BASIC default value. Nobody would use an integer for a tag number as that silly generic “duns” (what role does it have in the data model? Can it be lawful person? Etc?). AdventureWorks was meant to be a demo for every possible feature in T-SQL they could load into it. 

    Unfortunately, they had no idea how to do a valid data model, so it sucks. Instead of “Personnel”, the name of the abstract set, they used the singular noun “Employee”, “Person” is both a database and a table, etc. 

    There is no such thing as a “type_id”; the attribute property can be a “<something>_id” or a “<something>_type” not a monster hybrid. What is your “blood_type_id_value_code”? See how silly extra attribute properties are? And since there are not a lot of types in this nominal scale, it ought be a CHECK() clause, not a table. 

    The DUNS is how a business is identified; apparently this is also how they identify their personnel. Otherwise, we have Kabbalah numbers and magic. 

    The most portable way would be to create “Automobiles, Squids and Lady Gaga” VIEW from the fragmented universes (every schema is supposed to be its own little universe of discourse) in this non-data model. A very rough skeleton would be simple: UNION ALL the contact attributes from the tables that have been corrected to model entities, instead of a forced OO concept of data with Person as a super-type. 

    CREATE VIEW Global_Magical_Contacts -- rough skeleton
    (duns, first_name, last_name, contact_type, contact_source)
    AS 
    SELECT duns, first_name, last_name, job_title, 'Employee'
     FROM Personnel AS P
    UNION ALL
    SELECT duns, first_name, last_name, vendor_name, 'Vendor' 
     FROM Vendors AS V
    UNION ALL
    SELECT duns, first_name, last_name, store_name, 'Store' 
     FROM Stores AS S
    UNION ALL 
    SELECT duns, first_name, last_name, NULL, 'Customer' 
      FROM Customers AS C; 

    Every TVF I have seen should have been VIEW. I can nest VIEWs, use WITH CHECK OPTION, port my code and give the optimizer a fighting chance. Now use it in a vanilla SELECT: 

    SELECT * 
     FROM  Global_Magical_Contacts
    WHERE duns = @in_duns;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, July 01, 2013 11:17 PM
  • Hallo Kalman,

    exactly the same due to multiple reasons:

    - no relation in the query who's statistics can be used
    - estimated rowcount = 1

    Usage of multi statement functions ALWAYS have 1 record as "estimated" records. You can check it with a really simple demo followed by this line:

    USE tempdb;
    GO
    
    CREATE TABLE dbo.tbl_foo
    (
    	MasterId	int,
    	DetailId	int
    );
    
    CREATE CLUSTERED INDEX ix_tbl_foo ON dbo.tbl_foo (MasterId, DetailId);
    GO
    
    INSERT INTO dbo.tbl_foo
    VALUES
    (NULL, 1),
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 5),
    (3, 6),
    (3, 7)
    
    -- Now the creation of two functions
    -- multi = multi statement function
    -- inline = inline function
    CREATE FUNCTION dbo.multi()
    RETURNS @Result TABLE
    (
    	EmployeeId	int,
    	Level		int
    )
    AS
    BEGIN
    	;
    	WITH cte (EmployeeId, Level)
    	AS
    	(
    		SELECT	DetailId	AS	EmployeeId,
    				1			AS	Level
    		FROM	dbo.tbl_foo
    		WHERE	MasterId IS NULL
    
    		UNION ALL
    
    		SELECT	f.DetailId	AS	EmployeeId,
    				c.Level + 1	AS	Level
    		FROM	dbo.tbl_Foo f INNER JOIN cte c
    				ON (f.MasterId = c.EmployeeId)
    	)
    	INSERT INTO @Result (EmployeeId, Level)
    	SELECT * FROM cte
    	
    	RETURN
    END
    GO
    
    CREATE FUNCTION dbo.inline()
    RETURNS TABLE
    AS
    RETURN
    (
    	WITH cte (EmployeeId, Level)
    	AS
    	(
    		SELECT	DetailId	AS	EmployeeId,
    				1		AS	Level
    		FROM	dbo.tbl_foo
    		WHERE	MasterId IS NULL
    
    		UNION ALL
    
    		SELECT	f.DetailId	AS	EmployeeId,
    				c.Level + 1	AS	Level
    		FROM	dbo.tbl_Foo f INNER JOIN cte c
    				ON (f.MasterId = c.EmployeeId)
    	)
    	SELECT * FROM cte
    )
    GO

    OK - now I execute both queries:

    SET STATISTICS PROFILE ON;
    GO
    
    SELECT * FROM dbo.multi();
    SELECT * FROM dbo.inline();

    The statistics for multi are as expected (estimated = 1). The execution plan looks pretty smart (and horrible) for the inline function :)

    The look to the io statistics decribes the extrem difference, too :)

    Table '#BD90D330'. Scan count 1, logical reads 1,...

    Table 'tbl_foo'. Scan count 8, logical reads 16,...
    Table 'Worktable'. Scan count 2, logical reads 43,...


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, July 01, 2013 9:54 AM
  • This how the original "FN-" prefixed line of code was implemented in FORTRAN II  and then in 1960's BASIC! It was literally a text macro substitution precompiler command. Did you ever wonder where that "FN" came from?

    I don't recall anything like that in Fortran II or Dartmouth Basic, and Google doesn't turn anything up in a quick search.  Platform?  References?

    And I have nothing against macro substitution anyway, I wish to heaven the SQL Server had it to implement constants, though there are other ways to implement constants as well.  I could think of some other large benefits to using macros in SQL as well.  Totally RDBMS, if it were only a little better than it is.

    Josh

    Tuesday, July 02, 2013 2:41 AM

All replies

  • What I think is that, we can avoide UDFs only when we have alternate solution or sql statement giving the same result. Almost every programming languages has the flexibility of writing user define functions becouse of the following reasons,

    1. Code Simplicity : UDFs makes easy for complex business logic or algorithm.

    2. Code Reusability : Sharing of UDFs for multiple purposes.


    Regards, RSingh

    Sunday, June 30, 2013 3:28 PM
  • Can UDF-s be really avoided in T-SQL programming? 

    The short answer is "Yes" of course. Remember when people asked "can the GOTO be avoided in procedural programming?" before the Structured Programming revolution? Well, welcome to declarative or functional programming! 

    I need to sit down and write an article on it, with examples. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, June 30, 2013 6:08 PM
  • UDF's are a big bucket, containing

    -TSQL Inline Table-Valued UDFs

    -CLR Scalar-Valued UDFs

    -CLR Table-Valued UDFs

    -TSQL Multi-Statement Table-Valued UDFs

    -TSQL Scalar-Valued UDFs

    My guidance is that:

    You should generally avoid using scalar-valued UDFs that read the database.  It's not a good way to encapsulate data access: write better queries instead.

    You should generally avoid TSQL Scalar-Valued UDFs, as they prevent parallelism in the query plan.

    All the other kinds of UDFs are occasionally useful and not at all harmful.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, June 30, 2013 6:20 PM
  • Thanks Joe.  A number of DMV queries work with CROSS APPLY taking DMF on the right hand side.

    How do you program better the following AdventureWorks2012 table-valued function?

    CREATE FUNCTION [dbo].[ufnGetContactInformation](@PersonID int)
    RETURNS @retContactInformation TABLE 
    (
        -- Columns returned by the function
        [PersonID] int NOT NULL, 
        [FirstName] [nvarchar](50) NULL, 
        [LastName] [nvarchar](50) NULL, 
    	[JobTitle] [nvarchar](50) NULL,
        [BusinessEntityType] [nvarchar](50) NULL
    )
    AS 
    -- Returns the first name, last name, job title and business entity type for the specified contact.
    -- Since a contact can serve multiple roles, more than one row may be returned.
    BEGIN
    	IF @PersonID IS NOT NULL 
    		BEGIN
    		IF EXISTS(SELECT * FROM [HumanResources].[Employee] e 
    					WHERE e.[BusinessEntityID] = @PersonID) 
    			INSERT INTO @retContactInformation
    				SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
    				FROM [HumanResources].[Employee] AS e
    					INNER JOIN [Person].[Person] p
    					ON p.[BusinessEntityID] = e.[BusinessEntityID]
    				WHERE e.[BusinessEntityID] = @PersonID;
    
    		IF EXISTS(SELECT * FROM [Purchasing].[Vendor] AS v
    					INNER JOIN [Person].[BusinessEntityContact] bec 
    					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
    					WHERE bec.[PersonID] = @PersonID)
    			INSERT INTO @retContactInformation
    				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Vendor Contact' 
    				FROM [Purchasing].[Vendor] AS v
    					INNER JOIN [Person].[BusinessEntityContact] bec 
    					ON bec.[BusinessEntityID] = v.[BusinessEntityID]
    					INNER JOIN [Person].ContactType ct
    					ON ct.[ContactTypeID] = bec.[ContactTypeID]
    					INNER JOIN [Person].[Person] p
    					ON p.[BusinessEntityID] = bec.[PersonID]
    				WHERE bec.[PersonID] = @PersonID;
    		
    		IF EXISTS(SELECT * FROM [Sales].[Store] AS s
    					INNER JOIN [Person].[BusinessEntityContact] bec 
    					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
    					WHERE bec.[PersonID] = @PersonID)
    			INSERT INTO @retContactInformation
    				SELECT @PersonID, p.FirstName, p.LastName, ct.[Name], 'Store Contact' 
    				FROM [Sales].[Store] AS s
    					INNER JOIN [Person].[BusinessEntityContact] bec 
    					ON bec.[BusinessEntityID] = s.[BusinessEntityID]
    					INNER JOIN [Person].ContactType ct
    					ON ct.[ContactTypeID] = bec.[ContactTypeID]
    					INNER JOIN [Person].[Person] p
    					ON p.[BusinessEntityID] = bec.[PersonID]
    				WHERE bec.[PersonID] = @PersonID;
    
    		IF EXISTS(SELECT * FROM [Person].[Person] AS p
    					INNER JOIN [Sales].[Customer] AS c
    					ON c.[PersonID] = p.[BusinessEntityID]
    					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL) 
    			INSERT INTO @retContactInformation
    				SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer' 
    				FROM [Person].[Person] AS p
    					INNER JOIN [Sales].[Customer] AS c
    					ON c.[PersonID] = p.[BusinessEntityID]
    					WHERE p.[BusinessEntityID] = @PersonID AND c.[StoreID] IS NULL; 
    		END
    
    	RETURN;
    END;
    
    GO
    

    Usage:

    SELECT * FROM [dbo].[ufnGetContactInformation](1)
    /*
    PersonID	FirstName	LastName	JobTitle	BusinessEntityType
    1	Ken	Sánchez	Chief Executive Officer	Employee
    */


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Sunday, June 30, 2013 6:25 PM
  • Is it practical to avoid UDF-s in T-SQL programming? 

    I don't believe UDFs gives you any capability you cannot do without them.

    Their main benefit is the same as any subroutine or function in any other language, they let you encapsulate certain logic for reuse.

    Their main drawback is that Microsoft never quite figured out what they want to *do* with functions or where they fit in the SQL model, their interference with query optimization is horrible, and I think largely unnecessary.  It all centers on what is "stochastic" versus "deterministic" and what those mean in a SQL environment.

    Joe's major complaint is that they are proprietary syntax and not ANSI approved.  Well, that's as may be. 

    They are handy and pretty, I use them in limited circumstances and with knowledge in advance of their drawbacks.  There are lots of things in SQL and relational that are less than perfect but overall are worth the bother, UDFs are just another on that pile.

    Josh


    • Edited by JRStern Sunday, June 30, 2013 6:52 PM typos
    Sunday, June 30, 2013 6:51 PM
  • UDFs are easy to use and handy, and also escapes from execution plans and always( may be sometimes) trouble for performance engineers.

    I have removed many scalar UDFs from project code. They were the query eaters , but difficult to trace.

    As long as we have an alternative approach better to avoid them(IMHO).


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 01, 2013 12:36 AM
  • Hi Kalman,

    most topics have been discussed. But please keep in mind to avoid multi-statement-level functions because they cannot use any statistics from the underlying relations as inline functions will do.

    Take the following example from AdvenureWorks2012

    USE AdventureWorks2012
    GO
    
    CREATE FUNCTION dbo.multi_statement(@orderdate datetime)
    RETURNS @return_set TABLE
    (
    	SalesOrderId	int,
    	OrderDate	datetime
    )
    AS
    BEGIN
    	INSERT INTO @return_set
    	SELECT	SalesOrderId,
    			OrderDate
    	FROM	sales.SalesOrderHeader
    	WHERE	OrderDate = @OrderDate
    
    	RETURN
    END
    GO
    
    CREATE FUNCTION dbo.inline_statement(@orderDate datetime)
    RETURNS TABLE
    AS
    	RETURN
    		SELECT	SalesOrderId,
    				OrderDate
    		FROM	sales.SalesOrderHeader
    		WHERE	OrderDate = @orderDate
    GO

    Now execute both functions with the same value for @OrderDate

    DECLARE	@OrderDate datetime = '20050701'
    SELECT * FROM dbo.multi_statement(@OrderDate);
    SELECT * FROM dbo.inline_statement(@OrderDate);

    If you have a look to the execution plan and the profile you see why both functions will use different execution plans!

    The query processor replaces an inline UDF reference with its definition; in other words, the query processor “expands” the UDF definition and generates an execution plan accessing the underlying objects and can use the statistics!

    So - from my point of view - multi-statement-function maybe harmful :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Monday, July 01, 2013 5:37 AM
  • Uwe,

    How about a multi-statement TVF like this:

    WITH cteSequence ( SeqNo) as
    
    (
    
          SELECT 1
    
          UNION ALL
    
          SELECT SeqNo + 1
    
          FROM cteSequence
    
          WHERE SeqNo < @max
    
    )
    INSERT @Sequence
    SELECT * FROM cteSequence
    OPTION ( MAXRECURSION 0);
    RETURN
    END
    GO
    
    SELECT * FROM ufnIntSequence (100);
    GO


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Monday, July 01, 2013 8:36 AM
  • Hallo Kalman,

    exactly the same due to multiple reasons:

    - no relation in the query who's statistics can be used
    - estimated rowcount = 1

    Usage of multi statement functions ALWAYS have 1 record as "estimated" records. You can check it with a really simple demo followed by this line:

    USE tempdb;
    GO
    
    CREATE TABLE dbo.tbl_foo
    (
    	MasterId	int,
    	DetailId	int
    );
    
    CREATE CLUSTERED INDEX ix_tbl_foo ON dbo.tbl_foo (MasterId, DetailId);
    GO
    
    INSERT INTO dbo.tbl_foo
    VALUES
    (NULL, 1),
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 5),
    (3, 6),
    (3, 7)
    
    -- Now the creation of two functions
    -- multi = multi statement function
    -- inline = inline function
    CREATE FUNCTION dbo.multi()
    RETURNS @Result TABLE
    (
    	EmployeeId	int,
    	Level		int
    )
    AS
    BEGIN
    	;
    	WITH cte (EmployeeId, Level)
    	AS
    	(
    		SELECT	DetailId	AS	EmployeeId,
    				1			AS	Level
    		FROM	dbo.tbl_foo
    		WHERE	MasterId IS NULL
    
    		UNION ALL
    
    		SELECT	f.DetailId	AS	EmployeeId,
    				c.Level + 1	AS	Level
    		FROM	dbo.tbl_Foo f INNER JOIN cte c
    				ON (f.MasterId = c.EmployeeId)
    	)
    	INSERT INTO @Result (EmployeeId, Level)
    	SELECT * FROM cte
    	
    	RETURN
    END
    GO
    
    CREATE FUNCTION dbo.inline()
    RETURNS TABLE
    AS
    RETURN
    (
    	WITH cte (EmployeeId, Level)
    	AS
    	(
    		SELECT	DetailId	AS	EmployeeId,
    				1		AS	Level
    		FROM	dbo.tbl_foo
    		WHERE	MasterId IS NULL
    
    		UNION ALL
    
    		SELECT	f.DetailId	AS	EmployeeId,
    				c.Level + 1	AS	Level
    		FROM	dbo.tbl_Foo f INNER JOIN cte c
    				ON (f.MasterId = c.EmployeeId)
    	)
    	SELECT * FROM cte
    )
    GO

    OK - now I execute both queries:

    SET STATISTICS PROFILE ON;
    GO
    
    SELECT * FROM dbo.multi();
    SELECT * FROM dbo.inline();

    The statistics for multi are as expected (estimated = 1). The execution plan looks pretty smart (and horrible) for the inline function :)

    The look to the io statistics decribes the extrem difference, too :)

    Table '#BD90D330'. Scan count 1, logical reads 1,...

    Table 'tbl_foo'. Scan count 8, logical reads 16,...
    Table 'Worktable'. Scan count 2, logical reads 43,...


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, July 01, 2013 9:54 AM
  • >> you program better the following AdventureWorks2012 table-valued function? <,

    The DDL stinks and is full of ISO-11179 violations. Heck, nobody even got the word about Pascal and camelCase! For example, the USPS allows 35 characters for an address, not fifty characters from an old ACCESS and BASIC default value. Nobody would use an integer for a tag number as that silly generic “duns” (what role does it have in the data model? Can it be lawful person? Etc?). AdventureWorks was meant to be a demo for every possible feature in T-SQL they could load into it. 

    Unfortunately, they had no idea how to do a valid data model, so it sucks. Instead of “Personnel”, the name of the abstract set, they used the singular noun “Employee”, “Person” is both a database and a table, etc. 

    There is no such thing as a “type_id”; the attribute property can be a “<something>_id” or a “<something>_type” not a monster hybrid. What is your “blood_type_id_value_code”? See how silly extra attribute properties are? And since there are not a lot of types in this nominal scale, it ought be a CHECK() clause, not a table. 

    The DUNS is how a business is identified; apparently this is also how they identify their personnel. Otherwise, we have Kabbalah numbers and magic. 

    The most portable way would be to create “Automobiles, Squids and Lady Gaga” VIEW from the fragmented universes (every schema is supposed to be its own little universe of discourse) in this non-data model. A very rough skeleton would be simple: UNION ALL the contact attributes from the tables that have been corrected to model entities, instead of a forced OO concept of data with Person as a super-type. 

    CREATE VIEW Global_Magical_Contacts -- rough skeleton
    (duns, first_name, last_name, contact_type, contact_source)
    AS 
    SELECT duns, first_name, last_name, job_title, 'Employee'
     FROM Personnel AS P
    UNION ALL
    SELECT duns, first_name, last_name, vendor_name, 'Vendor' 
     FROM Vendors AS V
    UNION ALL
    SELECT duns, first_name, last_name, store_name, 'Store' 
     FROM Stores AS S
    UNION ALL 
    SELECT duns, first_name, last_name, NULL, 'Customer' 
      FROM Customers AS C; 

    Every TVF I have seen should have been VIEW. I can nest VIEWs, use WITH CHECK OPTION, port my code and give the optimizer a fighting chance. Now use it in a vanilla SELECT: 

    SELECT * 
     FROM  Global_Magical_Contacts
    WHERE duns = @in_duns;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, July 01, 2013 11:17 PM
  • The query processor replaces an inline UDF reference with its definition; in other words, the query processor “expands” the UDF definition and generates an execution plan accessing the underlying objects and can use the statistics!

    This how the original "FN-" prefixed line of code was implemented in FORTRAN II  and then in 1960's BASIC! It was literally a text macro substitution precompiler command. Did you ever wonder where that "FN" came from?  

    Totally non-RDBMS 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 1:35 AM
  •  Code Simplicity : UDFs makes easy for complex business logic or algorithm.

     Code Reusability : Sharing of UDFs for multiple purposes.

    No, VIEWS with a table name that describes the set of entiities would do this better. And the VIEWs wopdul port, be maintainable , etc. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 1:41 AM
  • CLR Scalar-Valued UDFs

    CLR Table-Valued UDFs

    Which one of the 42+ CLR languages with totally different  rules for MOD(). rounding, truncation  and a hundred otherbasic functions

    Do not mix CLR languages in SQL!  A man with two wrist watches does not know what time it really is., And he can't maintain the code ! 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 1:50 AM
  • Joe's major complaint is that they are proprietary syntax and not ANSI approved.  

    No, that they do not optimize. If they optimized, I could live with non_ANSI as ana excuse. But is it #2  :)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 1:53 AM
  • A doctor should know a diseases :)

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 1:57 AM
  • This how the original "FN-" prefixed line of code was implemented in FORTRAN II  and then in 1960's BASIC! It was literally a text macro substitution precompiler command. Did you ever wonder where that "FN" came from?

    I don't recall anything like that in Fortran II or Dartmouth Basic, and Google doesn't turn anything up in a quick search.  Platform?  References?

    And I have nothing against macro substitution anyway, I wish to heaven the SQL Server had it to implement constants, though there are other ways to implement constants as well.  I could think of some other large benefits to using macros in SQL as well.  Totally RDBMS, if it were only a little better than it is.

    Josh

    Tuesday, July 02, 2013 2:41 AM
  • Joe,

    The forum editor has a (old) bug when you press Quote: you get trapped in the quote block.

    Remedy:

    1. Click on html edit

    2. Add <p></p> at end

    3. Exit html edit

    4. Position under the quote block and type


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Tuesday, July 02, 2013 5:38 AM
  • No, that they do not optimize. If they optimized, I could live with non_ANSI as ana excuse. But is it #2  :)

    Hallo Joe,

    if it is an inline function it will optimize as my example had demonstrated. I'm not a fan of UDF and yes - most (if not ALL) - set based operations can be handled with views in the same way but inline functions may give a benefit because it can use variables as parameters (like predicates) and can optimize the query by using the underling objects.

    The responsibility of coding is by the developer! The tools will be given by Microsoft and the developer has to learn how to use them :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Tuesday, July 02, 2013 6:55 AM