use a sequence of column values in a query

Question

use a sequence of column values in a query

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Absolutely perfect... you actually need to use two tables... let's say you have TableA and TableB and they are identical. You have a view or a synonym pointing at TableA called TableC and the users are all using that with no problems and no downtime. In the meantime, you receive all new data and populate TableB with it. Once you're sure the data is good, you simply alter the TableC view or synonym to point to the newly updated TableB... the repoint takes scant milliseconds, the users never see a blip.

Next time you get new data... the users are now looking at TableB though the TableC view or synonym... you repopulate TableA and then alter the TableC view or synonym to point at the newly refreshed TableA.

Repeat every time you get new replacement data.

Cool thing about this is that if ANY failure or anomoly is detected, you simply DON'T repoint the TableC view or synonym. The user's will never know that some error kept today's data from being used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JRJ SSC Veteran Points: 223 More actions · Answer 2

Thanks Jeff, I don't how to implement this in DTS, I need to switch each time destination table, so I need to track what table is being used in the last import.

What do you think about the following, still using your idea but slightly differently.

Keep two identical table, tableA to import and tableB for users to query the data. The data will be imported from the external source always to tableA, but just after import tableB will be repopulated from tableA after checking data in tableA is ok. This is easily implemented in the DTS, as I can write a Stored procedure (do the import from TableA to B) and execute this after the external import. This is also have an advantage as I don't need to have any indexes on tableA as this won't be queried.

Please also let me know what will happen to the indexes when I delete and repopulate the tableB, will this be too much of a overhead for the server.

Thanks.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

During the repopulation of TableB in DTS, your users will wait.

Why does this have to be DTS? Why can't it be DTS executing a proc?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JRJ SSC Veteran Points: 223 More actions · Answer 4

That's possible. How can I change a views definition in a proc? Also I am deeling with duplicate indexes, is that ok?

thanks

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

JRJ (6/22/2008)
That's possible. How can I change a views definition in a proc? Also I am deeling with duplicate indexes, is that ok?
thanks

A view's definition can easily be changed using a bit'o dynamic SQL. So far as indexes go, you can have identically named indexes on two different tables. Not so with constraints like Primary Keys... they must be different. But, you're not going to be dropping the tables, anyway... just truncate them before you try to refill them. Of course, it's important not to have any Foreign Keys involved or you won't be able to truncate.

Here's an example of using Dynamic SQL to ALTER a view...

DECLARE @sql VARCHAR(8000)

SET @sql = '

ALTER VIEW dbo.TableC AS

SELECT * FROM dbo.TableA

'

EXEC (@SQL)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Everything Jeff has given you is good, but - you don't need to modify any code at all including modifying the views if you use Synonyms. Here is an example:

Use AdventureWorks;

go

If Not Exists(Select * From sys.schemas Where name = 'MySchema')

Execute ('Create Schema MySchema Authorization dbo;');

go

If Exists(Select * From sys.tables Where Name = 'TestTable1'

And schema_name(schema_id) = 'dbo')

Drop Table dbo.TestTable1;

Create Table dbo.TestTable1 (code char(2), description varchar(20));

go

If Exists(Select * From sys.tables Where Name = 'TestTable2'

And schema_name(schema_id) = 'dbo')

Drop Table dbo.TestTable2;

Create Table dbo.TestTable2 (code char(2), description varchar(20));

go

If Exists(Select * From sys.synonyms Where name = 'TestTable'

And schema_name(schema_id) = 'MySchema')

Drop Synonym MySchema.TestTable;

go

Create Synonym MySchema.TestTable For dbo.TestTable1;

Go

If Exists(Select * From sys.views Where name = 'vTestTable'

And schema_name(schema_id) = 'dbo')

Drop View dbo.vTestTable;

go

Create View dbo.vTestTable As

Select * From MySchema.TestTable;

Go

-- Insert data into both tables

Insert Into dbo.TestTable1 (code, description)

values('AA', 'Test Table 1');

Insert Into dbo.TestTable2 (code, description)

Values('BB', 'Test Table 2');

Select * From dbo.vTestTable;

go

-- Change the Synonym to the other table

Drop Synonym MySchema.TestTable;

Create Synonym MySchema.TestTable For dbo.TestTable2;

go

Select * From dbo.vTestTable;

go

-- Switch back

Drop Synonym MySchema.TestTable;

Create Synonym MySchema.TestTable For dbo.TestTable1;

go

Select * From dbo.vTestTable;

If your stored procedure references the view 'dbo.vTestTable' and that view references the Synonym 'MySchema.TestTable' then simply changing the Synonym will alter what table you are accessing. Or, you can reference the synonym directly in your procedure and not worry about accessing the view at all (your call).

As long as both tables are created exactly the same - there shouldn't be any issues at all. I would not recommend using '*' in the view - but use the actual columns that you need.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JRJ SSC Veteran Points: 223 More actions · Answer 7

Thank you guys for all your comments, it's really appreciated. I have learnt great deal.

JRJ

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Jeff W...

SELECT * works perfect on these type of "pass through" or "surrogate views". You can go through the process of naming all the columns if you want, but absolutely not necessary... underlying indexes on the tables will still be used.

I agree about the synonyms... they would be better that using views...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Yeah, I guess I could see using '*' in that situation - but it still suffers from the same problem. If someone modifies the source table, you will have problems (especially if they use the GUI and change column orders).

Jeff W.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeffrey Williams (6/22/2008)
Yeah, I guess I could see using '*' in that situation - but it still suffers from the same problem. If someone modifies the source table, you will have problems (especially if they use the GUI and change column orders).
Jeff W.

The remedy for this is trivial: just recompile the view. (The better solution is to not let anyone change the column orders in the first place, which I do not.) Though I will grant you that not having to recompile is one advantage that Synonyms have over Views for aliasing.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Whether you use SELECT * or not, if you want to see the table changes in a view, you have to recompile the view. Like Barry said, this is where synonyms are better... if you have 2005. In 2000, just get used to doing a recompile.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

Yes - and that is why I avoid using it in production code. Although in this situation I can see that it shouldn't matter much and you could always add a recompile step if needed.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Heh... in this situation, you're rebuilding the view! It's gonna recompile no matter what... 😉 Works fine in production code especially in 2k where there's no such thing as a synonym. Users like it, too! Total "outage" for the full table update is about 30 milliseconds... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 14

Of course, now that I actually think about it. Been a long day...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs