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

TSQL: Finding last key identity value

Often when people need the last inserted ID you see query’s like:

USE AdventureWorks2012;

select
    MAX([SalesOrderDetailID])
FROM
[Sales].[SalesOrderDetail];

On large table’s this can be very expensive especially if the identity column if part of a combined column primary key.

Luckily TSQL has a build in function called “IDENT_CURRENT”. This function gives back the last used identity value for a given table.

select IDENT_CURRENT( '[Sales].[SalesOrderDetail]' );

Till Next Time

TSQL EOMONTH Function

Most people use something like this:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,SYSDATETIME())+1,0)) as LastDayOfCurrentMonth;

to determine the last day of the current month. The problem with this function is that it actually return the last second of the current month.

Few people now the build in TSQL function EOMONTH. Here are some examples:

SELECT EOMONTH(SYSDATETIME()) as LastDayOfCurrentMonth;
SELECT EOMONTH('20130115') as LastDayOfSpecificMonth;
SELECT DATEADD(DAY,1,EOMONTH(SYSDATETIME(),-1)) as FirstDayOfCurrentMonth;
SELECT DATEADD(DAY,1,EOMONTH('20130115',-1)) as FistDayOfSpecificMonth;

Till Next Time