How to change clustered PK to non clustered on a replicated table

Hello,
I want to know if the re is an easy way to modify the Primary Key on a
replicated table to be a non-clustered index instead. Looking through
queries hitting this table show a clustered index on a different field will
significantly increase performance.
Anyone run into this and find a solution?
Thanks.
~lbLonnye Blake Bower wrote:
> Hello,
> I want to know if the re is an easy way to modify the Primary Key on a
> replicated table to be a non-clustered index instead. Looking through
> queries hitting this table show a clustered index on a different
> field will significantly increase performance.
> Anyone run into this and find a solution?
> Thanks.
> ~lb
Unfortunately, I don't think there is an easy way to do this. If you
ever wonder how a tool , like SQL Enterprise Manager, does something
using its UI, you can run Profiler to watch the activity. When you
change a PK constraint from clustered to non-clustered it looks like SQL
EM is creating new tables with the desired changes (and this affects FK
tables as well).
What you can do is work from a test database until you get the script
working properly. The script will have to remove the FK constraints,
remove the PK constraint, change the PK constraint, and re-create the FK
constraints. Once you have the script working, you should start the
database is single-user mode and make your changes off-hours.
David Gugick
Imceda Software
www.imceda.com

How to capture return value from 'execute'

declare @.table varchar(100);

declare @.q varchar(100);

declare @.key bigint;

select @.table = 'key_table';

select @.q = 'select key from ' + @.table;

select @.key = exec(@.q); -> not working.

Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||

In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.

Thanks,

|||

create a temp table and use this type

insert into #tempTable

exec ( @.yourExecString )

Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.

|||

Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.

declare @.table sysname;

declare @.q nvarchar(100);

declare @.key bigint;

select @.table = N'key_table';

select @.q = 'select @.key = key from dbo.[' + @.table + N']';

exec sp_executesql @.q, N'@.key bigint output', @.key output;

select @.key

go

Be careful with sql injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

How to calculate response time.

Hello.
I have a task to do which is as follows:
I have to do an "id query" on a primary key of my db and then calculate the response time of the query.
Task says that i'm able to take screenshots of a graphical view of response time.And as a hint says that there are sql commands that can do such thing.Allthough i've searched the books i have access to and the internet i can't find such thing.I dunno maybe someone more familiar with this stuff can understand what i'm supposed to do.I don't want to give me the whole solution but just if u can tell me where to look.
I don't understand what means "do an id query on primary key 'SMTH' " and i can't find anythin which is related on calculating system response time to execute a query.
Any help is much appreciated.
Thx a lot :)See set statistics statement, set statistics time in particular