Development

Getting started with dynamic SQL

Sometimes you need dynamic SQL. Sometimes you don't know at run time where you're querying or what fields you need to bring back, or what your criteria will be, so there will always be a place for the use of dynamic SQL. You have to be careful...

Published 7 Dec 2009 10:16 AM by TimChapman

Creating Indexes with IGNORE_DUP_KEY

Unique indexes and unique constraints limit the values that are allowed in a table column to unique values. This uniqueness is almost always a result of some type of business rule. Programming these types of business rules are usually not overcomplicated...

Published 15 Nov 2009 8:04 PM by TimChapman

Filed under: index, union all, IGNORE_DUP_KEY, unique index, sql server

SQL Server 2008 Syntactic Sugar

As a developer, I always enjoy new TSQL language features that can save me some time and potentially effort in accomplishing the same task that I otherwise could in a different fashion. These features aren't really a big deal to me to have but are...

Published 28 Sep 2009 8:21 PM by TimChapman

Filed under: merge sql 2008 operator variable compound

Pivot Rolling Periods : Dynamic, But Not!

In the Article, Pivot Rolling Periods Without Using Dynamic T-SQL ( experts-exchange.com/viewArticle.jsp?aid=654 ), I explored the pivot of moving date based reports without the use of dynamic T-SQL, but still allow for re-use month after month without...

Published 17 Sep 2009 11:53 PM by Kevin Cross

Filed under: SQL 2008, SQL, TSQL, CTE, SQL 2005, Beginner SQL

Unstringing a comma delimited column

Ever had a column which had a pile of information delimited by commas that you want to unpack ? Well, it is not that hard so long as the information is pretty short, and tightly controlled... -- step 1 create a sample table used as a datasource... if...

Published 28 May 2009 5:07 AM by mark_wills

Filed under: Intermediate SQL, Mark Wills

Two things I love about Management Studio 2008

I've been using the SQL Server product for quite some time now, and I almost always use the IDE programs that ship with the database engine. Enterprise Manager and Query Analyzer certainly had their quicks, but were overall nice tools. Management...

Published 3 Apr 2009 9:34 PM by TimChapman

Filed under: Intermediate SQL, Beginner SQL, Advanced SQL

Using TRY...CATCH IN SQL Server 2005/2008

New to SQL Server 2005 (and present in SQL Server 2008) is the ability to use TRY..CATCH constructs inside of your TSQL code to catch error statements. If you've done any type of programming in other programming languages, you know that this is a...

Published 3 Apr 2009 3:16 PM by TimChapman

Filed under: Intermediate SQL, Beginner SQL

Concatenating Results through SELECT statements

In this SQL tip I am going to show you a neat little trick which involves using a simple SELECT statement to concatenate row values into a single variable value. First, I'll create a temp table named #Results for which I'll store FirstName and...

Published 20 Mar 2009 4:53 PM by TimChapman

Filed under: SQL, TSQL, SQL 2005, Intermediate SQL, Beginner SQL, string sql variable concatenate temp table results

Stripping the time from a DATETIME value

Many times there is a need to strip the time off of a DATETIME value so you are left with the date only. I've seen a few different ways to handle this. To clarify, when I say a DATETIME value with no time, I really mean midnight (00:00:00.000) on...

Published 19 Mar 2009 9:39 PM by Aaron Akin

Filed under: TSQL, Beginner SQL

Finding the major version number of your SQL Server instance

Occasionally, I need to determine which version of SQL Server I am connected to within a script so that I can write the proper code for that version. I’m never really concerned with the build number or which service pack I have installed. Instead...

Published 18 Mar 2009 9:32 PM by Aaron Akin

Filed under: SQL, Version, Beginner SQL

Nested Common Table Expressions

If you've looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can't define a CTE in the definition of another CTE. Although this is technically true, you...

Published 18 Mar 2009 10:03 PM by Aaron Akin

Filed under: SQL 2008, SQL, TSQL, CTE, SQL 2005, Intermediate SQL

Syndication

Recent Posts

Tags

Archives