SQLRNNR (8/22/2014)
Raghavendra Mudugal (8/21/2014)
TomThomson (8/21/2014)
twin.devil (8/21/2014)
Just for the information.Becuase #table names max length is only 116 characters in SQL Server 2008 r2. its also valid of SQL SERVER 2012.
It's valid in every version from SQL Server 2005 to SQL Server 2014 inclusive. Probably in SQL Server 2000 too, but I haven't kept a copy of the transact-sql reference for SQL Server 2000 so I'm only 99.5% certain of that.
.
yup, its confimed.
text from sql server 2000 help file (from the CREATE TABLE statement)
table_name
Is the name of the new table. Table names must conform to the rules for identifiers. The combination of owner.table_name must be unique within the database. table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.
Nice Find.
IMHO - that needs to be updated so a temp table can be 128 characters too.
It won't be updated.
The reason for the shorter maximum length for temp table names is that SQL Server internally adds some characters to the name. That way, you can create a table #tmp1 and I can create a table #tmp1 on the same server, and we will not be in each other's way.
Can this be changed? Probably. But given how often I have sat behind my keyboard cursing the fact that I was unable to add a 117th character to the name (aka "never"), I have a very long list of things I much rather see the SQL Server development team work on!