April 28, 2016 at 12:32 pm
Is there any demonstrable problem with naming a column "number"? Other than causing confusion in people. Will it confuse SQL Server in any way? I'd love to be able to make a case that this should be changed. It is currently
CREATE TABLE LovelyTable(
number varchar(50)
)
--------------------------------------
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
April 28, 2016 at 12:41 pm
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.
-- Itzik Ben-Gan 2001
April 28, 2016 at 12:43 pm
I've never had a technical issue with it, but it's also not something that I would encourage. I try to avoid using reserved words as columns/tables/general naming whenever possible.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
April 28, 2016 at 12:44 pm
Yeah, but this is an existing column that I want to rename and they're going to want a performance reason
--------------------------------------
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
April 28, 2016 at 12:48 pm
Sioban I'm with you here!
I try like crazy to make everyone replace reserved words, as well as fixing misspelled columns i inherited.
I have a column ProvdierType that makes me crazy, i keep pushing for a code review to allow us to fix it.
I have a nightmare example i like to use that exemplifies keywords over object and column names.
--http://www.sqlservercentral.com/Forums/Topic1399375-392-1.aspx
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name ='SELECT')
CREATE DATABASE [SELECT];
GO
USE [SELECT];
GO
CREATE SCHEMA "SELECT";
GO
CREATE TABLE [SELECT].[SELECT]([SELECT] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
"[SELECT]" VARCHAR(10) DEFAULT 'SELECT');
INSERT INTO [SELECT].[SELECT] DEFAULT VALUES ;
SELECT
[SELECT].[SELECT] --TableName.ColumnName
"SELECT", --Alias
[SELECT]."[SELECT]" --TableName.ColumnName
"SELECT" --Alias
FROM [SELECT].[SELECT].[SELECT] ---DatabaseName.SchemaName.TableName
[SELECT]--Alias
WHERE "[SELECT]" --ColumnName
= 'SELECT'; --Static String
Lowell
April 28, 2016 at 12:50 pm
Sioban Krzywicki (4/28/2016)
Yeah, but this is an existing column that I want to rename and they're going to want a performance reason
There's no performance benefit to using/not using reserved words - it's just bad code. The performance benefit of correctly named columns is that developers who need to read and debug it are more productive. Industry best practices are about more than just performance; either they want to do it right or they don't.
-- Itzik Ben-Gan 2001
April 28, 2016 at 12:58 pm
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.
--------------------------------------
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
April 28, 2016 at 1:04 pm
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.
awesome. just adding that table in my Sandbox database made a lot of my developer built snippets not work as expected; thank you for a great example.
Lowell
April 28, 2016 at 1:09 pm
It does depend on the reserve word and the behavior within SQL Server. So, while today, any given word might not cause a problem, tomorrow...
I would avoid as best I could since you have no control over what Microsoft does. If you let that kind of stuff through and then suddenly the whole system goes down during a security update... YIKES.
"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
April 28, 2016 at 1:13 pm
The problem is the system is littered with this kind of bad naming. It's an established and large database. They've got columns like
number
version
start_date
and many more. They won't approve renaming unless there's a demonstrable need.
Oh well. Thanks!
Grant Fritchey (4/28/2016)
It does depend on the reserve word and the behavior within SQL Server. So, while today, any given word might not cause a problem, tomorrow...I would avoid as best I could since you have no control over what Microsoft does. If you let that kind of stuff through and then suddenly the whole system goes down during a security update... YIKES.
--------------------------------------
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
April 28, 2016 at 1:19 pm
I can't imagine any problem with a column named "number", since number is not a reserved word in SQL Server anyway.
I must admit, I don't generally worry too much about reserved words. I'm much more focused on naming the column to best describe the business data it represents, or to use an industry-standard term for that data if applicable. If that happens to also be a reserved word, so be it: I might change it, I might not, but not if it's the industry's standard term for that piece of data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 28, 2016 at 11:45 pm
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]
_____________
Code for TallyGenerator
April 29, 2016 at 8:25 am
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]
April 29, 2016 at 8:44 am
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
April 29, 2016 at 8:50 am
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 ([highlight="#ffff11"]N[/highlight]'+-¦', [highlight="#ffff11"]N[/highlight]'???');
SELECT * FROM [+--]
Return the wrong data :
[font="Courier New"]
+-¦???
+-¦???
[/font]
it;s the implicit conversion of varchar that breaks the data. gotta add the N'{whatever}' to make sure it's nvarchar.
Lowell
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply