March 3, 2012 at 5:56 pm
I'm looking at adding a table, one of the 'common sense' names for a column in that table is 'Description', however its my understanding that 'Description' is a keyword. Would there be any issue in using it as a column name? I could further qualify the column name, but would prefer not to for style purposes.
March 4, 2012 at 12:12 am
Hello,
see the defference..
declare @tbl TABLE(create smalldatetime,view int,declare decimal(20,4))
--it will rise error
go
declare @tbl TABLE([create] smalldatetime,[view] int,[declare] decimal(20,4))
-- this wont
March 4, 2012 at 11:26 am
abhilasht (3/4/2012)
Hello,see the defference..
declare @tbl TABLE(create smalldatetime,view int,declare decimal(20,4))--it will rise error
go
declare @tbl TABLE([create] smalldatetime,[view] int,[declare] decimal(20,4))
-- this wont
The OP did say that he preferred to not further qualify the column name which also means that he already knows about such qualifiers. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2012 at 11:35 am
david.holley (3/3/2012)
I'm looking at adding a table, one of the 'common sense' names for a column in that table is 'Description', however its my understanding that 'Description' is a keyword. Would there be any issue in using it as a column name? I could further qualify the column name, but would prefer not to for style purposes.
Reserved words are real funny. Some will allow you to use them as aliases and column names, some won't, and many fall into the "It Depends" world of "how and where are you using it".
'Description' is one of those names where you'll not have a problem most of the time in T-SQL. The problem is that the next hot-fix, CU, or SP could change that without warning.
My first recommendation, of course, would be to not use such reserved words. My second recommendation is that if such a reserved word makes the most sense for the column name, then make sure you always qualify it with brackets to "bullet proof" your code for future possible changes.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2012 at 10:04 am
When I said qualify, I was referring to qualifying the name of the column - TemplateDescription instead of Description within the table Templates. Technically 'Template' is not neccessary before 'Description' as its obvious, however with Description being a keyword 'Template' would qualify it. I was aware of qualifying the name in a T-SQL statement, but the idea is to make it as idiot proof as possible and avoid future issues due to changes from the Gods of Microsoft.
March 5, 2012 at 1:35 pm
If you are afraid of creating a table named CAVEAT and in any point in the future it begins to be a T-SQL reserved word you can prefix your objects names.
Its a good guess MS ill not create TB_CAVEAT, SP_CAVEAT, UF_CAVEAT, IX_CAVEAT or even ETC_CAVEAT as reserved words.
But no system or language is idiot proof...:-P
March 5, 2012 at 3:15 pm
I like to describe my descriptions 🙂 (Sorry, I couldn't figure out a better way.) So, in a Product table I may use a column name of prodDesc, product_description, prod_description, or prodDesc. It also helps when writing a query that goes out to multiple tables where you are returning multiple descriptions. Much easier to understand SELECT a.prod_description, b.sale_description, c.idontknowhwhat_description versus having to look down at the tables in the from clause to determine it, like SELECT a.description, b.description, c.description
FROM product a
INNER JOIN sales b
ON a.product_id = b.product_id
INNER JOIN idontknowwhat c
ON a.something = c.something
See!
Jared
CE - Microsoft
March 5, 2012 at 4:32 pm
Typically if I'm working with multiple columns with the same name, its usually in a view at which point I alias them as in 'Name' columns become 'BranchName', 'DepartmentName', 'FacilityName'. Since I always use 'Id' as the name of the primary key, the corresponding Foreign Keys are named 'BranchId', 'DepartmentId', etc to denote the related table. Of course, now that I think about it, I would use Id_Branch, Id_Department, etc. As a rule, I don't like underscores
March 5, 2012 at 4:50 pm
It seems names like Id, Description, Name, etc. are typical in object-oriented programming. However they should be avoided in the database coding. Ideally every column (attribute in the logical design) should have a unique name: BranchId, BranchDescription, BranchName. Typing is not the most time consuming work in programming while readability is essential. Also, if your identifier has a color in SSMS different from the expected one, change its name.
March 5, 2012 at 7:11 pm
I used follow that logic as in TrailerActivityHeaderId, TrailerActivityHeaderTrailerNumber in a table named 'TrailerActivityHeaders' and then started omitting the prefix on the grounds that it was neccessary given the table name. However, when I'm working with a VIEW or a JOIN I'll alias the column with the table name as a prefix.
March 6, 2012 at 4:10 am
Prefix is good to diff between views and tables.
Adding the table name to the columns is a bit redundant.
Except for the FKs (or if you like single char aliases in big queries).
select product.productName, sum(sales.salesTotal) as salesTotal
from product
join sales on sales.productId = product.productId
etc
March 6, 2012 at 6:22 am
SQLKnowItAll (3/5/2012)
I like to describe my descriptions 🙂 (Sorry, I couldn't figure out a better way.) So, in a Product table I may use a column name of prodDesc, product_description, prod_description, or prodDesc. It also helps when writing a query that goes out to multiple tables where you are returning multiple descriptions. Much easier to understandSELECT a.prod_description, b.sale_description, c.idontknowhwhat_descriptionversus having to look down at the tables in the from clause to determine it, likeSELECT a.description, b.description, c.descriptionFROM product a
INNER JOIN sales b
ON a.product_id = b.product_id
INNER JOIN idontknowwhat c
ON a.something = c.something
See!
I typically use much more descriptive table aliases than your example and, because of that, normally don't need to "describe the description" for the stuff that I write personally but I absolutely agree especially when writing table definitions where I'm not the only person who may be writing the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 6:36 am
My DB is a backend for an ASP.NET app as such being descriptive and using easy to remember column names comes in handy when working on the markup or the codebehind. Its very seldom that I have to look back at the underlying Select statement on the .NET side when coding.
March 6, 2012 at 7:50 am
Jeff Moden (3/6/2012)
SQLKnowItAll (3/5/2012)
I like to describe my descriptions 🙂 (Sorry, I couldn't figure out a better way.) So, in a Product table I may use a column name of prodDesc, product_description, prod_description, or prodDesc. It also helps when writing a query that goes out to multiple tables where you are returning multiple descriptions. Much easier to understandSELECT a.prod_description, b.sale_description, c.idontknowhwhat_descriptionversus having to look down at the tables in the from clause to determine it, likeSELECT a.description, b.description, c.descriptionFROM product a
INNER JOIN sales b
ON a.product_id = b.product_id
INNER JOIN idontknowwhat c
ON a.something = c.something
See!
I typically use much more descriptive table aliases than your example and, because of that, normally don't need to "describe the description" for the stuff that I write personally but I absolutely agree especially when writing table definitions where I'm not the only person who may be writing the code.
🙂 Well, I do try and stay away from a, b, c when I am not giving examples. I thought it made it more poignant lol However, I do try and keep my aliases as short as possible which may be a bad thing. For example, Product may become p, ProductCodes pc, Accounts a, Customers c, etc. In a database with a relatively small amount of tables this is probably ok, but in a more complex design I could see a lot of confusion for someone sitting down and looking at it for the first time. Good thoughts as always Jeff!
Jared
CE - Microsoft
March 6, 2012 at 8:26 am
"The true cost of software development is not when the software is originally created, but when it has to be modified, regardless if its the original developers making the changes or (gasp) developers not familar with the code."
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply