Would using a table variable be efficient?

[clear1140]

We divided our tables from table zone_a, ..._b, ..._c to zone_z, these tables have the same attributes. We have divided this tables in this way so that any data that is under zone_a would only be on it's specific table, and the same with the other remaining table. Since per table holds thousands of data per day we have decided it to be designed this way and also hoping to reduce lock up of tables everytime a user accesses the table.

Now, we made a function that returns a table. This function will handle what table is to be selected. The body of this function contains a table variable, it holds the data from the selected table. This function will return the table variable

We have made this function hoping that it would be efficient to use for other future procedures.

Is it efficient?

Thank You in advance.

[Jim Doherty]

@clear1140
Table variables generally should be used only with relatively small amounts of data in my view why not just use a stored procedure with a zone parameter passed in to divide the data from the source then all you need to do is run the stored procedure. One tip for you.....if all you are doing is accessing table data jto read records basically and are not too worried or concerned about concurrent edits then use the optimiser Hint WITH (NO LOCK) This prevents locks and will thus be faster

SELECT Column1,Column2,Column3 FROM YOURTABLENAME (WITH NO LOCK)

[clear1140]

@Jim Doherty
thank you so much. Am getting what u mean and somehow i knew this was gonna be the answer. Thanks for the WITH (NO LOCK) option, i have never used that before

[Jim Doherty]

@clear1140
You're welcome :) Yes the optimiser hint NO (LOCK) is very useful if used appropriately, ie fetching read only data to a web page for instance, where it doesnt really matter for that split second if we are retrieving all available updates and so on.

[ck9663]

Or you can still use a function that handles the returning of the value. Since they seems to have an identical structure, you can do something like...

 
create function ReturnYourData (@fromtablename varchar(50))
returns table
as
return
(
select 'TableA' as source, col1, col2, col3, col4 from TableA where @fromtablename = 'TableA'
union all
select 'TableB' as source, col1, col2, col3, col4 from TableB where @fromtablename = 'TableB'
union all
select 'TableC' as source, col1, col2, col3, col4 from TableC where @fromtablename = 'TableC''
)
 
 

Since a variable can only contain a single value at any point in time, your query will only return the value from a specific table. This is also a handy technique rather than using an dynamic query.

Make sure your table have proper index.

Good Luck!!!

~~ CK