none
One-part name VS two-part name RRS feed

  • Question

  • What is the meaning of using one-part names to reference the table or using two-part names in SQL Server 2012?
    Tuesday, July 17, 2012 8:51 AM

Answers

  • Here are the referencing options for tables (not the same for all database objects):

    1. 1-part naming: Product  (schema: dbo - default, database: current, server: connection)

    2. 2-part naming: Production.Product  (database current)

    3. 3-part naming: AdventureWorks2012.Production.Product

    4. 4-part naming: [LONDONPRODSERVER].AdventureWorks2012.Production.Product

    [LONDONPRODSERVER] is a linked server.

    Actually you can use 4 with your SQL Server also if DATA ACCESS is setup:

    SELECT * FROM [YOUR_SS_SERVER].AdventureWorks2012.Production.Product


    Kalman Toth Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    Sunday, July 22, 2012 7:26 AM
    Moderator
  • If you use one-part names, the second part, the schema, will be taken from the default. Which for an ad-hoc batch is the default schema for the current user, but for a stored proedure, view, trigger etc is the default schema for the object owner.

    It's generally considered best practice to use two-part names. However, there are many applications that only uses the dbo schema, why adding that "dbo." may become a little tedious. But it's only OK to cheat in stored procedures. Applications should always use two-part notation, no matter they only call stored procedures, or send SQL batches.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 17, 2012 9:53 AM
  • I guess one part name means the object_name and two part means schema_name.object_name

    Thanks and regards, Rishabh K

    Tuesday, July 17, 2012 8:53 AM

All replies

  • I guess one part name means the object_name and two part means schema_name.object_name

    Thanks and regards, Rishabh K

    Tuesday, July 17, 2012 8:53 AM
  • If you use one-part names, the second part, the schema, will be taken from the default. Which for an ad-hoc batch is the default schema for the current user, but for a stored proedure, view, trigger etc is the default schema for the object owner.

    It's generally considered best practice to use two-part names. However, there are many applications that only uses the dbo schema, why adding that "dbo." may become a little tedious. But it's only OK to cheat in stored procedures. Applications should always use two-part notation, no matter they only call stored procedures, or send SQL batches.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 17, 2012 9:53 AM
  • Here are the referencing options for tables (not the same for all database objects):

    1. 1-part naming: Product  (schema: dbo - default, database: current, server: connection)

    2. 2-part naming: Production.Product  (database current)

    3. 3-part naming: AdventureWorks2012.Production.Product

    4. 4-part naming: [LONDONPRODSERVER].AdventureWorks2012.Production.Product

    [LONDONPRODSERVER] is a linked server.

    Actually you can use 4 with your SQL Server also if DATA ACCESS is setup:

    SELECT * FROM [YOUR_SS_SERVER].AdventureWorks2012.Production.Product


    Kalman Toth Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    Sunday, July 22, 2012 7:26 AM
    Moderator