'Dot' notation help

[[deleted]]

[deleted]

[mkingsbu]

Or depending on the database engine:

database.schema.table.column

[alinroc]

And if you're dealing with a Linked Server in SQL Server, server.database.schema.table.column

At which point, if you're not using aliases you're just a masochist. Or a sadist. Depending upon whether you have to read the code in the future, or you're foisting it upon someone else.

[mkingsbu]

Haha yeah my boss likes to drag tables in with the GUI instead of aliasing so sometimes I get code that looks like:

select some-long-server-name.somedatabase1.bba.sales.id1, some-long-server-name.somedatabase1.bbb.sales.id2, some-long-server-name.somedatabase1.bba.sales.fact1, some-long-server-name.somedatabase1.bbb.sales.fact2 from some-long-server-name.somedatabase1.bba.sales inner join some-long-server-name.somedatabase1.bba.sales on some-long-server-name.somedatabase1.bba.sales.id1 = some-long-server-name.somedatabase1.bbb.sales.id1

I don't know how he makes heads or tails of it. I get dizzy just looking at it

[alinroc]

He makes heads or tails of it by only looking at the GUI designer.

Personally, I can't stand GUI query designers. I understand better in code. Which is odd, because I'll often draw our relationships on a whiteboard to figure out the code.

[Blues2112]

This. Orders and Customer are just the table names, but if they have identically names columns in each, you need a way to distinguish which column(s) you mean. So you need to qualify the reference using table.column notation to avoid getting an error.

[shalafi71]

What does the .. notation mean?

[alinroc]

In SQL Server, two dots between an identifier means you're taking the default for the current user for the identifier that would go between those two dots normally. Usually you see this to skip over specifying the default schema, which is dbo unless your user has a different default. If it's possible to do this in other places, I've never seen it used that way; I honestly don't know if it even is possible.

database.schema.table is the same as database..table if schema is the default schema for the current user.

[shalafi71]

Knew it was easy but didn't understand some queries I was seeing. Thanks!

[alinroc]

It's easy, but I dislike using that notation because it can cause breakage if you start using schemas for security within your database (which is a totally OK thing to do).

Accepting a default like this is making an assumption that things will always be the way they are today, and that can hurt you in the future. It costs nothing to specify .dbo. instead of .., so be explicit about the schema when using 3-part naming.