June 10, 2016 at 2:04 am
Hi,
Can any one suggest and explain me which is better to run on table which having billion of records.
Syntax:
update table set columnname= 1 where col1= 2648;
-------------------------------------------------------------
DECLARE @updatesql nvarchar(max)
set @updatesql = 'update table
set columnname= 1
where extractorg_id = 2648';
exec sp_executesql @updatesql;
June 10, 2016 at 2:22 am
With that example there's no difference, so my advice would be to go with what's simplest. But if you were to put the dynamic SQL version into a stored procedure definition, you'd start creating security issues since you'd need to grant the caller access to the underlying table instead of just the stored procedure.
If you do go ahead with dynamic SQL, you might consider parameterising it properly to make it more reusable and to make it less likely that anyone who comes along afterwards will make a change that leaves you vulnerable to SQL injection. I haven't tested this so it might not be 100% syntactically correct.
DECLARE @updatesql nvarchar(max), @eoid int, @params nvarchar(max);
SET @eoid = 2648;
SET @params = N'@eoid int';
set @updatesql = N'update table
set columnname= 1
where extractorg_id = @eoid';
exec sp_executesql @stmt = @updatesql, @eoid = @eoid, @params = @params;
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply