backup and remove old files

-- i8 Solutions
-- backup database
-- retention 2 weeks


DECLARE @strDirectory NVARCHAR(50)= 'D:\i8Solutions\bu\';
DECLARE @dtDeleteBefore datetime;
DECLARE @MyFileName varchar(50);
DECLARE @strDatabaseName NVARCHAR (50)=N'i8_Portal';

SELECT @MyFileName = (SELECT  @strDirectory + @strDatabaseName + '_' + convert(varchar(50),GetDate(),112) + '.bak')

-- Declare and initialize variables
DECLARE @dt DATETIME =
ISNULL(@dtDeleteBefore, DATEADD(WEEK, -2, GETDATE()));


-- Delete the backup history older than the specified date
EXECUTE msdb.dbo.sp_delete_backuphistory
@oldest_date = @dt;

-- Delete the backup files older than the specified date
EXECUTE master.dbo.xp_delete_file
0 -- file type; 0 = backup, 1 = report
, @strDirectory -- directory in which to look for files
,  'bak'-- extension of files to look for (no period!)
, @dt -- delete files older than this date
, 1 -- traverse sub-folders; 0 = FALSE, 1 = TRUE




BACKUP DATABASE @strDatabaseName
TO  DISK = @MyFileName WITH NOFORMAT,
INIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Dutch Date Time Formant

select 
        convert(varchar(2),datepart(d,getdate()))                    -- Dag
+ ' ' + convert(varchar(20),datename (MONTH,getdate()))            -- Maandnaam
+ ' ' + convert(varchar(4),datepart(yyyy,getdate()))                -- Jaar
+ ' ' + convert(varchar(2),datepart(HH,getdate()))                    -- Uur
+ ':' + convert(varchar(2),datepart(mi,getdate()))                    -- Minuut
Till Next Time

Parent Child with Hierarchy Path

;WITH cte AS (
    SELECT
      EmployeeKey
    , FirstName + ' ' + LastName EmployeeName
    , ParentEmployeeKey
    , 1 EmployeeLevel
    , CAST(EmployeeKey as varchar(max)) as  HierarchyPath
    FROM dbo.DimEmployee
    WHERE ParentEmployeeKey IS NULL
    and EndDate IS NULL
    UNION ALL
    SELECT
      c.EmployeeKey
    , c.FirstName + ' ' + c.LastName
    , c.ParentEmployeeKey
    , p.EmployeeLevel + 1
    , p.HierarchyPath + '\' + cast(c.EmployeeKey as varchar(max)) as  HierarchyPath
    FROM dbo.DimEmployee c
    JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey
    where EndDate IS NULL
)
SELECT
      cte.EmployeeName
    , cte.EmployeeLevel
    , HierarchyPath
    , cte.EmployeeKey
    , cte.ParentEmployeeKey
    , case when sum(isnull(f.SalesAmount,0)) <> 0 then cast(1 as bit) else cast(0 as bit) end as HasFactRecords
 FROM cte
LEFT JOIN dbo.FactResellerSales f
  ON f.EmployeeKey = cte.EmployeeKey
  group by   cte.EmployeeName, cte.EmployeeLevel , HierarchyPath     , cte.EmployeeKey ,cte.ParentEmployeeKey
ORDER BY HierarchyPath, EmployeeName






Till Next Time

TSQL Parent Child

;WITH cte AS (
    SELECT
      EmployeeKey
    , FirstName + ' ' + LastName EmployeeName
    , ParentEmployeeKey
    , 1 EmployeeLevel
    FROM dbo.DimEmployee
    WHERE ParentEmployeeKey IS NULL
    UNION ALL
    SELECT
      c.EmployeeKey
    , c.FirstName + ' ' + c.LastName
    , c.ParentEmployeeKey
    , p.EmployeeLevel + 1
    FROM dbo.DimEmployee c
    JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey
)
SELECT
      REPLICATE('- ',cte.EmployeeLevel) +  cte.EmployeeName
    , cte.EmployeeLevel
    , sum(ISNULL(f.SalesAmount, 0)) SalesAmount
FROM cte
LEFT JOIN dbo.FactResellerSales f
  ON f.EmployeeKey = cte.EmployeeKey
  group by cte.EmployeeName,cte.ParentEmployeeKey,cte.EmployeeLevel
ORDER BY EmployeeLevel, EmployeeName

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 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: 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

TSQL Clearing CRASH tables

declare @strtablename varchar(250)
declare @strsql varchar(250)
declare tcur cursor for
select name from sys.objects where name like 'crash%'
and type ='u'
open tcur
fetch tcur into @strtablename
while @@fetch_status = 0
begin
set @strsql = 'delete from dbo.'+ @strtablename + ' where crash_date <= getdate() - 60 '
exec( @strsql)
fetch next from tcur into @strtablename
end
close tcur
deallocate tcur

Welcome to TSQL 101

One of the many 101 blogs I reserverd