Welcome to EMC Consulting Blogs

Browse by Tags

• Common Table Expression
• Dataflow Task
• DRY
• Execute SQL Task
• Expressions
• Grouping Sets
• katmai
• List of dates
• Live Mesh
• Microsoft
• Microsoft Connect
• Microsoft Source Analysis for C#
• Nugget
• Oracle
• pl-sql
• raw files
• Redgate
• slowly changing dimensions
• sp_executesql
• SQL
• SQL Prompt
• SQL Server
• SQL Server 2005
• sqL server 2008
• SQL Server Integration Services
• SQL Server Management Studio
• sqlmesh
• SSIS
• SSMS
• StyleCop

• String Aggregation in T-SQL & PL-SQL : SQL Nugget

  In my current day job I’m doing a lot of work against an Oracle back-end and I’ve just come across a situation where I need to do some string aggregation. Effectively I needed to turn this: Parent Child Charles William Charles Harry Anne Peter Anne Zara Read More... Posted 16 July 2009 13:32 by jamie.thomson | 6 Comments Filed under: SQL Server, Oracle, T-SQL, Nugget, pl-sql

• DRY SQL

  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 Read More... Posted 02 July 2009 22:05 by jamie.thomson | 4 Comments Filed under: SQL Server, SQL, T-SQL, DRY

• Using return types as parameters in T-SQL

  In SQL 2005 we could write something like this: declare @i int select @i = dbo.udf_MyScalarFunction() exec usp_MyStoredProc @i In SQL 2008 we can write the same functionality more succinctly like this: declare @i int = dbo.udf_MyScalarFunction() exec Read More... Posted 23 March 2009 20:32 by jamie.thomson | 3 Comments Filed under: SQL Server, T-SQL

• SQL Mesh March 09 update

  I’m glad to report that, 2 months after I launched it, SQL Server Mesh code repository is still going strong. We’re now approaching 100 members (there were 53 at last count ) with more members and more content being added every day. If you are submitting Read More... Posted 20 March 2009 18:42 by jamie.thomson | 1 Comments Filed under: SQL Server, T-SQL, Live Mesh, sqlmesh

• Formatting T-SQL

  Simon Sabin just published a blog post Where should the ON clause go? where he explained his penchant for writing ON predicates for JOIN clauses on the same line so he can easily spot them like so: select track, level, title, Name, ss.length   from   Read More... Posted 11 March 2009 22:45 by jamie.thomson | 5 Comments Filed under: SQL Server, T-SQL

• SQL Mesh update

  Three weeks ago I posted a blog entry entitled SQL Server code repository on Live Mesh where I announced the creation (if that’s the right word) of a folder, hosted on Live Mesh, that anyone in the SQL Server community could get access and contribute Read More... Posted 12 February 2009 23:22 by jamie.thomson | 3 Comments Filed under: SQL Server, T-SQL, Live Mesh, sqlmesh

• SQL Server code repository on Live Mesh

  Those who know me may be aware that as well as my obvious interest in SQL Server I also have a very keen interest in Windows Live and specifically in Live Mesh , an incubation technology from Microsoft that synchronises data between different devices Read More... Posted 20 January 2009 22:05 by jamie.thomson | 8 Comments Filed under: SQL Server, T-SQL, Live Mesh, sqlmesh

• Making the case for expressions instead of parameterised SQL (and vice versa) : SSIS Nugget

  [Although I colloquially refer to myself on this blog as “SSIS Junkie” it won’t have escaped the notice of regular readers that I haven’t been talking about SSIS much lately, in fact nothing of real substance in regard to SSIS has appeared here since Read More... Posted 08 December 2008 13:35 by jamie.thomson | 1 Comments Filed under: SQL Server Integration Services, SQL Server, SSIS, T-SQL, Expressions, Execute SQL Task, Nugget, sp_executesql

• T-SQL: Do you use aliases?

  A pet peeve of mine is the lack of alias usage when using T-SQL. I’m not sure why people don’t bother using aliases –I’ve never asked- but I suspect its laziness. I can understand that but I want to speak up for the bastion of embattled SQL developers Read More... Posted 04 July 2008 20:58 by jamie.thomson | 29 Comments Filed under: SQL Server, T-SQL

• T-SQL: Multiple ALTER COLUMN clauses in an ALTER TABLE statement

  Quick one. Do you think it would be useful to be able to do this: ALTER TABLE t ALTER COLUMN c1 int null ALTER COLUMN c2 int null ALTER COLUMN c3 int null ALTER COLUMN c4 int null; instead of this: ALTER TABLE t ALTER COLUMN c1 int null; ALTER TABLE t Read More... Posted 17 June 2008 16:11 by jamie.thomson | 2 Comments Filed under: SQL Server, T-SQL