Adam Machanic : Architecture

Stored procedures are not parameterized views

Adam Machanic — Thu, 13 Jul 2006 01:54:00 GMT

Peter van Ooijen over at CodeBetter.com posted in his blog about some observations he had when working with stored procedures in a recent project. What I found to be interesting about his post was his comment that a stored procedure can be, "a view with parameters." I've run into this assertion before, and it's something I think needs some clarification for a lot of developers. I do not feel that there is any real similarity between stored procedures and views -- they are entirely different types of objects in an SQL database, and should not be considered forms of one another in any way.

Following is an edited version of the response I left in Peter's blog; I thought it warranted its own post:

Stored procedures are not -- and never can be -- "parameterized views". A view in an SQL database can be treated the same as a table in virtually every context. Consider:

SELECT * 
FROM Tbl 

vs.

SELECT * 
FROM 
View 

One of the great things about working with views and tables is that the person querying the database does not need to know whether the base object being queried is a view or a table. For the sake of writing SQL queries, they are one and the same. Both a view and a table have well-defined columns, with well-defined datatypes.

These assertions cannot be made for a stored procedure as compared with a view. A stored procedure is related to a view only in as much as both are defined using SQL syntax. But beyond there, the two diverge into completely different types of entities. First of all, consider:

SELECT *
FROM StoredProcedure

This will not work, and will only result in an "invalid object name" exception. The reason? Stored procedures expose no explicit output contract. Thanks to conditional branching, dynamic SQL, and SELECT *, a stored procedure can output vastly different results beween invocations, or based on different input parameters. It is quite possible to code a stored procedure that will output no result sets for one set of input parameters, two result sets for another, and four for another. Or, it's possible to change the returned result sets, e.g. by outputting different column names or datatypes. Please note, this is an extremely poor (and very dangerous) coding habit to get into -- but the point is, it is impossible to verify the output of a stored procedure for a given set of input parameters without running it.

Furthermore, a stored procedure "late binds" to the base objects being queried. This adds to the difficulty in verifying the output of a stored procedure, and is why you can create the following stored procedure without getting an exception (until you try to run it, of course):

CREATE PROC XYZ 

AS 
    SELECT 
* 
    FROM ThisTableDoesNotExist 

GO 

These stored procedure behaviors are in stark contrast to the way views work. Views provide a couple of means of verification:

The output columns/data types can be verified, and bound to, before actually querying the view
A view can be "schema bound", meaning that the underlying base tables (or other views) which the view is based on cannot be changed, schema-wise, unless the view is dropped.

For the first point, simply query the INFORMATION_SCHEMA.COLUMNS or sys.Columns views, and column information can be determined for a view without having to query it.

The second point adds to the first in a few ways. Schema binding brings to views a certain sense of "early binding," which as I mentioned is missing in stored procedures. Although no view can be created if one of its base objects does not exist, schema binding takes it one step further and guarantees that the base objects used to create the view must exist, and must not be changed, for as long as the view is present in the database. This means that if a schemabound view is created that outputs a certain set of columns with certain datatypes, it is guaranteed to do so for as long as it exists in the database -- in other words, its contract is bound to the schema, and changes to other objects cannot affect it. This is a powerful guarantee, which stored procedures fail to make.

So now the question is, if a stored procedures isn't a parameterized view then what is? The answer, as of SQL Server 2000 (and continuing in 2005), is the table-valued UDF. A table-valued UDF is parameterized, has an explicit and verifyable output contract*, and can be schema bound. If you are looking to implement a solution that makes use of a form of parameterized views, stored procedures are probably not the right choice. I think that table-valued UDFs are quite underused and deserve a second (or first!) look from many T-SQL developers who may have glossed over them in the past.

* Note: Unlike for a view, the column list for a table-valued UDF cannot be queried from the INFORMATION_SCHEMA.COLUMNS table. The column list is, however, available from sys.Columns.

Rowset string concatenation: Which method is best?

Adam Machanic — Thu, 13 Jul 2006 01:15:00 GMT

Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side.

Except if you really have to create delimited strings in SQL Server. In which case you should read on.

There was a little discussion on SQLTeam about the best way to concatenate. I recommended a scalar UDF solution, whereas Rob Volk recommended a solution involving a temp table.

I mentioned my dislike for the temp table solution for a couple of reasons. First of all, it relies on a clustered index for ordering. That will probably work in this example, but is not guaranteed to always work and relying on indexes rather than ORDER BY for ordering is definitely not a habit I want anyone to get into. The clustered index as it was described in Rob's example also has another problem that I didn't even notice until I was writing this entry. But I'll get to that in a moment. The second reason I dislike the temp table is that I felt it would be less efficient than the scalar UDF.

Rob didn't agree about the efficiency. And so I set out to prove him wrong...

We'll use the Authors table in Pubs. I want a comma-delimited list, per state, of the last name of each author who lives there.

First, the scalar UDF:

USE pubs
GO

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

	SELECT @Output =	CASE @Output 
				WHEN '' THEN au_lname 
				ELSE @Output + ', ' + au_lname 
				END
	FROM Authors
	WHERE State = @State
	ORDER BY au_lname

	RETURN @Output
END
GO

To find the list I want:

SELECT DISTINCT State, dbo.ConcatAuthors(State)
FROM Authors
ORDER BY State

... And the adaptation of Rob's temp table method... I did change two things due to problems I discovered during testing. One, I've altered the au_lname column to VARCHAR(8000); the column in the Authors table is VARCHAR(40), not large enough for all of the California authors. What if we were dealing with a much larger dataset? Second, I added an IDENTITY column, and I'm clustering on that instead of the actual data to get the ordering. I'm doing so because of the VARCHAR(8000). Index rows can be a maximum of 900 bytes, so if we had enough data to exceed that length, this method would fail.

CREATE TABLE #AuthorConcat
(
	State CHAR(2) NOT NULL,
	au_lname VARCHAR(8000) NOT NULL,
	Ident INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)

INSERT #AuthorConcat 
(
	State,
	au_lname
)
SELECT
	State, 
	au_lname
FROM Authors
ORDER BY 
	State, 
	au_lname

DECLARE @Authors VARCHAR(8000)
SET @Authors = ''
DECLARE @State CHAR(2)
SET @State = ''

UPDATE #AuthorConcat
SET @Authors = au_lname =	CASE 
				WHEN @State = State THEN @Authors + ', ' + au_lname 
				ELSE au_lname END,
	@State = State

SELECT State, MAX(au_lname) 
FROM #AuthorConcat
GROUP BY State

Clever, but more complex and harder to read than the scalar UDF version. Output is identical, but that's not why we're here. Which one is more efficient?

Drumroll, please...

Results were tabulated using STATISTICS IO, STATISTICS TIME, and Query Analyzer's Show Execution Plan. DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE were run before each test.

Scalar UDF Method
Total cost: 0.0492
Total Scan count: 1
Total Logical reads: 2
Total Physical reads: 2
Total time: 25 ms

Temp Table Method
Total cost: 0.2131
Total Scan count: 4
Total Logical reads: 9
Total Physical reads: 2
Total time: 88 ms

So in conclusion, neither method is incredibly taxing with the tiny Pubs dataset, but I think I have proven that the UDF is far more efficient.

Update, February 28, 2005: Modified the adapation of Rob Volk's method to use a CREATE TABLE instead of SELECT INTO, as the latter is not necessarily guaranteed to insert rows in the right order for the sake of this example. Thanks to "PW" on SQLServerCentral for pointing this problem out. Note that this changed the total costs very slightly -- for the better -- but the UDF still performs better by quite a large margin.

No, stored procedures are NOT bad

Adam Machanic — Thu, 13 Jul 2006 01:10:00 GMT

I recently found a rather old post from Frans Bouma's blog, "Stored procedures are bad, m'key?".

Since comments are closed (he posted it almost a year ago), I have to reply here.

I'll state my bottom line at the top: Stored procedures are not only not bad, they're necessary for maintaining loosely coupled, application agnostic, flexible databases. And they're also necessary for maintaining loosely coupled, database agnostic, flexible database-based applications.

Mr. Bouma's argument centers around three claims: Ad-hoc queries are easier to construct in application code, stored procedures don't provide greater security than the application already does, and in certain cases stored procedure execution plan caching can be detrimental to performance. And all three of these arguments are true! But none of them has any bearing whatsoever on the real benefits of stored procedures.

Let's have a quick refresher on Computer Science 101, as it applies to object-oriented design: Loose coupling and high cohesion. What does that mean in plain English? Each module, or object, should have one very specific job, which it can perform for a VARIETY of other modules or objects on request (high cohesion). Furthermore, no module or object (or as few as possible) should depend upon the inner workings of another module or object. This latter problem is known as tight coupling, and its consequences are dire. Change one piece of code, end up changing every other piece of code that uses it.

News flash for Mr. Bouma: This is the reason object-oriented programming was invented. We try to separate components from one another so that their functions can be re-used and re-applied to other modules, other objects, and if we've done our job really well, even other applications entirely.

So how does this apply to the relationship between a database and an application?

The database, undoubtedly, should be completely application unaware. The database is serving up data. It has no idea what application is requesting the data, or whether an application is requesting the data. It needs to have no such idea. All it needs to do is keep serving and all is happy. Furthermore, the database doesn't care if multiple applications request data, or if those applications request the same data. The database is set up to provide a single point of access for all data requests in its domain of knowledge (i.e. the data in the database being queried).

And now to the other side, the application itself. I am an advocate of the application being as database agnostic as possible. I don't believe this is entirely possible, but it is certainly a goal to which we can aspire. Applications should request data from the database using standardized, documented interfaces, after which that data should be composed as quickly as possible (at the lowest level) into native objects. This allows for changes in the data interface to have as little ripple effect as possible in the application.

Mr. Bouma makes the claim that, "changes to a relational model will have always an impact on the application that targets that model". Again, he is correct. Which is why applications should NOT target a model. Applications should have absolutely no knowledge of database schema, including table names, column names, data types, or any other information. This is the role of stored procedures. We can, using stored procedures, completely encapsulate this metadata and provide standardized interfaces into the data.

Need to change a datatype in the database? Perhaps you won't need to change the output values that the application receives from the stored procedures. Perhaps you won't need to change the parameter input values. No change is needed in application code. Need to change a column name? Same thing. Need to re-architect the entire schema? Again, just change your stored procedures. The application will keep running as if nothing changed.

Contrast this to Mr. Bouma's suggestion that we drop stored procedures altogether and instead build all queries within application code. Suddenly, any small change in the database needs to be completely regression tested throughout not only one app, but every application that uses the database. You've created an extremely tight, perhaps unbreakable coupling between the application and the database. Large-scale changes to the schema will most likely never be possible. Will there ever really be time to re-write all of that application code?

Now, back to the three central arguments: Yes, ad-hoc queries are easier to construct in application code; but it's also argued by many database experts that ad-hoc queries are a sign of either poor database design, poor application design, or both. Either way, dynamic SQL isn't too hard to work with, and I've seen plenty of extremely ad-hoc applications in which its use is hardly a stumbling block. Next, security. It's true that if a user isn't authenticated in the application, he or she won't be able to use that application to access the data. So in that case, the stored procedure does not provide greater access control. But the same is certainly not true for every other application that uses the same database. Unless, of course, code is duplicated across every application. The database should be the final word on its data. This includes data security and data integrity. Application code simply cannot do this if the database need ever be shared. Finally, Bouma's assertions about cache plans are simply not worth touching. The WITH RECOMPILE option has been around long enough that DBAs and developers know it's there and know how to use it.

Now that we're at the bottom of this post, I'll re-state my conclusion: Stored procedures are necessary for proper object-oriented database-based application development. Not using them will force code duplication, tight coupling between applications and databases, and greatly increased man hours for every change made to either application or database.

Caveats of the TEXT datatype

Adam Machanic — Thu, 13 Jul 2006 01:02:00 GMT

Someone named "Krygim" posted the following question today in the microsoft.public.sqlserver.programming newsgroup:

"Will defaulting a TEXT field to an empty string take up more space than defaulting it to Null when no data is entered into the field. [sic]"

Before reading any further, I ask that you think for a moment and consider what you think the answer should be...

If you answered "yes", you're correct. An empty TEXT column will take up more space than a NULL one -- quite a bit more space, as it turns out. Here's the test code I posted to prove it:

USE tempdb

CREATE TABLE atable(acol TEXT NULL)

INSERT atable(acol)
SELECT NULL
FROM 	pubs..authors a,
	pubs..authors b,
	pubs..authors c,
	pubs..authors d

EXEC sp_spaceused 'atable'

CREATE TABLE btable(acol TEXT NOT NULL)

INSERT btable(acol)
SELECT ''
FROM	pubs..authors a,
	pubs..authors b,
	pubs..authors c,
	pubs..authors d

EXEC sp_spaceused 'btable'

DROP TABLE atable
DROP TABLE btable

Running this on my end tells me that the NULLs took up only 3 MB, whereas the empty strings took up 33 MB -- 11 times more space used!

I did a quick scan of Inside Microsoft SQL Server 2000 and discovered that for TEXT and IMAGE datatypes:

"If the amount of data is less than 32 KB, the text pointer in the data row points to an 84-byte text root structure."

This, in addition to the 16-byte pointer to the off-row location of the data, explains the huge difference in size between these two tables.

At this point, you're probably either wondering how to fix this or you've drifted away and are half-reading, half-thinking about what's for dinner. If you're in the latter category and not also in the former category, you either know the answer already or are not enough of a certified geek to be reading my blog. And if you're in the former category and not in the latter category, your priorities are certainly skewed, as we all know that food is more important than any stupid DBMS space wasted due to empty strings problem.

But for those still following me, the answer is one of my favorite about-to-be-deprecated features of SQL Server, the mighty text-in-row option... Re-run the second part of the previous example, with a new line inserted:

USE tempdb

CREATE TABLE btable(acol TEXT NOT NULL)

EXEC sp_tableoption 'btable', 'text in row', '24'
-- HINT: This is the new line

INSERT btable(acol)
SELECT ''
FROM	pubs..authors a,
	pubs..authors b,
	pubs..authors c,
	pubs..authors d

EXEC sp_spaceused 'btable'

DROP TABLE btable

There it is. Back down to 3 MB, even with the non-empty strings in the TEXT column. By storing the small data in-row, we've eliminated the 16-byte off-row pointer and the 84-byte root structure, and other assorted bytes that are used by the TEXT datatype that I don't know about (the math didn't quite add up when I tried to calculate where all of the space went).

So what does this tell us? I'm thinking that as a best practice, perhaps the text-in-row option should be used for every table with LOB columns, and that it should be set to around 100 bytes. This will still keep row sizes down when larger data is inserted (as it will go off-row), but it will also keep overall IO way down if the amount of rows with LOB data larger than 100 bytes is fairly sparse.

Comments?