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

TSQL COALESCE v ISNULL

The COALESCE function accepts a list of expressions as input and returns the first that is not NULL. COALESCE(<expr1>, <expr2>…<exprn>). The ISNULL function replaces  expression1 with expression2 if expression1 is NULL. The difference is that ISNULL uses the dataformat of expression1 for the replacement whereas COALESCE uses the dataformat of the first not NULL expression:

DECLARE
    @x as varchar(5) = NULL,
    @y as varchar(10) = '0123456789',
    @z as varchar(15) = 'ABCDEGHIJKLMNOP';

select
    COALESCE    (@X,@Y) as Cxy,
    ISNULL        (@x,@y)    as Ixy,
    COALESCE    (@X,@z) as Cxz,
    ISNULL        (@x,@z)    as Ixz
   
--Cxy                Ixy        Cxz                           Ixz
--0123456789    01234    ABCDEGHIJKLMNOP    ABCDE

set @y = NULL;

select
    COALESCE    (@X,@Y,@z) as Cxyz,
    ISNULL(ISNULL(@x,@y),@z)    as Ixyz
--Cxyz                          Ixyz
--ABCDEGHIJKLMNOP    ABCDE

Till Next Time

TSQL newsequentialid()

If you have a table where you use a GUID as a tablekey please consider using newsequentialid() instead of newid().

Why? newsequentialid() uses fewer cached data pages.  Normally a data page or an index page reserves space for in between values, with newsequentialid()  you will have a better filled page, making you DBA happy

Here is an example:

CREATE TABLE [dbo].[tNewSequentialID](
    [RecGuid] [uniqueidentifier] NOT NULL,
    [Text] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_tNewSequentialID] PRIMARY KEY CLUSTERED
(
    [RecGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tNEWSEQUENTIALID] ADD  CONSTRAINT [DF_tNEWSEQUENTIALID_RecGuid]  DEFAULT (newsequentialid()) FOR [RecGuid]
GO

INSERT into  [dbo].[tNewSequentialID] ([Text]) Values (N'A');
INSERT into  [dbo].[tNewSequentialID] ([Text]) Values (N'B');
INSERT into  [dbo].[tNewSequentialID] ([Text]) Values (N'C');

Select * from  [dbo].[tNEWSEQUENTIALID];

Till Next Time

TSQL TRY_CAST, TRY_PARSE & TRY_CONVERT

Normally if you use a CAST, PARSE or CONVERT the whole statement fails if a value can’t be cast, parsed or converted. If you use the TRY_ version you get a NULL result if the conversion fails without an error.

SELECT CAST(N'ABC' as INT) as CastResult;
--Conversion failed when converting the nvarchar value 'ABC' to data type int.

SELECT TRY_CAST(N'ABC' as INT) as CastResult;
-- Result NULL

SELECT PARSE('$345,98' AS money USING 'nl-NL') as ParseResult;
-- Error converting string value '$345,98' into data type money using culture 'nl-NL'.
-- (They use Euro's in NL)

SELECT TRY_PARSE('$345,98' AS money USING 'de-DE') AS ParseResult;
-- Result Null

SET DATEFORMAT dmy;
SELECT CONVERT(datetime2, '12/31/2010') AS ConvertResult;
-- Conversion failed when converting date and/or time from character string.

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS ConvertResult;
-- Result Null

Till Next Time

TSQL Playing with aggregates part 2

In part 1 we went back to the basics in this part I want to show more advanced possibility's.

Consider the following question: How much do my customers contribute to my sales on a yearly basis?

This means we have to have at least two numbers in the same query:

• Total Sales per year.
• Total Sales per year per customer.

To achieve this we can use the “PARTITION BY” clause in the “OVER ()” statement:

 

Don’t panic if you see the same number reappearing in SalesPerYearPerCustomer column. This is caused by the fact that the people who made the AdventureWorks base set weren’t very creative with the orders…….

To work around this I added an extra column named SalesAmountReal and filled this with a randomized amount:

This would give us:

Even in my randomized set there isn’t a huge spread in sales amount.

Till Next Time

TSQL Playing with aggregates part 1

One of the least understood functionalities in TSQL is the usage of aggregates. In this article I want to back to the basics. For my examples I’m using the AdventureWorks 2012 DW:

The most basic aggregates are count and sum. How many or how much do I have:

For an information point of view these number don’t tell much. Most of the time one is interested in amount per category. A category can be something like period, customer, product, salesrep etc. Most querys have a period based axis.

This introduces the “GROUP BY” part. As you can see the results aren’t ordered. For that we have to a an “ORDER BY”.

Both the “GROUP BY” and the “ORDER BY” work for the whole set. In part 2 of this series I will show you how to make them work for subsets.

Till Next Time