SQL

Posted on: December 18, 2012

CRM comes with a user-defined type called “UDT_FUZZYDATE”. If you look at that datatype, you’ll notice that it’s just a CHAR(8) field. Converting from a standard datetime to a UDT_FUZZYDATE can be done pretty easily using native SQL. Anybody reading this blog could figure this out on their own, but here it is just to save you the typing. if exists(select * from information_schema.routines where routine_name = 'usr_ufn_datetime_to_fuzzydate') begin drop function usr_ufn_datetime_to_fuzzydate; end; GO create function usr_ufn_datetime_to_fuzzydate ( @dt as datetime ) returns udt_fuzzydate as begi... Read More

Posted on: September 5, 2012

I recently overheard the question at Blackbaud the other day, “What is the “Numbers” table used for?. We’ll let me try to relay an answer to you good people. The numbers table can be a pretty powerful thing to use. Often times in SQL, you find yourself needing to generate numbers or finding gaps in numbered sequences. You can use it to order things sequentially when querying for data. For example, you can use them to help generate date/time in your queries: DECLARE @STARTDATE as DATETIME = '1/1/2012' DECLARE @ENDDATE as DATETIME = '12/31/2012' select DATEADD(d,NUM,@STARTDATE) from NUMBER... Read More

Posted on: May 25, 2012

Smart fields are a great way to speed up the end user experience by performing complex calculations over night or on a scheduled basis so that the results are cached and available to users when needed. For information on getting started with smart fields, I would recommend reviewing the Smart Field SDK documentation. For some general guidelines on improving smart field performance, I would also recommend my previous post. In version 2.91, we added the BypassProcessorUpdate flag to smart field specs. In doing some performance evaluations of our existing smart fields, we found that the pr... Read More

Posted on: May 24, 2012

I have seen and done a lot of work lately with the CRM and BBIS that deals directly with the database. I have also had the joys of refactoring other developer’s code, which we all know is a blast. Some of the major problems I have seen and encountered are inline SQL, which is a huge no-no, and the general lack of knowledge around the SqlClient classes. Some basics: 1) NEVER use inline SQL, always parameterize your queries Make sure that you don’t do something like what is below, because it’s…wrong. Look up inline SQL on google and read the first 10 results. Dim cmdText As String = "sele... Read More