December 21, 2008 at 10:51 pm
Hi,
Whenever i type the words NAME, TYPE, STATUS in the query window it automatically turns blue. Like: select UID,NAME,TYPE,STATUS,Department FROM My_Table. In this query, except the column Department everythign truns blue. Do anyone know the reason.
Please Do Reply Me Your Answers. Thanks.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
December 22, 2008 at 12:44 am
They are SQL reserved words. Hence management studio highlights them as such.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 5:30 am
And best practices dictate that you don't use them in user defined tables. Nothing prevents you from doing it, but it's generally considered poor design and can, in some cases, lead to problems.
"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
December 23, 2008 at 7:19 am
GilaMonster (12/22/2008)
They are SQL reserved words. Hence management studio highlights them as such.
Not exactly. Since when is UID a reserved word? Search for UID in BOL and see what you get. It is the name of a column in some of the system tables and an abbreviation for "User ID" in connection strings.
They ARE NOT SQL Server 2005 reserved keywords. Reserved keywords: http://technet.microsoft.com/en-us/library/ms189822(SQL.90).aspx If they were SQL reserved words they would not be able to be used in the OP's query unless they were quoted.
They are various keywords that have been subjectively chosen by Microsoft and placed into a list within the query editor syntax coloring file.
I've been using NAME, TYPE, STATUS as column names for decades in my relational databases. But one should always check for reserved words and not use them as object names. However, there is no guarantee that any database vendor will not use some term in the future as a "reserved keyword".
December 23, 2008 at 8:46 am
Enclose them in square brackets and SQL syntax won't format them blue.
December 23, 2008 at 9:00 am
MrsPost (12/23/2008)
Enclose them in square brackets and SQL syntax won't format them blue.
Or...
1. Just ignore the coloring
2. Change the coloring to be all the same color. Under Tools / Options / Environment / Fonts and Colors
December 23, 2008 at 9:10 am
That's weird, did it for me as well. And those shouldn't be reserved words. My guess is someone at MS thinks they are and included them in an XML file that determines what is colored as what.
December 23, 2008 at 10:10 am
Change the coloring to be all the same color.
Wut ??
Highlighting keywords is a valuable assistance provided by the system. A typo in a keyword or failing to add a space between the keyword and a follow-up name will be made immediately obvious. Incomplete strings (missing an apostrophe are also made obvious).
As for table names or column that are the same as reserved keywords. Well, I also would prefer to be advised about that. Note that over the course of time, a perfectly innocent name may become a reserved keyword, so yes, you can avoid using EXISTING keywords but unless you use a foreign language you can never be 100 % sure this will never happen.
Try to avoid prefixing columns with "col". This only serves to reduce legibility of the code and, in the specific case of a column holding an identification, labeling the column colID instead of ID does not help you - colid is also a keyword recognized by the system.
The color is there to tell you something. Turning it off it akin to burying your head in the sand to avoid seeing what you do not want to see. And are you going to force everybody else in your outfit to neutralize the coloring so that a minor issue such as a column or table name shown off as a reserved keyword remains hidden ?
December 23, 2008 at 10:34 am
J (12/23/2008)
Change the coloring to be all the same color.Wut ??
Highlighting keywords is a valuable assistance provided by the system. A typo in a keyword or failing to add a space between the keyword and a follow-up name will be made immediately obvious. Incomplete strings (missing an apostrophe are also made obvious).
As for table names or column that are the same as reserved keywords. Well, I also would prefer to be advised about that. Note that over the course of time, a perfectly innocent name may become a reserved keyword, so yes, you can avoid using EXISTING keywords but unless you use a foreign language you can never be 100 % sure this will never happen.
Try to avoid prefixing columns with "col". This only serves to reduce legibility of the code and, in the specific case of a column holding an identification, labeling the column colID instead of ID does not help you - colid is also a keyword recognized by the system.
The color is there to tell you something. Turning it off it akin to burying your head in the sand to avoid seeing what you do not want to see. And are you going to force everybody else in your outfit to neutralize the coloring so that a minor issue such as a column or table name shown off as a reserved keyword remains hidden ?
It was only a suggestion for the OP if they are bothered by the coloring.
As for me, I just ignore the erroneous "reserved word" (sic) colorization. E.g., since when is "colid" a reserved word? (BTW, "colid" is not highlighted as a keyword on my system).
I suspect that the "keyword" list includes every column in every one of the system tables. Which would be totally wrong.
As for "correctness" of the reserved words, the following works fine in SQL Server 2005:
CREATE TABLE NAME (STATUS INT, UID UNIQUEIDENTIFIER);
December 23, 2008 at 10:42 am
If a user is bothered by the color itself, he might as well use a black and white monitor... I think the OP was more concerned as to the implications of the system highlighting something.
As for reserved keywords, even thought SQL Server has no issues with it, such usage is flagged by Embarcadero ...
December 23, 2008 at 10:48 am
And you are right about colid not even being highlighted.
colid as in, say
[font="Courier New"]select SC.[name]
from syscolumns SC
inner join sysobjects SO ON SO.[id] = SC.[id]
AND SO.[name] = 'Customer'
order by sc.[colid][/font]
So even though colid means something special to the system, that one is not even flagged.
December 23, 2008 at 10:54 am
I think the OP is bothered, as am I slightly. When I see the highlighting, it has meaning, so having words marked as blue when they're not reserved doesn't make sense.
December 23, 2008 at 10:56 am
So even though colid means something special to the system, that one is not even flagged.
You stated that one shouldn't use "colid" as it is a reserved keyword. By who? Embarcadaro?
The problem with "keyword" highlighting is that the highlighting is not done in the context of the statement. So, one can create a table with a column name of "type". But the word "type" is a keyword in the context of the new SQL Server 2008 "CREATE TYPE..." statement.
Therefore, the highlighting of the "keywords" has to be taken with a "grain of salt" -- otherwise you'll never be able to write any T-SQL as virtually every thing you could use as an object name will be perceived to be invalid/incorrect. The only true test is to run the command through the end product. E.g., Oracle, SQL Server, etc.
December 23, 2008 at 10:59 am
-> JonhG
Agreed.
Embarcadero does raise an issue with using reserved keywords as column names. If you do not use Embarcadero, then no problem.
As for colid, not it is not a reserved keyword, just a column name in a system table. Which shows another limitation of highlighting, raising false alarms and ignoring minor ones.
Does not change the fact that you are right. We can only use names that make sense to su, too bad if they become reserved keywords in future versions of SQL Server -- as long as MS makes sure to never break the code base.
December 23, 2008 at 11:11 am
Now if Microsoft was really smart they would highlight the true SQL reserved words, as noted in http://technet.microsoft.com/en-us/library/ms189822(SQL.90).aspx for SQL Server 2005, in a totally different color. Let's say Blinking RED.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply