Database name with special character(Urgent)
-
i agree with Kenneth...whenever I need to use brackets I can't help but thinking "What idiot named this object something they we're not supposed to". I don't think it causes any problems using them however the only time I use them 100% of the time is when doing dynamic SQL and you don't know what kind of possible object names you may be looking referencing.
prost
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Probably y never built reports.
_____________
Code for TallyGenerator -
I hope you can point to a document which tells exactly what we're suppose to do and what we're not suppose to do with names of identifiers.
_____________
Code for TallyGenerator -
I didn't? Thought I did..
> Best practice #1:
> Always use objectnames that *don't* have to use square brackets in order to not crash your code []
If this was unclear, the intended contex with the above sentence is 'objectnames'...
(Perhaps I should have said 'identifiers', which is the, I believe, perhaps more correct term...)
Since the thread was about the troubles of referencing a database named in such a way that you needed [], I simply assumed that the context was known.. (silly me)
No, we don't want to fool newbies, I agree. (honestly)
Though I love discussions, it's easy to drift off topic, so let's back up a step and review..
Seems like our 'argument' is over what 'best' means, right?
Let me then just re-word the above then, with a small explanation of what I intend 'best practice' to mean.
'Best' is the same thing as 'preferred' or 'recommended' - in any case that's what I've always thought...
These are synonyms: 'Best practice', 'Recommended practice', 'Preferred practice'.
So, therefore my advice for 'best/recommended/preferred' practice regarding how to name objects/identifiers is: Don't use anything that relies on [] to work. I've never said that one should *not* use them, right? I've just said that it's preferrable to not depend on []. I still recommend that as 'best/recommended/preferred'
>> So, it allows you to - on purpose or unknowingly - create something that may cause problems further on.
> Can you show a case, just single case, when using brackets will create problems?
Well, ok... This example is rather convoluted, I'll admit.
But since you wanted an example, you didn't ask for a 'reasonable' example
Suppose someone thought that it would be neat to not only have a table that consisted of two words (eg 'My Table') but also that it would be nice to have two rows. ('My' + <CR> + 'Table')
This isn't exactly what I'd recommend anyone to do, but if you use [] it's perfectly possible to do that. Or a table named with just a space charachter, or a tab, delete or any other 'fun char' there is. I do believe that would cause some problems further down the line...
>> So, chances are then that it would 'fail' in the same way that a variant datatype would 'fail' if you used it to store, say numbers, and a letter 'A' should get in there.
>Very interesting.
>I use sql_variant datatype, very rarely, but I do. And I store integers, dates and strings in there.
>I never faced any problems with it. If I compare to int value it just ignores all values stored in other datatypes. No issues here.
>Do I miss anything here?
Yep, you do, Since you asked, my point was 'missed'. (or, you just like to type, like me)
I know that you know the purpose of 'typing' (as in 'datatyping'), and the point was simply an attempt to say: 'It would be like all of your columns in all tables would be of the variant datatype', which would mean that you would miss out on the 'benefits of using correct datatypes' (which is a subject all of it's own, don't you think?)
>> We could say that the business rule for objectnaming is the rules that BOL state, right?
>What kind of rules are you talking about?
These are the 'rules': (from 2005 BOL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/171291bb-f57f-4ad1-8cea-0b092d5d150c.htm
--- quote ---
The rules for the format of regular identifiers depend on the database compatibility level. This level can be set with sp_dbcmptlevel. When the compatibility level is 90, the following rules apply:
- The first character must be one of the following:
- A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
- The underscore (_), "at" sign (@), or number sign (#).
Certain symbols at the beginning of an identifier have special meaning in SQL Server. An identifier that starts with the "at" sign denotes a local variable or parameter. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object.
Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.
- Subsequent characters can include the following:
- Letters as defined in the Unicode Standard 3.2.
- Decimal numbers from either Basic Latin or other national scripts.
- The "at" sign, dollar sign ($), number sign, or underscore.
- The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words.
- Embedded spaces or special characters are not allowed.
When they are used in Transact-SQL statements, identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.
--- end quote ---
/Kenneth
- The first character must be one of the following:
-
> Best practice #1:
> Always use objectnames that *don't* have to use square brackets in order to not crash your code []
You cannot always follow this rule.
As you said: it depends.
If it depends, it's not always any more.
And there is actually no point for it.
> So, therefore my advice for 'best/recommended/preferred' practice regarding how to name objects/identifiers is: Don't use anything that relies on [] to work. I've never said that one should *not* use them, right? I've just said that it's preferrable to not depend on []. I still recommend that as 'best/recommended/preferred'
OK, let's make it clear.
There are 2 practices:
1) Don't use any identifier names which may make you use brackets.
2) Always use brackets around identifiers to avoid confusions.
What makes 1st practice better than 2nd?
> I do believe that would cause some problems further down the line...
I did not asked in what you do believe.
I asked for a real example when using brackets cause problems.
> which would mean that you would miss out on the 'benefits of using correct datatypes'
Datatypes bring some constraints, presentation and manipulation functionality. So, I can see real use of it.
But I cannot see any benefits of avoiding spaces in column names.
Can you show one?
> These are the 'rules': (from 2005 BOL)
Sorry. These are absolute crap.
The identifier must not be a Transact-SQL reserved word...
... Embedded spaces or special characters are not allowed...
... identifiers that do not comply with these rules ...
What?
They must not be, not allowed but they possibly do not comply?
So, they actually can be? And actually allowed?
"Severity of Russian laws is compensated by the fact that their enforcement is optional" - is it the case?
_____________
Code for TallyGenerator -
Have we learned nothing from the dark ages of "religious" intolerance...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
I have never seen so clearly the thinness of the line separating us from lawyers in how finely we split hairs over coding instead of points of law.
The one cogent argument I saw, that I believe is above reproach, would be the use of "[ ]" as a name. Did I use a space? a space plus back-space plus space? Some other non-display character? How about an underscore plus backspace plus space?
The rest of the debate and discussion borders on what some might think is the philosophy of databases or some other type of abstract system from which we derive artificial languages that make our jobs possible.
It's always difficult to speak coherently and accurately when working in translation - in addition, we are talking about underlying principles. We are translating principles, not enforceable rules, about an artificial language (T-SQL) in at least one natural language - all of us are reading this in English, but I suspect that at least some of us who have entered this discussion do not have English as their first language.
I am grateful that this thread has not left the 'high' ground of arguing logical points, unlike other threads I've seen. (I keep thinking of the old news skit on Saturday Night Live, where Dan Aykroyd says to Jane Curtin, 'Jane, you miserable...'), which poked fun at the kind of degeneration these discussions can fall into.
I would like to suggest that there is room for personal style within the language(s) we are using. However, we are confronting the situation at the beginning of this thread, where someone else's personal style is considered to be in very poor taste. Not impossible to work with, but very painful and unneedfully difficult.
-
> kind of degeneration these discussions can fall into.
So, please don't initiate that fall.
> Did I use a space? a space plus back-space plus space?
I hope you can explain how to save and/or run a script with space-backspace-space name.
_____________
Code for TallyGenerator -
Some other folks mentioned language, which I agree is something very important.
Especially when the language is the only media you have available to communicate with.
Add to that different language and cultural backgrounds all using a third intermediate language (english in these forums) for 'listeners' as well as 'speakers', I guess that we really do need to look beyond the literals and see the intent in the words. (or between the lines)
I assume that english isn't your native language? In any case, it's not my native language.
And I don't think that it's the op's native language either.
We're not really debating 'facts', but rather 'interpretations' of what different words and sentences 'mean', don't you agree?
> There are 2 practices:
> 1) Don't use any identifier names which may make you use brackets.
> 2) Always use brackets around identifiers to avoid confusions.
> What makes 1st practice better than 2nd?
Oh, well.. To be picky, I haven't said 1) ... (those pesky words again correlated with intent <g> )
What I've said (which is the same as 'what I've written with the intended meaning of....') is:
1) Don't
usechoose or create any identifier names whichmay makewill force you (to) use brackets.I myself, prefer 1) over 2), simply beacuse 2) isn't the expected notation unless you have somewhere explicitly stated that [] should always be used. Should someone not be aware of that, or forget it, or whatever, then if that particular name is 'unfortunate' an error would occur, and some time would be spent on figuring out that 'oh, we need [] here, then it'll work again'. In my experience, [] isn't what people in general default to use at all times, thus sticking to 1) will remove that risk.
Naturally, it's up to each and everyone to have their own opinions on which is 'better' (that subjective word again..) , and if asked, I recommend 1) over 2)
> I did not asked in what you do believe.
> I asked for a real example when using brackets cause problems.
You got an example, right?
'I do belive ....' is just a 'being polite' way of wording in english that means 'not necessarily always, or for all and everyone, but there is a possibility', it has nothing to do with faith.
We could also state the fact - 'It will cause someone somewhere problems'.
For an example, just look at the first post in this very thread.
> Datatypes bring some constraints, presentation and manipulation functionality. So, I can see real use of it.
> But I cannot see any benefits of avoiding spaces in column names.
> Can you show one?
Never mentioned avoiding spaces in columnnames in particular, spaces in general goes with the other 'illegal and thus must be delimited' chars. What I did say was if the name of a table was a single space alone. [ ]
Can you show the benefit of allowing a name like that?
> Sorry. These are absolute crap.
Oh well, you have to take that with those who wrote it.
Though since it is in fact the official text from the manufacturer of this particular forum's products, I use it as official guide of what is intended and what is not. One doesn't have to like it or approve, but not liking it doesn't make it 'not there' either.
/Kenneth
-
Sergiy - for closure, and to let you know that I did see your response:
1. It doesn't look like I have initiated that fall, but I debated whether to post exactly because it could have started the fall into a degenerated discussion.
2. I did not intend to explain how to save/run a script with such a name. I simply stretched my imagination to try and invent an extreme example - how a backspace inserted into the middle of a name could defy reproduction and thereby prevent any further use of such a name. If the string were inserted into the middle of a 'normal looking' name, how would one know? Perhaps the use of '_' + backspace + '_' would have been more effective and achieve the same result?
-
1. It's you who initiated this fall. Noone was trying turn to personal points before you.
2. Your scenario is rubbish.
If name can be created it can be used - in exactly the same way.
If name cannot be used - there is no way to create it.
Sorry for your imagination.
_____________
Code for TallyGenerator -
> Some other folks mentioned language, which I agree is something very important.
I don't see any language related problems here.
So, let's cut the crap.
> Oh, well.. To be picky, I haven't said 1)
Really?
What's the difference between meanings of "don't use" and "don't choose or create"?
> I myself, prefer
Here we are.
It's not really best practice. It's what you prefer.
You entitled to use whatever practice you prefer. But to name it "best practice" you need to have some proof it's better than other practices.
> You got an example, right?
Wrong.
Your example does not show any failure for practice 2).
> We could also state the fact - 'It will cause someone somewhere problems'.
No.
Unless that someone does not follow the rules of practice 2).
> For an example, just look at the first post in this very thread.
That post just indicates lack of SQL syntax knowledge. Nothing else.
> Can you show the benefit of allowing a name like that?
I can show benefit of using [].
Because SQL Server has explicit instruction that the text is an identifier and nothing else it saves compilation time.
Another benefit is filtering out bad SQL programmers before they've built their "fabulous" queries. If programmer is not aware of [] it's better not to let him touch databases at all. Saves a lot of time.
Can you show the benefit of NOT allowing a name like that?
_____________
Code for TallyGenerator -
Heh... I knew it
... the term "Best Practice" is a bloody Management term 
... AND, it appears that there's more than 1 definition of "Best Practice" in the following...Before I get too deep into this, though, I should tell you that I don't use the square brackets on everything... my "personal preference" is to avoid names with special characters simply because I hate typing the square brackets... I don't use underscores for the same lazy reason.
However...From Wikipedia (http://en.wikipedia.org/wiki/Best_practice)...
Best Practice is a management idea which asserts that there is a technique, method, process, activity, incentive or reward that is more effective at delivering a particular outcome than any other technique, method, process, etc. The idea is that with proper processes, checks, and testing, a desired outcome can be delivered with fewer problems and unforeseen complications. Best practices can also be defined as the most efficient (least amount of effort) and effective (best results) way of accomplishing a task, based on repeatable procedures that have proven themselves over time for large numbers of people.
Let's break that apart...
Best Practice is a management idea which asserts that there is a technique, method, process, activity, incentive or reward that is more effective at delivering a particular outcome than any other technique, method, process, etc.
Sounds to me like "It Depends" on what outcome management wants.
The idea is that with proper processes, checks, and testing, a desired outcome can be delivered with fewer problems and unforeseen complications.
Hmmm... is it easier to check and test with or without brackets? I'm thinking "doesn't matter" there except that if brackets are used, I know I don't have to check for special characters. And, is it easier to quarantee that no special characters have been used, or to just use brackets for everything? Which would cause fewer problems and unforeseen complications? ... Missing a special character or just using brackets for everything? Teaching newbies not to use special characters or teaching them that all objects must be in brackets?
Best practices can also be defined as the most efficient (least amount of effort) and effective (best results) way of accomplishing a task, based on repeatable procedures that have proven themselves over time for large numbers of people.
Sure... takes less time to type without brackets. Might even take less time to gen code for objects because you wouldn't have to add the brackets to every object name. Hmmm... but Microsoft always included brackets when they gen code in EM and QA... even on names with no special characters... and everyone one uses those at some point in time. Must've been easier (least amount of effort) for them to do it that way.
And, I've never seen that gen code fail... not ever. Could using brackets be the most effective (best results) way of accomplishing a task, based on repeatable procedures that have proven themselves over time for large numbers of people?Perhaps "Best Practices" isn't the word to be used here because it seems to leave way too much to individual interpretation. Let's try another term instead of "Best Practices"...
(http://en.wikipedia.org/wiki/Best_Coding_Practices)
Best coding practices can be broken into many levels based on the coding language, the platform, the target environment and so forth. Using best practices for a given situation greatly reduces the probability of introducing errors into your applications, regardless of which software development model is being used to create that application.
Let's grab the important part...
Using best practices for a given situation greatly reduces the probability of introducing errors...
If we did like Microsoft and bracketed everything that could be bracketed as they do in EM and QA (SQL Server 2000), what is the probability of brackets introducing an error as opposed to someone NOT following the "rules" from Books Online (already posted in this thread)?
And, if we go back and look carefully at those rules, anyone see where Microsoft said that either set of rules was a "Best Practice"? No... all they said is here are "The rules for the format of regular identifiers...". They never even recommended that you follow those rules. They followed that up with the bracket alternative if you don't follow those rules but they never mentioned which they thought would be the best practice in that Books Online article. They leave that up to the reader to decide.
Let's go one little step further... did you know that Microsoft actually MANDATES the use of at least one special character for a particular form of stored procedures? Take a look at CREATE PROCEDURE in Books Online which states the following...
Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Have you ever looked into that? Here's what BOL says about the "number" parameter...
;number
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.
Anyone see anywhere where they say it is NOT a Best Practice to use such a name for a stored procedure? No... about the only thing they say about anything coming close to a "Best Practice" recommendation, is in the section called "Tuning Recommendations" where they infer that you might get better performance from a single query instead of a Cursor... but, they never come out and say "Don't use a Cursor.. it's not a Best Practice". Again, they leave it up to the reader to determine what the "Best Practice" should be. (No, I am NOT condoning the use of Cursors!!!
)Ok... let's just say you disagree with all that rhetoric I just spewed
. Let's put everybody's money where their mouth is... the question at hand is "Is avoiding special characters in SQL object names the BEST PRACTICE or is using square bracket names around all SQL object names the BEST PRACTICE?" 
Everyone can answer that quite correctly by answering the following simple question..."If you were directed to write a script generator for SQL Objects, would you include square brackets around all SQL objects, just the ones with special characters, or none of the objects?"
Dunno about the rest of you, but I'd write it to include the square brackets around all the SQL objects (just like Microsoft did) because it follows all the rules of both "Best Practices" AND "Best Coding Practices"...
- It's the easiest thing to do (no need to write special tests in code for special characters).
- It's the most effecient thing to do (no extra clock cycles figuring out if an object has special characters).
- Testing is much easier (no need to test to see if it will handle all special characters [possibly_missing-some] or every combination of special characters).
- The probability of introducing errors drops to zero because it works everytime.
- One thing that Management really likes is the Management idea of something working, guaranteed, everytime.
I'll probably continue my lazy way of writing code without the square brackets... can I say that's the "Best Practice"? Nope... because in the past, I've missed things that square brackets would have taken care of without causing my unit testing to bomb. With that in mind, I gotta agree with Serqiy and say using square brackets all the time is "THE Best Practice" for things to work all the time. If you're a lazy ol' bugger like me, you can probably find some way of justifying not ever using them, but you've gotta know... that's a Management Idea of what a "Best Practice" should be and not a true "Best Coding Practice".
I may have to change my ways, though... I find that "Management Ideas" is frequently an oxymoron
--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)
Intro to Tally Tables and Functions -
My own personal "Best Practice" is to not use brackets when I control the object identifiers, as I won't let exceptions through. I'll even happily send the object creator back to the drawing board if they break the "rules". On the other hand, when writing things that are used in an environment that I don't have complete control over (much as the situation MS has with EM and QA code generation), procs and functions that take object identifiers as parameters, procs that must work against third party schemas, etc., I use delimiters, so that my code won't break.
So, avoid the need for delimiters when you can (and therefore feel free to not use them in those cases), but use delimiters in the cases where it's possible that they'll be needed.
It's a lot like cursors, udfs, triggers, etc. I try really hard to avoid them, but sometimes they are the best (or only sensible) option to solve a given task.
Just like about everything else in the SQL Server world, whether they should be used or not comes down to "It depends."
-
Heh... I agree... but that's not the debate... "It Depends" is not the debate... the debate is "What is the best practice?" and it also seems to be coming "What the heck is a best practice?". To some, it's what works for them. To others, it's what works all the time. Being the picky bugger that I am, I'd have to say the best "Best Practice" is the one that works all the time. Square brackets on all objects fit that bill even though, like you, I rarely use them... I'm not practicing the best "Best Practice".
--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)
Intro to Tally Tables and Functions
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply