Popular Articles

Using REPLACE in an UPDATE statement

This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.

31 March 2010 - Bill Graziano - 12 Comments

How to Use GROUP BY with Distinct Aggregates and Derived tables

In How to Use GROUP BY, we worked on a simple report request and covered the basics of GROUP BY and the issue of duplicate rows caused by JOINs. Today we'll finish up that report while examining SUM(Distinct), and see just how crucial derived tables are when summarizing data from multiple tables.

31 July 2007 - Jeff Smith - 5 Comments

Custom Auto-Generated Sequences with SQL Server

This article by Jeff Smith covers different ways to create sequence numbers. It starts with the basic identity and GUIDs and quickly moves to much more complicated sequence numbers including those with auto-incrementing mixed letters and numbers such as "A0001", etc. Jeff shows one of the easiest ways I've ever seen to efficiently handle very odd sequence numbers.

24 April 2007 - Jeff Smith - 28 Comments

Working with Time Spans and Durations in SQL Server

What is the best way to return the "duration" of an event in SQL, given the start and end datetime values? How can we add up these durations to return grand totals? What data types should be used to return this data to our clients? How do we handle overflows, such as when hours go over 23 or minutes total up to over 59? Are there any T-SQL functions or other techniques that are useful in these scenarios?

15 October 2007 - Jeff Smith - 9 Comments

Temporary Tables

Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!" This article covers temporary tables and tables variables and is updated for SQL Server 2005.

17 January 2001 - Bill Graziano - 15 Comments

Using BULK INSERT to Load a Text File

This example combines dynamic SQL, BULK INSERT and the proper handling of double-quotes to solve a client's problem with loading various text file formats into a database. (This article has been updated through SQL Server 2005.)

19 March 2001 - Garth Wells - 64 Comments

How to Insert Values into an Identity Column in SQL Server

Identity columns are commonly used as primary keys in database tables.  These columns automatically assign a value for each new row inserted.  But what if you want to insert your own value into the column?  It's actually very easy to do.

6 August 2007 - Bill Graziano - 6 Comments

An Introduction to Triggers -- Part I

This article, submitted by Garth , covers the basics of using triggers. "A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure." If you're a developer and not familiar with triggers this article is a great starting point.

30 April 2001 - Garth Wells - 12 Comments

Stored Procedures: Returning Data

This article discusses three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.  Each example includes client-side code (ASP.NET) and server-side code (T-SQL) to read the results. (This article has been updated through SQL Server 2005.)

9 April 2001 - Bill Graziano - 9 Comments

Aggregating Correlated Sub-Queries

Every now and then you may attempt to calculate an aggregate function -- such as SUM() -- on a correlated subquery, only to encounter the following error:

Msg 130, Level 15, State 1, Line 24
Cannot perform an aggregate function on an 
expression containing an aggregate or a subquery.

This can be a tricky situation to resolve depending on how your query is structured and how complicated it is, but here's a couple of ideas to help you out.

23 October 2007 - Jeff Smith - 2 Comments

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -