Database name with special character(Urgent)
-
My Database name is ST_Rel_4.050_Test
When i fire query in select * from ST_Rel_4.050_Test.dbo.sa_desk
It fails in Query Analyzer also as there is dot(.) in b/w database name
What to do to run this query
I have problem related to this with asp.net application also
So i want this to be run anyhow
Waiting.................
-
try putting square brackets around the object names:
select * from [ST_Rel_4.050_Test].[dbo].[sa_desk]
You probably only need it around the database name but I have shown it around all levels.
J
-
...or.... don't use illegal names with characters that's not allowed...?
/Kenneth
-
I can appreciate you're wanting to use the version number within the database name, but your life will be MUCH easier if you rename the database so that instead of ....'4.050'... you use something like ...'4V050'... for 'version' or use an 'S' for 'subversion' or 'R' for 'release' or some other mnemonic device.
Using a separator for another purpose also threatens to confuse anyone not familiar with your naming convention / nomenclature. I forget who said it: Great Britain and the USA are two nations divided by a common language. or some such...
Do you need / want references to the best practices pages of BOL? Many pain-filled hours went into establishing best practices.
-
Thanks to all for your replies
It worked with []
Steve Smith, i need best practices if you can give which can be understand by anybody who looks into it.
Again thanks a lot
Waiting.......
-
Best practice #1:
Always use objectnames that *don't* have to use square brackets in order to not crash your code []
/Kenneth
-
I would not name it best practice, I would not name it even recommended practice.
You cannot always control naming conventions. Sometimes it's dictated by some business rules you cannot change.
So, best practice would be
Always use square brackets [] in order to not crash your code.
_____________
Code for TallyGenerator -
I actually disagree
The reason I don't like square brackets, is that they was invented so that illegal names would 'become legal'.
What the brackets really do, is to skip the parsing, and you also loose the colorcoding.
If that's really how we should do as a first option, then why have the limitations in the first place?
I think, as a 'best practice', it's better to follow the rules at hand, instead of the other way around...
(I know sometimes there are situations where you can't change things, but those cases falls outside of 'best practice')
/Kenneth
-
I would agree with Sergiy, though I see Kenneth's point.
The reason, in my opinion, that the square brackets came into being was due to extensions to SQL. Many common words people used in code objects became reserved in the 7/2000 timeframe. To allow stuff to upgrade without changing names, though not without code changes, the square brackets were added.
I do think you should "avoid" using those names, but what if you ported from another platform? Upgraded from a legacy system. Sometimes you cannot control the names.
I guess I might say avoiding [] is a best practice, but it's certainly not always avoidable.
-
It's not about limitations.
It's about SQL Server interpreter.
It reads a string and translates it to computer code according to its understanding.
It takes string word by word (word is text between word delimiters which are spaces and line terminators) and tries to make some sense of it.
When it sees word "User" it tries to match it with system function first, and because there is a match it will never try to match with table names.
If it sees "MyColumn" it goes through all possible identifier types until it finds the match in the list of columns.
In fact, including MyColumn in brackets you speed up translation process by telling server - it's my identifier, don't bother to match it to system keywords or operators.
And I don't believe you can lose any colorcoding by using brackets. Custom identifiers are not colored anyway.
> I think, as a 'best practice', it's better to follow the rules at hand, instead of the other way around...
Agree. Trick is using quoted identifiers is a rule as well.
_____________
Code for TallyGenerator -
Well, let's put it this way then.
'Best practice' (as far as naming conventions go) would be to use names that won't rely on either square brackets or quoted identifiers, but instead adhere to the rules for naming object identifiers...
Now, this isn't the same thing as saying 'don't use', or 'avoid at all costs'.
There are, as noted, many times that we inherit stuff from other places (Access maybe
), and then it's a good (if nothing else, timesaving) thing that there is an option available that will let the stuff work anyway.'Best practice' is 'what to choose from when we have the opportunity to choose ourselves', agreed?
OTOH, one could say that if it was about how to write safe dynamic SQL or similar, then indeed would using quoted identifiers (really QUOTENAME) be considered as a best practice...
Who said 'it depends'...?
/Kenneth
-
"Best practice" is something best, right?
If "it depends" then there are cases when it's not the best.
So, it cannot be named "the best practice".
The practice you suggested fails in many cases, that's why I was disagree with naming it "best practice".
BTW, can you explain what's the strong reason for not using square brackets?
Can you show any case when this practice will fail?
Yes, it takes some extra time to code with brackets (unless you use script options in QA), as well as format your code or put comments in it. But is it really a reason to consider any of these practices not the best?
_____________
Code for TallyGenerator -
The thing about the 'best thing' is that in order to determine that it really is 'the best', you need something to relate it to. That is, a context.
Also, 'best' is a very subjective word.
There are different best practices in different situations, so therefore, 'it depends'
In this case, it's about objectnames, nothing else.
Well, the 'strong' reason, in my mind, is more of a protective nature.
(and I don't like them, cuz I think they clutter up the code, and the pretty colours go away)
If you do use them, then there's nothing there to say 'hey there, now this here name you've chosen is a reserved word, or, it's plain illegal, I won't do that'. So, it allows you to - on purpose or unknowingly - create something that may cause problems further on.
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.
You loose the 'datatype property' of the objectname, so to speak.
It's kinda like making a table with all columns nullable, even though they all don't need to allow nulls.
(let's just skip the theory aspects of it)
Once you allow nulls in a column, then all references to it must also always handle the possibility of something being null. Should someone forget that, chances are that results then may be unexpected.
Another way of looking at it, is from a 'business rule' perspective.
We could say that the business rule for objectnaming is the rules that BOL state, right?
[] would then be a sure way to short-circuit those business rules.
If this was data (which it is after all), how would we then validate that business rules are honored?
Remember, this is about choosing when the choice is there, not about 'the other stuff' that is out of your control. In that case, I don't see where following the rules from BOL as a practice would fail.
/Kenneth
-
First of all, you did not specify context where you "best practice" is the best. You just declared it "the best".
You were wrong there.
Let's not fool newbies.
> 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?
> 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?
> We could say that the business rule for objectnaming is the rules that BOL state, right?
What kind of rules are you talking about?
_____________
Code for TallyGenerator -
Sergiy,
I disagree. Business rules should not dictate how you name database objects.
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply