Post reply

What does 2 periods after DB name mean?

  • October 14, 2008 at 7:32 am

    #194310

    Hi all,

    Normally I see db name dot field name when referring to a field in a T-SQL statement (ie Customers.FirstName). Can someone tell me what the double dots mean (ie Customers..FirstName) and why and when to use them?

    Thanks for your help.

    Strick

  • October 14, 2008 at 7:37 am

    #884612

    databasename.schemaname.tablename.fieldname - standard 4 part naming.

    When working with MSAccess DB (for example) you are using:

    databasename..tablename.fieldname since MSAccess does not have schemas.

  • October 14, 2008 at 7:37 am

    #884613

    Proper syntax is ServerName.Databasename.Owner.ObjectName. You are seeing DatabaseName..ObjectName and letting it automatically fill in the owner name, which either always attempts dbo, or attempts based on the current user connection, I can't remember which.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 14, 2008 at 7:37 am

    #884614

    That just means that the default schema (dbo) should be used. In other words:Customers..FirstName is the same thing as Customers.dbo.FirstName

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 14, 2008 at 7:39 am

    #884619

    Ooop I might have phrased my question wrong... THe double dots are occuring in the FROM section of a SQL statement I'm worknig with. So they actually have DB name then Table name

    (ie

    From

    DBname..Table1

    DB2Nam..Table2

    Strick

  • October 14, 2008 at 7:42 am

    #884621

    Yeah, all 3 of us assumed you did that =).

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 14, 2008 at 7:48 am

    #884626

    Answers still apply no matter where you put the naming.

  • October 14, 2008 at 7:54 am

    #884632

    Great!

    Thanks guys

  • October 14, 2008 at 1:33 pm

    #884854

    While SQL Server allows this I feel it is a bad practice to have production code to run it. Here is why.

    When SQL sees this syntax it will first look at the current users schema to see if the table exists, and will use that one if it does. If it doesn't then it looks at the dbo schema and uses the table from there. Now, this is all fine and dandy UNLESS you mistakenly created the user schema table AND the dbo schema table. Now you don't always get what you expect. Especially if someone is used to putting dbo in front of all their objects. Plus it takes time for SQL Server to look up the schema.

    IMHO The best practice is to ALWAYS put the schema in front of the table name. This way you always know that you are hitting the correct object and you get a very small bit of increased performance that could add up over time.

    Gary Johnson
    Sr Database Engineer

  • October 14, 2008 at 3:27 pm

    #884931

    Yeah, I tend to agree with that too. I always put the schema in front of my tables. Why be lazy? Know what I mean? I inherited the current SQL statement I'm working with which is why I didn't know what double dots meant..lol.

    Thanks

    Strick

  • October 25, 2008 at 7:39 pm

    #889702

    rbarryyoung (10/14/2008)

    That just means that the default schema (dbo) should be used. In other words:Customers..FirstName is the same thing as Customers.dbo.FirstName

    Not the same for performance, though... 😉

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

  • October 26, 2008 at 11:10 am

    #889762

    A couple of things you should be aware of with this:

    1) In SQL Server 2000 - the following works: servername.database..objectname, in SQL Server 2005 it will no longer work. The schema is now required in linked server references.

    2) In SQL Server 2005 - not specifying the schema will cause separate execution plans to be created for each user accessing the system. See: http://blogs.msdn.com/sqlprogrammability/archive/2006/04/03/567663.aspx

    There are plenty of additional articles on this - you can search google.

    Additionally, if your code is not using the schema now and your DBA's decide that the objects in the database really should be in separate schemas - your code is going to break. Your DBA's then have to 'trick' the system by moving the object to the new schema and then creating a synonym in the old schema.

    One final thing - if the object is not in the default schema and the users default schema is 'dbo', the object will not be found unless the schema is specifically stated. Example:

    database..objectname will not be found

    database.schema.objectname will be found

    Moral: start using two-part naming convention as a minimum in all code that references database objects.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 27, 2015 at 1:37 pm

    #1773499

    I saw a question posted on StackOverflow which puzzled me. This is NOT an argument against fully qualifying table names, but just curiosity about an oddity of the feature.

    You can reference table names using double dots

    select top 100 * from myDB..myTable

    You can reference procs

    exec myDB..myProc

    But you can't reference functions

    select myDB..myUDF()

    /*

    Msg 102, Level 15, State 1, Line 50

    Incorrect syntax near '.'.

    */

    I also can't find any decent documentation for the feature online. Anyone have a convincing argument for why this is the case?

    Executive Junior Cowboy Developer, Esq.[/url]

  • January 28, 2015 at 7:21 pm

    #1773867

    The ".." represents the default schema for the security context accessing the data, without you having to type it. Let us say that I am logged in as loginA. Under a servers security tab (in SSMS) - when you go to the properties page for a login, there is a user mappings tab. In here you can change the default schema away from dbo on any one particular database.

    So if I am in the tempDB and I have a default schema of Acct in the accountingDB database,

    I can access Acct.table1 (schema.table) there by typing

    use tempDB;

    GO

    select top 10 *

    from accountingDB..table1

    /* instead of typing accountingDB.Acct.table1 in the From clause*/

    ----------------------------------------------------

  • January 30, 2015 at 1:38 am

    #1774260

    It is [Databasename].[Schema].[ObjectName]. When [Databasename]..[ObjectName] is used, SQL Server wil consider default schema

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

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