Global Variants table and reinterpret casts

Global Variants table and reinterpret casts

Post by boblair_pe.. » Thu, 29 Jun 2000 04:00:00



I'm using a table to store global variables. To be general, I store
the variable value as as a varbinary(512) and the type:

CREATE TABLE tblGlobals (
        [Name]          varchar(32)     NOT NULL ,
        [XType]         tinyint         NOT NULL , -- SQL Server XType,
as defined in the systypes table
        Value           varbinary(512)  NOT NULL ,
        Hints           varchar(32)     NULL       -- Optional code
that gives hints on how to interpret the value (eg, CSV for comma-
seperated-values)
) ON [PRIMARY]
GO

I will use these variables in where clauses to compare to clustered
primary keys on other tables. For example:

-- BEGIN EXAMPLE ------------------------------------------







select * from SomeTable s


-- END EXAMPLE ------------------------------------------

I want to be sure that the value from tblGlobals is used directly to
access SomeTable's index tree. Since only a reinterpret cast is needed,
this shouldn't be a problem. However, I can't find any documentation
that specifies how SQL Server will handle this. I'm afraid it might
decide to do a table scan and cast and compare at each record, ignoring
the index at great cost.

BTW, I don't know ahead of time which values are used with which
tables. That association is looked up in another table (and hard-coded
in some places).

Any ideas? Are there other ways to implement a table for storing global
variables that would be more efficient?

Thanks for your help and advice.

-Ben Blair

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Global Variants table and reinterpret casts

Post by dlm » Thu, 29 Jun 2000 04:00:00



> I will use these variables in where clauses to compare to clustered
> primary keys on other tables. For example:
...

> select * from SomeTable s
> where TableKey > cast((select Value from tblGlobals where [Name] =

...
> I want to be sure that the value from tblGlobals is used directly to
> access SomeTable's index tree.

A table hint could be a way out since it specifies one or more indexes to be
used by the query optimizer. If a clustered index exists, INDEX(0) forces a
clustered index scan and INDEX(1) forces a clustered index scan or seek.

 
 
 

1. reinterpret-cast for faster joins?

This is highly non-normal, but I've got a table
defined as:

CREATE TABLE tblExample AS
(
   [ID]  int NOT NULL PRIMARY KEY CLUSTERED,
   XType tinyint NOT NULL,
   Value varbinary(256) NULL
)

that I'm using to store, effectively, global
variant-type variables.

I use this to join to columns in other tables
(based on the ID matching a metatable of mine).

For example:

SELECT * FROM tblMainTable AS m
INNER JOIN tblExample AS e on
m.[ID] = CAST(e.Value AS int)

Here, I look up the 'int' in CAST from
systypes based on XType and build the
above SQL string then EXEC it.

What I'd like to know is if SQL Server
does the CAST for each row, and if so,
does it do a 'real' cast or does it just
do a "reinterpret" cast. The reason I'm
concerned is that I want to be sure that
the CASTed value can be used efficiently
in the above joins to search through the
index tree (rather than doing a table scan).

If it's going to be doing a "full" CAST
for every record in a table scan, I think
I might as well store my variables as
varchars so they're easier to read.

If there's a better way to store global
variables that will be used to index into
aribtrary tables, I'd love to know how.

Thanks for your help and advice.

Regards,

-Ben Blair

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Cluster service and SQL 2000

3. Casting VARIANT to ADO Recordset

4. Changing Sort Order

5. Saving DTS file w/ Variant Global Variable

6. CO-Colorado Springs-241409--VAX-VMS-CMS-Java-ORACLE-UNIX-PVCS-Client/Server-Software Systems Engineer

7. check validity of cast before performing cast?

8. Export to MAS-90

9. CREATE CAST requires immutable cast function?

10. Temp table vs Global Temp table

11. Global validation table vs. separate validation tables

12. Saving VARIANT variables into table causes error