Is your login in the sysadmin Server Role? Is it a domain login, a local Windows login or a SQL Login?

DECLARE @bcpCommand VARCHAR(8000)

SET...

SELECT ukey,

intman,

admtype,

los,

...

What are you executing in the call to xp_cmdshell...are you running something that connects back to the SQL Server, like bcp? If so, please provide the command line (with any...

Not always...it depends on who is executing it. Have a look at the "Remarks" section...

I see your concern now...and I was able to recreate the plan on my instance after looking at your plan using this code:

DECLARE @0 BIT = 1,

...

An alternative is to enable mixed mode authentication, setup a SQL Login that has permissions to run the query in your bcp command and change from using -T to supplying...

In SSIS you can use the Derived Column Data Flow Transformation in your...

Hmmm....when I run your sample DDL into my SQL 2008 R2 test DB and run your select query I see index seeks (see attached sqlplan):

SELECT child.PATH

FROM ...

Please post your DDL and DML.

For functions:

SELECT *

FROM sys.objects

WHERE type IN (N'FN', N'IF', N'TF', N'FS', N'FT') ;

/*

From http://msdn.microsoft.com/en-us/library/ms177596.aspx:

FNSQL_SCALAR_FUNCTION

FSAssembly (CLR) scalar-function

FTAssembly (CLR) table-valued function

IFSQL_INLINE_TABLE_VALUED_FUNCTION

TFSQL_TABLE_VALUED_FUNCTION

*/

USE some_database_name

GO

SELECT name,

create_date,

modify_date

FROM sys.views

SELECT name,

...

Using what? The new Audit objects?

By default the local Windows security group "BUILTIN\Administrators" is part of the sysadmin Server Role. Check if your login is part of the local "Administrators" group. If it is not...

Please post the DDL for your table and the DML for your data.

You were asking about different approaches...a denormalized table approach is what you described in the hypothetical table in your original post. Implementing a star schema is an alternative to that....