THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » T-SQL » SQL Server   (RSS)
Showing page 2 of 5 (48 total posts)
  • Increment a variable in the SET clause of an UPDATE statement [T-SQL]

    There’s a very nifty feature of T-SQL that, in my experience, not everyone knows about but is handy for generating surrogate key values as part of an ETL routine. I myself had to ask on Twitter what the syntax was because I couldn’t remember it (thanks to Matija Lah for reminding me) so I think its worth blogging in case I forget it again. To ...
    Posted to Jamie Thomson (Weblog) by jamiet on May 28, 2013
  • Obtaining rowcounts when using Composable DML [T-SQL]

    In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that ...
    Posted to Jamie Thomson (Weblog) by jamiet on February 7, 2013
  • The perils of double-dash comments [T-SQL]

    I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re ...
    Posted to Jamie Thomson (Weblog) by jamiet on December 6, 2012
  • Using Composable DML to maintain entity history [T-SQL]

    I recently inherited a data warehousing SQL Server solution from my good buddy James Rowland-Jones (you may know him from his more auspicious role on the SQLPass board) and it contained a very cool technique of using composable DML (a feature that arrived in SQL Server 2008) to maintain history of updates to a particular table. I knew about ...
    Posted to Jamie Thomson (Weblog) by jamiet on July 13, 2012
  • Bitmask data insertions in SSDT Post-Deployment scripts

    On my current project we are using SQL Server Data Tools (SSDT) to manage our database schema and one of the tasks we need to do often is insert data into that schema once deployed; the typical method employed to do this is to leverage Post-Deployment scripts and that is exactly what we are doing. Our requirement is a little different though, our ...
    Posted to Jamie Thomson (Weblog) by jamiet on June 11, 2012
  • Use VALUES clause to get the maximum value from some columns [SQL Server, T-SQL]

    My ex-colleague Paul Mcmillan pointed me at a thread on Stack Overflow that demonstrated a neat T-SQL trick to get the maximum value from a collection of columns in a row. Paul had never seen it before and neither had I so I figure one or two of you out there might learn something from it too. In short you can use the VALUES clause to effectively ...
    Posted to Jamie Thomson (Weblog) by jamiet on January 20, 2012
  • Delete all data from a database

    Sometimes I use this blog to share scripts that I know I'm going to need at some point in the future, this is one of those times. The script below will remove all data from a database while respecting/preserving all foreign key constraints - very useful in a development or test environment if you want to get back to a ''vanilla'' snapshot of your ...
    Posted to Jamie Thomson (Weblog) by jamiet on September 19, 2011
  • [T-SQL Tuesday] Some code is born crap, some code achieves crapness and some code has crapness thrust upon it!

    I recently returned to a client at which I last worked back in 2006 and, as you might imagine, that gives me ample material for a blog post dedicated to Crap Code; conveniently the subject of the latest T-SQL Tuesday. I first worked for this client back in 2004 and that is significant because back then we were writing code on SQL Server 2000. Ah, ...
    Posted to Jamie Thomson (Weblog) by jamiet on August 10, 2011
  • On the typeof() campaign trail

    Given a table: CREATE TABLE [person] (  [id] INT IDENTITY(1,1) ,  [name] VARCHAR(100) ,  [age] INT); How nice would it be to be able to do something like this inside your stored procedures?: DECLARE @person TABLE (  [name] typeof([person].[name]) ,  [age] typeof([person].[age])); Or, perhaps, ...
    Posted to Jamie Thomson (Weblog) by jamiet on August 10, 2011

    I learnt something about T-SQL today that I never knew and I figure I’m not the only one so it might be worth sharing what I learnt; namely, I discovered the SQL_VARIANT_PROPERTY function. It all started when going over a piece of code that I recently inherited, here is an abbreviated version of that code in order to demonstrate the ...
    Posted to Jamie Thomson (Weblog) by jamiet on August 3, 2011
Privacy Statement