Dossies @ Work: Dynamics 365 CDS

I already used a number of times queries in my custom applications/reports that use the CRM tables instead of the Filtered Views. I know this is not the supported way to go, but sometimes it is just more performant to use the tables directly. Although using the CRM tables instead of the views can give already a performance boost (escpecially because you bypass the security), sometimes the performance could still be better. These are some simple tuning tips I collected during my 'SQL Query Journey' with thanks to the SQL Server community: Don't return unnecessary columns. Returning too many columns ( select * from ) can have a negative impact on performance. So only return the columns you really need. Use the LIKE operator with caution. Like with the value enclosed in wildcards ("%...%") almost always causes a table scan. Also negative operations ( or NOT LIKE) are not very efficient. If you are only checking for existence, use the IF EXISTS or the IF NO...

It happens frequently that certain clients want to see the weeknumber of certain dates in CRM reports. To accomplish this, you can use the following function in your query to get the week number for a given date: select datepart(ww,scheduledstart) from filteredappointment The above query will give you the weeknumber of the scheduled start date from the filtered appointments view. Kevin Dosseray

Want to know which SQL Server version you exactly have? Execute the following query and the SQL Management Studio will tell you: select @@version Kevin Dosseray