Blog Archives

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),

Is your brain faster than your PC?

Computer vs the brain

computer vs the brain

I wrote a post quite some time ago illustrating methods that humans can use to solve multiplication faster than a PC.

The methods are very simple and involve practice over a weekly (or monthly) period of time, with time varying depending on the last time you had any math courses.

If you have practiced these methods, you are witness of the proof of human innovation firsthand, yet technically speaking, your PC computes the results much faster, and most benchmarked speed differences are mostly relative to the speed that the computation is entered into the PC.

As the numbers get larger, your ability to pinpoint an exact value becomes increasingly difficult, similar to a PC. However, I have yet to see a PC compare to the estimating abilities of humans involving complex computations.

Enrico Fermi is a case in point. A physicist and mathematician, Fermi made and proposed solutions involving estimates that even today’s PCs would have trouble deliberating. One such example is the Fermi paradox; a mathematical counter-analysis made by Fermi in response to the Drake Equation – an estimate involving extraterrestrial life in our galaxy.

Can your computer estimate?

The drake equation is both mathematical and relatively computable (by machines with degrees of accuracy), as you can clearly see if you take a look at it.

There is a catch. Ultimately the results of the equation are of such a large scale, that the mathematical rules of indeterminate form apply, and while the computer is chugging along at finding an accurate result, you can come to the same approximate answer by simply saying the result is either “very big in the billions” or “0″.

While computers may be very good at getting exact results, likely faster than you can, a more interesting and probably also more applicable question is: can you computer determine the best degree of estimation?

The next time someone asks you who can compute faster – the brain or the PC (or Mac), you can safely quote me in response with something such as:

The PC is faster at many math problems, but when it comes to a fermi problem, the PC slows to a grinding halt.

After all, computers can currently only solve what we have designed them to. How could they possibly compute estimations at the same level as our brain if we do not understand the limitations of our own design?

Mentally Calculate… (ronniediaz),
Enrico Fermi (wiki),
Fermi Problem (wiki), <a href="
Fermi Paradox (wiki),
Drake Equation (wiki),
Indeterminate Form (wiki),
Computers vs. Humans (blog image),

How to Create secure relational databases in SQL

The first step to a secure DB, is a well structured DB and the application layers that access it.

Take the following example of a table containing factories and the robots they produce.


This serves a good purpose by itself, and allows for easy manipulation and viewing of the stored data, but this table could very quickly grow out of hand. What if you needed to store the parts that are required to make each robot? Or if some factories share the types of robots that they can create?

First, lets separate the data into two separate tables of factories and robots:




1 Ron
2 John
3 Bob
4 Bill

In order to group these two together. we now require a mapping table.

1 1
1 2
2 3
2 4

To see this data in a single set, like your original table, you would run a select statement with a join. Some programmers put these statements with joins directly in their code as plain text while others keep them in the database.

Relatively newer technologies, such as Microsoft Linq or Ruby have their own syntax for performing these joins and queries which is ultimately translated to plain sql behind the scenes.

Unfortunately, this often leads to a common misconception that because you are using Linq, Ruby or another data access layer that your work ends there.

Consider the following scenario:
App A->retrieves a join of factories and robots
App B->also retrieves the same join

To make this simpler, you create a new project (this would be your data access layer) and add a new method called “GetRobotsInFactories” which uses Linq, plain sql, ROR or some other technology to run the select with the join.

So now you have:
App A->DAL->GetRobotsInFactories
App B->DAL->GetRobotsInFactories

For future changes, simply modify GetRobotsInFactories, upload the DLL and that’s it! Or is it?

What if a new business change now requires you to only get robots in factories after specific manufacture dates?

You could just add an optional parameter to GetRobotsInFactories, but now you have to code in conditional logic to your function checking if the parameter is null, and forming a different query based on this. If there are 10 more parameters like this, your function could now very quickly become much more troublesome to support, especially when certain combinations of parameters may be required.

This creates a weaker structure, and therefore opens the door to security vulnerabilities. The approach on separating the DAL into a new project and wrapping everything in here is great, but you must be very careful on how your DAL accesses the DB.

What’s the alternative?

Views are joins

For starters, creating views in the database for your joins will save you from having to constantly type out select statements with joins and improve your productivity in database debugging greatly.

An example of such join for our above is:


select * from factories inner join robots on 
factories.factoryid = robots.robotid

This now also allows you to perform specific select statements against the view, which will look very similar to your first table you created before segregating the data.

Cool, now what?

You might consider plugging in a simple “select * from VW_JOIN_FACTORIES_ROBOTS” into that GetRobotsInFactories function, but not done yet.

Stored Procedures offer security

Many developers understand the minimal amount of SQL required to power their app, or have heard of or created and modified views as well as stored procedures, but often are not aware of the security benefits.

Stored procedures encapsulate the data you need to retrieve. This allows you to control not only the tables being modified, but the way they are modified, dependencies, constraints, parameters and even database permissions on who can execute or modify which stored procedures and ultimately obfuscate your underlying data from would be attackers or meddlers.

Consider the following stored procedure:


USE [RobotCo]
CREATE PROCEDURE [dbo].[RobotCo] (@MfgDate datetime)
select * from VW_JOIN_FACTORIES_ROBOTS where datetime between @MdfDate and getdate()

You can now change your “GetRobotsInFactories” function to call “SP_GET_ROBOTS_IN_FACTORIES” and simply pass in date as a parameter to the SP. Future changes will only require you to add parameters to the SP and then simply update the DAL/dbml or definition if you’re using linq/ror/etc.

Locking it down

All structures are already in place! You’re next steps are to make sure the applications have their own SQL user accounts with only access to execute the specified stored procedures.

Without direct access to the view or the tables, exploitation of the app will prevent attackers from retrieving or manipulating the data directly.

Hopefully this offered some insight on database level security, as well as understanding some of the benefits of view and SP utilization for better overall software architecture.

Microsoft Linq,
Ruby on Rails,

Lambda Functions in .Net

Lambda expressions in .Net are denoted by the ‘=>’ symbol.

These powerful operators can be used to create sleek, optimized loops that perform much faster.

The usage below condenses the equivalent of at least 20 lines of two loops.

(Sorry kiddos, no time for VB conversion…C# only this time. ;))

Edit (20101229): Lambda rocks! Simply amazing! Greatest ever!

Ex. 1:

            PaymentMethodCollection visiblePaymentMethods = new PaymentMethodCollection();

            DataSet myPaymentMethods = GlobalDataLoads.PaymentMethods();
            List<DataRow> pmids = myPaymentMethods.Tables[0].Select("Visible = 1").ToList();
            List<int> ids = pmids.Select(i => i["PaymentMethodId"]).Cast<int>().ToList(); 
            List<PaymentMethod> pm = availablePaymentMethods.FindAll(i => ids.Contains(i.PaymentMethodId));


Ex. 2:

USER user = tol_dc.USERs.Single(u => u.USER_ID == (int)userid);

Ex. 3:

//DataContext tol_dc;
            List<int> courseids = registrationList.Select(i=>i.MODULE_ID).ToList();
            List<MODULE> modulenames = tol_dc.MODULEs.Where(i=>courseids.Contains(i.MODULE_ID)).Cast<MODULE>().ToList();

Ex. 4: (Get the value of data item inside the child repeater of a parent repeater if the UID is known)

//RepeaterCommandEventArgs e
        if ((stringvalueineed==null) || (stringvalueineed=="")) {
        int uid= (int)((System.Data.DataTable)rptCourses.DataSource).Rows[e.Item.ItemIndex]["UID"];
        stringvalueineed= ((System.Data.DataRow[])(((Repeater)rptOne.Items[e.Item.ItemIndex].FindControl("rptTwo")).DataSource)).Single(i => (int)i["UID"] == uid)["COLUMNNAME"].ToString();

Ex. 5a: (filter a list of available products by removing all excluded products from the list, such as those out of stock)

        private bool Load_AvailableProducts()
            string region = Session["region"];

            LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();

            List<LinqData.GET_PRODUCT_LISTResult> allproducts = (from p in dc.GET_PRODUCT_LIST(region)
                                       select p).ToList();
            List<int> excproducts = (from ListItem m in lbExcludedProducts.Items
                                       select Convert.ToInt32(m.Value)).ToList();

            List <LinqData.GET_PRODUCT_LISTResult> availproducts = allproducts.Where(p => !excproducts .Contains(p.ProductID)).ToList();

            lbAvailableProducts.DataSource = availproducts;

            lbAvailableProducts.DataTextField = "ProductName";
            lbAvailableProducts.DataValueField = "ProductID";
            return true;

Ex. 5b: (same as this version code is smaller and more load is placed on SQL server rather application (IIS). analyze with SQL profiler and you’ll see the difference)

        private bool Load_AvailableProducts()
            string region = Session["Region"];

            LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();

            List<LinqData.GET_PRODUCT_LISTResult> availproducts = (from i in dc.GET_PRODUCT_LIST(region)
                                       select i).Where(m => !(from ListItem l in lbExcludedProducts.Items
                                       select Convert.ToInt32(l.Value)).ToList().Contains(m.ProductID)).ToList();

            lbAvailableProducts.DataSource = availproducts;

            lbAvailableProducts.DataTextField = "ProductName";
            lbAvailableProducts.DataValueField = "ProductID";
            return true;

The lambda one-liner indicated in the snippet below essentially nests at least two “for/foreach” loops. See my related article here for full source.

Ex. 6:

protected void LoadFactories()

        if (ddlSegment.SelectedIndex > 0)
            using (AndroneticsDataContext db = new AndroneticsDataContext(
                List<UP_GET_FactoriesResult> allFactories = db.UP_GET_Factories(Convert.ToInt32(ddlRegion.SelectedValue), null).ToList();

                ////this would be approximately how you would approach traditionally
                //foreach (UP_GET_FactoriesResult r in allFactories)
                //    if (GetUnregisteredRobots(r.FACTORY_ID).Count != 0)
                //    {
                //        //etc, would need another loop here
                //    }

                //now for the lambda way
                var Factories = allFactories.Where(
                    r => GetUnregisteredRobots(r.FACTORY_ID).Where(
                        ra => !lstDecommissionRobots.Items.Contains(new ListItem(ra.ROBOT_NAME,ra.ROBOT_ID.ToString()))).Count() != 0);

                ddlFactories.DataSource = Factories;
                ddlFactories.DataTextField = "FACOTRY_TITLE";
                ddlFactories.DataValueField = "FACTORY_ID";

                ListItem li = new ListItem("", "");
                ddlFactories.Items.Insert(0, li);

    private List<UP_LOAD_COURSE_RobotsResult> GetUnregisteredRobots(int courseid)
        using (AndroneticsDataContext db = new AndroneticsDataContext(
            List<UP_LOAD_COURSE_RobotsResult> allRobots = db.UP_LOAD_COURSE_Robots(courseid).ToList();

            int userID = int.Parse(Request.QueryString["ID"]);
            List<string> excregisteredRobots = (from m in db.UP_GET_REGISTERED_Robots(userID) select m.MODULE_NAME).ToList();

            return allRobots.Where(m => !excregisteredRobots.Contains(m.MODULE_NAME)).ToList();

Ex. 7 (foreach)

//make sure list first
List<mydatatype> data = ReturnSomeData().ToList();

//technically long way but helps understand what is happening
Func<mydatatype, string> f = (a) => a.somepropertyname = "somevalue";
                    data.ForEach(i => f(i));

//or simply

data.ForEach(c => c.somepropertyname = "somevalue";

Ex. 8 Lambda Cast to Custom Object (list of objects from one type to another)

//DAL.StoreProcedures is simply a static class with functions that instantiate and dispose of datacontexts and return object collections from data calls

List<geocode> geocodes = DAL.StoredProcedures.LoadGeoCodes_Zip(market, datestart).Select(g=> new geocode {lat=g.Latitude.ToString(),lng=g.Longitude.ToString()}).Cast<geocode>().ToList();

MSDN, “Lambda Expressions (C# Programming Guide)”,
MSDN C# Developer Center, “101 Linq Samples”,
Fraction of The Blogosphere,


Get every new post delivered to your Inbox.