Prev: C# and SQL Server: Parallel vs. Sequential Writes (?)
Next: Transfer file from Access to Server
From: --CELKO-- on 23 Feb 2010 09:34
>> What we're dealing with is complicated and any standard we can use to keep things clearer and simpler can only work in our favor.<<

Get a copy of my SQL PROGRAMMING STYLE and use the conventions in it.
They are based on the ISO-11179 Standards and the research we
collected at AIRMICS. The other advantage is if some programmers do
not like it, you can blame that Damn Joe Celko :)

The quoted names have another disadvantage; they cannot be used
outside of SQL Server when they are reserved words in SQL and/or host
languages.

Here is the reserved word list from ANSI SQL:2006

<reserved word> ::=
ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS |
ASENSITIVE
| ASYMMETRIC | AT | ATOMIC | AUTHORIZATION | AVG

| BEGIN | BETWEEN | BIGINT | BINARY | BLOB | BOOLEAN | BOTH | BY

| CALL | CALLED | CARDINALITY | CASCADED | CASE | CAST | CEIL |
CEILING
| CHAR | CHAR_LENGTH | CHARACTER | CHARACTER_LENGTH | CHECK | CLOB |
CLOSE
| COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION |
CONNECT
| CONSTRAINT | CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP |
COVAR_SAMP
| CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_CATALOG |
CURRENT_DATE
| CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE |
CURRENT_SCHEMA
| CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE
| CURRENT_USER | CURSOR | CYCLE

| DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT |
DELETE
| DENSE_RANK | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT |
DISTINCT
| DOUBLE | DROP | DYNAMIC

| EACH | ELEMENT | ELSE | END | END-EXEC | ESCAPE | EVERY | EXCEPT |
EXEC
| EXECUTE | EXISTS | EXP | EXTERNAL | EXTRACT

| FALSE | FETCH | FILTER | FLOAT | FLOOR | FOR | FOREIGN | FREE |
FROM
| FULL | FUNCTION | FUSION

| GET | GLOBAL | GRANT | GROUP | GROUPING

| HAVING | HOLD | HOUR

| IDENTITY | IN | INDICATOR | INNER | INOUT | INSENSITIVE | INSERT
| INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS

| JOIN

| LANGUAGE | LARGE | LATERAL | LEADING | LEFT | LIKE | LIKE_REGEX
| LN | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOWER

| MATCH | MAX | MEMBER | MERGE | METHOD | MIN | MINUTE
| MOD | MODIFIES | MODULE | MONTH | MULTISET

| NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NO | NONE | NORMALIZE |
NOT
| NULL | NULLIF | NUMERIC

| OCTET_LENGTH | OCCURRENCES_REGEX | OF | OLD | ON | ONLY | OPEN | OR
| ORDER | OUT | OUTER | OVER | OVERLAPS | OVERLAY

| PARAMETER | PARTITION | PERCENT_RANK | PERCENTILE_CONT |
PERCENTILE_DISC
| POSITION | POSITION_REGEX | POWER | PRECISION | PREPARE | PRIMARY
| PROCEDURE

| RANGE | RANK | READS | REAL | RECURSIVE | REF | REFERENCES |
REFERENCING
| REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 |
REGR_SLOPE
| REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RESULT | RETURN |
RETURNS
| REVOKE | RIGHT | ROLLBACK | ROLLUP | ROW | ROW_NUMBER | ROWS

| SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SELECT | SENSITIVE
| SESSION_USER | SET | SIMILAR | SMALLINT | SOME | SPECIFIC |
SPECIFICTYPE
| SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START | STATIC
| STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSTRING | SUBSTRING_REGEX
| SUM
| SYMMETRIC | SYSTEM | SYSTEM_USER

| TABLE | TABLESAMPLE | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR |
TIMEZONE_MINUTE
| TO | TRAILING | TRANSLATE | TRANSLATE_REGEX | TRANSLATION | TREAT
| TRIGGER | TRIM | TRUE

| UESCAPE | UNION | UNIQUE | UNKNOWN | UNNEST | UPDATE | UPPER | USER
| USING

| VALUE | VALUES | VAR_POP | VAR_SAMP | VARBINARY | VARCHAR | VARYING

| WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN |
WITHOUT

| YEAR

From: Tibor Karaszi on 23 Feb 2010 10:38
> So really the only problem is delimiters which isn't really that big a
> problem. So surely using reserved words is pretty much a non isue.

I'm sorry, but I strongly disagree with both above statements.


:-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Michael C" <mike(a)nospam.com> wrote in message
news:uEKLkvBtKHA.5940(a)TK2MSFTNGP02.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote
> in message news:%23EPSId4sKHA.6004(a)TK2MSFTNGP04.phx.gbl...
>>I definitely shy away from delimited identifiers. The problem with them
>>are the ... delimiters. For some historically stupid reason, the SQL
>>Server world has decided to go for square brackets instead of double
>>quotes (which is ANSI SQL). This is in script generation, examples in BOL,
>>courses etc. Now, having double-quotes doesn't really solves the real
>>problem, but I just wanted to let some steam off regarding the choice of
>>identifiers. The real problem to me is ... readability. My brain if far
>>far slower parsing SQL which is delimited compared to non-delimited SQL.
>
> So really the only problem is delimiters which isn't really that big a
> problem. So surely using reserved words is pretty much a non isue.
>
> Although what I would consider more of an issue is readability, if you
> call a column Date then code just becomes a little less readable and could
> be potentially confusing is some situations.
>
> Michael
>
From: Jay on 23 Feb 2010 13:09
+1

"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:uttzI4JtKHA.3360(a)TK2MSFTNGP06.phx.gbl...
>> So really the only problem is delimiters which isn't really that big a
>> problem. So surely using reserved words is pretty much a non isue.
>
> I'm sorry, but I strongly disagree with both above statements.
>
>
> :-)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "Michael C" <mike(a)nospam.com> wrote in message
> news:uEKLkvBtKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote
>> in message news:%23EPSId4sKHA.6004(a)TK2MSFTNGP04.phx.gbl...
>>>I definitely shy away from delimited identifiers. The problem with them
>>>are the ... delimiters. For some historically stupid reason, the SQL
>>>Server world has decided to go for square brackets instead of double
>>>quotes (which is ANSI SQL). This is in script generation, examples in
>>>BOL, courses etc. Now, having double-quotes doesn't really solves the
>>>real problem, but I just wanted to let some steam off regarding the
>>>choice of identifiers. The real problem to me is ... readability. My
>>>brain if far far slower parsing SQL which is delimited compared to
>>>non-delimited SQL.
>>
>> So really the only problem is delimiters which isn't really that big a
>> problem. So surely using reserved words is pretty much a non isue.
>>
>> Although what I would consider more of an issue is readability, if you
>> call a column Date then code just becomes a little less readable and
>> could be potentially confusing is some situations.
>>
>> Michael
>>


From: --CELKO-- on 23 Feb 2010 14:44
You missed the point. A data element should have the same name in ALL
databases, in ALL applications, ALL documentation, etc. There is more
to the enterprise than just an SQL schema :)

The ISO 11179 Standard gives you guidelines that boil down to "name a
thing for what it is" and "be precise" in 25 words or less.

And this kind of thing really matters. The DoD research found that
good formatting and naming standards could save 8-12% of the cost of
maintaining software systems. Since maintaining a system is 80% of the
lifetime cost, this matters.



From: Gert-Jan Strik on 23 Feb 2010 15:26
Michael C wrote:
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
> message news:%23EPSId4sKHA.6004(a)TK2MSFTNGP04.phx.gbl...
> >I definitely shy away from delimited identifiers. The problem with them are
> >the ... delimiters. For some historically stupid reason, the SQL Server
> >world has decided to go for square brackets instead of double quotes (which
> >is ANSI SQL). This is in script generation, examples in BOL, courses etc.
> >Now, having double-quotes doesn't really solves the real problem, but I
> >just wanted to let some steam off regarding the choice of identifiers. The
> >real problem to me is ... readability. My brain if far far slower parsing
> >SQL which is delimited compared to non-delimited SQL.
>
> So really the only problem is delimiters which isn't really that big a
> problem. So surely using reserved words is pretty much a non isue.
>
> Although what I would consider more of an issue is readability, if you call
> a column Date then code just becomes a little less readable and could be
> potentially confusing is some situations.
>
> Michael

So you wouldn't be worried or confused if you read something like this:

SELECT "GROUP" 'Group'
FROM "GROUP"
WHERE "GROUP".."GROUP" > 100
GROUP BY "GROUP"
ORDER BY 'Group'

I most certainly agree with Tibor. The biggest problem with queries is
that users / developers don't understand them or misread them. The
parser will have no problem whatsoever. I think any reasonable thing you
can do to avoid confusion and make the query more readable should be
taken seriously. And using reserved words is exactly the opposite.

--
Gert-Jan
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: C# and SQL Server: Parallel vs. Sequential Writes (?)
Next: Transfer file from Access to Server