THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Learning PostgreSql: Embracing Change With Copying Types and VARCHAR(NO_SIZE_NEEDED)

PostgreSql 9.3 allows us to declare parameter types to match column types, aka Copying Types. Also it allows us to omit the length of VARCHAR fields, without any performance penalty. These two features make PostgreSql a great back end for agile development, because they make PL/PgSql more resilient to changes. Both features are not in SQL Server 2008 R2. I am not sure about later releases of SQL Server.

Let us discuss them in more detail and see why they are so useful.

Using Copying Types

Suppose that we have the following table:

CREATE TABLE MyData(ID SMALLINT, SomeValue REAL);
INSERT INTO MyData(ID, SomeValue) VALUES(1, 1.23);

The following two functions do the same thing. The second one uses copying types, and as such it will be more robust should the table structure change. We shall see that soon.


CREATE OR REPLACE FUNCTION GetSomeValueById(p_ID INT) RETURNS TABLE(SomeValue REAL) AS
$BODY$
BEGIN
    RETURN
QUERY SELECT MyData.SomeValue FROM MyData
                
WHERE ID = p_ID;
END;
$BODY$
LANGUAGE plpgsql
;

CREATE OR REPLACE FUNCTION GetSomeValueById_Robust(p_ID MyData.ID%TYPE) RETURNS TABLE(SomeValue MyData.SomeValue%TYPE) AS
$BODY$
BEGIN
    RETURN
QUERY SELECT MyData.SomeValue FROM MyData
                
WHERE ID = p_ID;
END;
$BODY$
LANGUAGE plpgsql
;

Let us change the table structure, as follows:


ALTER TABLE MyData ALTER COLUMN ID Type NUMERIC(10,2);
ALTER TABLE MyData ALTER COLUMN SomeValue Type FLOAT;

INSERT INTO MyData(ID, SomeValue) VALUES(1.34, 1.234567890101112);

All we need to do to get the second function working is drop and create it:

DROP FUNCTION GetSomeValueById_Robust(MyData.ID%TYPE);
The first function needs more work - we have to explicitly change all the hard-coded types:
DROP FUNCTION GetSomeValueById(p_ID INT);

CREATE OR REPLACE FUNCTION GetSomeValueById(p_ID NUMERIC) RETURNS TABLE(SomeValue FLOAT) AS
$BODY$
BEGIN
    RETURN
QUERY SELECT MyData.SomeValue FROM MyData
                
WHERE ID = p_ID;
END;
$BODY$
LANGUAGE plpgsql
;
As we have seen, copying types reduces the need for tedious error-prone busywork after changes to table structure.
Using VARCHAR without explicitly specifying the size
Theoretically, we might want to specify the length of VARCHAR columns, and use that length as some kind of CHECK constraint. 
However, in practice I usually do not do it - typically this approach is not good enough, for two reasons.

First reason: usually we do not want silent truncation of values that are too long, as shown in the following example:
DECLARE @ConfirmationCode VARCHAR(6);
SET @ConfirmationCode = '1234567';
SELECT @ConfirmationCode AS ConfirmationCode;

ConfirmationCode
----------------
123456
Usually we want the code to blow up rather than silently truncate a wrong value. CHECK constraints do exactly what we want: blow up. 
This is why we typically prefer to use CHECK constraints to validate the length of a VARCHAR value.
Second reason: changing column length may require changes of child tables and parameters. 
Should we have to change the length of ConfirmationCode in a parent table, for example, we will have modify all the child tables that
refer to ConfirmationCode.
This process is slow and expensive.
Similarly, we may have to comb through all our code, modifying all the occurrencies of the parameter @ConfirmationCode VARCHAR(6). 
This is error-prone and inefficient.
This is why in our SQL Server development we typically declare our VARCHAR columns wider than necessary, 
and enforce the length limit via a CHECK constraint.

In other words, in my practice the need to specify the length of VARCHAR columns is an inconvenience.
In PostgreSql, this inconvenience is eliminated we are recommended not to specify the length of VARCHAR columns at all. 
This carries no performance penalty, as discussed here
http://people.planetpostgresql.org/dfetter/index.php?/archives/24-VARCHARn-Considered-Harmful.html
 
and here:
http://www.postgresql.org/docs/9.3/static/datatype-character.html 
Quoting from the last link: "While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead." 

 

 

Published Tuesday, November 12, 2013 11:56 AM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

tobi said:

The SQL Server devs should take a look at other RDBMS'es from time to time. The varchar-feature seems very easy to implement, for example.

November 13, 2013 9:35 AM
 

Alexander Kuznetsov said:

Tobi,

I am with you - it makes sense to reach out of our comfort zone and see what others are up to. I am not sure whether "The varchar-feature seems very easy to implement" - that really depends on the quality of their source code. If their source code is already brittle, changing it might be just too difficult. Because their source code is closed, we have no way of knowing that. What do you think?

November 13, 2013 4:19 PM
 

Greg Low said:

Yep, we've had a connect item up about being able to declare a variable based on the type of a column, for ages. Sadly in a recent discussion with the product team, I was asked "but why would you want that?". Unfortunately, development on improving T-SQL coding seems to have stopped. SQL 2014 has no T-SQL improvements. Wish it wasn't so.

November 14, 2013 12:23 AM
 

Alexander Kuznetsov said:

Greg,

I am with you - I also feel that "development on improving T-SQL coding seems to have stopped". Regarding "but why would you want that?" - I agree, it is sad when developers do not understand how their product is actually used, although not uncommon.

November 15, 2013 12:33 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement