Using reserved words as column names

Question

Post reply

Using reserved words as column names

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

j-1064772 SSCertifiable Points: 5316 More actions · Answer 1

...gotta add the N'{whatever}' to make sure it's nvarchar.

Ach Soooo ! That's a new one for me. The only time I was using the "N thing" was in something like this:

IF NOT (OBJECT_ID (N'[DimCountriesList]', N'U') IS NULL)

DROP TABLE [DimCountriesList];

Thanks Lovell.

News flash - workplace violence: a dba pulled the trigger on the table designer...

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

John Mitchell-245523 (4/29/2016)
Sergiy (4/28/2016)
There is nothing wrong with objects or columns named Number, Version, Join, etc.
The names don't affect performance or quality of the code anyway.
It's not a bad coding practice either.
The real bad practice of coding is not to use brackets for identifiers.
If you ask SSMS to script a table for you, it will put every identifier into square brackets.
Take the lead and make this practice into your habit.
Then any sequence of characters won't be a problem for your code.
Even this one, which I used recently for a report destined to Excel:
[Total
Amount
Per Account]
I'm the opposite. While I agree that it makes no difference to performance, square brackets look so ugly to me that I avoid them where possible - this means not using reserved words or special characters in object or column names. Code becomes more readable and less bloated. Of course, that's just my preference. You pay your money, you make your choice.
John

I agree, can't stand brackets. To the point that when I have SSMS script something out, I remove them.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

j-1064772 (4/29/2016)
Sioban Krzywicki (4/28/2016)
I keep trying to convince people that the most secure database would be one where they can't use alphanumeric table and column names. I created one that's all pipes like
CREATE TABLE +-- (
+-¦ int,
??? varchar(100)
)
I think it'd be better than reserved words.
This has got to be the rottenest, toothenest of them all.:alien:
Now why does the following:
CREATE TABLE [+--] (
[+-¦] nvarchar(20),
[???] nvarchar(100)
);
INSERT INTO [+--] ([+-¦], [???]) VALUES ('+-¦', '???');
SELECT * FROM [+--]
Return the wrong data :
[font="Courier New"]
+-¦???
+-¦???
[/font]

Probably implicit conversions. If you want to be able to enter that kind of data, you have to declare both columns as nvarchar. Currently one is an int and the other is a varchar

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Sioban Krzywicki (4/29/2016)
John Mitchell-245523 (4/29/2016)
Sergiy (4/28/2016)
There is nothing wrong with objects or columns named Number, Version, Join, etc.
The names don't affect performance or quality of the code anyway.
It's not a bad coding practice either.
The real bad practice of coding is not to use brackets for identifiers.
If you ask SSMS to script a table for you, it will put every identifier into square brackets.
Take the lead and make this practice into your habit.
Then any sequence of characters won't be a problem for your code.
Even this one, which I used recently for a report destined to Excel:
[Total
Amount
Per Account]
I'm the opposite. While I agree that it makes no difference to performance, square brackets look so ugly to me that I avoid them where possible - this means not using reserved words or special characters in object or column names. Code becomes more readable and less bloated. Of course, that's just my preference. You pay your money, you make your choice.
John
I agree, can't stand brackets. To the point that when I have SSMS script something out, I remove them.

Yeah, same here. Brackets are heinous. That's another great thing about SQL Prompt. You can remove all the brackets in an automated fashion. You can add them all back in too (blech).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 5

Grant Fritchey (4/29/2016)
Sioban Krzywicki (4/29/2016)
John Mitchell-245523 (4/29/2016)
Sergiy (4/28/2016)
There is nothing wrong with objects or columns named Number, Version, Join, etc.
The names don't affect performance or quality of the code anyway.
It's not a bad coding practice either.
The real bad practice of coding is not to use brackets for identifiers.
If you ask SSMS to script a table for you, it will put every identifier into square brackets.
Take the lead and make this practice into your habit.
Then any sequence of characters won't be a problem for your code.
Even this one, which I used recently for a report destined to Excel:
[Total
Amount
Per Account]
I'm the opposite. While I agree that it makes no difference to performance, square brackets look so ugly to me that I avoid them where possible - this means not using reserved words or special characters in object or column names. Code becomes more readable and less bloated. Of course, that's just my preference. You pay your money, you make your choice.
John
I agree, can't stand brackets. To the point that when I have SSMS script something out, I remove them.
Yeah, same here. Brackets are heinous. That's another great thing about SQL Prompt. You can remove all the brackets in an automated fashion. You can add them all back in too (blech).

I'm looking forward to when they get me a license for SQL Prompt. Supposed to be soon.

In the meantime I just do my usual ctrl+H Search for [ and replace with

then repeat for ]

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 6

I agree, can't stand brackets. To the point that when I have SSMS script something out, I remove them.
Yeah, same here. Brackets are heinous. That's another great thing about SQL Prompt. You can remove all the brackets in an automated fashion. You can add them all back in too (blech).

I'm glad I'm not the only one.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Sioban Krzywicki (4/29/2016)
John Mitchell-245523 (4/29/2016)
Sergiy (4/28/2016)
There is nothing wrong with objects or columns named Number, Version, Join, etc.
The names don't affect performance or quality of the code anyway.
It's not a bad coding practice either.
The real bad practice of coding is not to use brackets for identifiers.
If you ask SSMS to script a table for you, it will put every identifier into square brackets.
Take the lead and make this practice into your habit.
Then any sequence of characters won't be a problem for your code.
Even this one, which I used recently for a report destined to Excel:
[Total
Amount
Per Account]
I'm the opposite. While I agree that it makes no difference to performance, square brackets look so ugly to me that I avoid them where possible - this means not using reserved words or special characters in object or column names. Code becomes more readable and less bloated. Of course, that's just my preference. You pay your money, you make your choice.
John
I agree, can't stand brackets. To the point that when I have SSMS script something out, I remove them.

There are times when they come in very handy but, for the most part, totally agree from a visual aspect. And, yeah... I'll usually remove the brackets that MS generates for production code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

If you really want, you can also model an entire database containing objects with only spaces for names.

create database [ ];

GO

use [ ];

GO

create schema [ ];

GO

create table [ ].[ ]

(

[ ] int

);

GO

create proc [ ] as

select * from [ ].[ ];

GO

:crazy:

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 9

Eric M Russell (4/29/2016)
If you really want, you can also model an entire database containing objects with only spaces for names.
create database [ ];
GO
use [ ];
GO
create schema [ ];
GO
create table [ ].[ ]
(
[ ] int
);
GO
create proc [ ] as
select * from [ ].[ ];
GO
:crazy:

Nice! I'll have to try this sometime.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

j-1064772 SSCertifiable Points: 5316 More actions · Answer 10

Can't help myself.

I am calling out every one to push this even further down the road.

Have we reached bottom yet ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Alan.B (4/28/2016)
Something like this would not work:
CREATE TABLE LovelyTable(
Join varchar(50)
)
SQL Server is generally very good at dealing with reserved keywords as column names but its a terrible practice. Column names like "Date" fails to describe your column correctly and leads to code that's more difficult to read, debug, improve, etc.

But this will work:

CREATE TABLE LovelyTable(

[Join] varchar(50)

);

Sergiy SSC Guru Points: 110208 More actions · Answer 12

j-1064772 (4/29/2016)
Can't help myself.
I am calling out every one to push this even further down the road.
Have we reached bottom yet ?

Like some people brackets, I hate Latin alphabet.

It's not my native, it's not what I've learnt from the childhood.

I guess, I should be starting work with any code with replacing Latin symbols with whatever else.

🙂

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Sergiy (4/29/2016)
j-1064772 (4/29/2016)
Can't help myself.
I am calling out every one to push this even further down the road.
Have we reached bottom yet ?
Like some people brackets, I hate Latin alphabet.
It's not my native, it's not what I've learnt from the childhood.
I guess, I should be starting work with any code with replacing Latin symbols with whatever else.
🙂

Heh... might be better than some of the code I've been reviewing lately.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 14

Lynn Pettis (4/29/2016)
Alan.B (4/28/2016)
Something like this would not work:
CREATE TABLE LovelyTable(
Join varchar(50)
)
SQL Server is generally very good at dealing with reserved keywords as column names but its a terrible practice. Column names like "Date" fails to describe your column correctly and leads to code that's more difficult to read, debug, improve, etc.
But this will work:
CREATE TABLE LovelyTable(
[Join] varchar(50)
);

This thread has gotten so out of hand.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sergiy SSC Guru Points: 110208 More actions · Answer 15

Alan.B (4/29/2016)
This thread has gotten so out of hand.

Well, it was not in good hands from the very beginning.

Is there any demonstrable problem with naming a column "number"?

I think the problem has been perfectly demonstrated.

It's with personal preferences of each particular coder.

No other problems found.

Use of square brackets as identifier's delimiters allow to use any chain of characters as objects and columns names.

Some people hate to use them - but it's not SQL Server's problem.

Actually, not using square brackets creates a bit of an additional work for the compiler.

Because lack of square brackets forces it to check every identifier it meets in a query against the list of keywords, other language constructions, while opening square bracket clearly tells it - everything until the corresponding closing bracket is an identifier and nothing else.

_____________
Code for TallyGenerator