UPDATE in Batches

WHILE (2 > 1)
BEGIN
  BEGIN TRANSACTION
  UPDATE TOP(100000)
  <<TABLE>>  
  SET <<COLUMN>> = <<VALUE>>
  WHERE <<WHERE_CLAUSE>>

   IF @@ROWCOUNT = 0
     BEGIN
       COMMIT TRANSACTION
       BREAK
     END
COMMIT TRANSACTION
-- 1 second delay
  WAITFOR DELAY '00:00:01'
END -- WHILE



Till Next Time

 

CHECKSUM_AGG

Playing around with CHECKSUM_AGG:

select CHECKSUM_AGG ( QTY ) AS CheckSumAgg_QTY, CHECKSUM_AGG ( Distinct QTY ) AS CheckSumAgg_DistinctQTY, sum(QTY) as Sum_QTY


from (

select 4 as qty

union all

select 2 as qty

union all

select 2 as qty

union all

select 2 as qty) T   Till Next Time

Playing around with default Contraints

select
schema_name(t.schema_id) as Schema_Name,
t.name as Table_Name,
d.name as Default_Constraint_Name,
c.name as Column_Name,
d.definition as Constraint_Definition
, 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) +'.'+ QUOTENAME(T.NAME) + ' DROP CONSTRAINT ' + D.NAME + ';' as Drop_Statement
, 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) +'.'+ QUOTENAME(T.NAME) + ' ADD CONSTRAINT DF_'+schema_name(t.schema_id)+'_'+t.name+'_'+ c.name + ' DEFAULT '+d.definition + ' FOR '+QUOTENAME( C.NAME) +';' as Create_Statement
, 'exec sp_rename ''' +schema_name(t.schema_id)+'.'+d.name +''', ''DF_'+schema_name(t.schema_id)+'_'+ t.name+'_'+ c.name +''';' as Rename_Statement
from sys.tables t
join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id

Till Next Time

TSQL Difference between 2 rows

use AdventureWorks2012;

select

CustomerID,
OrderDate,
TotalDue,
sum(Totaldue) over(partition by CustomerId order by OrderDate Desc ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) as LastOrderAmount,
TotalDue - sum(Totaldue) over(partition by CustomerId order by OrderDate Desc ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) as ChangeFromLastOrder from sales.SalesOrderHeader

order by CustomerID, OrderDate Desc

Till Next Time

TSQL Last Backup Taken

select

@@SERVERNAME as ServerName,
Database_name,
COALESCE(Convert(varchar(20), MAX(backup_finish_date), 112),'Backup Not Taken') as
LastBackUpTakenDate,
COALESCE(Convert(varchar(20), MAX(user_name), 101),'NA') as BackupTakenUser
from
msdb.dbo.backupset
GROUP BY
Database_name
order by
LastBackUpTakenDate desc

Till Next Time

TSQL Please avoid Float / Real datatypes

The Float / Real (=> Float(24)) datatype are Approximate-number data types. This means you can run into trouble converting them to other type or doing math with them:

From the documentation:

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

(see: http://msdn.microsoft.com/en-us/library/ms173773(v=sql.110).aspx)

Converting example:

DECLARE @f as Float = '29545428.022495';
select cast(@f as numeric(28,14));

result:

29545428.02249500200000

Math Example:

DECLARE @f as Float = '29545428.022495';

select cast(((@f * 4))as numeric(28,14)) ;

118181712.08998001000000

result:
select cast(@f as numeric(28,14)) * 4 ;

result:

118181712.08998000800000

Till Next Time

TSQL The danger of using SCOPE_IDENTITY() and @@IDENTITY

If you are using

select SCOPE_IDENTITY();

or

select @@IDENTITY;

to determine the last sequence number added to an identity column you must keep in mind that these are session and scope depended functions.

• SCOPE_IDENTITY() returns the last inserted identity value within the scope of the current session.
• @@IDENTITY returns the last inserted identity value within the current session.
  

Since both function are table independent you run the risks of getting the identity value for the wrong table. It’s better to use IDENT_CURRENT(‘TableName’);

Till Next Time