Nested Schemas

Question

Post reply

Nested Schemas

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
November 17, 2015 at 10:22 pm

#74523

Comments posted to this topic are about the item Nested Schemas
November 17, 2015 at 10:26 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

HappyGeek SSCoach Points: 18729 More actions · Answer 1

Good question, but would echo the sentiments would you really want to?

...

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Easy one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 3

Hi Steve ,

Interesting question but I am not understanding exactly its meaning and more exactly its usefulness.

1) we are to surround the name by brackets because . symbol is not allowed.

2) you are not creating real nested schemas , only creating distinct schemas.

3) it could be useful only if you want to search a partial match on a name of schemas in the sys.schemas catalog view , maybe useful to conceive a theoretical database but in reality very far from the real database.

4) the necessary use of the square brackets is making difficult scripts or queries to read. Moreover , if you forget them , you are sure to get an error maybe difficult to detect.

Interesting question , but I would advice to prohibit this way of coding/naming.

Have a nice day.

PS : I hope you will accept my excuses for my poor English , but even after posted on English speaking forums , I am not really mastering the English language.

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 4

patricklambin (11/18/2015)
I would advice to prohibit this way of coding/naming.

I disagree, naming conventions should reflect the factory needs. So, it may be that [Sales.XXX] is good in some environment.

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

Nice question, thanx

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

I don't use punctuation (especially spaces) in database object names myself because they require that I use brackets everywhere, so I would never even have considered this. Hence, I learned something new today. Thanks for a very interesting question.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sknox SSChampion Points: 12388 More actions · Answer 7

patricklambin (11/18/2015)
4) the necessary use of the square brackets is making difficult scripts or queries to read.

I disagree with this point. I find that consistent use of brackets makes it easier for me to read scripts, as I can quickly tell what is an object identifier vs a keyword.

The important point here, of course, is "consistent". If you only use brackets when absolutely necessary, then it can make the code look inconsistent, and indeed make it harder to read. Use them always or never.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Carlo Romagnano (11/18/2015)
patricklambin (11/18/2015)
I would advice to prohibit this way of coding/naming.
I disagree, naming conventions should reflect the factory needs. So, it may be that [Sales.XXX] is good in some environment.

Naming conventions are good, but using periods in object names are a really bad idea. You could replace the period with an underscore which will allow to have a nice blank space to improve readability without causing other concerns.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sestell1 SSChampion Points: 10230 More actions · Answer 9

Luis Cazares (11/18/2015)
Carlo Romagnano (11/18/2015)
patricklambin (11/18/2015)
I would advice to prohibit this way of coding/naming.
I disagree, naming conventions should reflect the factory needs. So, it may be that [Sales.XXX] is good in some environment.
Naming conventions are good, but using periods in object names are a really bad idea. You could replace the period with an underscore which will allow to have a nice blank space to improve readability without causing other concerns.

this is exactly what I was going to say, you beat me to it!

An underscore gives you the ability to have several separated terms in your name without the need for the square brackets.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

sestell1 (11/18/2015)
Luis Cazares (11/18/2015)
Carlo Romagnano (11/18/2015)
patricklambin (11/18/2015)
I would advice to prohibit this way of coding/naming.
I disagree, naming conventions should reflect the factory needs. So, it may be that [Sales.XXX] is good in some environment.
Naming conventions are good, but using periods in object names are a really bad idea. You could replace the period with an underscore which will allow to have a nice blank space to improve readability without causing other concerns.
this is exactly what I was going to say, you beat me to it!
An underscore gives you the ability to have several separated terms in your name without the need for the square brackets.

Agreed. The dot means something in SQL, whereas the underscore is the character used to separate words in databases since... Ummm...forever, I think.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 11

I ran across this with someone creating schemas as a namespace, reflecting something they were doing in development.

These aren't nested schemas per se, but that was just a title. In terms of looking at this from a development perspective, they appear nested, even though they are distinct.

Square brackets are used in T-SQL all over, and while they may appear strange, lots of scripts and code uses them. I would disagree these are an impact on readability .

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 12

Luis Cazares (11/18/2015)
Carlo Romagnano (11/18/2015)
patricklambin (11/18/2015)
I would advice to prohibit this way of coding/naming.
I disagree, naming conventions should reflect the factory needs. So, it may be that [Sales.XXX] is good in some environment.
Naming conventions are good, but using periods in object names are a really bad idea. You could replace the period with an underscore which will allow to have a nice blank space to improve readability without causing other concerns.

Agreed. It's the same problem I have with people that use dashes in instance names. And brackets make for really ugly code even if used consistently, IMHO. It's as bad as "tbl'ing". 🙂 I wish that MS would make it so that some intelligence would be used to not return brackets around names unless they were actually needed.

--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)

starunit SSCommitted Points: 1817 More actions · Answer 13

I have to agree with @patricklambin: it's not really a 'nested schema', rather it's really an effed up name...

Personally, l would never add FQN delimiters to an object name (unless under duress), but, heck, it might thwart some hacking attempts, or might be necessary for the product....

In either case, I did learn something, and now have another tool in my conceptual toolbox...

Cheers from Denver,

Mark
Just a cog in the wheel.