ColumnName size

Do large column names hamper performance?Names - no widths - yes. Increases the physical I/O required to process
queries.
HTH
Jerry
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:003C3771-3BA5-4A37-B7A9-0951B2BCAD09@.microsoft.com...
> Do large column names hamper performance?|||Possibly. At the very least longer names might increase network traffic if
they are being used in SQL batches. But if you are experiencing a
performance problem then I would look for other potential causes first.
An unusually long column name perhaps indicates either a poor naming
convention or, worse, that you are representing some element of data in a
column name. Either of those are good reasons to change the name - with or
without any performance impact. For the record, most of the column names in
my current project are less than 30 characters long.
David Portas
SQL Server MVP
--

ColumnName Property on an Attribute

I have a Matter dimension with an OrgUnit attribute, the ColumnKey is Matter.OrgUnitKey and the ColumnName is OrgUnit.Name:

This is the select generated when processing the cube is:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1],[Foundation].[Person] AS [Foundation_Person]

WHERE (([Foundation_Person].[PrimaryOrgUnit1Key] = [Foundation_OrgUnit1].[Key])

AND([Foundation_Matter].[BillingPersonKey]= [Foundation_Person].[Key]))

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

2

Firm 2

2

Firm 3

3

Firm 1

3

Firm 2

3

Firm 3

4

Firm 1

4

Firm 2

4

Firm 3

This is more or less the select I was expecting:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1]

WHERE ([Foundation_Matter].[OrgUnit1Key] = [Foundation_OrgUnit1].[Key])

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

3

Firm 2

4

Firm 3

The question is how is the SQL statement build?

I have defined the following relationships:

Matter.OrgUnitKey = OrgUnit.Key

Matter.BillingPersonKey = Person.Key

Peson.OrgUnitKey = OrgUnit.Key

I can define a select in the DSV to get the Name with the Key but like to avoid it if I can,

Please Help

It looks like you have a loop in your relationships and SSAS has chosen to go through the person table to get to the OrgUnit. Do you have any attributes in your Matter dimension that reference the Person table? If not, you might need to create a named query in the DSV for the OrgUnit table so that there are two instances of it. In this way you could break the realtionship loop into 2 pieces so SSAS would not be able to choose the wrong path.

|||

There is a relationship Matter to Person and Person to OrgUnit.I was trying to avoid going to the DVS, but it seems unavoidable.There is more that one Dimension that will have this problem.

Thank you for you quick response.

ColumnName Parameter for Table Valued Function

Hi All,

Is it possible to do the following:
1. I have a udf_xxx which returns Col1,Col2 and Col3
2. I need to join this udf_xxx to get Col1 and Col2 where Col3 matches with table tbl1.

SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
JOIN dbo.udf_xxx (t.Col3) udf ON
t.Col3 = udf.Col3

Thanks in advance.In SQL Server 2005, you could use CROSS (OUTER) APPLY:
SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
CROSS APPLY dbo.udf_xxx (t.Col3) udf|||I just happened to find out that too. Thanks Konstantin Kosinsky!

columnname in sql

how to select all column names from sql whose columnvalue is '1' for a specific user

Could you please post the table structure? Ideally the Create Table SQL.

Thanks,

Matt

|||

Thankyou for the response Matt.

Actually the entire column in the table is dynamically created.it will grow everytime wen user add .but it wont happen frequently.so i cant post that table struct.let me describe my needs with this sample

Create table sample(id varchar(10), item1 bit,item2 bit,item3 bit,item4 bit)...... and goes on

now i need to select all column names ,actually i can do this.

but i jus need to select columns whose value is one.

eg.

id item1 item2 item3 item4

zzz 1 0 0 1

yyy 0 1 1 1

my sql qurey or SP should return the column name for specific id whose value is one.

lets say id=zzz it should returnItem1 and item4(i meant column name)

if id = yyy it should return item2,item3,item4(i meant column name)

Thanks in advance

|||

HiTweety@.net,

As far as i know you need to use cursor in your case. But where are planing to store those column names??

See the following codes i've written for you:

declare @.item1int,@.item2int,@.item3int,@.item4intdeclare cursor_test cursorforselect * from test_tblopen cursor_testfetch next from cursor_testwhile @.@.fetch_status=0beginfetch next from cursor_testinto @.item1,@.item2,@.item3,@.item4if(@.item1=1)print'column 1 name'if(@.item2=1)print'column 2 name'if(@.item3=1)print'column 3 name'if(@.item4=1)print'column 4 name'endclose cursor_testdeallocate cursor_test

BTW, if possible, i would suggest you write the code in your application(using c# or other .net languages instead of T-SQL)

Hope my suggestion helps

|||

Hi Thanks for the Response,

i managed to solve this issue by retreiving the column names in table first and then checking the value for one using vb program

Column to Search in DB

Hi, Can anyone help me by telling the sql statement through which I should b
e
able to locate the table which contains that ColumnName...
Thanks in Advance
KDKD,
Query Analyzer includes this capability and more with the Object Search
(F4).
HTH
Jerry
"KD" <KD@.discussions.microsoft.com> wrote in message
news:B44D796D-7957-46BA-A5DE-337D6808A3A0@.microsoft.com...
> Hi, Can anyone help me by telling the sql statement through which I should
> be
> able to locate the table which contains that ColumnName...
> Thanks in Advance
> KD|||Hi,
Use the below query:-
select Table_name from information_schema.columns where
column_name='column_name'
You can also query the system table SYSCOLUMNS.
Thanks
Hari
SQL Server MVP
"KD" <KD@.discussions.microsoft.com> wrote in message
news:B44D796D-7957-46BA-A5DE-337D6808A3A0@.microsoft.com...
> Hi, Can anyone help me by telling the sql statement through which I should
> be
> able to locate the table which contains that ColumnName...
> Thanks in Advance
> KD|||Thanks Hari, That's what I really wanted !
Thanks !
KD
"Hari Pra" wrote:

> Hi,
> Use the below query:-
> select Table_name from information_schema.columns where
> column_name='column_name'
> You can also query the system table SYSCOLUMNS.
> Thanks
> Hari
> SQL Server MVP
>
> "KD" <KD@.discussions.microsoft.com> wrote in message
> news:B44D796D-7957-46BA-A5DE-337D6808A3A0@.microsoft.com...
>
>