Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: SQL Humor

Re: SQL Humor

From: Tibor Karaszi <tibor_please.no.email_karaszi_at_hotmail.nomail.com>
Date: Fri, 19 Aug 2005 09:43:51 +0200
Message-ID: <eglr#GJpFHA.3256@TK2MSFTNGP12.phx.gbl>


> , so that you should put the char() fields to the front of
> the record and the varchar()'s to the end.

SQL Server does this for you :-). If you study the row layout in SLQ server, you have all the fixed columns, in order, in the beginning of the row. Then comes some fluff like a null block etc. Then you have a variable column offset table, which has pointers to the last section of the row, the actual variable column data block. In the variable column offset block, you first have a 2 byte section describing at which position the first variable column's data ends, etc.

One can argue that this also has performance impact (handling the variable length stuff compared to the simple fixed length algorithms). But as you know, we rarely measure CPU cycles in SQL Server (like you probably do in a C compiler).

I'm surprised why this discussion haven't focused on the data? If it is fixed length data, use char! Like SSN (I would imagine the counterpart in Sweden is fixed length). If it isn't fixed length (firstname, lastname), use varchar.

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"jxstern" <jxstern_at_nowhere.xyz> wrote in message news:em0ag1p2v0mcc3726f5pbleoevcmfbf3qb_at_4ax.com...

> On 18 Aug 2005 11:23:34 -0700, "Razvan Socol" <rsocol_at_gmail.com>
> wrote:
>>> there is not a single advantage of char over varchar2 >> >>"varchar2" ? You seem to live in an Oracle world, but here we are MS >>SQL Server people. And in SQL Server there is an advantage for char >>over varchar: char(n) has takes two bytes less to store than >>varchar(n), if the string has always n characters. >
> I've had my ear bent about some more putative performance advantages
> to char() fields, in that SQLServer can find the n'th data field more
> efficiently, so that you should put the char() fields to the front of
> the record and the varchar()'s to the end.
>
> No idea if it's true or significant. Hope it's not too significant,
> since I've never actually paid any attention to it!
>
> J.
>
Received on Fri Aug 19 2005 - 02:43:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US