Blog Archives

Query Sql Version Microsoft MSSQL

    set nocount on
    select @@version
    select 'Edition: ' + convert(char(30), serverproperty('Edition'))
    select 'Product Version: ' + convert(char(20), serverproperty('ProductVersion'))
    select 'Product Level: ' + convert(char(20),serverproperty('ProductLevel'))
    set nocount off


MSSQL User Defined Functions vs Stored Procedures

I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”

While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.

Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.

Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.

SP_who filtering UDF vs SP,
Data Manipulation Language,
(Some) Differences (,
(Some) Differences (stackoverflow),