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