Welcome to EMC Consulting Blogs

I recently inherited some SQL that someone else had written and had the job of “tidying it up” before it gets pushed out to production. Here’s a slightly simplified (yes, simplified) version of that SQL: 1: --options 2: select asset_class 3: , case when volume > 0 then 'profit' 4: ...

Ok I admit - I use it. Pretty much everyone I know uses it and yet I feel dirty when I do.  Why? Well it's not portable SQL and most certainly not ANSI standard.  I need to get the datetime I hear you say.  How do I do this without getdate()?  There is a perfectly good substitute in the form of CURRENT_TIMESTAMP that is ...

A colleague of mine asked for a simple example to get him up and running with the basics of bitmasking.  Since I had gone to the effort to create the example I thought I might as well blog it :o). This example uses the logical AND operator &. First of all you need to define your table.. create table BitmaskDemo (ID int Primary ...

  This issue caught me out today whilst putting together some build scripts for reference data. So I thought I’d share it. USE [tempdb] GO CREATE TABLE [dbo].[Table_1](       [Col1] [int] IDENTITY(1,1) NOT NULL,       [Col2] [char](1) NOT NULL ) ON [PRIMARY] GO delete from ...

Following a few requests, here are some sites you may find useful for supporting your SQL learning: General http://www.stickyminds.com/  http://www.thefreecountry.com/documentation/onlinesql.shtml (free online tutorials) T-SQL http://sqlcourse.com/ (free online tutorial) http://www.w3schools.com/sql/default.asp (free online ...

Today I was looking at a table with an apparantly empty column and discovered that the field is actually populated with blank spaces - Fortunately this field is of no interest to me and I noticed it purely by chance when I pasted an extract of the dat into excel and happened to notice the cell was unusually wide when I formatted the sheet. Anyway, ...

If you ever need to browse through a database, and dip in and out of tables to see what the data looks like, but you have very little data to go on, then you will be aware that simply running a select * against each table can be a time consuming and inefficient process. I am in this situation currently with a database that has no enforced ...

A colleague forwarded me this link today, which I thought would be beneficial to anyone who has to write a lot of SQL statements. The tool will automatically format the statements to 'beautify' them, so no more excuses for 'ugly' code! Check it out here James

Today I ran into a blocker when trying to do this. Having spent a reasonable amount of time taking all of my sql statements out of the OPENQUERY argument and putting them into a string variable I then found it didn't work. The reason (from BOL) it turns out is that, and I quote; "OPENQUERY does not accept variables for its ...

After experimentation with various datepart and datename methods following my most recent blog post, I was able to identify many if not all the necessary components for constructing a calendar table.To do this I have combined these functions with the SQL to generate a list of dates as posted by Jamie some time ago. The resultant SQL is given ...