Indexing of binary and varbinary

Hi
I’m writing an Alarms and Events database. Our A&E is identified by a byte
array in the A&E object. This byte array I will store in a column of a tabl
e
in addition to other columns with other relevant data.
I’m thinking of using the binary or varbinary type for the purpose of
storing the byte array.
I also need to have an index on the varbinary field, -is that possible?
This field will identify an instance of a specific alarm and will be used to
calculate statistics regarding how often the alarm occurs etc.
Is possible to do SELECT on a varbinary, and if so is it more resource
demanding compared to do this with a string on the same size? (Another
possibility is to convert the byte array to a hex string).
SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
ORDER BY BinId
Regards
Kjell ArneHi
You can index varbinary columns, but if you are going to always divide this
into individual bytes and want to mainly access these separately then you
would probably be better off holding them separately, expecially if you want
to index them separately. If the bytes represent characters then you should
hold them in char columns if they are numeric or binary they should be
numeric or binary.
HTH
John
"Kjell Arne Johansen" wrote:

> Hi
> I’m writing an Alarms and Events database. Our A&E is identified by a by
te
> array in the A&E object. This byte array I will store in a column of a ta
ble
> in addition to other columns with other relevant data.
> I’m thinking of using the binary or varbinary type for the purpose of
> storing the byte array.
> I also need to have an index on the varbinary field, -is that possible?
> This field will identify an instance of a specific alarm and will be used
to
> calculate statistics regarding how often the alarm occurs etc.
> Is possible to do SELECT on a varbinary, and if so is it more resource
> demanding compared to do this with a string on the same size? (Another
> possibility is to convert the byte array to a hex string).
> SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
> ORDER BY BinId
> Regards
> Kjell Arne

Indexing of binary and varbinary

Hi
I’m writing an Alarms and Events database. Our A&E is identified by a byte
array in the A&E object. This byte array I will store in a column of a table
in addition to other columns with other relevant data.
I’m thinking of using the binary or varbinary type for the purpose of
storing the byte array.
I also need to have an index on the varbinary field, -is that possible?
This field will identify an instance of a specific alarm and will be used to
calculate statistics regarding how often the alarm occurs etc.
Is possible to do SELECT on a varbinary, and if so is it more resource
demanding compared to do this with a string on the same size? (Another
possibility is to convert the byte array to a hex string).
SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
ORDER BY BinId
Regards
Kjell Arne
Hi
You can index varbinary columns, but if you are going to always divide this
into individual bytes and want to mainly access these separately then you
would probably be better off holding them separately, expecially if you want
to index them separately. If the bytes represent characters then you should
hold them in char columns if they are numeric or binary they should be
numeric or binary.
HTH
John
"Kjell Arne Johansen" wrote:

> Hi
> I’m writing an Alarms and Events database. Our A&E is identified by a byte
> array in the A&E object. This byte array I will store in a column of a table
> in addition to other columns with other relevant data.
> I’m thinking of using the binary or varbinary type for the purpose of
> storing the byte array.
> I also need to have an index on the varbinary field, -is that possible?
> This field will identify an instance of a specific alarm and will be used to
> calculate statistics regarding how often the alarm occurs etc.
> Is possible to do SELECT on a varbinary, and if so is it more resource
> demanding compared to do this with a string on the same size? (Another
> possibility is to convert the byte array to a hex string).
> SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
> ORDER BY BinId
> Regards
> Kjell Arne

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

Hi Rama,

You can use the option EXPAND VIEWS in SELECT clause for your purpose.

Microsoft recommend's, however, to let the query optimizer dynamically determine the best access methods to use for each individual query

Jag

|||

Can u explain me in detail.if possible with example becz i'm new to DB stuff.

indexed views and context

Hi guys ,

we had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.

Now the question is can I create indexed views?

If yes how can I implement .Because data depends on context is there any way to create global indexed views.

In case of indexed views query optimizer will automatically select indexed views for efficient way of execution .but I have to restrict to use only our existing views and these views have to refer indexed views and I need to force query optimizer not use indexed views.

First of all you can't create a index on your view.

Reason: suser_sname is not a deterministic function. So it wont to allow you to create a index on this..

|||

Can u explain me in detail .I’m new to indexed views.

Can we create indexed view for all data in tables and can be filtered this data by referencing this indexed view with in another view? Can u consider this point also.

If this is possible then, is there any way restrict not to use indexed views by query optimizer .