Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

T-SQL

Generic T-SQL tips and tricks. Some of this applies to other dialects of SQL as well.
Why to avoid SELECT * from tables in your Views

-- clean up any messes left over from before: if OBJECT_ID('AllTeams') is not null  drop view AllTeams go if OBJECT_ID('Teams') is not null  drop table Teams go -- sample table: create table Teams (  id int primary key,  City varchar(20),  TeamName varchar(20) ) go -- sample data: insert into Teams (id, City, TeamName ) select 1,'Boston','Red Sox' union all select 2,'New York','Yankees' go create view AllTeams as  select * from Teams go select * from AllTeams --Results: -- --id          City                 TeamName ------------- -------------------- -------------------- --1           Boston               Red Sox --2           New York             Yankees -- Now, add a new column to the Teams table: alter table Teams add League varchar(10) go -- put some data in there: update Teams set League='AL' -- run it again select * from AllTeams --Results: -- --id          City                 TeamName ------------- -------------------- -------------------- --1           Boston               Red Sox --2           New York            ...

posted @ Friday, May 11, 2012 10:10 AM | Feedback (2) | Filed Under [ T-SQL Techniques SQL Server 2008 ]

How to calculate Median in SQL Server

Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. -- sample table: create table People (     Person varchar(1) primary key,     City varchar(10),     Age int ) go -- with some sample data: insert into People select 'A','Boston',23 union all  -- odd # select 'B','Boston',43 union all select 'C','Boston',29 union all select 'D','Chicago',15 union all -- single # select 'E','NY',12 union all  -- even # select 'F','NY',55 union all select 'G','NY',57 union all select 'H','NY',61 go -- here's our query, showing median age per city: select city,     AVG(age) as MedianAge from (     select City, Person, Age,         ROW_NUMBER() over (partition by City order by Age...

posted @ Monday, August 30, 2010 12:00 AM | Feedback (1) | Filed Under [ T-SQL Techniques SQL Server 2005 GROUP BY ]

The Mailbag: More on Ordering by Distinct Values ...

I promise to get back to writing articles on a more regular basis soon, but in the meantime, here's a comment from Nathan A. on using DISTINCT and ORDER BY: Jeff, This is actually a problem I have been puzzling over for quite a while now. I actually need to do that sort. I wonder if I may have to create another column that has the list of ordering values in it in increasing order so for my example above assuming a letter table and a number table that contains the numbers for letters, the number table would not change but I...

posted @ Tuesday, December 23, 2008 9:25 AM | Feedback (5) | Filed Under [ T-SQL Sorting User Defined Functions ]

Processing an OLAP cube with a T-SQL Stored Procedure

Here's a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL.  The parameters should be self-explanatory.  To me, this is a little easier and more flexible than processing using DTS packages. create procedure ProcessCube     @Database varchar(100),     @Cube varchar(100),     @Partition varchar(100)  = null, -- If NULL, process the entire Cube     @Server varchar(100) = 'localhost' as   /*     Author:        Jeff Smith     Version:    10/27/2008 */   /* variables used to store object handles */ declare @o_svr int, @o_db int, @o_cube int, @o_part int, @o_mds int declare @hr int   /* different cube processing options. This SP uses "default" */ declare @PROCESS_DEFAULT int declare @PROCESS_FULL int declare @PROCESS_REFRESH_DATA...

posted @ Monday, October 27, 2008 3:26 PM | Feedback (12) | Filed Under [ T-SQL OLAP ]

How To Calculate the Number of Week Days Between two Dates

If the start date and end date are both week days, then the total number of week days in between is simply: (total difference in days) - (total difference in weeks) * 2 or  DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 ... since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends. If you have a table of holidays, then you can simply subtract them out as well: DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 -  (select count(*) from holidays where holiday_date between @start and @end) Now, what if the start day or the end day is...

posted @ Thursday, July 31, 2008 12:39 PM | Feedback (13) | Filed Under [ T-SQL ]

Convert input explicitly at your client; don't rely on the database to "figure it out"

A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).  I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled. In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written...

posted @ Thursday, July 24, 2008 9:12 AM | Feedback (11) | Filed Under [ T-SQL .NET (C# / VB) Techniques DateTime Data ]

The Truth about "Cursor Busting" in SQL

Let's say you are called in to troubleshoot a stored procedure that is performing poorly. You dive in to investigate and this is what you find: create procedure ProcessProducts as     declare @Products cursor, @ProductID int     set @Products = cursor for select ProductID from Products order by ProductID     open @Products     fetch next from @Products into @ProductID     while (@@FETCH_STATUS=0)         begin         exec DoSomething @ProductID         fetch next from @Products into @ProductID         end     deallocate @Products Ah ha! A cursor!  It seems we have identified the bottleneck: Clearly, the performance problems are because the code is not doing things in a set-based manner, but rather...

posted @ Thursday, June 05, 2008 10:56 AM | Feedback (14) | Filed Under [ T-SQL Efficiency ]

GROUP BY ALL

Here's an obscure piece of SQL you may not be aware of:  The "ALL" option when using a GROUP BY. Consider the following table: Create table Sales (     SaleID int identity not null primary key,     CustomerID int,     ProductID int,     SaleDate datetime,     Qty int,     Amount money ) insert into Sales (CustomerID, ProductID, SaleDate, Qty, Amount) select 1,1,'2008-01-01',12,400 union all select 1,2,'2008-02-25',6,2300 union all select 1,1,'2008-03-02',23,610 union all select 2,4,'2008-01-04',1,75 union all select 2,2,'2008-02-18',52,5200 union all select 3,2,'2008-03-09',99,2300 union all select 3,1,'2008-04-19',3,4890 union all select 3,1,'2008-04-21',74,2840 SaleID      CustomerID  ProductID   SaleDate                Qty         Amount ----------- ----------- ----------- ----------------------- ----------- --------------------- 9           1           1           2008-01-01 00:00:00.000 12          400.00 10          1           2           2008-02-25 00:00:00.000 6           2300.00 11          1           1           2008-03-02 00:00:00.000 23          610.00 12          2          ...

posted @ Monday, May 05, 2008 12:25 PM | Feedback (5) | Filed Under [ T-SQL GROUP BY ]

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important.  In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set.  We all know that there's lots of bad databases designs out there, so this can be a handy technique to know.  Of course, even a well designed, fully normalized database can still benefit from "unpivoting" from time to time,...

posted @ Wednesday, April 23, 2008 10:33 AM | Feedback (17) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques SQL Server 2005 Report Writing SQL Server 2008 ]

But it's not missing. It's just ... invisible, that's all.

Microsoft Access has a pretty handy boolean property that you can set for any "text" column in your tables: AllowZeroLength True means that empty strings ('') are allowed in the column, False means that they are not. This is actually quite nice, because by using this along with the Required (i.e., disallow NULLS) property, you can ensure that your column has an actual, non-Null, non-Empty String value without the need for any additional constraints. SQL Server does not have this property, but we can easily achieve the same effect by using a CHECK constraint: create table foo (     column1 varchar(100) not null check (column1 <>...

posted @ Wednesday, March 19, 2008 3:51 PM | Feedback (4) | Filed Under [ T-SQL ]

Does SQL Server Short-Circuit?

I got an email recently regarding one of my early blog posts from the olden days: Steve Kass wrote about your post:"there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed". I am not certain that optimization changes the priority of the expressions, but I do...

posted @ Friday, February 22, 2008 11:04 AM | Feedback (8) | Filed Under [ T-SQL Efficiency ]

Simplify Your SQL with Variables and Derived Tables (or Common Table Expressions)

As with any programming language, it is important in SQL to keep your code short, clear and concise. Here are two quick tips that I find are very helpful in obtaining this goal.

posted @ Thursday, December 20, 2007 12:20 PM | Feedback (7) | Filed Under [ T-SQL Techniques Efficiency GROUP BY ]

SELECT DISTINCT and ORDER BY

Let's take a look at another one of those stupid, arbitrary SQL Server error messages that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too): Msg 145, Level 15, State 1, Line 4 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. This message pops up when you ask for DISTINCT rows for one set of columns, but you'd like to have the results ordered by one or more columns not specified in your distinct set.  For some reason, SQL Server will not allow...

posted @ Thursday, December 13, 2007 2:58 PM | Feedback (49) | Filed Under [ T-SQL GROUP BY Sorting ]

Some SELECTs will never return 0 rows -- regardless of the criteria

In SQL, the general rule of thumb is that the number of rows returned from a SELECT will be zero if your criteria did not match any data. However, there is an important exception to this rule: it does not apply when asking for aggregate calculations such as SUM(), MIN() or MAX(), without any grouping. read more...

posted @ Tuesday, November 13, 2007 11:11 AM | Feedback (11) | Filed Under [ T-SQL GROUP BY ]

Taking a look at CROSS APPLY

Applying a Sub-Query, Joining a Derived Table ... I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table.  Let's see if I can explain that .... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible (though variables and parameters can be referenced).  For example, consider: select A.*, b.X from A cross join (select B.X from B where B.Val=A.Val) b That is not legal because A.Val is out of scope within the derived table; this is because the derived...

posted @ Thursday, October 18, 2007 5:01 PM | Feedback (10) | Filed Under [ T-SQL Techniques SQL Server 2005 Joins/Relations ]

Working with Time Spans and Durations in SQL Server

If you read this blog on a regular basis, you probably know that a frequently covered topic is dates and times and the use of the DateTime data type.   Well, along those same lines, my latest article has just been published over at SQL Team: Working with Time Spans and Durations in SQL Server. From the Article: 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?...

posted @ Monday, October 15, 2007 9:28 AM | Feedback (0) | Filed Under [ T-SQL Links DateTime Data ]

By The Way ... DISTINCT is not a function ...

Have you ever seen (or written) code like this: select distinct(employeeID), salary from salaryhist That compiles and executes without returning any errors.  I've seen that attempted many times over the years, and of course people think DISTINCT is "broken" and "not working" because they see multiple rows for each employeeID.  "But I asked for only distinct employeeIDs!" they say. Well, the DISTINCT has nothing to do with the EmployeeID column; it is not a function that accepts arguments!  It is just a tag that you can put after the word SELECT to indicate that you want only distinct combinations of all columns in the...

posted @ Friday, October 12, 2007 10:45 AM | Feedback (3) | Filed Under [ T-SQL ]

Be Careful When Mixing INNER and OUTER Joins

Be careful when mixing OUTER and INNER JOINS together in a single SELECT statement. The end result doesn't always seem to "work", and it can be tricky to understand exactly why and how to fix it without incurring additional unintended side effects. Here's an example that demonstrates some of the issues. read more...

posted @ Thursday, October 11, 2007 2:39 PM | Feedback (27) | Filed Under [ T-SQL Techniques Joins/Relations ]

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...

posted @ Tuesday, October 09, 2007 3:02 PM | Feedback (19) | Filed Under [ T-SQL .NET (C# / VB) Efficiency Report Writing ASP.NET Joins/Relations ]

A handy but little-known SQL function: NULLIF()

A web application I have inherited uses data from a stored procedure that returns two columns:  Description and Override.  Basically, for each item returned, the web page should display the Description unless an Override is provided, in which case the Override is displayed.  This appears to be a simple case of using ISNULL() or COALESCE(): select coalesce(Override, Description) as Display from ... Unfortunately, it turns out that there is a mixture of NULL values and empty strings ('') in the Override column, so this doesn't work -- that expression will return '' instead of using the override because the coalesce() check for NULL...

posted @ Thursday, September 27, 2007 9:50 AM | Feedback (22) | Filed Under [ T-SQL ]

Group by Month (and other time periods)

When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs, but remember: Keep it short and simple in T-SQL, and always do all of your formatting at your presentation layer where it belongs. read more...

posted @ Monday, September 10, 2007 11:28 AM | Feedback (58) | Filed Under [ T-SQL GROUP BY DateTime Data ]

Is it a String Literal or an Alias?

Every now and then I see T-SQL code written like this: select somecolumn as 'columnname' from sometable Notice that 'columnname', despite its appearance, is not a string literal or a string expression, it is the alias that we are assigning to the column somecolumn.  Putting the name of an alias in single quotes in T-SQL is completely valid syntax, and it will work fine, but I feel that it is a really bad practice.   It makes the distinction between string literals and object names very blurry, and it can lead to confusion when examining and maintaining your code. For example, to me this is...

posted @ Thursday, August 30, 2007 11:36 AM | Feedback (4) | Filed Under [ Miscellaneous T-SQL ]

Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME

The next time you are working with dates and times, please remember: how would you handle things if you were working with integers and decimals? The same logic and reasoning applies. Be smart, let SQL do the work for you and use the right data types for the job, even if things don't always "look" right. read more...

posted @ Wednesday, August 29, 2007 10:04 AM | Feedback (33) | Filed Under [ T-SQL Efficiency Database Design DateTime Data ]

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. read more...

posted @ Tuesday, July 31, 2007 12:44 PM | Feedback (6) | Filed Under [ T-SQL Links GROUP BY ]

SELECT * FROM TABLE -- except for these columns

A occasional question seen in the forums, which was just recently asked today, is: "I know I can use SELECT * FROM table to get all of the columns from a table, but is there a way to write SELECT *  minus columnX FROM Table to get all of the columns except for certain ones?" Now, my goal isn't to debate whether not "SELECT *" is bad or good or should be used or not.  The fact of the matter is, people use it all the time because it is quicker and shorter than typing out all of the column names. People are lazy, right? ...

posted @ Thursday, July 26, 2007 3:36 PM | Feedback (25) | Filed Under [ Miscellaneous T-SQL ]

"Nested WHERE-IN" Anti-Pattern Follow-up; More on Derived Tables (sub-queries)

A quick follow up to the "Nested WHERE-IN" anti-pattern post from yesterday ... If you didn't get a chance, be sure to read the comments from that post as well, there are some great points in there so far. read more...

posted @ Friday, July 13, 2007 9:33 AM | Feedback (3) | Filed Under [ T-SQL Techniques Joins/Relations ]

The "Nested WHERE-IN" SQL Anti-Pattern

There's a fascinating technique that I see many beginners use when writing SQL statements, and I call it the "Nested WHERE-IN" anti-pattern. It is, unfortunately, a common SQL technique used to avoid JOINS at all costs. read more...

posted @ Thursday, July 12, 2007 12:50 PM | Feedback (19) | Filed Under [ T-SQL Techniques Joins/Relations ]

Retrieving Identity Values When Inserting Multiple Rows

You're INSERTing multiple rows from an un-normalized import table. Each row is assigned an identity primary key. You know you can use scope_identity() to retrieve one identity at a time, but how do you retrieve a whole set of them? Are cursors the only answer? As usual, it depends on having logical specifications and a good design. read more..

posted @ Monday, July 02, 2007 12:31 PM | Feedback (9) | Filed Under [ T-SQL Database Design Imports/Exports ]

Passing an Array or Table Parameter to a Stored Procedure

SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure. For example, you might wish to pass a set of Customers to an invoice generating stored procedure, or a set of employees for which you’d like to calculate a particular bonus. SQL Server already has everything you need to do this, and you don't need CSV or XML strings. read more...

posted @ Tuesday, June 26, 2007 9:36 AM | Feedback (29) | Filed Under [ T-SQL Techniques Database Design ]

Using GROUP BY to avoid self-joins

Sometimes, it appears that a necessary solution to common SQL problems is to join a table to itself. While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table.

read more...

posted @ Tuesday, June 12, 2007 11:35 AM | Feedback (5) | Filed Under [ T-SQL Techniques Efficiency Joins/Relations GROUP BY ]

More on Runs and Streaks in SQL

That's right boys and girls, it's what you've been waiting for all weekend: Another edition of the mailbag!

posted @ Wednesday, May 30, 2007 8:43 AM | Feedback (11) | Filed Under [ T-SQL Techniques Efficiency ]

SQL Server 2005: Specifying Partitions for Aggregate Functions

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statements?

read more...

posted @ Monday, May 21, 2007 2:52 PM | Feedback (7) | Filed Under [ T-SQL SQL Server 2005 Links GROUP BY ]

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule

Here's my response to a SQL Challenge, regarding how to find consecutive free time slots in a schedule. This can sometimes be tricky to solve in SQL, but using either of the two techniques shown here, it is actually pretty easy. (Updated to show 2 possible solutions)

read more...

posted @ Tuesday, May 15, 2007 9:52 AM | Feedback (6) | Filed Under [ T-SQL Techniques Efficiency Links DateTime Data ]

Criteria on Outer Joined Tables

When using an OUTER JOIN, you should put criteria on the outer table in the join condition, not in the WHERE clause. However, I often see a "workaround" to avoid this simple and solid rule, which might seem to work but actually doesn't. Since it is hard to explain why over and over in forum posts, I thought it might be helpful to address that here once and for all with an example.

read more...

posted @ Monday, May 14, 2007 10:12 AM | Feedback (16) | Filed Under [ T-SQL Joins/Relations ]

Hamming Distance Algorithm in SQL

Here's a simple User-Defined Function implementation of the Hamming Distance Algorithm for SQL Server, useful for tracking down transposition errors in your data.

read more...

posted @ Wednesday, May 09, 2007 10:39 AM | Feedback (6) | Filed Under [ T-SQL Code Library - SQL ]

In SQL, it's a Case Expression, *not* a Case Statement

A Google search for the phrase sql "case statement" returns 127,000 results. Meanwhile, if we do a search for the phrase sql "case expression" we get back only 43,900 results. What does this mean? Most people don't understand what SQL's CASE feature really is, how to use it, or how it works!

read more...

posted @ Thursday, May 03, 2007 9:15 AM | Feedback (18) | Filed Under [ Miscellaneous T-SQL ]

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables. Well, now in SQL 2005, we have another option: using EXCEPT and INTERSECT. And these are even easier!

read more...

posted @ Wednesday, May 02, 2007 2:47 PM | Feedback (26) | Filed Under [ T-SQL SQL Server 2005 ]

Thinking Set-Based .... or not?

So, I hear you're a "set-based SQL master" ! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach. However, don't completely forget the most important skill that you learned in the procedural world!

read more...

posted @ Monday, April 30, 2007 1:03 PM | Feedback (21) | Filed Under [ T-SQL Techniques Report Writing Joins/Relations ]

Custom Auto-Generated Sequences in SQL Server

Be sure to visit SQLTeam.com to check out my latest article, Custom Auto-Generated Sequences in SQL Server. It addresses a common question we see in the SQL Team forums.

posted @ Tuesday, April 24, 2007 8:36 AM | Feedback (3) | Filed Under [ T-SQL Database Design Links ]

Better Alternatives to a FULL OUTER JOIN

As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.

read more...

posted @ Thursday, April 19, 2007 11:56 AM | Feedback (69) | Filed Under [ T-SQL Techniques Efficiency Report Writing Joins/Relations GROUP BY ]

How to format a Date or DateTime in SQL Server

Everything you ever wanted to know about how to use SQL Server's advanced features to format Dates into any format that you need! Here, for the first time, all in one place, is the secret that no one wants you know about how easy it is to format data in T-SQL!

read more...

posted @ Friday, April 13, 2007 4:45 PM | Feedback (56) | Filed Under [ T-SQL Techniques Efficiency Database Design DateTime Data ]

SQL Check Constraints: Like George Costanza, sometimes you need to do the Opposite!

It's been a while, so let's open up the old mailbag. Today's question is about writing a character-based check constraint. The answer: Simple ... do the *opposite* !

read more..

posted @ Thursday, April 05, 2007 2:26 PM | Feedback (16) | Filed Under [ T-SQL Database Design ]

Conditional Joins in SQL Server

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort. You might think to do this with either a CASE expression or with some OR boolean logic in your join expression. There's a much better way to approach the problem.

read more...

posted @ Tuesday, April 03, 2007 12:15 PM | Feedback (40) | Filed Under [ T-SQL Report Writing Joins/Relations ]

Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects. Here's a nice list of handy things to help you out with converting your projects.

read more...

posted @ Friday, March 30, 2007 3:02 PM | Feedback (38) | Filed Under [ T-SQL MS Access ]

More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions

I wrote about a few of the new features in SQL 2005 and how they can be used to solve some old "classic" SQL problems very easily, and I thought I'd briefly discuss a few more. Also, if you enjoy baseball, read on!

read more...

posted @ Friday, March 30, 2007 9:21 AM | Feedback (31) | Filed Under [ T-SQL Sports Paging Data SQL Server 2005 ]

SQL Server 2005: Using PARTITION and RANK in your criteria

The RANK and PARTITION features in 2005 are simply amazing. They make so many "classic" SQL problems very easy to solve.

read more...

posted @ Wednesday, March 28, 2007 2:18 PM | Feedback (34) | Filed Under [ T-SQL Sports SQL Server 2005 ]

Sometimes the problem isn't the code. It's the specs.

I thought I'd take a few minutes to discuss something we see quite often in the programming world, using a T-SQL example of a stored procedure that accepts a list of optional parameters allowing you to determine some basic filters on the results.

read more...

posted @ Wednesday, March 14, 2007 10:23 AM | Feedback (13) | Filed Under [ T-SQL Techniques GROUP BY ]

Simple T-SQL Proper Case User-Defined Function

I posted this one a long time ago and needed to use it today, so I thought I'd post it up here as well in case anyone finds it useful. This simply attempts to capitalize the first letter of each word for the string passed in. Use it to help clean up some pre-existing data, but don't use it as a way of presenting your data that is stored improperly since it isn't exact.

read more...

posted @ Friday, March 09, 2007 8:53 AM | Feedback (22) | Filed Under [ T-SQL ]

Essential SQL Server Date, Time and DateTime Functions

I've posted some variations of these before, but here they all are in 1 easy package: The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

read more...

posted @ Tuesday, January 02, 2007 11:38 AM | Feedback (98) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Always Use Parameters. Even if you don't use Stored Procedures.

Let's assume that for some reason you are not using Stored Procedures. While I can respect your choice in that regard, that doesn't mean that you cannot still use parameters when constructing your SQL statements at the client.

read more...

posted @ Friday, July 21, 2006 10:43 AM | Feedback (22) | Filed Under [ T-SQL .NET (C# / VB) Techniques ]

How to JOIN Multiple Transactional Tables in SQL

A common difficulty beginning SQL programmers encounter is joining two or more transactional tables all in one SELECT statement. Missing data, duplicates, time-out errors, and other unexpected results often arise from trying to directly write JOINS between two transaction tables.

read more...

posted @ Monday, June 19, 2006 3:34 PM | Feedback (10) | Filed Under [ T-SQL Techniques Joins/Relations GROUP BY ]

Vulnerable to SQL Injection?

One of the things that troubles me most about SQL Injection is that is seems it is still very misunderstood. For example, is the following psuedo-code vulnerable to SQL Injection?

read more...

posted @ Friday, April 21, 2006 9:34 AM | Feedback (9) | Filed Under [ T-SQL Techniques Security ]

Another SQL Suduko Solver

There's been quite a few posts out there with SQL implementations of Suduko puzzle solvers.

read more...

posted @ Thursday, April 06, 2006 1:55 PM | Feedback (6) | Filed Under [ T-SQL Techniques ]

Some Simple SQL Rules to Live By

Most of these are really basic. Some are my take on established ideas and standards, and may be controversial. Agree or disagree? All feedback is welcome! (well ... mostly the "I agree, you're a genius" feedback is welcome ... but I'll accept all of it, I suppose)

read more...

posted @ Tuesday, March 14, 2006 12:42 PM | Feedback (33) | Filed Under [ T-SQL Techniques Efficiency SQL Server 2005 Security ]

Data belongs in your tables -- not in your code

Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables. Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.

read more..

posted @ Friday, February 10, 2006 10:41 AM | Feedback (37) | Filed Under [ T-SQL Techniques Efficiency Database Design ]

SQL GROUP BY techniques

One aspect of the versatile SELECT statement that seems to confuse many people is the GROUP BY clause. It is very important to group your rows in the proper place.

read more...

posted @ Wednesday, December 14, 2005 11:53 AM | Feedback (80) | Filed Under [ T-SQL GROUP BY ]

The power of the Cross Join

Many SQL books recommend that you “avoid cross joins” or “beware of Cartesian products” when writing your SELECT statements, which occur when you don't express joins between your tables. It’s true that you need to ensure that your join conditions are adequately stated so that you don’t accidentally produce this effect, but it is not true that you should avoid these types of joins in every situation.

read more...

posted @ Monday, September 12, 2005 9:20 AM | Feedback (26) | Filed Under [ T-SQL Techniques Joins/Relations ]

.NET CrossTabs versus SQL Server CrossTabs

As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.

read more...

posted @ Thursday, May 12, 2005 10:01 AM | Feedback (7) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Efficiency Report Writing Code Library - C# / VB.NET ]

.NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database.  Consider the result of a crosstab operation -- the columns returned will vary depending on the data.  There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes.  Also, in T-SQL there is no easy way to dynamically pivot data, and even doing it with...

posted @ Wednesday, May 11, 2005 3:49 PM | Feedback (23) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

Another Dynamic SQL CrossTab Stored Procedure

First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!

read more...

posted @ Monday, May 02, 2005 2:00 PM | Feedback (60) | Filed Under [ T-SQL CrossTabs / Pivoting Data Code Library - SQL ]

Date Only and Time Only User Defined Dataypes in SQL Server 2000

I talked about separating dates from times for certain columns in your database. One thing I have never really used before in SQL Server 2000 is user-defined datatypes and rules. Rules are like CHECK constraints, but from what I understand they are very non-standard.

read more...

posted @ Thursday, December 02, 2004 2:41 PM | Feedback (8) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Breaking apart the DateTime datatype -- Separating Dates from Times in your Tables

I don't like storing dates along with the time portion in my database tables. Sure, I use the datatype, but I wish SQL provided a “Date” datatype and a “Time” datatype that were separate. It really sometimes requires too much work in T-SQL to separate the date portion from the time portion, and often in a WHERE clasue you don't care about the time, you just want data for a single day.

posted @ Thursday, December 02, 2004 11:17 AM | Feedback (10) | Filed Under [ T-SQL DateTime Data ]

Returning Random Numbers in a SELECT statement

As it is implemented, the RAND() function in SQL Server doesn't let you return a different random number per row in your SELECT statement. For example ...

read more...

posted @ Monday, November 22, 2004 3:13 PM | Feedback (25) | Filed Under [ T-SQL ]

A Quick Lesson on SQL Indexes

Just a quick post of some benchmarking code for our good friend Guayo at TheDailyWTF, in regards to this discussion.

posted @ Friday, November 12, 2004 4:42 PM | Feedback (14) | Filed Under [ Miscellaneous T-SQL Techniques ]

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.

read more...

posted @ Wednesday, November 10, 2004 9:29 AM | Feedback (152) | Filed Under [ T-SQL Techniques GROUP BY ]

Searching a column for all words, any words, or exact phrase in a SQL Table

Here's one way to implement a search, similar to Google's, if you don't want to (or can't) use full text indexing. This allows you to pass a string of words, and indicate that either ALL words must match, ANY must match, or the exact string must match.

posted @ Tuesday, November 02, 2004 4:35 PM | Feedback (27) | Filed Under [ T-SQL Code Library - SQL ]

Delete Duplicates And Resolve Foreign Key References in SQL

Here's a scenario many of us have encountered: You inherit a database. TableX has an identity column "ID" set up as the primary key, and contains many duplicates.

read more...

posted @ Thursday, October 07, 2004 11:15 AM | Feedback (4) | Filed Under [ T-SQL Database Design Joins/Relations ]

Passing Arrays of Values as Parameters to Stored Procedures

I see many people have the need to pass in multiple pieces of information to stored procedures in a parameter, often as a CSV string. For example, you might have a stored procedure that will return customer info for all customer ID's passed in a CSV string, called something like this:

read more...

posted @ Monday, October 04, 2004 9:49 AM | Feedback (6) | Filed Under [ T-SQL ]

Using SQL to solve the "Sliding Tiles" puzzle

I just wanted to take a minute to highlight this thread in the sqlteam forums.

posted @ Wednesday, September 22, 2004 12:05 PM | Feedback (0) | Filed Under [ Miscellaneous T-SQL Techniques ]

More aggregate SQL functions

Despite the cool things you can do with writing your own custom aggregate functions as shown in my last article, in many cases you don't need custom functions to get what you need. This should answer some of the questions raised in the comments from my article.

more...

posted @ Sunday, July 11, 2004 10:12 PM | Feedback (1) | Filed Under [ T-SQL Code Library - SQL ]

SQL Server 2000 UDF's : Custom Aggregate and Cumulative Functions

If the topic intrigues you, please check out my latest article: http://weblogs.sqlteam.com/jeffs/articles/1490.aspx A little too long for a blog unfortunately.  Before reading the article, keep in mind it's purely an academic exercise.  But the ideas presented are hopefully somewhat original, and might even have some practical applications, who knows? Enjoy!

posted @ Thursday, June 03, 2004 9:24 PM | Feedback (0) | Filed Under [ T-SQL ]

Efficient and DYNAMIC Server-Side Paging with SQL Server 2000

Here is a completely dynamic version of my paging technique in the form of a stored procedure. Just pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.

read more...

posted @ Monday, March 22, 2004 10:13 PM | Feedback (88) | Filed Under [ T-SQL Paging Data ]

Using OPENXML to process CSV (comma-separated value) strings

This may be common knowledge to those among us that are handy with XML (I'm not), but since I just uncovered this nice little trick I thought I'd pass it along either way.  It is a very easy way to process a CSV string and turn it into multiple rows: declare @idoc int  -- the handle for the XML documentdeclare @csv varchar(8000) -- the CSV we are being passed set @csv = '1,2,3,7,4,8,10'  -- our list of CSV values -- With a simple edit of the CSV string: set @csv = '' -- We can now do this: EXEC sp_xml_preparedocument @idoc OUTPUT, @csv -- And then: SELECT    *FROM      ...

posted @ Sunday, January 25, 2004 10:01 AM | Feedback (5) | Filed Under [ T-SQL ]

Efficient paging of recordsets: SQL Server 2000

Here's the most efficient way that I can think of to return rows @a through @b from a table or query, when each row is ordered and can be uniquely identified by a set of columns, in SQL 2000.

more...

posted @ Monday, December 22, 2003 12:31 PM | Feedback (35) | Filed Under [ T-SQL Paging Data Code Library - SQL ]

Create a Date in T-SQL

Another way to create dates easily, w/o using CONVERT or using a UDF (i.e., if you have SQL 7.0), if you are starting out with integers representing the year, month and day of the date you need to create:

read more...

posted @ Monday, December 15, 2003 10:07 AM | Feedback (6) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Creating Dates and Date Math in T-SQL with a UDF

To me, this is a pretty handy function to have in your toolbox ...

posted @ Tuesday, December 09, 2003 10:50 AM | Feedback (27) | Filed Under [ T-SQL DateTime Data ]

A Recursive User-Defined Function (SQL Server 2000)

Anyone know what this will return? Unfortunately, the argument must be less than or equal to 32 due to the number of levels of recursion SQL can handle. It's probably not very efficient, either. But, it's pretty cool!

posted @ Friday, November 21, 2003 4:33 PM | Feedback (3) | Filed Under [ T-SQL ]

SQL WHERE clauses: Avoid CASE, use Boolean logic

As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.

posted @ Friday, November 14, 2003 12:12 PM | Feedback (38) | Filed Under [ T-SQL Techniques Efficiency ]

Keep those SQL Crosstabs flexible!

I recently helped someone with performing some simple cross-tabs in SQL ... read more...

posted @ Friday, October 24, 2003 4:40 PM | Feedback (1) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques ]

Comparing sets of rows from two tables

I thought I'd post a blog showing a technique I haven't seen elsewhere.  This is from a recent post of mine at the SqlTeam forums. The problem: you have two tables, each containing mutiple rows of data per “SetID”.  You need to compare the two tables to see which sets have complete matches. First, the DDL and the data: create table #t1  (SetID int not null,  Attribute varchar(10) not null,  Value int not null, constraint t1_pk primary key (SetID, Attribute)) create table #t2  (SetID int not null,  Attribute varchar(10) not null,  Value int not null, constraint t2_pk primary key (SetID, Attribute)) GO insert into #t1 select 1,'a',1 union select 1,'b',2 union select 1,'c',3 union select 2,'a',4 union select 2,'c',5...

posted @ Friday, October 03, 2003 1:59 PM | Feedback (15) | Filed Under [ T-SQL Joins/Relations ]

PARSENAME() - a simple way to parse (some) strings in SQL

Handy reminder: The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods.

read more...

posted @ Tuesday, September 30, 2003 5:09 PM | Feedback (2) | Filed Under [ Miscellaneous T-SQL Code Library - SQL ]

Powered by:
Powered By Subtext Powered By ASP.NET