Jeff's SQL Server WeblogJeff's SQL Server WeblogJeff's very popular Microsoft SQL Server weblog. Advice, ideas, techniques and more for beginners and experts alike, with a sense of humor as well. Articles
SQL Server Express Automated Backups
2007-12-06 21:10:00 I recently set up a web application using SQL Serve r Express for a client, and it was the first time I had done any "production" level work using the Express edition. As such, this was the first time I ever needed to configure automated backups for a SQL Express database. However, there is no built-in way to schedule backups without using manual scripts or external applications. I briefly toyed with writing something myself to get the job done, but of course we should not reinvent the wheel so I turned to Google to find out what's out there for options. After much searching, here's the solution I went with: Jasper Smith (no relation!) has written an extremely useful and powerful stored procedure called expressmaint that handles backups, generates reports, performs validation, removes old files, and has many other options and features. It's very complete and very easy to use. In addition to the stored procedure, he provides an expressmaint application that does the same thi... More About: Automated , Backups
Some SELECTs will never return 0 rows -- regardless of the criteria
2007-11-13 17:11:00 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. This is rather interesting and important to know and look out for, as it can cause some confusion and recently some of my ASP.NET code failed due to this. Let's take a look. We'll be using the following simple table for our examples: create table tmp (f1 int, f2 int) insert into tmp select 1,1 union all select 1,2 union all select 2,3 Now, if we SELECT from this table without any criteria, we will get 3 rows returned: select f1,f2 from tmp f1 f2 ----------- ----------- 1 1 1 2 2 3 (3 row(s) affected) If we put criteria on our SELECT that doesn't match any rows -- for example, where f2 > 3 -- we will get back zero rows: select... More About: Return , Criteria , Some
Splitting a single DataTable into Parent/Child DataTables for Hierarchical
2007-11-02 17:16:00 In ASP.NET, we often would like to output "grouped" data on our web pages, like this: Customer Product Sales -------- ---------- ----- ABC Foundation Product 1 $200 Product 2 $437 Product 3 $523 The XLZ Company Product 1 $240 Product 2 $892 Product 3 $395 The easiest way to do this is with nested Repeater controls; one for the outer group (Customers, this case), and within that Repeater's ItemTemplate we'd have another Repeater control for the details (Products). To use nested repeaters, you would return two separate result sets from SQL: A "Customers" result set, listing one row per Customer to display on the report A "Products" result set, listing one row per Product to display. You would put both of those results into a single DataSet as DataTables, and then create a relation between the two (on the CustomerID column). Now, in our DataSet, we have a parent-child relation and e... More About: Child , Single , Parent , Tata , Pare
Date Only and Time Only data types in SQL Server 2005 (without the CLR)
2007-10-31 14:13:00 In this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Serve r 2000. Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy. First, we must create two user defined data types: create type Date from dateTime create type Time from dateTime So, internally (and externally to our clients), these types are really just DateTime. But, we will apply some rules to these types so that the Date data type will always be constrained to a time exactly at midnight, and the the Time data type will always be at the "base date", or 1900-01-01: create rule DateOnlyRule as dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime go create rule TimeOnlyRule as datediff(dd,0,@DateTime) = 0 go Finally, we call sp_bindrule to bind the rules to the data types: EXEC sp_bindrule 'DateOnlyRule', 'Date' EXEC sp_bindrule 'TimeOnlyRule', 'Time' That's it! Now, we can create a table that uses our brand-n... More About: Data , Types
Taking a look at CROSS APPLY
2007-10-18 23:01:00 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 table is evaluated independently of the other tables in the SELECT. To limit the rows in table B so that B.Val = A.Val, we must do that outside of the derived table via a join or in the criteria: select A.*, b.X from A cross join (select * from B) b where A.Val = b.Val (Of course, the above is equivalent to doing an INNER JOIN to the derived table, or just joining to the table B.) Also, keep i... More About: Cross , Taking
Some Observations on CROSS APPLY
2007-10-18 23:01:00 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 everything in the parent SELECT is not accessible (though variables and parameters can be used). 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; A.Val is out of scope within the derived table, since the derived table is evaluated independently of the other tables in the SELECT. To limit the rows in table B so that B.Val = A.Val, we must do that outside of the derived table via a join or in the criteria: select A.*, b.X from A cross join (select * from B) b where A.Val = b.Val (Of course, the above is equivalent to doing an INNER JOIN to the derived table, or just joining to the table B.) Also, keep in mind that the scope-of-derived-tab... More About: Cross , Observations , Some
The Mailbag: Referencing Assemblies in Reporting Services; some SQL help
2007-10-18 18:19:00 As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag! Just some quickies today. Christopher writes: Greetings Jeff, First and foremost, great job with all of the blogs. I have a questions that I cannot seem to get a straight answer for. I am working with SQL Server Reporting Services (SSRS) and have the need to create VB functions to customize the reports generated. For example, a setter/getter to display information that would not be readily available from the query. SSRS allows this type of custom Visual Basic code to reside in the report itself, but since most of my code is across multiple reports, it makes 0 sense to place the same code in each report. Do you know of a way that I can create a "code library" so that there is one location for all of the code and all of the reports can access it? Thank you very much for your time and effort. Sincerely, Christopher McGraw Chris, Here's all the information that you need: Using Custom Ass... More About: Refer , Some
Some HTML / ASP.NET Thoughts ...
2007-10-17 17:13:00 On a particular real estate website, we have a "Property Summary" section that contains each property's name, the address, a phone number (if it exists), and a contact email address (if it exists). The information is data bound to properties in the page's code-behind, and the ASP.NET code basically looks like this: <div> <%# Property Name%> <%# AddressLine1 %> <%# AddressLine2 %> <%# AddressLine3 %> <%# CityStateZIP %> <%# Phone %> <%# Email %> </div> Because lots of these elements are optional, all of these properties have all been written to work like this in the code-behind: Protected Readonly Property AddressLine1 as String Get If _AddressLine1 = "" Then Return "" Else Return _AddressLine1 + "<br>" End if End Get End Property It was written this way so that there will not be extra empty lines where the data is missing, and there will be a line break if data exists. Ty... More About: Thoughts , Html , Some
Working with Time Spans and Durations in SQL Server
2007-10-15 15:28:00 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: Workin g with Time Spans and Durations in SQL Serve r . 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? 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? It is mostly targeted towards beginner SQL programmers, but even experienced programmers may find some of the ideas and tips useful. Article link
By The Way ... DISTINCT is not a function ...
2007-10-12 16:45:00 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 result set returned. That syntax is accepted because (employeeID) is just an expression, a reference to a column, which happens to be surrounded by parenthesis. For example, you could write: select distinct (employeeID), (salary) from salaryhist or: select (employeeID), (salary) from salaryhist or even: select distinct ((employeeID)), ((salary)) from salar... More About: Function
Be Careful When Mixing INNER and OUTER Joins
2007-10-11 20:39:00 I had previously written about the danger of Criteria on Outer Joins, but recently another situation popped up that occasionally causes confusion with OUTER JOINS that I thought I might address. The issue is when you have multiple tables joined in a single SELECT, and you mix OUTER and INNER JOINS together. 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. Consider the following schema and sample data: create table People ( PersonID int identity primary key, PersonName varchar(20) ) create table PetTypes ( PetTypeID int identity primary key, PetType varchar(10) ) create table Pets ( PetID int identity primary key, PetTypeID int references PetTypes(PetTypeID) not null, PetName varchar(10), OwnerID int references People(PersonID) not null ) insert into People (PersonName) select 'Fred Flintstone' union all select 'Barney Rubble'... More About: Careful
Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at
2007-10-09 21:02:00 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... More About: Creating , Strings , Done , Tring , Tena
A handy but little-known SQL function: NULLIF()
2007-09-27 15:50:00 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 fails. So, on the surface, it seems that perhaps the only way to handle this is with a case expression: select case when coalesce(Override, '') = '' then Description else Override end as Display from ... However, this is actually a great example of when the little used NULLIF() function can be handy. NULLIF() returns NULL if the two paramet... More About: Handy , Function , Litt , Null
Optimizing Conditional WHERE Clauses: Avoiding ORs and CASE Expressions
2007-09-18 22:25:00 Often, we need to create a flexible stored procedure that returns data that is optionally filtered by some parameters. If you wish to apply a filter, you set the parameter to the necessary value, if not, you leave it null. This is pretty standard stuff, of course, that we can write fairly easily, without the need for dynamic SQL, like this: create procedure GetData @MinDate int = null, @MaxDate int = null, @MinAmount money = null, @MaxAmount money = null, @ProductCode varchar(200) = null, @CompanyID int as select * from Data where (@MinDate is null OR @MinDate >= Date) and (@MaxDate is null OR @MaxDate <= Date) and (@MinAmount is null OR @MinAmount >= Amount) and (@MaxAmount is null OR @MaxAmount <= Amount) and (@ProductCode is null OR ProductCode = @ProductCode) and (@CompanyID is null OR CompanyID = @CompanyID) Note that we are using good boolean algebra as discussed here, and no... More About: Expressions , Case , Condi , Expression , Clause
Filtering transactions by month (plus other time periods)
2007-09-14 18:21:00 Introduction Previously, I wrote about grouping transactions by month. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. There are two parts to this equation: First, what is the best way to declare parameters that will be used to indicate which month you are looking for? Second, how can we efficiently and easily make use of those parameters to get back the data we need? Let's take a look at some approaches, both recommended and not. And, please: if you are not using the DATETIME data type to store dates in your tables, don't even bothering reading further -- fix your design first! Month Parameter Options @YearMonth as char(6) If you know me at all, you know that I will advise not to use this approach. We?ll need to parse and validate the values passed into ensure that they are valid, and we need to somehow communicate exactly which format to use when setting the parameter values. For example,... More About: Time , Transactions , Peri , Tran
Group by Month
2007-09-10 17:28:00 When you need to summarize transactional data by Mont h , 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... More About: Group
SQL Server 2008 - Enhancements in Date and Time Data Types (link)
2007-08-31 04:05:00 Speaking of dates and times, there's a nice post from Ravi. S. Maniam over at the msdn blogs regarding the new and exciting Enhancements in Date and Time Data Types for SQL Server 2008. It will be very interesting to see how date and time usage changes once SQL Server 2008 becomes the most commonly used edition. Of course, since even SQL 2005 still doesn't seem to be as widely adopted as I would like, who knows when that will be! More About: Link
Is it a String Literal or an Alias?
2007-08-30 17:36:00 Every now and then I see T-SQL code written like this: select col as 'columnname' from table 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 col. 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 difficult to quickly look at and digest: select 'literal' as 'columnname' columnname ---------- literal (1 row(s) affected) It just makes your code harder to read, especially if syntax highlighting is used, since we no longer know at a glance what is a string literal and what is not. Of course, it gets even more difficult when you are dealing with long, complicated SQL statements. It gets even ... More About: Alias , String , Tring
Working with Date and/or Time values in SQL Server: Don't Format, Don't Con
2007-08-29 16:04:00 The Importance of Data Types Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text. If you are designing a database in this scenario and you need to store or return integer values, which data type -- MONEY or VARCHAR -- would you use? Suppose I were to argue that MONEY is too complicated, and a waste of space, since all MONEY values always have 4 digits after the decimal which we don't need and it might get confusing. Therefore, we should use VARCHAR, which is much simpler. After all, if we use MONEY, every time we execute a SELECT, our integer data will be returned with 4 decimal places like "45.0000", which is certainly not what we want and doesn?t look like an integer; but if we use VARCHAR we will get "45" which looks much better and is really what we want to see. While eventually we will notice that we cannot add these VARCHAR numbers together, or sort them, or compare them, or do any kind of math w... More About: Time , Values , Format , Working
Composite Primary Keys
2007-08-23 16:07:00 Ah ? primary keys ? such a topic! When discussing what columns to define as a primary key in your data models, two large points always tend to surface: Surrogate Keys versus Natural Keys Normalization These can be very complicated and sometimes polarizing things to discuss. As I often try to do, I will attempt to discuss this topic from a slightly different perspective. Let's start things off with what I feel is a good interview question: How would you define what a primary key of a table is? a. An auto-generated numeric or GUID column in the table that uniquely identifies each row b. Any non-null column in the table that uniquely identifies each row c. None of the above I suspect that many people will answer (a), and quite a few will answer (b). If you answer (c), though, you are correct! Why? Because a primary key is not a single column, it is a set of columns. Many people who have designed large, complicated systems are simply not aware of this! I... More About: Composite , Primary , Posi , Prim
The problem isn't the poor database model; It's that external code is bound
2007-08-15 15:34:00 Dealing with poorly designed databases is a simple and common fact of life for programmers. It happens, sometimes due to lack of experience or education, or sometimes because business requirements were never analyzed properly or they changed. It's hard to avoid poor database designs, but it takes only a simple concept to make fixing those designs much easier. read more... More About: Model , Code , Database , Problem , Bound
More on GROUP BY; Examining SUM(Distinct)
2007-07-31 18:44:00 I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary report request and works towards the solution step-by-step. Part I: Intro to GROUP BY; Duplicates caused by JOINS; Identifying "Virtual Primary Keys"; Using COUNT(Distinct) Part II: Examining SUM(Distinct); GROUPING before JOINING; Using Derived Tables I tried to focus on what happens when you join two tables and then try to group and aggregate the results, and where and how to do this to avoid aggregating duplicate values. As always, I attempt to demonstrate breaking down the larger problem into smaller, simpler parts and then putting those pieces together to produce the final result. Perhaps most importantly: if you've ever used SUM(Distinct) in your code in an... More About: Group
SELECT * FROM TABLE -- except for these columns
2007-07-26 21:36:00 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? (myself included.) And these lazy people often would like to return all of the columns from a table except for one or two without typing them all out. So, why isn't this concept allowed? Would it make any sense? Here's my usual response: First, the answer is no, SQL does not support that. You must specify what you want. Second, if you ask me "why not? It would be great!", let me ask you this in return: "If SQL did support that syntax, and you execut... More About: Columns , Select , Sele , These
Becoming a Better Developer, plus a SQL Crime Drama!
2007-07-26 16:36:00 Sorry for the delay, Denis; thinking of ways to improve our developer skills is a great idea, thank you for including me. Here are my thoughts on the next 6 months. More design, less code. I'm going to try to focus a little more on user interfaces, graphics, layouts, and so on. I tend to write boring, plain vanilla corporate-style applications that frankly aren't much fun. Sure, they work great (usually!) and the layout is clean and easy to use (hopefully!), but my goodness -- it is boring. I plan on focusing a little more on the aesthetic design of my applications and web pages in the next 6 months; not so much that it detracts from the usability of my applications, or that it bloats them, but so that I can consciously improve upon this aspect of my work. Learn Flash. This kind of goes hand in hand with my previous point. I do lots of web development, but so far I have stayed away from Flash. Now, I think it's time to at least learn it. I am not a fan of flash-heavy ... More About: Drama , Crime , Developer , Develop , Ming
Distinguishing data from code
2007-07-24 15:02:00 What is data, and what is code? How do we define the difference, and decide what goes where? It is great to say "keep data out of your code", but what if that data is integral to the application itself? Isn't it therefore code, and not data? read more... More About: Data , Code , Shin
But *WHY* Must That Column Be Contained in an Aggregate Function or the GRO
2007-07-20 16:33:00 Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Arggh!! There it is, yet again .. that annoying error message. Why is SQL so picky about this? What's the deal!? read more... More About: Column , Must , Function , Greg , The G
Using LINQ with SQL (link)
2007-07-13 22:27:00 There's a great series of posts over at Scott Guthrie's Blog covering LINQ, a new feature in the upcoming version of Visual Studio ("Orcas"). Check it out; I have not had a chance to play around with it yet, but it certainly looks very interesting. The articles are very well done and explain the concept very clearly with lots of examples. From the article: The above language features help make querying data a first class programming concept. We call this overall querying programming model "LINQ" - which stands for .NET Language Integrated Query. Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring supp... More About: Link
"Nested WHERE-IN" Anti-Pattern Follow-up
2007-07-13 15:33:00 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... More About: Pattern , Anti , Where , Este
The "Nested WHERE-IN" SQL Anti-Pattern
2007-07-12 18:50:00 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... More About: Pattern , Anti , Where , Este
SQL 2005 PIVOT Operator (link)
More articles from this author:2007-07-12 15:24:00 There's two interesting posts over at the MSDN blogs from Craig Freedman about the new PIVOT operator in SQL 2005. First, he gives a nice overview of the operator and how to use it, and then he follows it up with a performance analysis as well. A good read, check it out. I hope to cover both the PIVOT and also the UNPIVOT operators a little myself at some point in a future post. More About: Link , Operator , Pivo 1, 2, 3 |
|
 |
|
 |
