April 5, 2010 at 9:11 pm
Jeff Moden (4/5/2010)Nope... you don't really need to do that if it's a single query that does the inserts into the truncated table.
For the maximum speed, though, I'd maintain "two" main tables. One would be "online" and have a synonym pointed to it and the other one would be dropped. When it was time to rebuild the main table and provided that none of the data would come across a linked server, I'd use SELECT/INTO to build the new table and add the necessary indexes. Then, just repoint the synonym to the new table (which only takes a couple of milliseconds, BTW) and bingo... you're new updated table is online and there isn't a user that will ever notice.
I like this usage of synonyms - another one of the many uses for them. 😉
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
April 6, 2010 at 3:23 am
Hi,
Jeff Moden(06 April 2010)
For the maximum speed, though, I'd maintain "two" main tables. One would be "online" and have a synonym pointed to it and the other one would be dropped.
This is a very good idea, however, I don't know if it synonyms would work for me. I am using SQL 2005 for SSRS but it's in SQL2000 compatibility mode as the servers are all SQL2000.
I'll see if I can still use it, either way, I've got a lot of good ideas here.
Thanks
April 6, 2010 at 6:57 am
pablavo (4/6/2010)
Hi,Jeff Moden(06 April 2010)
For the maximum speed, though, I'd maintain "two" main tables. One would be "online" and have a synonym pointed to it and the other one would be dropped.
This is a very good idea, however, I don't know if it synonyms would work for me. I am using SQL 2005 for SSRS but it's in SQL2000 compatibility mode as the servers are all SQL2000.
I'll see if I can still use it, either way, I've got a lot of good ideas here.
Thanks
There's still a way even in SQL Server 2000. Long before synonyms, we used "pass through views" which are nothing more than a view that does a select from all columns (don't use *... name each column). They just as easy to repoint with an "ALTER VIEW".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2010 at 7:08 am
Jeff Moden (4/6/2010)
There's still a way even in SQL Server 2000. Long before synonyms, we used "pass through views" which are nothing more than a view that does a select from all columns (don't use *... name each column). They just as easy to repoint with an "ALTER VIEW".
Just curious to know is it different from normal view ? as you mentioned "pass through views"
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 6, 2010 at 7:35 am
It's a normal view, just of one table. So for the customers table it's
create view PassThroughCustomers
as
select * from Customers
April 6, 2010 at 7:48 am
pablavo (4/6/2010)
This is a very good idea, however, I don't know if it synonyms would work for me. I am using SQL 2005 for SSRS but it's in SQL2000 compatibility mode as the servers are all SQL2000.
Synonyms are available on 2005 servers onward, regardless of compatibility level.
For a full list of things that are affected by the compatibility setting, see:
ALTER DATABASE Compatibility Level (Transact-SQL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 7:27 pm
Steve Jones - Editor (4/6/2010)
It's a normal view, just of one table. So for the customers table it's
create view PassThroughCustomersas
select * from Customers
Correct. As a side bar, the only thing you don't want to ever do with one of these is to use "*" because if anyone ever makes the mistake of inserting a column in the table using the design mode of SSMS or EM or similar code, it won't break the view but you can suddenly have data showing up in the wrong columns until you recompile the view (most folks use ALTER for that).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2010 at 9:29 am
Jeff Moden (7/04/2010)
There's still a way even in SQL Server 2000. Long before synonyms, we used "pass through views" which are nothing more than a view that does a select from all columns (don't use *... name each column). They just as easy to repoint with an "ALTER VIEW".
Just to make sure I've got this right, I'd like to clarify what's been suggested.
One table would be online. during this time, I'd have my procedures working away to transform the updated data and then I'd do a SELECT INTO to my other 'Main' table and create indexes on it.
After this, I'd ALTER VIEW to point to the new (let's say) tableB and the report that's running off the view will now have updated data? Then I can drop tableA
Hopefully what I've derived at is correct. If this is the case, would I need two jobs, one running 30 mins after the other so that one job alters the view to point to tableB and the other job alters the view to point to tableA, when it's recreated?
Sorry if I'm off track with this
EDIT 07/04/2010
I really hope I'm not embarresing myself by being off track with what's been suggested. If it is a case of pointing to each new main table, one after the other, is it possible to use the code I've written below to alter the view?
This looks inside syscomments with the ID that matches my view to see if there is the matching table name in the [text] field. If so, it alters the view to point to the other main table and vice versa each time it runs; every 30 minutes to update the data.
(please note the * is used only for example)
IF
(
select [TEXT]
from syscomments where ID = 2027154267
) like '%tableA%'
EXEC DBNAME..sp_executesql
N'alter view vwTableSwap
AS
SELECT *
FROM dbo.tableB'
ELSE
EXEC DBNAME..sp_executesql
N'alter view vwTableSwap
AS
SELECT *
FROM dbo.tableA'
Paul
April 7, 2010 at 6:06 pm
USE tempdb;
GO
CREATE TABLE dbo.A (a INT NOT NULL);
CREATE TABLE dbo.B (b INT NOT NULL, c INT NOT NULL);
GO
INSERT dbo.A (a) VALUES (1);
INSERT dbo.B (b, c) VALUES (2, 3)
GO
CREATE SYNONYM dbo.MyTable FOR dbo.A;
GO
SELECT *
FROM MyTable;
GO
IF EXISTS
(
SELECT *
FROM sys.synonyms
WHERE name = N'MyTable'
AND base_object_name = N'[dbo].[A]'
)
BEGIN
DROP SYNONYM dbo.MyTable;
CREATE SYNONYM dbo.MyTable FOR dbo.B;
END;
GO
SELECT *
FROM MyTable;
GO
DROP SYNONYM dbo.MyTable;
DROP TABLE dbo.A;
DROP TABLE dbo.B;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 6:17 pm
Paul White NZ (4/7/2010)
USE tempdb;
GO
CREATE TABLE dbo.A (a INT NOT NULL);
CREATE TABLE dbo.B (b INT NOT NULL, c INT NOT NULL);
GO
INSERT dbo.A (a) VALUES (1);
INSERT dbo.B (b, c) VALUES (2, 3)
GO
CREATE SYNONYM MyTable FOR dbo.A;
GO
SELECT *
FROM MyTable;
GO
IF EXISTS
(
SELECT *
FROM sys.synonyms
WHERE name = N'MyTable'
AND base_object_name = N'[dbo].[A]'
)
BEGIN
DROP SYNONYM MyTable;
CREATE SYNONYM MyTable FOR dbo.B;
END;
GO
SELECT *
FROM MyTable;
GO
DROP SYNONYM MyTable;
DROP TABLE dbo.A;
DROP TABLE dbo.B;
Paul - that would work great if the OP was not working against a SQL 2000 database. From the previous post, he stated that SSRS is at 2005 but the source systems (where I assume this process would have to be run) are all 2000 servers.
One thing I would say about your solution is that you really should schema qualify the synonym creation to make sure it is created in the right schema.
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
April 7, 2010 at 8:24 pm
Jeffrey Williams-493691 (4/7/2010)
Paul - that would work great if the OP was not working against a SQL 2000 database. From the previous post, he stated that SSRS is at 2005 but the source systems (where I assume this process would have to be run) are all 2000 servers.
Heh...so, I hadn't noticed that - I just saw the 2005 reference. Nevertheless, this is the 2005 forum, so maybe other people that read this thread in future will find it useful? Threads last a long time, and the discussions often benefit more than just the original author you know 😉
One thing I would say about your solution is that you really should schema qualify the synonym creation to make sure it is created in the right schema.
Ha...demo code in tempdb...picky! :laugh:
I'll edit it in for you. 😎
You'll notice the SELECT * too, and a missing semicolon statement-terminator at one point as well :doze:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 9:55 pm
Paul White NZ (4/7/2010)Heh...so, I hadn't noticed that - I just saw the 2005 reference. Nevertheless, this is the 2005 forum, so maybe other people that read this thread in future will find it useful? Threads last a long time, and the discussions often benefit more than just the original author you know 😉
One thing I would say about your solution is that you really should schema qualify the synonym creation to make sure it is created in the right schema.
Ha...demo code in tempdb...picky! :laugh:
I'll edit it in for you. 😎
You'll notice the SELECT * too, and a missing semicolon statement-terminator at one point as well :doze:
Yeah - I agree, in fact I recommend synonyms for a lot of solutions myself. And, I know it is a bit picky on the schema stuff, but it is kind of a pet peeve of mine. :crazy:
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
April 7, 2010 at 10:29 pm
Jeffrey Williams-493691 (4/7/2010)
...but it is kind of a pet peeve of mine.
I am usually fastidious about schema qualification - except for trigger definitions, for some reason.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 8, 2010 at 2:23 am
Paul White( wrote
USE tempdb;
GO
CREATE TABLE dbo.A (a INT NOT NULL);
CREATE TABLE dbo.B (b INT NOT NULL, c INT NOT NULL);
GO
INSERT dbo.A (a) VALUES (1);
INSERT dbo.B (b, c) VALUES (2, 3)
GO
CREATE SYNONYM dbo.MyTable FOR dbo.A;
GO
SELECT *
FROM MyTable;
GO
IF EXISTS
(
SELECT *
FROM sys.synonyms
WHERE name = N'MyTable'
AND base_object_name = N'[dbo].[A]'
)
BEGIN
DROP SYNONYM dbo.MyTable;
CREATE SYNONYM dbo.MyTable FOR dbo.B;
END;
GO
SELECT *
FROM MyTable;
GO
DROP SYNONYM dbo.MyTable;
DROP TABLE dbo.A;
DROP TABLE dbo.B;
Thanks for showing me this Paul... I think you're right that this will be usefull, so I may be able to use this in the future.
Jeffrey Williams(08/04/2010)
SSRS is at 2005 but the source systems (where I assume this process would have to be run) are all 2000 servers.
I've confused people by using a 2005 forum so many apologies for that.
Does anyone think that the code I've provided below will work for my cause? I used the code against test dbs and it seemed to work fine, however, would it work in a production environment? Any support on this question would be much appreciated:)
IF
(
select [TEXT]
from syscomments where ID = 2027154267
) like '%tableA%'
EXEC DBNAME..sp_executesql
N'alter view vwTableSwap
AS
SELECT *
FROM dbo.tableB'
ELSE
EXEC DBNAME..sp_executesql
N'alter view vwTableSwap
AS
SELECT *
FROM dbo.tableA'
April 9, 2010 at 3:16 am
Since one of the tables will be dropped, I've decided to use
IF OBJECT_ID (N'dbo.tableA') IS NOT NULL
/*
--run code here to
exec usp_B to create my tableB add all data and then...
*/
EXEC DBNAME..sp_executesql
N'alter view vwTableSwap
AS
SELECT *
FROM dbo.tableB';
drop tableA;
else
/*
--run code here to
exec usp_A to create my tableA add all data and then...
*/
EXEC DBNAME..sp_executesql
N'alter view vwTableSwap
AS
SELECT *
FROM dbo.tableA';
drop tableB;
Thanks for all the help I've had with this:)
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply