What is the difference between "tempdb.#tmptbl" and "tempdb...#tmptbl"?

• cino

  SSC Rookie

  Points: 42

  More actions

  June 5, 2011 at 8:20 pm

  #252265

  And we get an error (like below) when we exceed 3 periods.

  The object name 'tempdb....#tmptbl' contains more than the maximum number of prefixes. The maximum is 3

  What does this periods stands for?

  I notice some sql servers reports maximum number of prefixes is 2 (Mine is 3), what is determining the maximum number of prefixes??

  Thanks,

  cino

• Lowell

  SSC Guru

  Points: 323753

  More actions

  June 5, 2011 at 9:07 pm

  #1334171

  every object is resolved to a four part name when a statement is compiles

  ServerName.DatabaseName.SchemaName.ObjectName

  ServerName.DatabaseName.SchemaName.Tablename

  sometimes you can skip explicitly nameing the object and leave the word between the periods out, and the compiler will assume a default.

  for example:

  SELECT * FROM DEV223.SandBox.dbo.ZipCodes

  try this to get your mental arms around it:

  --create a temp table on the fly:

  select 'Hello World' As TheData

  INTO #temp

  --see the data?

  SELECT * FROM #temp --what about with a schema?

  SELECT * FROM dbo.#temp --works!

  SELECT * FROM tempdb.dbo.#temp --the whole thing with dbname?

  SELECT * FROM tempdb.dbo.#temp --the whole thing with dbname?

  SELECT * FROM [HOME-PC].tempdb.dbo.#temp --the whole thing with dbname?

  SELECT * FROM tempdb..#temp --use the shorthand notation for the "default" schema

  SELECT * FROM [HOME-PC].tempdb..#temp --use the shorthand notation for the "default" schema

  SELECT * FROM [HOME-PC]...#temp --use the shorthand notation for the "default" schema and default database

  SELECT * FROM tempdb..#temp --use the shorthand notation for the "default" schema

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!